MAILAM ENGINEERING COLLEGE, MAILAM

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

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.

1. What is meant by normalization of data? (or)What is normalization? (MAY

2010)
It is a process of analyzing the given relation schemas based on their
Functional Dependencies (FDs) and primary key to achieve the properties
 Minimizing redundancy
 Minimizing insertion, deletion and updating anomalies.
2. Define canonical cover?
A canonical cover Fc for F is a set of dependencies such that F logically implies
all dependencies in FC and Fc logically implies all dependencies in F. Fc must have
the following properties
3. List the properties of canonical cover.
Fc must have the following properties.
 No functional dependency in Fc contains an extraneous attribute.
 Each left side of a functional dependency in Fc is unique.
4. Explain the desirable properties of decomposition. APR-2019
 Lossless-join decomposition
 Dependency preservation
 Repetition of information
5. What is first normal form?
The domain of attribute must include only atomic (simple, indivisible) values.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 1

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
6. What is meant by functional dependencies? (or) Write a note on functional
dependencies.(MAY 2010)
 Functional Dependency is when one attribute determines another attribute in a
DBMS system.
 Functional Dependency plays a vital role to find the difference between good
and bad database design.
 A functional dependency is denoted by an arrow →
 The functional dependency of X on Y is represented by X →Y and X is a
determinant set and Y is a dependent set.
7. What are the uses of functional dependencies?
 To test relations to see whether they are legal under a given set of functional
dependencies.
 To specify constraints on the set of legal relations.
 Functional Dependency plays a vital role to find the difference between good
and bad database design.
8. Explain trivial dependency?
 The Trivial dependency is a set of attributes which are called a trivial if the set
of attributes are included in that attribute.
 So, X -> Y is a trivial functional dependency if Y is a subset of X.
9. What are axioms?
Axioms or rules of inference provide a simpler technique for reasoning about
functional dependencies
10. What is meant by computing the closure of a set of functional dependency?
The closure of F denoted by F+ is the set of functional dependencies logically
implied by F.
11. What is 2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in
R is fully functionally dependent on primary key.
12. What is a Prime attribute?
Any attribute of relation schema R,is called as prime attribute, if it is a
member of some candidate key of R.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 2

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
13. What is a non Prime attribute?
Any attribute of relation schema R, is called as non-prime attribute, if it is not
a member of any candidate key.
14. What is partial functional dependency?
Partial dependency means that a nonprime attribute is functionally dependent
on part of a candidate key.
A functional dependency XY is a partial functional dependency if some
attribute A £ X, can be removed from X the dependency still holds (i.e) from A£X,(X-
{A})->Y. (i.e) If any attribute is removed from X, then function dependency holds.
15. What is 3NF?(DEC 2010)
A relation schema R is in 3NF if
 It satisfies 2NF
 Every non-prime attribute of R is not transitively dependent on the primary key.
16. What is transitive dependency?
A functional dependency XY in a relation schema R is a transitive
dependency if there is a set of attributes Z that is neither a candidate nor a subset of
any key of R and both XZ and ZY hold.

XY = XZ & Z Y

17. What is BCNF?
A relation schema R is in BCNF with respect to a set F of functional
dependency, if for all F+ of the form α â, where á C R & â C F, then one of the
following holds.
 α  β is a trivial functional dependency (β C α)
 α is a super key for schema
18. What is multivalued dependency?
 Multivalued dependency occurs when two attributes in a table are independent
of each other but, both depend on a third attribute.
 A multivalued dependency consists of at least two attributes that are
dependent on a third attribute that's why it always requires at least three
attributes

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 3

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
19. What is trivial multivalued dependency?
An multivalued dependency XY in r is called as trivial multivalued
dependency if
 y is a subset of X
 XUY=R.
20. What is non trivial multivalued dependency?
A multivalued functional dependency that satisfies the condition y is a subset
of X, XUY=R.is called as non trivial functional dependency.
21. What is 4NF?
A relation schema R is in 4NF with a set of functional dependencies F if
for every non-trivial multivalued dependency XY in f+, X is a super key of R.
22. What is Trivial Join Dependency?
A join dependency specified on a relation schema R is a trivial join
dependency if one of schema R in join dependency is equal to R
23. What is 5NF?
A relation schema R is in 5NF , if and only of every nontrivial join
dependency that is satisfied by R is implied by the candidate keys of R , if and only if
each of A,B,…..Z is a super key of R.
24. What is a super key?
 It is a set of one or more attributes, that taken collectively allows us identify
uniquely a tuple in the relation.
 Example: Reg_no of students entity ie a superkey.
25. What are the ways to choose the functional dependency?
 To test relations to see whether they are legal under a given set of
functional dependencies.
 If a relation r is said to be legal under a g set of fune dependencies F, use
say that r satisfies F.
 To satisfy constraints on set of legal relations
 If a relation on schema R satisfies the set of functional dependency F then
