Database Design: Normalization of Sample Item Records to 3NF - ERD

This report provides a detailed explanation of database normalization, focusing on transforming sample item records into a set of 3NF relations. It begins with an introduction to database normalization, emphasizing its role in reducing data redundancy and identifying relationships. The report includes a dependency diagram illustrating the dependencies within the original sample item records table. The normalization process results in the creation of an employee table to store building manager data and related employee information. An ERD diagram is also constructed to visually represent the relationships between entities, demonstrating how an employee can manage multiple buildings. The report concludes by highlighting the importance of dependency diagrams in identifying attribute dependencies and eliminating data anomalies, achieved through creating separate tables, assigning primary keys, and using foreign keys to establish relationships between tables.

Running head: DATABASE SYSTEMS
Database Systems
Name of the Student
Name of the University
Author’s Note

1
DATABASE SYSTEMS
Table of Contents
Introduction..........................................................................................................................2
Dependency Diagram..........................................................................................................2
3NF......................................................................................................................................3
ERD.....................................................................................................................................4
Conclusion...........................................................................................................................5
Bibliography........................................................................................................................6

2
DATABASE SYSTEMS
Introduction
The data base normalization is a technique that is used for designing the tables in such a
manner that there is no redundancy in the data that is inputted in the tables. The normalization is
applied for reducing the redundancy in data and identification of the relationship between the
data. The report is prepared for explaining the dependency and decomposition of the Sample
Item Records into a set of 3NF.
Dependency Diagram
The following table is in original state of “Sample Item Records”.
Table 1: Sample Item Records
ITEM_I
D
ITEM_LAB
EL
ROOM_NUMB
ER
BLDG_CO
DE
BLDG_NA
ME
BLDG_MANA
GER
231134-
678
HP Deskjet
895Cse
325 NTC Nottoclear I.B. Rightonit
342254-
225
HP Toner 325 NTC Nottoclear I.B. Rightonit
254668-
449
DT Scanner 123 CSF Canseefar May B. Next
The following dependency diagram is created from the above table and here the building code
(BLDG_CODE) is not considered as a determinant of Room Number and since the building may
have many rooms and the building code would not be sufficient to know the room.

3
DATABASE SYSTEMS
3NF
The employee table is created for storing data regarding the building manager and the
other employees of the organization.
The Room number is renamed as Item_room for relating with the building.

4
DATABASE SYSTEMS
ERD
Figure 1: ERD
(Source: Created by author)
The ERD Diagram is created for keeping track on the office furniture and normalizing the
given table structure. It acts as a reflection of the business rules of the organization and here an
employee can manage more than one building and thus one to many relationship is used for
connecting the employee table with the building. In the dependency diagram the nature of

5
DATABASE SYSTEMS
relationship cannot be depicted and thus the entity relationship diagram is created for the
demonstration of the relationship. It is assumed that the items would have an unique code and
thus the item is stored in one place at a time.
Conclusion
From the above report it can be concluded that the creation of the dependency diagram
helps in identification of the dependency between the attributes and eliminating the data
anomalies and redundancy by creating separate tables and assigning a primary key for each of
the new table. The primary key of the other table is used as foreign key of the other table for
joining each other and show the accurate result according to the query.

6
DATABASE SYSTEMS
Bibliography
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Bassil, Y. (2012). A comparative study on the performance of the Top DBMS systems. arXiv
preprint arXiv:1205.2889.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Hingorani, K., Gittens, D., & Edwards, N. (2017). REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND
NORMALIZATION TOGETHER FOR DESIGNING ROBUST DATABASES. Issues
in Information Systems, 18(1).