Database system concepts and architecture Outline Concepts and
Database system concepts and architecture Outline: Concepts and Architecture (Chapter 2 – 3 rd, 4 th, 5 th, and 6 th ed. ) • Database schema • Working process with a database system • Database system architecture • Data independence concept • Database language • Database application • Interfaces • Database environment Sept. 2012 Yangjun Chen ACS-3902 1
Database system concepts and architecture Schema: a description of a database -- meta data • the intension of the database Schema evolution: if the database definition changes, we say it evolves. Database State: the data in the database at a particular point in time • the extension of the schema Sept. 2012 Yangjun Chen ACS-3902 2
Database system concepts and architecture Sample database Course CName CNo Cr. Hrs Dept Database 8803 3 CS C Student Name St. No Class Major Smith 17 1 CS Brown 8 2 CS Section SId CNo 32 8803 25 8803 43 2606 Sept. 2012 Semester Yr Spring 2000 Winter 2000 Spring 2000 Yangjun Chen 2606 3 CS Grades St. No SId Grade 17 25 A 17 43 B Instructor Smith Jones ACS-3902 3
Database system concepts and architecture University database schema: Student Course Cname CNo Cr. Hrs Dept. Sept. 2012 string(20) integer String(20) Section grades St. No SId Grade Name St. No Class Major string(20) integer String(20) SID CNo Semester Yr Instructor integer String(20) Yangjun Chen ACS-3902 integer string(20) 4
Database system concepts and architecture Schema evolution: Student Course Cname CNo Cr. Hrs Dept. Name St. No Class Major string(20) integer String(20) Prerequisite CNo Pre-CNo …. . . string(20) ed add e l b a t new Sept. 2012 Yangjun Chen ACS-3902 5
Database system concepts and architecture Database evolution: Student Course Cname Cno Cr. Hrs Dept. Name St. No Class Major Age Sex string(20) integer String(20) integer string(20) …. . . d e d d a s e t ribu t t a w e n Sept. 2012 Yangjun Chen ACS-3902 6
Database system concepts and architecture Sample database Course CName CNo Cr. Hrs Dept Database 8803 3 CS C Student Name St. No Class Major Smith 17 1 CS Brown 8 2 CS Section SId CNo 32 8803 25 8803 43 2606 Sept. 2012 Semester Yr Spring 2000 Winter 2000 Spring 2000 Yangjun Chen 2606 3 CS Grades St. No Sid Grade 17 25 A 17 43 B Instructor Smith Jones ACS-3902 7
Database system concepts and architecture Database state changed: Course CName CNo Cr. Hrs Dept Database 8803 3 CS C Student Name St. No Class Major Smith 17 1 CS Brown 8 2 CS 2606 3 CS Grades St. No Sid Grade 17 25 A 17 43 B Section SId CNo Semester Yr Instructor 32 8803 Spring 2000 Smith 25 8803 Winter 2000 Smith Sept. 2012 Yangjun Chen ACS-3902 8
Database system concepts and architecture We define the database Definition Construction Manipulation database state is the empty state with no data initial state when database is populated (loaded) current state changes with each insert, delete, update hopefully, the database goes from one correct/valid state to another Sept. 2012 Yangjun Chen ACS-3902 9
Database system concepts and architecture Three-schema architecture External view Describes the whole database for all users Conceptual schema Physical storage structures and details Internal schema Sept. 2012 Yangjun Chen A specific user or groups view of the database ACS-3902 10
Database system concepts and architecture Data independence Ability to change the database at one level with no impact to the next higher level • physical data independence - the ability to change the physical schema without affecting the conceptual schema • typical example: add a new index • logical data independence - the ability to change the conceptual schema without affecting existing external views or application programs • typical example: add an attribute Sept. 2012 Yangjun Chen ACS-3902 11
Database system concepts and architecture DBMS Languages Data definition language (DDL): used to define the data schema. Ideally one schema definition language per level • in practice there might be one for all levels • SQL provides DDL capabilities for the conceptual and external levels Sept. 2012 Yangjun Chen ACS-3902 12
Database system concepts and architecture DDL - Examples: • Create schema: Create schema COMPANY authorization JSMITH; • Create table: Create table EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10, 2), SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY(SSN), FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER)); Sept. 2012 Yangjun Chen ACS-3902 13
Database system concepts and architecture DBMS Languages Data Manipulation language (DML): Used to manipulate data. • typical systems provide a general purpose language for inserting, updating, deleting, and retrieving data • two distinctions: set-oriented and row-at-a-time Sept. 2012 Yangjun Chen ACS-3902 14
Database system concepts and architecture DML - Examples: • Insert • Update • Delete INSERT INTO employee ( fname, lname, ssn, dno ) VALUES ( "Joe", "Smith", 909, 1); UPDATE employee SET salary = 100000 WHERE ssn = 909; DELETE FROM employee WHERE ssn = 909; Query: Select salary from employee; Sept. 2012 Yangjun Chen ACS-3902 15
Database system concepts and architecture Set-oriented operations • select Name, St. No from student where Name like “M%” • EXEC SQL DELETE FROM authors WHERE au_lname = 'White' Record-at-a-time • procedural - need loops, etc • navigate through data obtaining 1 record at a time • note that SQL does permit this via cursors Sept. 2012 Yangjun Chen ACS-3902 16
Database system concepts and architecture Database applications If an application program that accesses the database embeds DML commands within it, then we have a host language and a data sublanguage. EXEC SQL DECLARE C 1 CURSOR FOR SELECT au_fname, au_lname FROM authors FOR BROWSE; EXEC SQL OPEN C 1; while (SQLCODE == 0) { EXEC SQL FETCH C 1 INTO : fname, : lname; print (“the author name is: ”, fname, laname); } Sept. 2012 Yangjun Chen ACS-3902 17
Database system concepts and architecture DBMS interfaces • menu-based • forms-based • GUI – Graphic User Interface • natural language • for parametric users • for dba Sept. 2012 Yangjun Chen ACS-3902 18
Database system concepts and architecture Example of a menu-based interface: from MS Access Sept. 2012 Yangjun Chen ACS-3902 19
Database system concepts and architecture Example of a GUI from Star. Tracker Sept. 2012 Yangjun Chen ACS-3902 20
Database system concepts and architecture Example of a natural language interface: (from MS SS 7) English Query translates a natural-language question about data in the database to a set of SQL SELECT statements that can then be executed against the SQL Server database to get the answer. For example, given a car sales database, an application can send English Query a string containing the question, “How many blue Fords were sold in 2011? ” English Query returns to the application an SQL statement such as: SELECT COUNT(*) FROM Car. Sales WHERE Make = 'Ford’ AND Color = 'Blue' AND DATEPART(yyyy, Sales. Date) = ’ 2011' The application can then execute the SQL statement against the SQL Server database to get a number. It can return to the user. Note the comment: English Query works best with a normalized database. Sept. 2012 Yangjun Chen ACS-3902 21
Database system concepts and architecture DBMS environment see figure 2. 3 • stored data manager A module to control access to DBMS information that is stored on disk, whether it is part of the database or the catalog. • DDL compiler A module to process schema definition, specified in the DDL, and store description of the schema (meta-data) in the DBMS catalog. • DML compiler It translates the DML commands into object code for database access. • run-time database processor It handles database access at run time; it receives retrieval or update operations and carries them out on the database. Sept. 2012 Yangjun Chen ACS-3902 22
Database system concepts and architecture A DDL statement: Create table EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10, 2), SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY(SSN), FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER)); Sept. 2012 Yangjun Chen ACS-3902 23
Database system concepts and architecture Meta data in System Catalog: REL_AND_ATTR_CATALOG REL_NAME ATTR_TYPE MEMBER_OF_PK MEMBER_OF_FK FK_RELATION FNAME VSTR 15 no no EMPLOYEE SUPERSSN STR 9 no yes EMPLOYEE DNO INTEGER no yes DEPARTMENT EMPLOYEE . . . Sept. 2012 Yangjun Chen ACS-3902 24
Database system concepts and architecture DBMS environment • query compiler It handles high-level queries that are entered interactively. It parses, analyzes, and compiles or interprets a query by creating database access code, and then generates calls to the rum-time processor for executing the code. • pre-compiler It extracts DML commands from an application program which is written in host programming language like C, Pascal, etc. Sept. 2012 Yangjun Chen ACS-3902 25
Database system concepts and architecture DBMS utilities • loading existing files - such as text files or sequential files - into the database. • Backup creating a backup copy of the database, usually by dumping the entire database into tape. • file reorganization reorganizing a database file into a different file organization to get a better performance. • performance monitoring database usage and providing statistics to the DBA. Sept. 2012 Yangjun Chen ACS-3902 26
Database system concepts and architecture Classifying DBMSs data model: Relational /object-oriented/hierarchical/network/objectrelational users: single-user/multi-user location: distributed/centralized cooperation: homogeneous/heterogeneous OLTP: on-line transaction processing • Used to run the day-to-day operations of a business • event-oriented: take an order, make a reservation, payment for goods, withdraw cash, . . . Sept. 2012 Yangjun Chen ACS-3902 27
Database system concepts and architecture name number With attributes, etc: fname ssn bdate degree lname minit sex address startdate employee 1 supervisor works for N salary manages department 1 1 N hours N supervisee 1 supervision works on dependents of N dependent name 1 sex birthdate relationship location M 1 number of employees controls N project name number location
Database system concepts and architecture ER-to-Relational mapping 1. Create a relation for each strong entity type 2. Create a relation for each weak entity type • include primary key of owner (an FK - foreign key) • owner’s PK + partial key becomes PK 3. For each binary 1: 1 relationship choose an entity and include the other’s PK in it as an FK. Include any attributes of the relationship 4. For each binary 1: n relationship, choose the n-side entity and include an FK w. r. t the other entity. Include any attributes of the relationship
Database system concepts and architecture 5. For each binary M: N relationship, create a relation for the relationship • include PKs of both participating entities and any attributes of the relationship • PK is the concatenation of the participating entity PKs 6. For each multivalued attribute create a new relation • include the PK attributes of the entity type • PK is the PK of the entity type and the multivalued attribute 7. For each n-ary relationship, create a relation for the relationship • include PKs of all participating entities and any attributes of the relationship • PK may be the concatenation of the participating entity PKs
- Slides: 30