it means that F holds on R.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 4

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
26. What is a closure of functional dependency?
Given a relation schema R a functional dependency f on R is logically implied
by a set of functional dependencies F on R, if every relation instance r(R) that
satisfies F also satisfies f
27. What are the Armstrong Axioms available?
The Armstrong axioms consist of the following rules
Reflexivity rule:
If α is set of attributes and β c α, then α->β holds.
Augmentation rule:
If α->β holds, and γ is a set of attributes, then γα -> γβ holds.
Transitivity rule:
If α->β, β->γ holds, then α->γ holds addition rules.
Union rules:
If α->β, α->γ, α->βγ holds.
Decomposition rule:
If α->βγ , holds then α->β , α->γ holds.
Pseudo transitivity rules:
If α->β , and γβ->δ , holds αγ-.δ holds
28. What is a closure of attribute set?
It is the set of all attributes functionally determined by α under a set F of
functional dependencies; the closure of α under F.The input is a set F of functional
dependencies and set α of attributes
30. What is an extraneous attribute
?
An attribute of function dependency is said to be extraneous, if we remove it
without changing the closure of set of functional dependency
31. What are the goals of relational database design?
To generate set of relational schemas that allows us to store information
with unnecessary redundancy and allow us to retrieve information easily.
32. What are the pitfalls of relational database design?
 Repetition of information

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 5

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 Inability to store certain data
33. What is Reflexivity rule?
If α is set of attributes and β c α, then α->β holds.
34. What is Augmentation rule?
If α->β holds, and γ is a set of attributes, then γα -> γβ holds.
35. What is Transitivity rule?
If α->β, β->γ holds, then α->γ holds addition rules.
36. What is Union rule?
If α->β, α->γ, α->βγ holds.
37. What is Decomposition rule?
If α->βγ , holds then α->β , α->γ holds.
38. What is a Pseudo transitivity rule?
If α->β , and γβ->δ , holds αγ-.δ holds
39. Define irreduciable sets of dependencies (NOV/DEC 2010)
A functional depending sets S is irreduciable if the set has the following three
properties:;
 Each right set of a functional dependency of S contains only one attribute.
 Each left set of a functional dependency of S is irreducible. It means reducing
one attribute from left set will change the content of S (S will lose some
information).
 Reducing any functional dependency will change the content of S.
Sets of Functional Dependencies(FD) with these properties are also called canonical
or minimal.
41. What is meant by lossless-join decomposition or loss less join ?(APL/MAY
2011)
 Decomposition must be lossless. It means that the information should not get
lost from the relation that is decomposed.
 It gives a guarantee that the join will result in the same relation as it was
decomposed.

42. What is the need for Normalization? (MAY/JUNE 2013)

 Minimizing redundancy

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 6

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 Minimizing insertion, deletion and updating anomalies.
43. Define the terms i) Entity set ii) Relationship set (apr-2019)
Entity set: The set of all entities of the same type is termed as an entity set.
Relationship set: The set of all relationships of the same type is termed as a
relationship set.
44. Define weak and strong entity sets?
 Weak entity set: entity set that do not have key attribute of their own are
called weak entity sets.
 Strong entity set: Entity set that has a primary key is termed a strong entity
set
45. What does the cardinality ratio specify?
Mapping cardinalities or cardinality ratios express the number of entities to which
another entity can be associated. Mapping cardinalities must be one of the following:
• One to one
• One to many
• Many to one
• Many to many
46. What is a weak entity? Give Example. Nov/Dec 2016
In a relational database, a weak entity is an entity that cannot be uniquely identified
by its attributes alone; therefore, it must use a foreign key in conjunction with its
attributes to create a primary key. The foreign key is typically a primary key of an
entity it is related to.

47.What are the desirable properties of decomposition? Apr/May 2017

 Decomposition is the process of breaking down in parts or elements.
 It replaces a relation with a collection of smaller relations.
 It breaks the table into multiple tables in a database.
 If there is no proper decomposition of the relation, then it may lead to problems like
loss of information.
The properties of Decomposition,
1. Lossless Decomposition
2. Dependency Preservation
3. Lack of Data Redundancy

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 7

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

48. Distinguish between key and super key. Apr/May 2017

1. Super key:
It is a set of one or more attributes,that taken collectively allows us identify
uniquely a tuple in the relation.
Example: Reg_no of students entity ie a superkey.
2. Primary key:
It is a candidate key which acts as a principal means of identifying tuples
within a relation.

49. What are the difference between strong entity and weak entity.

50. What is the significance of "participation role name" in the

description of relationship types?
 Each entity type that participates in a relationship type plays a particular role
in the relationship.
 It necessary to use role names in the description of relationship types, in some
cases the same entity type participates more than once in relationship type in
different roles.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 8

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 In such cases the role name become essential for distinguishing the meaning of
each participation. Such relationship types are called recursive relationships.

51.'Boyce-Codd normal form is found to be stricter than third normal

form'. Justify the statement.

Boyce-Codd normal form (BCNF) was proposed as a simpler form of 3NF, but it
was found to be stricter than 3NF. That is, every relation in BCNF is also in 3NF;
however, a relation in 3NF is not necessarily in BCNF.

PART-B
1. Explain the Entity Relationship Model? (APRIL/MAY 2010) (nov/dec
2010) (May/June 2012)
 Introduction
 Basic Concepts of ERModel
o Entity
o EntitySets
o RelationshipSet
o Attributes
 Constraints
o Mapping cardinalities
o Participationconstraints
o Keys
 Entity Relationship Diagram –Notations
 Extended E-RFeatures
o Specialization
o Generalization
o Constraints ongeneralization
o Attributeinheritance
o
 Introduction:
The E-R model is a high-level data model it distinguish between basic object called
entities and relationship among those object.
 Basic Concepts of ERModel

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 9

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
o Entity
o Entitysets
o Relationshipsets
o Attributes
Entity
 Entity: It is a thing or object in the real world that is distinguishable from
otherobjects.
 An entity has a set of properties and the values for some set of properties
may uniquely identify anentity.

o Ex: Person is an entityandperson_id – uniquely identifies that

