UNIT II

DATABASE DESIGN

Entity-Relationship model – E-R Diagrams – Enhanced-ER Model – ER-to-Relational Mapping –

Functional Dependencies – Non-loss Decomposition – First, Second, Third Normal Forms,

Dependency Preservation – Boyce/Codd Normal Form – Multi-valued Dependencies and

Fourth Normal Form – Join Dependencies and Fifth Normal Form

2.1 ENTITY-RELATIONSHIP MODEL (ER Model):

∙ The ER model defines the conceptual view of a database.

∙ It works around real-world entities and the associations among them.
∙ At view level, the ER model is considered a good option for designing databases.

Entity:
∙ An entity can be a real-world object that can be easily identifiable.
∙ Example:
◦ In a school database, students, teachers, classes, and courses offered can be
considered as entities.
∙ All entities have some attributes or properties that give them
their identity. ∙ An entity set is a collection of similar types of
entities.
∙ An entity set may contain entities with attribute sharing similar values.
∙ Example:
◦ A Students set may contain all the students of a school; likewise a Teachers set
may contain all the teachers of a school from all faculties.

Attributes:
∙ Entities are represented by means of their properties, called attributes.
∙ All attributes have values.
∙ Example:
◦ a student entity may have name, class, and age as attributes.
∙ There exists a domain or range of values that can be assigned
to attributes. ∙ Example:
◦ a student's name cannot be a numeric value. It has to be alphabetic. A student's
age cannot be negative, etc.

Types of Attributes:
∙ Simple attribute − Simple attributes are atomic values, which cannot be divided
further. Example: a student's phone number is an atomic value of 10 digits.
∙ Composite attribute − Composite attributes are made of more than one simple attribute.
1
Example: a student's complete name may have first_name and last_name.
∙ Derived attribute − Derived attributes are the attributes that do not exist in the
physical database, but their values are derived from other attributes present in the
database. Example: average_salary in a department should not be saved
Directly in the database, instead it can be derived.
Example: age can be derived from data_of_birth.
∙ Single-value attribute − Single-value attributes contain single value.
Example: Social_Security_Number.
∙ Multi-value attribute − Multi-value attributes may contain more than
one values. Example: a person can have more than one phone
number,email_address, etc.
These attribute types can come together in a way like −
∙ simple single-valued attributes
∙ simple multi-valued attributes
∙ composite single-valued attributes
∙ composite multi-valued attributes
Keys:
Key is an attribute or collection of attributes that uniquely identifies an entity
among entity set.
Example: the roll_number of a student makes him/her identifiable among students.

∙ Super Key − A set of attributes (one or more) that collectively identifies an entity
in an entity set.
Example: consider the student relation, student (rollno,Name ,Age) is a super
key.
∙ Candidate Key − A minimal super key is called a candidate key. An entity set may
have more than one candidate key.
Example: consider the car relation car
(license_no,engine_serial_no,make,model,year)
The candidate keys are license_no and engine_serial_no
∙ Primary Key − An attribute which is unique and not null, can identify an instance
of the entity set is termed as primary key. A primary key is one of the candidate
keys chosen by the database designer to uniquely identify the entity set.
Example: consider the employee relation, Employee
(eno,ename,doj,sal,job,dno) in this eno is the primary key.
∙ Foreign key: An attribute in one relation whose value matches the primary key in
some other relation is called a foreign key.
Example: consider the two relations employee and dept,
Employee(eno,ename,doj,sal,job,dno)
Dept(dno,dname,dloc)
In the above relations, the primary key of dept 'dno' is present in the
employee relation so that employee relation dno is known as foreign
key.

Relationship:
∙ The association among entities is called a relationship.
∙ Example:
 ◦ An employee works_at a department, a student enrolls in a course. Here,
Works_at and Enrolls are called relationships.

2
Relationship Set:
A set of relationships of similar type is called a relationship set. Like entities,
a relationship too can have attributes. These attributes are called descriptive
attributes.

Degree of Relationship:
The number of participating entities in a relationship defines the degree of the
relationship. ∙ Binary = degree 2
∙ Ternary = degree 3
∙ n-ary = degree n

