Database Fundamentals Assignment: Normalization, ERD, and Solutions

This assignment solution focuses on database table normalization, a crucial process for organizing data to minimize redundancy and ensure data integrity. The solution begins with an introduction to databases and normalization, emphasizing its importance in modern organizations. The core of the assignment involves normalizing a given database table through three normal forms: 1NF, 2NF, and 3NF. The solution details the steps taken to transform the table into each normal form, explaining the reasoning behind the changes and the identification of dependencies, including functional and transitive dependencies. Dependency diagrams are presented to illustrate the relationships between attributes. The solution also includes the creation of an Entity Relationship Diagram (ERD) to visually represent the database structure. Each step of the normalization process is explained, including identifying multi-valued attributes and partial dependencies. The final result is a well-structured database table that minimizes redundancy and ensures data consistency. This assignment provides a comprehensive understanding of database normalization and its practical application.

Database Fundamentals
1

Contents
INTRODUCTION...........................................................................................................................3
TASK 1- DEPENDENCY DIAGRAM...........................................................................................3
TASK 2- NORMALIZATION TABLE IN FIRST NORMAL FORM..........................................6
Normalise the table in 1-NF........................................................................................................6
Conversion in First normal form.................................................................................................8
Discuss the solution and explain reason why table was not in first Normal form.......................9
Changes made to turn into First Normal Form..........................................................................10
TASK 3- NORMALIZATION TABLE IN SECOND NORMAL FORM...................................10
Normalise table in second normal form.....................................................................................10
Discuss the solution and explain reason as why table was not in 2-NF....................................11
Changes made to turn into second normal form........................................................................12
If table is 1-NF and why it is in 1-NF........................................................................................12
TASK 4- NORMALIZATION TABLE IN THIRD NORMAL FORM.......................................13
Normalise table in Third normal form.......................................................................................13
Changes made to turn into third normal form...........................................................................14
If the table is in the third normal form, explain why it is in the third normal form..................14
TASK 5- ERD................................................................................................................................14
CONCLUSION..............................................................................................................................15
REFERENCES..............................................................................................................................16
2

INTRODUCTION
Database can be defied as a structured set of data which is helped within a computer that
can be accessed from anywhere at any time. It helps in improving overall quality of data and
further helps in storing it in an accurate error free manner (Vo and Hoang, 2020). Most of the
organizations today use different kinds of databases for storing their data in a secured manner so
that only authorised person can access and edit the data as per their need and requirement.
Normalization is one of the most important process that help in organizing data in database. It is
extremely important that data stored in database is normalized to minimum redundancy and
ensure that only related data is stored in each table. This assessment will lay emphasis upon
performance of database table normalization in which database table will be first normalized in
1NF, then in second normal form and then in third normal form.
TASK 1- DEPENDENCY DIAGRAM
Dependency is a kind of contestant in database that applies to or defines relationship
between attributes (Ordonez, Al-Amin and Bellatreche, 2020). Dependency in database occurs
when information or data is stored in the same database table uniquely and helps in determining
other information stored in the same table. It can also be defied as a kind of value of attribute
which is sufficient to understand values of other attributes stored in same table.
There are various kinds of dependencies that are present within database that are:
functional dependency, fully functional dependency, multivalued dependency, transitive
dependency and partial dependency. In normalization functional and transitive dependencies
play a vital and important role. Functional dependency is a kind of dependency in which a value
or information stored in a table is uniquely sufficient to determine other information or data
stored in same table (Alattar and Sali, 2020). Whereas transitive dependency is a kind of
dependency in which there is indirect relationship that cause functional dependency.
In order to draw a dependency diagram, it is important to identify desirable dependency
which is based on primary key and less desirable dependency which is based upon composite
primary key and transitive dependency. Dependency diagram of given student table case has
3

