Unit 2

Relational Data Model

Relational Model:-
Define
Domain: A domain is a set of all possible (or permissible) values in an attribute.
Tuple: A row or a record is called as tuple in relational database management system.
Relation: A relation is nothing but a table which can store data in rows and columns form
in relational database management system.

Advantages of relational database.

1) Controlled redundancy
2) Sharing of data
3) Improved data security
4) Consistency
5) Higher integrity
Primary Key: A primary key is an attribute in Relation that uniquely identifies the rows in
relation. A Primary key does not hold NULL values and duplicate values. OR A key which
is selected by the designer to uniquely identify the entity is called as Primary key.
Example: In a Student table(Rollno , Name, Percentage) , Rollno is the primary key

Candidate key In a relation there may be a key or combination of keys which uniquely
identify the record. Such a key is called as Candidate key.
Example : Consider a Student table (Rollno,Name,Percentage), if (Rollno) and(
Name)both are unique then both are identified as candidate keys. OR Consider a Student
table (Rollno,Name,Percentage), if (Rollno ,Name) is unique , then (Rollno, Name) can be
a candidate key if and only if Name and Rollno individually are not unique.

Foreign Key:-
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field
(or collection of fields) in one table that refers to the PRIMARY KEY in another table. It
acts as a cross-reference between tables because it references the primary key of another
table, thereby establishing a link between them.

Types of Relational constraints.

Relational Constraints Relational constraints are a set of rules. It is used to maintain the
quality of information. Integrity constraints ensure that the data insertion, updating, and
other processes have to be performed in such a way that data integrity is not affected. Thus,
integrity constraint is used to guard against accidental damage to the database. Types of
Relational integrity Constraints are as follows
1. Domain constraints
2. Entity integrity constraints
3. Referential Integrity Constraints

Domain Constraint –
It is used to maintain value according to user specification For example: Not null, check
constraint.
1) Not Null: By default, all columns in tables allows null values. When a NOT NULL
Constraint is enforced on column or set of columns it will not allow null values.
Example SQL> CREATE TABLE STUDENT (ROLL_NO NUMBER (5), NAME
VARCHAR2 (20) NOT NULL);
2) Check Constraint: The constraint defines a condition that each row must satisfy. A
single Column can have multiple check condition.
Example SQL> CREATE TABLE EMP (ID NUMBER (5), NAME VARCHAR2 (10),
SAL NUMBER (10) CHECK (SAL>15000));

3) The DEFAULT constraint provides a default value to a column when there is no value
provided while inserting a record into a table.
Example SQL> CREATE TABLE STUDENT (ROLL_NO NUMBER (5), NAME
VARCHAR2 (20) NOT NULL, COURSE VARCHAR(20)DEFAULT ‘DMS’);

Entity integrity constraints –

It provides a way of ensuring that changes made to the database by authorized users do not
result in a loss of data consistency. For example: Primary key, unique constraints
1) Primary Key constraint: It is used to avoid redundant/duplicate value entry within the
row of specified column in table. It restricts null values too.
Example SQL> CREATE TABLE EMP (ID NUMBER (5)PRIMARY KEY, NAME
VARCHAR2 (10), SAL NUMBER (10));

2) Unique Constraint: The UNIQUE constraint uniquely identifies each record in a

database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for
uniqueness of a column or set of columns. It allows null value.
Example CREATE TABLE PERSONS (P_ID NUMBER(5)UNIQUE, FIRSTNAME
VARCHAR2(20), CITY VARCHAR2(20));

Referential Integrity Constraint:

It establishes parent child relationship between two tables. For example :Foreign key
constraints
It is a relational database concept in which multiple tables share a relationship based on the
data stored in the tables, and that relationship must remain consistent. A value of foreign
key is derived from primary key which is defined in parent table.

Example CREATE TABLE DEPARTMENT (EMP_ID NUMBER(5) REFERENCES

EMP(EMP_ID), DNO NUMBER(3));

Entity:-
• An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
• Consider an organization as an example- manager, product, employee, department etc.
can be taken as an entity.
Entity set:-
• An entity set is a group of similar entities and these entities can have attributes.
• In terms of DBMS, an entity is a table or attribute of a table in database, so by showing
relationship among tables and their attributes, ER diagram shows the complete logical
structure of a database.
Strong Entity:-
• The strong entity has a primary key or Unique key.
• Weak entities are dependent on strong entity. Its existence is not dependent on any other
entity.
• Strong Entity is represented by a single rectangle −
• example, Professor is a strong entity here, and the primary key is Professor_ID.
Weak Entity:-
• The weak entity in DBMS do not have a primary key and are dependent on the parent
entity. It mainly depends on other entities.
• Weak Entity is represented by double rectangle −
• Continuing our previous example, Professor is a strong entity, and the primary key is
Professor_ID. However, another entity is Professor_Dependents, which is our Weak
Entity.

