Relational Design Part B: Normalization<\/p>\n
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.<\/p>\n
1. List the functional dependencies (FDs) relevant to the table. Some FDs need to be derived from Client Notes.<\/p>\n
2. Determine the highest normal form the (existing) table is in (explain your answer).<\/p>\n
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.).<\/p>\n
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.<\/p>\n
4. Normalize the table to 4NF explaining your steps. Remember to ensure:<\/p>\n
– You’ve fixed all problems with the table design including missing information<\/p>\n
– 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)<\/p>\n
– You’ve shown that the final relations are in 4NF (based on the functional dependencies)<\/p>\n
– You’ve marked the primary key of the tables at each stage<\/p>\n
– You’ve written down any assumptions made \/ explained any new attributes introduced.<\/p>\n
Carefully review FDs and \u201cClient Notes\u201d to ensure you have not missed any requirements for tables or attributes.<\/p>\n
Note:<\/p>\n
\u2022 Please do not ignore or contradict FDs or client Notes<\/p>\n
\u2022 Assume that all listed determinants and dependents are atomic unless the case says otherwise.<\/p>\n
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.<\/p>\n
Please write down any assumptions you make.<\/p>\n
MIS<\/p>\n
Department<\/p>\n
CURRENT TABLES (may have errors; address the errors during normalization)<\/p>\n
1) CAST_MEMBERS (castID, castSSN, castName, castAddress, castPhoneNumberList)<\/p>\n
2) CONTENT (contentID, castID, earningYear, castFirstName, castLastName, title, filmedDate, releaseDate, budget, amountEarned)<\/p>\n
3) CONTENT_DATA (contentID, distributionPlatform, license)<\/p>\n
4) PAYMENTS (paymentRecipientID, contentID, paymentPeriodBegin, empID, empFName, empLName, paymentMethod, paymentAmount, paymentDate, paymentPeriodEnd)<\/p>\n
5) REGIONS (regionID, regionName, HQLocation, distributor, manager)<\/p>\n
FUNCTIONAL DEPENDENCIES (correct, but may be incomplete)<\/p>\n
castID \u2192 castSSN<\/p>\n
castSSN \u2192 castID, castDOB, castFName, castLName, castStreetAddress, castCity, castState, castZip<\/p>\n
contentID \u2192 budget, contentType, filmedDate, releaseDate, language, regionID, totalCrewSize<\/p>\n
contentID, paymentRecipientID, paymentPeriodBegin \u2192 transactionNumber, empID, paymentPeriodBegin, paymentPeriodEnd, paymentMethod, paymentAmount, paymentDate<\/p>\n
contentID, earningYear \u2192 contentID, earningYear, amountEarned<\/p>\n
empEmail \u2192 empID, empFName, empLName, empEmail<\/p>\n
empID \u2192 empID, empLName, empFName, empEmail<\/p>\n
regionID \u2192 HQLocation<\/p>\n
regionID \u2192 regionName, regionSize, dateOperationsStarted<\/p>\n
regionName \u2192 regionID, manager, distributor, dateOperationsStarted<\/p>\n
transactionNumber \u2192 contentID, paymentRecipientID, paymentPeriodBegin, paymentPeriodEnd, paymentMethod, paymentAmount, paymentDate, transactionNumber<\/p>\n
CLIENT NOTES<\/p>\n
(use these to understand tables and supplement functional dependencies)<\/p>\n
\u25cf Addresses are made up of street, city, state and zip code.<\/p>\n
\u25cf 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., \u201c5551234, 5551244, 5551255\u201d) within castPhoneNumberList.<\/p>\n
\u25cf Cast (full) name is made up of first and last name.<\/p>\n
\u25cf Each content piece may have many cast members. A cast member may be part of many content pieces.<\/p>\n
\u25cf Content may be available on multiple distribution platforms (e.g., TV, streaming, etc.).<\/p>\n
\u25cf Content may also require many licenses (e.g., product placements, use of other IPs, etc.).<\/p>\n
\u25cf The amount earned for a piece of content is evaluated at the end of each year<\/p>\n
\u25cf 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).<\/p>\n
\u25cf 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.<\/p>\n
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 \u2192 A, B} or {A \u2192 A}. Similarly, inferred dependencies may not be shown. For example: {A \u2192 C} can be inferred from: {A \u2192 B} and {B \u2192 C}.<\/p>\n
USE BELOW TEMPLATE TO COMPLETE THE PART B ASSIGNMENT<\/p>\n
Normalization <\/p>\n
[Repeat the template below for each table, replace the template \u201cTableX(\u2026attributes\u2026)\u201d with the actual<\/p>\n
table and attributes.]<\/p>\n
1. CAST_MEMBERS (castID, castSSN, castName, castAddress, castPhoneNumberList)<\/p>\n
1) List Functional<\/p>\n
Dependencies relevant<\/p>\n
to Table (including from<\/p>\n
client notes)<\/p>\n
castID \u2192 castSSN<\/p>\n
castSSN \u2192 castID, castDOB, castFName, castLName, castStreetAddress, castCity, castState, castZip<\/p>\n
2A) Current Normal form<\/p>\n
2B) Explanation for<\/p>\n
normal form<\/p>\n
3A) Insert Anomaly<\/p>\n
3B) Delete Anomaly<\/p>\n
3C) Update Anomaly<\/p>\n
3D) Other Problems<\/p>\n
4) Normalization Steps<\/p>\n
2. CONTENT (contentID, castID, earningYear, castFirstName, castLastName, title, filmedDate, releaseDate, budget, amountEarned)<\/p>\n
1) List Functional<\/p>\n
Dependencies relevant<\/p>\n
to Table (including from<\/p>\n
client notes)<\/p>\n
2A) Current Normal form<\/p>\n
2B) Explanation for<\/p>\n
normal form<\/p>\n
3A) Insert Anomaly<\/p>\n
3B) Delete Anomaly<\/p>\n
3C) Update Anomaly<\/p>\n
3D) Other Problems<\/p>\n
4) Normalization Steps<\/p>\n
3. CONTENT_DATA (contentID, distributionPlatform, license)<\/p>\n
1) List Functional<\/p>\n
Dependencies relevant<\/p>\n
to Table (including from<\/p>\n
client notes)<\/p>\n
2A) Current Normal form<\/p>\n
2B) Explanation for<\/p>\n
normal form<\/p>\n
3A) Insert Anomaly<\/p>\n
3B) Delete Anomaly<\/p>\n
3C) Update Anomaly<\/p>\n
3D) Other Problems<\/p>\n
4) Normalization Steps<\/p>\n
4. PAYMENTS (paymentRecipientID, contentID, paymentPeriodBegin, empID, empFName, empLName, paymentMethod, paymentAmount, paymentDate, paymentPeriodEnd)<\/p>\n
1) List Functional<\/p>\n
Dependencies relevant<\/p>\n
to Table (including from<\/p>\n
client notes)<\/p>\n
2A) Current Normal form<\/p>\n
2B) Explanation for<\/p>\n
normal form<\/p>\n
3A) Insert Anomaly<\/p>\n
3B) Delete Anomaly<\/p>\n
3C) Update Anomaly<\/p>\n
3D) Other Problems<\/p>\n
4) Normalization Steps<\/p>\n
5. REGIONS (regionID, regionName, HQLocation, distributor, manager)<\/p>\n
1) List Functional<\/p>\n
Dependencies relevant<\/p>\n
to Table (including from<\/p>\n
client notes)<\/p>\n
2A) Current Normal form<\/p>\n
2B) Explanation for<\/p>\n
normal form<\/p>\n
3A) Insert Anomaly<\/p>\n
3B) Delete Anomaly<\/p>\n
3C) Update Anomaly<\/p>\n
3D) Other Problems<\/p>\n
4) Normalization Steps<\/p>\n
ALPHABETICAL list of FINAL Normalized relations (4NF)<\/p>\n
Mark all Primary Keys and Foreign keys.<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[10],"class_list":["post-78879","post","type-post","status-publish","format-standard","hentry","category-research-paper-writing","tag-writing"],"_links":{"self":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/78879","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/comments?post=78879"}],"version-history":[{"count":0,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/78879\/revisions"}],"wp:attachment":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/media?parent=78879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/categories?post=78879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/tags?post=78879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}