Database Design Using Normalization

(Bottom-Up Approach)
Aim
To design a relational database using the bottom-up approach and apply normalization
(1NF, 2NF, 3NF, and BCNF) to eliminate redundancy and anomalies.

Theory
Bottom-Up Approach

In the bottom-up approach:

 We start by identifying individual data items (attributes).

 Group related attributes into relations.
 Apply normalization rules step-by-step.
 Define primary and foreign keys.
 Remove redundancy and anomalies.

This approach focuses on data elements first, then builds the database structure.

Problem Statement
Design a database for a Student Course Enrollment System.

Given Unnormalized Relation:

ENROLLMENT(
Student_ID, Student_Name, Dept_Name,
Course_ID, Course_Name, Instructor, Instructor_Phone,
Grade
)

Assumptions:

 A student can enroll in multiple courses.

 A course can have multiple students.
 Each course is handled by one instructor.
 Each instructor has one phone number.

Step 1: Unnormalized Form (UNF)

Student_ Student_Na Dept_Na Course_ Course_Na Instruct Instructor_Ph Grad
ID me me ID me or one e

Problems:

 Repeating course details for each student.

 Redundant instructor information.
 Update anomalies.

Step 2: First Normal Form (1NF)

Rule:

 Remove repeating groups.

 Ensure atomic values.
 Each cell contains a single value.

The table already contains atomic values → so it is in 1NF.

Primary Key:
(Student_ID, Course_ID)

Step 3: Second Normal Form (2NF)

Rule:

 Must be in 1NF.
 No partial dependency.
 Non-key attributes must depend on the entire primary key.

Partial Dependencies:

 Student_Name → depends only on Student_ID

 Dept_Name → depends only on Student_ID
 Course_Name → depends only on Course_ID
 Instructor → depends only on Course_ID
 Instructor_Phone → depends only on Instructor

Decomposition into 2NF:

1. STUDENT(Student_ID, Student_Name, Dept_Name)

2. COURSE(Course_ID, Course_Name, Instructor)
3. INSTRUCTOR(Instructor, Instructor_Phone)
4. ENROLLMENT(Student_ID, Course_ID, Grade)
Step 4: Third Normal Form (3NF)
Rule:

 Must be in 2NF.
 No transitive dependency.

Transitive dependency:
Instructor → Instructor_Phone
(Already separated into INSTRUCTOR table)

So the relations are in 3NF.

Step 5: BCNF (Boyce-Codd Normal

Form)
Rule:

For every functional dependency X → Y,

X must be a super key.

All decomposed relations satisfy BCNF.

Final Normalized Schema

1. STUDENT(Student_ID, Student_Name, Dept_Name)
2. COURSE(Course_ID, Course_Name, Instructor)
3. INSTRUCTOR(Instructor, Instructor_Phone)
4. ENROLLMENT(Student_ID, Course_ID, Grade)

Foreign Keys:

 ENROLLMENT.Student_ID → STUDENT.Student_ID
 ENROLLMENT.Course_ID → COURSE.Course_ID
 [Link] → [Link]

SQL Implementation
CREATE TABLE STUDENT (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Dept_Name VARCHAR(50)
);

CREATE TABLE INSTRUCTOR (

Instructor VARCHAR(50) PRIMARY KEY,
Instructor_Phone VARCHAR(15)
);

CREATE TABLE COURSE (

Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(50),
Instructor VARCHAR(50),
FOREIGN KEY (Instructor) REFERENCES INSTRUCTOR(Instructor)
);

CREATE TABLE ENROLLMENT (

Student_ID INT,
Course_ID INT,
Grade VARCHAR(2),
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);

Advantages of Normalization
 Eliminates data redundancy
 Avoids update anomalies
 Maintains data integrity
 Efficient storage
 Logical data organization

Result
The database was successfully designed using the bottom-up approach and normalized
up to BCNF, eliminating redundancy and ensuring data consistency.
DATABASE DESIGN USING NORMALIZATION-BOTTOM UP APPROACH
DATE

