The following two tables could be used to store the data in Figure 5-15 in a database. ChoirMember (LastName, FirstName, Email, Phone, Part) MusicalWork (NameOfWork, Composer, Part, CopyNumber)
This notation means there are two tables, one named ChoirMember and a second named MusicalWork. The ChoirMember table has five columns: LastName, FirstName, Email, Phone, and Part; MusicalWork has four columns: NameOfWork, Composer, Part, and CopyNumber.
a. Redraw the data in Figure 5-15 into this two-table format.
b. Select primary keys for the ChoirMember and MusicalWork tables. ChoirMember (LastName, FirstName, Email, Phone, Part) MusicalWork (NameofWork, Composer, Part, CopyNumber)
c. The two tables are not integrated; they do not show who has checked out which music. Add foreign key columns to one of the tables to integrate the data.
d. This two-table design does not eliminate the potential for data integrity problems that occur in the spreadsheet. Explain why not.