ANNAMACHARAYA INSTITUTE OF TECHNOLOGY AND SCIENCES

PIGLIPUR, BATASINGRAM, HYDERABAD

(AUTONOMOUS)

DATABASE MANAGEMNET SYSTEM

LECTURER NOTES
FOR
B TECH CSE 2ND YEAR- SEM-II(R22)

UNIT-2
Introduction to the Relational Model

DEPARTMENT OF COMPUTER SCIENCE ENGINEERING

(2025-2026)

NAME OF THE FACULTY HOD-CSE

 SYLLABUS

 Introduction to the Relational Model:

 Integrity constraint over relations,
 enforcing integrity constraints,
 querying relational data, logical database design,
 introduction to views,
 destroying/altering tables and views.
 Relational Algebra,
 Tuple relational Calculus,
 Domain relational calculus.
INTRODUCTION TO THE RELATIONAL MODEL
The Relational Model is a way to organize and manage data using tables. It was proposed by Edgar F.
Codd in 1970 and is the foundation of most modern databases (like MySQL, PostgreSQL, Oracle, SQL
Server).

In this model, data is stored in relations, which are simply tables made up of rows and columns.

1. Relation

A relation is the main construct in a relational database. It has:

1. Relation Schema – Defines the structure of the relation.

2. Relation Instance – Contains the actual data (tuples).

1.1 Relation Schema

A relation schema defines:

 Name of the relation

 Names of the fields (attributes)
 Domain (type) of each field

1. Relation (Table)

A relation is a table with:

 Rows (also called tuples)

 Columns (also called attributes)

Example:
STUDENT

StudentID Name Age

1 Alex 16
2 Jordan 17

2. Attributes (Columns)

 Each column represents a property of the data.

 Every attribute has a data type (e.g., integer, text).

Example:
  StudentID → Number
 Name → Text
 Age → Number

3. Tuples (Rows)

 Each row represents one record.

 A tuple contains values for all attributes in the table.

4. Domain

 A domain is the set of allowed values for an attribute.

 Example:
o Age → whole numbers between 0 and 120

Example:

Students(sid CHAR(10), name CHAR(10), login CHAR(10), age INTEGER, gpa REAL)

 sid → CHAR(10)
 name → CHAR(10)
 login → CHAR(10)
 age → INTEGER
 gpa → REAL

This is the structure of the relation. No actual data yet.

1.2 Relation Instance

A relation instance is a set of tuples (records) that match the schema.

Example Instance of Students Relation:

sid name login age gpa

S101 Ram ram123 18 8.5
S102 Priya priya21 19 9.0
S103 Anil anil77 18 8.0
S104 Meera meera1 20 9.2
S105 NULL rina55 19 8.7
S106 Rina NULL 21 9.1

2. Degree and Cardinality

  Degree (Arity): Number of attributes (columns) in the relation.
Example: Students has 5 attributes → degree = 5
 Cardinality: Number of tuples (rows) in the relation instance.
Example: Instance above has 6 tuples → cardinality = 6

3. Relational Database

A relational database is a collection of relations (tables), where each relation has a unique
name. These tables are used to store related data in an organized manner.

Schema

 The schema is the overall structure of the database.

 It defines:
o Table names
o Attributes (columns)
o Data types
o Relationships between tables
 The schema does not change frequently.

Example:
STUDENT(StudentID, Name, Age)
COURSE(CourseID, CourseName)

Instance

 An instance refers to the actual data stored in the database at a specific moment in time.
 It changes whenever data is inserted, updated, or deleted.

Example (Instance of STUDENT table):

StudentID Name Age

1 Alex 16
2 Sam 17

Advantages of the Relational Model

1. Simple and Easy to Use – Tables are easy to understand.

2. Manageability – Each relation is independent, easier to manipulate and maintain.
3. Query Capability – Relational algebra allows easy data access via SQL.
4. Data Integrity – Constraints ensure correct and consistent data.

Disadvantages of the Relational Model

1. Performance decreases as the number of relations grows.

 2. More tables → higher memory usage.
