UNIT-II

DATABASE DESIGN
ER MODEL & ER DIAGRAMS
 Database design/ Database model
• well-designed database shall:
– Eliminate Data Redundancy: the same piece of data shall
not be stored in more than one place. This is because
duplicate data not only waste storage spaces but also easily
lead to inconsistencies.
– Ensure Data Integrity and Accuracy
• Database design is a logic structure of a database
Design can be done in different levels:
• Physical level
• Logical level
• User/view level
 ER Model – Basic Concepts
• The ER model defines the three most relevant steps
• 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.

Database Design Process

1. Requirement Analysis
2. Conceptual Database Design
3. Logical Database Design
Requirement Analysis

• The very first step in designing database application is to understand what

data us to be stored in the database, what applications must be built on the
top of it and what operations are we must find out what the users want
from the database

Conceptual Database Design

• The information gathered in the requirements analysis step is used to
develop a high-level description of the data to be stored in database, along
with the constraints known to hold over the data
• The ER model is one of the high-level or semantic, data models used in
database
Logical Database Design
• We must choose a DBMS to implement our database design, and convert the
conceptual database schema into a database schema in the data model of chosen
DBMS. Sometimes conceptual schema is called logical schema in Relational
Data model
Entity-Relationship Data Model
• Classical, popular conceptual data model

• First introduced (mid 70’s) as a (relatively minor) improvement to the relational

model: pictorial diagrams are easier to read than relational database schemas
• Then evolved as a popular model for the first conceptual representation of data
structures in the process of database design
 Introduction
• E-R diagram is the short form of “Entity-Relationship” diagram. 
• An ER diagram efficiently shows the relationships between various entities stored in a
database.
• ER diagrams are a visual tool which is helpful to represent the ER model.
• It was proposed by Peter Chen in 1971 to create a uniform convention which can be used
for relational database and network.
• He aimed to use an ER model as a conceptual modeling approach.
Definition
ER-Diagram is a visual representation of data that describes how
data is related to each other
Example:
 Why use ER Diagrams?
• Helps you to define terms related to entity relationship modeling
• Provide a preview of how all your tables should connect, what fields
are going to be on each table
• Helps to describe entities, attributes, relationships
• ER diagrams are translatable into relational tables which allows you
to build databases quickly
• ER diagrams can be used by database designers as a blueprint for
implementing data in specific software applications
• The database designer gains a better understanding of the
information to be contained in the database with the help of ERP
diagram
• ERD is allowed you to communicate with the logical structure of the
database to users
 Features of the ER Diagram
• E-R diagrams are used to represent E-R model in a database,
which makes them easy to be converted into relations (tables).
• E-R diagrams provide the purpose of real-world modeling of
objects which makes them intently useful.
• E-R diagrams require no technical knowledge & no hardware
support.
• These diagrams are very easy to understand and easy to create
even by a naive user.
• It gives a standard solution of visualizing the data logically.
ER Diagram Symbols and Notations
 Components of the ER Diagram
This model is based on three basic concepts:
• Entities
• Attributes
• Relationships
Example
For example, in a University database, we might have entities for Students, Courses, and
Lecturers. Students entity can have attributes like Rollno, Name, and DeptID. They
might have relationships with Courses and Lecturers.
A. Entity
Any real-world object can be represented as an entity about which
data can be stored in a database. All the real world objects like a
book, an organization, a product, a car, a person are the examples of
an entity. Any living or non-living objects can be represented by an
entity. An entity is symbolically represented by a rectangle enclosing
its name.

Examples of Entities:
• Person: Employee, Student, Patient
• Place: Store, Building
• Object: Machine, product and Car
• Event: Sale, Registration, Renewal
• Concept: Account, Course
Entities can be characterized into two types:
• Strong entity: A strong entity has a primary key attribute which uniquely
identifies each entity. Symbol of strong entity is same as an entity.

