The Relational Model 3:

1
Chapter 4

• The Relational Model 3
• Advanced Topics

Database Management
2
Objectives

• Discuss views what they are, how they are
  described, and how they are used.
• Discuss the use of indexes for improving
  performance.
• Examine the security features of a DBMS.
• Explain entity and referential integrity.

3
Objectives

• Discuss the manner in which the structure of a
  relational database can be changed.
• Define the catalog and explain its use.
• Discuss the integrity support within SQL.

4
Views

• Snapshot of certain data in the database at a
  given moment in time
• Usually much less involved than the full
  database, offers simplification.
• Provides measure of security, since sensitive
  tables or columns can be omitted.

5
Figure 4.1 Housewares View
6
Figure 4.2 Premiere Products Sample Data
7
Figure 4.3 Sales Cust View
8
Advantages of Views

• Provides data independence.
• The same data can be viewed by different users in
  different ways.
• Contains only those columns required by a given
  user.
• Simplifies the users perception of the DBMS
• Provides a measure of security by omitting
  sensitive columns and tables

9
Indexes

• Conceptually the same as an index in a book.
• Record numbers are automatically assigned and
  used by the DBMS.

10
Figure 4.4 Customer Table with Record Numbers
11
Figure 4.5 Index for Customer Table on Customer
Number Column
12
Figure 4.6 Indexes for Customer Table on Credit
Limit and Sale Rep Number Columns
13
Advantages and Disadvantages of Indexes

• Can be added or dropped at will.
• Makes certain types of retrieval more efficient.
• Occupies space that can be used for something
  else.
• The DBMS must update the index whenever
  corresponding data in the database are updated.

14
Figure 4.7 Indexing Options
15
Security

• The prevention of unauthorized access to the
  database.
• In SQL, there are two security mechanisms
• GRANT to grant privileges to certain users.
• REVOKE to later revoke those privileges.

16
Integrity Rules

• Entity integrity is the rule that no column that
  is part of the primary key may accept null
  values.
• Referential integrity states that if table A
  contains a foreign key that matches the primary
  key of table B, then values of this foreign key
  either must match the value of the primary key
  for the same row in table B or must be null.

17
Figure 4.8 Defining Explicit Relationships
Between Tables Using Primary and Foreign Keys
18
Figure 4.9 Enforcing Referential Integrity
19
Figure 4.10 Referential Integrity Violation
Warning When Adding Data
20
Figure 4.11 Referential Integrity Violation
Warning When Deleting Data
21
Changing the Structure of a Relational Database

• ALTER TABLE
• Changes a tables structure in SQL.
• DROP TABLE
• Deletes a table no longer needed in SQL.

22
The Catalog

• Information about tables in the database is kept
  in the system catalog.

23
Figure 4.13 Syscolumns Table
24
Integrity in SQL

• Integrity Enhancement Feature (IEF) provides
  three types of integrity support
• Legal values. CHECK ensures that only values
  that satisfy a particular condition are allowed.
• Primary keys. PRIMARY KEY is used to specify the
  primary key for a table.
• Foreign keys. FOREIGN KEY specifies the foreign
  key and its corresponding primary key.