Database Reference
In-Depth Information
Sysindexes
Systables
system catalog
Sysviews
trigger
validation rule
validation text
view
149
Review Questions
1. What is a view? How do you define a view? Does the data described in a view definition ever exist in that form?
What happens when a user accesses a database through a view?
2. Using data from the Premiere Products database, define a view named TopLevelCust. It consists of the number,
name, address, balance, and credit limit of all customers with credit limits that are greater than or equal to
$10,000.
a. Using SQL, write the view definition for TopLevelCust.
b. Write an SQL query to retrieve the number and name of all customers in the TopLevelCust view with bal-
ances that exceed their credit limits.
c. Convert the query you wrote in Question 2b to the query that the DBMS will actually execute.
3. Define a view named PartOrder. It consists of the part number, description, price, order number, order date,
number ordered, and quoted price for all order lines currently on file.
a. Using SQL, write the view definition for PartOrder.
b. Write an SQL query to retrieve the part number, description, order number, and quoted price for all orders in
the PartOrder view for parts with quoted prices that exceed $100.
c. Convert the query you wrote in Question 3b to the query that the DBMS will actually execute.
4. What is an index? What are the advantages and disadvantages of using indexes? How do you use SQL to cre-
ate an index?
5. Describe the GRANT statement and explain how it relates to security. What types of privileges may be granted?
How are they revoked?
6. Write the SQL commands to grant the following privileges:
a. User Stillwell must be able to retrieve data from the Part table.
b. Users Webb and Bradley must be able to add new orders and order lines.
7. Write the SQL command to revoke user Stillwell's privilege.
8. What is the system catalog? Name three items about which the catalog maintains information.
9. Write the SQL commands to obtain the following information from the system catalog:
a. List every table that you created.
b. List every field in the Customer table and its associated data type.
c. List every table that contains a field named PartNum.
10. Why is it a good idea for the DBMS to update the catalog automatically when a change is made in the database
structure? Could users cause problems by updating the catalog themselves? Explain.
11. What are nulls? Which field cannot accept null values? Why?
12. State the three integrity rules. Indicate the reasons for enforcing each rule.
13. The Orders table contains a foreign key, CustomerNum, that must match the primary key of the Customer table.
What type of update to the Orders table would violate referential integrity? If deletes do not cascade, what type
of update to the Customer table would violate referential integrity? If deletes do cascade, what would happen
when a customer was deleted?
14. How would you use SQL to change a table's structure? What general types of changes are possible? Which
commands are used to implement these changes?
15. What are stored procedures? What purpose do they serve?
16. What are triggers? What purpose do they serve? How do you gain the functionality of a trigger using Access
2010?