Attribute:-
• The attribute is used to describe the property of an entity. Eclipse is used to represent an
attribute.
• For example, id, age, contact number, name, etc. can be attributes of a student.
Types Of Attribute:-
• Key Attribute
• The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined.
•

Composite attribute:-
• An attribute that composed of many other attributes is known as a composite attribute.
The composite attribute is represented by an ellipse, and those ellipses are connected
with an ellipse.
Multivalued Attribute:-
• An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.
• For example, a student can have more than one phone number.

Derived Attribute:-
• An attribute that can be calculated from other attribute is known as a derived attribute. It
can be represented by a dashed ellipse.
• For example, A person's age changes over time and can be derived from another
attribute like Date of birth.
Different symbols used in ER-Model are the following:
Relationship:-
• A relationship is used to describe the relation between entities. Diamond is used to
represent the relationship.

One-to-One Relationship:-
• When only one instance of an entity is associated with the relationship, then it is
known as one to one relationship.
• For example, one Department have one Manager

One-to-many relationship:-
• When only one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then this is known as a one-to-many
relationship.
• For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.

Many-to-one relationship:-
• When more than one instance of the entity on the left, and only one instance of an entity
on the right associates with the relationship then it is known as a many-to-one
relationship.
• For example, Student enrolls for only one course, but a course can have many students.
Many-to-many relationship:-
• When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
• For example, Employee can assign by many projects and project can have many
employees.

Functional dependency
A functional dependency occurs when one attribute in a relation uniquely determine another
attribute.
(OR)
A relation say R attribute X is functionally dependent on attribute Y if every value in X in
the relation has exactly one value of Y in the given relation.

The functional dependency is represented as X Y, which specifies X is functionally

dependent on Y or Y attribute functionally determine the attribute X.

• 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.