been drawn and explained below. In this, transitive functionalities present in 1NF have been
explained and identified in below diagram.
Figure 1 Dependency diagram
In the above diagram there are three functional dependencies and two transitive
dependencies present in 1NF form. 1 functional dependencies and on transitive dependencies in
2 NF.
1NF
Functional dependencies are:
STU_NAME -> STU_PHONENUMBER -> STU_DOB
SCHOOL_HEAD -> SCHOOL_PHONENUMBER
STU_SUBJECT -> SUBJECT_CODE ->SUBJECT_CREDITPOINT
Transitive dependencies are:
STU_NAME -> STU_SUBJECTMARK
STU_NAME -> SUBJECT_CODE
2NF
Functional dependencies are:
STU_NAME -> STU_SUBJECTMARK -> STU_SCHOOL
Transitive dependencies are:
SUBJECT_CODE -> STU_NAME
4

Given Database tables-
Student table-
STU_name STU_pho
neNo
STU_DO
B
STU_home
Country
STU_contin
et
STU_gende
r
STU_School STU_major
John 1231 12/3/1995 Australia Oceania Male Business Accounting
Nick 3213 1/2/2000 India Asia Female IT &
engineering
Software
developmen
t
William 9748 3/5/2015 India Asia Male IT &
engineering
Information
Systems
Kim 6383 12/2/1990 China Asia Female Business Finance
Table: 1
School Head Table-
School_phoneNo STU_Subject
9054 1321 Accounting Principles, Business Law,
Quantitative Method
9067 1835 Introduction to Programming, Database
Fundamentals
9067 1835 Introduction to Programming, Introduction to
Information Systems
9054 1321 Introduction to Microeconomics,
Principles of Finance
Table: 2
5

Subject Lecturer table-
SUBJECT
CODE
SUBJECT
CREDITPOINT
STU
SUBJECTMARK
STU
AVERAGEMARK
STU
SUBJECTGRADE
ACCT1001,
BLAW1002,
QUAN1003
6, 6, 6 60, 70, 80 70 P,C,D
BIT1001,
BIT100
12, 6 50, 60 55 P,P
BIT1001,
BIT1003
12, 6 70, 80 75 C,D
FINC1001,
FINC1002
6, 6 70, 70 70 C,C
Table: 3
TASK 2- NORMALIZATION TABLE IN FIRST NORMAL FORM
Normalization process- It is based on the systematic approach in order to support for
decomposing database tables to eliminate any kind of redundancy or repetition (Kobyzev, Prince
and Brubaker, 2020). This type of process is becoming consider as important in database that
help for including undesirable characteristics such as update, deletion, insertion and Anomalies.
On the other hand, it is also used as multi-step process that can puts data in the form of tables,
removing duplicated data from relational database table.
Normalise the table in 1-NF
Relation Student_head is not in 1 NF because of multi-valued attributes STU_subject
School_phoneNo STU_Subject
9054 1321 Accounting Principles, Business Law,
Quantitative Method
6

9067 1835 Introduction to Programming, Database
Fundamentals
9067 1835 Introduction to Programming, Introduction to
Information Systems
9054 1321 Introduction to Microeconomics,
Principles of Finance
Table: 1
Conversion to First Normal Form
School_phoneNo STU_Subject
9054 1321 Accounting Principles,
9054 1321 Business Law,
9054 1321 Quantitative Method
9067 1835 Introduction to Programming,
9067 1835 Database Fundamentals
9067 1835 Introduction to Programming,
9067 1835 Introduction to Information Systems
9054 1321 Introduction to Microeconomics,
9054 1321 Principles of Finance
Table: 2
Relation Subject Lecture is not in 1 NF because of multi-valued attributes SUBJECT
7

CREDITPOINT, STU_SUBJECTMARK , STU_ SUBJECTGRADE, SUBJECT
CODE
Subject Lecturer table-
SUBJECT
CODE
SUBJECT
CREDITPOINT
STU
SUBJECTMARK
STU
AVERAGEMARK
STU
SUBJECTGRADE
ACCT1001,
BLAW1002,
QUAN1003
6, 6, 6 60, 70, 80 70 P,C,D
BIT1001,
BIT100
12, 6 50, 60 55 P,P
BIT1001,
BIT1003
12, 6 70, 80 75 C,D
FINC1001,
FINC1002
6, 6 70, 70 70 C,C
Table: 3
Conversion in First normal form
8

