Database Management System

(DBMS)

Dr. Rahul Kumar Verma

Department of Computer Science

1
Number of Credits in this Course
• Lecture – 3 Credits
• Tutorial – 0 Credits
• Lab – 1 Credit
-------------------------------
Total – 4 Credits

Evaluation Scheme
Components Weightage
Quizzes 40 Marks
Class Participation 10 Marks
150 Marks for Theory
Mid-Term Exam 30 Marks
End-Term Exam 70 Marks
Continuous Lab Evaluation 100 Marks
Books
• Fundamentals of database systems (Ramez Elmsari · Shamkant B. Navathe)
• Database System Concepts (Avi Silberschatz · Henry F. Korth · S. Sudarshan)
Outlines
• Introduction of database
• File processing system
• Disadvantages of file processing system
• Transition to Database management system
• Characteristics of the database management system
• MCQs
• Summary

4
What is Database Management System (DBMS)?
• Data - Fact that can be recorded or stored
• e.g. Person Name, Age, Gender and Weight etc.
• Database - Collection of logically related data (i.e., record)
• Traditional Database (TDB): Text and Number
• Multimedia Database (MDP): Video, Speech, song, movie, etc.
• Geographic Information System (GIS): Images of earth
• Real-Time Database (RTD): Production, Supermarket – varying products data
• Data Warehouse (DW): Huge and Historical Data.

• Management - Manipulation, Searching and Security of data

• e.g. Viewing result in university website, Searching exam papers in university website etc.

• System - Programs or tools used to manage database

• e.g. SQL Server Studio Express, Oracle etc.

• Database Management System (DBMS) – Set of programs or software used to define, manipulate, retrieve and
manage data in a database.
• e.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.

• DB+DBMS = DBS (Database System)

Introduction
• A number of records created or exists at various places, like:
• Universities: students registration, grades, etc., form students records
• Banking: transactions records
• Airlines: reservations detail records
• Sales: customers, products, purchase
• Online Retailers: Order tracking
• Many more
• All these records are to be stored and managed properly for their efficient utilization.
• Data Base Management System (DBMS) helps in all those areas where handling of the
data is required.
• First of all, we will start with traditional data handling approach : File processing system.

6
Database System vs. File-processing system
• A file processing system is a software that stores and manage files in computer
hard disk.
• It allows access to single files or tables at a time.
• Data is directly stored in set of files.
• It contains flat files that have no relation to other files.
• File system consists of different files which are grouped into directories.
The directories further contain other folders and files.
• E.g. NTFS (New Technology File System), EXT (Extended File System)
• DBMS
• A Database Management System (DBMS) is a software that allows user to efficiently define,
create, maintain and share databases.
• E.g. Oracle, MySQL, MS SQL server, IBM DB2, MS Access, dBASE, SQLite, etc.

7
 How it works?

Accounts HRM Production

Department Department Department

Accounts HR Production
Data Data Data

Each department maintain their own set of data. There is no link between those data pools.
Example for file-processing system

• University records,
• The grade reporting office,
• may keep files of students and their grades.
• Programs to print a student’s transcript and to enter new grades.
• The accounting office,
• may keep track of students’ fees and their payments.
• Although both users are interested in data about students,
• Each user maintains separate files—and programs to manipulate
these files.
• This make redundancy in defining and storing data

9
Disadvantages file-processing system
• Data redundancy and inconsistency
• Redundancy occurs when same piece of the data is held in two or more separate places.
• Inconsistency occurs when similar data is kept in different format or values at two or more separate places.
• Data redundancy leads to data inconsistency.
• All copies may not be updated properly.
• Difficulty in accessing data
• It does not allow needed data to be retrieved in a convenient and efficient manner .
• More responsive data-retrieval systems are required .
• Data isolation
• Data is scattered in various files, and files may be in different formats.
• It is very difficult to handle.
• Integrity problems
• The data values stored in the database must satisfy certain types of constraints.
• It is difficult to change the programs , when new constraints are added.

10
Disadvantages file-processing system
• Atomicity problems
• It is essential in database that either complete query to be executed, or none, which means
either all the operations in a transaction executes or none.
• It is difficult to ensure atomicity in a conventional file-processing system.
• Failures may leave database in an inconsistent state with partial updates carried out.
• Concurrent-access anomalies
• For faster response, many systems allow multiple users to update the data simultaneously.
• In such an environment, interaction of concurrent updates is possible and may result in
inconsistent data.
• Security problems
• Not every user of the database system should be able to access all the data.
• As application programs are added to the file-processing system in an ad hoc manner,
enforcing such security constraints is difficult.

Data based system offer solutions to all the above problems

11
 How DBMS works?

Accounts HRM Production

Department Department Department

Data
Advantages of DBMS over File System
• No Redundant Data: Redundancy removed by data normalization. No data duplication saves
storage and improves access time.
• Data Consistency and Integrity: The root cause data inconsistency is data redundancy, since data
normalization takes care of the data redundancy, data inconsistency also been taken care of as
part of it.
• Data Security: It is easier to apply access constraints in database systems so that only authorised
user is able to access it. Each user has a different set of access thus data is secured form the
issues such as identity theft, data leaks and misuse of data.
• Privacy: Limited access means privacy of data.
• Easy access to data: Database systems manage data in such a way so that the data is easily
accessible with fast response times.
• Easy recovery: Since database systems keep the backup of data, its easier to do a full recovery of
data in case of failure.
• Flexible: Database systems are more flexible than file processing systems.
2
Characteristics of the Database Approach
• In the database approach, a single repository maintains data.
• It should be accessed by various users repeatedly through queries, transactions,
and application programs.
• The main characteristics of the database approach are:
• Self-describing nature of a database system
• Insulation between programs and data, and data abstraction
• Support of multiple views of the data
• Sharing of data and multiuser transaction processing

3
Self-Describing Nature of a Database System
• A fundamental characteristic of the database approach is
• It should contain not only the database itself but also contains its complete definition.
• The definition contains
• The structure of each file, the type and storage format of each data item, and various constraints
on the data.
• This information is called meta-data.

4
Example of a
Database:

5
Self-Describing Nature

6
Insulation between Programs and Data
• In traditional file processing, the structure of data files is embedded in the application
programs.
• So any changes to the structure of a file may require changing all programs that access
that file.
• In DBMS, structure of data files is stored separately from the access programs.

7
Data Abstraction
• An operation (also called a function or method) is specified in two parts.
• The interface (or signature) of an operation includes the operation name and the data types of its
arguments (or parameters).
• The implementation (or method) of the operation is specified separately and can be changed
without affecting the interface.
• User application programs can operate on the data by invoking these operations through their
names and arguments, regardless of how the operations are implemented.
• This may be termed program-operation independence.
• The characteristic that allows program-data independence and program-operation independence
is called data abstraction.
• A DBMS provides users with a conceptual representation of data that does not include many of the
details of how the data is stored or how the operations are implemented.

8
Support of Multiple Views of the Data
• A database typically has many types of users, each of whom may require a
different perspective or view of the database.
• A view may be a subset of the database or it may contain virtual data that is
derived from the database files.
• But this view is not explicitly stored.

Sharing of Data and Multiuser Transaction Processing

• It must allow multiple users to access the database at the same time.
• It is essential if data for multiple applications is to be operated on a single database.
• For this, DBMS must include concurrency control software.

9
Basis File System DBMS
File system is a software that manages and organizes
1. Structure DBMS is a software for managing the database.
the files in a storage medium within a computer.

2. Data Redundancy Redundant data can be present in a file system. In DBMS there is no redundant data.

It doesn’t provide backup and recovery of data if it is It provides backup and recovery of data even if it is
3.Backup and Recovery
lost. lost.

4. Query processing There is no efficient query processing in file system. Efficient query processing is there in DBMS.

There is more data consistency because of the process

5.Consistency There is less data consistency in file system.
of normalization.

It has more complexity in handling as compared to file

6. Complexity It is less complex as compared to DBMS.
system.

File systems provide less security in comparison to DBMS has more security mechanisms as compared to
7.Security Constraints
DBMS. file system.

8.Cost It is less expensive than DBMS. It has a comparatively higher cost than a file system.

9. Data Independence There is no data independence. In DBMS data independence exists

10
 DBMS Architecture
• The DBMS design depends upon its architecture. The basic client/server architecture is used to
deal with a large number of PCs, web servers, database servers and other components that are
connected with networks.
• This architecture consists of many PCs and a workstation which are connected via the network.
• DBMS architecture depends upon how users are connected to the database to get their
request done.
Types of DBMS Architecture:
 Types of DBMS Architectures
1-Tier Architecture:
• The database is directly available to the user.
• Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for
end users.
• The 1-Tier architecture is used for development of the local application, where programmers can
directly communicate with the database for the quick response.

2-Tier Architecture:

• The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the
client end can directly communicate with the database at the server side. For this interaction, API's
like: ODBC, JDBC are used.
• The user interfaces and application programs are run on the client-side. The server side is responsible to
provide the functionalities like: query processing and transaction management.
• To communicate with the DBMS, client-side application establishes a connection with the server side.
 Types of DBMS Architectures
2-Tier Architecture:

• The 2-Tier architecture is same as basic client-server. In

the two-tier architecture, applications on the client end
can directly communicate with the database at the
server side. For this interaction, API's
like: ODBC, JDBC are used.
• The user interfaces and application programs are run on
the client-side. The server side is responsible to provide
the functionalities like: query processing and transaction
management.
• To communicate with the DBMS, client-side application
establishes a connection with the server side.
 Types of DBMS Architectures
3-Tier Architecture:

• The 3-Tier architecture contains another layer between

the client and server. Here, client can't directly
communicate with the server.
• The application on the client-end interacts with an
application server which further communicates with the
database system.
• End user has no idea about the existence of the
database beyond the application server. The database
also has no idea about any other user beyond the
application.
• The 3-Tier architecture is used in case of large web
application.
Simplified database system environment

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
 Data Models
• A Data Model in DBMS is the concept of tools that are developed to summarize the
description of the database.
• It defines how the logical structure of a database is modeled.
• Data Models are fundamental entities to introduce abstraction in a DBMS.
• It defines how data is connected to each other and how they are processed and stored
inside the system.
• A Data Model is collection of conceptual tools for describing:
• Data
• Data Relationships
• Data Semantics
• Consistency Constraints
Types of Data Model
• Relational Model
• Entity-Relationship Model
• Object-based Data Model
• Semi-Structured Data Model
• Hierarchical Model
• Network Model

7
 Relational Model
• Relational Model is the most widely used model.
• In this model, the data is maintained in the form of a two-dimensional tables called relation.
• All the information is stored in the form of row and columns, where column represents attributes and row
represents records or tuples.

• Features:
• Simple
• Scalable
• Structural Independence
 E-R Model
• Entity-Relationship Model or simply ER
Model is a high-level data model
diagram
• In this model, we represent the real-
world problem in the pictorial form.
• It is also very easy for the developers to
understand the system by just looking at
the ER diagram.
• We use the ER diagram as a visual tool
to represent an ER Model.

• Features:
• Graphical Representation for Better
Understanding
• Helps to Design Database
 Object-Based Model
• Real-world problems are more closely represented through the object-based i.e. object-oriented data model.
• Both the data and relationship are present in a single structure known as an object.
• In this model, two are more objects are connected through links. We use this link to relate one object to
other objects.
 Hierarchical and Network Model

• Organizes the data in the hierarchical tree structure • Extension of the hierarchical model
• Hierarchy starts from the root and expands in the form of a tree
• Record can have more than one parent.
• Features:
• Features:
• One-to-many relationship
• Ability to Merge more Relationships
• Parent-Child Relationship
• Many paths
• Deletion Problem
• Circular Linked List
• Pointers
Data Models
• Data models describes a set of concepts
• Structure of a database
• Elements and their data types
• Records consisting of groups of elements
• Relationships among the records
• Operations for manipulating these structures
• Retrievals and updates
• Certain constraints that the database should obey
Categories of Data Models
• Conceptual (high-level) data models:
• Provide concepts that are close to the way many users perceive data.
• Entity Relationship (ER) Diagram comes under this model.
• Physical (low-level, internal) data models:
• Describe details of how data is stored in the computer.
• These are usually specified in an ad-hoc manner through DBMS design
• Implementation (representational) data models:
• Provide concepts that fall between the above two
• It is used by many commercial DBMS implementations (e.g. relational data models)
 Database Schema

• It is important to distinguish between the description of the database and the

database itself.
• The description of a database is called the database schema.
• Includes descriptions of the database structure, data types, and the constraints
on the database.
• It is specified during database design and is not expected to change frequently.
• Each object in the schema—such as STUDENT or COURSE— called a schema
construct.
Example of a simple database

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
Example of a Database Schema

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
 Database State or Instance
• The data in the database at a particular moment in time is called a
database state or snapshot.
• It changes every time we add new data in database.
• It is also called the current set of occurrences or instances in
the database.
• The term instance is also applied to individual database components, e.g.
record instance, table instance, entity instance.
• A state that satisfies the structure and constraints of the database is
called valid state.
Example of a database state

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
 Three-Schema Architecture
• Defines DBMS schemas at three levels:
• Internal schema at the internal level to describe physical storage structures and
access paths (e.g indexes).
• Typically uses a physical data model.
• Conceptual schema at the conceptual level to describe the structure and constraints
for the whole database for a community of users.
• The conceptual schema hides the details of physical storage structures.
• Uses a conceptual or an implementation data model.
• External schemas (view schemas) describes the database part that a particular user
group is interested and hides the remaining database from that user group.
• The view schema describes the end user interaction with database systems.
• Usually uses the same data model as the conceptual schema.
Three-Schema Architecture
Three-Schema Architecture
• Mappings among schema levels are needed to transform requests
and data.
• Programs refer to an external schema, and are mapped by the DBMS
to the internal schema for execution.
• Data extracted from the internal schema is reformatted to match the
user’s external view
• e.g. formatting the results of an SQL query for display in a Web page.
9
 Data Independence
• The ability to modify the schema at one level of the database system without
altering the schema at the next higher level.

• Logical Data Independence:

• The capacity to change the conceptual schema without having to change the external
schemas and their associated application programs.
• Physical Data Independence:
• The capacity to change the internal schema without having to change the conceptual
schema.
• For example, the internal schema may be changed when certain file structures are reorganized or
new indexes are created to improve database performance.
• Then only the mappings between this schema and higher-level schemas need to be changed in a
DBMS.
• The application programs need not be changed since they refer to the external schemas.
 View of Data in DBMS
• Database systems are made up of complex data structures.
• To ease the user interaction with database, the developers hide internal irrelevant details
from the users and provides abstract view of data to users. This process of hiding irrelevant
details from user is called Data Abstraction.
• Abstraction is one of the main features of database systems.

11
 Levels of Abstraction

• Physical Level (Internal Level):

• This is the lowest level of data abstraction.
• It describes how data is actually stored in database. You can get the complex data structure details at this
level.

• Logical Level (Conceptual Level):

• This is the middle level of 3-level data abstraction architecture.
• It describes what data is stored in database and what relationships exist among those data.

• View Level (External Level):

• Highest level of data abstraction.
• This level describes only part of the entire database, i.e., it describes the user interaction with database
system via application programs that hide details of data types.

12
Three Levels of Abstraction

• At View Level: User just interact with system using GUI. They are not
aware of how the data is stored.

• At the Logical Level: These records can be described as fields and

attributes along with their data types, their relationship among each
other can be logically implemented.
• The programmers generally work at this level because they are aware
of such things about database systems.

