3nf normalization

Data Normalization Exercises

Normalize to 3NF the following collections of data attributes. Make whatever
assumptions that you need to, but document your assumptions.

1) Entity1(ProfessorName, Faculty, Department, OfficeNumber, PhoneExt, Secretary,
CourseName, CourseID)
· Assume that all of the professors in a department share a secretary.
· Assume that a professor may teach more than one course.
· Assume that ProfessorName is unique.

2) Entity2(RecipeName, Ingredient, IngredientUnitCost, RecipeIngredientQuantity,
CookingTime, CookingTemp)
· Recipes use many ingredients, and an ingredient is used in many recipes.

3) Entity3(BookTitle, AuthorName, PublisherName, PublisherAddress,
BookYearPublished, BookFormat, BookCost)
· Assume that BookTitle is unique for a book, but that it can be published in
different formats (ex. Hardcover, PaperBack, e-Book,...) and that each format can
have a different price.
· Assume that a book may have more than one author.

4) Entity4(EmpID, EmpName, ProjID, ProjName, MeetingDate, MeetingLocation)
· Assume that a project meets at most once per day.
· Assume that a project can have many employees assigned to it
· Assume that employees can be assigned to multiple projects.

5) Entity5(StudentID, Name, Club, Cost, AmtPaid)
· Assume Name is student name, Club is club name, Cost is club cost, AmtPaid is
how much has been paid to date for a specific club membership
· Students can join one or more clubs, each of which charges a membership fee.
Solutions to Data Normalization Exercises

Note that the solutions provided are not necessarily the only correct way to solve the
problems, and that depending on the assumptions that you make you may have different
solutions. After I originally posted these solutions for section 91, I presented the
exercises in section 01 and we came up with some changes to the solutions.

1) The original posted solution was

· Entity1a(ProfessorName, Department, OfficeNumber, PhoneExt)
· Entity1b(Department, Faculty, Secretary)
· Entity1c(CourseID, CourseName)

After working this out in class, we realized that there would very likely be a dependency
(and hence a 3rd normal form violation) between PhoneExt and OfficeNumber since
while one office may have more than one extension if the office is shared, it is unlikely
that a phone extension would be in more than one office. The reason why we care about
this is that the violation of 3NF could lead to an update anomaly where the professor
moves into a new office and we are left with the old phone number. This led to replacing
Entity 1a with the following:
· Entity1ai(ProfessorName, Department)
· Entity1aii(PhoneExt, OfficeNumber)
Breaking these two entities apart also anticipates the situation where a Professor may
have more than one office.

There is a similar 3NF issue arising from the fact that if you know a Secretary then you
can determine the Faculty. We can anticipate that we will have more Faculty specific
information coming though, and choose to move Faculty into its own entity.

Another thing that we realized was Department is held in Entity1b, and at this stage of
the design it should not be also in Entity1a. At a later point in the design process when
we implement the relationship between Departments and Professors we will be migrating
Department into Entity1a as a foreign key, but it is premature to do so now.

At the end of the discussion we could have the following design:
· Entity1a(ProfessorName)
· Entity1b(PhoneExt, OfficeNumber)
· Entity1c(Department, Secretary)
· Entity1d(Faculty,...)
· Entity1e(CourseID, CourseName)

You can see from this line of logic that the end result of data normalization is usually a
very large number of very small tables. This can be difficult to implement in the real
world and is the reason why we usually end up de-normalizing before final
implementation, but at the same time being careful to add code to protect against the
anomalies that de-normalization allows to creep into the database.
2) The original posted solution was:
· Entity2a(RecipeName, CookingTime, CookingTemp)
· Entity2b(IngredientName, IngredientUnitCost)
· Entity2c(RecipeName, IngredientName, RecipeIngredientQuantity)
The only problem that we found in this solution was the question of whether there was a
relationship between cooking time and cooking temperature. We know that we can cook
something at a lower temperature if we cook it for a longer time. After discussion we
decided that while this is true, it is not something that most recipes or cookbooks deal
with. If we wanted to create an information system that allowed us to choose a number
of recipes and would then alter the cooking temperatures so that the times all worked out
to be the same, then it would be an important piece of information. It seems like an
unnecessary complication at this point in time, although you may want to footnote the
solution to note the issue.

3) The original posted solution was:
· Entity3a(BookTitle, BookYearPublished)
· Entity3b(PublisherName, PublisherAddress)
· Entity3c(BookTitle, AuthorName)
· Entity3d(BookTitle, BookFormat, BookCost)
There were a number of problems noted with this solution.

First of all, Entity3c should not contain BookTitle, and should instead simply be
· Entity3c(AuthorName)
There is in fact a many to many relationship between BookTitle and Author (more on this
in future classes) and that relationship will lead to an entity like the original 3c, but it is
premature to anticipate it at this point in the design process.

Secondly there was a question raised regarding whether or not BookYearPublished truly
belongs in Entity3a, or if it should not really be in Entity3d since different formats may
be published at different points in time. This is a great question and the answer depends
on how you construe the business rules of the publishing industry. Either way you should
probably footnote the entity containing BookYearPublished and explain what you are
assuming regarding the business process.


4) The original posted solution was:
· Entity4a(EmpID, EmpName)
· Entity4b(ProjID, ProjName)
· Entity4c(ProjID, MeetingDate, MeetingLocation)
This seems pretty good.
5) The original posted solution was:
· Entity5a(StudentID, Name)
· Entity5b(Club, Cost)
· Entity5c(StudentID, Club, AmtPaid)
It would be difficult to decompose this any further, so I think it is probably good.


As you can see, data normalization (and the design process in general) is iterative. It is
difficult to come up with the right answer right away, and in many cases there may well
be more than one right answer. While there may be several right answers, there are many
more wrong answers. When doing your work I would suggest doing it early, then
thinking about the solution and reviewing it again later. Remember to footnote your
designs to explain what you are assuming about the business processes to support your
work.

If you have a problem understanding any of the solutions then be sure to ask about it.