AIM:
To Create the Database Design Using ER Modeling, Normalization and
Implementation.

NORMALIZATION
DEFINITION

Normalization is the analysis of functional dependencies between attributes/data

items of user views. It reduces a complex user view to a set of small and stable
subgroups of the fields and relations. This process helps to design a logical data model
known as conceptual data model.
First Normal Form(1NF)

 1NF states that the domain of an attribute must include only atomic (simple,
indivisible) values and that value of any attribute in a tuple must be a single
value from the domain of that attribute.
 Attributes must be atomic:
– they can be chars, ints, strings
– they can’t be
1. _ tuples
2. _ sets
3. _ relations
4. _ composite
5. _ multivalued
E-R DIAGRAM

First Normal Form

Epnorm1

Eno Ename Eadd

Sal

Snam
Sno City Country

ALGORITHM:
FIRST NORMAL FORM:
1. Create a type address for the composite address attribute.
SQL>create or replace type address as object
(sno number (3),sname varchar2(30),city varchar2(20),country varchar2(20));

2. Create a employee table with the following fields eno,ename,eadd,sal and having
the eno as the primary key.
SQL>Create table emp(eno number(3) primary key,ename varchar2(20),eadd
address,sal number(7,2));
SQL> desc employees
Name Null? Type
ENO NOT NULL NUMBER(3)
ENAME VARCHAR2(20)
EADD ADDR
SAL NUMBER(7,2)

3. Insert values in the emp table

insert into emp values(&eno,'&ename',address
(&sno,'&sname','&city','&country'),&sal);
SQL> insert into employees
values(&eno,'&enmae',addr(&sno,'&sname','&city','&state'),&sal);
Enter value for eno: 001
Enter value for ename: anbu
Enter value for sno: 12
Enter value for sname: Ist street
Enter value for city: chennai
Enter value for state: tamilnadu
Enter value for sal: 10000
old 1: insert into employees
values(&eno,'&enmae',addr(&sno,'&sname','&city','&state'),&sal)
new 1: insert into employees values(001,'anbu',addr(12,'Ist
street','chennai','tamilnadu'),10000)
1 row created.

4. Emp table is not in the first normal form since it has a composite [Link] it has
been normalized to first normal form.

BEFORE NORMALIZATION :

Eno Ename Eadd Sal

Normalization To First Normal Form:

1. creating the en11 table with eno,ename and esal from emp;
SQL>create table en11 as select eno,ename,sal from emp;
2. creating the table en12 with eno and eadd from emp
SQL>create table en12 as select eno,eadd from emp;
3. altering the table en11 with primary key on eno
SQL>alter table en11 add constraint k1 primary key(eno);
4. altering the table en12 with foreign key on eno with reference from en11

SQL>alter table en12 add constraint c1 foreign key(eno) references en11(eno)

After Normalization:

en11 e12
En Eadd
Eno Ename Sal o

Functional Dependencies (FDs)

Functional dependency describes the relationship between attributes in a relation.
For example, if A and B are attributes of relation R, and B is functionally dependent on
A (denoted
A-> B), if each value of A is associated with exactly one value of B. (A and B may each
consist of
one or more attributes.)

B is Functionally dependent

A B
On A
Trivial functional dependency means that the right-hand side is a subset ( not
necessarily a proper
subset) of the left- hand side.

Second Normal Form(2NF)

A relation is said to be in 2NF if it is already in 1NF and it has no partial
dependency.2NF
is based on the concept of full functional dependency.
A functional dependency(FD) X->Y is full functional dependency if(X-(A))->Y
does not
hold dependency any more if A->X.
A functional dependency X->Y is partial dependency if A can be removed
which does not
affect the dependency i.e. (X-(A))->Y holds.

Second Normal Form

Epnorm2

Hours

Eno Ename Pno

Pname

SECOND NORMAL FORM:

1. Creating the emp project table
SQL> create table epnorm2(eno number(3) primary key,pno number(3) unique,pname
varchar2(20),hours number(3),ename varchar2(20))

2. checking the table

SQL> desc epnorm2
Name Null? Type
ENO NOT NULL NUMBER(3)
PNO NUMBER(3)
PNAME
VARCHAR2(20)

HOURS NUMBER(3)
ENAME
VARCHAR2(20)

3. inserting the values in the table;

insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')
example of insertion
SQL> insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')
Enter value for eno: 1
Enter value for pno: 101
Enter value for pname: Sharma
Enter value for hours: 75
Enter value for ename: Aravind
old 1: insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')
new 1: insert into epnorm2 values(1,101,'Sharma',75,'Aravind')
1 row created.
4. To normalize the above table to second normal form.

Before Normalization
 Eno Ename Pno Pname hours

Normalization To Second Normal Form

a) create the table en21 with eno,ename from the table epnorm2
SQL> create table en21 as select eno,ename from epnorm2;
Table created.
b) Create the table en22 with pno,pname from table epnorm2
SQL> create table en22 as select pno,pname from epnorm2;
Table created.
c) Alter table en21 with a primary key constraint on eno.
SQL> alter table en21 add constraint en21 primary key(eno);
Table altered.
d) Alter table en22 with a primary key constraint on pno.
SQL> alter table en22 add constraint en22 primary key(pno);
Table altered.
e) Create table en23 with eno,pno and hours from the table epnorm2.
SQL> create table en23 as select eno,pno,hours from epnorm2;
Table created.
f) Alter table en23 with a foreign key on eno with references on eno from en21
SQL> alter table en23 add constraint en231 foreign key(eno) references en21(eno);
Table altered.
g) Alter table en23 with foreign key on pno with references on pno from en22
SQL> alter table en23 add constraint en232 foreign key(pno) references en22(pno);
Table altered.
After Normalization

