Databases Reference
In-Depth Information
EXERCISES
1. The main relation of a motor vehicle registration
bureau's relational database includes the following
attributes:
a. List the product name and unit price of all of the
products.
b. List the employee names and titles of all the
employees of customer 2198.
c. Retrieve the record for office number 1284.
d. Retrieve the records for customers headquartered
in Los Angeles.
e. Find the size of office number 1209.
f. Find the name of the salesperson assigned to
office number 1209.
g. List the product name and quantity sold of each
product sold by salesperson 361.
6. Consider the General Hardware Corp. relational
database and the data stored in it, as shown
in Figure 5.14. Find the answer to each of the
following queries (written in the informal relational
command language described in this chapter).
a. Select rows from the CUSTOMER EMPLOYEE
relation in which Customer Number
=
2198.
b. Select rows from the CUSTOMER EMPLOYEE
relation in which Customer Number
=
2198.
Project Employee Number and Employee Name
over that result.
c. Select rows from the PRODUCT relation in
which Product Number
=
21765.
d. Select rows from the PRODUCT relation in
which Product Number
Vehicle
License
Owner
Identification
Plate
Serial
Number
Number
Number
Manufacturer
Model
Year
Color
The Vehicle Identification Number is a unique num-
ber assigned to the car when it is manufactured. The
License Plate Number is, in effect, a unique number
assigned to the car by the government when it is
registered. The Owner Serial Number is a unique
identifier of each owner. Each owner can own more
than one vehicle. The other attributes are not unique.
What is/are the candidate key(s) of this relation? If
there is more than one candidate key, choose one
as the primary key and indicate which is/are the
alternate key(s).
2. A relation consists of attributes A, B, C, D, E, F, G,
and H.
No single attribute has unique values.
The combination of attributes A and E is unique.
The combination of attributes B and D is unique.
The combination of attributes B and G is unique.
Select a primary key for this relation and indicate
and alternate keys.
3. In the General Hardware Corp. relational database
of Figure 5.14:
a. How many foreign keys are there in each of the
six relations?
b. List the foreign keys in each of the six relations.
4. Identify the relations that support many-to-many
relationships, the primary keys of those relations,
and any intersection data in the General Hardware
Corp. database.
5. Consider the General Hardware Corp. relational
database. Using the informal relational command
language described in this chapter, write commands
to:
21765. Project Unit
=
Price over that result.
e. Join the SALESPERSON and CUSTOMER
relations using the Salesperson Number attribute
of each as the join fields. Select rows from that
result in which Salesperson Name
Baker.
=
Project Customer Name over that result.
f. Join the PRODUCT relation and the SALES
relation using the Product Number attribute of
each as the join fields. Select rows in which
Product Name
=
Pliers. Project Salesperson
Number and Quantity over that result.
7. For each of Exercise 6, describe in words what the
query is trying to accomplish.
MINICASES
1.
Consider the following relational database for Happy
Cruise Lines. It keeps track of ships, cruises, ports, and
passengers. A ''cruise'' is a particular sailing of a ship
on a particular date. For example, the seven-day journey
of the ship Pride of Tampa that leaves on June 13,
2009, is a cruise. Note the following facts about this
environment.
Search WWH ::
Custom Search