Concepts of Database Management, Fifth Edition

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

3
Views

• Application programs or individual users
  picture of the database
• Less involved than full database
• Offers simplification
• Provides measure of security
• Sensitive tables or columns omitted where not
  appropriate

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

5
Figure 4.1 SQL to Create View
CREATE VIEW Housewares AS SELECT PartNum,
Description, OnHand, Price FROM Part WHERE
ClassHW
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

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 OnHandlt 25
Actually executes as
SELECT PartNum, Description, OnHand, Price FROM
Part WHERE ClassHW AND OnHandlt 25
8
Figures 4.3 - 4.4 Access Query Design of View
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.RepNumCustomer.RepNum
10
Figures 4.5-4.6 Access Query Design of View
with Changed Field Names
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

12
Advantages of Views

• Provides data independence
• Same data viewed by different users in different
  ways
• Contains only information required by a given
  user

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

14
Figure 4.10 Customer Table with Record Numbers
15
Figure 4.11 Customer Table Index on CustomerNum
16
Figure 4.12 Table Indexes on CreditLimit, RepNum
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

18
SQL to Create Index
CREATE INDEX CustomerName ON Customer
(CustomerName)
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

20
SQL to Delete Index
DROP INDEX RepBal
21
Figure 4.13 Index on Single Field in Access
22
Figure 4.14 Index on Multiple Fields in Access
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
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

25
Integrity Rules (cont)

• 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

26
Figure 4.15 Primary Key in Access
PRIMARY KEY (CustomerNum)
27
Figure 4.16 Multi-Field Primary Key in Access
PRIMARY KEY (OrderNum, PartNum)
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

29
Figure 4.17 Relationships Window to Relate
Tables in Access
FOREIGN KEY (RepNum) REFERENCES Rep
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

31
Figure 4.18 Specifying Referential Integrity
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

33
Figure 4.19 Violating Referential Integrity on
Adding
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))

35
Validation Rule in Access
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

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)
38
Figure 4.22 Add Field in Access
39
Figure 4.23 Change Field Characteristic in Access
40
Structure Changes - Delete
Deleting field
ALTER TABLE Part DELETE Warehouse
Delete SQL Table
DROP TABLE SmallCust
41
Figure 4.24 Delete Field in Access
42
Figure 4.25 Delete Table in Access
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

44
System Catalog (cont.)

• Other possible tables
• Sysindexes information about the indexes that
  are defined on these tables
• Sysviews information about the views that have
  been created

45
Figure 4.26 Systables Table
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

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

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