without the written permission of VIT

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 1
Copyright © 2015 – 2018, Victorian Institute of Technology.
The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT,
other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology.
NORMALIZATION
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 2
Topics
• Normalization
• Normalization Steps
• Normalization Forms
• First Normal Form
• Second Normal Form
• Third Normal Form
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 3
Normalization
• Having studied functional dependencies and some of their
properties, we are now ready to use them to specify some
aspects of the semantics of relation schemas.
• We assume that a set of functional dependencies is given for
each relation, and that each relation has a designated
primary key; this information combined with the tests
(conditions) for normal forms drives the normalization
process for relational schema design. Most practical
relational design projects take one of the following two
approaches:
– First perform a conceptual schema design using a conceptual model
such as ER or EER and then map the conceptual design into a set of
relations.
– Design the relations based on external knowledge derived from an
existing implementation of files or forms or reports.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 4
Normalization (Cont…)
• Initially, Codd proposed three normal forms, which he called
first, second, and third normal form. A stronger definition of
3NF-called Boyce-Codd normal form (BCNF)-was proposed
later by Boyce and Codd. All these normal forms are based
on the functional dependencies among the attributes of a
relation.
• Normalization of data can be looked upon as a process of
analyzing the given relation schemas based on their FDs and
primary keys to achieve the desirable properties of
– Minimizing redundancy
– Minimizing the insertion, deletion, and update anomalies
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 5
Normalization Steps
• Normalization is a mechanical process to evaluate and
correct the table structure, with an aim to eliminate data
redundancies
– Goes through a number of stages known as “normal form”
– First stage is called the first normal form (1NF)
• Generally, (n+1)-NF is better than the n-NF
– For most situations, 3NF is the furthest we go
– Higher normal forms has less redundancies
– There are 10 Normal Forms exist
– We will look up to 3rd Normal Form
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 6
Normalization Forms
User Users’ Requirement Specification Sources describing the enterprise such
as Data Dictionary and Data Model
Unnormalized Form (UNF)
Transfer attribute into table format
First Normal Form (1NF)
Remove repeating groups
Second Normal Form (2NF)
Remove partial dependencies
Third Normal Form (3NF)
Remove transitive dependencies
Boyce-Codd normal Form (BCNF)
Make sure every determinant is candidate key
Fourth Normal Form (4NF)
Remove multi-valued dependencies
Special case of 3NF
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 7
First Normal Form
• A table is in First Normal Form when
– It has a primary key
– All fields are atomic (indivisible).
• For example in a shopping Website’s database an attribute
named “Credit Card Details” would NOT be atomic, and should
be broken up into the atomic attributes “credit card number”,
“expiry data”, “account name”, “name on card”, etc.
– Repeating groups have been removed.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 8
First Normal Form (Cont…)
• Primary Key
– We know that a Primary Key is an attribute that contain a unique
values for each record – an identifier
– We often create a new column containing an “auto number” for this
job, and normally call it “ID”. We can though use composite keys
(also known as compound keys), which means combining 2 or more
attributes to give us that unique value. Take this example of a table
recording when a manager first meets members:

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 9
First Normal Form (Cont…)
• Primary Key
– None of the 3 fields alone give a record a unique identity. Our
choices for a primary key are either to create a 4th column containing
an auto-number, or combining ManagerID and MemberID into a
composite key. S12C21, for example, will be a unique value in the
table. Only existing for that first record and never being repeated.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 10
First Normal Form (Cont…)
• All fields are atomic
– An atomic field is one that can’t logically or usefully be subdivided.
– Examples
• A field named “Customer Name” is probably not atomic, because
breaking up into “first name” and “last name” would probably be
logical and useful in your situation e.g. if you think it would useful
to search for customers based on their surnames.
• A field named “Street” could be divided into “street number” and
“street name”, but if that level of detail is unimportant for your
database, “Street” may be considered to be at the atomic level.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 11
First Normal Form (Cont…)
• All fields are atomic
– Example
• The author details are in this table are non-atomic
• They would be better broken up like this:
• Or even better, like this:

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 12
First Normal Form (Cont…)
• No Repeating Groups
– You can see that as you add more books, you may find that the
author details become a repeating group
– You should therefore spilt the table up, and maintain a connection by
placing the repeating table’s primary key in the other table (the table
on the many side – the author has many books):

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 13
First Normal Form (Cont…)
• No Repeating Groups
– Some of you may be thinking that a book can be written by many
authors. This would be the case, and so we now recognise that we
once again have a repeating group or non-atomic fields.
– Our solution for this is to
• create multiple author ID rows, but how many? Not a neat
solution.
• Flatten the table, as you can see on the next slide. Again, this is
not a neat solution, because it gets us back to repeating rows

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 14
First Normal Form (Cont…)
• No Repeating Groups
– Repeating values (the title and ID are repeating)
– So, once again we split them up

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 15
First Normal Form (Cont…)
• No Repeating Groups
– Remember that we always need a primary key in 1NF for each
table. The title table still has one, but not the Book/Author table we
just created. We can either create a new attribute (here called “ID”)
or we can combine the Book ID and Author ID into a composite
Primary key for the new table, or an auto number could be added:

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 16
First Normal Form (Cont…)
• To summarise that example, we now have a situation where
an author can write many books and a book can be written
by many authors (and so we have changed one table into 3)

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 17
First Normal Form (Cont…)
• What about if we add telephone numbers field (Phone)?
– Is it a repeating group?
– It may make sense to more columns – one for home phone, mobile,
work etc.
– But how many columns? How many phone numbers?
– You might decide creating columns is an impractical waste of space.
In that case, what you really have is repeating information, as you
can see if you flatten the table out (see next slide)

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 18
First Normal Form (Cont…)
– “flattening” the table reveals the repeating group
– So, the solution is, once again, to create 2 tables:
– Would you be confident that ID and Phone would form a composite
key (a unique value)?
– Notice too that we lose the “NULL” value in Janet’s phone number.

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 19
First Normal Form (Cont…)
• Our original guess at a table may have looked like this:
• In 1NF it becomes (with foreign and primary keys marked)

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 20
Second Normal Form
• Remove Partial Dependency
– 2NF need only be considered if you have a table that uses a
composite Primary Key
– A table is in 2NF if
• It is in 1NF,
and
• A non-key attribute is dependent on all parts of the composite key
(not just one of the composite key columns).
• In other words, if an attribute, doesn’t describe the entity it
belongs to, and instead only describes part of the Primary Key, it
shouldn’t be in the table.
• Removing it helps to eliminate redundant data
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 21
Second Normal Form (Cont…)
• Trivial functional dependency
– For example, our Phone table from the previous example contains
a composite key, and if we were to expand the table like so:
– You can see that the City data is relevant to the author alone – it’s
not connected with the phone number. We therefore have to pull it
out of this table, but where to?
Author table.
– What about Default Number? It is dependent on both the Author ID
and Phone #, so it should stay in this table.

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 22
Second Normal Form (Cont…)
• Another example (Electric Toothbrush Models) taken from
Wikipedia(https://en.wikipedia.org/wiki/Second_normal_form).
• The composite key is Manufacturer/Model.
• Model Full Name is dependent on both the Manufacturer and
Model (that is, for example Forte X-Prime is relevant to Forte’s
X-Prime, not Hoch’s X-Prime)

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 23
Second Normal Form (Cont…)
• Manufacturer Country is related to the Manufacturer and
has nothing to do with the Models of the manufacturer. So, it
doesn’t belong to that table.

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 24
Third Normal Form
• A table is in 3NF if
– It is in 2NF, and
– Transitive dependencies are removed, which means all non-key
attributes are dependent on the primary key, not another non-key
attribute. If you aren’t in 3NF, it often means you’ve allocated a field
to the wrong table.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 25
Third Normal Form (Cont…)
• Example (https://en.wikipedia.org/wiki/Third_normal_form)
• Note that 3NF is not concerned with nature of the primary
key (Composite or not). You can see that Winner’s date of
birth is relevant to the Winner column, which is a nonprimary key. It’s not connected directly with Tournament or
Year, and so it doesn’t belong in the table.

ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 26
Third Normal Form (Cont…)

• Update anomalies cannot occur in these tables, because
unlike before, Winner is now a primary key in the second
table, thus allowing only one value for Date of Birth for
each Winner.
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 27
Third Normal Form (Cont…)
• Summary
– A table is in 1NF if repeating groups are eliminated (and a primary
key has been defined and all attributes are atomic)
– A table is in 2NF if it’s in 1NF and all partial dependencies have been
removed
– A table is in 3NF if it’s in 2NF and all transitive dependencies have
been removed
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 28
Normalization Forms
• 1NF – First Normal Form
• 2NF – Second Normal Form
• 3NF – Third Normal Form
• EKNF – Elementary Key Normal Form
• BCNF – Boyce–Codd Normal Form
• 4NF – Fourth Normal Form
• ETNF – Essential Tuple Normal Form
• 5NF – Fifth Normal Form
• DKNF – Domain/Key Normal Form
• 6NF – Sixth Normal Form
ITDA1001 [Lesson 4] Copyright © 2018 VIT, All Rights Reserved 29
Summary
• Revision of Key Concepts
• Class Work
– Lesson 4 – Activity 4 (Lesson_4 Activity.docx)
• Questions and Answer

The post without the written permission of VIT appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms