1 / 48

Concepts of Database Management, Fifth Edition

Concepts of Database Management, Fifth Edition. Chapter 4: The Relational Model 3: Advanced Topics. Objectives. Define, describe, and use views Use indexes to improve database performance Examine the security features of a database management system (DBMS)

pepin
Download Presentation

Concepts of Database Management, Fifth Edition

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

  2. Objectives • Define, describe, and use views • Use indexes to improve database performance • Examine the security features of a database management system (DBMS) • 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, 5th Edition

  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, 5th Edition

  4. SELECT Command • Called the defining query • Indicates precisely what to include in the view • Query acts as a sort of window into the database • Does not produce a new table, only the view of the table Concepts of Database Management, 5th Edition

  5. Figure 4.1: SQL to Create View CREATE VIEW Housewares AS SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class=‘HW’ ; Concepts of Database Management, 5th Edition

  6. Query on a View • With a query that involves a view, the DBMS changes the query to one that selects data from table(s) in the database that created the view • The DBMS merges the query with the query that defines the view to form the query that is actually executed • One advantage of this approach is that the view never exists in its own right so any update to the table is immediately available in the view • If the view were a table, this would not be the case Concepts of Database Management, 5th Edition

  7. 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, 5th Edition

  8. Figures 4.3 - 4.4: Access Query Design of View Concepts of Database Management, 5th Edition

  9. Access Query Design View with Changed Field Names • SQL can be used to change the field names in a view by including the new field names in the CREATE VIEW statement • The CREATE VIEW statement would be: 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, 5th Edition

  10. Figures 4.5-4.6: Access Query Design of View with Changed Field Names Concepts of Database Management, 5th Edition

  11. Row and Column Subset View • Consists of a subset of the rows and columns in some individual table • Because the query can be any SQL query, a view could also join two or more tables Concepts of Database Management, 5th Edition

  12. 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, 5th Edition

  13. 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, 5th Edition

  14. Figure 4.10: Customer Table with Record Numbers Concepts of Database Management, 5th Edition

  15. Figure 4.11: Customer Table Index on CustomerNum Concepts of Database Management, 5th Edition

  16. Figure 4.12: Table Indexes on CreditLimit, RepNum Concepts of Database Management, 5th Edition

  17. 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, 5th Edition

  18. SQL to Create Index CREATE INDEX CustomerName ON Customer (CustomerName) ; Concepts of Database Management, 5th Edition

  19. Creating Indexes • Single-field index – an index whose key is a single field • Multiple-field index • An index with more than one key field • List the most important key first • If data for either key appears in descending order, follow the field name with the letters DESC Concepts of Database Management, 5th Edition

  20. SQL to Delete Index DROP INDEX RepBal ; Concepts of Database Management, 5th Edition

  21. Figure 4.13: Index on Single Field in Access Concepts of Database Management, 5th Edition

  22. Figure 4.14: Index on Multiple Fields in Access Concepts of Database Management, 5th Edition

  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, 5th Edition

  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 Concepts of Database Management, 5th Edition

  25. Integrity Rules (con’t) • To specify primary key, enter a PRIMARY KEY clause in either an ALTER TABLE or a CREATE TABLE command • Foreign key – a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table Concepts of Database Management, 5th Edition

  26. Figure 4.15: Primary Key in Access PRIMARY KEY (CustomerNum) Concepts of Database Management, 5th Edition

  27. Figure 4.16: Multi-Field Primary Key in Access PRIMARY KEY (OrderNum, PartNum) Concepts of Database Management, 5th Edition

  28. 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 • Usually a foreign key is in a different table from the primary key it is required to match • The only restriction is that the foreign key must have a name that is different from the primary key because the fields are in the same table Concepts of Database Management, 5th Edition

  29. Figure 4.17: Relationships Window to Relate Tables in Access FOREIGN KEY (RepNum) REFERENCES Rep Concepts of Database Management, 5th Edition

  30. Cascade Delete and Update • Cascade delete - ensures that the deletion of a master record deletes all records in sub tables related to it • Cascade update – ensures that changes made to the primary key of the master table are also made in the related records Concepts of Database Management, 5th Edition

  31. Figure 4.18: Specifying Referential Integrity Concepts of Database Management, 5th Edition

  32. Enforcing Referential Integrity • With referential integrity enforced, users are not allowed to enter a record that does not match any sales rep currently in the Rep table • An error message, such as the one shown in Figure 4.19, appears when an attempt is made to enter an invalid record Concepts of Database Management, 5th Edition

  33. Figure 4.19: Violating Referential Integrity on Adding Concepts of Database Management, 5th Edition

  34. Legal-Values Integrity • States no record can exist with field values other than legal ones • Use SQL CHECK clause • Validation rule – in Access, a rule that data entered into a field must follow • Validation – in Access, text to inform the user of the reason for the rejection when the user attempts to enter data that violates the rule CHECK (CreditLimit IN (5000, 7500, 10000, 15000)) ; Concepts of Database Management, 5th Edition

  35. Validation Rule in Access Concepts of Database Management, 5th Edition

  36. Structure Changes • Can change the database structure • By adding and removing tables and fields • By changing the characteristics of existing fields • By creating and dropping indexes • The exact manner in which these changes are accomplished varies from one system to another • Most systems allow all of these changes to be made quickly and easily • Made using the SQL ALTER TABLE command Concepts of Database Management, 5th Edition

  37. Structure Changes – Add and Change 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, 5th Edition

  38. Figure 4.22: Add Field in Access Concepts of Database Management, 5th Edition

  39. Figure 4.23: Change Field Characteristic in Access Concepts of Database Management, 5th Edition

  40. Structure Changes - Delete Deleting field ALTER TABLE Part DELETE Warehouse ; Delete SQL Table DROP TABLE SmallCust ; Concepts of Database Management, 5th Edition

  41. Figure 4.24: Delete Field in Access Concepts of Database Management, 5th Edition

  42. Figure 4.25: Delete Table in Access Concepts of Database Management, 5th Edition

  43. System Catalog • Information about database kept in system catalog • Maintained by DBMS • Example catalog has two tables • Systables – information about the tables known to SQL • Syscolumns – information about the columns or fields within these tables Concepts of Database Management, 5th Edition

  44. System Catalog (con’t.) • Other possible tables • Sysindexes – information about the indexes that are defined on these tables • Sysviews – information about the views that have been created Concepts of Database Management, 5th Edition

  45. Figure 4.26: Systables Table Concepts of Database Management, 5th Edition

  46. Summary • Views - used to give each user his or her own view of the data in a database • View is defined in structured query language (SQL) by using a defining query • Indexes are often used to facilitate data retrieval from the database • Security is provided in SQL systems using the GRANT and REVOKE commands • Entity integrity is the property that states that no field that is part of the primary key can accept null values Concepts of Database Management, 5th Edition

  47. Summary • Referential integrity - property stating that the value in any foreign key field must either be null or match an actual value in the primary key field of another table • Legal-values integrity is the property that states that the value entered in a field must be one of the legal values • The ALTER TABLE command allows you to add fields to a table, delete fields, or change the characteristics of fields Concepts of Database Management, 5th Edition

  48. Summary • The DROP TABLE command lets you delete a table from a database • The system catalog is a feature of many relational DBMSs that stores information about the structure of a database Concepts of Database Management, 5th Edition

More Related