Mapping Cardinalities:
Cardinality defines the number of entities in one entity set, which can be
associated with the number of entities of other set via relationship set.
∙ One-to-one − One entity from entity set A can be associated with at most one entity
of entity set B and vice versa.

associated with more than one entities of entity set B

however an entity
from entity set B,
can be associated
with at most one
entity.
 ∙ Many-to-one − More than one entities from entity set A can be associated with at
most one entity of entity set B, however an entity from entity set B can be
associated with more than one entity from entity set A.

∙ Many-to-many − One entity from A can be associated with more than one entity
from B and vice versa.

2.2 E-R DIAGRAMS:

Entities are represented by means of rectangles. Rectangles are named with the
entity set they represent.

Attributes are the properties of entities. Attributes are represented by means of

ellipses. Every ellipse represents one

attribute and is
directly connected to
its entity (rectangle).
 If the attributes are composite, they are further divided in a tree like structure.
Every node is then connected to its attribute. That is, composite attributes are
represented by ellipses that are connected with an ellipse.

Multivalued attributes are depicted by double ellipse.

Derived attributes are depicted by dashed ellipse.

Relationship
 Relationships are represented by diamond-shaped box. Name of the relationship
is written inside the diamond-box. All the entities (rectangles) participating in a
relationship, are connected to it by a line.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary

relationship. Cardinality is the number of instance of an entity from a relation that
can be associated with the relation.
∙ One-to-one − When only one instance of an entity is associated with the relationship, it is
5
marked as '1:1'.

∙ One-to-many − When more than one instance of an entity is associated with a

relationship, it is marked as '1:N'.

∙ Many-to-one − When more than one instance of entity is associated with the
relationship, it is marked as 'N:1'.
 ∙ Many
-to
many
− The following image reflects that more than one instance of an entity on the
left and more than one instance of an entity on the right can be associated with
the relationship. It depicts many-to-many relationship.

Participation Constraints:

∙ Total Participation − Each entity is involved in the relationship. Total

participation is represented by double lines.
∙ Partial participation − Not all entities are involved in the relationship. Partial
participation is

represe
nted by
single
lines.

6
2.3 ENHANCED-ER MODEL (EER Model):

∙ The EER model includes all the basic modeling concepts of the ER model. ∙ In
addition, it includes the concepts of subclass and superclass and the related
concepts of specialization and generalization.
∙ Another concept included in the EER model is that of a category or union type ,
which is used to represent a collection of objects (entities) that is the union of
objects of different entity types.
∙ EER is used to model concepts more accurately than the ER diagram.

Subclasses, Superclasses, and Inheritance:

 ∙ An entity type has numerous subgroupings or subtypes of its entities that are
meaningful and need to be represented explicitly.
∙ Example:
◦ the entities that are members of the EMPLOYEE entity type may be distinguished
further into SECRETARY, ENGINEER, MANAGER, TECHNICIAN,
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE, and so on.
∙ Subgroupings are called as a subclass or subtype of the EMPLOYEE entity type,
and the EMPLOYEE entity type is called the superclass or supertype.
∙ An entity can be included as a member of any number of subclasses.
∙ Example:
◦ a salaried employee who is also an engineer belongs to the two subclasses
ENGINEER and SALARIED_EMPLOYEE of the EMPLOYEE entity type.
∙ An important concept associated with subclasses (subtypes) is that of type
inheritance. ∙ The type of an entity is defined by the attributes it possesses and the
relationship types in which it participates.
∙ An entity that is a member of a subclass inherits all the attributes of the entity as a
member of the superclass.
∙ The entity also inherits all the relationships in which the superclass

participates. Specialization:

∙ Specialization is the process of defining a set of subclasses of an entity type; this

entity type is called the superclass of the specialization.
∙ Example:
◦ the set of subclasses {SECRETARY, ENGINEER, TECHNICIAN} is a
specialization of the superclass EMPLOYEE that distinguishes among
employee entities based on the job type of each employee entity.
∙ There may be several specializations of the same entity type based on different
distinguishing characteristics.
∙ Example: another specialization of the EMPLOYEE entity type may yield the set of
subclasses {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}; this
specialization distinguishes among employees based on the method of pay.
∙ The subclasses that define a specialization are attached by lines to a circle that
represents the specialization, which is connected in turn to the superclass.

