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.

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.

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.

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.