• Weak entity: A weak entity does not have a primary key attribute and
depends on other entity via a foreign key attribute.
Difference between Strong Entity & Weak Entity
B. Attribute:
Each entity has a set of properties. These properties of each entity are
termed as attributes. For example, a car entity would be described by
attributes such as price, registration number, model number, color etc.
Attributes are indicated by ovals in an e-r diagram.

An attribute can be characterized into following types:

• Simple attribute:- An attribute is classified as a simple attribute if
it cannot be partitioned into smaller components. For example, age
and sex of a person. A simple attribute is represented by an oval.
• Composite attribute:- A composite attribute can be subdivided into
smaller components which further form attributes. For example,
‘name’ attribute of an entity “person” can be broken down into first
name and last name which further form attributes. Grouping of these
related attributes forms a composite attribute. ‘name is the composite
attribute in this example.

• Single valued attribute:- If an attribute of a particular entity

represents single value for each instance, then it is called a single-
valued attribute. For example, Ramesh, Kamal and Suraj are the
instances of entity ‘student’ and each of them is issued a separate
roll number. A single oval is used to represent this attribute.
• Multi valued attribute:– An attribute which can hold more than
one value, it is then termed as multi-valued attribute. For
example, phone number of a person. Symbol of multi-valued
attribute is shown below,

• Derived attribute: A derived attribute calculate its value from

another attribute. For example, ‘age’ is a derived attribute if it
calculates its value from ‘current date’ & ‘birth date’ attributes. A
derived attribute is represented by a dashed oval.
C. Relationships:
A relationship is defined as bond or attachment between 2 or
more entities. Normally, a verb in a sentence signifies a
relationship.
For example,
• An employee assigned a project.
• Teacher teaches a student.
• Author writes a book.
• A diamond is used to symbolically represent a relationship in the
e-r diagram.
Various terms related to relationships
a) Degree of relationship:- It signifies the number of entities
involved in a relationship. Degree of a relationship can be
classified into following types:
• Unary relationship:- If only single entity is involved in a
relationship then it is a unary relationship. For example, An
employee(manager) supervises another employee.
• Binary relationships:- when two entities are associated to form a
relation, then it is known as a binary relationship. For example, A
person works in a company. Most of the times we use only
binary relationship in an e-r diagram. The teacher-student
example shown above signifies a binary relationship.

Other types of relationships are ternary and quaternary. As the name

signifies, a ternary relationship is associated with three entities
and a quaternary relationship is associated with four entities.
b) Connectivity of a relationship:- Connectivity of a relationship
describes, how many instances of one entity type are linked to how
many instances of another entity type. Various categories of
connectivity of a relationship are;
• One to One (1:1) – “Student allotted a project” signifies a one-to-
one relationship because only one instance of an entity is related
with exactly one instance of another entity type.

• One to Many (1:M) – “A department recruits faculty” is a one-to-

many relationship because a department can recruit more than one
faculty, but a faculty member is related to only one department.
• Many to One (M:1) – “Many houses are owned by a person” is a
many-to-one relationship because a person can own many houses
but a particular house is owned only a person.

• Many to Many (M:N) – “Author writes books” is a many-to-

many relationship because an author can write many books and a
book can be written by many authors.
 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
• The mapping cardinalities are used in representing the binary
relationship sets
• Four types of mapping cardinalities are
1. One to One
2. One to Many
3. Many to One
4. Many to Many
 Types of Cardinality
1. One to One 2. One to Many
An entity A is associated with at An entity A is associated with
least one entity on B and an any number of entities in B.
entity B is associated with However, An entity in B is
at one entity on A. associated with at most one
entity in A.
 Contd…
3. Many to One 4. Many to Many
An entity in A is associated with An entity in A is associated with
at most one entity in B. An any number of entities in B,
entity in B, however can be and an entity in B is
associated with any number associated with any number
of entities in A of entities in A
 Summary