3. Query response time may slow down in large databases.
4. Cost of implementation may increase due to complexity.

Important Terminologies

Term Meaning & Example

Attribute Property of an entity. Example: ROLL_NO, NAME, ADDRESS
Structure of relation. Example: STUDENT(ROLL_NO, NAME, ADDRESS, PHONE,
Relation Schema
AGE)
Tuple A single row in a relation. Example: 1, RAM, DELHI, 9455123451, 18
Relation Instance Set of tuples at a particular time. Can change with insert, update, delete
Degree Number of attributes in relation. Example: 5
Cardinality Number of tuples in relation. Example: 4
Column Set of values for an attribute. Example: Column ROLL_NO → 1, 2, 3, 4
NULL Values Unknown or unavailable value. Example: PHONE for ROLL_NO 4 is NULL

KEYS IN RELATIONAL DATABASES:

A key is a set of one or more attributes that uniquely identifies a tuple (row) in a relation (table).
Keys help maintain uniqueness and define relationships between tables.

These are of the following types.

 PrimaryKey
  CandidateKey

 SuperKey

 ForeignKey

 Alternate Key

 Composite Key

1. Primary Key (PK)

The primary key is a column or a set of columns in a table that uniquely identifies each record
in that table. A table can have only one primary key, and the primary key must satisfy the
following rules:

 The value of the primary key can never be NULL.

 The value of the primary key must always be unique; no duplicates are allowed.
 The value of the primary key cannot be changed once it is assigned (i.e., no updates are
allowed).
 A value for the primary key must be provided when inserting a new record.
 A table is allowed to have only one primary key.

Example: STUDENT Table

STUD_ID NAME AGE EMAIL

S101 Ram 18 ram@[Link]

S102 Priya 19 priya@[Link]

S103 Anil 18 anil@[Link]

 Here, STUD_ID is the primary key.

 Unique, cannot be NULL, and identifies each student uniquely.

2. Superkey

A super key is a set of one or more attributes in a table that can uniquely identify each row
(tuple) in that table.
Key points about super keys:

 A super key can consist of a single attribute or a combination of multiple attributes.

 There is no restriction on the number of attributes in a super key.
 Every super key uniquely identifies a row, but it may include extra attributes that are
not necessary for uniqueness.

Examples of super keys in a Student table:

 (StudID, RollNo, FirstName, LastName, Email)

 (RollNo, FirstName, LastName, Email)
 (StudID, FirstName, LastName, Email)
 (StudID, FirstName, LastName)
 (StudID, FirstName)
 (LastName, Email)
 (StudID, Email)
 (RollNo, FirstName)
 (StudID)

All of the above sets can uniquely identify each student, so they are valid super keys.

3. Candidate Key

A candidate key is the minimal set of attributes that can uniquely identify a tuple (row) in a
table.

Key points about candidate keys:

 A candidate key is a minimal super key, meaning it has no unnecessary attributes.

 It can uniquely identify each record in a table.
 It must contain unique values.
 It can contain NULL values, unlike a primary key.
 Every table must have at least one candidate key.
 A table can have multiple candidate keys, but only one candidate key is chosen as the
primary key.
 o The primary key cannot have NULL values, so a candidate key that allows
NULL cannot become the primary key.

Example:
In the STUDENT table, STUD_NO can be a candidate key because it uniquely identifies each
student.

4. Alternate Key

An alternate key is a candidate key that is not chosen as the primary key.

Key points about alternate keys:

 All candidate keys other than the primary key are called alternate keys.
 It is sometimes referred to as a secondary key.
 An alternate key can consist of one or more attributes.
 The values in an alternate key must be unique, just like a candidate key (they are not
allowed to repeat).

Example:
In a STUDENT table:

 If STUD_NO is the primary key, then SNAME (student name) or ADDRESS can be alternate
keys if they can also uniquely identify a student.

5. Composite Key

A composite key is a combination of two or more columns that together uniquely identify
each row in a table.