person.
EntitySets:
o Entity set: An entity set is a set of entity of same type that share same
properties orattributes.
o Ex: Student - represents the set of all students in the university.
o Example Student

Roll no S_name
101 John
102 Peter
301 Saran
405 Michael
 Types of EntitySet
o Strong EntitySet
An entity set that has a primary key is called strong entityset
o Weak EntitySet
An entity set that does not have a primary key is called
weak entityset

o Identifying or Owner Entityset

Weak entity set must be associated with another entity set
called identifying or owner entityset.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 10

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
RelationshipSet
 Relationship: Association among severalentities.
 Relationshipset:A relationship set is a of relationships of same type.

Attributes
 An attribute of an entity set is a function that maps from the
entity set into adomain.
 For each attributes, there is a set of permitted value set of that
attribute.
 Types ofattributes:
 Simple andcomposite
 Single value andmultivalued
 Derived
 Descriptive
 Simple & compositeattributes:
1. Simpleattributes:
Attributes that cannot be divided into subparts are called simple
attributes Ex: S.no is a simpleattributes.
2. Compositeattribute:
Attribute that can be divided into subparts is called as composite
attributes.
Ex: name -first _name, last_name
 Single valued & multi valuedattribute:
1. Single valuedattribute:
The attribute that have single value for a particular entity is called
single valued attribute. Ex: student_id – refers to only one student.
2. Multivaluedattributes:
The attribute that has a set of values for a specific entity is called
multivalued attributes. Ex: phone_number.

 Derived attributes:
The value of this type of attribute can be derived from the values of
otherrelated attributes orentities.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 11

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Example: DOB - base or stored attribute

Age - derived attribute

 Descriptive attributes:
The attribute present in the relationship is called as descriptive attribute.
EX: 1

ER diagram with descriptive attribute

Dat
ee

Teacher adviso student

r

 NULL value:
The attribute takes a NULL value, when an entity does not have a value
for it.
Ex : ER diagram with composite, multivalued and derived attributes

In the above ER diagram

C_name& address - Composite attribute

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 12

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Street - Component attribute
Phone-number - Multi-valued attribute
Age - Derived attribute
 CONSTRAINTS
o Mapping cardinalities
o Participation constraints
o keys

Mapping cardinalities:
 A mapping cardinality is a data constraint that specifies how many
entities an entity can be related to in a relationshipset.
 Types of mapping cardinalitiesare
- one to one
- one tomany
- many toone
- many tomany
 Consider a binary relationship set R on entity sets A andB.
 There are four possible mapping cardinalities in thiscase:
1.One-To-One(MAY/JUNE 2013)
An entity in A is related to at most one entity in B, and an entity in B is related to
at most one entity in A.

Example: there is one project manager who manages only one project.
Project manager Handles
Project
1 1
2.One-To-Many

An entity in A is related to any number of entities in B, but an entity in B is

related to at most one entity in A.

Example : one customer places order at a time.

Customer places Order

1 *

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 13

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
3. Many-To-One
An entity in A is related to at most one entity in B, but an entity in B is related to
any number of entities in A.
Example: Many Student take a ComputerScience Course

ComputerScience
Students * Takes 1 Course

4.Many-To-Many
An entity in A is related to any number of entities in B, but an entity in B is related
to any number of entities in A.
Example: Many teachers can teach many students

Students
Teachers teaches

 Participation Constraints
1.Total Participation:
 The participation of an entity set E in a relationship set R is said to be
total, if every entity in E participates in at least one relationship in R

Example :

 Double Line indicate that from loan to borrower, each loan must have
atleast one associated customer
2. Partial Participation:

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 14

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
If only some entities in E participate in relationship in R ,then the participation of
entity E in relationship R is said to be partial

Instructor Advisor Students

 ENTITY RELATIONSHIP DIAGRAM - SYMBOLS

Links attributes to entity set & to entity set to

relationship
EX 1: ER Diagram corresponding to Customer & Loan

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 15

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

C_name Loan_no Amount

C_id

C_Street

Borrower Loan
Customer

2. EXPAIN IN DETAIL ABOUT ENHANCED E-R MODLEL

 Specialization
 Generalization
 Attribute inheritance
 Aggregation
Specialization

 The process of designating sub groupings within an entity set is

calledspecialization.

 This is a top down process.

 The symbol used for specialization/generalization is

 Ex: Specializations of person allow us to distinguish among persons

according to whether they are employee orcustomers.
 Specialization is defined by a triangle component IS A .it stands for
customer is aperson.
 IS A can also referred as a super class-subclassrelationship.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 16

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

name street
P-ID
city

person

salary ISA
creditrating

employee customer

Generalization:

 The process of making superclass from subclasses is called Generalization.

 It is a containment relationship that exists between a higher level entity set and
one or more lower level entityset.To create a generalization, the attribute must
be given in commonname.

 This is a Bottom up process.

 Super class: An Entity type that represents a general concept at high level , is
called Super class.

 SubClass:: An Entity type that represents a specific concept at lower levels ,is
called sub class.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 17

 name
MAILAM ENGINEERING COLLEGE,
street MAILAM
DEPARTMENTP-ID
OF COMPUTER SCIENCE AND ENGINEERING
city

person

salar IS
A creditrat
y
ing

employ custom
ee er

 Constraints on generalization:
Constraint1:

 Conditiondefined
 Userdefined
Conditiondefined
 In lower level entity sets , membership is evaluated on the basis of
whether or not an entity satisfies an explicit condition or predicate.

 Entities that satisfy account type= ―saving‖, belong to low level entity
