A Guide to SQL, Ninth Edition Page 7–1
Chapter 7
Database Administration
At a Glance
Instructor’s Manual Table of Contents
• Overview
• Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
A Guide to SQL, Ninth Edition Page 7–2
Lecture Notes
Overview
In this chapter, students learn about some special issues involved in database administration.
Students learn how the database administrator can give each user his or her own view of the
database. They see how to use the GRANT and REVOKE commands to assign different
database privileges to different users. They use indexes to improve performance. Students learn
how SQL stores information about the database structure in the system catalog and how to
access that information. Finally, students learn how to specify integrity constraints that
establish rules for the data in the database.
Chapter Objectives
In this chapter, students learn about:
• Understanding, creating, and dropping views
• Recognizing the benefits of using views
• Using a view to update data
• Granting and revoking users’ database privileges
• Understanding the purpose, advantages, and disadvantages of using an index
• Creating, using, and dropping an index
• Understanding and obtaining information from the system catalog
• Using integrity constraints to control data entry
Teaching Tips
Introduction
1. Database administration is the process of managing a database. The person or group
charged with managing the database is known as the database administrator.
Teaching
Tip
Because databases are shared resources, the database administrator acts as the
custodian of the data. Database administration actually involves many functions
such as setting data policies and procedures; resolving data conflicts; selecting
hardware and software; managing the data security, privacy, and integrity; and
data backup and recovery. Review these tasks with students before beginning
this chapter so that they understand how these SQL commands fit into the larger
picture.
You may want students to find ads for database administrators in the
A Guide to SQL, Ninth Edition Page 7–3
Creating and Using Views
1. Define view. A view is an application program’s or an individual user’s picture of the
database. Base tables are the existing, permanent tables in a relational database.
3. Views can simplify query tasks and can provide a measure of security.
5. To create a view, use the CREATE VIEW command, which includes the words
CREATE VIEW, followed by the name of the view, the word AS, and then a query.
7. Mention the Access User Note on page 192. Access does not support the CREATE
VIEW command. Instead, you create a query and save the query object in the database.
9. Use Figure 7-3 to illustrate that the view is a subset of the ITEM table.
10. Use Figure 7-4 to explain using a view in a query. When you create a query that uses a
12. Point out that this view is an example of a row-and-column subset view. It consists of
a subset of the rows and columns in some base table.
14. Use Example 3 and Figures 7-7 and 7-8 to illustrate creating a view that joins two
tables.
16. Use Example 4 and Figures 7-9 and 7-10 to illustrate creating a view that involves
statistics.
17. Point out that views provide several benefits. First, views provide data independence. If
19. The second benefit of views is that different users can view the same data in different
ways through his or her individual view.
20. The final benefit of using views is that a view can contain only those columns required
Teaching
Tip
Point out that views contain the most current data and use little storage space.
One disadvantage of views is that there is processing time required to recreate
A Guide to SQL, Ninth Edition Page 7–4
A Guide to SQL, Ninth Edition Page 7–5
Dropping a View
1. To remove a view, use the DROP VIEW command.
3. Mention the Access User Note on page 204, explaining that, in Access, you simply
Security
1. Define security. Security is the prevention of unauthorized access to a database. Within
2. In SQL systems, there are two security mechanisms. Views, which were previously
3. The GRANT command allows the database administrator to specify certain privileges
for each user.
5. Use Example 7 to illustrate granting a user the privilege to add records.
7. Use Example 9 to illustrate granting a user the privilege to delete records.
9. Use Example 11 to illustrate granting a user the privilege to create an index.
11. The WITH GRANT OPTION clause grants the indicated privilege to the user and also
permits the user to grant the same privileges to other users.
13. Use Example 14 to illustrate revoking a user the privilege to retrieve data.
Teaching
Tip
Make sure that students understand that there are other security measures that
should be used besides GRANT and REVOKE to protect databases. Most
systems will require a user ID and a password before accessing the database.
Quick Quiz 2
1. _____ is the prevention of unauthorized access to the database.
2. The _____ command is the main mechanism for providing access to a database.
3. The _____ command is used to revoke access to the database.
Indexes
1. Within relational data model systems on both mainframes and personal computers, the main
mechanism for increasing the efficiency with which data are retrieved from the database is
2. Use Figures 7-18 through 7-20 to explain how indexes work.
4. Point out that there are two disadvantages associated with indexes:
(2) Indexes must be updated whenever corresponding data in the database is updated.
5. Explain the difference between sorting and indexing. Sorting means that records are
Creating an Index
2. Use Example 15 and Figures 7-21 through 7-23 to illustrate creating an index.
Dropping an Index
1. To drop (delete) an index, use the DROP INDEX command. This command permanently
deletes the index.
2. Review the SQL User Note on page 218. SQL Server requires that, when you drop an
Creating Unique Indexes
1. When a table’s primary key is specified, SQL automatically ensures that the values entered
2. To ensure the uniqueness of values in a non-primary key column, a unique index can be
A Guide to SQL, Ninth Edition Page 7–7
System Catalog
2. The catalog tables are SYSTABLES (information about tables), SYSCOLUMNS
3. In Oracle, the names are DBA_TABLES, DBA_TAB_COLUMNS, and DBA_VIEWS.
5. Review the Access User Note and the SQL Server User Note on page 213 on how you
access data about tables, columns, and views in these DBMSs.
7. Use Example 17 and the SQL code to illustrate using DBA_VIEWS.
9. Use Example 19 and the SQL code to illustrate using DBA_TAB_COLUMNS to show
information about tables containing a specific column name.
10. Point out that the DBMS updates the system catalog automatically when users make
Update of the System Catalog
1. Point out that the DBMS updates the system catalog automatically when users make
2. Mention that you should not update the catalog directly.
Integrity Constraints in SQL
1. An integrity constraint is a rule for the data in the database. The integrity of the database
can be compromised when users enter data that violate integrity constraints.
2. To prevent this type of problem, SQL provides integrity support, the process of specifying
4. To add a primary key after creating a table, use the ADD PRIMARY KEY clause with the
ALTER TABLE command.
6. A foreign key is a column in one table whose values match the primary key in another
8. Use Example 20 and Figure 7-24 to illustrate adding a foreign key constraint.
9. Review the Access User Note on page 216 for information on how to enforce referential
integrity in Access.
11. Use Figure 7-26 to illustrate what happens when a user attempts to delete a record that
violates foreign key constraints.
13. Point out that the error messages in Figures 7-25 and 7-26 use the terms parent and child.
14. Use Example 21 and Figure 7-27 to illustrate adding an integrity constraint using the
15. Use Figure 7-28 to point out what happens when a user violates an integrity constraint.
Teaching
Tip
Referential integrity is the rule that, if a table A contains a foreign key that
matches the primary key of table B, then the value of this foreign key must either
match the value of the primary key for some row in table B or be null. Spend
some time explaining referential integrity concepts to students. These concepts
can be related to the concept of redundancy that was discussed in Chapter 2.
Using more than one table in a database eliminates redundancy, but there needs
to be some way to link the tables and prevent errors. Referential integrity is a key
characteristic of the relational data model, and all relational database
management systems must have the ability to enforce referential integrity.
Quick Quiz 3
1. A _____ is a column in one table whose values match the primary key in another table.
2. To add a primary key after creating a table, you can use the _____ clause of the ALTER
TABLE command.
3. To ensure that only legal values satisfying a particular condition are allowed in a given
column, use the _____ clause.
Class Discussion Topics
1. The database administrator is charged with managing the database. What does this
mean? What types of tasks would the database administrator do?
2. A view is an individual user’s picture of a database. In the TAL Distributors database,
why would you want to create a view for each category?
3. What other ways are available to prevent unauthorized access to a database other than
what is discussed in the chapter?
Additional Projects
1. Some versions of SQL use a CONSTRAINT clause rather than a CHECK clause.
2. ON UPDATE CASCADE is an optional clause that you can use when you define
referential integrity. What does this clause do?
Additional Resources
1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last
2. Referential Integrity: http://databases.about.com/cs/administration/g/refintegrity.htm
Key Terms
➢ ADD FOREIGN KEY: The clause of the ALTER TABLE command used to identify a
foreign key
➢ ADD PRIMARY KEY: The clause of the ALTER TABLE command used to add a
➢ data dictionary: System catalog or catalog
➢ database administration: The process of managing a database
➢ database administrator: The person or group charged with managing the database
A Guide to SQL, Ninth Edition Page 7–10
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
protected website for classroom use.
➢ DBA_TABLES: In Oracle, a table in system catalog that contains information about tables
known to SQL
➢ DBA_VIEWS: In Oracle, a table in system catalog that contains information about views
that have been created
➢ defining query: A query that indicates the rows and columns to include in a view
➢ DROP INDEX: The SQL command used to delete an index
➢ DROP VIEW: The SQL command used to delete a view
➢ foreign key: A column in one table whose values match the primary key of another table
➢ GRANT: The SQL command used to give users access privileges to data in the database
➢ index: A file that relates key values to records that contain those key values; the main
mechanism for increasing the efficiency with which data is retrieved from the database
➢ integrity constraint: A rule for the data in the database
➢ integrity support: The process of specifying integrity constraints for a database that the
DBMS will enforce
➢ SYSTABLES: A table in system catalog that contains information about tables known to
SQL
➢ system catalog: An object that stores information about the tables in the database
➢ SYSVIEWS: A table in system catalog that contains information about views that have
been created
➢ unique index: An index that ensures the uniqueness of values in a non-primary key column