Key points about composite keys:

 A primary key consisting of multiple attributes is called a composite key.

 It can act as a primary key if no single attribute is sufficient to uniquely identify rows.
 Two or more attributes are combined to form the composite key.
  Each combination of values across these attributes must be unique for each row.

Example:
In a table Enrollments(StudentID, CourseID, Grade):

 Neither StudentID nor CourseID alone can uniquely identify a row.

 But the combination {StudentID, CourseID} can uniquely identify each enrollment record →
This is a composite key.

6. Foreign Key (FK)

A foreign key is a column or a set of columns in a table that establishes a link to the primary
key of another table. It is used to maintain referential integrity between the two tables.

Key points about foreign keys:

 Every value in a foreign key column must match a value in the referenced table’s
primary key, or it can be NULL (if allowed).
 It creates a relationship between two tables, allowing data to be connected across
tables.
 It acts as a cross-reference between tables.
 The main purpose is to maintain data integrity and allow navigation between related
records.

Example:

Summary Table of Keys

Key Type Description Example

Primary Key Uniquely identifies each row, cannot be NULL STUD_ID in STUDENT

Superkey Any set of attributes that uniquely identifies a row (STUD_ID, NAME), (EMAIL)

Candidate
Minimal superkey (no extra attributes) STUD_ID, EMAIL
Key

Alternate
Candidate key that is not primary key EMAIL
Key

Composite
Two or more attributes combined to form a key (STUD_ID, COURSE_ID)
Key

Column referencing primary key of another table to STUD_ID in

Foreign Key
maintain integrity COURSE_REGISTRATION

Integrity Constraints over Relations

 Integrity constraints are a set of rules used to maintain the quality and correctness of
information in a database.
 Each relation (table) has certain conditions that must hold for it to be considered valid. In
other words, integrity constraints check whether the table’s data is valid.
 Integrity constraints ensure that operations like data insertion, updating, and deletion are
performed in a way that does not compromise data integrity.
 An integrity constraint (IC) is a condition specified on a database schema that restricts
the type of data that can be stored in a database instance. If a database instance satisfies
all the integrity constraints of the schema, it is considered a legal instance.
 A DBMS (Database Management System) enforces integrity constraints by allowing
only legal instances to be stored in the database.

1. Domain Constraints
  Domain constraints are a set of rules that define what kind of values an attribute can store
in a table (entity).
 They specify the valid set of values that an attribute can take.
 The data type of a domain can include: string, character, integer, float, date, time,
currency, etc.
 Every value stored in an attribute must belong to its defined domain.

Example:

2. Entity Integrity Constraints

 The entity integrity constraint states that the primary key value cannot be null.
 This is because the primary key is used to uniquely identify each row in a table. If the
primary key is null, the row cannot be identified.
 Other fields (columns) in the table can contain null values, as long as they are not part
of the primary key.

[Link] Integrity Constraints

A referential integrity constraint is specified between two tables.
In referential integrity constraints, if a foreign key in Table 1 refers to the primary key of Table
2, then every value of the foreign key in Table 1 must either be NULL or must exist in Table 2.

Example:

[Link] Constraints

A key is a set of attributes that can uniquely identify each tuple in a given relation.

An entity set can have multiple keys, out of which one key is chosen as the primary key.
A primary key must contain unique values and cannot contain NULL values in a relational table.

Example:

Enforcing Integrity Constraints

Enforcing integrity constraints means applying rules to a database to ensure the accuracy and
consistency of the data. There are several types of data integrity constraints commonly found in
relational databases, including the following:

1. Required Data (NOT NULL Constraint) – Some columns in a database must contain
valid data in every row; they cannot be left empty (NULL). For example, in a database of
orders, every order must be associated with a customer. The DBMS can enforce this rule
by preventing NULL values in this column.
2. Validity Checking (Domain Constraint) – Every column in a database has a domain,
which is the set of allowed data values for that column. The DBMS can enforce this by
preventing any value that does not belong to the specified domain.
3. Entity Integrity (Primary Key Constraint) – The primary key of a table must contain a
unique value for each row. Duplicate values are not allowed because they prevent the
database from uniquely identifying each entity. The DBMS can enforce this rule to
ensure uniqueness.
4. Referential Integrity (Foreign Key Constraint) – A foreign key in a child table links
each row to a corresponding row in the parent table by matching the primary key. The
DBMS can enforce this constraint to ensure that relationships between tables remain
consistent.

