Task 1: Review Questions
1: The basic function of normalization is preventing the anomalies during modification in the database.
2: The functional dependency is considered to be constraint between two different sets of attributes in an entity.
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.
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.
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.
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). 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
Normalization 1:
Figure 2: Second Normal Form of Entity ‘T’
(Source: Created by Author)
![]()
Figure 3: Third Normal Form of Entity ‘T’
(Source: Created by Author)
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 COOK
|
JAN 13/2002
|
01 - RABIES 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
|
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
|
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
|
519
|
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
|
341
|
JAN 13/2002
|
01 - RABIES VACCINATION
|
519
|
APR 30/2002
|
20 - ANNUAL CHECK UP
|
519
|
APR 30/2002
|
12 - EYE WASH
|
Bibliography
Gouhar, A. (2017). Database Management System. International Journal of Engineering Science, 11766.
Hababeh, I., Khalil, I., & Khreishah, A. (2015). Designing high performance web-based computing services to promote telemedicine database management system. IEEE transactions on services computing, 8(1), 47-64.
Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., ... & Lin, M. (2015). How I do it: a practical database management system to assist clinical research teams with data collection, organization, and reporting. Academic radiology, 22(4), 527-533.
Nidzwetzki, J. K., & Güting, R. H. (2016). DISTRIBUTED SECONDO: An extensible highly available and scalable database management system. FernUniversität, Fakultät für Mathematik und Informatik.
Van Aken, D., Pavlo, A., Gordon, G. J., & Zhang, B. (2017, May). Automatic database management system tuning through large-scale machine learning. In Proceedings of the 2017 ACM International Conference on Management of Data (pp. 1009-1024). ACM.
Yunus, M. A. M., Krishnan, S. K. G., Nawi, N. M., & Surin, E. S. M. (2017). Study on Database Management System Security Issues. JOIV: International Journal on Informatics Visualization, 1(4-2), 192-194.