1 / 29

Introduction to Relational Database Systems

Introduction to Relational Database Systems. Lecture 4. Relational database systems. data objects relations / tables operators applied to tables generate tables. Relations / tables. explicit data values extensionally defined atomic keys integrity design

tuyen
Download Presentation

Introduction to Relational Database Systems

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. Introduction to Relational Database Systems Lecture 4

  2. Relational database systems • data objects • relations / tables • operators • applied to tables • generate tables

  3. Relations / tables • explicit data values • extensionally defined • atomic • keys • integrity • design • includes how to organise data in tables

  4. Data about departments Depts

  5. Data about employees Emps

  6. Atomic values no access to individual items

  7. Primary and foreign keys foreign primary

  8. Integrity • restrictions on data defined by users • on individual tables • age > 18; salary < 100k • on more than one table • if budget < 10M then salary < 50k • implicit in the data model

  9. Primary key integrity incorrect model

  10. Foreign key integrity incorrect model ?

  11. Relational operators • characteristics • set at a time • base and derived tables • ‘closed’ with respect to relations / tables • nested expressions • include • RESTRICT • PROJECT • JOIN

  12. RESTICT RESTRICT Depts WHERE Budget > 8M

  13. PROJECT PROJECT Depts OVER Dept_id, Budget

  14. JOIN JOIN Depts AND Emps OVER Dept_id

  15. Nested statements • “the members of all departments that have the budget greater than 7M” • JOIN (RESTRICT Depts WHERE Budget > 7M) AND Emps OVER Dept_id

  16. Relational model • a data model in which all data is modelled as relations • a way of looking at data • a prescription for a way of • representing data • manipulating data • representing integrity constraints

  17. Relational database systems • relational DBMS • implements the relational model • not in its entirety • may add new features • relational database system • a database application developed in the relational model and implemented in a relational DBMS • physical details hidden from the user

  18. Relational DBMS - features • views • security • the optimiser • the data catalogue / data dictionary

  19. Views (in relational systems) • named derived table • the definition stored in the catalogue • evaluated only when used • optimisation • used as if it were a real table • problems with updates • views • ANSI/SPARC • relational

  20. Views • CREATEVIEW TopEmp AS • PROJECT • (SELECT Emps WHERE Salary > 33K) • OVER E_name, Salary

  21. Views - usage • SELECT TopEmp WHERE Salary <= 40 • SELECT • PROJECT • SELECT Emps WHERE Salary > 33 • OVER E_name, Salary • WHERE Salary <= 40

  22. Security and views • how would you use the view mechanism in conjunction with the security system? DEFINE SECURITY RULE AS ...

  23. The optimiser • operators - set level • the DBMS decides how to best perform the operations, based on • strategies of evaluation • information about the DB (in the catalogue)

  24. The best evaluation strategy • PROJECT • SELECT Emps WHERE E_id = E2 • OVER E_name, Salary

  25. The catalogue • information about the database • schemas • mappings • integrity rules • views definition • security rules ... • other modules that need it • the optimiser • the security system ...

  26. The system table “Tables” PROJECT Tables OVER Coulcount, Rowcount

  27. The system table “Columns”

  28. Summary • relational model • relations • operators • integrity • relational DBMSs • implement the relational model • views + security • the optimiser • the catalogue • next lecture : SQL

More Related