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 a...Read More

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

TASK 4- NORMALIZATION TABLE IN THIRD NORMAL FORM
Normalise table in Third normal form
SUBJECT_CODE STU_SUBJECTMARK
ACCT1001 60
BLAW1002 70
QUAN1003 80
BIT1001 50
BIT100 60
BIT1001, 70
BIT1003 80
FINC1001, 70
FINC1002 70
SUBJECT_CODE STU_name STU_School
ACCT1001 John Business
BLAW1002 Nick IT & engineering
QUAN1003 William IT & engineering
BIT1001 Kim Business
BIT100 - -
BIT1001, - -
BIT1003 - -
FINC1001, - -
FINC1002 - -
13

Discuss the solution and explain reason as why table was not in 3-NF
For Above relation,
 SUBJECT_CODE-> STU_SUBJECTMARK
 STU_SUBJECTMARK -> STU_NAME
So, STU_NAME is transitively dependent on SUBJECT_CODE. It violates third Normal form
and not in 3-NF.
In order to convert into third Normal form, it will be decompose relation
Student (STU_name, STU_school)
Subject_literature (SUBJECT_CODE, STU_SUBJECTMARK)
Changes made to turn into third normal form
 Table Exit in 2-NF
 Create a separate tables to eliminate or remove transitive functional dependencies
 Determine any kind of additional attributes that needed in new tables
If the table is in the third normal form, explain why it is in the third normal form
If a table in the 3-NF which means that have less redundancy than those in 1-NF, it may
still suffer from any update anomalies. In 3 NF, there is no transitive dependency for non-prime
attributes and also exist in 2-NF.
Whenever, it has been done necessary changes according to the conversion of 3-NF.
Therefore, it can easily satisfied the condition of 3-NF.
TASK 5- ERD
ERD is also known as Entity relationship diagram or ER model. It is a kind of structural
diagram which is designed and used for designing a database. It consists of visuals, symbols and
connectors that helps in visualizing two important information and relationship between them.
Entity relationship diagram helps in identifying main entities of database (Alattar and Sali,
2020). Main attributes of each entity and relationship between two entities so that database can
be developed. Below ER diagram will help in understanding Entity relationship diagram.
14

Above entity relationship diagram consist of 3 main entities that are student, subject and
school. Each of these entities have their own attributes and are inter-related to each other that
helps in explaining relationship between them. Relationship in above diagram has been
explained with the help of a diamond, entities have been explained with the help of rectangle and
attributes have been explained with the help of oval shapes.
CONCLUSION
From the assessment it has been identified that in order to store data within database in
accurate manner it extremely important to normalize data and reduce any kind of redundancy
present in the table so that accuracy of the table can be maintained. In order to normalize tables,
it is important to first of convert table into first normal form then into second normal form and
then to third normal form. It has also been summarized that in order to normalize a table it is
important to first of all reduce functional redundancy then reduce transitional redundancy. For
proper and accurate normalization, it is important to follow proper normalization process
following all the main criteria’s of database so that none of the data is compromised.
15

REFERENCES
Book and Journals
Alattar, M. and Sali, A., 2020, February. Functional dependencies in incomplete databases with
limited domains. In International Symposium on Foundations of Information and
Knowledge Systems (pp. 1-21). Springer, Cham.
Kobyzev, I., Prince, S. and Brubaker, M., 2020. Normalizing flows: An introduction and review
of current methods. IEEE Transactions on Pattern Analysis and Machine Intelligence.
Köhler, H. and Link, S., 2018. SQL schema design: foundations, normal forms, and
normalization. Information Systems, 76, pp.88-113.
Ordonez, C., Al-Amin, S.T. and Bellatreche, L., 2020, December. An ER-Flow Diagram for Big
Data. In 2020 IEEE International Conference on Big Data (Big Data) (pp. 5795-5797).
IEEE.
Vo, M.H.L. and Hoang, Q., 2020. Transformation of UML class diagram into OWL
Ontology. Journal of Information and Telecommunication, 4(1), pp.1-16.
Wu, Y. and He, K., 2018. Group normalization. In Proceedings of the European conference on
computer vision (ECCV) (pp. 3-19).
16