Concepts of Database Management Seventh Edition Chapter 4
![Concepts of Database Management Seventh Edition Chapter 4 The Relational Model 3: Advanced Topics Concepts of Database Management Seventh Edition Chapter 4 The Relational Model 3: Advanced Topics](https://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-1.jpg)
Concepts of Database Management Seventh Edition Chapter 4 The Relational Model 3: Advanced Topics
![Objectives • • Define, describe, and use views Use indexes to improve database performance Objectives • • Define, describe, and use views Use indexes to improve database performance](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-2.jpg)
Objectives • • Define, describe, and use views Use indexes to improve database performance Examine the security features of a DBMS Discuss entity, referential, and legal-values integrity 2
![Objectives (continued) • Make changes to the structure of a relational database • Define Objectives (continued) • Make changes to the structure of a relational database • Define](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-3.jpg)
Objectives (continued) • Make changes to the structure of a relational database • Define and use the system catalog • Discuss stored procedures, triggers, and data macros 3
![Views • View: application program’s or individual user’s picture of the database • Less Views • View: application program’s or individual user’s picture of the database • Less](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-4.jpg)
Views • View: application program’s or individual user’s picture of the database • Less involved than full database • Simplification • Security 4
![Views (continued) • Defining query: SELECT command that creates a view – Indicates what Views (continued) • Defining query: SELECT command that creates a view – Indicates what](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-5.jpg)
Views (continued) • Defining query: SELECT command that creates a view – Indicates what to include in the view • Query acts as a window into the database • Does not produce a new table • Query that involves a view – DBMS does not execute the query in this form – Query actually executed is created by merging this query with the query that defines the view 5
![Views (continued) CREATE VIEW Housewares AS SELECT Part. Num, Description, On. Hand, Price FROM Views (continued) CREATE VIEW Housewares AS SELECT Part. Num, Description, On. Hand, Price FROM](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-6.jpg)
Views (continued) CREATE VIEW Housewares AS SELECT Part. Num, Description, On. Hand, Price FROM Part WHERE Class='HW' ; FIGURE 4 -1: Housewares view 6
![Views (continued) • To create a view in Access, create and save a query Views (continued) • To create a view in Access, create and save a query](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-7.jpg)
Views (continued) • To create a view in Access, create and save a query • Changing field names in a view – SQL: include the new field names in the CREATE VIEW command – Access: precede the name of the field with the desired name, followed by a colon • Row-and-column subset view – Subset of rows and columns in an individual table 7
![Views (continued) FIGURE 4 -3: Access query design of the Housewares view 8 Views (continued) FIGURE 4 -3: Access query design of the Housewares view 8](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-8.jpg)
Views (continued) FIGURE 4 -3: Access query design of the Housewares view 8
![Views (continued) FIGURE 4 -5: Access query design of the Housewares view with changed Views (continued) FIGURE 4 -5: Access query design of the Housewares view with changed](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-9.jpg)
Views (continued) FIGURE 4 -5: Access query design of the Housewares view with changed field names 9
![Views (continued) • A view can join two or more tables • Advantages of Views (continued) • A view can join two or more tables • Advantages of](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-10.jpg)
Views (continued) • A view can join two or more tables • Advantages of views – Data independence – Each user has his or her own view – View should contain only fields required by the user • Greatly simplifies user’s perception of database • Security 10
![Indexes • Conceptually similar to book index • Increase data retrieval efficiency • Record Indexes • Conceptually similar to book index • Increase data retrieval efficiency • Record](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-11.jpg)
Indexes • Conceptually similar to book index • Increase data retrieval efficiency • Record numbers automatically assigned and used by DBMS • Index key: field or combination of fields on which index is built • Advantages – Makes some data retrieval more efficient 11
![Indexes (continued) FIGURE 4 -10: Customer table with record numbers 12 Indexes (continued) FIGURE 4 -10: Customer table with record numbers 12](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-12.jpg)
Indexes (continued) FIGURE 4 -10: Customer table with record numbers 12
![Indexes (continued) FIGURE 4 -11: Index for the Customer table on the Customer. Num Indexes (continued) FIGURE 4 -11: Index for the Customer table on the Customer. Num](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-13.jpg)
Indexes (continued) FIGURE 4 -11: Index for the Customer table on the Customer. Num field 13
![Indexes (continued) • Disadvantages – Occupies space on disk – DBMS must update index Indexes (continued) • Disadvantages – Occupies space on disk – DBMS must update index](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-14.jpg)
Indexes (continued) • Disadvantages – Occupies space on disk – DBMS must update index whenever corresponding data are updated • Create an index on a field (or fields) when: – – Field is the primary key of the table Field is the foreign key in a relationship Field will be frequently used as a sort field Need to frequently locate a record based on a value in this field 14
![Indexes (continued) • SQL command to create an index: CREATE INDEX Customer. Name ON Indexes (continued) • SQL command to create an index: CREATE INDEX Customer. Name ON](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-15.jpg)
Indexes (continued) • SQL command to create an index: CREATE INDEX Customer. Name ON Customer (Customer. Name) ; • Single-field index – Key is a single field – Also called a single-column index • Multiple-field index – More than one key field – Also called a multiple-column index 15
![Indexes (continued) FIGURE 4 -13: Creating an index on a single field in Access Indexes (continued) FIGURE 4 -13: Creating an index on a single field in Access](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-16.jpg)
Indexes (continued) FIGURE 4 -13: Creating an index on a single field in Access 16
![Indexes (continued) FIGURE 4 -14: Creating a multiple-field index in Access 17 Indexes (continued) FIGURE 4 -14: Creating a multiple-field index in Access 17](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-17.jpg)
Indexes (continued) FIGURE 4 -14: Creating a multiple-field index in Access 17
![Security • Prevention of unauthorized access to database • Database administrator determines types of Security • Prevention of unauthorized access to database • Database administrator determines types of](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-18.jpg)
Security • Prevention of unauthorized access to database • Database administrator determines types of access various users can have • SQL security mechanisms – GRANT: provides privileges to users GRANT SELECT ON Customer TO Jones ; – REVOKE: removes privileges from users REVOKE SELECT ON Customer FROM Jones ; 18
![Integrity Rules • Two integrity rules must be enforced by a relational DBMS – Integrity Rules • Two integrity rules must be enforced by a relational DBMS –](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-19.jpg)
Integrity Rules • Two integrity rules must be enforced by a relational DBMS – Integrity rules defined by Dr. E. F. Codd – Entity integrity – Referential integrity 19
![Entity Integrity • No field that is part of primary key may accept null Entity Integrity • No field that is part of primary key may accept null](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-20.jpg)
Entity Integrity • No field that is part of primary key may accept null values • To specify primary key in SQL: – Enter a PRIMARY KEY clause in either an ALTER TABLE or a CREATE TABLE command • To designate primary key in Access: – Select primary key field in Table Design view – Click the Primary Key button in the Tools group on the Table Tools Design tab 20
![Entity Integrity (continued) • SQL command to specify a primary key: PRIMARY KEY (Customer. Entity Integrity (continued) • SQL command to specify a primary key: PRIMARY KEY (Customer.](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-21.jpg)
Entity Integrity (continued) • SQL command to specify a primary key: PRIMARY KEY (Customer. Num) FIGURE 4 -15: Specifying a primary key in Access 21
![Entity Integrity (continued) • SQL command when more than one field included: PRIMARY KEY Entity Integrity (continued) • SQL command when more than one field included: PRIMARY KEY](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-22.jpg)
Entity Integrity (continued) • SQL command when more than one field included: PRIMARY KEY (Order. Num, Part. Num) FIGURE 4 -16: Specifying a primary key consisting of more than one field in Access 22
![Referential Integrity • Foreign key: field(s) whose value is required to match the value Referential Integrity • Foreign key: field(s) whose value is required to match the value](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-23.jpg)
Referential Integrity • Foreign key: field(s) whose value is required to match the value of the primary key for a second table • Referential integrity: if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must match the value of the primary key for some row in table B or be null • To specify referential integrity in SQL: – FOREIGN KEY clause in either the CREATE TABLE or ALTER TABLE commands 23
![Referential Integrity (continued) • To specify a foreign key, must specify both: – Field Referential Integrity (continued) • To specify a foreign key, must specify both: – Field](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-24.jpg)
Referential Integrity (continued) • To specify a foreign key, must specify both: – Field that is a foreign key – Table whose primary key the field is to match • Example: FOREIGN KEY (Rep. Num) REFERENCES Rep • In Access, specify referential integrity while defining relationships 24
![Referential Integrity (continued) FIGURE 4 -18: Specifying referential integrity in Access 25 Referential Integrity (continued) FIGURE 4 -18: Specifying referential integrity in Access 25](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-25.jpg)
Referential Integrity (continued) FIGURE 4 -18: Specifying referential integrity in Access 25
![Referential Integrity (continued) FIGURE 4 -19: Referential integrity violation when attempting to add a Referential Integrity (continued) FIGURE 4 -19: Referential integrity violation when attempting to add a](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-26.jpg)
Referential Integrity (continued) FIGURE 4 -19: Referential integrity violation when attempting to add a record 26
![Legal-Values Integrity • Legal values: set of values allowable in a field • Legal-values Legal-Values Integrity • Legal values: set of values allowable in a field • Legal-values](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-27.jpg)
Legal-Values Integrity • Legal values: set of values allowable in a field • Legal-values integrity: no record can exist with a value in the field other than one of the legal values • SQL – CHECK clause enforces legal-values integrity – Example: CHECK (Credit. Limit IN (5000, 7500, 10000, 15000)) 27
![Legal-Values Integrity (continued) • Access – Validation rule: must be followed by data entered Legal-Values Integrity (continued) • Access – Validation rule: must be followed by data entered](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-28.jpg)
Legal-Values Integrity (continued) • Access – Validation rule: must be followed by data entered – Validation text: informs user of the reason for rejection of data that violates the rule 28
![Legal-Values Integrity (continued) FIGURE 4 -21: Specifying a validation rule in Access 29 Legal-Values Integrity (continued) FIGURE 4 -21: Specifying a validation rule in Access 29](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-29.jpg)
Legal-Values Integrity (continued) FIGURE 4 -21: Specifying a validation rule in Access 29
![Structure Changes • Examples of changes to database structure – Adding and removing tables Structure Changes • Examples of changes to database structure – Adding and removing tables](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-30.jpg)
Structure Changes • Examples of changes to database structure – Adding and removing tables and fields – Changing characteristics of existing fields – Creating and dropping indexes • SQL ALTER TABLE command changes table’s structure • To add a new field to the Customer table: ALTER TABLE Customer ADD Cust. Type CHAR(1) ; 30
![Structure Changes (continued) FIGURE 4 -22: Adding a field in Access 31 Structure Changes (continued) FIGURE 4 -22: Adding a field in Access 31](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-31.jpg)
Structure Changes (continued) FIGURE 4 -22: Adding a field in Access 31
![Structure Changes (continued) • Changing properties of existing fields ALTER TABLE Customer CHANGE COLUMN Structure Changes (continued) • Changing properties of existing fields ALTER TABLE Customer CHANGE COLUMN](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-32.jpg)
Structure Changes (continued) • Changing properties of existing fields ALTER TABLE Customer CHANGE COLUMN Customer. Name TO CHAR(40) ; • Deleting a field from a table ALTER TABLE Part DELETE Warehouse ; • DROP TABLE command deletes a table DROP TABLE Small. Cust ; 32
![Structure Changes (continued) FIGURE 4 -23: Changing a field property in Access 33 Structure Changes (continued) FIGURE 4 -23: Changing a field property in Access 33](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-33.jpg)
Structure Changes (continued) FIGURE 4 -23: Changing a field property in Access 33
![Structure Changes (continued) FIGURE 4 -24: Dialog box that opens when a field in Structure Changes (continued) FIGURE 4 -24: Dialog box that opens when a field in](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-34.jpg)
Structure Changes (continued) FIGURE 4 -24: Dialog box that opens when a field in Access is deleted 34
![Structure Changes (continued) FIGURE 4 -25: Deleting a table in Access 35 Structure Changes (continued) FIGURE 4 -25: Deleting a table in Access 35](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-35.jpg)
Structure Changes (continued) FIGURE 4 -25: Deleting a table in Access 35
![Making Complex Changes • Some changes might not be allowed by your DBMS • Making Complex Changes • Some changes might not be allowed by your DBMS •](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-36.jpg)
Making Complex Changes • Some changes might not be allowed by your DBMS • In these situations, you can: – Use CREATE TABLE command to describe the new table – Insert values into it using INSERT command combined with a SELECT clause • SELECT INTO command can create the new table in a single operation 36
![System Catalog • System catalog (or catalog) – Contains information about tables in the System Catalog • System catalog (or catalog) – Contains information about tables in the](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-37.jpg)
System Catalog • System catalog (or catalog) – Contains information about tables in the database – Maintained automatically 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 37
![System Catalog (continued) • Other possible tables – Sysindexes: information about indexes – Sysviews: System Catalog (continued) • Other possible tables – Sysindexes: information about indexes – Sysviews:](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-38.jpg)
System Catalog (continued) • Other possible tables – Sysindexes: information about indexes – Sysviews: information about views • Catalog can be used to determine information about the structure of the database • Documenter: allows user to print detailed documentation about any table, query, report, form, or other object in the database • My. SQL uses SHOW TABLES, SHOW INDEXES, and SHOW COLUMNS commands 38
![Stored Procedures • Client/server system – Database resides on a computer called the server Stored Procedures • Client/server system – Database resides on a computer called the server](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-39.jpg)
Stored Procedures • Client/server system – Database resides on a computer called the server – Users access database through clients • Client – Computer connected to a network – Has access through server to the database 39
![Stored Procedures (continued) • Stored procedure – – Special file used to store a Stored Procedures (continued) • Stored procedure – – Special file used to store a](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-40.jpg)
Stored Procedures (continued) • Stored procedure – – Special file used to store a query that is run often Placed on the server Improves overall performance Convenience 40
![Stored Procedures (continued) • My. SQL – Delimiter: semicolon at the end of a Stored Procedures (continued) • My. SQL – Delimiter: semicolon at the end of a](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-41.jpg)
Stored Procedures (continued) • My. SQL – Delimiter: semicolon at the end of a My. SQL command – Need to temporarily change the delimiter for a stored procedure – To use a stored procedure: CALL followed by the procedure name • Access does not support stored procedures – Use a parameter query instead 41
![Triggers • Action that occurs automatically in response to an associated database operation such Triggers • Action that occurs automatically in response to an associated database operation such](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-42.jpg)
Triggers • Action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command • Stored and compiled on the server • Need to temporarily change the delimiter • Access does not support triggers – Access 2010 has data macros that have similar functionality 42
![Data Macros in Access 2010 Figure 4 -29: Macro Designer window for the After Data Macros in Access 2010 Figure 4 -29: Macro Designer window for the After](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-43.jpg)
Data Macros in Access 2010 Figure 4 -29: Macro Designer window for the After Insert event associated with the Order. Line table 43
![Summary • Views give each user his or her own view of the data Summary • Views give each user his or her own view of the data](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-44.jpg)
Summary • Views give each user his or her own view of the data in a database • Indexes facilitate data retrieval from the database • Security is provided in SQL systems using the GRANT and REVOKE commands • Entity integrity: no field that is part of the primary key can accept null values • Referential integrity: value in any foreign key field must be null or must match an actual value in the primary key field of another table 44
![Summary (continued) • Legal-values integrity: value entered in a field must be one of Summary (continued) • Legal-values integrity: value entered in a field must be one of](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-45.jpg)
Summary (continued) • Legal-values integrity: value entered in a field must be one of the legal values that satisfies some particular condition • ALTER TABLE command allows you to add fields to a table, delete fields, or change the characteristics of fields • In Access, change the structure of a table by making the changes in the table design • DROP TABLE command lets you delete a table from a database 45
![Summary (continued) • In Access, delete a table by selecting the Delete command on Summary (continued) • In Access, delete a table by selecting the Delete command on](http://slidetodoc.com/presentation_image/96b9f3f9cb8d83d7e4f2d5947863efce/image-46.jpg)
Summary (continued) • In Access, delete a table by selecting the Delete command on the table’s shortcut menu in the Navigation Pane • System catalog stores information about the structure of a database • Stored procedure: query saved in a file that users can execute later • Trigger: action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE • Data macros: Access 2010 equivalent of triggers 46
- Slides: 46