Introduction to Database Systems

1. Introduction to Database Systems Purpose of Database Systems Views of Data Data Models Data Definition Language Data Manipulation Language Transaction Management Storage Management Database Administrator Database Users Overall System Structure

2. Database Management System (DBMS) • Collection of interrelated data • Set of programs to access the data • DBMS contains information about a particular enterprise • DBMS provides an environment that is both convenient and efficient to use.

3. Purpose of Database Systems • Difficulties in conventional file-processing systems: (DBMS attempts to solve) • Data redundancy and inconsistency • Difficulty in accessing data • Data isolation -- multiple files and formats • Integrity problems • Atomicity of updates • Concurrent access by multiple users • Security problems

4. View of Data View level …... View 1 View 2 View n Logical level Physical level

5. Levels of Abstraction • Physical level: describes how a record (eg.: customer) is stored in terms of block#, sector#, byte# etc. • Logical level: describes data stored in database, and the relationship among the data: typedef struct customer { string name; string street; integer city; } • View level: Application programs/GUI etc hide the actual representation and present only the required data in a convenient way. Eg: Visual forms, graphical forms.

6. Instances and Schemas • Schema is a description and an instance is a set of data that fits the description. • Schema : logical structure of the database (eg. Set of customers and accounts and the relationship between them) • Instance : actual content of the database at a particular point in time. • One schema may have many instances. • Analogy : type and variable in a programming language.

7. Data Independence • Ability to modify a schema definition in one level without affecting a scheme definition in the next higher level. • Interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others: • Two levels of data independence: • Physical data independence • Logical data independence

8. Data Models • A collection of representations for describing: • data, data relationships, data semantics, data constraints • Object-based logical models • Entity-Relationship (ER) model • Object-oriented model • Semantic model • Functional model • Record-based logical models • Relational model (e.g. SQL/DS, DB2) • Network model • Hierarchical Model

9. Entity-Relationship Model street SSN number balance city name customer depositor account entity attribute relationship

10. Relational Model Customer Table Name SSN street city account no. Johnson Palo Alto A-101 192-83-7465 Alma Account Table Account No. Balance A-101 500

11. Data Definition Language (DDL) • Specification notation for defining database scheme • DDL compiler generates a set of tables in a data dictionary • Data dictionary contains metadata (data about data) • Data storage and definition language - special type of DDL in which storage and access methods used by the dbms are specified.

12. Data Manipulation Language • Language for accessing and manipulating the data organized by appropriate data model • Two classes of languages: • Procedural - user specifies what data is required and how to get those data • Non-procedural -- user specifies what data is required without specifying how to get those data

13. Transaction Management • A transaction is a collection of operations that perform a single logical function in a database application • Example: Withdraw $100 from Acct Number A-101 • Verify the balance • Update account table (& other tables) • Deliver money

14. Transaction Management (contd.) • Transaction-management component ensures that the db remains in a correct state despite system failures (e.g. power failures and operating system crashes) and transaction failures. • ACID property: Atomicity, Consistency, Isolation, Durability • Concurrency-control manager controls the interaction among the concurrent transactions.

15. Storage Management • A storage manager provides the interface between the low-level data and the application programs and queries submitted to the system. • The storage manager is responsible for the following tasks: • interaction with file-manager • efficient storing, retrieving, and updating of data

16. Naïve users Application programmers Sophisticated users DBA Application programs Application interfaces query Database scheme DDL interpreter Embedded DML compiler DML compiler Object code Query processing Query evaluation engine DBMS System Buffer manager Transaction manager Storage manager File manager Disk storage indices Data files Data dictionary Statistical data Overall System Structure

17. Database Administrator • Coordinates all the activities of the database system; • DBA should have a good understanding of the enterprise’s information resources and needs. • DBA’s duties include: • Scheme definition • Storage structure and access method definition • Granting user authority to access the database • Specifying integrity constraints • Acting as liaison with users • Monitoring performance and responding to changes in requirements

18. Database Users • Differentiated by the how they interact with the system • Naïve users: invoke pre-determined application with high-level user interface • Application programmers; Use DML calls, embedded calls • Sophisticated users: managers, decision support systems: request using query languages • Specialized users: design and write specialized applications.