2010043223

Database Management Systems

Unit 2: Relational query languages

SEMESTER: 3
PREPARED BY:
 What is Data Model?
• Data Model is a logical structure of Database.
• Data models define how data is connected to each other and
how they are processed and stored inside the system.
• It describes database entities, attributes, relationship among
data, constrains etc.
• Data Model Provides a way to describe the design of database
at Physical, Logical and View level.
1. Entity-Relationship Model (E-R Model)
2. Relational Model
3. Network Model
4. Object Oriented Data Model
Entity-Relationship Model
• Entity-Relationship (ER) Model is based on
1. Entities and their attributes
2. Relationships among them
• Entity is a thing or object in the real world. (Person, Place,
object)
• It is graphical (pictorial) representation of database.
• ER diagram shows the complete logical structure of a database.
• It uses different types of symbols to represent different objects
Relation may
of database.
be
one to one
one to many
many to one
many to many
 Entity
• An entity is a person, a place or an object.
• An entity is represented by a rectangle which contains the
name of an entity.
• Entities of a college database are:
– Student
– Professor/Faculty
– Course Entity Name
– Department Symbol
– Result
– Class
Student
– Subject
Entity
Entity Set
• It is a set (group) of entities of same type.
• Examples:
– All persons having an account in a bank
– All the students studying in a college
– All the professors working in a college
– Set of all accounts in a bank
Attributes
• Attribute is properties or details about an entity.
• An attribute is represented by an oval containing name of an
attribute.
• Attributes of Student are:
– Roll No Attribute
– Student Name Name

– Branch Symbol
– Semester
– Address Name
RollNo
– Mobile No
– Age
– SPI Student

– Backlogs
 Types of Attribute

Simple Attribute Composite Attribute

Cannot be divided into subparts Can be divided into subparts
E.g. RollNo, CPI E.g. Name
(first name, middle name, last name)

Address
(street, road, city)
Symbol Symbol
Roll No Name

First name Last name

Middle name
Types of Attribute

Single-valued Attribute Multi-valued Attribute

Has single value Have multiple value
E.g. Rollno, CPI E.g. Phoneno
(person may have multiple phone nos)

EmailID
(person may have multiple emails)
Symbol Roll No Symbol Phone No
Types of Attribute

Stored Attribute Derived Attribute

It’s value is stored manually in It’s value is derived or calculated from
database other attributes.
E.g. Birthdate E.g. Age
(can be calculated using current date
and birthdate)
Symbol Symbol
Birthdate Age
Relationship
• Relationship is an association (connection) between several
entities.
• It should be placed between two entities and a line connecting it
to an entity.
• A relationship is represented by a diamond containing
relationship's name.

Relationship
Name

Symbol

Student Issue Book

E-R Diagram of a Library System

Primary Key Attribute Primary Key

s
RollNo Name BookNo Name
Relationship

Student Issue Book

Branch Sem Entities Author Price

• Each and every entity must have one primary key attribute.
• Relationship between 2 entities is called binary relationship.
Entity with all types of Attributes

Middle
Name
First Name Last Name
Single
Simple
Value
RollNo Name Composite Apartment
Derived Composite

Age Student Address Street

Multiple Stored
Value
Phone No Birth Date Area
 Descriptive Attribute

• Attributes of the relationship is called descriptive attribute.

Descriptive
Attribute

Issue
RollNo Name Date BookNo Name

Student Issue Book

Branch Sem Author Price

Mapping Cardinality

• It represents how different entity set are connected using a

relationship set.
• Mapping cardinality must be one of the following types:
1. One to One
2. One to Many
3. Many to One
4. Many to Many
 One-to-One relationship (1 – 1)
• An entity in A is associated with only one entity in B and an entity
in B is associated with only one entity in A.

customer borrow loan

A1 B1
C1 L1
A2 B2
C2 L2

A B C3 L3

• Example: A customer is connected with only one loan using the

relationship borrower and a loan is connected with only one
customer using borrower.
One-to-Many relationship (1 – N)
• An entity in A is associated with more than one entities in B and
an entity in B is associated with only one entity in A.

customer borrow loan

A1 B1
C1 L1
A2 B2
C2 L2