Querying Relational Data in DBMS

Querying Relational Data in a DBMS refers to retrieving and manipulating data stored in relational
tables using a query language—most commonly SQL (Structured Query Language).

For example, we might want to:

 Find all students whose age is less than 18.

 Find all students enrolled in a particular course.

The SELECT statement is used to fetch data from a database table. The result is returned as a result set,
which is a table containing the queried data.

Basic SELECT Syntax in MySQL

To select specific columns:

SELECT column1, column2, ...

FROM table_name;

To select all columns from a table:

SELECT *
FROM table_name;

The symbol * means "select all fields" of the selected rows.

Example 1: Selecting rows based on a condition

To retrieve rows corresponding to students younger than 18:

SELECT *
FROM Students
WHERE age < 18;

Here, only tuples (rows) where the age field is less than 18 are selected.

Example 2: Selecting specific columns with a condition

To get only the student_id and first_name of students younger than 18:

SELECT student_id, first_name

FROM Students
WHERE age < 18;

Example 3: Combining information from multiple tables

To combine information from multiple tables, you can use a join.

Syntax:

SELECT table1.column1, table2.column2

FROM table1, table2
WHERE table1.common_column = table2.common_column;

Example:

SELECT Students.student_id, Enrolled.course_id

FROM Students, Enrolled
WHERE Students.student_id = Enrolled.student_id;

This query retrieves the student IDs along with the courses they are enrolled in.

Logical Database Design

Logical database design is the process of transforming a conceptual schema (usually an ER

diagram) into a schema that fits a specific data model, like a relational or object-oriented
model, which can be implemented in a DBMS.

This step comes after conceptual design and before physical design.

Goals of Logical Database Design

1. Representation Goal

 Ensure the logical schema can capture all valid states of the conceptual schema.
 Example: If your ER diagram shows that a student can enroll in multiple courses, your
logical schema must allow multiple course entries per student.

2. Data Management Goals

 Make querying easier and more efficient.

 Minimize storage and constraint maintenance costs.
 Ensure data integrity.

SQL Commands

SQL commands are instructions used to communicate with a database. They allow you to
perform tasks like:

 Creating tables
 Adding data to tables
 Modifying or deleting tables
 Setting permissions for users
 Querying data

There are five types of SQL commands:

1. DDL – Data Definition Language

2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Transaction Control Language
5. DQL – Data Query Language
1. Data Definition Language (DDL)

DDL commands are used to change the structure of database objects (like tables). They are
auto-committed, meaning changes are permanent.

Command Purpose

CREATE Create a new table

ALTER Modify the structure of an existing table

DROP Delete a table along with its data

TRUNCATE Remove all rows from a table (frees space)

Examples:

a. CREATE – Create a new table

CREATE TABLE EMPLOYEE (

Name VARCHAR2(20),
Email VARCHAR2(100),
DOB DATE
);

Example Output Table: (Empty table with columns)

Name Email DOB

b. DROP – Delete a table

DROP TABLE EMPLOYEE;

Example Output:

 Deletes the EMPLOYEE table entirely.

 No table exists afterward.

c. ALTER – Modify table structure

Add a new column:

ALTER TABLE STU_DETAILS ADD (ADDRESS VARCHAR2(20));

Modify existing column:

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

Example Output Table: (After adding column)

Name Age ADDRESS

John 20

Alice 22

d. TRUNCATE – Delete all rows

TRUNCATE TABLE EMPLOYEE;

Example Output Table: (Structure remains, no rows)

Name Email DOB

2. Data Manipulation Language (DML)

DML commands are used to modify data in tables. They are not auto-committed, meaning changes
can be rolled back.

Command Purpose

INSERT Add new rows to a table

UPDATE Modify existing rows

DELETE Remove rows from a table