savingaccount.

 Entities that satisfy account type =‖checking‖ belongs to lowlevel

entity set checkingaccount.

 Since the lower level entities are evaluated on the basics of same
attribute it is called as attribute defined.
Userdefined:

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 18

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 Not constrained by a membershipcondition.

 Database user assign entities to a given entityset.

Constraint 2:

 Disjoint

 Overlapping

Disjoint:
 A disjointness constraint requires that an entity belong to no more
one low level entityset.

 Ex: Account entity satisfy only one condition either a saving account
or checking account , but cannot beboth.
Overlapping:
 The same entity may belong to more than one lower level entity set
within a singlegeneralization.

 Ex: Generalization applied to customer & employee leads to higher

level entity setperson.
Constraint 3:

Total generalization orspecialization:

 Each higher level entity set must belong to an lower level entityset.

Partial generalization orspecialization:

 Some higher level entity set may not belong to any lower level entity
set.
AttributeInheritance:
 The attributes of higher level entity are said to be inherited by lower
level entityset.

 Ex: Customer & employee inherit the attributer of aperson.

Aggregation
 Aggregation is a process when relation between two entities is treated
as a single entity.

 The limitation of ER model is that it cannot express relationships

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 19

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
among relationship. One alternative for representing the relationship
is to create a quaternaryrelationship

 Aggregation is an abstraction through which relationship are treated

as higher levelentities.
Example : ER diagram with redundant relationship

Job

Employee Wor Branc

k h
On

Manage
s

Manager
EXAMPLE : ER diagram with Aggregation

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 20

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Job

employee Branch
Work
on

Manages

Manager

3.Construct an ER diagram for hospital with a set of patients and a set of

medical doctors. Associated with each patient a log of the various test
and examinations conducted.
 Patients are treated in a single ward by the doctors assigned to them.
Usually each patient will be assigned a single doctor, but in rare cases they
will have two.
 Heathcare assistants also attend to the patients, a number of these are
associated with each ward.
 Initially the system will be concerned solely with drug treatment. Each
patient is required to take a variety of drugs a certain number of times per
day and for varying lengths of time.
 The system must record details concerning patient treatment and staff
payment. Some staff are paid part time and doctors and care assistants
work varying amounts of overtime at varying rates (subject to grade).
 The system will also need to track what treatments are required for which
patients and when and it should be capable of calculating the cost of
treatment per week for each patient (though it is currently unclear to what
use this information will be put).
How do we start an ERD?

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 21

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 Define Entities: these are usually nouns used in descriptions of the system, in
the discussion of business rules, or in documentation; identified in the
narrative (see highlighted items above).
 Define Relationships: these are usually verbs used in descriptions of the
system or in discussion of the business rules (entity ______ entity); identified in
the narrative (see highlighted items above).
 Add attributes to the relations: these are determined by the queries, and may
also suggest new entities, e.g. grade; or they may suggest the need for keys or
identifiers.
What questions can we ask?
o Which doctors work in which wards?
o How much will be spent in a ward in a given week?
o How much will a patient cost to treat?
o How much does a doctor cost per week?
o Which assistants can a patient expect to see?
o Which drugs are being used?
o Add cardinality to the relations
o This flexibility allows us to consider a variety of questions such as:
o Which beds are free?
o Which assistants work for Dr. X?
o What is the least expensive prescription?
o How many doctors are there in the hospital?
o Which patients are family related?

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 22

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

Examples of ER diagrams
a. Draw an ER diagram describing theatre.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 23

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
b.Construct an ER diagram for a mechanic shop that repairs a car.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 24

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

c. Draw an ER diagram for creating and delivery that project

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 25

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

d. Construct an ER diagram for purchase an item using credit card.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 26

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

e.. Entity Relationship Diagram and schematic for Books.

4.Explain the concept of functional dependency in detail?

Define a functional dependency. List and discuss the six inference rules
for functional dependencies. Give relevant examples. DEC 2011
What are the pitfalls in relational database design? With a suitable example,
explain the role of functional dependency in the process of normalization.
MAY 2011
Explain the concept of functional dependency in detail? (DEC 2011))( APR-
2019)
Functional dependencies:

 Functional Dependency is when one attribute determines another attribute in a

DBMS system.
 Functional Dependency plays a vital role to find the difference between good
and bad database design.
 A functional dependency is denoted by an arrow →

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 27

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 The functional dependency of X on Y is represented by X →Y and X is a
determinant set and Y is a dependent set.
 Given a relation R, a set of attributes X in R is said to functionally determine
another attribute Y, also in R, (written X → Y) if and only if each X value is
associated with exactly one Y value.
Rules of Functional Dependencies
Below given are the Three most important rules for Functional Dependency:
 Reflexive rule –. If X is a set of attributes and Y is_subset_of X, then X holds a
value of Y.
 Augmentation rule: When x -> y holds, and c is attribute set, then ac -> bc
also holds. That is adding attributes which do not change the basic
dependencies.
 Transitivity rule: This rule is very much similar to the transitive rule in
algebra if x -> y holds and y -> z holds, then x -> z also holds. X -> y is called
as functionally that determines y.
Types of Functional Dependencies
 Multivalued dependency
 Trivial functional dependency
 Non-trivial functional dependency
 Transitive dependency
Multivalued dependency
 Multivalued dependency occurs when two attributes in a table are independent
of each other but, both depend on a third attribute.
 A multivalued dependency consists of at least two attributes that are
dependent on a third attribute that's why it always requires at least three
attributes.
 Example: Suppose there is a bike manufacturer company which produces two