en21

Eno Ename

en22
Pno Pname

En23

eno pno hours

Third Normal Form

Epnorm3

Sal Epnam Eno Dname

e Dn

Third Normal Form(3NF)

A relation is said to be in 3NF if it is already in 2NF and it has no transitive dependency.

A FD X->Y in a relation schema R is a transitive dependency if there is a set of
attributes Z that is neither a candidate key nor a subset of any key of the relation and
both X->Z
and Z->Y hold.

THIRD NORMAL FORM

1. create the table emp_dept with eno,ename,sal,dno and dname as attributes.

SQL> create table emp_dept(eno number(3) primary key,ename varchar2(20),sal
number(7,2),dno number(3),dname varchar2(20));
Table created.

2. insert the values in the table.

SQL> insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname');
Example record
SQL> insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname')
Enter value for eno: 1
Enter value for ename: Mr. Brown
Enter value for sal: 10000
Enter value for dno: 1
Enter value for dname: cse
old 1: insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname')
new 1: insert into emp_dept values(1,'Mr. Brown',10000,1,'cse')
1 row created.

3. The relation does not satisfy the 3rd normal form since dno is not a primary key. So
normalization is done for the third normal form.

Before Normalization
Empdept

Eno Enam Dno Dname

e

Normalization To Third Normal Form

a) Create table en31 with eno,ename,sal,dno from the table emp_dept.
SQL> create table en31 as select eno,ename,sal,dno from emp_dept
Table created.
b) Create table en32 with dno,dname from the table emp_dept.
SQL> create table en32 as select dno,dname from emp_dept;
Table created.
c) Alter the table en31 with the constraint primary key on eno.
SQL> alter table en31 add constraint en31 primary key(eno);
Table altered.
d) Alter table en32 with the constraint primary key on dno;
SQL> alter table en32 add constraint en32 primary key(dno);
Table altered.
e) Alter table en31 with the constraint foreign key on dno with reference from dno
in en32
SQL> alter table en31 add constraint en311 foreign key(dno) references
en32(dno)
Table altered.
After Normalization

En31

Eno Enam Sal Dno

e

en32

Dno Dname

RESULT:
Thus the study of Database Design Using ER Modeling and Normalization was
successfully
implemented