Examples:

a. INSERT – Add data

INSERT INTO javatpoint (Author, Subject) VALUES ('Sonoo', 'DBMS');

Example Output Table:

Author Subject

Sonoo DBMS

b. UPDATE – Modify data

UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = 3;

Example Output Table (before update):

Student_Id User_Name

3 Rahul

After update:

Student_Id User_Name

3 Sonoo

c. DELETE – Remove rows

DELETE FROM javatpoint WHERE Author = 'Sonoo';

Example Output Table:

Author Subject

3. Data Control Language (DCL)

DCL commands control user permissions in a database.

Command Purpose

GRANT Give access privileges

REVOKE Take back permissions

Examples:
GRANT

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

REVOKE

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

Example Output Table (Permissions):

User Table Permissions

SOME_USER MY_TABLE SELECT, UPDATE

ANOTHER_USER MY_TABLE SELECT, UPDATE

USER1 MY_TABLE None

USER2 MY_TABLE None

4. Transaction Control Language (TCL)

TCL commands manage transactions (group of DML operations).

Command Purpose

COMMIT Save all changes permanently

ROLLBACK Undo unsaved changes

SAVEPOINT Set a point to roll back to

Examples:

COMMIT

DELETE FROM CUSTOMERS WHERE AGE = 25;

COMMIT;

Example Output Table (after commit):

Customer_ID Name Age

1 Alice 30

2 Bob 40

... ... ...

(Customers aged 25 are deleted permanently)

ROLLBACK

DELETE FROM CUSTOMERS WHERE AGE = 25;

ROLLBACK;

Example Output Table:

Customer_ID Name Age

1 Alice 30

2 Bob 40

3 Sam 25

(Changes undone; customer aged 25 remains)

SAVEPOINT

SAVEPOINT sp1;

Example Output:

 Marks a point in the transaction.

 Can roll back partially without affecting earlier operations.

5. Data Query Language (DQL)

DQL commands are used to fetch data from tables.

SELECT Example:

SELECT emp_name FROM employee WHERE age > 20;

Example Output Table:

emp_name

Alice

Bob

INTRODUCTION TO VIEWS

 A view is a table whose rows are not explicitly stored in the database but are computed.
 Views in SQL are considered virtual tables. A view also contains rows and columns, just
like a regular table.
 To create a view, we can select fields from one or more tables present in the database.
 A view can display either specific rows based on certain conditions or all the rows of a
table.

Advantages of Views over Database Tables

 Using views, we can join multiple tables into a single virtual table.
 Views hide data complexity from the user.
 Views take less storage space than tables because the database stores only the view
definition, not the data itself.
 Views can represent a subset of data that is contained in the tables of the database.

Disadvantages of Views over Database Tables

 You cannot execute INSERT, UPDATE, or DELETE statements on a view if it is read-only.

 You cannot create a view on temporary tables.
 You cannot perform INSERT, UPDATE, or DELETE if the view contains GROUP BY,
DISTINCT, or aggregate functions.
 You cannot associate rules or defaults with views.

Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram
 Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18

Student Tables

Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18

1. Creating a View

A view can be created using the CREATE VIEW statement. It can be based on a single table or multiple
tables.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example: Create a view showing the names and addresses of students with STU_ID < 3:

CREATE VIEW my_view AS

SELECT NAME, ADDRESS
FROM Student_Detail
WHERE STU_ID < 3;

To display data from the view:

SELECT * FROM my_view;

Result:

NAME ADDRESS
Stephan Delhi
Kathrin Noida

2. Creating a View from Multiple Tables

A view can combine data from multiple tables by including them in the SELECT statement.

Example: Create a view MarksView that shows NAME, ADDRESS, and MARKS from Student_Detail
and Student_Marks:

CREATE VIEW MarksView AS

SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Marks
WHERE Student_Detail.NAME = Student_Marks.NAME;

To display the view:

SELECT * FROM MarksView;

Result:

NAME ADDRESS MARKS

Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90
3. Deleting a View

A view can be deleted using the DROP VIEW statement.