colors(white and black) of each model every year.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 28

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
BIKE_MODEL MANUF_YEAR COLOR

M2011 2008 White

M2001 2008 Black

M3001 2013 White

M3001 2013 Black

M4006 2017 White

M4006 2017 Black

these two columns can be called as multivalued dependent on BIKE_MODEL. The

representation of these dependencies is shown below:
1. BIKE_MODEL → → MANUF_YEAR
2. BIKE_MODEL → → COLOR
Trivial functional dependency
 The Trivial dependency is a set of attributes which are called a trivial if the set
of attributes are included in that attribute.
 So, X -> Y is a trivial functional dependency if Y is a subset of X.
 For example:

Emp_id Emp_name

AS555 Harry

AS811 George

AS999 Kevin
Consider this table with two columns Emp_id and Emp_name.
{Emp_id, Emp_name} -> Emp_id is a trivial functional dependency as Emp_id is a
subset of {Emp_id,Emp_name}.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 29

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Non trivial functional dependency
 Functional dependency which also known as a nontrivial dependency occurs
when A->B holds true where B is not a subset of A.
 In a relationship, if attribute B is not a subset of attribute A, then it is
considered as a non-trivial dependency.

Company CEO Age

Microsoft Satya Nadella 51

Google Sundar Pichai 46

Apple Tim Cook 57

Example:
(Company} -> {CEO} (if we know the Company, we knows the CEO name)But CEO is
not a subset of Company, and hence it's non-trivial functional dependency.
Transitive dependency:
A transitive is a type of functional dependency which happens when t is indirectly
formed by two functional dependencies.
Example:

Company CEO Age

Microsoft Satya Nadella 51

Google Sundar Pichai 46

Alibaba Jack Ma 54

{Company} -> {CEO} (if we know the compay, we know its CEO's name)
{CEO } -> {Age} If we know the CEO, we know the Age
Therefore according to the rule of rule of transitive dependency:
{ Company} -> {Age} should hold, that makes sense because if we know the company
name, we can know his age.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 30

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Extraneous attributes: An attribute of a functional dependency is said to be
extraneous if we can remove it without changing the closure of the set of functional
dependencies.
Canonical cover: A canonical cover of a set of functional dependencies F such that
ALL the following properties are satisfied:
 F logically implies all dependencies in FC.
 FClogically implies all dependencies in F.
 No functional dependency inFCcontains an extraneous attribute.
 Each left side of a functional dependency inFC is unique. That is, there are no
two dependencies and in such that .
5. Explain in detail about nonloss decomposition .(nov/dec 2017)
decomposition
 Decomposition is the process of breaking down in parts or elements.
 It replaces a relation with a collection of smaller relations.
 It breaks the table into multiple tables in a database.
 It should always be lossless, because it confirms that the information in the original
relation can be accurately reconstructed based on the decomposed relations.
 If there is no proper decomposition of the relation, then it may lead to problems like
loss of information.
The properties of Decomposition,
1. Lossless Decomposition
2. Dependency Preservation
3. Lack of Data Redundancy
1.Nonloss decomposition(or) Lossless jon
 Decomposition must be lossless. It means that the information should not get lost
from the relation that is decomposed.
 It gives a guarantee that the join will result in the same relation as it was
decomposed.
To check for lossless join decomposition using FD set, following conditions
must hold:
1. Union of Attributes of R1 and R2 must be equal to attribute of R. Each attribute
of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)
2. Intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩ Att(R2) ≠ Φ

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 31

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
3. Common attribute must be a key for at least one relation (R1 or R2)
Att(R1) ∩ Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)
Example-
Consider the following relation R( A , B , C )-

A B C

1 2 1

2 5 3

3 3 3

R( A , B , C )

Consider this relation is decomposed into two sub relations R1( A , B ) and R2( B , C )-

The two sub relations are-

A B

1 2

2 5

3 3

R1( A , B )

B C

2 1

5 3

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 32

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
3 3

R2( B , C )

Now, let us check whether this decomposition is lossless or not.

For lossless decomposition, we must have-
R1 ⋈ R2 = R
Now, if we perform the natural join ( ⋈ ) of the sub relations R1 and R2 , we get-

A B C

1 2 1

2 5 3

3 3 3

This relation is same as the original relation R.Thus, we conclude that the above
decomposition is lossless join decomposition.

2.Dependency Preservation
 Dependency is an important constraint on the database.
 Every dependency must be satisfied by at least one decomposed table.
 If {A → B} holds, then two sets are functional dependent. And, it becomes more
useful for checking the dependency easily if both sets in a same relation.
 This decomposition property can only be done by maintaining the functional
dependency.
 In this property, it allows to check the updates without computing the natural join of
the database structure.

3.Lack of Data Redundancy

 Lack of Data Redundancy is also known as a Repetition of Information.
 The proper decomposition should not suffer from any data redundancy.
 The careless decomposition may cause a problem with the data.
 The lack of data redundancy property may be achieved by Normalization process.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 33

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
6.What is Normalization? Explain the properties that decomposition should
posses (MAY 2007)(APR-2019)
Normalization:

 Normalization of data is a process of analyzing the given relation schema based

on their functional dependencies and primary keys to achieve the desirable
properties of
 Minimizing redundancy
 Minimizing the insertion, deletion & update anomalies.
 This can be achieved by decomposition.
 Decomposition should posses 2 desirable properties
 Lossless join (or) non additive join
 Functional dependency preservation.
