CS 212 Database Management System
Introduction to Relational Model
Outline
Structure of Relational Databases
Database Schema and Instance
Keys
Relational Query Languages
The Relational Algebra
Example of a Relation: Instructor relation
attributes
(or columns)
tuples
(or rows)
Example of a Relation: teaches relation
Attribute
The set of allowed values for each attribute is called the domain
of the attribute
Roll #: Alphanumeric string
Fname, Lname: Alphabetic string
DoB: Date
Passport #: String (Letter followed by 7 digits)- nullable
Aadhaar #: 12-digit number
Department: Alphabetic string
Attribute values are (normally) required to be atomic; that is,
indivisible
The special value null is a member of every domain. It indicates
that the value is “unknown”
Students
Roll # Fname Lname DoB Passport # Aadhaar # Department
15CS1026 Lalit Dubey 27-Mar-1997 L4032464 172861749239 Computer
16EE3029 Jatin Chopra 17-Nov-1996 null 152861649112 Mathematics
Database Schema and Instance
A1, A2, A3…..,An are attributes
R (A1, A2, A3…..,An) is a relation schema
Example:
instructor (ID, name, dept_name, salary)
Formally, for given sets D1, D2,…….Dn, a relation r is a subset of
D1 x D2 x…….x Dn
The current values (relation instance) of a relation are specified by a
table
An element t of r is a tuple, represented by a row in a table
A finite set of tuples in the relational database system represents
relation instance
Database Schema and Instance
Database schema: It is the logical structure of the database
Database instance: It is a snapshot of the data in the database at a
given instant of time
Example:
schema: instructor (ID, name, dept_name, salary)
Instance:
Relations are Unordered
Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
Example: instructor relation with unordered tuples
Keys
Let K R
K is a super key of R if values for K are sufficient to identify a
unique tuple of each possible relation r(R)
Example: {ID} and {ID, name} are both super keys of instructor.
Super key K is a candidate key if K is minimal
Example: {ID} is a candidate key for Instructor
One of the candidate keys is selected to be the primary key.
A surrogate key (or synthetic key) in a database is a unique
identifier for an entity set in the modeled world (or database)
The surrogate key is not derived from application data, unlike a natural
key, which is derived from application data
Keys
Super Key: Roll#, {Roll#, DoB}, …………
Candidate Keys: Roll#, Aadhaar#, {Fname, Lname}
Passport# cannot be a key. Why?
Primary Key: Roll#
Secondary/Alternate Key: {Fname, Lname}, Aadhaar#
Students
Roll # Fname Lname DoB Passport # Aadhaar # Department
15CS1026 Lalit Dubey 27-Mar-1997 L4032464 172861749239 Computer
16EE3029 Jatin Chopra 17-Nov-1996 null 152861649112 Mathematics
16EC2016 Smriti Mongra 23-Dec-1996 G3200125 190852699005 Electronics
16CE4038 Ramdin Minz 21-Jan-1995 X8300127 165721079131 Civil
Keys
Simple Key: Consists of a single attribute
Composite Key: {Fname, Lname}
Consists of more than one attribute to uniquely identify an entity
One or more of the attributes, which make up the key, are not simple
keys in their own right
Students
Roll # Fname Lname DoB Passport # Aadhaar # Department
15CS1026 Lalit Dubey 27-Mar-1997 L4032464 172861749239 Computer
16EE3029 Jatin Chopra 17-Nov-1996 null 152861649112 Mathematics
16EC2016 Smriti Mongra 23-Dec-1996 G3200125 190852699005 Electronics
16CE4038 Ramdin Minz 21-Jan-1995 X8300127 165721079131 Civil
Keys
Foreign key constraint: Value in one relation must appear in another
relation
Referencing relation: Enrolment
Foreign keys- Roll#, Course#
Referenced relations: Students, Courses
Compound key: A compound key consists of more than one attribute to
uniquely identify an entity occurrence
Each attribute, which makes up the key, is a simple key in its own right
{Roll#, Course#}
Students
Roll # Fname Lname DoB Passport # Aadhaar # Department
Courses
Course# Course Name Credits L-T-P Department
Enrolment
Roll # Course# Instructor ID
Relational Query Languages
Procedural vs. Non-procedural (or Declarative) languages
Procedural programming requires that the programmer tell
the computer what to do
That is, how to get the output from given inputs
The programmer must know an appropriate algorithm
Non-procedural programming: The programmer does not
provide a specific procedure for obtaining output
The programmer only need to know what relationships hold between
input and output
Example: Root of n is m such that m2 = n
Relational Query Languages
“Pure” query languages:
Relational algebra
Tuple relational calculus
Domain relational calculus
The above 3 pure languages are equivalent in computing
power
We will concentrate here on relational algebra
End