7
The subset symbol on each line connecting a subclass to the circle indicates the
direction of the superclass/subclass relationship.
∙ Attributes that apply only to entities of a particular subclass—such as TypingSpeed
of SECRETARY—are attached to the rectangle representing that subclass.
These are called specific attributes (or local attributes) of the subclass.
∙ A subclass can participate in specific relationship types, such as the
HOURLY_EMPLOYEE subclass participating in the BELONGS_TO
relationship.

∙ The specialization process allows us to do the following:

■ Define a set of subclasses of an entity type
■ Establish additional specific attributes with each subclass
 ■ Establish additional specific relationship types between each
subclass and other entity types or other subclasses

Generalization:

The generalization process can be viewed as being functionally the inverse of

the specialization process.

8
we can view {CAR, TRUCK} as a specialization of VEHICLE, rather than
viewing VEHICLE as a generalization of CAR and TRUCK.

Constraints and Characteristics of Specialization and Generalization Hierarchies:

∙ Several specializations can be defined on the same entity type.In such a case,
entities may belong to subclasses in each of the specializations.
∙ A specialization may also consist of a single subclass only.
∙ In some specializations we can determine exactly the entities that will become
members of each subclass by placing a condition on the value of some attribute
 of the superclass. Such subclasses are called predicate-defined (or condition-
defined) subclasses

9
∙ Example:
◦ If the EMPLOYEE entity type has an attribute Job_type we can specify the
condition of membership in the SECRETARY subclass by the condition
(Job_type = ‘Secretary’), which we call the defining predicate of the subclass.
◦ A predicate-defined subclass is indicated by writing the predicate condition next
to the line that connects the subclass to the specialization circle.
∙ If all subclasses in a specialization have their membership condition on the same
attribute of the superclass, the specialization itself is called an attribute-defined
specialization, and the attribute is called the defining attribute of the
specialization.
∙ Two other constraints may apply to a specialization.
1.disjointness (or disjointedness) constraint, which means that an
entity can be a member of at most one of the subclasses of the
specialization.
2. completeness (or totalness) constraint, which may be total or partial.

A total specialization constraint specifies that every entity in the superclass

must be a member of at least one subclass in the specialization.
There are four possible constraints on specialization:
■ Disjoint, total
■ Disjoint, partial
■ Overlapping, total
■ Overlapping, partial
 10
2.4 ER-TO-RELATIONAL MAPPING:
∙ ER Model, when conceptualized into diagrams, gives a good overview of entity-
relationship, which is easier to understand.
∙ ER diagrams can be mapped to relational schema, that is, it is possible to create
relational schema using ER diagram..
∙ There are several processes and algorithms available to convert ER Diagrams into
Relational Schema. Some of them are automated and some of them are manual.
We may focus here on the mapping diagram contents to relational basics.
∙ ER diagrams mainly comprise of −
❖ Entity and its attributes
❖ Relationship, which is association among entities.
EXAMPLE: College Management System
1. A college contains many departments
2. Each department can offer any number of courses
3. Many instructors can work in a department
4. An instructor can work only in one department
5. For each department there is a Head
6. An instructor can be head of only one department
7. Each instructor can take any number of courses
8. A course can be taken by only one instructor
9. A student can enroll for any number of courses
10. Each course can have any number of students
Step 1 : Identify the Entities
What are the entities here?
From the statements given, the entities are
1. Department
2. Course
3. Instructor
4. Student
Stem 2 : Identify the relationships
1. One department offers many courses. But one particular course can be offered by
only one department. hence the cardinality between department and course is One to
Many (1:N) 2. One department has multiple instructors . But instructor belongs to only
one department. Hence the cardinality between department and instructor is One to
Many (1:N)
3. One department has only one head and one head can be the head of only one
department. Hence the cardinality is one to one. (1:1)
11
4. One course can be enrolled by many students and one student can enroll for many
courses. Hence the cardinality between course and student is Many to Many (M:N)

