1 / 40

Chapter 4

Chapter 4. The Relational Model 3: Advanced Topics. Objectives. Define, describe, and use views Use indexes to improve database performance Discuss entity, referential, and legal-values integrity Make changes to the structure of a relational database Define and use the system catalog.

misu
Download Presentation

Chapter 4

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. Chapter 4 The Relational Model 3: Advanced Topics Concepts of Database Management, 4th Edition, Pratt & Adamski

  2. Objectives • Define, describe, and use views • Use indexes to improve database performance • Discuss entity, referential, and legal-values integrity • Make changes to the structure of a relational database • Define and use the system catalog Concepts of Database Management, 4th Edition, Pratt & Adamski

  3. Views • Application program’s or individual user’s picture of the database • Less involved than full database • Offers simplification • Provides measure of security • Sensitive tables or columns omitted where not appropriate Concepts of Database Management, 4th Edition, Pratt & Adamski

  4. SQL to Create ViewFigure 4.1 CREATE VIEW Housewares AS SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class=‘HW’ ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  5. Housewares View of Database Figure 4.2 Concepts of Database Management, 4th Edition, Pratt & Adamski

  6. Query on a View • Selects data only from Tables created in the view • Query is merged with query used to create view SELECT * FROM Housewares WHERE OnHand< 25 ; Actually executes as SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class=‘HW’ AND OnHand< 25 ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  7. Access Query Design of View Figures 4.3 - 4.4 Concepts of Database Management, 4th Edition, Pratt & Adamski

  8. Access Query Design of View with Changed Field Names Figures 4.5 - 4.6 Concepts of Database Management, 4th Edition, Pratt & Adamski

  9. SalesCust View SQL Statement CREATE VIEW SalesCust (Snum, SLast, SFirst, Cnum, CName) AS SELECT Rep.RepNum, LastName, FirstName, CustomerNum, CustomerName FROM Rep, Customer WHERE Rep.RepNum=Customer.RepNum ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  10. SalesCust View Figure 4.7 Concepts of Database Management, 4th Edition, Pratt & Adamski

  11. Access Query Design of SalesCust View Figure 4.8 Concepts of Database Management, 4th Edition, Pratt & Adamski

  12. Access Query Design of SalesCust View (con’t.) Figure 4.9 Concepts of Database Management, 4th Edition, Pratt & Adamski

  13. Advantages of Views • Provides data independence • Same data viewed by different users in different ways • Contains only information required by a given user Concepts of Database Management, 4th Edition, Pratt & Adamski

  14. Indexes • Conceptually similar to book index • Increases data retrieval efficiency • Automatically assigns record numbers • Used by DBMS, not by users • Fields on which index built called Index Key Concepts of Database Management, 4th Edition, Pratt & Adamski

  15. Customer Table with Record Numbers Figure 4.10 Concepts of Database Management, 4th Edition, Pratt & Adamski

  16. Customer Table Index on CustomerNum Figure 4.11 Concepts of Database Management, 4th Edition, Pratt & Adamski

  17. Table Indexes on CreditLimit, RepNum Figure 4.12 Concepts of Database Management, 4th Edition, Pratt & Adamski

  18. Pros/Cons of Indexes • Can be added or dropped without loss of function • Can make retrieval more efficient • Occupies space that might be required for other functions • DBMS must update index whenever corresponding data are updated Concepts of Database Management, 4th Edition, Pratt & Adamski

  19. SQL to Create Index CREATE INDEX CustomerName ON Customer (CustomerName) ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  20. SQL to Delete Index DROP INDEX RepBal ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  21. Index on Single Field in Access Figure 4.13 Concepts of Database Management, 4th Edition, Pratt & Adamski

  22. Index on Multiple Fields in Access Figure 4.14 Concepts of Database Management, 4th Edition, Pratt & Adamski

  23. Security • Prevention of unauthorized access to database • Two SQL security mechanisms • GRANT provides privileges to users • REVOKE removes privileges from users GRANT SELECT ON Customer TO JONES ; REVOKE SELECT ON Customer FROM JONES ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  24. Integrity Rules • Related to foreign keys and primary keys • Defined by Dr. E.F. Codd • Entity integrity • No field that is part of the primary key may accept null values • Referential integrity • If Table A contains a foreign key matching the primary key of Table B, then values must match for some row in Table B or be null Concepts of Database Management, 4th Edition, Pratt & Adamski

  25. Primary Key in Access Figure 4.15 PRIMARY KEY (CustomerNum) Concepts of Database Management, 4th Edition, Pratt & Adamski

  26. Multi-Field Primary Key in Access Figure 4.16 PRIMARY KEY (OrderNum, PartNum) Concepts of Database Management, 4th Edition, Pratt & Adamski

  27. Relationships Window to Relate Tables in Access Figure 4.17 FOREIGN KEY (RepNum) REFERENCES Rep Concepts of Database Management, 4th Edition, Pratt & Adamski

  28. Specifying Referential Integrity Figure 4.18 Concepts of Database Management, 4th Edition, Pratt & Adamski

  29. Violating Referential Integrity on Adding Figure 4.19 Concepts of Database Management, 4th Edition, Pratt & Adamski

  30. Violating Referential Integrity on Deleting Figure 4.20 Concepts of Database Management, 4th Edition, Pratt & Adamski

  31. Legal-Values Integrity • States no record can exist with field values other than legal ones • Use SQL CHECK clause CHECK (CreditLimit IN (5000, 7500, 10000, 15000)) ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  32. Other SQL Adding new field ALTER TABLE Customer ADD CustType CHAR(1) ; Changing field properties ALTER TABLE Customer CHANGE COLUMN CustomerName TO CHAR(50) ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  33. Add Field in Access Figure 4.22 Concepts of Database Management, 4th Edition, Pratt & Adamski

  34. Change Field Characteristic in Access Figure 4.23 Concepts of Database Management, 4th Edition, Pratt & Adamski

  35. Other SQL Deleting field ALTER TABLE Part DELETE Warehouse ; DeleteSQL Table DROP TABLE SmallCust ; Concepts of Database Management, 4th Edition, Pratt & Adamski

  36. Delete Field in Access Figure 4.24 Concepts of Database Management, 4th Edition, Pratt & Adamski

  37. Delete Table in Access Figure 4.25 Concepts of Database Management, 4th Edition, Pratt & Adamski

  38. System Catalog • Information about database kept in system catalog • Maintained by DBMS • Example catalog has two tables • Systables • Syscolumns Concepts of Database Management, 4th Edition, Pratt & Adamski

  39. Systables Table Figure 4.26 Concepts of Database Management, 4th Edition, Pratt & Adamski

  40. Partial Syscolumns Table Figure 4.27 Concepts of Database Management, 4th Edition, Pratt & Adamski

More Related