• At Physical Level: These records can be described as blocks of storage

(bytes, gigabytes, etc) in memory.
• These details are often hidden from the programmers.

13
Basic concepts
} What is Database Design?
Ê Database Design is a collection of processes that facilitate the designing, development, implementation and
maintenance of enterprise database management systems.
} What is E-R diagram?
Ê E-R diagram: (Entity-Relationship diagram)
Ê It is graphical (pictorial) representation of database.
Ê It uses different types of symbols to represent different objects of database.
Entity
} An entity is a person, a place or an object.
Entity Name
} An entity is represented by a rectangle which contains the name of an entity.
} Entities of a college database are: Symbol
Ê Student
Ê Professor/Faculty
Ê Course
Ê Department
Student Faculty Course
Ê Result
Ê Class
Ê Subject

Exercise Write down the different entities of bank database.

Exercise Write down the different entities of hospital database.

Entity Set or Entity Type
} 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. Attribute
Name
} An attribute is represented by an oval containing name of an attribute.
} Attributes of Student are: Symbol
Ê Roll No
Ê Student Name RollNo Name
Ê Branch
Ê Semester
Ê Address
Student
Ê Mobile No
Ê Age
Ê SPI
Ê Backlogs
Exercise Write down the different attributes of Faculty entity.

Exercise Write down the different attributes of Account entity.

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 Primary Key

Attributes
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.
Ternary Relationship
ProjectID Project Name

Project

FacID Name RollNo Name

Faculty Guide Student

Branch Technology Branch Sem

• Relationship between 3 entities is called ternary relationship.

• Number of entities participated in a relationship is called degree of relationship
Exercise
} Draw an E-R diagram of following pair of entities
Ê Customer & Account
Ê Customer & Loan
Ê Doctor & Patient
Ê Student & Project
Ê Student & Teacher
§ Note: Take four attributes per entity with one primary key attribute.
Keep proper relationship between two entities.
Types of Attributes

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 Name

Roll No First name Last name

Middle name
Types of Attributes

Single-valued Attribute Multi-valued Attribute

Has single value Has multiple (more than one) value
E.g. RollNo, CPI E.g. PhoneNo
(person may have multiple phone nos)
EmailID
(person may have multiple emails)
Symbol Symbol

Roll No Phone No
Types of Attributes

Stored Attribute Derived Attribute

It’s value is derived or calculated from
It’s value is stored manually in database
other attributes
E.g. Birthdate E.g. Age
(can be calculated using current date and
birthdate)
Symbol Symbol
Birthdate Age
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
Exercise
 Draw an E-R diagram of Banking Management System.
 Draw an E-R diagram of Hospital Management System.
 Draw an E-R diagram of College Management System.
 Take only 2 entities
 Keep proper relationship between two entities
 Use all types of attributes
Descriptive Attribute
 Attribute of the relationship is called descriptive attribute.

Descriptive
Attribute

Issue
RollNo Name Date BookNo Name

Student Issue Book

Branch Sem Author Price

Domain
 All possible values of any attribute is called domain of attribute.

 Domain of multi-valued attribute is the subset of value from the basic domain.

 Domain of composite attribute is the Cartesian product of domain of component of composite

attribute.
Address

House No. Street No.

{1, 2, 3, 4, 5, ……50} X {1, 2, 3, 4, 5, ……10}

 Null Attribute: In some cases a particular entity may not have some applicable value for an
attribute.
Role
 Roles are indicated by labeling the lines that connect diamonds (relationship) to rectangles
(entity).
 The labels “Coordinator” and “Head” are called roles; they specify Faculty entities interact with
whom via Reports_To relationship set.
 Role labels are optional, and are used to clarify semantics (meaning) of the relationship.

EmpID Name

Coordinator
Faculty Reports_To
Head

Branch Experience
Recursive Relationship
 An Entity set related to itself is called recursive relationship.
Recursive Relationship Set
 The same entity participates in a relationship set more than once then it is called recursive
relationship set.
FacID FName DeptID DName

Faculty Works Department

Post Recursive
Relationship
FName Post Set DName
Ajay Professor Prof. Computer
Harish Professor Civil
Ramesh HOD Mechanical
Mapping Cardinality (Cardinality Constraints)
 It represents the number of entities of another entity set which are connected to an entity
using a relationship set.

 It is most useful in describing binary relationship sets.

 For a binary relationship set the mapping cardinality must be one of the following types:
 One to One
 One to Many
 Many to One
 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

C3 L3
A B

 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

C3 L3
A B
L4

 Example: A loan is connected with only one customer using borrower and a customer is
connected with more than one loans using borrower.
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

C3 L3
A B
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

C3 L3
A B
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.
Mapping Cardinality (Cardinality Constraints) [Exercise]
 Draw an E-R diagram and specify which type of mapping cardinality will be there in the
following examples:
 Each customer has only one account in the bank and each account is held by only one customer. [single
account]
 Each customer has only one account in the bank but an account can be held by more than one customer.
[joint account]
 A customer may have more than one account in the bank but each account is held by only one customer.
[multiple accounts]
 A customer may have more than one account in the bank and each account is held by more than one
customer. [join account as well as multiple accounts]
 A student can work in more than one project and a project can be done by more than one student.
 A student can issue more than one book but a book is issued to only one student.
 A subject is taught by more than one faculty and a faculty can teach more than one subject.
Participation Constraints
} It specifies the participation of an entity set in a relationship set.
} There are two types of participation constraints
Ê Total participation
Ê Partial participation

Partial participation Total participation

• some entities in the entity set may not participate in • every entity in the entity set participates in at least
any relationship in the relationship set. one relationship in the relationship set.
• indicated by single line • indicated by double line

customer borrow loan

C1 L1
Each customer has
maximum one loan C2 L2

C3
Cardinality Ratio or (min, max) Notation for Relationship
} Cardinality Ratio: It defines minimum and maximum entity occurrences that participating in
a relationship.
} Each entity e in E participates in at least min and at most max relationship instances in R.
} Must have min£max, 0£ min £1£max, Default(no constraint): min=0, max=n.
(min, max)
(0, 1) (1, 2)
Assign
Employee Dept
to
e1 d1
e2 d2
e3
} Examples: e4
Ê A department has exactly one manager and an employee can manage at most one department.
§ Specify (1,1) for participation of DEPARTMENT in MANAGES
§ Specify (0,1) for participation of EMPLOYEE in MANAGES
Ê An employee can work for exactly one department but a department can have any number of employees.
§ Specify (1,1) for participation of EMPLOYEE in WORKS_FOR
§ Specify (1,n) for participation of DEPARTMENT in WORKS_FOR
Example: (min, max) notation for relationship
} Read the min, max numbers next to the entity type and looking away from the entity type

Note: If minimum cardinality is 0 then it is partial participation. If minimum cardinality is 1 then total participation.

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
Exercises:
} Interpret the following sample of E-R diagram and explain the relationship in terms of
participation.

(1, 6) (0, 100)

Student Register Course

} Draw the E-R diagram for the following:

Ê All project need not have employee. Maximum 25 employee can work on a project. Each employee can
participate at least two and maximum of 4 projects.

Note: If minimum cardinality is 0 then it is partial participation. If minimum cardinality is 1 then total participation.

Ramez Elmasri and Shamkant B. Navathe. 1989. Fundamentals of database systems. Benjamin-Cummings Publishing Co., Inc., USA.
Keys : Super Key and Candidate Key
} Key or Super Key: An attribute or collection of attributes whose value uniquely identify an
entity in the entity set.
Ê Example: rollno, empid.
} Note: A key may have more than one attribute and an entity set may have more than one
key.
Name Roll No. Branch
} Student Name and Roll is a super key.
Ajay 101 ENC
Tanya 102 ENC
} Candidate Key: Super key whose proper subset cannot be a super key. Rohan 104 CSE
Ê Example: {Roll No.}, {Name, Branch} Ajay 106 CSE
Jatin 108 EC
} Primary Key: Any one candidate key which is selected for
Super Key
manipulating the data base.
Candidate Key