A B C3 L3

L4

• Example: A loan is connected with only one customer using

borrower and a customer is connected with more than one loans
using borrower.

17
Many-to-One relationship (N – 1)
• An entity in A is associated with only one entity in B and an entity
in B is associated with more than one entities in A.

customer borrow loan

A1 B1
C1 L1
A2 B2
C2 L2

A B C3 L3

C4

• Example: A loan is connected with more than one customer

using borrower and a customer is connected with only one loan
using borrower.
Many-to-Many relationship (N – N)
• An entity in A is associated with more than one entities in B and
an entity in B is associated with more than one entities in A.

customer borrow loan

A1 B1
C1 L1
A2 B2
C2 L2

A B C3 L3

C4 L4

• Example: A customer is connected with more than one loan

using borrower and a loan is connected with more than one
customer using borrower.
Weak Entity Set
• An entity set that does not have a primary key is called weak
entity set.
Payment-date

loan-no amount payment-no Payment-amount

loan L_P payment

Strong Entity Weak Entity Weak Entity

Set Relationship Set

• Weak entity set is indicated by double rectangle.

• Weak entity relationship set is indicated by double diamond.
Weak Entity Set

• The existence of a weak entity set depends on the existence of a

strong entity set.
• The discriminator (partial key) of a weak entity set is the set of
attributes that distinguishes all the entities of a weak entity set.
• The primary key of a weak entity set is created by combining the
primary key of the strong entity set on which the weak entity set
is existence dependent and the weak entity set’s discriminator.
• We underline the discriminator attribute of a weak entity set with
a dashed line.
• Payment entity has payment-no which is discriminator.
• Loan entity has loan-no as primary key.
• So primary key for payment is (loan-no, payment-no).
Generalization v/s Specialization

Generalization Specialization
The process of creation of group from The process of creation of sub-groups
various entities is called generalization. within an entity is called specialization.
It is Bottom-up approach. It is Top-down approach.
The process of taking the union of two The process of taking a sub set of higher
or more lower level entity sets to level entity set to form a lower level
produce a higher level entity set. entity set.
In Generalization, size of schema gets In Specialization, size of schema gets
reduced. increased.
Generalization is normally applied to group We can apply Specialization to a single
of entities. entity.
Generalization process starts with the Specialization process starts from a single
number of entity sets and it creates high- entity set and it creates a different entity set
level entity with the help of some common by using some different features.
features.
There is no inheritance in Generalization. There is inheritance in Specialization.
Generalization & Specialization example
Name Address

PID City
Person

ISA

Salary Employee Customer Balance

ISA

Full Time Part Time

Days Hour
Worked Worked
E-R diagram of Hospital
PatID Name HosID Name

Patient Admitted Hospital

Has Has

Medical Record Doctor

Report
MRID DrID Dr Name
Name
E-R diagram of Hospital
PatID Name HosID Name

Patient Admitted Hospital

ISA Has
RoomNo

Indoor Outdoor
Doctor
Has
IPDID OPDID
DrID Dr Name
Charge Medical Record

Report
MRID
Name
 Selection Operator

• Symbol: σ (Sigma)
• Notation: σ condition (Relation)
• Operation: Selects tuples from a relation that satisfy a given
condition.
• Operators: =, <>, <, >, <=, >=, Λ (AND), V (OR)
Selection Operator example
• Display the detail of students belongs to “CE” branch.

Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

σ Branch=‘CE’ (Student)
Output
RollNo Name Branch SPI
101 Raj CE 8
104 Punit CE 9
Selection Operator example
• Display the detail of students belongs to “CE” branch and having
SPI more than 8.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

σ Branch=‘CE’ Λ SPI>8 (Student)

Output
RollNo Name Branch SPI
104 Punit CE 9
Selection Operator example
• Display the detail of students belongs to either “EE” or “ME”
branch.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

σ Branch=‘EE’ V Branch=‘ME’ (Student)

Output
RollNo Name Branch SPI
102 Meet ME 9
103 Harsh EE 8
Selection Operator example
• Display the detail of students whose SPI between 7 and 9.

Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

σ SPI>7 Λ SPI<9 (Student)

