Introduction to Database Systems z Purpose of Database

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

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

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

View of Data View level View 1 View 2 …. . . View n Logical level Physical level 9/3/2021 4

Levels of Abstraction z Physical level: describes how a record (eg. : customer) is stored in terms of block#, sector#, byte# etc. z Logical level: describes data stored in database, and the relationship among the data: customer { string name; string street; integer city; } z 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. 9/3/2021 5

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

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

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

Entity-Relationship Model SSN street number balance city name customer depositor account entity attribute relationship 9/3/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 9/3/2021 500 10

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

Data Manipulation Language z. Language for accessing and manipulating the data organized by appropriate data model z. Two classes of languages: y. Procedural - user specifies what data is required and how to get those data y. Non-procedural -- user specifies what data is required without specifying how to get those data z. Query language (sequential query 9/3/2021 language: sql) : insert data, update data 12

Relational Algebra z. Tables representing relations z. Row uniquely identified by a primary key z. Related tables are associated using foreign keys z. Special operators are defined and axioms: join, project etc. : relational algebra zhttp: //db. grussell. org/section 010. html 9/3/2021 13

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

Transaction Management (contd. ) z 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. z ACID property: Atomicity, Consistency, Isolation, Durability z Concurrency-control manager controls the interaction among the concurrent transactions. 9/3/2021 15

Storage Management z. A storage manager provides the interface between the low-level data and the application programs and queries submitted to the system. z. The storage manager is responsible for the following tasks: yinteraction with file-manager yefficient storing, retrieving, and updating of data 9/3/2021 16

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 9/3/2021 Application programmers indices Statistical data Data dictionary Disk storage 17

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

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