Primary Key
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
 Entity types that do not contain any key attribute and can not be identified independently are
called weak entity.
 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).
Superclass v/s Subclass
Super Class Sub Class
A superclass is an entity from which another A subclass is an entity that is derived from
entities can be derived. another entity.
E.g, E.g,
an entity account has two subsets saving_account and current_account entities
saving_account and current_account are derived from entity account.
So an account is superclass. So saving_account and current_account are
subclass.
Account Super Class

Saving_Account Current_Account

Sub Class
Generalization v/s Specialization
Generalization Specialization
It extracts the common features of multiple It splits an entity to form multiple new entities
entities to form a new entity. that inherit some feature of the splitting entity.
Name Address Name Address

SPI Salary
Person Person
Name Name
ISA ISA
Address Address
Bottom-up approach

Student Faculty Student Faculty

SPI Salary SPI Salary

Generalization v/s Specialization
Generalization Specialization
The process of creation of group from various The process of creation of sub-groups within
entities is called generalization. an entity is called specialization.
It is Bottom-up approach. It is Top-down approach.
The process of taking the union of two or more The process of taking a sub set of higher level
lower level entity sets to produce a higher level entity set to form a lower level entity set.
entity set.
It starts from the number of entity sets and It starts from a single entity set and creates
creates high level entity set using some different low level entity sets using some
common features. different features.
Generalization & Specialization example

Name Address

PID City
Person

ISA

Salary Employee Customer Balance

ISA

Full Time Part Time

Days Worked Hour Worked

Exercise
 Give the examples of Generalization/Specialization in the following E-R diagram:
 Hospital Management System.
 College Management System.
 Bank Management System.
 Insurance Company.
Constraints on Specialization and Generalization

Constraints

Disjoint Participation

Non-disjoint Total Partial

Disjoint
(Overlapping) (Mandatory) (Optional)
Disjoint Constraint
 It describes relationship between members of the superclass and subclass and indicates
whether member of a superclass can be a member of one, or more than one subclass.
 Types of disjoint constraints
 Disjoint Constraint
 Non-disjoint (Overlapping) Constraint
Disjoint Constraint
 It specifies that the entity of a super class can belong to only one lower-level entity set (sub
class).
 Specified by ‘d’ or by writing disjoint near to the ISA triangle. Employee
Cricketer (Super class)
(Super class)

Disjoint
Batsman Bowler ISA
(Sub class) (Sub class)

Full-time Part-time
(Sub class) (Sub class)

All the players are associated with only one sub class either (Batsman or Bowler).
Non-disjoint (Overlapping) Constraint
 It specifies that an entity of a super class can belong to more than one lower-level entity set
(sub class).
 Specified by ‘o’ or by writing overlapping near to the ISA triangle. Employee
Cricketer (Super class)
(Super class)

Non-disjoint
Batsman Bowler ISA
(Sub class) (Sub class)

Faculty Head
(Sub class) (Sub class)

One player (Yuvraj singh) is associated with more than one sub class.
Constraints on Specialization and Generalization

Constraints

Disjoint Participation

Non-disjoint Total Partial

Disjoint
(Overlapping) (Mandatory) (Optional)
Participation (Completeness) Constraint
 It determines whether every member of super class must participate as a member of subclass
or not.
 Types of participation (Completeness) Constraint
 Total (Mandatory) participation
 Partial (Optional) participation
Total (Mandatory) Participation
 Total participation specifies that every entity in the superclass must be a member of some
subclass in the specialization.
 Specified by a double line in E-R diagram. Employee
Cricketer (Super class)
(Super class)

Batsman Bowler ISA

(Sub class) (Sub class)

Professor Head
(Sub class) (Sub class)

All the players are associated with minimum one sub class either (Batsman or Bowler).
Partial (Optional) Participation
 Partial participation specifies that every entity in the super class does not belong to any of the
subclass of specialization.
 Specified by a single line in E-R diagram. Employee
Cricketer (Super class)
(Super class)

Batsman Not associated with Bowler ISA

(Sub class) any sub class (Sub class)

Professor Head
(Sub class) (Sub class)
Aggregation represents relationship between a whole object and its component. Using
aggregation we can express relationship among relationships. Aggregation shows ‘has-a’ or ‘is-
part-of’ relationship between entities where one represents the ‘whole’ and other ‘part’.
Limitation of E-R diagram
 In E-R model we cannot express relationships between two relationships.

Relation 1 Relation Relation 2

Entity 1 Relation Entity 2

Limitation of E-R diagram

Customer
Company

Employee Works Department

Customer
Can not connect two relationship

Borrow Borrow

Loan Loan

Process of creating an entity by combining various components of

E-R diagram is called aggregation.
E-R diagram of Hospital Management System

MRID
PatID Name HosID Name

Medical Record Has Patient Admitted Hospital

Report Name ISA Has

Indoor Outdoor
Doctor
RoomNo
IPDID OPDID DrID Dr Name
Charge
Summery of Symbols used in E-R diagram

Customer Name Hold

Entity Attribute Relationship

EmpID Age PhoneNo

Primary Key Derived Multi Valued
Attribute Attribute Attribute

Payment PymtID Issue

Weak Entity Discriminating Weak Entity

Attribute Relationship
Role
Name
E R E R ISA

Total Role Specialization/

Participation Indicator Generalization
Summery of Symbols used in E-R diagram

Disjoint
E R E ISA ISA
One to One Total Disjoint
Specialization/ Specialization/
E R E Generalization Generalization
One to Many

E R E Overlapping

Many to One ISA ISA

Partial Overlapping
E R E
Specialization/ Specialization/
Many to Many Generalization Generalization
Structure of Relational Databases
Columns (5) Table (Relation): A database object that holds a
collection of data for a specific topic.
Student Table consist of rows and columns.
Attributes:
RollNo Name Branch Semester SPI
Title of column Column (Attribute): The vertical component of a
101 Raju CE 3 8
table. A column has a name and a particular data

Cardinality = No of tuples (7)

102 Mitesh CI 3 7 type; e.g. varchar, decimal, integer, datetime etc.
Rows or 103 Mayur CE 3 6
Tuples or Record (Tuple): The horizontal component of a
104 Nilesh EE 3 9
Records (7) table, consisting of a sequence of values, one for
105 Hitesh CI 3 7 each column of the table. It is also known as row.
106 Tarun ME 3 8
107 Suresh CE 3 9 A database consists of a collection of tables
(relations), each having a unique name.
Degree = No of columns (5)
Domain is a set of all possible unique values for a specific column.
Domain of Branch attribute is (CE, CI, ME, EE)

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 4

Super Key
• A super key is a set of one or more attributes whose values uniquely identifies
each record within a relation (table).
Super Key Super Key Super Key
EnrollNo (RollNo, Branch, Sem) (SPI, Name, BL)

EnrollNo RollNo Branch Sem SPI Name BL

190540107001 101 CE 3 8 Raju 0
190540107002 102 CE 3 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
190540106002 102 CI 3 9 Nilesh 0
180540107001 101 CE 5 7 Hitesh 1
180540106001 101 CI 5 8 Raju 0
180540106002 102 CI 5 9 Suresh 0

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 6

Candidate Key
• A candidate key is a subset of a super key.
• A candidate key is a single attribute or the least combination of attributes that
uniquely identifies each record in the table.
• A candidate key is a super key for which no proper subset is a super key.
• Every candidate key is a super key but every super key is not a candidate key.
Candidate Key Candidate Key
EnrollNo (RollNo, Branch, Sem)

EnrollNo RollNo Branch Sem SPI Name BL

190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 7

Primary Key
• A primary key is a candidate key that is chosen by database designer to identify
tuples uniquely in a relation (table).

Primary Key

Candidate Key Candidate Key

EnrollNo (RollNo, Branch, Sem)

EnrollNo RollNo Branch Sem SPI Name BL

190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 8

Alternate Key
• An alternate key is a candidate key that is not chosen by database designer to
identify tuples uniquely in a relation.

Primary Key Alternate Key

Candidate Key Candidate Key

EnrollNo (RollNo, Branch, Sem)

EnrollNo RollNo Branch Sem SPI Name BL

190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 9

Primary Key rules
• A primary key may have one or more attributes.
• There is only one primary key in the relation (table).
• A primary key attribute value cannot be NULL.
• Generally, the value of a primary key attribute does not change.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 10

Foreign Key
• A foreign key is used to link two relations (tables).
• A foreign key is an attribute or collection of attributes in one table that refers to
the primary key in another table.
• A table containing the foreign key is called the child table, and the table containing
the primary key is called the parent table.
Parent Child
Table Table

Student Project
EnrollNo Name Branch Sem ProjectID Title EnrollNo
190540107001 Raju CE 3 101 Bank 190540107001
190540107002 Mitesh CE 3 102 College 190540107002
190540107003 Nilesh CE 3 103 School 190540107003
190540107004 Meet CE 3 104 Hospital 190540107001

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 11

Relational Algebra Operations
Operator Description
Selection Display particular rows/records/tuples from a relation
Projection Display particular columns from a relation
Cross Product Multiply each tuples of both relations
Combine data or records from two or more tables
1. Natural Join / Inner Join
Joins
2. Outer Join
1. Left Outer Join 2. Right Outer Join 3. Full Outer Join
Combine the results of two queries into a single result.
Set Operators
1. Union 2. Intersection 3. Minus / Set-difference
Division Divides one relation by another
Rename Rename a column or a table

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 13

Selection Operator
Selection Operator
• Symbol: σ (Sigma)
• Notation: σ condition (Relation)
• Operation: Selects tuples from a relation that satisfy a given condition.
• Operators: =, <>, <, >, <=, >=, Λ (AND), V (OR)
Example Display the detail of students belongs to “CE” Branch. Answer σBranch=‘CE’ (Student)
Student Output
RollNo Name Branch SPI RollNo Name Branch SPI
101 Raju CE 8 101 Raju CE 8
102 Mitesh ME 9 104 Meet CE 9
103 Nilesh CI 9
104 Meet CE 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 15

Selection Operator [σ condition (Relation)]
Example Display the detail of students belongs to “CE” Branch and having SPI more than 8.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

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

Output
RollNo Name Branch SPI
104 Meet CE 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 16

Selection Operator [σ condition (Relation)]
Example Display the detail of students belongs to either “CI” or “ME” Branch.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

Answer σBranch=‘CI’ V Branch=‘ME’ (Student)

Output
RollNo Name Branch SPI
102 Mitesh ME 9
103 Nilesh CI 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 17

Selection Operator [σ condition (Relation)]
Example Display the detail of students whose SPI between 7 and 9.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 9

Answer σSPI>7 Λ SPI<9 (Student)

Output
RollNo Name Branch SPI
101 Raju CE 8

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 18

Exercise
• Write down the relational algebra for the student table. Student
• Display the detail of students whose RollNo is less than 104. RollNo Name Branch SPI
• Display the detail of students having SPI more than 8. 101 Raj CE 6
• Display the detail of students belongs to “CE” Branch having SPI
less than 8. 102 Meet ME 8
• Display the detail of students belongs to either “CE” or “ME” 103 Harsh EE 7
Branch.
• Display the detail of students whose SPI between 6 and 9. 104 Punit CE 9

Employee
} Write down the relational algebra for the employee table.
Ê Display the detail of all employee. EmpID Name Dept Salary
Ê Display the detail of employee whose Salary more than 10000. 101 Nilesh Sales 10000
Ê Display the detail of employee belongs to “HR” Dept having Salary more 102 Mayur HR 25000
than 20000.
103 Hardik HR 15000
Ê Display the detail of employee belongs to either “HR” or “Admin” Dept.
Ê Display the detail of employee whose Salary between 10000 and 25000 104 Ajay Admin 20000
and belongs to “HR” Dept.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 19

Projection Operator
Projection Operator
} Symbol: ∏ (Pi)
} Notation: ∏ attribute set (Relation)
} Operation: Selects specified attributes of a relation.
} It removes duplicate tuples (records) from the result.
Example Display RollNo, Name and Branch of all students. Answer ∏ RollNo, Name, Branch (Student)

Student Output
RollNo Name Branch SPI RollNo Name Branch
101 Raju CE 8 101 Raju CE
102 Mitesh ME 9 102 Mitesh ME
103 Nilesh CI 9 103 Nilesh CI
104 Meet CE 9 104 Meet CE

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 21

Exercise
Student
• Write down the relational algebra for the student table.
RollNo Name Branch SPI
• Display RollNo, Name and SPI of all students.
101 Raj CE 6
• Display Name and SPI of all students.
102 Meet ME 8
• Display the Name of all students.
103 Harsh EE 7
• Display the Name of all branches.
104 Punit CE 9

Employee
} Write down the relational algebra for the employee table.
Ê Display EmpID with Name of all employee. EmpID Name Dept Salary
Ê Display Name and Salary of all employee. 101 Nilesh Sales 10000
Ê Display the Name of all employee. 102 Mayur HR 25000
Ê Display the Name of all departments. 103 Hardik HR 15000
104 Ajay Admin 20000

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 22

Combined Projection & Selection Operation
Example Display RollNo, Name & Branch of “ME” Branch students.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7

Step-1 σBranch=‘ME’ (Student) Answer ∏ RollNo, Name, Branch (σBranch=‘ME’ (Student))

Output-1 Output-2
RollNo Name Branch SPI RollNo Name Branch
102 Mitesh ME 9 102 Mitesh ME

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 23

Combined Projection & Selection Operation
Example Display Name, Branch and SPI of students whose SPI is more than 8.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7

Step-1 σSPI>8 (Student) Answer ∏ Name, Branch, SPI (σSPI>8 (Student))

Output-1 Output-2
RollNo Name Branch SPI Name Branch SPI
102 Mitesh ME 9 Mitesh ME 9
103 Nilesh CI 9 Nilesh CI 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 24

Combined Projection & Selection Operation
Example Display Name, Branch and SPI of students who belongs to “CE” Branch and SPI is more than
7.
Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7

Step-1 σBranch=‘CE’ Λ SPI>7 (Student) Answer ∏ Name, Branch, SPI (σBranch=‘CE’ Λ SPI>7 (Student))
Output-1 Output-2
RollNo Name Branch SPI Name Branch SPI
101 Raju CE 8 Raju CE 8

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 25

Combined Projection & Selection Operation
Example Display Name of students along with their Branch who belong to either “ME” Branch or “CI” Branch.

Student
RollNo Name Branch SPI
101 Raju CE 8
102 Mitesh ME 9
103 Nilesh CI 9
104 Meet CE 7

Step-1 σBranch=‘ME’ V Branch=‘CI’ (Student) Answer ∏ Name, Branch (σBranch=‘ME’ V Branch=‘CI’ (Student))
Output-1 Output-2
RollNo Name Branch SPI Name Branch
102 Mitesh ME 9 Mitesh ME
103 Nilesh CI 9 Nilesh CI

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 26