• The ER model is a high-level data model diagram
• ER diagrams are a visual tool which is helpful to represent the ER model
• Entity relationship diagram displays the relationships of entity set stored in a
database
• ER diagrams help you to define terms related to entity relationship modeling
• ER model is based on three basic concepts: Entities, Attributes & Relationships
• An entity can be place, person, object, event or a concept, which stores data in the
database
• Relationship is nothing but an association among two or more entities
• A weak entity is a type of entity which doesn't have its key attribute
• It is a single-valued property of either an entity-type or a relationship-type
• It helps you to defines the numerical attributes of the relationship between two
entities or entity sets
• ER- Diagram is a visual representation of data that describe how data is related to
each other
• While Drawing ER diagram you need to make sure all your entities and
relationships are properly labeled.
 The Enhanced ER Model
• As the complexity of data increased in the late 1980s, it became more and more difficult to use the
traditional ER Model for database modeling. Hence some improvements or enhancements were made to
the existing ER Model to make it able to handle the complex applications better.

• Hence, as part of the Enhanced ER Model, along with other improvements, three new concepts were
added to the existing ER Model, they were:

• Generalization
• Specialization
• Aggregation
• Subclass / Super class
• Union  collection of superclass
• Inheritance
Generalization:
Generalization is a bottom-up approach in which two lower level
entities combine to form a higher level entity. In generalization,
the higher level entity can also combine with other lower level
entities to make further higher level entity.
It's more like Superclass and Subclass system, but the only
difference is the approach, which is bottom-up. Hence, entities
are combined to form a more generalized entity, in other words,
sub-classes are combined to form a super- class
Specialization:
Specialization is opposite to Generalization. It is a top-down
approach in which one higher level entity can be broken down
into two lower level entity. In specialization, a higher level entity
may not have any lower-level entity sets, it's possible.
Aggregation:
Aggregation is a process when relation between two entities is
treated as a single entity.
In the diagram above, the relationship between Center and
Course together, is acting as an Entity, which is in relationship
with another entity Visitor. Now in real world, if a Visitor or a
Student visits a Coaching Center, he/she will never enquire about
the center only or just about the course, rather he/she will ask
enquire about both.
 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.
• We cannot import all the ER constraints into relational model,
but an approximate schema can be generated.
• 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.
ER diagrams mainly comprise of −
• Entity and its attributes
• Relationship, which is association among entities.
Mapping Entity:
An entity is a real-world object with some attributes.
Mapping Process (Algorithm)
• Create table for each entity.
• Entity's attributes should become fields of tables with their
respective data types.
• Declare primary key
Mapping Relationship:
A relationship is an association among entities.
Mapping Process
• Create table for a relationship.
• Add the primary keys of all participating Entities as fields of table with
their respective data types.
• If relationship has any attribute, add each attribute as field of table.
• Declare a primary key composing all the primary keys of participating
entities.
• Declare all foreign key constraints.
Mapping Weak Entity Sets:
A weak entity set is one which does not have any primary key
associated with it.
Mapping Process
• Create table for weak entity set.
• Add all its attributes to table as field.
• Add the primary key of identifying entity set.
• Declare all foreign key constraints.
Mapping Hierarchical Entities:
ER specialization or generalization comes in the form of hierarchical entity
sets.
Mapping Process
• Create tables for all higher-level entities.
• Create tables for lower-level entities.
• Add primary keys of higher-level entities in the table of lower-level
entities.
• In lower-level tables, add all other attributes of lower-level entities.
• Declare primary key of higher-level table and the primary key for lower-
level table.
• Declare foreign key constraints.
Functional Dependency:
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.
X → Y
The left side of FD is known as a determinant, the right side of the production
is known as a dependent.
For example:
Assume we have an employee table with attributes:
Emp_Id, Emp_Name, Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of

employee table because if we know the Emp_Id, we can tell that employee
name associated with it. Functional dependency can be written as:

Emp_Id → Emp_Name
Types of Functional dependency:

Trivial functional dependency

