Database Normalization Report: Dependency Diagrams and ERDs

Desklib provides solved assignments and past papers to help students understand database normalization.

Normalisation

Introduction
In this report, defined about the normalisation of the database and also defined its dependencies
and dependency diagram. In this report, designed the dependency diagram of database and also
dram crow’s foot entity-relationship diagram (ERD). Normalisation is a process in which data is
represented and also it is a procedure in which data is taken from the problems and reduced it
with the set of relations. Many forms are used in database normalisation in which 1st normal
form, 2nd normal form, 3rd normal form and Boyce code normal form (BCNF) are included. In
this report, dependency diagram also defined in which all the tasks are defined in the form of
graphical display in which all the requests are completed. Normalisation is used in the database
management system in which all the redundant data is removed from the tables and to improve
the integrity of data, storage productivity and also data scalability.
a)

a). Dependency Diagram –
The dependency diagram is a graphical presentation of the whole tasks and workflow in which
required to fulfill a request. It is used to generate dependencies among many tasks. In the
dependency diagram components of primary key should be with underline, shaded with different
colors and bold. In the dependency diagram, arrows indicate all dependencies in above the
diagram that is based on primary key and the below the diagram, arrows indicate less
dependencies that is not based on primary key.
Dependency in database management system is defined by the relation between its attributes.
Dependency in the database occurs when a lot of information are deposited in same table of the
database (Pang, 2018).
Figure 1: Design of Dependency
This diagram displays all entities in which EMP_NAME, DEPT_CODE, PROF_OFFICE,
EMP_NAME, PROF_RANK, COMMITTEE_CODE, JOURNAL_CODE, ADVISEE is
included in the table. In this dependency diagram, three PK (primary key) are defined that is
EMP_NAME, PROF_OFFICE and DEP_NAME.

b) Multi-valued Dependency –
Multivalued dependency is known as full constraint in which it shows the relation between two
groups of attributes. In Multivalued dependency, requires certain rows which is presented in a
relation. Multivalued dependency is also a part of join dependency in which value of two sets are
involved and dependency of tuple generating. Multivalued dependency is defined in database
normalisation of 4th normal form. In a multivalued dependency, at least three columns or
attributes are required in a relation and all attributes are connected with each other.
Multivalued dependency arises when it defines independent of two attributes in a relation of
many other attributes but these attributes are dependent with third attribute. In the multivalued
dependency of database management system, it signified by the double arrow. Multivalued
dependency produces redundancies in a table and contributes with inconsistent information
(Hermo, 2018).
Figure 2: Design of Multi-valued Dependency
The above multivalued dependency diagram displays a primary key that is PROF_OFFICE and
also defined attributes name that is JOURNAL_CODE, ADVISEE, and COMMITTEE_CODE.
Any attributes are not needed with these attributes as these attributes are before themselves
dependent or independent.
Figure 3: Part 1
Figure 4: Part 2

Figure 5: Part 3

c) 3NF Structure Dependency –
3NF is a type of normalisation of database management system in which 3NF dependency is a
normal form of normalisation in database management system in which all the conditions are
followed of 2NF and in this, non-prime attributes are not depended on another non-prime
attributes. In other words, in 3NF no transitive dependency is required for non-prime features.
3NF (normal form) is used to decrease the duplication of data and also it is used to get the
integrity of data.
3NF is designed to delete unnecessary data in a table and it is used to make model of information
more useful. 3NF is also designed to reduce the requirement elements over time for restructuring
(Albarak, 2018).
There are some structure table are defined in which 3NF structure dependency is followed.
InfoOfProfessor Table
Figure 6: InfoOfProfessor table
Above diagram displays the table of professor in which it displays the relation between its
attributes that is PROF_OFFICE and PROF_BAMK and primary key in which primary key is
PROF_OFFICE.
InfoOfEmployee Table
Figure 7: InfoOfEmployee table
Above diagram shows the table of employee in which it displays the relation between its
attributes and primary key. In this diagram attributes are EMO_NAME and EMO_NUM and
primary key is EMP_NAME.

DetailsOfProfInfo Table
Figure 8: DetailsOfProfInfo Table
Above diagram shows the table of professor info in which it displays the relation between its
attributes that is COMMITTEE_CODE, PROF_OFFICE, ADVISEE JOURNAL_CODE and
primary key that is PROF_OFFICE.
InfoOfDepartment Table
Figure 9: InfoOfDepartment Table
Above diagram shows the table of department in which it displays the relation between its
attributes that is DEPT_NAME and DEPT_CODE and primary key that is DEPT_CODE.

d) Crow Foot Notation diagram –
Crow Foot Notation is a database of design of modeling notation in which it is also identified as
notation of IE. It is represented by the symbol of graphical design in which it specifies ‘many’
symbols of the relation. In the crow foot notation diagram, entities are presented by symbols and
lines also describe the relation between the rows.
Figure 10: Crow's Foot Diagram
Above diagram displays the entity relationship diagram of the system or the diagram of crow
foot notation in which it shows the relation between the attributes and the entities in a relation.

Conclusion
The above following points are mentioned to define about normalization. In this report, student
learnt about the uses of normalisation in database management system and also four types of
normal forms of normalisation. Through this assignment, student understood about the
dependencies and its use in database management system. This report discussed the 3NF
structure diagram of normalisation and also many relationships between attributes and entities or
attributes and primary key. In this report, defined about the primary key and how used in a table
and also how is designed in a relation and also defined many key elements about entity
relationship diagram. The above report is defined by the structure of complete system or
complete normalisation of the already defined database which is represented in the brief already.
The above report is also defined the crow foot notation diagram which completely works on
entity relationship diagram.

References
Albarak, M., & Bahsoon, R. (2018, May). Prioritizing technical debt in database normalization
using portfolio theory and data quality metrics. In Proceedings of the 2018 International
Conference on Technical Debt (pp. 31-40). ACM.
Pang, J. N. K., Watts, M. S., Parandehgheibi, A., & Yadav, N. (2018). U.S. Patent No.
9,967,158. Washington, DC: U.S. Patent and Trademark Office.
Hermo, M., & Ozaki, A. (2018). Exact learning of multivalued dependency
formulas. Theoretical Computer Science, 716, 4-14.