Output
RollNo Name Branch SPI
101 Raj CE 8
103 Harshan EE 8
Projection Operator

• Symbol: ∏ (Pi)
• Notation: ∏ attribute set (Relation)
• Operation: Selects specified attributes of a relation.
• It removes duplicate tuples (records) from the result.
Projection Operator example
• Display rollno, name and branch of all students.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

∏ RollNo, Name, Branch (Student)

Output
RollNo Name Branch
101 Raj CE
102 Meet ME
103 Harsh EE
104 Punit CE
Cartesian Product / Cross Product
•
Cartesian Product / Cross Product
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

[Link] Name Branch [Link] SPI

101 Raj CE 101 8
101 Raj CE 103 9
102 Meet ME 101 8
102 Meet ME 103 9

If both relations have some attribute with the same name, it can
be distinguished by combing [Link]-name.
Cartesian Product / Cross Product
• Example:
Student Result
RollNo Name Branch Sem RollNo SPI BL Rank
101 Raj CE 3 101 8 1 2
102 Meet ME 5 103 9 0 1

[Link] Name [Link] SPI BL

101 Raj 101 8 1
101 Raj 103 9 0
102 Meet 101 8 1
102 Meet 103 9 0
Natural Join (Inner Join)
• Symbol:
• Notation: Relation-1 (R1) Relation-2 (R2)
• Operation: Natural join will retrieve consistent data from
multiple relations.
• It combines records from different relations that satisfy a given
condition.

Steps
Step – 1 It performs Cartesian Product
Step – 2 Then it deletes inconsistent tuples
Step – 3 Then it removes an attribute from
duplicate attributes
Natural Join (Inner Join) example
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9
Step 1 : Performs Cartesian Product

[Link] Name Branch [Link] SPI

101 Raj CE 101 8
101 Raj CE 103 9
102 Meet ME 101 8
102 Meet ME 103 9 Step 3 : Removes an
attribute
Step 2 : Removes inconsistent
Student Result tuples Student Result
[Link] Name Branch [Link] SPI RollNo Name Branch SPI
101 Raj CE 101 8 101 Raj CE 8
Natural Join (Inner Join) example

Branch Step 1 : Performs Cartesian Product

BID BName Head [Link] BName Head FID FName [Link]

1 CE Patel 1 CE Patel 1 Raj 1
2 ME Shah 1 CE Patel 2 Meet 2

Faculty 2 ME Shah 1 Raj 1

2 ME Shah 2 Meet 2
FID FName BID
1 Raj 1 Step 2 : Removes inconsistent
tuples
2 Meet 2 [Link] BName Head FID FName [Link]
Step 3 : Removes an
attribute
1 CE Patel 1 Raj 1
2 ME Shah 2 Meet 2

BID BName Head FID FName To perform a natural join there must
1 CE Patel 1 Raj be one common attribute (column)
2 ME Shah 2 Meet between two relations.
Outer Join
• Operation: In natural join some records are missing, if we want
that missing records than we have to use outer join.
• Types: Three types of Outer Join
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Left Outer Join
• Display all the tuples of the left relation even through there is no
matching tuple in the right relation.
• For such kind of tuples having no matching, the attributes of right
relation will be padded with null in resultant relation.
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME null
Left Outer Join example
• Display Name, Salary and Balance of the of all employees.

Employe Customer
e
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

∏ Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Meet 15000 8000
Jay 20000 null
Right Outer Join
• Display all the tuples of right relation even through there is no
matching tuple in the left relation.
• For such kind of tuples having no matching, the attributes of left
relation will be padded with null in resultant relation.
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
103 null null 9
Right Outer Join example
• Display Name, Salary and Balance of the of all customers.

Employe Customer
e
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

∏ Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Raj null 5000
Meet 15000 8000
Full Outer Join
• Display all the tuples of both of the relations. It also pads null
values whenever required. (Left outer join + Right outer join)
• For such kind of tuples having no matching, it will be padded with
null in resultant relation.
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME null
103 null null 9
Full Outer Join example
• Display Name, Salary and Balance of the of all employee as well
as customers.
Employe Customer
e
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

∏ Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Meet 15000 8000
Jay 20000 null
Raj null 5000