itdb 1102-chapter4
TRANSCRIPT
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 1/42
Semester1 (2013-2014)
ITDB 1102Introduction to Database
Normalization 1
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 2/42
Semester1 (2013-2014)
Normalization
•
Normalization is a database design Technique.• The purpose of normalization is to remove redundancy
(duplication).
• One Piece of information is stored in one place in the
database.
2
Normalization is the process of decomposing
relation with anomalies into small relations while
ensuring data integrity and eliminating dataredundancy
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 3/42
Semester1 (2013-2014)
Data integrity
•Data integrity: all of the data in the database is consistent,and satisfies all integrity constraints.
3
Dept_IDDOBStd_NameStd _ID
D0013/10/1994AhmadS121
D00223/6/1993SalmaS122
D0012/9/1994KhalidS123
Dept_NameDept_ID
ITD001
EnggD002
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 4/42
Semester1 (2013-2014)
Data Redundancy
• Data is said to contain redundancy if:
• Direct redundancy: Data in the database can be found intwo different locations
• Indirect redundancy :Data can be calculated from other
data items).
• Data should only be stored once and avoid storing data that can be
calculated from other data already held in the database.
4
Dept_NameDept_IDDOBStd_NameStd _ID
Dept_NameDept_ID
AgePhone_NoDOBStd_NameStd _ID
19927363483/10/1994AhmadS121
209863762123/6/1993KhalidS122
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 5/42
Semester1 (2013-2014)
Data Redundancy
• If redundancy exists in the database then problems can
arise when the database is in normal operation:
• Insert : When data is inserted the data must be
duplicated correctly in all places where there is
redundancy.
• Update: When data is modified in the database, if the
data being changed has redundancy, then all versions of
the redundant data must be updated simultaneously.
• The removal of redundancy helps to prevent insertion,
deletion, and update errors, since the data is only available
in one attribute of one table in the database. 5
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 6/42
Semester1 (2013-2014)
Normal Forms
• The data in the database can be considered to be in one
of a number of `normal forms'.
• Normal form of the data indicates how much
redundancy is in that data. The normal forms have a
strict ordering
• 1st Normal Form
• 2nd Normal Form
• 3rd Normal Form
• To be in a particular form requires that the data meets the criteria to also be
in all normal forms before that form. Thus to be in 2nd normal form the data
must meet the criteria for both 2nd normal form and 1st normal form. The
higher the form the more redundancy has been eliminated. 6
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 7/42Semester1 (2013-2014)
Anomalies
7
EmpId Ename DeptName Salary Course_Title Date_Completed
100 Ali Marketting 4800 SPSS 6/19/2011
100 Ali Marketting 4800 Surveys 10/7/2011
140 Said Accounting 5200 Tax Acc 12/8/2011
110 ahmed Infosystem 4300 Visual Basic 1/12/2011
110 ahmed Infosystem 4300 C++ 4/22/2011
150 Khalid Marketting 4200 SPSS 6/19/2011
150 khalid Marketting 4200 Java 8/12/2011
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 8/42Semester1 (2013-2014)
1. Insert AnomalyInsert Anomaly – The insert anomaly refers to a situation where in
you cannot insert data into the database because of an artificial
dependency among columns in a table.
• A record about an entity cannot be inserted into the table
without first inserting information about another entity.
• Example:• The primary key for this table is the combination of Emp_ID and
Course_Title.
• Therefore, to insert a new row, the user must supply values for both
Emp_ID and Course_Title (because primary key values cannot be null
or nonexistent).
• This is an anomaly. Because the user should be able to enter
employee data without supplying course data.8
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 9/42Semester1 (2013-2014)
2. Delete AnomalyDelete Anomaly– It refers to a situation where in the deletion of
data causes unintended loss of other data.
• A record cannot be deleted without deleting a record about a
related entity.
• Cannot delete a sales order without deleting all of the
customer’s information.
• Example:
• Suppose that the data for employee number 140 are deleted from
the table.
•
This will result in losing the information that this employeecompleted a course (Tax Acc) on 12/8/200X.
• In fact it results in losing the information that this course had an
offering that completed on that date.9
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 10/42Semester1 (2013-2014)
3. Update Anomaly
Update Anomaly – An update anomaly refers to a situationwhere in an update of a single data value requires multiple
rows to be updated.
Cannot update information without changing information in
many places.
To update customer information, it must be updated for each
sales order the customer has placed.
• Example:
• Suppose that employee number 100 gets a salary increase.
• We must record the increase in each of the rows for thatemployee (two occurrences in above table); otherwise the data
will be inconsistent.
10
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 11/42Semester1 (2013-2014)
Normalization
What is Normalization? • Normalization is the process of decomposing relations with
anomalies to produce smaller relations with minimal
redundancy.
The goal of normalization:
1. Minimize data redundancy, thereby avoiding anomalies
and conserving storage space.
2. Simplify the enforcement of referential integrity
constraints.
3. Make it easier to maintain data (insert, update, and
delete).11
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 12/42Semester1 (2013-2014)
Normalization
Advantages:
1. Reduce data redundancy (duplication of data).
2. Eliminate data inconsistency (improper data)
3. Reduce the amount of space a database
consumes.
4. Reduces time to search a data.
5. Reduces time to sort data. 12
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 13/42
Semester1 (2013-2014)
Functional Dependencies
• Functional dependency describes the relationship between
attributes in a relation.
• If A and B are attributes of a relation R, B is functionally
dependent on A (den. A → B), if each value of A in R is associated
with exactly one value of B in R.
• Example:
13
Tutor_NameTutor_NoSur_nameFirst_NameMatric_No
Tutor_No -> Tutor_Name Determinant Dependent
employee
A BB is functionally dependent on A
Determinant: Attribute or set of attributes
on the left hand side of the arrow
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 14/42
Semester1 (2013-2014)
Functional Dependencies• Identify the candidate key for a relation:
• Recognize the attribute (group of attributes) that uniquelyidentifies each row in a relation.
• All of the attributes that are not part of the primary key (non-
primary key attributes) should be functionally dependent on the
key.
14
Staff-NO PositionPosition is functionally
dependent on Staff-NO1:1
10002 HOD
Position Staff NOStaff NO is functionally
dependent on Position1:M
10002
HOD
10002
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 15/42
Semester1 (2013-2014)
The Normal Form
1. First Normal Form ( 1NF )
2. Second Normal Form ( 2NF )
3. Third Normal Form ( 3NF )
4. Fourth Normal Form ( 4NF )
5. Boyce-Codd Normal Form ( BCNF )
15
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 16/42
Semester1 (2013-2014)
Process of Normalization:•
Un normalized form (UNF): A table that contains one or morerepeating groups.
• Repeating group: An attribute or group of attributes within a
table that occurs with multiple values for a single occurrence
of the nominated key attributes of that table.
16
Std ID Name Date
of
Birth
AdvisorID Advisor
Name
Advisor
Phone
No
CourseID Course
Name
Credit Hrs Passing
grade
Std
Mark
S01234 Ali 5/7/90 11 Mr. Aijaz 123 ITSE1100
ITNT1103
ITDB1102
Multimedia
Hardware
Database
6
5
5
D
C
C
60
70
50
S01235 Said 6/5/89 12 Ms.Aysha 124 ITSE1100
ITNT1103
Multimedia
Hardware
6
5
D
C
70
80
Repeating group
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 17/42
Semester1 (2013-2014)
First Normal Form•
A relation in which the intersection of each rowand column contains one and only one value.
• UNF → 1NF:
1. Remove repeating groups- by entering
appropriate data in the empty columns of
rows.
2. Placing repeating data along with a copy of the
original key attribute in a separate relation.3. Identifying a primary key for each of the new
relations.17
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 18/42
Semester1 (2013-2014)
Second normal form (2NF):A relation is said to be in 2NF if is in 1NF and no partial functional
dependency.• Partial functional dependency: A functional dependency in which
one or more non key attributes are functionally dependent on part
(but not full/all) of the primary key.
• Note: Applies to relations with composite keys (primary key
composed of two or more attributes).
• A relation with a single attribute primary key is in 2NF.
• 1NF → 2NF:
1. Remove partial dependencies
2. The functionally dependent attributes are removed from the
relation by placing them in a new relation along with a copy of
their determinant. (Part of the primary key)18
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 19/42
Semester1 (2013-2014)
Third normal form (3NF):A relation is said to be in 3NF if it is in 2NF and no transitively
dependency exist.
• Transitive dependency: A relationship between two (or more)
non-key attributes.
• When one non-key attribute is functionally dependent onanother non-key attribute
• 2NF → 3NF:
• The transitively dependent attributes are removed from therelation by placing them in a new relation along with a copy of
their determinant.19
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 20/42
Semester1 (2013-2014)
An example for Normalization• Consider the following table which is not in the first normal form:
20
Std ID Nam
e
Date
of
Birth
Advisor-
ID
Advisor
Name
Advisor
Phone
No
CourseID Course
Name
Credit
Hrs
Passing
grade
Std
Mark
S01234 Ali 5/7/9
0
11 Mr. Aijaz 123 ITSE1100
ITNT1103
ITDB1102
Multimedia
Hardware
Database
6
5
5
D
C
C
60
70
50
S01235 Said 6/5/8
9
12 Ms.Aysha 124 ITSE1100
ITNT1103
Multimedia
Hardware
6
5
D
C
70
80
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 21/42
Semester1 (2013-2014)
To bring the table to 1NF:
1. Adjust the table to have one value in each cell.
2. Separate the repeating group fields into a separate table.
• The repeating fields are Student ID, Name, Date of Birth, Advisor
ID, Advisor Name and Advisor Phone No.
3. Provide a link (by foreign key) between student table and
Student-course table.
4. Determine the primary keys and foreign keys in each table21
Student
ID
Name Date of
Birth
AdvisorID Advisor
Name
Advisor
Phone
CourseID Course
Name
Credit
Hrs
Passing
Grade
Std
Mark
S01234 Ali 5/7/90 11 Mr. Aijaz 123 ITSE1100 Multimedia 6 D 60
S01234 Ali 5/7/90 11 Mr. Aijaz 123 ITNT1103 Hardware 5 C 70
S01234 Ali 5/7/90 11 Mr. Aijaz 123 ITDB1102 Database 5 C 50
S01235 Said 6/5/89 12 Ms. Aysha 124 ITSE1100 Multimedia 6 D 70
S01235 Said 6/5/89 12 Ms. Aysha 124 ITNT1103 Hardware 5 C 80
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 22/42
Semester1 (2013-2014)
First Normal Form
• Student Table
• Primary Key – StudentID
22
StudentID Name Date of
Birth
AdvisorID Advisor Name Advisor
Phone No
S01234 Ali 5/7/90 11 Mr. Aijaz 123
S01235 Said 6/5/89 12 Ms. Aysha 124
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 23/42
Semester1 (2013-2014)
• Student- Course Table
1. The StudentID is not unique itself, as one student may take
multiple courses.
2. The Course_ID is not unique itself, as many students may takethe same course, then StudentID + CourseID is unique, so the
primary key is StudentID + CourseID.
• Primary Key: StudentID + CourseID (Composite key)
• Foreign Key: StudentID, it links Student Course Table with Student
Table.
23
StudentID CourseID Course Name Credit Hrs Passing Grade Std Mark
S01234 ITSE1100 Multimedia 6 D 60
S01234 ITNT1103 Hardware 5 C 70
S01234 ITDB1102 Database 5 C 50
S01235 ITSE1100 Multimedia 6 D 70
S01235 ITNT1103 Hardware 5 C 80
FK
PK (Composite key)
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 24/42
Semester1 (2013-2014)
24
0 NF 1NF 2NF 3NF
Std ID Student
Name Std ID PK
Date of Birth Name
Advisor-ID Date of Birth
Advisor Name Advisor-ID
Advisor
Phone No
Advisor Name
CourseID Advisor Phone No
Course Name Student-course
Credit Hrs Std ID FK
Passing grade CourseID
Std Mark Course Name
Credit Hrs
Passing grade
Std Mark
Primary Key Foreign Key
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 25/42
Semester1 (2013-2014)
Second Normal Form (2NF)• 2NF applies on Student- Course table only because it contains a
composite key. To bring this table to 2NF:1. Find out the non key fields which do not depend on the whole
primary key and place them in a separate table with a link (Foreign
Key).
For the Student - Course table the primary key is: StudentID + CourseID
2. The Non-key fields Course Name, Credit Hours, and Passing Grade
depend on the CourseID only which is a portion of the primary key.
3. The remaining field is StdMark which depends on the StudentID +CourseID which is the primary key where it gives the mark of a
student in a specific course.
25
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 26/42
Semester1 (2013-2014)
Second Normal Form (2NF)
• Student Table
• Primary Key – StudentID
26
StudentID Name Date of
Birth
AdvisorID Advisor Name Advisor
Phone No
S01234 Ali 5/7/90 11 Mr. Aijaz 123
S01235 Said 6/5/89 12 Ms. Aysha 124
Student table is already in 2NF because it has a primary key formed
with one attribute (StudentID).
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 27/42
Semester1 (2013-2014)
Second Normal Form (2NF)Divide the Student-Course
table into two tables:1. Student-Course table
2. Course table.
Student-Course table
• Primary Key:
StudentID+CourseID
• Foreign Key:
StudentID, CourseID
27
StudentID CourseID Std Mark
S01234 ITSE1100 60
S01234 ITNT1103 70
S01234 ITDB1102 50
S01234 ITDB1102 70
S01235 ITSE1100 70
S01235 ITNT1103 80
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 28/42
Semester1 (2013-2014)
Second Normal Form (2NF)• Course Table
• Primary Key: CourseID
28
CourseID Course Name Credit Hours Passing grade
ITSE1100 Multimedia 6 D
ITNT1103 Hardware 5 C
ITDB1102 Database 5 C
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 29/42
Semester1 (2013-2014)
29
0 NF 1NF 2NF 3NF
Std ID Student Student
Name Std ID PK Std ID PK
Date of Birth Name Name
Advisor-ID Date of Birth Date of Birth
Advisor Name Advisor-ID Advisor-ID
Advisor Phone No Advisor Name Advisor Name
CourseID Advisor Phone No Advisor Phone No
Course Name Student-course Student-course
Credit Hrs Std ID FK Std ID FK
Passing grade CourseID CourseID FK
Std Mark Course Name Std Mark
Credit Hrs Course
Passing grade CourseID PK
Std Mark Course Name
Credit Hrs
Passing grade
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 30/42
Semester1 (2013-2014)
Third normal form (3NF)
• Third normal form (3NF) goes one large step further:
• For each non key attribute (or set of attributes) that is a
determinant in a relation, create a new relation.
• That attribute (or set of attributes) becomes the primary key of
the new relation.
•
Move all of the attributes that are functionally dependent on theattribute from the old to the new relation.
• Leave the attribute (which serves as a primary key in the new
relation) in the old
• relation to serve as a foreign key that allows you to
associate the two relations.
30
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 31/42
Semester1 (2013-2014)
Third normal form (3NF)• Course table and Student- Course table remains the same
because they are already upto 3rd NF.
(No non-key attribute determines the other non-key attribute)
• In the Student table, a non key attribute Advisor Phone is
determined by another non key attribute Advisor Name.
• So the advisor information should be placed in a separate
table and link/association (Foreign key) with the Student table.31
non-key attribute ---> non-key attribute
Advisor Name ---> Advisor Phone
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 32/42
Semester1 (2013-2014)
Third normal form (3NF)• Student Table
Primary Key: StudentID Foreign Key: AdvisorID
• Advisor Table
Primary Key: AdvisorID
32
StudentID Name Date of Birth AdvisorID
S01234 Ali 5/7/90 11
S01235 Said 6/5/89 12
AdvisorID Advisor Name Advisor Phone No
11 Mr. Aijaz 123
12 Ms.Aysha 124
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 33/42
Semester1 (2013-2014)
33
0 NF 1NF 2NF 3NF
Std ID Student Student Student
Name Std ID PK Std ID PK Std ID PK
Date of Birth Name Name Name
Advisor-ID Date of Birth Date of Birth Date of Birth
Advisor Name Advisor-ID Advisor-ID Advisor-ID FK
Advisor Phone No Advisor Name Advisor Name Advisor
CourseID Advisor Phone No Advisor Phone No Advisor-ID PK
Course Name Student-course Student-course Advisor Name
Credit Hrs Std ID FK Std ID FK Advisor Phone No
Passing grade CourseID CourseID FK Student-course
Std Mark Course Name Std Mark Std ID FK
Credit Hrs Course CourseID FK
Passing grade CourseID PK Std Mark
Std Mark Course Name Course
Credit Hrs CourseID PK
Passing grade Course Name
Credit Hrs
Passing grade
PK
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 34/42
Semester1 (2013-2014)
Case Study:
34
UNF
Invoice_No
Invoice_date
Customer_No
Customer_Name
Customer_Address
Tax_total
Gross_Total
Net_Total
Item_code
Item_Description
Price
Quantity
Tax_percent
Tax_amount
Net_amount
Primary Key
Foreign Key
Repeating Groups: Note that each invoice has
many items, so the attributes related to the
item in the invoice are repeated.
UNF 1NF
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 35/42
Semester1 (2013-2014)
35
UNF 1NF
Invoice_No Invoice
Invoice_date Invoice_No
Customer_No Customer_No
Customer_Name Invoice_dateCustomer_Address Customer_Name
Tax_total Customer_Address
Gross_Total Tax_total
Net_Total Gross_Total
Item_code Net_Total
Item_Description Item
Price Invoice_No
Quantity Item_code
Tax_percent Item_DescriptionTax_amount Price
Net_amount Quantity
Tax_percent
Tax_amount
Net_amount
Primary Key
Foreign Key
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 36/42
Semester1 (2013-2014)
36
0 NF 1NF 2NF 3NF
Matric_No Student Student
Name Matric_No Matric_No
Date_Of_Birth Name Name
Subject Date_Of_Birth Date_Of_Birth
Grade
Record Record
Matric_No Matric_No
Subject Subject
Grade Grade
Primary Key
Foreign Key
UNF 1NF 2NF
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 37/42
Semester1 (2013-2014)
37
UNF 1NF
Invoice_No Invoice
Invoice_date Invoice_No
Customer_No Customer_No
Customer_Name Invoice_date
Customer_Address Customer_Name
Tax_total Customer_Address
Gross_Total Tax_total
Net_Total Gross_Total
Item_code Net_Total
Item_Description Item
Price Invoice_No
Quantity Item_code
Tax_percent Item_Description
Tax_amount Price
Net_amount Quantity
Tax_percent
Tax_amount
Net_amount
Primary Key
Foreign Key
Check part key
dependency: We note that
description, price, and taxdepend on part of the key
( Item code) not on full key
( item code+ invoiceno) so
remove these data item
into new table.
2NF
Invoice
Invoice_No
Invoice_date
Customer_No
Customer_Name
Customer_Address
Tax_total
Gross_Total
Net_Total
Item
Item_code
Item_Description
Price
Tax_percent
Invoice_Item
Invoice_No
Item_Code
Quantity
Tax_amount
Net_amount
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 38/42
Semester1 (2013-2014)
38
UNF 1NF 2NF
Invoice
Invoice_No Invoice Invoice_No
Invoice_date Invoice_No Invoice_date
Coustomer_no Invoice_date Customer_no
Customer_Name Customer_no Customer_Name
Customer_Address Customer_Name Customer_Address
Tax_total Customer_Address Tax_total
Gross_Total Tax_total Gross_Total
Net_Total Gross_Total Net_Total
Item_code Net_Total Item
Item_Description Item Item_code
Price Invoice_No Item_Description
Quantity Item_code Price
Tax_percent Item_Description Tax_percent
Tax_amount Price Invoice_Item
Net_amount Quantity Invoice_No
Tax_percent Item_code
Tax_amount Quantity
Net_amount Tax_amount
Net_amount Primary Key
Foreign Key
Check none key dependency:
We note that customer
name, customer address
depends oncustomer No. so we create a
new data group with primary
key customer No. all other
data groups
are fully dependent on
primary key, so no change
will take place.
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 39/42
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 40/42
Semester1 (2013-2014)
Summary
• 1NF
• A relation is in 1NF if it contains no repeating groups
• To convert an unnormalised relation to 1NF either:
•
Flatten the table and change the primary key, or• Decompose the relation into smaller relations, one for
the repeating groups and one for the non-repeating
groups.
•
Remember to put the primary key from the originalrelation into both new relations.
• This option is liable to give the best results.40
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 41/42
Semester1 (2013-2014)
Summary
• 2NF
• A relation is in 2NF if it contains no repeating groups and no
partial key functional dependencies
• Rule: A relation in 1NF with a single key field must be in 2NF
•To convert a relation with partial functional dependencies to 2NF.create a set of new relations:
• One relation for the attributes those are fully dependent upon
the key.
• One relation for each part of the key that has partially dependent
attributes
41
8/10/2019 ITDB 1102-Chapter4
http://slidepdf.com/reader/full/itdb-1102-chapter4 42/42
Summary
• 3NF
• A relation is in 3NF if it contains no repeating groups, no partial
functional dependencies, and no transitive functional
dependencies
•
To convert a relation with transitive functional dependencies to3NF, remove the attributes involved in the transitive dependency
and put them in a new relation
• Rule: A relation in 2NF with only one non-key attribute must be in
3NF
•
In a normalized relation a non-key field must provide a fact aboutthe key, the whole key and nothing but the key.
• Relations in 3NF are sufficient for most practical database design
problems. However, 3NF does not guarantee that all anomalies
have been removed.42