Relational Model: Prof P Sreenivasa Kumar Department of CS&E, IITM 1
Relational Model: Prof P Sreenivasa Kumar Department of CS&E, IITM 1
Relational Model: Prof P Sreenivasa Kumar Department of CS&E, IITM 1
Introduction
Proposed by E.F. Codd in the early seventies.
Most of the modern DBMS are relational.
Simple and elegant model with mathematical basis.
Led to the development of a theory of data dependencies
and database design.
Relational algebra operations –
crucial role in query optimization & execution.
Laid the foundation for the development of
Tuple relational calculus and then
Database standard SQL
Attribute domains
names
Domain – set of atomic (or indivisible ) values – data type
student
yearOf phoneNumber branch
studentName rollNumber
Admission Of Study
Sriram CS04B123 2004 9840110489 CS
Rajesh CS04B125 2004 9840110490 EC
…
Course Department
courseId name credits deptNo deptId name hod phone
An Example:
CE751 MASS 3 4
TRANSFER
ES04M001 Deepak
note: Mahesh is displayed only once because
ME04M001 Lalitha
project operation results in a set.
ME03M002 Mahesh
r1 ⋃ r2 = {t | t ∈ r1 or t ∈ r2};
r1 ∩ r2 = {t | t ∈ r1 and t ∈ r2}
r1 − r2 = {t | t ∈ r1 and t ∉ r2};
Courses
Department courseId cname credits deptNo
CS635 Algorithms 3 1
deptId name hod phone
CS636 A.I 4 1
1 Computer Science CS01 22576235
ES456 D.S.P 3 2
2 Electrical Engg. ES01 22576234 ME650 Aero 3 3
Dynamics
3 Mechanical Engg. ME01 22576233
• Notation : r = r1 * r2
• Another Definition
Division operator produces a relation R (X) that includes all
tuples t [X] in R1 (Z) that appear in R1 in combination with
every tuple from R2 (Y) where Z = X ⋃ Y
(c2, d2) is not present in the result of division as it does not appear
in combination with all the tuples of s in r
Find those students who have registered for all courses offered
in dept of Computer Science.
Schema
The set of operations called outer joins are used when all
tuples in relation r or relation s or both in r and s have
to be in result.
Professor
empId name sex startYear deptNo phone
CS01 GIRIDHAR M 1984 1 22576345
For each entity set and relationship set in E/R diagram we can
have a corresponding relational table with the same name as
entity set / relationship set
Each table will have multiple columns whose names are obtained
from the attributes of entity types/relationship types
Student
Student Hostel
RollNo Name Address RoomNo RoomNo HostelName
Student Professor
Name RollNo ProfId ProfId Name phone
M N
E1 R E2
R1 T R2
PK1 FK1 FK2 PK2
CourseTable
Example
M is N CourseID Credits Timing
PreReq
Of
Course PreRequisiteTable
Timing
CourseID PreRequisiteOf
CourseID
Credits