Introduction to Database Systems Purpose of Database Systems

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

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. 12/15/2021 2

Purpose of Database Systems Difficulties in conventional file-processing systems: (DBMS attempts to solve) n n n n 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 12/15/2021 3

View of Data View level View 1 View 2 …. . . View n Logical level Physical level 12/15/2021 4

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. 12/15/2021 5

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. 12/15/2021 6

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: n Physical data independence n Logical data independence 12/15/2021 7

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

Entity-Relationship Model SSN street number balance city name customer depositor account entity attribute relationship 12/15/2021 9

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

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/15/2021 11

Data Manipulation Language for accessing and manipulating the data organized by appropriate data model Two classes of languages: n n 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 12/15/2021 12

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 n n n Verify the balance Update account table (& other tables) Deliver money 12/15/2021 13

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. 12/15/2021 14

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: n n interaction with file-manager efficient storing, retrieving, and updating of data 12/15/2021 15

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

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: n Scheme definition n Storage structure and access method definition n Granting user authority to access the database n Specifying integrity constraints n Acting as liaison with users n Monitoring performance and responding to changes in requirements 12/15/2021 17

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. 12/15/2021 18