CSTE 2201
Database Management System
Fateha Khanam Bappee, Ph.D.
Department of Computer Science and Telecommunications Engineering
Noakhali Science and Technology University
September 2025
Courtesy: Database System Concepts (7th Edition) by
Silberschatz, Korth & Sudarshan
Md. Mahbub Alam (Dalhousie University)
Introduction to the Relational Model
• Phases of Database Design
• Structure of Relational Databases
• Database schema
• Keys
• Schema Diagram
• Relational Query Languages
• The Relational Algebra
2
Phases of Database Design
3
Logical Design
• Mapping ER Models to Relational Models
• Data is represented in terms of the elements of relational model such as tables, columns, rows,
primary key, foreign keys and constraints.
4
Structure of Relational Database
• A relational database consists of a collection of tables (set of relations), each of
which is assigned a unique name.
• Relation:
- A relation is a named, two-dimensional table of data.
- A table consists of
Ø rows (records or tuples)
Ø columns (attributes or fields)
5
Structure of Relational Database
• Requirements for a table to qualify as a relation:
- Relation (table) must have a unique name
- Attributes (columns) in tables must have unique names
- Every attribute value must be atomic (not multivalued, not composite)
- Every row must be unique (can’t have two rows with exactly the same values for all
their fields)
- The order of the columns and rows are irrelevant
• The set of allowed values for each attribute is called the domain of the attribute
• The special value null is a member of every domain. Indicated that the value is
“unknown”
6
Relations are unordered
• Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
• Example: instructor relation with unordered tuples
7
Database Schema
• 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:
8
Keys in a Relation
• A key is a property of the entire relation, rather than of the individual tuples.
• The designation of a key represents a constraint in the real-world enterprise being
modeled.
§ Super key: A superkey is a set of one or more attributes that allow us to identify uniquely a tuple
in the relation.
Example: {ID} and {ID, name} are both superkeys of instructor.
If K is a superkey, then so is any superset of K.
§ Candidate key: Superkeys for which no proper subset is a superkey. Such minimal superkeys are
called candidate keys. It does not contain any redundant attributes.
Example: {ID} is a candidate key for Instructor
It is possible that several distinct sets of attributes could serve as a candidate key.
Example: {name, dept_name} is a candidate key for instructor
9
Keys in a Relation
§ Primary Key:
§ Uniquely identifies each row of a relation.
§ Primary key must have unique values for each row.
§ One of the candidate keys is selected to be the primary key.
§ Foreign Key:
§ Set of fields in one relation that is used to `refer’ to a tuple in another (or the same) relation.
§ A foreign key is defined in one table but references the primary key of another table
10
Keys in a Relation
§ Super key, candidate key and primary key:
Super key
Candidate key
Primary key
11
Composite Primary Keys
• Composite primary keys are made of more than one attribute
12
Relations with Primary-Key Attributes
13
Referential Integrity Constraint
• In a foreign-key constraint, the referenced attribute(s) must be the primary key of the
referenced relation.
• Referential-integrity constraint: a referential-integrity constraint, relaxes the requirement
that the referenced attributes form the primary key of the referenced relation.
instructor (ID, name, dept_name, salary) Referencing Relation of F. key constraint
Department (dept_name, building, budget) Referenced Relation
14
Schema Diagram
for University
Database
• A database schema, along
with primary key and
foreign-key constraints,
can be depicted by
schema diagrams.
15
Relational Query Language
• A query language is a language in which a user requests information
from the database.
• Query languages can be categorized as:
- Imperative
- Functional/Procedural
- Declarative
• Pure query languages:
- The relational algebra Functional
- The tuple relational calculus
Declarative
- The domain relational calculus
• The SQL query language includes elements of the imperative, functional, and
declarative approaches.
16
Relational Query Language
• Imperative:
Instructs the system to perform a specific sequence of operations on the database to compute
the desired result
• Functional/Procedural
The computation is expressed as the evaluation of functions that may operate on data in the
database or on the results of other functions;
• Declarative
The user describes the desired information without giving a specific sequence of steps or
function calls for obtaining that information;
17
Algebra?
• Mathematical system consisting of:
§Operands: Variables or values from which new values can be
constructed.
§Operators: Symbols denoting procedures that construct new values
from given values.
18
Relational Algebra
• An algebra whose
§Operands are relations or variables that represent relations.
§Operators are designed to do the most common things that we need
to do with relations in a database.
• The result is an algebra that can be used as a query language for
relations.
19
Core of Relational Algebra
• Union (∪), intersection (∩), and difference (−):
- Usual set operations, but both operands must have the same
relation schema.
• Selection (𝜎): picking certain rows.
• Projection (π): picking certain columns.
• Products and joins: compositions of relations.
• Renaming (ρ) of relations and attributes
20
Select Operation
• 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 the
“Physics” department.
üQuery:
üResult:
Instructor Relation
21
Select Operation
• In the selection predicate, we allow comparisons using:
=, ≠, >, ≥, <, ≤
• 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.
• The selection predicate may include comparisons between two attributes.
- Example: Find all departments whose name is same as their building name.
Instructor Relation
22
Project Operation
• The project operation is a unary operation that returns its argument relation, with
certain attributes left out.
• Duplicate rows are eliminated since relations are set
• Notation:
where A1, A2 are attribute names and r is a relation name.
• Example: eliminate the dept_name attribute of instructor
- Query:
23
Project Operation
• Example: eliminate the dept_name attribute of instructor
- Query:
- Result
Instructor Relation
24
The Rest of the Operations
• Composition of Relational Operations
• Cartesian-product Operation
• Join Operation
• Union Operation
• Set-intersection Operation
• Set Difference Operation
• The Rename Operation
***** Please follow the book slides ([Link])*********
[Link]
25
Practice Exercise
• Consider the following Employee database:
Give an expression in the relational algebra to express each of the following queries:
a. Find the name of each employee who lives in city “Miami”.
b. Find the name of each employee whose salary is greater than $100000.
c. Find the name of each employee who lives in “Miami” and whose salary is greater than
$100000.
26
Practice Exercise
27
Example: A Sample Database
28
Example 1
29
Example 1
30
Example 2
31
Example 2
32
Example 3
33
Example 3
• Step 1: Select employees earning more than 40K
𝜎!"#"$%&'(((( 𝐸𝑚𝑝𝑙𝑜𝑦𝑒𝑒𝑠
• Step 2: Match those employees with their supervisors
• Step 3: Get supervisor details from Employees
• Step 4: Project only supervisor’s name and salary
34
Example 3
35
Example 4
36
Outer Join Example
37
Outer Join Example
38
Homework!
§ Read class note 2 and chapter 2 ………
39
Thank you
J
