ITC556 Database Systems Assessment 3: Normalization

Desklib provides solved assignments and past papers to help students succeed.

ITC556 - DATABASE SYSTEMS
ASSESSMENT ITEM 3 –
NORMALISATION
Student ID:
Student Name:

Table of Contents
Introduction......................................................................................................................................2
Dependency Diagram......................................................................................................................3
Multivalued Dependency.................................................................................................................4
3NF Structure...................................................................................................................................5
Crow’s Foot ERD............................................................................................................................6
Conclusion.......................................................................................................................................7
References........................................................................................................................................8
List of Figures
Figure 1: Diagram of Dependency..................................................................................................3
Figure 2: Diagram of Multivalued dependency...............................................................................4
Figure 3: First..................................................................................................................................4
Figure 4: Second..............................................................................................................................5
Figure 5: Third.................................................................................................................................5
Figure 6: table 1...............................................................................................................................6
Figure 7: Table 2..............................................................................................................................6
Figure 8: Table 3..............................................................................................................................7
Figure 9: Table 4..............................................................................................................................7
Figure 10: ERD Crow Foot..............................................................................................................8
1

Introduction
Normalization is shown in the following report which also shows databases design of the given
system. Assessment 3 covers all the structures of databases and tables included in it. The report
contains the explanation of normalization and other dependency diagrams which shows the
relationship between different attributes. The 3NF normalization structure is shown with the
attributes which have the property of 3NF. The Report contains the different diagrams and
Crow’s Foot ERD diagram for a given attribute. This report helps to understand different terms
in the databases and the connectivity between different attributes.
2

Dependency Diagram
The Dependency Diagram is a diagram that shows the dependency of one attribute on another.
There are different types of dependency on a table. The diagram represents the flow of the
complete work in the system which is shown using attributes and other types of relationships
between these attributes. The primary key shows the uniqueness of the values in the given tables.
The diagram is used for showing the dependency of the attributes (Visual Studio, 2018).
Figure 1: Diagram of Dependency
The above diagram shows the dependency relation between the attributes included in the table.
These attributes are linked with other attributes in such a manner that working of the complete
system depends upon these attributes. The Attributes includes primary key which isEmp_Name,
Dep_Code, and Prof_Office. The other attributes are Emp_Num, Dept_Num, Prof_Room,
Advisee, Journal_code, and Committee_code. Among these attributes, the relation in the form of
partial dependency and transitive dependency.
3

Multivalued Dependency
Multivalued dependency is the type of dependency in which there is more than one dependent
attribute for a single attribute. There are multiple attributes that do not have any dependency on
each other but there is a dependence upon the common attribute. There are at least 3 attributes
required for showing Multivalued Dependency. The diagram is used for showing the
dependency.
Figure 2: Diagram of Multivalued dependency
The given diagram shows the multivalued dependency for the given attributes and the one
primary key is also shown with different colors. The Primary is underlined. The other attributes
are Advisee, Journal_code and Committee_code.
These dependencies can be shown as a single relation as it is shown in the below diagram:
Figure 3: First
4

Figure 4: Second
Figure 5: Third
The given figures show that all the values are dependent on the single value which is
Prof_Office. The table is split on the basis of dependencies.
5

3NF Structure
The 3NF is a form of the normalization which is used to shows the relationship between different
attributes. The 3NF shows that the given dependency contains 2NF normalization and also there
is the dependency of all fields that are nonprimary upon the Primary key present in the table. The
transitive dependency can occur in any relationship which is shown in this part. There are
different table which can be shown on the basis of the 3NF normalization form (“Database -
Third Normal Form (3NF) (Tutorialspoint, 2019).
The structures are:
Table_Professor
Figure 6: table 1
The above diagram reflects the dependency between two attributes in a table Professor. The
Attributes are Prof_Rank and Prof_Office in which Prof_Office is the primary key.
Table_Department
6

Figure 7: Table 2
The given figure shows the dependency of the attributes in the table of Departments. The
attributes are Dept_Code and Dept_Name in which Dept_Code is the primary key.
Table_Employee
Figure 8: Table 3
The following diagram shows the normalization between the attributes of the table Employee.
Their Primary key is Emp_Num which has a unique value.
Table_InfoProfessor
7

Figure 9: Table 4
The following figure shows the relationship between different attributes in which there are
multiple dependencies in a table. The multivalued dependency is shown in the following diagram
with the primary key included in it. Prof_Office is selected as a primary key.
8

Crow’s Foot ERD
The Crow’s Foot ERD is used for showing the relationship between different entities in the
system with their attributes. There are some primary keys included in the diagram with the
dependencies are also included in the diagram. Each entity is linked with another entity so the
dependency is at a high level (Dybka, 2019).
Figure 10: ERD Crow Foot
The following diagram shows the Crow’s Foot notation which shows the different types of
relationships between the attributes and shows the structure of the complete system.
9

Conclusion
Assessment 4 is based on the structure design of the database and expiation of different relation
types. The dependency between different attributes and tables are reflected using the diagram.
The Crow’s Foot diagram shows the relationship between different entities and the attributes
include in it. the Complete system is shown using the tables and the dependency between these
attributes.
10

References
Database - Third Normal Form (3NF) - Tutorialspoint. (2019). Retrieved 12 September 2019,
from https://www.tutorialspoint.com/sql/third-normal-form.htm("Database - Third Normal Form
(3NF) - Tutorialspoint", 2019)
Dybka, P. (2019). Crow’s Foot Notation. Retrieved 12 September 2019, from
https://www.vertabelo.com/blog/crow-s-foot-notation/(Dybka, 2019)
Dependency Diagrams: Guidelines - Visual Studio. (2018). Retrieved 12 September 2019, from
https://docs.microsoft.com/en-us/visualstudio/modeling/layer-diagrams-guidelines?view=vs-
2019
11