Syntax:

DROP VIEW view_name;

Example: Delete the view MarksView:

DROP VIEW MarksView;

4. Updating a View

A view can be updated using the CREATE OR REPLACE VIEW statement.

Syntax (MySQL):

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Add a column Mobile to a view Students_CSE:

CREATE OR REPLACE VIEW Students_CSE AS

SELECT Roll_no, Name, Mobile
FROM Student_Detail;

5. Inserting into a View

You can insert data into a view if it is updatable:

Syntax (MySQL):

INSERT INTO view_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example:

INSERT INTO Students_CSE (Roll_no, Name, Mobile)

VALUES (5, 'Ravi', '9876543210');

6. Deleting Rows from a View

You can delete rows from a view using the DELETE statement:

Syntax (MySQL):

DELETE FROM view_name

WHERE condition;

Example: Delete the student with Name = 'Ram':

DELETE FROM Students_CSE

WHERE Name = 'Ram';

Relational Algebra

Relational algebra is a procedural query language that takes relations (tables) as input and
produces relations as output. It uses operators to perform queries.

 An operator can be unary (operates on one relation) or binary (operates on two

relations).
 Relational algebra can be applied recursively, and intermediate results are also
considered relations.

The fundamental operations of relational algebra are:

1. Selection (σ)
2. Projection (π)
3. Union (U)
4. Set Difference (-)
5. Set Intersection (∩)
6. Cartesian Product (×)
7. Rename (ρ)

1. Selection (σ)

The selection operation is used in relational algebra to pick only the rows (tuples) from a
table (relation) that satisfy a condition.

 Symbol: σ
 Syntax: σ_condition(Relation)
 Meaning: "Select all rows from the relation where the condition is true."

Notation:

σ condition (Relation)
Example 1:

Relation R:

A B C

1 2 4

2 2 3

3 2 3

4 3 4

Query:

σ(C > 3)(R)

 Condition: C > 3

 This means: "Only choose rows where the value in column C is greater than 3."

Step-by-Step Process

1. Look at the first row: A=1, B=2, C=4

o Is C > 3? ✅ Yes, 4 > 3
o Keep this row.
2. Second row: A=2, B=2, C=3
o Is C > 3? ❌ No, 3 is not greater than 3
o Discard this row.
3. Third row: A=3, B=2, C=3
o Is C > 3? ❌ No, discard.
4. Fourth row: A=4, B=3, C=4
o Is C > 3? ✅ Yes, keep this row.

Output:

A B C
1 2 4
4 3 4
Example 2 (LOAN table):

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Query:

σ BRANCH_NAME = "Perryride" (LOAN)

3. Step-by-Step Filtering

Go row by row:

1. Downtown, L-17, 1000 → Branch is Downtown ❌ Not Perryride → Discard

2. Redwood, L-23, 2000 → Branch is Redwood ❌ Discard
3. Perryride, L-15, 1500 → Branch is Perryride ✅ Keep
4. Downtown, L-14, 1500 → ❌ Discard
 5. Mianus, L-13, 500 → ❌ Discard
6. Roundhill, L-11, 900 → ❌ Discard
7. Perryride, L-16, 1300 → ✅ Keep

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Projection (π)

 Projection selects specific attributes (columns) from a relation, eliminating the rest.
 Denoted by π.

Notation:

π A1, A2, ... (Relation)

Example (CUSTOMER table):

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Query:

π NAME, CITY (CUSTOMER)

Output:

NAME CITY

Jones Harrison

Smith Rye
 NAME CITY

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union (U)

 The union operation combines all tuples from R or S or both, eliminating duplicates.
 Denoted by U.

Condition: R and S must have the same number of attributes.

Example (DEPOSITOR & BORROW):

• DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284

BORROWRELATION
 CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Query:

π CUSTOMER_NAME (BORROW) U π CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes
4. Set Intersection (∩)

 Selects tuples common to both R and S.

 Denoted by ∩.

Query:

π CUSTOMER_NAME (BORROW) ∩ π CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference (-)

 Selects tuples in R but not in S.

 Denoted by -.

