concepts of database management, fifth edition chapter 4: the relational model 3: advanced topics
TRANSCRIPT
![Page 1: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/1.jpg)
Concepts of Database Concepts of Database Management, Fifth Management, Fifth
EditionEdition
Chapter 4: Chapter 4:
The Relational Model 3:The Relational Model 3:
Advanced Topics Advanced Topics
![Page 2: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/2.jpg)
2Concepts of Database Management, 5th Edition
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
![Page 3: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/3.jpg)
3Concepts of Database Management, 5th Edition
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
![Page 4: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/4.jpg)
4Concepts of Database Management, 5th Edition
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
![Page 5: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/5.jpg)
5Concepts of Database Management, 5th Edition
Figure 4.1: SQL to Create View
CREATE VIEW Housewares ASSELECT PartNum, Description, OnHand, PriceFROM PartWHERE Class=‘HW’;
![Page 6: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/6.jpg)
6Concepts of Database Management, 5th Edition
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
![Page 7: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/7.jpg)
7Concepts of Database Management, 5th Edition
Query on a View Selects data only from Tables created in the
view
Query is merged with query used to create view
SELECT *FROM HousewaresWHERE OnHand< 25;
SELECT PartNum, Description, OnHand, PriceFROM PartWHERE Class=‘HW’AND OnHand< 25;
Actually executes as
![Page 8: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/8.jpg)
8Concepts of Database Management, 5th Edition
Figures 4.3 - 4.4: Access Query Design of View
![Page 9: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/9.jpg)
9Concepts of Database Management, 5th Edition
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) ASSELECT Rep.RepNum, LastName, FirstName, CustomerNum, CustomerNameFROM Rep, CustomerWHERE Rep.RepNum=Customer.RepNum;
![Page 10: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/10.jpg)
10Concepts of Database Management, 5th Edition
Figures 4.5-4.6: Access Query Design of View with Changed Field Names
![Page 11: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/11.jpg)
11Concepts of Database Management, 5th Edition
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
![Page 12: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/12.jpg)
12Concepts of Database Management, 5th Edition
Advantages of Views
Provides data independence
Same data viewed by different users in different ways
Contains only information required by a given user
![Page 13: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/13.jpg)
13Concepts of Database Management, 5th Edition
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
![Page 14: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/14.jpg)
14Concepts of Database Management, 5th Edition
Figure 4.10: Customer Table with Record Numbers
![Page 15: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/15.jpg)
15Concepts of Database Management, 5th Edition
Figure 4.11: Customer Table Index on CustomerNum
![Page 16: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/16.jpg)
16Concepts of Database Management, 5th Edition
Figure 4.12: Table Indexes on CreditLimit, RepNum
![Page 17: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/17.jpg)
17Concepts of Database Management, 5th Edition
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
![Page 18: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/18.jpg)
18Concepts of Database Management, 5th Edition
SQL to Create Index
CREATE INDEX CustomerNameON Customer (CustomerName);
![Page 19: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/19.jpg)
19Concepts of Database Management, 5th Edition
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
![Page 20: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/20.jpg)
20Concepts of Database Management, 5th Edition
SQL to Delete Index
DROP INDEX RepBal;
![Page 21: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/21.jpg)
21Concepts of Database Management, 5th Edition
Figure 4.13: Index on Single Field in Access
![Page 22: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/22.jpg)
22Concepts of Database Management, 5th Edition
Figure 4.14: Index on Multiple Fields in Access
![Page 23: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/23.jpg)
23Concepts of Database Management, 5th Edition
Security
Prevention of unauthorized access to database
Two SQL security mechanisms
GRANT provides privileges to users
REVOKE removes privileges from usersGRANT SELECT ON Customer TO JONES;
REVOKE SELECT ON Customer FROM JONES;
![Page 24: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/24.jpg)
24Concepts of Database Management, 5th Edition
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
![Page 25: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/25.jpg)
25Concepts of Database Management, 5th Edition
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
![Page 26: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/26.jpg)
26Concepts of Database Management, 5th Edition
Figure 4.15: Primary Key in Access
PRIMARY KEY (CustomerNum)
![Page 27: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/27.jpg)
27Concepts of Database Management, 5th Edition
Figure 4.16: Multi-Field Primary Key in Access
PRIMARY KEY (OrderNum, PartNum)
![Page 28: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/28.jpg)
28Concepts of Database Management, 5th Edition
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
![Page 29: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/29.jpg)
29Concepts of Database Management, 5th Edition
Figure 4.17: Relationships Window to Relate Tables in Access
FOREIGN KEY (RepNum) REFERENCES Rep
![Page 30: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/30.jpg)
30Concepts of Database Management, 5th Edition
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
![Page 31: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/31.jpg)
31Concepts of Database Management, 5th Edition
Figure 4.18: Specifying Referential Integrity
![Page 32: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/32.jpg)
32Concepts of Database Management, 5th Edition
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
![Page 33: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/33.jpg)
33Concepts of Database Management, 5th Edition
Figure 4.19: Violating Referential Integrity on Adding
![Page 34: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/34.jpg)
34Concepts of Database Management, 5th Edition
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)) ;
![Page 35: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/35.jpg)
35Concepts of Database Management, 5th Edition
Validation Rule in Access
![Page 36: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/36.jpg)
36Concepts of Database Management, 5th Edition
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
![Page 37: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/37.jpg)
37Concepts of Database Management, 5th Edition
Structure Changes – Add and Change
ALTER TABLE CustomerADD CustType CHAR(1);
Adding new field
Changing field properties
ALTER TABLE CustomerCHANGE COLUMN CustomerName TO CHAR(50);
![Page 38: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/38.jpg)
38Concepts of Database Management, 5th Edition
Figure 4.22: Add Field in Access
![Page 39: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/39.jpg)
39Concepts of Database Management, 5th Edition
Figure 4.23: Change Field Characteristic in Access
![Page 40: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/40.jpg)
40Concepts of Database Management, 5th Edition
Structure Changes - Delete
ALTER TABLE PartDELETE Warehouse;
Deleting field
Delete SQL Table
DROP TABLE SmallCust;
![Page 41: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/41.jpg)
41Concepts of Database Management, 5th Edition
Figure 4.24: Delete Field in Access
![Page 42: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/42.jpg)
42Concepts of Database Management, 5th Edition
Figure 4.25: Delete Table in Access
![Page 43: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/43.jpg)
43Concepts of Database Management, 5th Edition
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
![Page 44: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/44.jpg)
44Concepts of Database Management, 5th Edition
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
![Page 45: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/45.jpg)
45Concepts of Database Management, 5th Edition
Figure 4.26: Systables Table
![Page 46: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/46.jpg)
46Concepts of Database Management, 5th Edition
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
![Page 47: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/47.jpg)
47Concepts of Database Management, 5th Edition
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
![Page 48: Concepts of Database Management, Fifth Edition Chapter 4: The Relational Model 3: Advanced Topics](https://reader030.vdocuments.us/reader030/viewer/2022032708/56649e665503460f94b606b0/html5/thumbnails/48.jpg)
48Concepts of Database Management, 5th Edition
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