The assignment content discusses normalization and functional dependencies in relational databases. It provides examples of relations in different normal forms (1NF, 2NF, 3NF) and explains the reasons why they are or are not in those forms. The concepts of insertion, update, and deletion anomalies are also discussed.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
NORMALIZATION HW STUDENT NAME: 15 October 2016
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
1.Given the following functional dependencies: Student_ID --> Student_Name Student_ID --> Class Student_ID --> Advisor_ID Advisor_ID--> Advisor_Name Advisor_ID --> Advisor_Office# What is the highest normal form the following relation is in and why: Student(Student_ID, Student_Name, Class, Advisor_ID, Advisor_Name, Advisor_Office#) Answer: the relation is in 1NF. This is so because the attributes are atomic. There will be one and only one value in each row. But there is partial dependency in the relation, so 2ndNF is not possible. The Advisor_ID is dependent on Student_ID and Advisor_Name, Advisor_Office# is dependent on the Advisor_ID, hence a partial and transitive dependency is created and still exists. So the highest form for the above relation is 1NF. 2.Consider the following table state: PublisherAuthorBook PenguinPynchonGravity’s Rainbow Back BayWallaceInfinite Jest ScribnerBloomHow to Read and Why VikingPynchonGravity’s Rainbow Specify ALL the functional dependencies that, based on above the data, we can rule OUT and WHY we can rule them out Answer: the functional dependencies are: 1.Author -> Book: This is so because there is unique author name for every book. Each author is related to one book. 2.Publisher -> Author For the following questions, consider the following relation: Repair (Repair_Call_ID, Date, Mechanic_SSN, Mechanic_Name, Grade, Machine_ID, Machine_Type, Machine_Date_of_Purchase) Relevant Business Rules: (for each of the following you can assume that ‘one’ is equivalent to ‘at most one’) A given mechanic, identified by SSN, has one name and one grade and can go on many repair calls. A machine is identified by its ID and has one type and one date of purchase. A given repair call, identified by its ID number, is attended by one mechanic and is associated with one machine. 1|P a g e
3.List all the functional dependencies in the above description. Answer: the functional dependencies are: a.Mechanic_SSN -> Mechanic_Name b.Mechanic_SSN -> Grade c.Machine_ID -> Machine_Type d.Machine_ID -> Machine_Date_of_Purchase e.Repair_Call_ID -> (Mechanic_SSN, Machine_ID) 4.Is this relation in 2nd normal form? Explain how you are able to tell. Answer: the relation is not in 2NF because the attributes are not completely dependent upon the primary key. There is a partial dependency in the relation like Mechanic_Name and Grade are dependent on Mechanic_SSN and Machine_Type and Machine_Date_of_Purchase are dependent on Machine_ID. So these are not completely dependent on Repair_Call_ID, hence not in 2NF but are in 1NF. 5.Is this relation in 3rdnormal form? Explain how you are able to tell. Answer: since the relation is not in 2NF, hence it cannot be in 3NF as well. 6.Provide a specific, concrete example of an INSERTION anomaly that could arise from the relations above. Answer: we assume that new machine data has been added but there are no corresponding repairs related to it. This means that Repair_Call_ID will be null, which is a primary key and hence will not be allowed. Insert into repairs values (Repair_Call_ID, Date, Mechanic_SSN, Mechanic_Name, Grade, Machine_ID, Machine_Type, Machine_Date_of_Purchase) values (null, null, null, null, null, 1, ‘sewing’, ‘2016-10-10’) 7.Provide a specific, concrete example of an UPDATE anomaly that could arise from the relations above. Answer: we assume that there are 6 rows of data. Now, if the name of the mechanic with SSN say = 1 has to be updated, all the corresponding rows will have to be updated. If any row is left, it will create an update anomaly. 8.Provide a specific, concrete example of a DELETION anomaly that could arise from the relations above. Answer: if a mechanic with say SSN = 2 leaves the company, all the corresponding records will be deleted if we delete the data of this mechanic. Hence this will create a deletion anomaly. 2|P a g e