5. One course is taught by only one instructor. But one instructor teaches many courses.
Hence the cardinality between course and instructor is Many to One (N :1)
 Step 3: Identify the key attributes

1. "Departmen_Name" can identify a department uniquely. Hence Department_Name is

the key attribute for the Entity "Department".
2. Course_ID is the key attribute for "Course" Entity.
3. Student_ID is the key attribute for "Student" Entity.
4. Instructor_ID is the key attribute for "Instructor" Entity.
5.
Step 4: Identify other relevant attributes

1. For the department entity, other attributes are location

2. For course entity, other attributes are course_name,duration
3. For instructor entity, other attributes are first_name, last_name, phone
4. For student entity, first_name, last_name, phone
5.
Step 5: Draw complete ER diagram
12
2.5 FUNCTIONAL DEPENDENCIES:

A functional dependency is a constraint between two sets of attributes from the

database. ∙ A functional dependency between two sets of attributes X and Y that
are subsets of R specifies a constraint on the possible tuples that can form a
relation state r of R. ∙ The constraint is that, for any two tuples t1 and t2 in r that
have t1[X] = t2[X], they must also have t1[Y]=t2[Y].
∙ It requires that the value for a certain set of attributes determines uniquely the value
for another set of attributes.

In a given relation R, X and Y are attributes. Attributes Y is functionally dependent

on attribute X if each value of X determines exactly one value of Y, which is
represented as X → Y
i.e., “X determines Y” or “Y is functionally dependent on X”
X → Y does not imply Y → X
∙ Example:
◦ In a student relation the value of an attribute “Marks” is known then the value of
an attribute “Grade” is determined since
Marks → Grade.
◦ Consider the relation schema EMP_PROJ ; from the semantics of the attributes
and the relation, we know that the following functional dependencies should
hold:
a. Ssn→Ename
b. Pnumber →{Pname, Plocation}

c. {Ssn, Pnumber}→Hours
These functional dependencies specify that (a) the value of an employee’s
Social Security number (Ssn) uniquely determines the employee name (Ename), (b)
the value of a project’s number (Pnumber) uniquely determines the project name
(Pname) and location (Plocation), and (c) a combination of Ssn and Pnumber values
 uniquely determines the number of hours the employee currently works on the project
per week (Hours).

13
Types
(a) Full functional dependency (b)
Partial functional dependency
(c) Transitive functional
dependency
 14
(a) Full dependencies
∙ In a relation R, X and Y are attributes. X functionally determines Y. Subset of X
should not functionally determine Y.
∙ Example:

Student_no Course_no

Marks

In the above example marks is fully functionally dependent on student_no

and course_no together and not on subset of {student_no, course_no}.
This means marks cannot be determined either by student_no or course_no
alone. It can be determined only using student_no and course_no together.
Hence marks is fully functionally dependent on {student_no, course_no}.

(b) Partial Dependencies

(c)
∙ Attribute Y is partially dependent on the attribute X only if it is dependent on a subset
of attribute X.
∙ Example:
◦ course_name, Instructor_name are partially dependent on composite attributes
{student_no, course_no} because course_no alone defines course_name,
Instructor_name.

(d) Transitive Dependencies

∙ X, Y and Z are 3 attributes in the relation R.

X
Y
X

∙ Example:
YZZ

◦ grade depends on marks and in turn make depends on {student_no course_no},

hence Grade depends fully transitively on {student_no & course_no}.

Use of Functional Dependencies

❖ Test relations to see if they are legal under a given set of functional dependencies. If a
relation r is legal under a set F of functional dependencies, we say that r satisfies
F.
❖ Specify constraints on the set of legal relations.
 ❖ A specific instance of a relation schema must satisfy a functional dependency
even if the functional dependency does not hold on all legal instances.
For example, a specific instance of loan-schema may by chance satisfy
Loan_no → customer_name