Prime Attribute:
Any attribute of relation schema R,I s called as prime attribute, if it is a
member of some candidate key of R.
Non prime Attribute:
Any attribute of relation schema R, is called as non-prime attribute, if it is not a
member of any candidate key.
Candidate key:
If a relation schema has more than one key, then each key is called as a candidate
key.
 The data in the database can be considered to be in one of a number of normal
forms.
 1st Normal Form
 2nd Normal Form
 3rd Normal Form
 Boyce/Codd Normal Form
 4th Normal Form
 5th Normal Form

7. Explain First Normal Form (1 NF) with a suitable Example? (MAY/JUNE

2012)

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 34

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
First Normal Form

 It is the process of eliminating duplicate forms from same data and creating the
separate tables for each group of related data and also to identify each row with
a unique column or set of columns.
 The table has repeating groups; it is called an unnormalized table.
 Relational databases require that each row only has a single value per
attribute, and so a repeating group in a row is not allowed.
 A relation is in first normal form if it meets the definition of a relation:
1. Each attribute (column) value must be a single value only.
2. All values for a given attribute (column ) must be of the same type.
3. Each attribute (column) name must be unique.
4. No two tuples (rows) in a relation can be identical.
o Example:

We re-arrange the relation (table) as below, to convert it to First Normal

Form.

Summary: 1NF
 A relation is in 1NF if it contains no repeating groups
 To convert an unnormalised relation to 1NF either

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 35

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
 Flatten the table and change the primary key or
 Decompose the relation into smaller relations, one for the repeating groups and
one for the non-repeating groups.
 Remember to put the primary key from the original relation into both new
relations.
 This option is liable to give the best results.
8. Explain Second Normal Form (2 NF) with a suitable Example? (MAY/JUNE
2012)
Second Normal Form

 A relation is in 2NF if and only if, it is in 1NF and every non-key attribute is
fully functionally dependent on the whole key (primary key). i.e. All attributes
are fully dependent on primary key.

 A relation is in 2NF if it contains no repeating groups and no partial key

functional dependencies.

 To convert a relation with partial functional dependencies to 2NF. Create a set

of new relations:
o One relation for the attributes is fully dependent upon the key.
o One relation for each part of the key that has partially dependent
attributes.

 A relation is in second normal form (2NF) if its non-prime attributes are fully
functionally dependent on primary key.

 A relation is in second normal form if it is free from partial-key dependencies

Prime attribute − an attribute, which is a part of the prime-key, is known
as a prime attribute.
Non-prime attribute − an attribute, which is not a part of the prime-key, is
said to be a non-prime attribute.
Types of functional dependency,
o Full functional dependency
o Partial functional dependency

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 36

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Full functional dependency:
o A functional dependency P → Q is fully functional dependency if removal
of any attribute A from P means that the dependency does not hold any
more.
o Example:
If AD → C, is fully functional dependency, then we cannot remove A or D.
i.e. C is fully functional dependent on AD. If we are able to remove A or
D, then it is not fully functional dependency.
Partial functional dependency:
o A functional dependency P → Q is partial functional dependency if
removal of any attribute A from P means that the dependency still holds.
Example:

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30

25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a

proper subset of a candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 37

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT

25 Chemistry

25 Biology

47 English

83 Math

83 Computer

Summary: 2NF
 A relation is in 2NF if it contains no repeating groups and no partial key
functional dependencies.
 Rule: A relation in 1NF with a single key field must be in 2NF
 To convert a relation with partial functional dependencies to 2NF. Create a set
of new relation.
 One relation for the attributes that are fully dependent upon the key
 One relation for each part of the key that has partially dependent attributes.
9 Explain Third Normal Form (3 NF) with a suitable Example? (MAY/JUNE 2012)
Third Normal Form
the third normal form let us first discuss the concept transitive dependency, super
key and candidate key .

Concept of Transitive Dependency

A functional dependency is said to be transitive if it is indirectly formed by tw
functional dependencies. For example -
X -> Z is a transitive dependency if the following functional dependencies hold

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 38

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
true:

X->Y
Y->Z
Concept of Super key and Candidate Key
Superkey : A super key is a set or one of more columns (attributes) to unique identify
rows in a table.
Candidate key: The minimal set of attribute which can uniquely identify a
tupleknown as candidate key. For example consider following table

RegID RollNo Sname

101 1 AAA
102 2 BBB
103 3 CCC
104 4 DDD
Superkeys
 {RegID}
 {RegID, RollNo}
 {RegID,Sname}
 {RollNo,Sname}
 {RegID, RollNo,Sname}
Candidate Keys
 {RegID}
 {RollNo}
Third Normal Form
A table is said to be in the Third Normal Form when,
i) It is in the Second Normal form.(i.e. it does not have partial functional
dependency
ii) It doesn't have transitive dependency.
Or in other words
In other words 3NF can be defined as : A table is in 3NF if it is in 2NF and for each

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 39

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
functional dependency
x-> y
at least one of the following conditions hold:
i) X is a super key of table
ii) Y is a prime attribute of table
For example: Consider following table Student _details as follows -
sid sname zipcode cityname state
1 AAA 11111 Pune Maharashtra
2 BBB 22222 Surat Gujarat
3 CCC 33333 Chennai Tamilnadu
4 DDD 44444 Jaipur Rajastan
5 EEE 55555 Mumbai Maharashtra
Here
Super keys: {sid},{sid,sname},{sid,sname,zipcode}, [sid.zipcode.cityname] ... and so
on.
Candidate keys: {sid}
Non-Prime attributes: {sname,zipcode,cityname,state}
The dependencies can be denoted as
sid-c-sname
sid-e-zipcode
zipcode- >cityname
cityname->state
The above denotes the transitive dependency. Hence above table is not in 3NF. We
can convert it into 3NF as follows:
Student
sid sname zip code
1 AAA 11111
2 BBB 22222
3 CCC 33333
4 DDD 44444
5 EEE 55555

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 40

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
ZIP