Query:

π CUSTOMER_NAME (BORROW) - π CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Jackson

Hayes

Williams

Curry

6. Cartesian Product (×)

 Combines each row in one table with each row in another table.
 Denoted by ×.

Example (EMPLOYEE × DEPARTMENT):

 Example:E
MPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A
2 Harry C
3 John B
DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal
Input:

Query:

EMPLOYEE × DEPARTMENT

Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal
7. Rename (ρ)

 Used to rename a relation or its attributes.

 Denoted by ρ.

Example:

ρ(STUDENT1, STUDENT)

Join Operations

A join combines tuples from different relations based on a join condition.

 Denoted by ⋈.

EMPLOYEE

EMP_CODE EMP_NAME

101 Stephan
102 Jack
103 Harry

SALARY

EMP_CODE SALARY

101 50000
102 30000
103 25000

Query:

EMPLOYEE ⋈ SALARY
Output:

EMP_CODE EMP_NAME SALARY

101 Stephan 50000

102 Jack 30000

103 Harry 25000

Types of Join Operations

Join Operations in Relational Algebra

denoted by ⋈.
A join operation combines tuples from different relations based on a join condition. It is

a. Natural Join (⋈)

 Combines tuples from relations R and S that have equal values for attributes with the
same name.
 No duplicate columns for the common attributes appear in the output.
  Denoted by: R ⋈ S

Example:

EMPLOYEE

EMP_CODE EMP_NAME
101 Stephan
102 Jack
103 Harry
SALARY

EMP_CODE SALARY
101 50000
102 30000
103 25000

π EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Query:

Output:

EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000

b. Equi Join

 Combines tuples based on equality of a specified attribute (can be differently named in

each table).
Duplicate columns may appear in output.
Denoted by: R ⋈_{[Link] = [Link]} S



Example:

CUSTOMER

CLASS_ID NAME
1 John
2 Harry
3 Jackson
PRODUCT
PRODUCT_ID CITY
1 Delhi
2 Mumbai
3 Noida

CUSTOMER ⋈_{CUSTOMER.CLASS_ID = PRODUCT.PRODUCT_ID} PRODUCT

Query:

Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi
2 Harry 2 Mumbai
3 Jackson 3 Noida

c. Conditional Join (Theta Join)

General form of join with any condition, not just equality.

Denoted by: R ⋈_θ S where θ is the condition.



Example:
STUDENTS

STUD_ID NAME AGE

1 John 18
2 Harry 20
COURSE

COURSE_ID MIN_AGE
101 18
102 19

STUDENTS ⋈_{[Link] ≥ COURSE.MIN_AGE} COURSE

Query:

Output:

STUD_ID NAME AGE COURSE_ID MIN_AGE

1 John 18 101 18
2 Harry 20 101 18
2 Harry 20 102 19
✅ Note: Any comparison operator can be used (=, <, >, >=, <=, !=).
2. Outer Join

An outer join is a type of join operation that returns all matching records from both tables and
also includes unmatched records from one or both tables.
For unmatched records, the missing values are filled with NULL.

Outer joins are of three types:

 Left Outer Join

 Right Outer Join
 Full Outer Join

Example Tables
EMPLOYEE

EMP_NAME STREET CITY

Ram Civilline Mumbai

Shyam Parkstreet Kolkata

Ravi [Link] Delhi

Hari Nehrunagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Common attribute: EMP_NAME

a) Left Outer Join ( ⟕ )

Definition
A Left Outer Join returns all records from the left table (EMPLOYEE) and the matching
records from the right table (FACT_WORKERS).
If there is no match, the right table values are filled with NULL.

Relational Algebra

EMPLOYEE ⟕ FACT_WORKERS

Output

EMP_NAME STREET CITY BRANCH SALARY

Ram Civilline Mumbai Infosys 10000

Shyam Parkstreet Kolkata Wipro 20000

Hari Nehrunagar Hyderabad TCS 50000

Ravi [Link] Delhi NULL NULL