A → B has trivial functional dependency if B is a subset of A
The following dependencies are also trivial like: A → A, B → B

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 trivial dependencies too.
Non-trivial functional dependency
A → B has a non-trivial functional dependency if B is not a subset of A.
When A intersection B is NULL, then A → B is called as complete non-trivial.
Example:
• Name → DOB
Armstrong’s Axioms  set of rules
• Reflexive rule
• Augmentation rule
• Transitivity rule
Anomaly  DB design is not perfect, managing is tough
• Update anomalies
• Deletion anomalies
• Insert anomalies
To overcome these anomalies  method called Normalization
Decomposition in DBMS removes redundancy, anomalies and inconsistencies
from a database by dividing the table into multiple tables.

Lossless / Non loss Decomposition:

• Decomposition is lossless if it is feasible to reconstruct
relation R from decomposed tables using Joins.
• This is the preferred choice.
• The information will not lose from the relation when
decomposed.
• The join would result in the same original relation.
Decompose the above table into two tables:
Lossy Decomposition:
As the name suggests, when a relation is decomposed into two or more
relational schemas, the loss of information is unavoidable when the original
relation is retrieved.
• Now, you won’t be able to join the above tables, since
Emp_ID isn’t part of the DeptDetails relation.

• Therefore, the above relation has lossy decomposition.

Normalization
• Database Normalization is a technique of organizing the data in
the database.
• Normalization is a systematic approach of decomposing tables to
eliminate data redundancy(repetition) and undesirable
characteristics like Insertion, Update and Deletion anomalies.
• It is a multi-step process that puts data into tabular form,
removing duplicated data from the relation tables.
• Normalization is used for mainly two purposes,
•Eliminating reduntant(useless) data.
•Ensuring data dependencies make sense i.e data is
logically stored.
Problems Without Normalization

• Insertion Anomaly
• Updation Anomaly
• Deletion Anomaly
Normalization Rule

Normalization rules are divided into the following normal forms:

1.First Normal Form
2.Second Normal Form
3.Third Normal Form
4.BCNF
5.Fourth Normal Form
6.Fifth Normal Form
First Normal Form (1NF)
• For a table to be in the First Normal Form, it should follow the
following 4 rules:
1.It should only have single(atomic) valued attributes/columns.
Each column of your table should be single valued which means
they should not contain multiple values. We will explain this with
help of an example later, let's see the other rules for now.
2.Values stored in a column should be of the same domain
This is more of a "Common Sense" rule. In each column the values
stored must be of the same kind or type. For example: If you have a
column dob to save date of births of a set of people, then you cannot
or you must not save 'names' of some of them in that column along
with 'date of birth' of others in that column. It should hold only 'date
of birth' for all the records/rows.
3.All the columns in a table should have unique names.
This rule expects that each column in a table should have a unique
name. This is to avoid confusion at the time of retrieving data or
performing any other operation on the stored data.
If one or more columns have same name, then the DBMS system
will be left confused.

4.And the order in which data is stored, does not matter.

This rule says that the order in which you store the data in your table
doesn't matter.
EXAMPLE
Create a table to store student data which will have student's roll no.,
their name and the name of subjects they have opted for.
Here is the table, with some sample data added to it.

The table already satisfies 3 rules out of the 4 rules, as all our
column names are unique, we have stored data in the order we
wanted to and we have not inter-mixed different type of data in
columns.
• But out of the 3 different students in our table, 2 have opted for
more than 1 subject. And we have stored the subject names in a
single column. But as per the 1st Normal form each column must
contain atomic value. It's very simple, because all we have to do
is break the values into atomic values.
• Here is our updated table and it now satisfies the First Normal
Form.
• By doing so, although a few values are getting repeated but
values for the subject column are now atomic for each
record/row. Using the First Normal Form, data redundancy
increases, as there will be many columns with same data in
multiple rows but each row as a whole will be unique.

Second Normal Form (2NF)

For a table to be in the Second Normal Form,
1.It should be in the First Normal form.
2.And, it should not have Partial Dependency.
Dependency:
Let's take an example of a Student table with columns student_id,
name, reg_no(registration number), branch and address(student's
home address).