zipcode cityname state

11111 Pune Maharashtra
22222 Surat Gujarat
33333 Chennai Tamilnadu
44444 Jaipur Rajasthan
55555 Mumbai Maharashtra

Summary: 3NF
 A relation is in 3NF if it contains no repeating groups, no partial functional
dependencies, and no transitive functional dependencies.
 T converts a relation with transitive functional dependencies to 3NF, remove
the attributes involved in the transitive dependency and put them in a new
relation.
 Rule: A relation in 2NF with only one non-key attribute must be in 3NF
 In a normalized relation a non-key field must provide a fact about the key, the
whole key and nothing but the key.
 Relations in 3NF are sufficient for most practical database design problems.
However, 3NF does not guarantee that all anomalies have been removed.
10. Explain Boyce/Codd Normal Form (BCNF) with a suitable Example?
(MAY/JUNE 2012)
Boyce/Codd Normal Form (BCNF)
 Boyce and Codd Normal Form is a higher version of the Third Normal form.
This formdeals 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:
i) R must be in 3rd Nohnal Form
ii) For each functional dependency ( X ~ Y ), X should be a super Key. In simple
words if Y is a prime attribute then X can not be non prime attribute.
For example - Consider following table that represents that a Student
enrollment for the course-

Enrollment Table

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 41

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
sid course Teacher
1 C Ankita
1 Java Poonam
2 C Ankita
3 C++ Supriya
4 C Archana
From above table following observations can be made:
 One student can enroll for multiple courses. For example student with sidel
enroll for C as well as Java.
 For each course, a teacher is assigned to the student.
 There can be multiple teachers teaching one course for example course C car
taught by both the teachers namely - Ankita and Archana.
 The candidate key for above table can be (sid,course), because using these
columns we can find
 The above table holds following dependencies
 (sid,course)->Teacher
 Teacher->course
 The above table is not in BCNF because of the dependency teacher-s-course.
that the teacher is not a superkey or in other words, teacher is a non p
attribute and course is a prime attribute and non-prime attribute derives thep
attribute.
 To convert the above table to BCNF we must decompose above table into Stu
and Course tables
Student

sid Teacher
1 Ankita
1 Poonam
2 Ankita
3 Supriya
4 Archana
Course

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 42

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Teacher course
Ankita C
Poonam Java
Ankita C
Supriya C++
Archana C

Now the table is in BCNF

11. Explain the concept of Multivalued dependency and Fourth
NormalForm(4NF)?(MAY/JUNE 2012)

Concept of Multivalued Dependencies

 A table is said to have multi-valued dependency, if the following conditions
are true,
1. For a dependency A -7 B, if for a single value of A, multiple values of B
exists, then the table may have multi-values 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.
If all these conditions are true for any relation(table), it is said to have multi-
valued dependency .
 In simple terms, if there are two columns A and B - and for column A if there
are
multiple values of column B then we say that MVD exists between A and B
 The multivalued dependency is denoted by 
 If there exists a multivalued dependency then the table is not in 4th normal
form.
 For example: Consider following table for information about student
Student

sid Course Skill

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 43

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
1 C English
C++ German
2 Java English
French

Here sid =1 leads to multiple values for courses and skill. Following table shows
this

sid Course Skill

1 C English

1 C++ German

1 C German

1 C++ English

2 Java English
2 Java French

Here sid and course are dependent but the Course and Skill are
independent.the multivalued dependency is denoted as :
sid >Course
sid > Skill
Fourth Normal Form
Definition: 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(BCNF).
2) And, the table should not have any multi-valued dependency.
For example: Consider following student relation which is not in 4NF as it cont '
multivalued dependency.
Student Table

sid Course Skill

1 C English
1 C++ German
1 C German
1 C++ English

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 44

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
2 Java English
2 Java French
Now to convert the above table to 4NF we must decompose the table into
follows two tables.
Student_course table
key:sid,course)

sid Course
1 C
1 C++
2 Java
Student_skill table:
Key : (sid,skill)

sid Skill
1 English
1 German
2 English
2 French
Thus the table in in 4 th
normal form.
12. Explain Join Dependency and Fifth Normal Form (5 NF)? (MAY/JUNE 2013)
Join Dependency and Fifth Normal Form (5NF)

Concept of Fifth Normal Form

The database is said to be in 5NF if -
i) It is in 4th Normal Form
ii) If we can decompose table further to eliminate redundancy and anomalies
and when we rejoin the table we should not be losing the original data or
get a new record(join Dependency Principle)

The fifth normal form is also called as project join normal form.
For example - Consider following table
Seller Company Product
Rupali Godrej Cinthol
Sharda Dabur Honey

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 45

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Sharda Dabur HairOil
Sharda Dabur Rosewater
Sunil Amul Icecrearn
Sunil Britania Biscuits
Here we assume the keys as {Seller, Company, Product}
The above table has multivalued dependency as
Seller>{Company, Product}. Hence table is not in 4th Normal Form. To
make the above table in 4th normal form we decompose above table into two
tables as
Seller _Company

Seller Company
Rupali Godrej
Sharda Dabur
Sunil Amul
Sunil Britania
Seller_product

