Databases Reference
In-Depth Information
1.
What is a view? How is it defined? Do the data described in a view definition ever exist in that form? What hap-
pens 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 num-
ber, name, address, balance, and credit limit of all customers with credit limits that are greater than or equal to
$10,000.
a.
144
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, num-
ber 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 data-
base 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 is deleted?
14.
How would you use SQL to change a table's structure? What general types of changes are possible? Which com-
mands 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?
In the following exercises, you will use the data in the Premiere Products database shown in Figure 2-1 in Chapter 2. (If
you use a computer to complete these exercises, use a copy of the original Premiere Products database so your data will
not reflect the changes you made in Chapter 3.) If you have access to a DBMS, use the DBMS to perform the tasks and
explain the steps you used in the process. If not, explain how you would use SQL to obtain the desired results. Check
with your instructor if you are not certain about which approach to take.
