Database Reference
In-Depth Information
9. Define interrelation constraint and give one example of such a constraint. How are interrelation constraints addressed?
10. Consider a Student table containing StudentNum, StudentName, student's StudentMajor, student's AdvisorNum,
student's AdvisorName, student's AdvisorOfficeNum, student's AdvisorPhone, student's NumCredits, and stu-
dent's Class (freshman, sophomore, and so on). List the functional dependencies that exist, along with the
assumptions that would support those dependencies.
11. Convert the following table to an equivalent collection of tables that are in third normal form. This table contains
information about patients of a dentist. Each patient belongs to a household.
Patient (HouseholdNum, HouseholdName, Street, City, State, Zip,
Balance, PatientNum, PatientName, (ServiceCode, Description,
Fee, Date) )
The following dependencies exist in the Patient table:
PatientNum
→
HouseholdNum, HouseholdName, Street, City, State,
Zip, Balance, PatientName
HouseholdNum
→
HouseholdName, Street, City, State, Zip, Balance
ServiceCode
→
Description, Fee
PatientNum, ServiceCode
→
Date
12. Using your knowledge of the college environment, determine the functional dependencies that exist in the fol-
lowing table. After determining the functional dependencies, convert this table to an equivalent collection of
tables that are in third normal form.
Student (StudentNum, StudentName, NumCredits, AdvisorNum,
AdvisorName, DeptNum, DeptName, (CourseNum, Description,
Term, Grade) )
13. Again, using your knowledge of the college environment, determine the functional or multivalued dependencies
that exist in the following table. After determining the functional dependencies, convert this table to an equiva-
lent collection of tables that are in fourth normal form. ActivityNum and ActivityName refer to activities in which a
student can choose to participate. For example, activity number 1 might be soccer, activity 2 might be band, and
activity 3 might be the debate team. A student can choose to participate in multiple activities. CourseNum and
Description refer to courses the student is taking.
Student (StudentNum, StudentName, ActivityNum, ActivityName,
CourseNum, Description)
180
Premiere Products Exercises
The following exercises are based on the Premiere Products database.
1. Using your knowledge of Premiere Products, determine the functional dependencies that exist in the following
table. After determining the functional dependencies, convert this table to an equivalent collection of tables that
are in third normal form.
Part (PartNum, Description, OnHand, Class, Warehouse,
Price, (OrderNum, OrderDate, CustomerNum,
CustomerName, RepNum, LastName, FirstName,
NumOrdered, QuotedPrice) )
2. List the functional dependencies in the following table that concerns invoicing (an application Premiere Products is
considering adding to its database), subject to the specified conditions. For a given invoice (identified by the Invoi-
ceNum), there will be a single customer. The customer's number, name, and complete address appear on the
invoice, as does the date. Also, there may be several different parts appearing on the invoice. For each part that
appears, display the part number, description, price, and number shipped. Each customer that orders a particular
part pays the same price. Convert this table to an equivalent collection of tables that are in third normal form.
Invoice (InvoiceNum, CustomerNum, LastName, FirstName,
Street, City, State, Zip, Date, (PartNum,
Description, Price, NumShipped) )
3. The requirements for Premiere Products have changed. A number
name now identify each warehouse.
Units of each part may be stored in multiple warehouses, and it is important to know precisely how many parts
and a