Exercise
• Write down the relational algebra for the student table. Student
• Display Rollno, Name and SPI of all students belongs to “CE” Branch. RollNo Name Branch SPI
• List the Name of students with their Branch whose SPI is more than 8 101 Raj CE 6
and belongs to “CE” Branch.
• List the Name of students along with their Branch and SPI who belongs 102 Meet ME 8
to either “CE” or “ME” Branch and having SPI more than 8. 103 Harsh EE 7
• Display the Name of students with their Branch name whose SPI
between 7 and 9. 104 Punit CE 9

} Write down the relational algebra for the employee table. Employee
Ê Display the Name of employee belong to “HR” Dept and having salary EmpID Name Dept Salary
more than 20000. 101 Nilesh Sales 10000
Ê Display the Name of all “Admin” and “HR” Dept’s employee.
102 Mayur HR 25000
Ê List the Name of employee with their Salary who belongs to “HR” or
“Admin” Dept having salary more than 15000. 103 Hardik HR 15000
Ê Display the Name of employee along with their Dept name whose 104 Ajay Admin 20000
salary between 15000 and 30000.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 27

Cartesian Product / Cross Product
Cartesian Product / Cross Product
} Symbol: X (Cross)
} Notation: Relation-1 (R1) X Relation-2 (R2) OR Algebra-1 X Algebra-2
} Operation: It will multiply each tuples of Relation-1 to each tuples of Relation-2.
Ê Attributes of Resultant Relation = Attributes of R1 + Attributes of R2
Ê Tuples of Resultant Relation = Tuples of R1 * Tuples of R2

Example Perform Cross Product between Student and Result. Answer (Student) X (Result)

Student Result Output

RNo Name Branch RNo SPI Student.RNo Name Branch Result.RNo SPI
101 Raju CE 101 8 101 Raju CE 101 8
102 Mitesh ME 102 9 101 Raju CE 102 9
102 Mitesh ME 101 8
If both relations have some attribute with the same name, it can be
102 Mitesh ME 102 9
distinguished by combing relation-name.attribute-name.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 29

Cartesian Product / Cross Product Example
Example Perform Cross Product between Student and Result. Consider only selected attributes
• Student – RNo, Name and Branch
Student Result • Result – RNo, SPI and BL
RNo Name Branch Sem RNo SPI BL Rank
101 Raju CE 3 101 8 1 2
102 Mitesh ME 5 103 9 0 1

Answer ∏ RNo, Name, Branch (Student) X ∏ RNo, SPI, BL (Result)

Output
Student.RNo Name Branch Result.RNo SPI BL
101 Raju CE 101 8 1
101 Raju CE 103 9 0
102 Mitesh ME 101 8 1
102 Mitesh ME 103 9 0

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 30

Cartesian Product / Cross Product Example
Example Perform Cross Product between Student and Result. Consider only selected tuples
• Student – Branch=‘CE’ and Sem=3
Student Result • Result – SPI>7 and BL<1
RNo Name Branch Sem RNo SPI BL Rank
101 Raju CE 3 101 8 1 2
102 Mitesh ME 5 103 9 0 1
103 Om CE 3 105 7 2 3
104 Dhara CE 5

Answer σBranch=‘CE’ Λ Sem=3 (Student) X σSPI>7 Λ BL<1 (Result)

Output
Student.RNo Name Branch Sem Result.RNo SPI BL Rank
101 Raju CE 3 103 9 0 1
103 OM CE 3 103 9 0 1

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 31

Natural Join / Inner Join
Natural Join / Inner Join
} Symbol:
} Notation: Relation-1 (R1) Relation-2 (R2) OR Algebra-1 Algebra-2
} Operation: Natural join will retrieve consistent data from multiple relations.
Ê It combines records from different relations that satisfy a given condition.

Steps performed in Natural Join

Steps Description
Step – 1 It performs Cartesian Product
Step – 2 Then it deletes inconsistent tuples
Step – 3 Then it removes an attribute from duplicate attributes

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 33

Natural Join / Inner Join Example
Example Perform Natural Join between Student and Result. Answer (Student) (Result)

Student Result Output

RNo Name Branch RNo SPI RNo Name Branch SPI
101 Raju CE 101 8 101 Raju CE 8
102 Mitesh ME 103 9
To perform a Natural Join there must be one common
Steps performed in Natural Join attribute (column) between two relations.

Step:1 Perform Cross Product Step:2 Removes inconsistent tuples

Student.RNo Name Branch Result.RNo SPI Student.RNo Name Branch Result.RNo SPI
101 Raju CE 101 8 101 Raju CE 101 8
101 Raju CE 103 9 Step:3 Removes an attribute from duplicate
102 Mitesh ME 101 8 RNo Name Branch SPI
102 Mitesh ME 103 9 101 Raju CE 8

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 34

Natural Join / Inner Join Example
Example Perform Natural Join between Branch and Faculty. Answer (Branch) (Faculty)

Branch Faculty Output

BID BName HOD FID FName BID BID Bname HOD FID FName
1 CE Shah 101 Raj 1 1 CE Shah 101 Raj
2 ME Patel 103 Meet 2 2 ME Patel 103 Meet

To perform a Natural Join there must be one common attribute (column)

between two relations.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 35

Write down query for the following tables/relations
• Relations
• Student (Rno, Sname, Address, City, Mobile)
• Department (Did, Dname)
• Academic (Rno, Did, SPI, CPI, Backlog)
• Guide (Rno, PName, Fid)
• Faculty (Fid, Fname, Subject, Did, Salary)
Example List the name of students with their department name and SPI of all student belong to “CE” department.

Answer ∏ Sname, Dname, SPI (σDname=‘CE’ (Student (Department Academic)))

Example Display the name of students with their project name whose guide is “A. J. Shah”.

Answer ∏ Sname, Pname (σFname=‘A.J.Shah’ (Student (Guide Faculty)))

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 36

Exercise: Write down relational algebra for the following tables/relations
} Relations
Ê Student (Rno, Sname, Address, City, Mobile)
Ê Department (Did, Dname)
Ê Academic (Rno, Did, SPI, CPI, Backlog)
Ê Guide (Rno, PName, Fid)
Ê Faculty (Fid, Fname, Subject, Did, Salary)
} List the name of students with their department name having backlog 0.
} List the name of faculties with their department name and salary having salary more than 25000 and
belongs to “CE” department.
} List the name of all faculties of “CE” and “ME” department whose salary is more than 50000.
} Display the students name with their project name of all “CE” department’s students whose guide is
“Z.Z. Patel”.
} Display the name of faculties with their department name who belongs to “CE” department and tough
“CPU” subject having salary more than 25000.
} List the name of students with their department name doing project “Hackathon” under guide “I. I.
Shah”.
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 37
Outer Join
Section - 3.5
Outer Join
• In natural join some records are missing, if we want that missing records than we
have to use outer join.
Three types of Outer Join
Sr. Outer Join Symbol
1 Left Outer Join
2 Right Outer Join
3 Full Outer Join

To perform a Outer Join there must be one common attribute (column)

between two relations.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 39

Left Outer Join
} Symbol:
} Notation: Relation-1 (R1) Relation-2 (R2) OR Algebra-1 Algebra-2
} Operation:
Ê 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 Perform Left Outer Join between Student and Result. Answer (Student) (Result)

Student Result Output

RollNo Name Branch RollNo SPI RollNo Name Branch SPI
101 Raj CE 101 8 101 Raj CE 8
102 Meet ME 103 9 102 Meet ME NULL

Exercise What is the output of (Result) (Student).

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 40

Left Outer Join Example
Example Perform Left Outer Join between Student and Result. (Display RollNo, Name and SPI)

Student Result
RollNo Name Branch RollNo SPI BL
101 Raj CE 101 8 1
102 Meet ME 103 9 0

Answer ∏ RollNo, Name, SPI ((Student) (Result))

Output
RollNo Name SPI
101 Raj 8
102 Meet NULL

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 41

Right Outer Join
} Symbol:
} Notation: Relation-1 (R1) Relation-2 (R2) OR Algebra-1 Algebra-2
} Operation:
Ê 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 Perform Right Outer Join between Student and Result. Answer (Student) (Result)

Student Result Output

