Relational Models

•Reference: Database System Concepts – 7th Edition,

Example of
an
‘Instructor’
Relation

Reference
 A1, A2, …, An are attributes
R = (A1, A2, …, An ) is a relation schema

Relation Example:
instructor = (ID, name, dept_name, salary)
Schema and
A relation instance r defined over schema R is denoted by r
Instance (R). values that are hold by the table at any instance
The current values a relation are specified by a table
An element t of relation r is called a tuple and is represented
by a row in a table

Reference:
 ▪ The set of allowed values for each attribute is called the domain of the
attribute

▪ Attribute values are (normally) required to be atomic; that is, indivisible

Attributes
▪ The special value null is a member of every domain. Indicated that the
value is “unknown”

▪ The null value causes complications in the definition of many operations

Reference:
 ▪ Order of tuples is irrelevant (tuples may be stored in an arbitrary
order)

▪ Example: instructor relation with unordered tuples

Relations are
Unordered

Reference:
 • Database schema -- is the logical structure of the database.
• Database instance -- is a snapshot of the data in the database at a given
instant in time.
• Example:
• schema: instructor (ID, name, dept_name, salary)
• Instance:

Database
Schema

Reference:
 ▪ Let K ⊆ R

▪ K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation
r(R) (the one that can uniquely identify tuples in a table )
• Example: {ID} and {ID,name} are both superkeys of instructor.

▪ Superkey K is a candidate key if K is minimal

Example: {ID} is a candidate key for Instructor (for relation instructor)

Keys ▪ One of the candidate keys is selected to be the primary key. Unique Key - can have null values.
• Which one? ( the one that has unique and non-null values)

▪ Foreign key constraint: Value in one relation must appear in another

• Referencing relation
• Referenced relation
• Example: dept_name in instructor is a foreign key from instructor referencing department
- foreign key refers to a foreign table, where that key is the primary key
- more than one foreign key in a single table is possible. Even all the
columns, implying solid data
Reference:
 Schema
Diagram for
University
Database

Reference:
 ▪ A procedural language consisting of a set of operations that take
one or two relations as input and produce a new relation as their
result.

▪ Six basic operators

Relational • select: σ
Algebra • project: ∏
• union: ∪
• set difference: –
• Cartesian product: x
• rename: ρ

Reference:
 ▪ The select operation selects tuples that satisfy a given predicate

▪ Notation: σ p (r)

▪ p is called the selection predicate

▪ Example: select those tuples of the instructor relation where the instructor is in
Select the “Physics” department.
• Query
Operation
σ dept_name=“Physics” (instructor)
• Result

Reference:
 ▪ Comparisons are allowed using
=, ≠, >, ≥. <. ≤
in the selection predicate.

▪ We can combine several predicates into a larger predicate by using the connectives:
∧ (and), ∨ (or), ¬ (not)

▪ Example: Find the instructors in Physics with a salary greater $90,000, we write:
Select σ dept_name=“Physics” ∧ salary > 90,000 (instructor)

Operation
▪ The select predicate may include comparisons between two attributes.
• Example, find all departments whose name is the same as their building name:
• σ dept_name=building (department)

Reference:
 1
▪ A unary operation that returns its argument relation, with certain
attributes left out.
bas select wale se certain attributes ko erase kr deta
h based upon projection precidate
▪ Notation:
∏ A1,A2,A3 ….Ak (r)
where A1, A2, …, Ak are attribute names and r is a relation name.
Project
Operation
▪ The result is defined as the relation of k columns obtained by erasing
the columns that are not listed

▪ Duplicate rows removed from result, since relations are sets

Reference:
 ▪ Example: eliminate the dept_name attribute of instructor

▪ Query:

∏ID, name, salary (instructor)

Project ▪ Result:
Operation

Reference:
 ▪ The result of a relational-algebra operation is relation and therefore of
relational-algebra operations can be composed together into a
relational-algebra expression.

▪ Consider the query -- Find the names of all instructors in the Physics
department.
Composition
of Relational ∏name(σ dept_name =“Physics” (instructor))
Operations

▪ Instead of giving the name of a relation as the argument of the

projection operation, we give an expression that evaluates to a relation.

Reference:
 ▪ The Cartesian-product operation (denoted by X) allows us to combine
information from any two relations.

▪ Example: the Cartesian product of the relations instructor and teaches

is written as:
instructor X teaches

