Transcript chapter04

Concepts of Database
Management, Fifth Edition
Chapter 4:
The Relational Model 3:
Advanced Topics
Objectives
 Define,
 Use
describe, and use views
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
2
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
3
SELECT Command
 Called
the defining query
 Indicates
 Query
precisely what to include in the view
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
4
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
5
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
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
Actually executes as
WHERE OnHand< 25
;
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class=‘HW’
AND OnHand< 25
;
Concepts of Database Management, 5th Edition
7
Figures 4.3 - 4.4: Access Query Design of View
Concepts of Database Management, 5th Edition
8
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
9
Figures 4.5-4.6: Access Query Design of View
with Changed Field Names
Concepts of Database Management, 5th Edition
10
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
11
Advantages of Views
 Provides
 Same
data independence
data viewed by different users in different
ways
 Contains
only information required by a given
user
Concepts of Database Management, 5th Edition
12
Indexes
 Conceptually
 Increases
similar to book index
data retrieval efficiency
 Automatically
 Used
assigns record numbers
by DBMS, not by users
 Fields
on which index built called Index Key
Concepts of Database Management, 5th Edition
13
Figure 4.10: Customer Table with Record Numbers
Concepts of Database Management, 5th Edition
14
Figure 4.11: Customer Table Index on CustomerNum
Concepts of Database Management, 5th Edition
15
Figure 4.12:
Table Indexes on CreditLimit, RepNum
Concepts of Database Management, 5th Edition
16
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
17
SQL to Create Index
CREATE INDEX CustomerName
ON Customer (CustomerName)
;
Concepts of Database Management, 5th Edition
18
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
19
SQL to Delete Index
DROP INDEX RepBal
;
Concepts of Database Management, 5th Edition
20
Figure 4.13: Index on Single Field in Access
Concepts of Database Management, 5th Edition
21
Figure 4.14: Index on Multiple Fields in Access
Concepts of Database Management, 5th Edition
22
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
23
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
24
Integrity Rules (con’t)
 To
specify primary key, enter a PRIMARY KEY
clause in either an ALTER TABLE or a CREATE
TABLE command
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
 Foreign
Concepts of Database Management, 5th Edition
25
Figure 4.15: Primary Key in Access
PRIMARY KEY (CustomerNum)
Concepts of Database Management, 5th Edition
26
Figure 4.16: Multi-Field Primary Key in Access
PRIMARY KEY (OrderNum, PartNum)
Concepts of Database Management, 5th Edition
27
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
28
Figure 4.17: Relationships Window to
Relate Tables in Access
FOREIGN KEY (RepNum) REFERENCES Rep
Concepts of Database Management, 5th Edition
29
Cascade Delete and Update
 Cascade
delete - ensures that the deletion of a
master record deletes all records in sub tables
related to it
update – ensures that changes made to
the primary key of the master table are also made
in the related records
 Cascade
Concepts of Database Management, 5th Edition
30
Figure 4.18: Specifying Referential Integrity
Concepts of Database Management, 5th Edition
31
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
32
Figure 4.19: Violating Referential Integrity on Adding
Concepts of Database Management, 5th Edition
33
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)) ;
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
 Validation
Concepts of Database Management, 5th Edition
34
Validation Rule in Access
Concepts of Database Management, 5th Edition
35
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
36
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
37
Figure 4.22: Add Field in Access
Concepts of Database Management, 5th Edition
38
Figure 4.23: Change Field Characteristic in Access
Concepts of Database Management, 5th Edition
39
Structure Changes - Delete
Deleting field
ALTER TABLE Part
DELETE Warehouse
;
Delete SQL Table
DROP TABLE SmallCust
;
Concepts of Database Management, 5th Edition
40
Figure 4.24: Delete Field in Access
Concepts of Database Management, 5th Edition
41
Figure 4.25: Delete Table in Access
Concepts of Database Management, 5th Edition
42
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
43
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
44
Figure 4.26: Systables Table
Concepts of Database Management, 5th Edition
45
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
46
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
47
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
48