15
❖ A functional dependency is trivial if it is satisfied by all instance of a relation. For
example, A → A is satisfied by all relations involving attribute A. AB → A is

In general a functional dependency of the form α→β is trivial if β ⊆ α

satisfied by all relations involving attribute A.

Closure of a Set of Functional Dependencies:

∙ The set of all functional dependencies logically implied by F is the

closure of F. ∙ It is denoted by F+.
∙ We can find all of F+ by applying Armstrong‘s Axioms

Armstrong’s Axioms:

William W. Armstrong established a set of rules which can be used to infer

the functional dependencies in a relational database :
∙ Reflexivity rule: If A is a set of attributes, and B is a set of attributes that are
completely contained in A, the A implies B.
∙ Augmentation rule: If A implies B, and C is a set of attributes, then if A implies B,
then AC implies BC.
∙ Transitivity rule: If A implies B and B implies C, then A implies C.
These can be simplified if we also use:
∙ Union rule: If A implies B and A implies C, the A implies BC.
∙ Decomposition rule: If A implies BC then A implies B and A implies C.
∙ Pseudotransitivity rule: If A implies B and CB implies D, then

AC implies D. Example:

Consider the schema R = (A, B, C, G, H, I) and the set F of functional dependencies

{A→ B, A→ C, CG → H, CG → I , B → H}. F+ can be found as follows:
∙ A → H. Since A → B and B → H hold, we apply the transitivity rule.
∙ CG → HI. Since CG → H and CG → I , the union rule implies that CG →HI
. ∙ AG → I. Since A→C and CG → I , the pseudotransitivity rule implies that
AG → I holds.

Anomalies:
Anomalies can be classified into insertion anomalies, deletion anomalies, and
modification anomalies.

Insertion Anomalies:
 Insertion anomalies can be differentiated into two types, illustrated by the
following examples based on the EMP_DEPT relation:
■ To insert a new employee tuple into EMP_DEPT, we must include either the
attribute values for the department that the employee works for, or NULLs . For
example, to insert a new tuple for an employee who works in department number 5, we
must enter all the attribute values of department 5 correctly so that they are consistent
with the corresponding values for department 5 in other tuples in EMP_DEPT.
■ It is difficult to insert a new department that has no employees as yet in the
EMP_DEPT relation. The only way to do this is to place NULL values in the attributes
for employee. This violates the entity integrity for EMP_DEPT because Ssn is its
primary key.
16
Deletion Anomalies:
The problem of deletion anomalies is related to the second insertion anomaly
situation. If we delete from EMP_DEPT an employee tuple that happens to represent
the last employee working for a particular department, the information concerning that
department is lost from the database.
This problem does not occur in the database were tuples are stored separately.

Modification Anomalies:
In EMP_DEPT, if we change the value of one of the attributes of a particular
department— say, the manager of department 5—we must update the tuples of all
employees who work in that department; otherwise, the database will become
inconsistent.
If we fail to update some tuples, the same department will be shown to have
two different values for manager in different employee tuples, which would be wrong.
 17
2.6 DECOMPOSITION
A single decomposition schema R={A ,A ,…..A } that includes all the attributes
1 2 n

of the database. Every attributes name is unique using the FDs, the algorithms
decomposes the universal relation schema R into a set of relation schema D={R , R , 1 2

….R } that will become the relational database schema D is called decomposition of
m

R.
We must make sure that each attribute in R will appear in atleast one relation
schema Ri in the decomposition so that no attributes are lost formally.
m
i=1
This is called attribute preservation condition of decomposition.

Properties of Decomposition
(1) Dependency preservation.
(2) Lossless (or non additive) join property.

Dependency Preservation
If each functional dependency X → Y specified in F either appeared directly in
one of the relation schemas R in the decomposition D or could be from the
i

dependencies that appear in some R . Informally this Dependency preservation

i

condition.
Definition
Given a set of dependencies F on R, the projection of F on R i, denoted by Π Ri(F)
where Ri-subset of R, is the set of dependencies X → Y in F+ such that the attributes in XUY are all
condition in Ri.
Hence the projection of F on each relation schema Ri in the decomposition D is
the set of FDs in F , such that all their left and RHS attributes are in Ri.
+

