Concepts of Database Management
Sixth 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 DBMS
Discuss entity, referential, and legal-values integrity
Concepts of Database Management
Objectives (continued)
Make changes to the structure of a relational
database
Define and use the system catalog
Discuss stored procedures and triggers
Concepts of Database Management
Views
View: application programs or individual users
picture of the database
Less involved than full database
Simplification
Security
Concepts of Database Management
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
Concepts of Database Management
Views (continued)
CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class='HW'
;
FIGURE 4-1: Housewares view
Concepts of Database Management
Views (continued)
FIGURE 4-2: Housewares view of the Premiere Products database
Concepts of Database Management
Execution By DBMS
SELECT *
FROM Housewares
WHERE OnHand<25;
What DBMS really execute:
SELECT PartNum, Description, OnHand,
Price
FROM Part
WHERE Class=HW
AND OnHand<25;
Concepts of Database Management
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
Concepts of Database Management
Views (continued)
SQL command to create Housewares view:
CREATE VIEW Housewares (Pnum, Pdesc, OnHd,
Price) AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class='HW'
;
Concepts of Database Management
10
Views (continued)
FIGURE 4-3: Access query design of the Housewares view
Concepts of Database Management
11
Views (continued)
FIGURE 4-5: Access query design of the Housewares view with changed field names
Concepts of Database Management
12
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 users perception of database
Security
Concepts of Database Management
13
Views (continued)
SQL command to create VSalesCust view from more than
one table:
CREATE VIEW VSalesCust (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
14
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
Concepts of Database Management
15
Indexes (continued)
FIGURE 4-10: Customer table with record numbers
Concepts of Database Management
16
Indexes (continued)
FIGURE 4-11: Index for the Customer table on the CustomerNum field
Concepts of Database Management
17
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
Concepts of Database Management
18
Indexes (continued)
SQL command to create an index:
CREATE INDEX ind_CustomerName
ON Customer (CustomerName)
;
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
Concepts of Database Management
19
Indexes (continued)
FIGURE 4-13: Creating an index on a single field in Access
Concepts of Database Management
20
10
Indexes (continued)
FIGURE 4-14: Creating a multiple-field index in Access
Concepts of Database Management
21
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
;
Concepts of Database Management
22
11
Integrity Rules
Two integrity rules must be enforced by a relational
DBMS
Integrity rules defined by Dr. E. F. Codd
Entity integrity
Referential integrity
Concepts of Database Management
23
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
Concepts of Database Management
24
12
Entity Integrity (continued)
SQL command to specify a primary key:
PRIMARY KEY (CustomerNum)
FIGURE 4-15: Specifying a primary key in Access
Concepts of Database Management
25
Entity Integrity (continued)
SQL command when more than one field included:
PRIMARY KEY (OrderNum, PartNum)
FIGURE 4-16: Specifying a primary key consisting of more than one field in
Access
Concepts of Database Management
26
13
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
Concepts of Database Management
27
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 (RepNum) REFERENCES Rep
In Access, specify referential integrity while
defining relationships
Concepts of Database Management
28
14
Referential Integrity (continued)
FIGURE 4-18: Specifying referential integrity in Access
Concepts of Database Management
29
Referential Integrity (continued)
FIGURE 4-19: Referential integrity violation when attempting to add a record
Concepts of Database Management
30
15
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 (CreditLimit IN (5000, 7500, 10000, 15000))
Concepts of Database Management
31
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
Concepts of Database Management
32
16
Legal-Values Integrity (continued)
FIGURE 4-21: Specifying a validation rule in Access
Concepts of Database Management
33
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 tables
structure
To add a new field to the Customer table:
ALTER TABLE Customer
ADD CustType CHAR(1)
;
Concepts of Database Management
34
17
Structure Changes (continued)
FIGURE 4-22: Adding a field in Access
Concepts of Database Management
35
Structure Changes (continued)
Changing properties of existing fields
ALTER TABLE Customer
Modify CustomerName CHAR(40)
;
Deleting a field from a table
ALTER TABLE Part
DROP [COLUMN] Warehouse
;
DROP TABLE command deletes a table
DROP TABLE SmallCust
;
Concepts of Database Management
36
18
Structure Changes (continued)
FIGURE 4-23: Changing a field property in Access
Concepts of Database Management
37
Structure Changes (continued)
FIGURE 4-24: Dialog box that opens when a field in Access is deleted
Concepts of Database Management
38
19
Structure Changes (continued)
FIGURE 4-25: Deleting a table in Access
Concepts of Database Management
39
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
Concepts of Database Management
40
20
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
Concepts of Database Management
41
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
Concepts of Database Management
42
21
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
Concepts of Database Management
43
Stored Procedures (continued)
Stored procedure
Special file used to store a query that is run often
Placed on the server
Improves overall performance
Convenience
Concepts of Database Management
44
22
Stored Procedures (continued)
MySQL
Delimiter: semicolon at the end of a MySQL
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
Concepts of Database Management
45
Stored Procedures (continued)
DELIMITER $$
CREATE PROCEDURES Change_Credit
(CNum CHAR(4), CLimit DECEMAL(8,2))
BEGIN
UPDATE Customer
SET CreditLimit=CLimit
WHERE CustomerNum=CNum;
END
$$
DELIMITER ;
To use a stored procedure:
CALL Change_Credit(356,10000);
Concepts of Database Management
46
23
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
Concepts of Database Management
47
Trigger Using INSERT Operation
DELIMITER $$
CREATE TRIGGER AddOrderLine
AFTER INSERT ON OrderLine
FOR EACH ROW
BEGIN
UPDATE Part
SET OnOrder=OnOrder+New.NumOrdered
WHERE PartNum=New.PartNum;
END
$$
DELIMITER ;
Concepts of Database Management
48
24
Trigger Using UPDATE Operation
DELIMITER $$
CREATE TRIGGER UpdateOrderLine
AFTER UPDATE ON OrderLine
FOR EACH ROW
BEGIN
UPDATE Part
SET OnOrder=OnOrder+New.NumOrdered-Old.NumOrdered
WHERE Part.PartNum=New.PartNum;
END
$$
DELIMITER ;
Concepts of Database Management
49
Trigger Using DELETE Operation
DELIMITER $$
CREATE TRIGGER DeleteOrderLine
AFTER DELETE ON OrderLine
FOR EACH ROW
BEGIN
UPDATE Part
SET OnOrder=OnOrder-Old.NumOrdered
WHERE PartNum=Old.PartNum;
END
$$
DELIMITER ;
Concepts of Database Management
50
25
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
Concepts of Database Management
51
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
Concepts of Database Management
52
26
Summary (continued)
In Access, delete a table by selecting the Delete
command on the tables 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
Concepts of Database Management
53
27