Explanation

 Ravi has no matching record in FACT_WORKERS

 So BRANCH and SALARY are NULL

b) Right Outer Join ( ⟖ )

Definition

A Right Outer Join returns all records from the right table (FACT_WORKERS) and the
matching records from the left table (EMPLOYEE).
If there is no match, the left table values are filled with NULL.

Relational Algebra

EMPLOYEE ⟖ FACT_WORKERS

Output

EMP_NAME STREET CITY BRANCH SALARY

Ram Civilline Mumbai Infosys 10000

Shyam Parkstreet Kolkata Wipro 20000

Hari Nehrunagar Hyderabad TCS 50000

EMP_NAME STREET CITY BRANCH SALARY

Kuber NULL NULL HCL 30000

Explanation

 Kuber does not exist in EMPLOYEE

 So STREET and CITY are NULL

c) Full Outer Join ( ⟗ )

Definition

A Full Outer Join returns all records from both tables.

If there is no matching record, the missing attributes are filled with NULL.

Relational Algebra

EMPLOYEE ⟗ FACT_WORKERS

Output

EMP_NAME STREET CITY BRANCH SALARY

Ram Civilline Mumbai Infosys 10000

Shyam Parkstreet Kolkata Wipro 20000

Hari Nehrunagar Hyderabad TCS 50000

Ravi [Link] Delhi NULL NULL

Kuber NULL NULL HCL 30000

Explanation

 Ravi exists only in EMPLOYEE

 Kuber exists only in FACT_WORKERS
 Both are included with NULL values
Relational Calculus

Relational calculus is an alternative way of formulating queries in a database.

 It is a non-procedural query language, meaning the user specifies what to retrieve

without describing how to retrieve it.
 It tells what to do, but not how to do it.
 Many commercial query languages, like SQL, QBE, and QUEL, are based on concepts
from relational calculus.

Types of Relational Calculus

1. Tuple Relational Calculus (TRC)

 TRC is a non-procedural, declarative language that specifies what data is required

rather than how to retrieve it.
 It selects tuples (rows) from a relation that satisfy a predicate (condition).
 Filtering variables represent tuples of a relation.

Notation:

{T | P(T)} or {T | Condition(T)}

 T = resulting tuple
 P(T) = condition to fetch T

Example: EMP table

 id Name salary

101 Jhon 20000

102 Hyes 25000

103 stella 32000

Query (TRC):

{T | Emp(T) AND [Link] > 20000}

Output:

id Name salary

102 Hyes 25000

103 Stella 32000

2. Domain Relational Calculus (DRC)

 DRC is also a non-procedural language but works on attribute domains instead of entire
tuples.
 Filtering variables represent values from an attribute’s domain.
Uses logical operators:
o ∧ (AND), ∨ (OR), ¬ (NOT)


 Query-by-Example (QBE) is related to DRC.

Notation:

{a1, a2, ..., an | P(a1, a2, ..., an)}

 a1, a2, ... = attributes

 P = formula built using these attributes

Example: EMP table

 id Name salary

101 Jhon 20000

102 Hyes 25000

103 stella 32000

Query (DRC):

{<id, name> | <id, name, salary> ∈ Emp ∧ salary > 25000}

Output:

id Name

103 Stella

Comparison: Tuple Relational Calculus (TRC) vs Domain Relational Calculus (DRC)

Basis of Comparison Tuple Relational Calculus (TRC) Domain Relational Calculus (DRC)

Selects tuples from a relation using Selects attribute values from a relation
Definition
tuple variables. using domain variables.

Representation of Variables represent tuples from a Variables represent values from attribute
variables relation. domains.

Tuple = single element (row) of a

Tuple / Domain Domain = column data type and constraints.
relation.

Reflects traditional pre-relational More similar to logic as a modeling

Similarity
file structures. language.

Syntax / Notation `{T P(T)}or{T

Example `{T Emp(T) AND [Link] > 20000}`

Focus Selecting tuples from a relation. Selecting values from a relation.

Expressiveness Less expressive More expressive

Ease of use Easier for simple queries More difficult for simple queries
THE END