A decomposition D={R , R …R } of R is dependency preserving with respect

1 2 m

to F if the union of the properties of F on each Ri on D is equivalent to F.

(Π R ,(R))∪.....∪(Π R (F))) =F
(ie)
+ +

im

Lossless (non additive) Joins

This property ensures that no spurious tuples are generated when a natural join
operation is applied to the relations in the decomposition.
A decomposition D={R , R …R }of R has the lossless (non additive) join
1 2 m

property with respect to the set of dependencies F on r if, for every relation state r of R
that satisfies F, the following holds, where (πR (r), ……πR (r)) = r is the natural join
i m

of all the relations in D.

2.7 NORMALIZATION:
∙ Normalization is a process of organizing the data in the database.
∙ It is a systematic approach of decomposing tables to eliminate data
redundancy. ∙ It was developed by E. F. Codd.
∙ Normalization is a multi-step process that puts the data into a tabular form by
removing the duplicate data from the relation tables.
∙ It is a step by step decomposition of complex records into simple records.
 ∙ It is also called as Canonical Synthesis.

18
∙ It is the technique of building database structures to store data.

Definition of Normalization
“Normalization is a process of designing a consistent database by minimizing
redundancy and ensuring data integrity through decomposition which is
lossless.”

Features of Normalization
∙ Normalization avoids the data redundancy.
∙ It is a formal process of developing data structures.
∙ It promotes the data integrity.
∙ It ensures data dependencies make sense that means data is logically stored. ∙ It
eliminates the undesirable characteristics like Insertion, Updation and Deletion
Anomalies.

Types of Normalization
Following are the types of Normalization:
1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. Fourth Normal Form
5. Fifth Normal Form
6. BCNF (Boyce – Codd Normal Form)
7. DKNF (Domain Key Normal Form)

FIRST, SECOND, THIRD NORMAL FORMS:

First Normal Form (1NF):

∙ The domain of an attribute must include only atomic values.
∙ 1NF disallows set of values, a tuples of values ie., it disallows composite and
Multivalued attribute.
Eg. DEPARTMENT relational schema whose primary key is DNUMBER.
Considering DLOCATIONS attribute. Each department can have a number of
locations.

1) The domain of DLOCATIONS contains atomic values, but some tuples can
have a set of these values. Therefore DLOCATIONS is not functionally dependent on
DNUMBER.
DNAME DNUMBER DMGRSSN DLOCATIONS

Research 5 334 Bellaire

Research 5 334 Alaska

Research 5 334 Newyork

19
2) Domain of DLOCATIONS has set of values and hence non-atomic. But
DNUMBER→ DLOCATIONS exist.
DNAME DNUMBER DMGRSSN DLOCATIONS

Research 5 334 {Bellaire,Alaska,Newyork}

Normalisation to achieve 1NF:

∙ Remove the attribute DLOCATIONS and place it in a separate relation along with the
primary key DNUMBER of the department. ie., primary key is the combination of
DNUMBER,DLOCATION}. So a distinct tuple exist for each location
DNAME DNUMBER DMGRSSN

Research 5 334

DNUMBER DLOCATIONS

5 Bellaire

5 Alaska

5 Newyork

Test for First Normal Form:

Relation should not have non-atomic attributes or nested relation.
Remedy
Form new relations for each non-atomic attribute or nested relation.

Second Normal Form:(2NF)

∙ 2NF is based on Full Functional Dependency.

Full Functional Dependency:

A FD X→ Y is a full functional dependency, if removal of any attribute A
from X, {X-{A}} does not functionally determine Y. Eg. {SSN, PNUMBER} →
HOURS

Partial Functional dependency:

A FD X→Y is a partial dependency if some attribute A ϵ X can be removed
from X and the dependency still holds ie., A ϵ X, X-{A} →Y Eg.,{SSN}→ Ename

Statement of Second normal form:

 A table is said to be in 2NF if both the following conditions hold:
