Class notes about Database management system

Functional Dependency and Normalization
Functional Dependency
The attributes of a table is said to be dependent on each other when an attribute of a
table uniquely identifies another attribute of the same table.

Ie, The functional dependency is a relationship that exists between two
attributes. It typically exists between the primary key and non-key attribute
within a table.

It is represented by an arrow sign (→) that is, X→Y, where X functionally determines
Y. The left-hand side attributes determine the values of attributes on the right-hand side.
The left side of FD is known as a determinant, the right side of the production is
known as a dependent.


(determinant) X→Y (dependent)
For example: Suppose we have a student table with attributes: Stud_Id, Stud_Name,
Stud_Age. Here Stud_Id attribute uniquely identifies the Stud_Name attribute of
student table because if we know the student id we can tell the student name
associated with it. This is known as functional dependency and can be written as
Stud_Id->Stud_Name or in words we can say Stud_Name is functionally
dependent on Stud_Id.

If column A of a table uniquely identifies the column B of same table then it can
represented as A->B (Attribute B is functionally dependent on attribute A)

Types of Functional Dependencies
 Trivial functional dependency
 non-trivial functional dependency
 Multivalued dependency
 Transitive dependency

Trivial Functional Dependency
If a functional dependency X → Y holds, where Y is a subset of X, then it is called a
trivial FD.

, The dependencies like: A → A, B → B are also trivial.

Consider a table with two columns Employee_Id and Employee_Name.
{Employee_id, Employee_Name} → Employee_Id
is a trivial functional dependency as Employee_Id is a subset of
{Employee_Id, Employee_Name}.
Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are t
rivial dependencies too.
Non trivial functional dependency
If a functional dependency X->Y holds true where Y is not a subset of X then
this dependency is called non trivial Functional dependency.

An employee table with three attributes: emp_id, emp_name, emp_address.
The following functional dependencies are non-trivial:
emp_id -> emp_name (emp_name is not a subset of emp_id)
emp_id -> emp_address (emp_address is not a subset of emp_id)

Completely non trivial FD:
If a FD X->Y holds true where X intersection Y is null then this dependency is said to
be completely non trivial function dependency.


Multivalued dependency
Multivalued dependency occurs when there are more than
one independent multivalued attributes in a table.

For example: Consider a bike manufacture company, which produces two colors
(Black and white) in each model every year.

Bike_model Manuf_year colour

M1001 2007 black

M1001 2007 red

M1002 2008 black

M1002 2008 red

, Here columns manuf_year and color are independent of each other and dependent on
bike_model. In this case these two columns are said to be multivalued dependent on
bike_model. These dependencies can be represented like this:

bike_model ->> manuf_year


Transitive dependency in DBMS
A Transitive Functional Dependency is a type of functional dependency
which happens when the non- key attribute is indirectly formed by its
functional dependencies on the primary key attributes

When an indirect relationship causes functional dependency it is called Transitive
Dependency.
If P -> Q and Q -> R is true, then P-> R is a transitive dependency.
Let us consider the below table to understand this,



Student_ID Student_Name Dept DOB




S_0101_C Sname01 Computer_C 01-01-1999




T_0307_M Tname02 Maths_M 03-07-1998




U_0711_E Uname03 English_E 07-11-1997




In this table, the Student_ID column is the primary key. The values in the

Student_ID column are formed by the combination of the first letter from