Normalization Homework

This homework assignment focuses on database normalization. It presents a series of problems related to identifying functional dependencies, determining the highest normal form of a given relation, and explaining anomalies (insertion, update, deletion). The assignment includes examples of relations and asks students to identify functional dependencies, determine the normal forms (1NF, 2NF, 3NF), and provide concrete examples of anomalies that could arise. The provided solution details the answers to each question, explaining the reasoning behind the answers and providing specific examples to illustrate the concepts of normalization and anomalies in relational databases.

NORMALIZATION HW
STUDENT NAME:
15 October 2016

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 2nd NF 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:
Publisher Author Book
Penguin Pynchon Gravity’s Rainbow
Back Bay Wallace Infinite Jest
Scribner Bloom How to Read and Why
Viking Pynchon Gravity’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 3rd normal 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