Fall Semester 2002

• 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.
• Make changes to the structure of a relational database.
• Define and use the system catalog.

Key Terms

• cascade delete
• cascade update
• catalog
• defining query
• Documenter
• entity integrity
• foreign key
• index
• index key
• legal-values integrity
• multiple-column index
• multiple-field index
• null
• referential integrity
• row-and-column subset view
• security
• single-column index
• single-field index
• Syscolumns
• Sysindexes
• Systables
• system catalog
• Sysviews
• validation rule
• validation text
• view

Chapter Outline

I.	Introduction
II.	Views
III.	Indexes
IV.	Security
V.	Integrity Rules
	A. Entity Integrity
	B. Referential Integrity
	C. Legal-Values Integrity
VI.	Structure Changes
	A. Making Complex Changes
VII.	System Catalog
VIII.	Summary
IX.	Key Terms
X.	Review Questions
XI.	Premiere Products Exercises
XII.	Henry Books Case

Views

A view is an application program's or an individual user's picture of the database that can be used to create reports and charts as well as other database objects. Because a view is usually much less involved than the full database, its use can represent a great simplification. Views have several advantages:

1. They provide data independence.
2. Different users can view the same data in different ways.
3. A view can be used to limit the data a particular user is able to see, thus providing a measure of security. Also, the users' perception of the database is simplified given that a view, in most cases, will contain fewer columns and or fewer rows than the actual database.

In SQL, use the CREATE VIEW command to create a view.

Indexes

Within relational model systems on both mainframes and personal computers, the main mechanism for increasing the efficiency with which data are retrieved from the database is the index. Conceptually, a database index is similar to the index in a book. Once an index has been created, it can be used to facilitate data retrieval. Indexes can be added and dropped as necessary. There are two disadvantages associated with indexes:

1. Indexes occupy space on disk.
2. Indexes must be updated whenever corresponding data in the database is updated.

In SQL, use the CREATE INDEX command to create an index and the DROP INDEX command to remove an index.

Security

Security is the prevention of unauthorized access to the database. Within an organization, the database administrator determines the types of access various users can have to the database. In SQL, use the GRANT statement to provide access to the database and the REVOKE statement to cancel authorization.

Integrity Rules

A relational database must enforce two important integrity rules: entity integrity and referential integrity. The entity integrity rule states that no column that is part of the primary key can accept null values. The referential integrity rule 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 some row in table B or must be null. A foreign key is a column (or collection of columns) in one table whose value is required to match the value of the primary key for another table. A third type of integrity is legal-values integrity. Legal-values integrity is the property that states that no record can exist in the database with a value in the field other than one of the legal values. Legal values are a particular set of values that are allowed for a field.

Structure Changes

You can change a database structure by adding and removing tables and fields, by changing the characteristics of existing fields, or by creating and dropping indexes. To accomplish these tasks in SQL, use the ALTER TABLE and DROP TABLE commands. If the changes are more complex and beyond the capabilities of the DBMS, you can use the CREATE TABLE command to describe a new table, and then insert values using the INSERT command combined with an appropriate SELECT clause.

System Catalog

The system catalog stores information about the tables in the database. Systables contains the Name, Creator, and ColCount columns that identify the table name, table creator, and number of columns contained in the table, respectively. Syscolumns contains the ColName, TbName, and ColType columns. These columns identify the field name, the table in which the field is found, and the data type for the column, respectively. Other tables in a catalog include Sysindexes and Sysviews. The same SQL syntax used to query the database is used to retrieve information from the catalog.

1. Which SQL command allows a database administrator to provide access to a database? ANSWER: GRANT (highlight to see the answer)

2. What is the rule called that states that no field that is part of the primary key may accept null values?
   ANSWER: entity integrity (highlight to see the answer)

3. What term is used to describe a field (or collection of fields) in a table whose value must match the value of the primary key for another table?
   ANSWER: foreign key (highlight to see the answer)

   Some important points:

   • A view does not exist as a separate table.
   • There is a difference between sorting and indexing. Sorting means that records are physically rearranged in a different order. For large databases, this can be a very time-consuming process.
   • Access automatically creates an index on the primary key of a table.
   • A null value represents an unknown value, and not a value of zero.
   • Primary key, foreign key, entity integrity, and referential integrity are important concepts. Relate these concepts to the concept of redundancy that was discussed in Chapter 1. Using more than one table in a database eliminates redundancy but there needs to be some way to link the tables and prevent errors.
   • The use of legal values is very useful when there are a limited number of values available for a field.
   • There are various reasons why the structure of a database might need to be changed. For example, the size of the zip code field may need to be increased to allow for a nine-digit zip code, or we might need to store more attributes (features) for the entities involved.

Note that Chapters 1, 2, 3, and 4 are what we need for the midterm exam. Special emphasis is on the topics of 2 and 3 (since they are the more operational). Chapter 4 brings in some advanced topics, some of which might be useful on the midterm (for example, creating views). Chapter 1 is extremely general, and a good introduction to Chapters 5 and 6 (coming next) and (at the same time) a good overall review of the concepts covered thus far. As a matter of fact, we can safely say that, thus far, we have been more concerned with data manipulation (given a structure for the database, and some data.) But we also have an idea of how a database should be designed. That is described in our Entity Relationship Recipe. With Chapters 5 and 6 we're going to deepen our understanding of the principles of good database design.