SUBJECT
CODE
SUBJECT
CREDITPOINT
STU
SUBJECTMARK
STU
AVERAGEMARK
STU
SUBJECTGRADE
ACCT1001 6 60 70 P
BLAW1002 6 70 C
QUAN1003 6 80 D
BIT1001 12 50 55 P
BIT100 6 60 P
BIT1001, 12 70 75 C
BIT1003 6 80 D
FINC1001, 6 70 70 C
FINC1002 6 70 C
Table: 4
Discuss the solution and explain reason why table was not in first Normal form
On the basis of 1NF conversion solution, it has been identified that there is existence of
repeated group values in the Student_head table like STU_subject. In this way, it was not in first
Normal form (Köhler and Link, 2018). On the other hand, it is applied the actions and changes
made in table and then turn into first Normal form.
On the basis of 1 NF conversion Solution, it has been identified that there is existence of
repeated group values in the Student_Lecture table such as SUBJECT_CREDITPOINT,
STU_SUBJECTMARK , STU_ SUBJECTGRADE, SUBJECT_CODE. In this way, it was not in
first Normal form. On the other hand, it is applied the actions and changes made in table and then
turn into first Normal form.
9

Changes made to turn into First Normal Form
As per given Student table, it would have done some changes so that it will turn into first Normal
form as follows:-
 It must be eliminated the repeating group (Wu and He, 2018).
 It may develop as a proper primary key, which is uniquely identified attribute values-
Rows (Functional dependency)
TASK 3- NORMALIZATION TABLE IN SECOND NORMAL FORM
Normalise table in second normal form
When combining two table Student table and “Subject_literature”
In order to assume Subject_code as primary in Subject_literature table so that it help for
eliminating redundancy in 2-NF
SUBJECT_CODE STU_SUBJECTMARK STU_name STU_School
ACCT1001 60 John Business
BLAW1002 70 Nick IT & engineering
QUAN1003 80 William IT & engineering
BIT1001 50 Kim Business
BIT100 60 - -
BIT1001, 70 - -
BIT1003 80 - -
FINC1001, 70 - -
FINC1002 70 - -
Subject_Code is a primary key in the subject_Literature table which has been established a
relation with student table. It means that SUBJECT_CODE show as foreign key into Student
table.
10

Discuss the solution and explain reason as why table was not in 2-NF
If a proper subset of candidate key determines non-prime attribute, it is known as partial
dependency. 2-NF removal of partial dependencies. In 1-NF, it exits the partial dependency so
that it was not consider as 2-NF. Afterwards, it remove the partial dependent attribute from
relation by placing them in a new relation.
In above 2-NF, STU_SUBJECTMARK would a non-prime attribute as it does not
completely belong to one only candidate key {SUBJECT_CODE, STU_NAME}
But SUBJECT_CODE-> STU_SUBJECTMARK, i.e. STU_SubjectMark is dependent on the
SUBJECT_CODE. It means that represent as partial dependency and so that it is conversion of
2_NF, split a table into two tables such as:-
SUBJECT_CODE STU_SUBJECTMARK
ACCT1001 60
BLAW1002 70
QUAN1003 80
BIT1001 50
BIT100 60
BIT1001, 70
BIT1003 80
FINC1001, 70
FINC1002 70
STU_name STU_School
John Business
Nick IT & engineering
William IT & engineering
Kim Business
- -
11

- -
- -
- -
- -
Changes made to turn into second normal form
 Table exits in 1-NF
 It does not include partial dependencies, every attributes are dependent on the portion of
primary key.
 Still, it is possible to exhibit transitive dependency when attributes may be functionally
dependent on non-key attributes.
If table is 1-NF and why it is in 1-NF
If table is 1-NF means that already eliminate all repeated groups within student table.
Therefore, it become easier for converting into 2NF with the help of Primary key in student
table.
12