Example:
Consider table : Employee( 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
We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

• Fully-Functional Dependency

• Partial Functional Dependency

• Transitive Dependency

• Multi-valued Dependency

Fully-Functional Dependency
• An attribute is fully functional dependent on another attribute, if it is Functionally
Dependent on that attribute and not on any of its proper subset.

• For example, an attribute Q is fully functional dependent on another attribute P, if it

is Functionally Dependent on P and not on any of the proper subset of P.

Project
Emp No No Hours
1 A1 12
2 B1 4
3 C1 8
4 D1 12

{Emp No, Project No} Hours

The number of hours spent on the project by a particular employee cannot be
determined with the Project Noalome. It needs the Emp No as well

Partial Functional Dependency

 • Partial Dependency occurs when a nonprime attribute is functionally dependent on
part of a candidate key.

Emp Project
No EmpName No Project Name Hours
1 Anil A1 ABC 12
2 Sunil B1 XYZ 4
3 Yogesh C1 PQR 8
4 Sumit D1 LMO 12

• In the above table, we have partial dependency; let us see how −

• The prime key attributes are EmpNo and ProjectNo.

• As stated, the non-prime attributes i.e. EmpName and PojectName should be

functionally dependent on part of a candidate key, to be Partial Dependent.

• The EmpName can be determined by EmpNo that makes the relation Partial
Dependent.

• The ProjectName can be determined by ProjectNo, which that the relation Partial
Dependent.

Multivalued Dependency
• When existence of one or more rows in a table implies one or more other rows in the
same table, then the Multi-valued dependencies occur.

• If a table has attributes P, Q and R, then Q and R are multi-valued facts of P.

• It is represented by double arrow −

• ->->

For our example:

• P->->Q
Q->->R
In the above case, Multivalued Dependency exists only if Q and R are independent
attributes.
Trivial functional dependency
Dependency between non prime key attribute
Suppose there is a relation R(X,Y,Z) and X->Y, Y-> Z
Then Z is said to be transitive dependent on X
Where Y and Z are Non prime attribute
And X is Prime attribute

Normalization
• Normalization is a systematic approach of decomposing tables to eliminate data
redundancy(repetition),ensuring data integrity and avoiding Insertion, Update and
Deletion Anomalies.

• It is a multi-step process that puts data into tabular form, removing duplicated data
from the relation tables.

Goals of Normalization
• Avoid Data Redundancy
• Ensure Data Integrity
• Avoid Data Anomaly
Data Anomalies
• Insertion
• Deletion
• Updation

First Normal Form (1NF)

A relation is said to be 1NF if and only if every entry of the relation has at most a single
(atomic) value.
OR
A relation R is said to be in first normal form (1NF) if the domain of all attributes of R are
atomic.
It does not allow multivalued attributes and composite attributes.

Example Supplier (sno, sname, location, pno, qty)

The above relation is in 1NF as all the domains are having atomic value. But it is not in
2NF.
Example 2
Faculty_Subject_details Before INF
Faculty Code Faculty Name Date of Birth Subject Hours
DSA 16
100 Yogesh 17/06/1964 SS 8
IS 12
MIS 16
101 Amit 24/12/1972 PM 8
IS 12
PWRC 8
102 Omprakash 3/2/1986 PCOM 8
IP 16

Faculty_Subject_details After 1NF

Sr. No Faculty Code Faculty Name Date of Birth Subject Hours
1 100 Yogesh 17/06/1964 DSA 16
2 100 Yogesh 17/06/1964 SS 8
3 100 Yogesh 17/06/1964 IS 12
4 101 Amit 24/12/1972 MIS 16
5 101 Amit 24/12/1972 PM 8
6 101 Amit 24/12/1972 IS 12
7 102 Omprakash 3/2/1986 PWRC 8
8 102 Omprakash 3/2/1986 PCOM 8
9 102 Omprakash 3/2/1986 IP 16

Second Normal Form (2NF)

Fully functional Dependency: If a & b are the attributes of the relation, b is fully
functionally dependent on a, if b is functionally dependent on a and a proper subset of a.
- So 2NF removes partial dependencies i.e. functionally dependent attributes are
removed from the relation by placing them in a new relation along with their copy of
determinants.
 - 2NF is achieved when relation is in 1NF and each record is fully dependent on
primary key of the relation for identification.

For Example If we consider following Teacher_details table.

Functional dependencies are as follows:

Teacher_id->Subject
Teacher_id->Teacher_Age
To convert the given table into 2NF, we decompose it into two tables considering above
functional dependencies:

Example 2
Faculty Subject
Faculty Faculty Sr. No Faculty Code Subject Hours
Code Name Date of Birth 1 100 DSA 16
100 Yogesh 17/06/1964 2 100 SS 8
101 Amit 24/12/1972 3 100 IS 12
102 Omprakash 3/2/1986 4 101 MIS 16
5 101 PM 8
6 101 IS 12
7 102 PWRC 8
8 102 PCOM 8
9 102 IP 16
Third Normal Form (3NF)
A relation is in 3NF, if it is in 2NF and no non-key attribute of the relation is transitively
dependent on primary key. Third normal form is used to minimize the transitive
dependency in order to remove the anomalies that arose in Second Normal Form.
Example :-
Sr. No Faculty Code Subject Hours
1 100 DSA 16
2 100 SS 8
3 100 IS 12
4 101 MIS 16
5 101 PM 8
6 101 IS 12
7 102 PWRC 8
8 102 PCOM 8
9 102 IP 16
In the above table Hours depend on Subject and Subject depends on Faculty Code and
Sr. No. But hours are not dependent on the Faculty Code nor Sr. No. Hence there exists a
transitive dependency between Sr. No, Subject and Hours. If the Faculty Code is deleted
due to transitive dependency, information regarding the Subject and Hours allotted to it
will also lost. So we can decompose the relation as follows:-
Faculty Faculty_Subject Subject_Hours
Faculty Faculty Sr. No Faculty Code Subject Subject Hours
Code Name Date of Birth DSA 16
1 100 DSA
100 Yogesh 17/06/1964 SS 8
2 100 SS
101 Amit 24/12/1972 IS 12
3 100 IS
102 Omprakash 3/2/1986 MIS 16
4 101 MIS
5 101 PM PM 8
6 101 IS IS 12
7 102 PWRC PWRC 8
8 102 PCOM PCOM 8
9 102 IP IP 16

BCNF:
Boyce Codd Normal Form (BCNF) is considered a special condition of third Normal form.
A table is in BCNF if every determinant is a candidate key. A table can be in 3NF but not in
BCNF. This occurs when a non key attribute is a determinant of a key attribute
Example of BCNF:
Let's assume there is a company where employees work in more than one department.
EMPLOYEE(EMP_ID,EMP_COUNTRY,EMP_DEPT,DEPT_TYPE,EMP_DEPT_N
O)
In the above table Functional dependencies are as follows:
1. EMP_ID -> EMP_COUNTRY
2. EMP_DEPT {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
1.EMP_COUNTRY table: EMP_ID EMP_COUNTRY
2.EMP_DEPT table: EMP_DEPT {DEPT_TYPE, EMP_DEPT_NO}
3. EMP_DEPT_MAPPING table:EMP_ID,EMP_DEPT

Functional dependencies:
1. EMP_ID EMP_COUNTRY
2. EMP_DEPT {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Now, this is in BCNF because left side part of both the functional dependencies is a key.
List of Advantages of Normalization are the following:
1. More efficient data structure.
2. Avoid redundant fields or columns.
3. More flexible data structure i.e. we should be able to add new rows and data values easily
4. Better understanding of data.
5. Ensures that distinct tables exist when necessary.
6. Easier to maintain data structure i.e. it is easy to perform operations and complex queries
can be easily handled.
7. Minimizes data duplication.
8. Close modeling of real world entities, processes and their relationships.

Benefits of denormalization
Reduce number of relations : It reduce the number of relations because it combines two
relations into one new relation.
Reduce number of foreign keys-It reduce number of foreign keys because number of
relations are reduced .
Minimizes need for joins-It minimizes need for joins because it combines many relations
into one.
Increase Performance - It increase performance of database by adding redundant data or
by grouping data.

Drawbacks of demoralization.
Slow Data Updates-It may speed up the retrieval but can slow down database updates
Increase size of relations -It can increase size of the relations due to combining multiple
relations into one single relation.
Complex implementation -It may simplify implementation in some cases but may make it
more complex in other.
Application Specific -It is always application-specific and needs to be re-evaluated if the
application changes.

Enhanced ER model :
Enhanced ER is a high-level data model that incorporates the extensions to the original ER
model. It is created to design more accurate database schemas. EER reflects data properties
and constraints more precisely. It also includes more complex requirements than traditional
application.

It is a diagrammatic technique for displaying the following concepts

Sub Class and Super Class
Specialization and Generalization
Union or Category
Aggregation
These concepts are used when they comes in EER schema and the resulting schema
diagrams called as EER Diagrams.

For example: Square, Circle, Triangle are the sub class of Shape super class.

Generalization uses bottom-up approach where two or more lower level entities combine
together to form a higher level new entity if they have common attributes in common. The
new generalized entity can further combine together with lower
level entity to create a further higher level generalized entity.
For Example, STUDENT and FACULTY can be generalized to a higher level entity called
PERSON
Q. Draw ER diagram for Banking system, to represent a customer has account
scenario. Identify entities with their attributes and draw a diagram.
Q. Consider a single table consisting following columns. Convert it into 2NF and 3NF.
Table (Supplier_no, Supplier_name, Supplier_city,. Order_no, Order_quantity,
order_amount, product_name)

Table 1 Schema given:

(Supplier_no,Supplier
Name,Supplier_city,Order_no,Order_quantity,Order_amount,Product_code,Pr oduct name)
Step 1.To convert It into 2NF, We have to decompose the given table into two tables with
fully functional dependencies and establishing a referential integrity constraint relationship
among the two tables.
Table2: Supplier Details (Supplier_no,Supplier_name,Supplier_city,Order_no)
Table 3:Order Details (Order_no, Order_ quantity, Order_amount, Product_code,
product_name)
Now the above two tables are in 2NF
Step 2: To convert the above tables in 3NF ,We have to decompose them in three tables
satisfying the transitive dependencies property.
Table 4: Supplier Details (Supplier_no,Supplier_name,Supplier_city)
Table 5: Order Details ((Order_no, Order_ quantity, Order_amount)
Table 6: Trasaction Details (Supplier_no, Order_no, Product_code, product_name)
Hence the above three tables are satisfying Transitive dependencies Thus they are in 3NF.
Q. Draw ER diagram of library management system in which library maintain the data of
books, borrowers, issue return details, fine collection, supplier of books etc. Assume
suitable data and display the relationship among entities.
Q. Consider the relation R with five attributes L, M, N, O, P You are given following
dependencies: L ->M, MN ->P, PO-> L
(i) List all keys for R.
(ii) Is R in 3NF?
Justify your answer.

(i) List all keys for R:

Since Right hand side does not have NO, it can be part of the key.
So, (NO)+ ={NO}
We will try other combinations with NO
(LNO)+ ={LNOMP} it is candidate key.
(MNO)+={MNOPL} it is candidate key.
(PNO)+={PNOLM} it is candidate key .
we get Keys as LNO,MNO,PNO.

(ii) IsRin3NF?:
M, P, L are prime attributes, so R(L,M,N,O,P) is in 3NF.

Q. Draw enhanced ER diagram for loan payment system. Consider the following entities:
(i) Loan (Loan_id, Loan_amount, Loan_date)
(ii) Payment (payment_id, Payment_date, Balance_amount)
(iii) Personal Loan (Personal Loan_no, Interest rate)
(iv) Home Loan (Home Loan_no, Interest rate)
Show strong entity set, weak entity set, super class and sub class.
Q. Consider following realtion student (Roll_No, name, class, total_marks, percentage,
Grade). Find appropriate dependencies and normalize upto 3NF.
Functional Dependencies:
Roll_no->name
Roll_no->class
total_marks->percentage
percentage ->Grade
1NF: Student(Roll_no, name. class, total_marks, percentage, Grade)

2NF: To convert It into 2NF, We have to decompose the given table into two tables with
fully functional dependencies and establishing a referential integrity constraint relationship
among the two tables.
Student(Roll_No, name, class )
Marks(Roll_No, total_marks, Percentage, Grade)

3NF: To convert the above tables in 3NF ,We have to decompose

them in three tables satisfying the transitive dependencies property
Student(Roll_No, name, class )
Marks(Roll_No, total_marks, percentage)
Grade (percentage, Grade)

Q. Consider given relation R = (A, B, C, D, E) with the following functional

dependencies {CE D, D B, C A}.
(i) List all key for R.
(ii) Identify the best normal form that R satisfies.
Step1: Find attributes that are neither on the LHS nor on RHS
--None
Step2:Find the attributes that are only on RHS
--A ,B
Step3: Find the attributes that are only on LHS.
--C, E
Step4: Combine the attributes on step 1 and 3
-CE
The attributes C and E will belong to candidate key, but to find others we need to calculate
closure of CE
Step5: Closure finding :
In our case, because with CE we get D and from D we get B and from C we get A
So we have only one candidate key that is CE The relation is in 1NF as it does not have
any composite as well as multivalued attribute.
But it is not in 2NF as the statement says that
1) It should be in 1NF
2) All non-key attributes are fully functionally dependent on primary key
In our case rule 2) is violated by C->A
Thus given relation is best suited for 1NF only.

Q.Draw the enhanced E-R diagram for College Management System and show strong
entity set, weak entity set, super class and sub class.
Q. Find 3NF decomposition of given relation schema.
Shipping (ship, capacity, date, cargo, value).
Functional dependencies Ship -> capacity.
Ship, date -> cargo.
Cargo, Capacity -> Value
R= (ship, capacity, date, cargo, value).
Functional dependencies Ship -> capacity.
Ship, date -> cargo.
Cargo, Capacity -> Value
1) Find all attributes in R that are not involved in any functional dependency.
Here no such attribute found.
2) R= (ship, capacity, date, cargo, value) No functional dependency has all the attributes.
3) For each Functional dependency
i)Ship -> capacity R1= (ship, capacity)
ii)Ship, date -> cargo. R2= (ship, date, Cargo)
iii) Cargo, Capacity -> Value R3= (cargo, capacity, value)
Above 3 relations R1, R2 and R3 gives 3NF decomposition which is lossless and
dependency preserving

Q. Let R = (A, B, C, D) and F={AB C, C A}

Find BCNF decomposition of R using the algorithm.
Let R1= (A, B, C, D) (Given relation)
Step:1 Find merged minimal cover of Functional Dependencies (FDs) which contain AB-
>C C->A
Round 1: Checking whether R1 is in BCNF The FD [ AB->C] violates BCNF as LHS is
not superkey , so table is split as below R2 = (A, B, C) R3= (A, B, D)
Round 2: Checking whether R2 is in BCNF The FD [ C->A] violates BCNF as the LHS is
not superkey, so table is split as below R4= (C, A) R5= (B, C) Relation R3, R4, and R5 are
in BCNF
Q. Construct an ER diagram for travel agency consider various entities such as travel
agency, passenger, branch, seat, bus, employee, tours etc. Design specialization and
generalization EER features.