Chapter 6, Problem 4CATC

To determine

To expand the database design created in exercise 3 to incorporate following user view:

CAT is required to send invoice to each participant. The details of invoice should be classes in which participant is enrolled, total fee for the adventure classes, name and address of participant.

Given information:

Following database design was developed for exercise 3:

Explanation:

Based on user view following Invoice table should be generated:

Invoice (InvoiceNum, ClassNum, TotalFees, LastName, FirstName, Address)

In the above relation attributes LastName, FirstName and Address are already present in Participant table. Thus, only by including the PNumber as foreign key in Invoice table one should be able to get last name, first name and address of the participant. Therefore, Invoice relations should have the following structure:

Invoice (InvoiceNum, ClassNum, TotalFees, PNumber)

Thus, DBDL for this user view will be as follows:

Cumulative design after this user view will be as follows: