DBMS Notes – Advanced Topics

(Relational Algebra → Calculus → SQL

→ Integrity & More)
Prepared: Amity University B.Tech Level
Index
1. 1. Relational Algebra (Theory & Examples)
2. 2. Domain Relational Calculus (DRC)
3. 3. Tuple Relational Calculus (TRC)
4. 4. Fundamental Operations
5. 5. Additional Operations
6. 6. SQL Fundamentals (Examples & Queries)
7. 7. Integrity Constraints
8. 8. Triggers
9. 9. Views
10.10. Relational Database Concepts & Summary
11.11. Practice Questions (20)
12.12. Solutions
1. Relational Algebra
Relational algebra is a procedural query language that works on relations
(tables). It consists of a set of operations that take one or two relations as
input and produce a new relation as output. It forms the theoretical
foundation for SQL and query optimization.

Common operators: Selection (σ), Projection (π), Union (∪), Set Difference
(−), Cartesian Product (×), Rename (ρ), Join (⋈), Intersection (∩), Division
(÷).

Example relation EMP:

EmpID Name Dept Salary

1 Aman CSE 50000
2 Riya ECE 48000
3 Sameer CSE 52000
Selection example: σ_Dept='CSE'(EMP) returns tuples where Dept is CSE.

Projection example: π_Name,Salary(EMP) returns only the Name and Salary

columns. These operators can be composed to express complex queries.

Join example: Combining EMP and DEPT relations using common attribute
DeptID (natural join or equi-join).
2. Domain Relational Calculus (DRC)
DRC is a non-procedural query language that specifies what to retrieve rather
than how. Queries are expressions that use domain variables which take
values from domains of attributes.

Syntax: { <d1, d2, ... , dn> | P(d1, d2, ..., dn) } where P is a predicate.

Example: { n | ∃i ∃c (STUDENT(i,n,c) AND c='CSE') } returns names n of

students in CSE.
3. Tuple Relational Calculus (TRC)
TRC is another non-procedural query language where variables range over
tuples. A TRC query has the form: { t | P(t) } where t is a tuple variable and P
is a predicate.

Example: { t | STUDENT(t) AND t.Age > 20 } returns tuples of STUDENT with

Age > 20.

TRC and DRC are equivalent in expressive power to relational algebra for safe
queries (queries that produce finite results).
4. Fundamental Operations
Selection (σ): selects tuples satisfying a predicate.
Projection (π): selects certain columns.
Union (∪): combines tuples from two relations with same schema.
Set Difference (−): tuples in one relation but not in another.
Cartesian Product (×): pairs tuples from two relations.

Properties: Commutativity: UNION, Intersection. Associativity: UNION,

Intersection. Distributivity and De Morgan's laws apply in set operations.
5. Additional Operations
Intersection (∩): common tuples.
Join (⋈): combines related tuples from two relations.
Division (÷): useful for queries like 'find entities related to all values of
another set'.
Renaming (ρ): change attribute names.

Join types: Natural Join, Equi-Join, Theta Join, Outer Joins (Left, Right, Full).
Division example: If R(A,B) and S(B) then R ÷ S returns A values related to
every B in S.
6. SQL Fundamentals
SQL is a declarative language used to define, query, and manipulate
relational data. Key statements include CREATE, INSERT, SELECT, UPDATE,
DELETE, and DDL/DML constructs.

Example schema and queries:

CREATE TABLE STUDENT(

StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Course VARCHAR(30)
);

INSERT INTO STUDENT VALUES (101, 'Asha', 20, 'CSE');

INSERT INTO STUDENT VALUES (102, 'Vikram', 21, 'ECE');

-- Simple SELECT
SELECT Name, Course FROM STUDENT WHERE Age > 20 ORDER BY Name;

-- JOIN example
CREATE TABLE DEPT(DeptID INT PRIMARY KEY, DeptName VARCHAR(30));
INSERT INTO DEPT VALUES (1, 'CSE');
INSERT INTO DEPT VALUES (2, 'ECE');

SELECT S.Name, D.DeptName FROM STUDENT S JOIN DEPT D ON

S.Course='CSE' /*example mapping*/;
Aggregate functions: COUNT, SUM, AVG, MIN, MAX. GROUP BY and HAVING
for grouped queries. Subqueries: correlated and uncorrelated. Transactions:
BEGIN, COMMIT, ROLLBACK.
7. Integrity Constraints
Integrity constraints enforce correctness of data. Types:
- Domain constraints
- Entity integrity (Primary Key cannot be NULL)
- Referential integrity (Foreign Key must refer to existing primary key)
- User-defined constraints (CHECK)

Example:

CREATE TABLE EMP(

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
DeptID INT,
CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES DEPT(DeptID)
);

Cascading actions: ON DELETE CASCADE, ON UPDATE SET NULL, etc. Ensure

careful design to avoid unintended deletions.
8. Triggers
Triggers are procedural code automatically executed in response to certain
events on a table (INSERT, UPDATE, DELETE). They are used for auditing,
complex constraints, and derived column maintenance.

Example trigger (pseudo-SQL):

CREATE TRIGGER trg_after_insert_emp

AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_LOG(EventTime, EventType, EmpID) VALUES (NOW(),
'INSERT', NEW.EmpID);
END;

Care: avoid long-running triggers; they execute within the transaction and
can affect performance.
9. Views
A view is a virtual table defined by a query. It simplifies complex queries,
enforces security, and provides a level of abstraction.

Example: CREATE VIEW CSE_STUDENTS AS SELECT StudentID, Name FROM

STUDENT WHERE Course='CSE';

Views can be updatable depending on DBMS and view definition. Materialized

views store results physically for performance.
10. Relational Database Concepts & Summary
Relational databases store data in normalized tables to reduce redundancy
and improve integrity. Normal forms (1NF, 2NF, 3NF, BCNF) help design
reliable schemas.

Relational algebra and calculus give formal foundations; SQL implements

much of this functionality in practical systems. Proper use of keys,
constraints, and indexes is essential for performance and correctness.

Indexing, query optimization, and transaction management (ACID properties)

are critical in real-world DBMS.
11. Practice Questions
Q1. Define relational algebra and list its basic operators.

Q2. Write a relational algebra expression to find names of employees in CSE.

Q3. Describe the difference between DRC and TRC with examples.

Q4. Given STUDENT(StudentID, Name, Course, Age), write a TRC query for
students older than 21.

Q5. Show how to express JOIN of EMP and DEPT in relational algebra and SQL.

Q6. Demonstrate a division operation example: 'Find students who took all
courses in a set'.

Q7. Write SQL to create tables STUDENT and ENROLL, and insert sample
rows.

Q8. Explain referential integrity and show an example with FOREIGN KEY.

Q9. Create a trigger to update a LAST_MODIFIED timestamp after row update.

Q10. What is a materialized view and when is it used?

Q11. List differences between INNER JOIN and OUTER JOIN.

Q12. Give an example of a correlated subquery.

Q13. Explain normalization up to 3NF with an example.

Q14. How do indexes improve query performance?

Q15. Write SQL to find average salary per department.

Q16. Explain ACID properties of transactions with examples.

Q17. Show an example of UNION and UNION ALL and explain difference.

Q18. Write a DRC query for student names enrolled in 'DBMS' course.

Q19. Explain how CHECK constraints work with an example.

Q20. Describe query optimization strategies used by RDBMS.

12. Solutions
A1. Relational algebra is a set of operations on relations: σ, π, ∪, −, ×, ρ, ⋈,
∩, ÷.

A2. σ_Dept='CSE'(EMP) then π_Name(σ_Dept='CSE'(EMP)). This selects rows

where Dept='CSE' then projects Name.

A3. DRC uses domain variables, TRC uses tuple variables. Example DRC: { n |
∃i ∃c (STUDENT(i,n,c) AND c='CSE') }. TRC: { t | STUDENT(t) AND
t.Course='CSE' }

A4. TRC: { t | STUDENT(t) AND t.Age > 21 } or using projection { t.Name |

STUDENT(t) AND t.Age > 21 }

A5. Relational algebra join: EMP ⋈_{EMP.DeptID = DEPT.DeptID} DEPT. SQL:

SELECT E.Name, D.DeptName FROM EMP E JOIN DEPT D ON E.DeptID =
D.DeptID;

A6. Division example: R(A,B) ÷ S(B) returns A values related to all B in S. For
students who took all courses C1,C2 in set S, use division.

A7. Example DDL provided earlier in SQL Fundamentals section; insert rows
using INSERT INTO.

A8. Referential integrity: FOREIGN KEY ensures referenced value exists.

Example: CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES
DEPT(DeptID).

A9. Example trigger provided earlier: trg_after_insert_emp inserts into

AUDIT_LOG; for update modify LAST_MODIFIED.

A10. Materialized view stores the result physically and is used for expensive
queries to improve performance at cost of storage and maintenance.

A11. INNER JOIN returns matched rows only; OUTER JOIN returns matched
plus unmatched from one or both sides (LEFT/RIGHT/FULL).

A12. Correlated subquery example: SELECT S.Name FROM STUDENT S

WHERE S.Age > (SELECT AVG(Age) FROM STUDENT WHERE Course =
S.Course);

A13. Normalization: 1NF remove repeating groups; 2NF remove partial

dependency on PK; 3NF remove transitive dependency. Example: splitting
student-course table into STUDENT and ENROLL.
A14. Indexes allow faster lookup using tree structures (B-trees), reducing full
table scans.

A15. SELECT DeptID, AVG(Salary) FROM EMP GROUP BY DeptID;

A16. ACID: Atomicity (all or nothing), Consistency (constraints preserved),

Isolation (transactions don't interfere), Durability (committed changes
persist).

A17. UNION removes duplicates, UNION ALL keeps duplicates. Example:

SELECT Name FROM A UNION SELECT Name FROM B;

A18. DRC: { n | ∃i ∃c (ENROLL(i,n,c) AND c='DBMS') } or appropriate schema

variables.

A19. CHECK constraint: CREATE TABLE EMP(... Salary INT CHECK (Salary >
0));

A20. Query optimization: use indexes, rewrite queries, push-down predicates,

use cost-based optimizer and statistics.