▪ Table is in 1NF (First normal form)
▪ No non-prime attribute is dependent on the proper subset of any
candidate key of table.
An attribute that is not part of any candidate key is known as non-prime attribute.
SSN Pnumber Hours Enmae Pname PLocation

20
E101 P27 90 Raju Systems ABC

21

E305 P27 10 Raju Finance EFG

E508 P51 Null Nithi Admin XYZ

E101 P51 101 Nithi Systems ABC

E101 P20 60 Anu Systems ABC

E508 P27 72 Benny Admin XYZ

Pname & PLocation violates 2NF. FD2 and FD3 are partially dependent on primary key.
Normalisation to achieve 2NF:
Test for Second Normal Form

Ename,
 For relations where primary key contains multiple attributes, non-key or non-
prime attribute should not be functionally dependent on a part of primary key.

Remedy:
Decompose and set up a new relation for each partial key with its dependent
attribute(s). Make sure to keep a relation with the original primary key and any
attributes that are fully functionally dependent on it.

Third Normal Form (3NF):

It is based on Transitive dependency.

22
Transitive Dependency:

A functional dependency X→ Y in R is a transitive dependency if there is a set

of attributes Z and both X→ Z and Z→ Y hold.

Statement of Third Normal Form:

A table design is said to be in 3NF if both the following conditions hold:
∙ Table must be in 2NF
should be r
e
m
o
v
e
d
.

An attribute that is not part of any candidate key is known as non-

prime attribute. Test for Third Normal Form:

A relation should not have non key attribute functionally determined by

another non key attribute ie., there should be no transitive dependency of a non key
attribute on the primary key. Guidelines for Converting a Table to 3NF

• Find and remove non-key attributes that are functionally dependent on the
attributes that are not the primary key. Place them in a different table.
• Group the remaining attributes.

BOYCE/CODD NORMAL FORM:

 It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is
stricter than 3NF. A 3NF table which does not have multiple overlapping candidate
keys is said to be in BCNF. For a table to be in BCNF, following conditions must be
satisfied:
∙ R must be in 3rd Normal Form
∙ For each functional dependency ( X → Y ), X should be a super Key.

Consider the table Project given below.

Project
Ecode Name Proj Code Hours

E1 Veronica P2 48

23
E2 Anthony P5 100

E3 Mac P6 15

E4 Susan P3 250

E4 Susan P5 75

E1 Veronica P5 40

ECode+ProjCode is the primary key. You will notice that Name+ProjCode

could be chosen as the primary key and hence, is a candidate key.
* Hours is functionally dependent on ECode+ProjCode.
* Hours is also functionally dependent on Name+ProjCode.
* Name is functionally dependent on Ecode.
* ECode Is functionally dependent on Name.
*
You will notice that this table has:

• Multiple candidate keys, that is ECode+ProjCode and

Name+ProjCode. • The candidate keys are composite.
• The candidate keys overlap since the attribute - ProjCode is common.
This is the case of the Boyce-Codd Normal form. This is in third NF. The only
non key item is Hours, which is dependent on the whole key that is ECode + ProjCode
or Name+ ProjCode. ECode and Name are determinants since they are functionally
dependent on the each other. However, they are not candidate keys by themselves. As
per BCNF, the determinants have to be candidate keys.

Guidelines for Converting a Table to BCNF

 • Find the remove the overlapping candidate keys. Place the part of the
candidate key and the attribute it is functionally dependent on, in a different
table.
• Group the remaining items into a table.
Hence, remove Name and ECode and place them in a different table. You will
arrive at the following tables.

Employee
Ecode Name

E1 Veronica

E2 Anthony

E3 Mac

E4 Susan

E4 Susan

E1 Veronica

Project
Proj Code Hours

P2 48

P5 100

P6 15

P3 250

P5 75

24
P5 40

Employee – Dept Mapping Table:

Ecode

E1
Proj Code P2
 25

E2

E3

E4

E4

E1

P5 P6 P3 P5 P5

MULTI-VALUED DEPENDENCIES AND FOURTH NORMAL

FORM: For a table to satisfy the Fourth Normal Form, it should satisfy the following
two conditions:
1.It should be in the Boyce-Codd Normal Form.
2.And, the table should not have any Multi-valued Dependency.

