Database Fundamentals

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