RollNo Name Branch RollNo SPI RollNo Name Branch SPI
101 Raj CE 101 8 101 Raj CE 8
102 Meet ME 103 9 103 NULL NULL 9

Exercise What is the output of (Result) (Student).

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 42

Right Outer Join Example
Example Perform Right Outer Join between Student and Result. (Display RollNo, Name and SPI)

Student Result
RollNo Name Branch RollNo SPI BL
101 Raj CE 101 8 1
102 Meet ME 103 9 0

Answer ∏ RollNo, Name, SPI ((Student) (Result))

Output
RollNo Name SPI
101 Raj 8
103 NULL 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 43

Full Outer Join
} Symbol:
} Notation: Relation-1 (R1) Relation-2 (R2) OR Algebra-1 Algebra-2
} Operation:
Ê 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 Perform Full Outer Join between Student and Result. Answer (Student) (Result)

Student Result Output

RollNo Name Branch RollNo SPI RollNo Name Branch SPI
101 Raj CE 101 8 101 Raj CE 8
102 Meet ME 103 9 102 Meet ME NULL
103 NULL NULL 9
Exercise What is the output of (Result)
(Student).
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 44
Full Outer Join Example
Example Perform Full Outer Join between Student and Result. (Display RollNo, Name and SPI)

Student Result
RollNo Name Branch RollNo SPI BL
101 Raj CE 101 8 1
102 Meet ME 103 9 0

Answer ∏ RollNo, Name, SPI ((Student)

(Result))
Output
RollNo Name SPI
101 Raj 8
102 Meet NULL
103 NULL 9
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 45
Set Operators
Set Operators
• Set operators combine the results of two or more queries into a single result.

Three types of Set Operators

Sr. Set Operator Symbol
1 Union U
2 Intersect / Intersection ∩
3 Minus / Set difference −

Conditions Set operators will take two or more queries as input, which must be union-compatible.

• Both queries should have same (equal) number of columns

• Corresponding attributes should have the same data type or domain

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 47

Conditions to perform Set Operators
Conditions-1 Both queries should have same (equal) number of columns.

Student Faculty Student Faculty

RNo Name Dept SPI FId Name Dept RNo Name Dept FId Name Dept
101 Raj CE 8 101 Patel CE 101 Raj CE 101 Patel CE
102 Meet ME 9 102 Shah ME 102 Meet ME 102 Shah ME
103 Jay CE 9 103 Dave ME 103 Jay CE 103 Dave ME

Conditions-2 Corresponding attributes should have the same data type.

Student Faculty Student Faculty

RNo Name Dept SPI FId Name Dept Sub RNo Name Dept SPI FId Name Dept Exp
101 Raj CE 8 101 Patel CE DS 101 Raj CE 8 101 Patel CE 5
102 Meet ME 9 102 Shah ME DBMS 102 Meet ME 9 102 Shah ME 3
103 Jay CE 9 103 Dave ME DF 103 Jay CE 9 103 Dave ME 4

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 48

Set Operators [Exercise]

Exercise Check whether following tables are compatible or not:

• A: (First_name(char), Last_name(char), Date_of_Birth(date))

• B: (FName(char), LName(char), PhoneNumber(number))
Χ (Not compatible) Both tables have 3 attributes but third attributes datatype is different.

• A: (First_name(char), Last_name(char), Date_of_Birth(date))

• B: (FName(char), LName(char), DOB(date))
ü (Compatible) Both tables have 3 attributes and of same data type.

• Person (PersonID, Name, Address, Hobby)

• Professor (ProfessorID, Name, OfficeAddress, Salary)
• (Not compatible) Both tables have 4 attributes but forth attributes datatype is different.
∏ Name, Address (Person) & ∏ Name, OfficeAddress (Professor)
• (Compatible) Both tables have 2 attributes and of same data type.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 49

Union Operator
} Symbol: U
} Notation: Relation-1 (R1) U Relation-2 (R2) OR Algebra-1 U Algebra-2
} Operation:
Ê It displays all the tuples/records belonging to the first relation (left relation) or the second relation (right
relation) or both.
Ê It also eliminates duplicate tuples (tuples present in both relations appear once).
Example Perform Union between Customer and Employee. Answer (Customer) U (Employee)
Customer Employee Output
Name Name Name
Raju Meet Manoj
Suresh Suresh Meet
Meet Manoj Raju
Suresh
Exercise Is there any difference in the output if we swap the tables in Union operator. (Employee) U (Customer).
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 50
Intersect/ Intersection Operator
} Symbol: ∩
} Notation: Relation-1 (R1) ∩ Relation-2 (R2) OR Algebra-1 ∩ Algebra-2
} Operation:
Ê It displays all the tuples/records belonging to both relations. OR
Ê It displays all the tuples/records which are common from both relations.

Example Perform Intersection between Customer and Employee. Answer (Customer) ∩ (Employee)
Customer Employee Output
Name Name Name
Raju Meet Meet
Suresh Suresh Suresh
Meet Manoj

Exercise Is there any difference in the output if we swap the tables in Intersection. (Employee) ∩ (Customer).
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 51
Minus/ Set difference Operator
} Symbol: −
} Notation: Relation-1 (R1) − Relation-2 (R2) OR Algebra-1 − Algebra-2
} Operation:
Ê It displays all the tuples/records belonging to the first relation (left relation) but not in the second relation
(right relation).

Example Perform Set difference between Customer and Employee. Answer (Customer) − (Employee)
Customer Employee Output
Name Name Name
Raju Meet Raju
Suresh Suresh
Meet Manoj

Exercise Is there any difference in the output if we swap the tables in Set difference. (Employee) − (Customer).
Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 52
Union Operators Example
Example Display Name of person who are either employee or customer.
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Answer ∏ Name (Customer) U ∏ Name (Employee)

Output
Name
Manoj
Raju
Suresh

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 53

Intersect/ Intersection Operators Example
Example Display Name of person who are employee as well as customer.
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Answer ∏ Name (Customer) ∩ ∏ Name (Employee)

Output
Name
Suresh

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 54

Minus/ Set difference Operators Example
Example Display Name of person who are employee but not customer.
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Answer ∏ Name (Employee) − ∏ Name (Customer)

Output
Name
Manoj

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 55

Minus/ Set difference Operators Example
Example Display Name of person who are customer but not employee.
Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Answer ∏ Name (Customer) − ∏ Name (Employee)

Output
Name
Raju

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 56

Set Operators [Exercise]
Exercise What is the output of following relational algebra for the below mentioned tables:

Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Algebra-1 ∏ ID, Name (Customer) U ∏ ID, Name (Employee)

Algebra-2 ∏ ID, Name, Balance (Customer) U ∏ ID, Name, Salary (Employee)

Algebra-3 ∏ ID, Name (Customer) ∩ ∏ ID, Name (Employee)

Algebra-4 ∏ ID, Name, Balance (Customer) ∩ ∏ ID, Name, Salary (Employee)

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 57

Set Operators [Exercise]
Exercise What is the output of following relational algebra for the below mentioned tables:

Customer Employee
ID Name Balance ID Name Dept Salary
1 Raju 10000 2 Suresh CE 8000
2 Suresh 20000 3 Manoj ME 9000

Algebra-1 ∏ ID, Name (Customer) − ∏ ID, Name (Employee)

Algebra-2 ∏ ID, Name, Balance (Customer) − ∏ ID, Name, Salary (Employee)

Algebra-3 ∏ ID, Name (Employee) − ∏ ID, Name (Customer)

Algebra-4 ∏ ID, Name, Balance (Employee) − ∏ ID, Name, Salary (Customer)

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 58

Division Operator
Division Operator
} Symbol: ÷ (Division)
} Notation: Relation1 (R1) ÷ Relation2 (R2) OR Algebra1 ÷ Algebra2
} Condition:
Ê Attributes of relation2/algebra2 must be a proper subset of attributes of relation1/algebra1.
} Operation:
Ê The output of the division operator will have attributes =
All attributes of relation1 – All attributes of relation2
Ê The output of the division operator will have tuples =
Tuples in relation1, which are associated with the all tuples of relation2.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 61

