(Solved): CIS 365 – Business Database Systems HW5 – Normalization (70 points)...
CIS 365 – Business Database Systems
HW5 – Normalization (70 points)
This is an individual exercise, NOT a group assignment.
HW5-1: Explore data in the following table (60 points)
Assume that the primary key of this relation consists of two components: author ID (AID) and book number (BNbr). Each author has an ID, last name, first name, and teaching institute. Each book has a number, name, price, and publisher which has an ID, name, city, and state. In addition, this table shows an individual author’s royalty amount for each book in the case of multi-authored books. Some books written by different authors might have the same name, i.e., BookName can't uniquely identify a book.
1. Identify the normal form in which the relation currently is. (5 points)
2. Identify the determinants and functional dependencies between the attributes. (25 points)
ADCmgpID ? ADCmpgName, StartDate, Duration, CmpgMgrID, CmpgMgrName (Partial)
CmpgMgrID ? CmpgMgrName (Transitive)
ModusID ? Media, Range (Partial)
ADCmpgID, ModusID ? BudgetPctg (Full)
3. After multi-valued attributes are transformed to store atomic values in the table, identify insert, update, and delete anomalies in the table. (12 points)
Insertion: problem description
Update: problem description
Deletion: problem description
4. Show the final result of the 3NF tables with primary key in each (bold and underlined), foreign key (dotted line for transitive dependency), a pointer from a foreign key to its primary key ?, and the rest of the attributes. (18 points)
HW5-2: (10 points)
Copy your HW4 solutions for the Housekeeping and Invoice tables and paste them in HW5 here.
If they are in 3NF, state that they are in 3NF and no further work required for this question. If they are not, transform them to 3NF and show your table result.
Due Date: EOD Monday
• Combine your answers for HW5-1, HW5-2, and HW5-3 into one pdf file for submission.
• Save your file name as HW5-Student Name.pdf.
• Submissions must be posted to Canvas to be graded.