Database Normalization Assignment Solution: First to Third Forms

This assignment solution focuses on database normalization, covering key concepts and practical applications. It begins with review questions addressing fundamental aspects of normalization, such as preventing anomalies, functional dependencies, and the characteristics of the first, second, and third normal forms. The solution then delves into practical problems, demonstrating the transformation of entities into the first, second, and third normal forms with illustrative figures and examples. The solution includes detailed explanations and examples of how to normalize a given table. The assignment concludes with a bibliography of relevant sources.

Running head: DATABASE NORMALIZATION
Database Normalization
Name of the Student
Name of the University

2DATABASE NORMALIZATION
Table of Contents
Task 1: Review Questions 3
Task 2: Problems 4
2.1 Normalization 1: 4
2.2 Normalization 2: 5
Bibliography: 10

3DATABASE NORMALIZATION
Task 1: Review Questions
Answer to Question 1: The basic function of normalization is preventing the anomalies
during modification in the database.
Answer to Question 2: The functional dependency is considered to be constraint between
two different sets of attributes in an entity.
Answer to Question 3: The first normal form database must not have any multivalued
attributes. Taken as an example, the one person can have different numbers (Nidzwetzki &
Güting, 2016). To shift the entity in first normalized form, the mobile numbers will be written in
two different rows and the rest of the person data will be copied.
Answer to Question 4: The second normalization form describes that all the non-key
attributes of the entity must be dependent on the proper subset of the entity only. Taken as an
example, an entity has teacher id, age and subject. As the subject is dependent on the teacher_id
only, this entity will be divided into two entities (Lee et al., 2015). One entity will hold the
teacher_id and age and other will hold teacher_id and subject.
Answer to Question 5: The third normalization form describes that the entity must not
have any transitive dependency. Taken as an example, a table has person details with zip, city,
street and state. The zip, address, city and state will be stored in a different table. The zip will be
used as the foreign key in the person table.
Answer to Question 6: There are mainly two reasons for using the normalization. The
first one is to increase the consistency of the database (Nidzwetzki & Güting, 2016).

4DATABASE NORMALIZATION
Normalization allows the entities to have singe row for each of the data sets. The second reason
is easy object-to-data mapping.
Task 2: Problems
2.1 Normalization 1:
Figure 1: First Normal Form of Entity ‘T’
(Source: Created by Author)

5DATABASE NORMALIZATION
Figure 2: Second Normal Form of Entity ‘T’
(Source: Created by Author)

6DATABASE NORMALIZATION
Figure 3: Third Normal Form of Entity ‘T’
(Source: Created by Author)
2.2 Normalization 2:
1st Normal Form: Visit Date and Procedure are multivalued attributes. The normalized
table is as following.
PET ID PET
NAME
PET
TYPE
PET
AGE
OWNER VISIT
DATE
PROCEDURE
246 ROVER DOG 12 SAM JAN 01 - RABIES

7DATABASE NORMALIZATION
COOK 13/2002 VACCINATION
246 ROVER DOG 12 SAM
COOK
MAR
27/2002
10 - EXAMINE
and TREAT
WOUND
246 ROVER DOG 12 SAM
COOK
APR
02/2002
05 - HEART
WORM TEST
298 SPOT DOG 2 TERRY
KIM
JAN
21/2002
08 - TETANUS
VACCINATION
298 SPOT DOG 2 TERRY
KIM
MAR
10/2002
05 - HEART
WORM TEST
341 MORRIS CAT 4 SAM
COOK
JAN
23/2001
01 - RABIES
VACCINATION
341 MORRIS CAT 4 SAM
COOK
JAN
13/2002
01 - RABIES
VACCINATION
519 TWEEDY BIRD 2 TERRY
KIM
APR
30/2002
20 - ANNUAL
CHECK UP
519 TWEEDY BIRD 2 TERRY
KIM
APR
30/2002
12 - EYE
WASH

8DATABASE NORMALIZATION
2nd Normal Form: The table is in first normal form. The procedure is dependent on the
visit date. Therefore, in the table, partial dependency exists. The normalized tables will be like
following.
PET ID
(Primary Key)
PET NAME PET TYPE PET AGE OWNER
246 ROVER DOG 12 SAM COOK
298 SPOT DOG 2 TERRY KIM
341 MORRIS CAT 4 SAM COOK
519 TWEEDY BIRD 2 TERRY KIM
PET ID (Primary Key) VISIT DATE (Primary
Key)
PROCEDURE
246 JAN 13/2002 01 - RABIES VACCINATION
246 MAR 27/2002 10 - EXAMINE and TREAT WOUND
246 APR 02/2002 05 - HEART WORM TEST
298 JAN 21/2002 08 - TETANUS VACCINATION
298 MAR 10/2002 05 - HEART WORM TEST
341 JAN 23/2001 01 - RABIES VACCINATION

9DATABASE NORMALIZATION
341 JAN 13/2002 01 - RABIES VACCINATION
519 APR 30/2002 20 - ANNUAL CHECK UP
519 APR 30/2002 12 - EYE WASH
3rd Normal Form: The normalized tables up to third normal form states that no transitive
dependency remains within the tables. The Pet owner has transitive dependency with the pet id.
The normalized tables would be like following.
PET ID (Primary
Key)
PET NAME PET TYPE PET AGE
246 ROVER DOG 12
298 SPOT DOG 2
341 MORRIS CAT 4
519 TWEEDY BIRD 2
PET ID (Primary Key) OWNER
246 SAM COOK
298 TERRY KIM
341 SAM COOK