Cartesian-Prod
▪ We construct a tuple of the result out of each possible pair of tuples:
uct Operation one from the instructor relation and one from the teaches relation (see
next slide)

▪ Since the instructor ID appears in both relations we distinguish

between these attribute by attaching to the attribute the name of the
relation from which the attribute originally came.
• instructor.ID
• teaches.ID

Reference:
The instructor
X teaches
table

Reference:
 ▪ The Cartesian-Product
instructor X teaches
associates every tuple of instructor with every tuple of teaches.
• Most of the resulting rows have information about instructors who did
NOT teach a particular course.

▪ To get only those tuples of “instructor X teaches “ that pertain to

Join Operation instructors and the courses that they taught, we write:
σ instructor.id = teaches.id
(instructor x teaches ))

• We get only those tuples of “instructor X teaches” that pertain to

instructors and the courses that they taught.

▪ The result of this expression, shown in the next slide

Reference:
 ▪ The table corresponding to:
σ instructor.id = teaches.id
(instructor x teaches))

Join Operation

Reference:
Join Operation

Reference:
 ▪ The union operation allows us to combine two relations

▪ Notation: r ∪ s

▪ For r ∪ s to be valid.
1. r, s must have the same arity (same number of attributes)
2. The attribute domains must be compatible (example: 2nd
Union
column of r deals with the same type of values as does the
Operation 2nd column of s)

▪ Example: to find all courses taught in the Fall 2017 semester, or in the
Spring 2018 semester, or in both
∏course_id (σ semester=“Fall” Λ year=2017
(section)) ∪
∏course_id (σ semester=“Spring” Λ year=2018
(section))

Reference:
 • Result of:
∏course_id (σ semester=“Fall” Λ year=2017
(section)) ∪
∏course_id (σ semester=“Spring” Λ year=2018
(section))

Union
Operation

Reference:
 ▪ The set-intersection operation allows us to find tuples that are in both the
input relations.
▪ Notation: r ∩ s
▪ Assume:
• r, s have the same arity
• attributes of r and s are compatible
Set-Intersectio ▪ Example: Find the set of all courses taught in both the Fall 2017 and the
Spring 2018 semesters.
n Operation ∏course_id (σ semester=“Fall” Λ year=2017 (section)) ∩
∏course_id (σ semester=“Spring” Λ year=2018 (section))

• Result

Reference:
 ▪ The set-difference operation allows us to find tuples that are in one relation but
are not in another.

▪ Notation r – s

▪ Set differences must be taken between compatible relations.

• r and s must have the same arity
Set-Difference
• attribute domains of r and s must be compatible
Operation
▪ Example: to find all courses taught in the Fall 2017 semester, but not in the
Spring 2018 semester
∏course_id (σ semester=“Fall” Λ year=2017
(section)) −
∏course_id (σ semester=“Spring” Λ year=2018
(section))

Reference:
 ▪ It is convenient at times to write a relational-algebra expression by
assigning parts of it to temporary relation variables.

▪ The assignment operation is denoted by ← and works like assignment in a

programming language.

▪ Example: Find all instructor in the “Physics” and Music department.

Assignment
Physics ← σ dept_name=“Physics” (instructor)
Operation
Music ← σ dept_name=“Music” (instructor)
Physics ∪ Music

Reference:
 ▪ The results of relational-algebra expressions do not have a name that
we can use to refer to them. The rename operator, ρ , is provided for
that purpose

▪ The expression:
Rename ρx (E)
Operation returns the result of expression E under the name x

▪ Another form of the rename operation:

ρx(A1,A2, .. An) (E)

Reference:
 ▪ There is more than one way to write a query in relational algebra.

▪ Example: Find information about courses taught by instructors in the

Physics department with salary greater than 90,000

▪ Query 1
Equivalent σ dept_name=“Physics” ∧ salary > 90,000 (instructor)

Queries

▪ Query 2
σ dept_name=“Physics” (σ salary > 90.000 (instructor))

Reference:
Equivalent
Queries

Reference:
 ▪ There is more than one way to write a query in relational algebra.

▪ Example: Find information about courses taught by instructors in the

Physics department with salary greater than 90,000

▪ Query 1
Equivalent σ dept_name=“Physics” ∧ salary > 90,000 (instructor)

Queries

▪ Query 2
σ dept_name=“Physics” (σ salary > 90.000 (instructor))

Reference: