1 / 30

Outline: Concepts and Architecture (Chapter 2 – 3 rd , 4 th , 5 th , and 6 th ed.) Database schema Working process

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.

triveni
Download Presentation

Outline: Concepts and Architecture (Chapter 2 – 3 rd , 4 th , 5 th , and 6 th ed.) Database schema Working process

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Outline: Concepts and Architecture • (Chapter 2 – 3rd, 4th, 5th, and 6th ed.) • Database schema • Working process with a database system • Database system architecture • Data independence concept • Database language • Database application • Interfaces • Database environment Yangjun Chen ACS-3902

  2. 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 Yangjun Chen ACS-3902

  3. Sample database Course CName CNo CrHrs Dept Database 8803 3 CS C 2606 3 CS Student Name StNo Class Major Grades StNo SId Grade Smith 17 1 CS 17 25 A Brown 8 2 CS 17 43 B Section SId CNo Semester Yr Instructor 32 8803 Spring 2000 Smith 25 8803 Winter 2000 Smith 43 2606 Spring 2000 Jones Yangjun Chen ACS-3902

  4. University database schema: • Course • Cname string(20) • CNo string(20) • CrHrs integer • Dept. String(20) • grades • StNo integer • SId integer • Grade String(20) • Student • Name string(20) • StNo integer • Class integer • Major String(20) • Section • SID integer • CNo string(20) • Semester integer • Yr integer • Instructor string(20) Yangjun Chen ACS-3902

  5. Schema evolution: • Course • Cname string(20) • CNo string(20) • CrHrs integer • Dept. String(20) • Student • Name string(20) • StNo integer • Class integer • Major String(20) • Prerequisite • CNo string(20) • Pre-CNo string(20) … ... new table added Yangjun Chen ACS-3902

  6. Database evolution: • Course • Cname string(20) • Cno string(20) • CrHrs integer • Dept. String(20) • Student • Name string(20) • StNo integer • Class integer • Major String(20) • Age integer • Sex string(20) … ... new attributes added Yangjun Chen ACS-3902

  7. Sample database Course CName CNo CrHrs Dept Database 8803 3 CS C 2606 3 CS Student Name StNo Class Major Grades StNo Sid Grade Smith 17 1 CS 17 25 A Brown 8 2 CS 17 43 B Section SId CNo Semester Yr Instructor 32 8803 Spring 2000 Smith 25 8803 Winter 2000 Smith 43 2606 Spring 2000 Jones Yangjun Chen ACS-3902

  8. Database state changed: Course CName CNo CrHrs Dept Database 8803 3 CS C 2606 3 CS Student Name StNo Class Major Grades StNo Sid Grade Smith 17 1 CS 17 25 A Brown 8 2 CS 17 43 B Section SId CNo Semester Yr Instructor 32 8803 Spring 2000 Smith 25 8803 Winter 2000 Smith Yangjun Chen ACS-3902

  9. Definition We define the database 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 Construction Manipulation Yangjun Chen ACS-3902

  10. Three-schema architecture A specific user or groups view of the database External view External view Describes the whole database for all users Conceptual schema Physical storage structures and details Internal schema Yangjun Chen ACS-3902

  11. 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 Yangjun Chen ACS-3902

  12. 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 Yangjun Chen ACS-3902

  13. 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 DATE, • 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)); Yangjun Chen ACS-3902

  14. 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 Yangjun Chen ACS-3902

  15. 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; Yangjun Chen ACS-3902

  16. Set-oriented operations • select Name, StNo 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 Yangjun Chen ACS-3902

  17. 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 C1 CURSOR FOR SELECT au_fname, au_lname FROM authors FOR BROWSE; EXEC SQL OPEN C1; while (SQLCODE == 0) { EXEC SQL FETCH C1 INTO :fname, :lname; print (“the author name is:”, fname, laname); } Yangjun Chen ACS-3902

  18. DBMS interfaces • menu-based • forms-based • GUI – Graphic User Interface • natural language • for parametric users • for dba Yangjun Chen ACS-3902

  19. Example of a menu-based interface: from MS Access Yangjun Chen ACS-3902

  20. Example of a GUI from StarTracker Yangjun Chen ACS-3902

  21. Example of a natural language interface: (from MS SS7) 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 CarSales WHERE Make = 'Ford’ AND Color = 'Blue' AND DATEPART(yyyy, SalesDate) = ’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. Yangjun Chen ACS-3902

  22. 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. Yangjun Chen ACS-3902

  23. A DDL statement: Create table EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, 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)); Yangjun Chen ACS-3902

  24. no no no yes no yes EMPLOYEE EMPLOYEE EMPLOYEE DNO SUPERSSN FNAME INTEGER STR9 VSTR15 DEPARTMENT EMPLOYEE Meta data in System Catalog: REL_AND_ATTR_CATALOG REL_NAMEATTR_NAME ATTR_TYPE MEMBER_OF_PK MEMBER_OF_FK FK_RELATION ... ... ... ... Yangjun Chen ACS-3902

  25. 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. Yangjun Chen ACS-3902

  26. DBMS utilities • loading • 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 • monitoring database usage and providing statistics to the DBA. Yangjun Chen ACS-3902

  27. Classifying DBMSs • data model: • Relational /object-oriented/hierarchical/network/object-relational • 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, ... Yangjun Chen ACS-3902

  28. With attributes, etc: name number location 1 fname lname works for minit N department salary address name sex 1 number of employees startdate 1 ssn controls manages employee 1 bdate 1 N N supervisor hours N degree supervisee M supervision works on 1 project dependents of name number location N dependent relationship name sex birthdate

  29. 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

  30. 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

More Related