Multi-valued Dependency:
A table is said to have multi-valued dependency, if the following conditions are true,
1.For a dependency A → B, if for a single value of A, multiple value of B exists,
then the table may have multi-valued dependency.
2.Also, a table should have at-least 3 columns for it to have a multi-valued
dependency. 3.And, for a relation R(A,B,C), if there is a multi-valued
dependency between, A and B, then B and C should be independent of each
other.

Pizza Delivery:
Restaurant Pizza Variety Delivery Area

A1 Pizza Thick Crust Springfield

A1 Pizza Thick Crust Shelbyville

A1 Pizza Thick Crust Capital City

A1 Pizza Stuffed Crust Springfield

A1 Pizza Stuffed Crust Shelbyville

A1 Pizza Stuffed Crust Capital City

Elite Pizza Thin Crust Capital City

 Elite Pizza Stuffed Crust Capital City

Vincenzo's Pizza Thick Crust Springfield

Vincenzo's Pizza Thick Crust Shelbyville

Vincenzo's Pizza Thin Crust Springfield

Vincenzo's Pizza Thin Crust Shelbyville

The {Restaurant} attribute depends on both Pizza Variety and Delivery Area. The
dependencies are: ∙ {Restaurant} {Pizza Variety}
∙ {Restaurant} {Delivery Area}

Varieties By Restaurant Delivery Areas By Restaurant

Restaurant Pizza Variety

A1 Pizza Thick Crust

Restaurant Delivery Area

A1 Pizza Springfield

26

A1 Pizza Stuffed CrustA1 Pizza Shelbyville

Elite Pizza Thin Crust

Elite Pizza Stuffed Crust

Vincenzo's Pizza Thick Crust

Vincenzo's Pizza Thin Crust

A1 Pizza Capital City

Elite Pizza Capital City

Vincenzo's Pizza Springfield

Vincenzo's Pizza Shelbyville

JOIN DEPENDENCIES AND FIFTH NORMAL FORM

Fifth normal form (5NF), also known as project-join normal form (PJ/NF)
is a level of database normalization designed to reduce redundancy.
A database is said to be in 5NF, if and only if,
∙ It's in 4NF
∙ If we can decompose table further to eliminate redundancy and anomaly, and when
we re join the decomposed tables by means of candidate keys, we should not be
losing the original data or any new record set should not arise.

Traveling Salesman Product Availability By Brand

Traveling Brand Product Type
Salesman

Jack Schneider Acme Vacuum Cleaner

Jack Schneider Acme Breadbox

Willy Loman Robusto Pruning Shears

Willy Loman Robusto Vacuum Cleaner

Willy Loman Robusto Breadbox

Willy Loman Robusto Umbrella Stand

Louis Ferguson Robusto Vacuum Cleaner

Louis Ferguson Robusto Telescope

Louis Ferguson Acme Vacuum Cleaner

Louis Ferguson Acme Lava Lamp

Louis Ferguson Nimbus Tie Rack

In that case, it is possible to split the table into three:

 27
Product Types By Traveling Salesman Brands By Traveling Salesman

Traveling Product Type

Salesman

Jack Schneider Vacuum Cleaner

Jack Schneider Breadbox

Willy Loman Pruning Shears

Willy Loman Vacuum Cleaner

Willy Loman Breadbox

Willy Loman Umbrella Stand

Louis Ferguson Telescope

Louis Ferguson Vacuum Cleaner

Louis Ferguson Lava Lamp

Louis Ferguson Tie Rack

Traveling Brand
Salesman

Jack Schneider Acme

Willy Loman Robusto

Louis Robusto
Ferguson

Louis Acme
Ferguson

Louis Nimbus
Ferguson

Product Types ByBrand

Brand Product Type

Acme Vacuum Cleaner

Acme Breadbox
Acme Lava Lamp

Robusto Pruning Shears

Robusto Vacuum Cleaner

Robusto Breadbox

Robusto Umbrella Stand

Robusto Telescope

Nimbus Tie Rack

28