Seller Product
Rupali Cinthol
Sharda Honey
Sharda HairOil
Sharda RoseWater
Sunil Icecream
Sunil . Biscuits

The above table is in 4 th Normal Form as there is no multivalued dependency.

But it not in 5th normal form because if we join the above two table we may get

Seller I Company I Product

Rupal Godrej I Cinthol
i
Shard Dabur Honey
a
Shard Dabur HairOil
a
Shard Dabur Rosewater
a
------ - ..•. ------- ------
Sunil Amul Icecream
Sunil Amul Biscuits
I
PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 46
 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Sunil Britania Icecream
Sunil Britania Biscuits
newly added
records

To avoid the above problem we can decompose the tables into three tables as
Seller Company, Seller_Product, and Company Product table

Seller , Company

Seller Company
Rupali Godrej
Sharda Dabur
Sunil Amul
Sunil Britania
Seller_Product
Seller Product
Rupali Cinthol
Sharda Honey
Sharda HairOil
Sharda RoseWater
Sunil Icecream
Sunil Biscuit
Company_Product
Company Product
Godrej Cinthol
Dabur Honey
Dabur HairOil
Dabur RoseWater
Amul Icecream
Britania Biscuit

Thus the table in in 5 th normal form.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 47

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
13.Discuss in detail the steps involved in the ER to relational mapping in
the process of relational database design
 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.
 It 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.
 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.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 48

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

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.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 49

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

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.

APRIL MAY-2019
PART-A

1.Give the properties of decomposition? Pg-4 ,Qn-2

2.Define the terms Entity set and Relationship Set? Pg-10 ,Qn-43
PART-B

1.a) What is normalization? Explain in detail about all normal forms? Pg-38,Qn-6
to 12
b) Briefly discuss about the functional dependency Concept? Pg-34 ,Qn-4

NOVEMBER/DECEMBER 2019
PART-A

1.'Boyce-Codd normal form is found to be stricter than third normal form'.

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 50

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Justify the statement. (Q.NO:50,PAGE NO:9)

2.What is the significance of "participation role name" in the description of

relationship types? (Q.NO:51,PAGE NO:10)
PART-B
1.a) i)Discuss in detail the steps involved in the ER to relational mapping in the
process of relational database design.(Q.NO:13, PAGE NO:47)(7 Marks)
ii) Exemplify the multivalve dependency and fourth normal form 4NF.(Q.NO:11,PAGE
NO:42)(6 Marks)
b) i) Explain with suitable example ,the constraints of specialization and
generalization in ER data modeling (Q.NO:2,PAGE NO:17)(7 Marks)
ii) Exemplify the join dependency and fifth normal form – 5NF?(Q.NO:12,PAGE
NO:45)(6 Marks)
Part C
16. a) Consider the following information about a university database:
i) Professors have an SSN, a name, an age, a rank and a research
specialty.
ii) Projects have a project number, a sponsor name (e.g., NSF), a starting
date, an ending date and a budget.
iii) Graduate students have an SSN, a name, an age and a degreeprogram
(e.g., M.S. or Ph.D.).
iv) Each project is managed by one professor (known as the project's
principal investigator).
V)Each project is worked on by one or more professors (known as the
project's co- in vestiga tors).
vi) Professors can manage and/or work on multiple projects.
vii) Each project is worked on by one or more graduate students (knownas
the project's research assistants).
viii) When graduate students work on a project, a professor must supervise
their work on the project. Graduate students can work on multiple
projects, in which case they will have a (potentially different) supervisor
for each one.
ix) Departments have a department number, a department name and a
main office.
X)Departments have a professor (known as the chairman) who runs the
department.
xi) Professors work in one or more departments, and for each department
that they work in, a time percentage is associated with their job.
xii) Graduate students have one major department in which they are
working

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 51

 MAILAM ENGINEERING COLLEGE, MAILAM
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
on their degree.
xiii) Each graduate student has another, more senior graduate student
(known as a student advisor) who advises him or her on what courses to
take. Design and draw an ER diagram that captures the information about
the university.
Use only the basic ER model here; that is, entities, relationships and
attributes.
Be sure to indicate any key and participation constraints.(Q.NO:3,PAGE
NO:28)

b) i) For the following relation schema R and set of functional

dependencies F :
R(A, B, C, D, E), F = {AC ~ E, B ~ D, E ~ A}. List all candidate keys.
(6) (Q.NO: 4,PAGE NO:28)
ii) Consider the Table-16 and answer to queries given below. (9)
Table-16 User_personal.

Userid U_Email Fname Lname City State Zip

MA12 mani@ymail.co Manish Jain Bilaspu Chatisgarh 45899
P045 m
pujag@gmail.co Pooja Magg r
Kacch Gujrat 1
83221
LA33 m
lavle98@jj.com Lavleen
Dhalla Raipur Chatisgarh 85357 2
CH99 cheki9j@ih.co Chimal Bedi Trichy Tamil 8
63201
DA74 m
danu58@g.com Dany James Trichy Nadu
Tamil 1
64501
Nadu
1) Is this table in First Normal Form-1NF? Justify and normalize 8to INF
if
needed.
2) Is this table in Second Normal Form-2NF? Justify andnormalize to
2NF
if needed.
3) Is User_personal in Third Normal Form-3NF ? Justify and normalize
to 3NF if needed. (Q.NO:7 ,PAGE NO:33)

PREPARED BY:S.VANAKOVARAYAN AP/CSE, D.SARANYA AP/CSE 52