ITDA1001 Database Fundamentals

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.