Relational Design Part B: Normalization
For each of the current tables (which may be poorly designed), follow the 5 steps listed below to normalize to 4NF. Use the submission template from D2L for a structured answer.
1. List the functional dependencies (FDs) relevant to the table. Some FDs need to be derived from Client Notes.
2. Determine the highest normal form the (existing) table is in (explain your answer).
3. List one example of: an insert, delete and update anomaly (each) for each table (if not in 4NF). Indicate any other problems the table might have (e.g., missing attributes, redundant data, etc.).
Note: Missing attributes are first shown in Step 3D, then integrated into the new (normalized) tables in Step 4. While in Step 1 you should include FDs relevant to the existing table including those that may have a relevant missing attribute, you do not need to consider missing attributes in Step 2.
4. Normalize the table to 4NF explaining your steps. Remember to ensure:
– You’ve fixed all problems with the table design including missing information
– You’ve shown tables (with primary keys marked) that you generate when going from the current normal form to each successive normal form all the way up to 4NF)
– You’ve shown that the final relations are in 4NF (based on the functional dependencies)
– You’ve marked the primary key of the tables at each stage
– You’ve written down any assumptions made / explained any new attributes introduced.
Carefully review FDs and “Client Notes” to ensure you have not missed any requirements for tables or attributes.
Note:
• Please do not ignore or contradict FDs or client Notes
• Assume that all listed determinants and dependents are atomic unless the case says otherwise.
Finally, after you’ve finished the normalization process (as described above) for ALL current tables, please make a list of tables that includes all the 4NF relations (with attributes) sorted alphabetically. Include primary keys and foreign keys in this version.
Please write down any assumptions you make.
MIS
Department
CURRENT TABLES (may have errors; address the errors during normalization)
1) CAST_MEMBERS (castID, castSSN, castName, castAddress, castPhoneNumberList)
2) CONTENT (contentID, castID, earningYear, castFirstName, castLastName, title, filmedDate, releaseDate, budget, amountEarned)
3) CONTENT_DATA (contentID, distributionPlatform, license)
4) PAYMENTS (paymentRecipientID, contentID, paymentPeriodBegin, empID, empFName, empLName, paymentMethod, paymentAmount, paymentDate, paymentPeriodEnd)
5) REGIONS (regionID, regionName, HQLocation, distributor, manager)
FUNCTIONAL DEPENDENCIES (correct, but may be incomplete)
castID → castSSN
castSSN → castID, castDOB, castFName, castLName, castStreetAddress, castCity, castState, castZip
contentID → budget, contentType, filmedDate, releaseDate, language, regionID, totalCrewSize
contentID, paymentRecipientID, paymentPeriodBegin → transactionNumber, empID, paymentPeriodBegin, paymentPeriodEnd, paymentMethod, paymentAmount, paymentDate
contentID, earningYear → contentID, earningYear, amountEarned
empEmail → empID, empFName, empLName, empEmail
empID → empID, empLName, empFName, empEmail
regionID → HQLocation
regionID → regionName, regionSize, dateOperationsStarted
regionName → regionID, manager, distributor, dateOperationsStarted
transactionNumber → contentID, paymentRecipientID, paymentPeriodBegin, paymentPeriodEnd, paymentMethod, paymentAmount, paymentDate, transactionNumber
CLIENT NOTES
(use these to understand tables and supplement functional dependencies)
● Addresses are made up of street, city, state and zip code.
● Multiple phone numbers may be stored for each cast member (the same phone number may be used by more than one cast member). Currently these are stored as a comma separated list (e.g., “5551234, 5551244, 5551255”) within castPhoneNumberList.
● Cast (full) name is made up of first and last name.
● Each content piece may have many cast members. A cast member may be part of many content pieces.
● Content may be available on multiple distribution platforms (e.g., TV, streaming, etc.).
● Content may also require many licenses (e.g., product placements, use of other IPs, etc.).
● The amount earned for a piece of content is evaluated at the end of each year
● Each payment is made for one piece of content and to one case member. Note: paymentRecipientID is the castID of the person (cast member) receiving the payment. One employee approves each payment (an employee may review many payments over time).
● Content types include: TV shows, movies, etc. For TV shows we store the total number of episodes and total number of seasons. For movies, we store the film genre.
Note: Each attribute (/ set of attributes) determines itself, and hence those dependencies may not be shown in the list of FDs. For example, {A, B → A, B} or {A → A}. Similarly, inferred dependencies may not be shown. For example: {A → C} can be inferred from: {A → B} and {B → C}.
USE BELOW TEMPLATE TO COMPLETE THE PART B ASSIGNMENT
Normalization
[Repeat the template below for each table, replace the template “TableX(…attributes…)” with the actual
table and attributes.]
1. CAST_MEMBERS (castID, castSSN, castName, castAddress, castPhoneNumberList)
1) List Functional
Dependencies relevant
to Table (including from
client notes)
castID → castSSN
castSSN → castID, castDOB, castFName, castLName, castStreetAddress, castCity, castState, castZip
2A) Current Normal form
2B) Explanation for
normal form
3A) Insert Anomaly
3B) Delete Anomaly
3C) Update Anomaly
3D) Other Problems
4) Normalization Steps
2. CONTENT (contentID, castID, earningYear, castFirstName, castLastName, title, filmedDate, releaseDate, budget, amountEarned)
1) List Functional
Dependencies relevant
to Table (including from
client notes)
2A) Current Normal form
2B) Explanation for
normal form
3A) Insert Anomaly
3B) Delete Anomaly
3C) Update Anomaly
3D) Other Problems
4) Normalization Steps
3. CONTENT_DATA (contentID, distributionPlatform, license)
1) List Functional
Dependencies relevant
to Table (including from
client notes)
2A) Current Normal form
2B) Explanation for
normal form
3A) Insert Anomaly
3B) Delete Anomaly
3C) Update Anomaly
3D) Other Problems
4) Normalization Steps
4. PAYMENTS (paymentRecipientID, contentID, paymentPeriodBegin, empID, empFName, empLName, paymentMethod, paymentAmount, paymentDate, paymentPeriodEnd)
1) List Functional
Dependencies relevant
to Table (including from
client notes)
2A) Current Normal form
2B) Explanation for
normal form
3A) Insert Anomaly
3B) Delete Anomaly
3C) Update Anomaly
3D) Other Problems
4) Normalization Steps
5. REGIONS (regionID, regionName, HQLocation, distributor, manager)
1) List Functional
Dependencies relevant
to Table (including from
client notes)
2A) Current Normal form
2B) Explanation for
normal form
3A) Insert Anomaly
3B) Delete Anomaly
3C) Update Anomaly
3D) Other Problems
4) Normalization Steps
ALPHABETICAL list of FINAL Normalized relations (4NF)
Mark all Primary Keys and Foreign keys.