In this table, student_id is the primary key and will be unique for
every row, hence we can use student_id to fetch any row of data
from this table
Even for a case, where student names are same, if we know the
student_id we can easily fetch the correct record.
• Hence we can say a Primary Key for a table is the column or a
group of columns(composite key) which can uniquely identify
each record in the table.
• I can ask from branch name of student with student_id 10, and I
can get it. Similarly, if I ask for name of student with student_id
10 or 11, I will get it. So all I need is student_id and every other
column depends on it, or can be fetched using it.This is
Dependency and we also call it Functional Dependency.
Partial Dependency:
Now that we know what dependency is, we are in a better state to
understand what partial dependency is.
For a simple table like Student, a single column like student_id can
uniquely identfiy all the records in a table.
But this is not true all the time. So now let's extend our example to
see if more than 1 column together can act as a primary key.
Let's create another table for Subject, which will have subject_id
and subject_name fields and subject_id will be the primary key.
• Now we have a Student table with student information and
another table Subject for storing subject information.
• Let's create another table Score, to store the marks obtained by
students in the respective subjects. We will also be saving name
of the teacher who teaches that subject along with marks.

• In the score table we are saving the student_id to know which

student's marks are these and subject_id to know for which
subject the marks are for.
• Together, student_id + subject_id forms a Candidate Key which
can be the Primary key.
• Now if you look at the Score table, we have a column names
teacher which is only dependent on the subject, for Java it's Java
Teacher and for C++ it's C++ Teacher & so on.

• Now as we just discussed that the primary key for this table is a
composition of two columns which is student_id & subject_id
but the teacher's name only depends on subject, hence the
subject_id, and has nothing to do with student_id.

• This is Partial Dependency, where an attribute in a table depends

on only a part of the primary key and not on the whole key.
How to remove Partial Dependency?
There can be many different solutions for this, but out objective is to
remove teacher's name from Score table. The simplest solution is to
remove columns teacher from Score table and add it to the Subject
table. Hence, the Subject table will become:

And our Score table is now in the second normal form, with no
partial dependency.
Third Normal Form (3NF)
A table is said to be in the Third Normal Form when,
1.It is in the Second Normal form.
2.And, it doesn't have Transitive Dependency.
So let's use the same example, where we have 3 tables, Student,
Subject and Score.
Student Table

Subject Table

Score Table
Transitive Dependency
With exam_name and total_marks added to our Score table, it saves
more data now. Primary key for the Score table is a composite key,
which means it's made up of two attributes or columns → student_id
+ subject_id. The new column exam_name depends on both student
and subject. For example, a mechanical engineering student will
have Workshop exam but a computer science student won't. And for
some subjects you have Practical exams and for some you don't. So
we can say that exam_name is dependent on both student_id and
subject_id.

And what about our second new column total_marks? Does it

depend on our Score table's primary key?
• Well, the column total_marks depends on exam_name as with
exam type the total score changes. For example, practicals are of
less marks while theory exams are of more marks.

• But, exam_name is just another column in the score table. It is

not a primary key or even a part of the primary key, and
total_marks depends on it.

• This is Transitive Dependency. When a non-prime attribute

depends on other non-prime attributes rather than depending
upon the prime attributes or primary key.
How to remove Transitive Dependency
Again the solution is very simple. Take out the columns exam_name and
total_marks from Score table and put them in an Exam table and use the
exam_id wherever required.
Score Table: In 3rd Normal Form

The new Exam table

Advantage of removing Transitive Dependency

•Amount of data duplication is reduced.
•Data integrity achieved.
Advantage of removing Transitive Dependency
•Amount of data duplication is reduced.
•Data integrity achieved.

Boyce and Codd Normal Form (BCNF)