Division Operator Example
Example Perform Division operation between Student and Subject. Answer (Student) ÷ (Subject)

Student Subject Output

Name Subject Subject Name
Raj DBMS DBMS Rohit

Raj DS DS Suresh
Meet DS DF
Meet DF
Rohit DBMS
Rohit DS
Rohit DF
Suresh DBMS
Suresh DF
Suresh DS

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 62

Division Operator Example
A B1 B2 B3 B4
Sno PNo PNo PNo PNo PNo
S1 P1 P2 P2 P1 P2
S1 P2 P4 P2 P5
S1 P3 P4
S1 P4
Algebra (A) ÷ (B1) Algebra (A) ÷ (B2) Algebra (A) ÷ (B3) Algebra (A) ÷ (B4)
S2 P1
Output Output Output Output
S2 P2
SNo SNo SNo SNo
S3 P2
S1 S1 S1
S4 P2
S2 S4
S4 P4
S3
S5 P4
S4

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 63

Division Operator Example
Example List the name of students doing a project in all technologies.

Student Project
RNo Name Technology TID Technology
101 Raj .NET 1 .NET
101 Raj PHP 2 PHP
102 Meet .NET 3 Android
102 Meet PHP 4 iPhone
102 Meet iPhone
Answer ∏ Name, Technology (Student) ÷ ∏ Technology (Project)
102 Meet Android
103 Rohit Android Output

104 Suresh .NET Name

Meet
104 Suresh iPhone
104 Suresh Android

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 64

Division Operator Example

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 65

Division Operator Example

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 66

Rename Operator
Rename Operator
} Symbol: ρ (Rho)
} Notation: ρA (X1,X2….Xn) (Relation)
} Operation:
Ê The rename operation is used to rename the output relation.
Ê The result of rename operator are also relations with new name.
Ê The original relation name can not be changed when we perform rename operation on any relation.
} How to use:
Ê ρ x (E)
Returns a relation E under a new name X.
Ê ρ A1, A2. …,An (E)
Returns a relation E with the attributes renamed to A1, A2, …., An.
Ê ρ x(A1, A2. …,An) (E)
Returns a relation E under a new name X with the attributes renamed to A1, A2, …., An.

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 68

Rename Operator Example
Example Rename Example Rename attributes
table
Student Student
RNo Name CPI Rno Name CPI
101 Raj 8 101 Raj 8
102 Meet 9 102 Meet 9
103 Jay 7 103 Jay 7

Algebra ρPerson (Student) Algebra ρ(RollNo, StudentName, SPI) (Student)

Person Student
RNo Name CPI RollNo StudentName SPI
101 Raj 8 101 Raj 8
102 Meet 9 102 Meet 9
103 Jay 7 103 Jay 7

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 69

Rename Operator Example
Example Rename table and attributes both Example Rename particular attributes
Student Student
Rno Name CPI Rno Name CPI
101 Raj 8 101 Raj 8
102 Meet 9 102 Meet 9
103 Jay 7 103 Jay 7

Algebra ρPerson (RollNo, StudentName) (∏ RNo, Name (Student)) Algebra ρStudentName / Name (Student)

Person Student
RollNo StudentName Rno StudentName CPI
101 Raj 101 Raj 8
102 Meet 102 Meet 9
103 Jay 103 Jay 7

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 70

Rename Operator Example
Example Find out maximum CPI from student table. Step-1 ρA (Student) X ρB (Student)
Student Output-1
Rno Name CPI A.Rno A.Name A.CPI B.Rno B.Name B.CPI
101 Raj 8 101 Raj 8 101 Raj 8
102 Meet 9 101 Raj 8 102 Meet 9
103 Jay 7 101 Raj 8 103 Jay 7
102 Meet 9 101 Raj 8
Step-2 σA.CPI<B.CPI (ρA (Student) X ρB (Student))
102 Meet 9 102 Meet 9
Output-2 102 Meet 9 103 Jay 7
A.Rno A.Name A.CPI B.Rno B.Name B.CPI 103 Jay 7 101 Raj 8
101 Raj 8 102 Meet 9 103 Jay 7 102 Meet 9
103 Jay 7 101 Raj 8 103 Jay 7 103 Jay 7
103 Jay 7 102 Meet 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 71

Rename Operator Example
Example Find out maximum CPI from student table.

Student
Step-3 ∏ A.CPI (σA.CPI<B.CPI (ρA (Student) X ρB (Student)))
Rno Name CPI
101 Raj 8 Output-3
A.CPI
102 Meet 9
8
103 Jay 7
7

Step-2 σA.CPI<B.CPI (ρA (Student) X ρB (Student))

Output-2
A.Rno A.Name A.CPI B.Rno B.Name B.CPI
101 Raj 8 102 Meet 9
103 Jay 7 101 Raj 8
103 Jay 7 102 Meet 9

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 72

Rename Operator Example
Example Find out maximum CPI from student table.

Student
Step-3 ∏ A.CPI (σA.CPI<B.CPI (ρA (Student) X ρB (Student)))
Rno Name CPI
101 Raj 8 Output-3
A.CPI
102 Meet 9
8
103 Jay 7
7

Step-4 ∏ CPI (Student) − ∏ A.CPI (σA.CPI<B.CPI (ρA (Student) X ρB (Student)))

Student Output-3 Output

CPI A.CPI CPI
8 − 8 = 9
9 7
7

Prof. Firoz A Sherasiya #3130703 (DBMS) w Unit 3 – Relational Query Languages 73

DBMS
January 25, 2022
Relational Algebra - Example

Consider the following relational database schema consisting of the four relation schemas:

passenger (pid, pname, pgender, pcity)

agency (aid, aname, acity)

flight (fid, fdate, time, src, dest)

booking (pid, aid, fid, fdate)

Answer the following questions using relational algebra queries;

Question 1. Get the complete details of all flights to New Delhi
σdest=”N ewDelhi” (f light)

Question 2. Get the details about all flights from Chennai to New Delhi.
σsrc=”Chennai” ∧ dest=”N ewDelhi” (f light)

Question 3. Find only the flight numbers for passenger with pid 123 for flights to Chennai before
06/11/2020.
πf id [(σpid=123 (booking)) ▷◁ (σdest=”Chennai” ∧ f date<06/11/2020 (f light))]

Question 4. Find the passenger names for passengers who have bookings on at least one flight.
πpname (passenger ▷◁ booking)

Question 5. Find the passenger names of those who do not have any bookings in any flights.
πpname [(πpid (passenger) − πpid (booking)) ▷◁ P assenger]

Question 6. Find the agency names for agencies that located in the same city as passenger with passenger
id 123.
πaname (agency ▷◁pcity=acity (σpid=123 (passenger)))

Question 7. Get the details of flights that are scheduled on both dates 01/12/2020 and 02/12/2020 at
16:00 hours.
[σf date=01/12/2020 ∧ time=16:00 (f light)] ∩ [sigmaf date=02/12/2020 ∧ time=16:00 (f light)]

Question 8. Get the details of flights that are scheduled on either of the dates 01/12/2020 or 02/12/2020
or both at 16:00 hours.
[σf date=01/12/2020 ∧ time=16:00 (f light)] U [sigmaf date=02/12/2020 ∧ time=16:00 (f light)]

1
 2

Question 9. Find the agency names for agencies who do not have any bookings for passenger with id
123.
πaname [agency ▷◁ [πaid (agency) − πaid (σpid=123 (booking))]
Question 10. Find the details of all male passengers who are associated with Jet agency.
πpid,pname,pgender,pcity [σpgender=”M ale”∧aname=”Jet” (passenger ▷◁ booking ▷◁ agency)]