• BCNF is a higher version of the Third Normal form.
• This form deals with certain type of anomaly that is not
handled by 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. In simple words, it means, that for a dependency A → B, A
cannot be a non-prime attribute, if B is a prime attribute.
Example
College enrolment table with columns student_id, subject and
professor.
In the table above:
• One student can enroll for multiple subjects. For example, student
with student_id 101, has opted for subjects - Java & C++
•For each subject, a professor is assigned to the student.
•And, there can be multiple professors teaching one subject like
Java.
What do you think should be the Primary Key?
• Well, in the table above student_id, subject together form the
primary key, because using student_id and subject, we can find all
the columns of the table.
• One more important point to note here is, one professor teaches
only one subject, but one subject may have two different
professors.
• Hence, there is a dependency between subject and professor here,
where subject depends on the professor name.
• This table satisfies the 1st Normal form because all the values are
atomic, column names are unique and all the values stored in a
particular column are of same domain.

• This table also satisfies the 2nd Normal Form as there is no

Partial Dependency.

• And, there is no Transitive Dependency, hence the table also

satisfies the 3rd Normal Form.

• But this table is not in Boyce-Codd Normal Form.

Why this table is not in BCNF?
• In the table above, student_id, subject form primary key, which
means subject column is a prime attribute.
• But, there is one more dependency, professor → subject.
• And while subject is a prime attribute, professor is a non-prime
attribute, which is not allowed by BCNF.
How to satisfy BCNF?
Student Table

Professor Table
Fourth Normal Form (4NF):
A table is said to be in the Fourth Normal Form when,
1.It is in the Boyce-Codd Normal Form.
2.it doesn't have 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.
Example
Below we have a college enrolment table with columns s_id, course
and hobby.

Well the two records for student with s_id 1, will give rise to two more records, as
shown below, because for one student, two hobbies exists, hence along with both
the courses, these hobbies should be specified.

And, in the table above, there is no relationship between the columns course and
hobby. They are independent of each other.
 
So there is multi-value dependency, which leads to un-necessary repetition of data
and other anomalies as well.
How to satisfy 4th Normal Form?
To make the above relation satisfy the 4th normal form, we can
decompose the table into 2 tables.
Course Opted Table

Hobbies Table

A table can also have functional dependency along with multi-valued

dependency. In that case, the functionally dependent columns are moved in a
separate table and the multi-valued dependent columns are moved to separate
tables.
Fifth Normal Form (5NF)
A database is said to be in 5NF, if and only if,
1.It's in 4NF
2.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.
In simple words, joining two or more decomposed table should not lose
records nor create new records.

What is Join Dependency?

If a table can be recreated by joining multiple tables and each of this table
have a subset of the attributes of the table, then the table is in Join
Dependency. It is a generalization of Multivalued Dependency.
Join Dependency can be related to 5NF, wherein a relation is in 5NF, only if it
is already in 4NF and it cannot be decomposed further.
Example
<Employee>

The above table can be decomposed into the following three tables; therefore it is not
in 5NF:
 Domain Key Normal Form(DKNF)
• A relation is in DKNF if every constraint on the relation is a logical
consequences of the definition of Keys and Domains.
• A constraint in this definition is any rule which is precise enough so that
you can evaluate whether or not it is true.
• A key is a unique identifier of a row in a table.
• A domain is the set of permitted values of an attribute.
Look at this database, which is in 1NF, to see what you must do to put that
database in DKNF.
• Table: SALES (Customer_ID, Product, Price)
• Key: Customer_ID
• Constraints:
• Customer_ID determines Product
• Product determines Price
• Customer_ID must be an integer >1000
To enforce Constraint 3 (that Customer_ID must be an integer greater than
1000), you can simply define the domain for Customer_ID to incorporate this
constraint. That makes the constraint a logical consequence of the domain of the
CustomerID column. Product depends on Customer_ID, and Customer_ID is a
key, so you have no problem with Constraint 1, which is a logical consequence
of the definition of the key.

• Constraint 2 is a problem. Price depends on (is a logical consequence of)

Product, and Product isn’t a key. The solution is to divide the SALES table
into two tables. One table uses Customer_ID as a key, and the other uses
Product as a key. The database, besides being in 3NF, is also in DK/NF. 1000
THANK YOU!!!