Database Management System (AD-402) Unit 2 Notes

How unit 2 is examined

The relational model (domains, tuples, keys, schemas, constraints), SQL DDL/DML, joins, and relational algebra and calculus; algebra (basic operations, Cartesian product, properties) carries the most marks, then calculus, keys, schemas, division and GROUP BY.

Domains

Not asked since 2022

Definition. A domain is the set of atomic values that an attribute is allowed to take.

Key points.

Every attribute is tied to one domain, such as integer, string of length 20, or date.
Values in a domain are atomic, meaning indivisible as far as the model is concerned.
The domain constraint rejects any value outside the domain.

A domain is the set of permitted atomic values for an attribute, written dom(A).

Tuples

Definition. A tuple is one row of a relation, an ordered list of values, one per attribute.

Key points.

A tuple with n attribute values is an n-tuple, and the number of tuples is the cardinality of the relation.
No two tuples of a relation are identical, because a relation is a set.
A tuple is also called a row or record.

A tuple is a single row of a relation holding one value from each attribute domain.

Attributes

Definition. An attribute is a named column of a relation that plays a role over a domain.

Key points.

The number of attributes is the degree (arity) of the relation.
Attribute names are unique within a relation.
Each attribute has one domain and holds atomic values.

An attribute is a named column of a relation, defined over a domain; the count of attributes is the degree.

Relations

Definition. A relation is a table, a finite set of tuples over a relation schema R(A1, ..., An).

Key points.

Formally a relation is a subset of the Cartesian product $D_1 \times D_2 \times \dots \times D_n$ of its domains.
Degree is the number of columns and cardinality is the number of rows.
Table, row and column are the informal names for relation, tuple and attribute.

A relation is a finite set of n-tuples, a subset of $D_1 \times \dots \times D_n$.

Characteristics of relations

Definition. These are the properties that make a table a valid relation.

Key points.

Every attribute value is atomic, so there are no repeating groups or multivalued cells.
Tuples are unordered and there are no duplicate tuples.
The order of attributes does not matter, and each column has a unique name.
All values in a column come from the same domain.

A relation has atomic values, no duplicate tuples, and no significance in the order of rows or columns.

Keys

Definition. A key is an attribute or set of attributes that identifies tuples. A superkey is any set that is unique; a candidate key is a minimal superkey; the primary key is the candidate key chosen to identify rows; a foreign key is an attribute that references the primary key of another (or the same) table.

Key points.

Primary key values are unique and NOT NULL, and a table has only one primary key.
A foreign key value must either match an existing primary key value in the referenced table or be NULL.
Both are declared in SQL as shown below.

CREATE TABLE Dept (did INT PRIMARY KEY, dname VARCHAR(20));
CREATE TABLE Emp (eid INT PRIMARY KEY, name VARCHAR(20), did INT,
  FOREIGN KEY (did) REFERENCES Dept(did));

A primary key uniquely identifies each row and cannot be NULL; a foreign key references the primary key of another table.

Asked: [7 marks] (Jun 2025) Define primary key and foreign key constraint and how these constraints are expressed in SQL?

Key attributes of relation

Definition. Key (prime) attributes are the attributes that belong to some candidate key.

Key points.

Attributes not part of any candidate key are non-key (non-prime) attributes.
Prime attributes are never NULL, since candidate keys identify rows.
The distinction is used later to define 2NF and 3NF.

A prime attribute is one that is a member of at least one candidate key.

Relational database

Definition. A relational database is a collection of relations (tables) with distinct names, managed by an RDBMS.

Key points.

Data is stored in tables, and relationships are shown by matching key values, not by pointers.
It is described by a relational schema and controlled by integrity constraints.
It is queried with SQL, based on relational algebra and calculus.

A relational database is a set of interrelated tables where links are made through primary and foreign key values.

Schemas

Definition. A relation schema R(A1, ..., An) gives the name of a relation and its attributes with their domains; a relational database schema is the set of all relation schemas plus the integrity constraints.

Key points.

Components are tables (relations), attributes with domains, tuples (the data), and keys.
Relationships between tables are expressed by foreign keys referencing primary keys.
Integrity constraints (domain, key, entity, referential) are part of the schema.

Example. STUDENT(sid, name, did) and DEPT(did, dname), where STUDENT.did is a foreign key to DEPT.did.

A database schema is the logical design of the database: relation names, attributes, domains, keys and constraints.

Asked: [7 marks] (Jun 2024) Define a relational database schema. Discuss the components of a schema, including tables, attributes, and relationships.

Integrity constraints

Definition. Integrity constraints are rules that keep the data in a database correct and consistent.

Key points.

Domain constraint: a value must belong to its attribute domain.
Key constraint: no two tuples have the same key value.
Entity integrity: no primary key attribute may be NULL.
Referential integrity links tables through foreign keys.

Integrity constraints are conditions every database state must satisfy: domain, key, entity and referential.

Referential integrity

Definition. Referential integrity requires that every foreign key value either be NULL or match an existing primary key value in the referenced relation.

Key points.

It prevents dangling references such as an employee in a department that does not exist.
Inserting a child row with a missing parent, or deleting a referenced parent row, violates it.
SQL handles deletes with ON DELETE CASCADE, SET NULL or RESTRICT.

Referential integrity means a foreign key must match an existing primary key of the referenced table or be NULL.

Intension and Extension

Definition. Intension is the schema, the fixed structure of a relation; extension is the instance, the set of tuples in it at a given time.

Basis	Intension	Extension
Meaning	Schema (design)	Instance (data)
Contains	Name, attributes, domains, constraints	Tuples present now
Changes	Rarely	Frequently on insert, update, delete
Example	STUDENT(sid, name, cgpa)	(1, Ravi, 8.2), (2, Anu, 9.0)

Intension is the relation schema and extension is the set of tuples currently stored in it.

Asked: [7 marks] (Jun 2024) Explain the difference between intension and extension using examples.

Relational Query languages: SQL-DDL

Definition. SQL is the standard relational query language; its commands fall into five categories.

Key points.

DDL defines structure: CREATE, ALTER, DROP, TRUNCATE.
DML changes data: INSERT, UPDATE, DELETE; DQL queries it: SELECT.
DCL controls access: GRANT, REVOKE; TCL controls transactions: COMMIT, ROLLBACK, SAVEPOINT.
Example: CREATE TABLE Emp(eid INT PRIMARY KEY, name VARCHAR(20)); creates a table; SELECT * FROM Emp; lists its rows.

SQL commands are grouped as DDL, DML, DQL, DCL and TCL according to what they define, change, read, protect or commit.

Asked: [7 marks] (Jun 2026) Explain the concept of SQL command with suitable example.

DML

Definition. DML commands INSERT, UPDATE, DELETE and SELECT manipulate the data in tables.

Key points.

GROUP BY groups rows having equal values in given columns so aggregates (COUNT, SUM, AVG, MAX, MIN) are computed per group.
HAVING filters groups after grouping, whereas WHERE filters individual rows before grouping.
Order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

SELECT did, COUNT(*), SUM(salary) FROM Emp
WHERE salary > 10000 GROUP BY did HAVING COUNT(*) > 2;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Asked: [7 marks] (Jun 2023) Discuss the usage of 'group by' and 'having' clauses in SQL.

Integrity constraints: CHECK, NOT NULL, UNIQUE

Definition. SQL enforces column rules declared in CREATE TABLE.

Key points.

NOT NULL forbids missing values; UNIQUE forbids duplicates but allows NULL.
CHECK enforces a condition, for example CHECK (age >= 18).
PRIMARY KEY is NOT NULL plus UNIQUE, and DEFAULT supplies a value.

NOT NULL, UNIQUE and CHECK restrict what values a column may hold.

Complex queries

Definition. A complex query nests one SELECT (a subquery) inside another.

Key points.

The inner query runs first and its result feeds the outer query through IN, EXISTS, ANY or ALL.
A correlated subquery uses a value from the outer row and is re-evaluated per row.
Example: SELECT name FROM Emp WHERE salary > (SELECT AVG(salary) FROM Emp);

A nested query places a subquery inside WHERE or FROM of an outer query.

various joins

Definition. A join combines rows of two tables on a condition.

Key points.

INNER JOIN returns only matching rows.
LEFT (RIGHT) OUTER JOIN keeps all rows of the left (right) table, with NULLs where nothing matches.
FULL OUTER JOIN keeps unmatched rows from both sides; CROSS JOIN gives the Cartesian product.

Inner join keeps matches only; outer joins also keep unmatched rows padded with NULL.

indexing

Definition. An index is a separate data structure, usually a B-tree, that speeds up searching on a column.

Key points.

It works like a book index, giving the row location without a full scan.
CREATE INDEX idx_name ON Emp(name);
Reads get faster, but inserts, updates and storage cost more.

An index trades extra storage and slower writes for much faster lookups.

triggers

Definition. A trigger is a stored procedure that fires automatically on an INSERT, UPDATE or DELETE event.

Key points.

It follows the Event-Condition-Action model.
It can run BEFORE or AFTER the event, per row or per statement.
Uses include auditing and enforcing rules that constraints cannot express.

A trigger is an Event-Condition-Action rule executed automatically by the DBMS.

assertions

Definition. An assertion is a named condition, created with CREATE ASSERTION, that the whole database must always satisfy.

Key points.

Syntax: CREATE ASSERTION name CHECK (condition);
It can span several tables, unlike a column CHECK.
Any update that would make the condition false is rejected.

An assertion is a database-wide CHECK condition that no update may violate.

Relational algebra and relational calculus

Definition. Relational algebra is a procedural query language: a set of operations that take one or two relations and return a relation. Relational calculus is its non-procedural (declarative) counterpart.

Key points.

Basic operations are select $\sigma$, project $\pi$, union $\cup$, set difference $-$, Cartesian product $\times$ and rename $\rho$; the others (join, intersection, division) are derived from them.
Closure: the result of every operation is itself a relation, so operations can be nested.
Union and intersection are commutative and associative; difference and Cartesian product are not commutative, though product is associative.
Selections commute: $\sigma_{c1}(\sigma_{c2}(R)) = \sigma_{c2}(\sigma_{c1}(R))$, and a cascade of projections collapses to the last.
Idempotence: $R \cup R = R$ and $R \cap R = R$.
Selection distributes over union and difference: $\sigma_c(R \cup S) = \sigma_c(R) \cup \sigma_c(S)$; it also pushes into a join.
Union and difference need union-compatible relations (same degree and domains).

Operation	Symbol	Meaning
Select	$\sigma_{c}(R)$	Rows satisfying condition c
Project	$\pi_{A}(R)$	Chosen columns, duplicates removed
Union	$R \cup S$	Tuples in R or S
Difference	$R - S$	Tuples in R not in S
Product	$R \times S$	Every R tuple paired with every S tuple
Rename	$\rho_{X}(R)$	Renames relation or attributes

Example. Cartesian product $P \times Q$: P(A,B,C) has 3 tuples and Q(B,C,D) has 3, so the result has $3 \times 3 = 9$ tuples and 6 attributes (P.A, P.B, P.C, Q.B, Q.C, Q.D).

P.A	P.B	P.C	Q.B	Q.C	Q.D
C	C	D	B	F	M
C	C	D	F	G	R
C	C	D	I	J	K
A	D	N	B	F	M
A	D	N	F	G	R
A	D	N	I	J	K
D	E	H	B	F	M
D	E	H	F	G	R
D	E	H	I	J	K

Answer frame. Open with "Relational algebra is a procedural query language whose operations take relations and give relations"; list the six basic operations in a table with symbol and example; then develop closure, commutativity, associativity, idempotence and distributivity with one equivalence each; for the numerical, state degree and cardinality first, then list the 9 tuples; close with "Calculus states what is wanted, algebra states how to get it."

Relational algebra is a procedural query language of operations on relations, and every operation returns a relation.

Asked: [7 marks] (Jun 2023) List out the properties of relation algebra. Asked: [14 marks] (Jun 2024) Write a short note on any two: i) Relational algebra and relational calculus ii) Select, Project, and Join Operations iii) Concurrency Control Techniques iv) Data Dictionary and Dynamic Performance Views Asked: [7 marks] (Jun 2025) Find the Cartesian product of the following P and Q. Asked: [7 marks] (Jun 2026) Discuss Relational algebra write basic operation of Relational algebra in DBMS.

Relational algebra operations like select

Definition. Select $\sigma_{c}(R)$ returns the tuples of R that satisfy condition c.

Key points.

Example: $\sigma_{salary>50000}(Emp)$ keeps the rich employees.
Conditions combine with $\land$, $\lor$, $\lnot$ and comparison operators.
It filters rows, keeps all attributes and never increases the number of tuples.

Select is a horizontal filter that picks rows satisfying a predicate.

Project

Definition. Project $\pi_{A_1,\dots,A_k}(R)$ keeps only the listed columns.

Key points.

It removes duplicate rows from the result.
Example: $\pi_{name, salary}(Emp)$.
It is a vertical filter and reduces the degree.

Project is a vertical filter that keeps chosen columns and removes duplicates.

Join

Definition. Join $R \bowtie_{c} S$ is a Cartesian product followed by a selection: $\sigma_c(R \times S)$.

Key points.

Theta join uses any comparison; equi-join uses only equality.
Natural join $R \bowtie S$ equates all common attributes and keeps one copy of each.
Example: $Emp \bowtie Dept$ joins on the common attribute did.

A join combines related tuples of two relations: product followed by selection.

Division

Definition. Division $R \div S$, for R(X, Y) and S(Y), returns the X values that are paired in R with every tuple of S; it suits "for all" queries.

Steps.

Schema needed: attributes of S must be a subset of R; result attributes are those of R not in S.
Take each X value and collect its set of Y values.
Keep X only if that set contains all of S.

Example. R = (S1,DB), (S1,OS), (S2,DB), (S3,DB), (S3,OS); S = {DB, OS}. S1 and S3 have both, S2 lacks OS, so $R \div S$ = {S1, S3}. Equivalent: $\pi_X(R) - \pi_X((\pi_X(R) \times S) - R)$; in SQL, NOT EXISTS twice.

Division finds the X values related to all the tuples of S.

Asked: [7 marks] (Jun 2023) Explain division operation in relational algebra with a suitable example.

outer union

Definition. Outer union combines two relations that are not union compatible.

Key points.

The result has all attributes of both, with common ones merged.
Missing values are filled with NULL.
Ordinary union needs the same degree and domains; outer union does not.

Outer union takes the union of the attributes and pads with NULL where values are missing.

Types of relational calculus (tuple and domain)

Definition. Tuple relational calculus (TRC) has the form $\{t \mid P(t)\}$, all tuples t for which predicate P is true. Domain relational calculus (DRC) has the form $\{\langle x_1,\dots,x_n\rangle \mid P(x_1,\dots,x_n)\}$ with variables ranging over attribute values.

Key points.

TRC variables range over whole tuples; DRC variables range over single domain values.
Both use $\exists$, $\forall$ and $\land, \lor, \lnot$; SQL is based on TRC, QBE on DRC.
TRC example: $\{t \mid t \in Emp \land t.salary > 50000\}$ returns all employee tuples earning above 50000.
DRC equivalent: $\{\langle n,s\rangle \mid \exists d\,(\langle n,s,d\rangle \in Emp \land s > 50000)\}$.
Safe expressions of both have the same expressive power as relational algebra.

Basis	Tuple calculus	Domain calculus
Variable ranges over	Tuples of a relation	Values of an attribute
Attribute access	`t.salary`	Variable `s` per column
Form	$\{t \mid P(t)\}$	$\{\langle x_1..x_n\rangle \mid P\}$
Basis of	SQL	QBE
Verbosity	Shorter	Needs a variable per attribute

Answer frame. For comparison: open with both definitions, draw the table above, then give the two example queries and the equivalence; close with "TRC is close to SQL, DRC to QBE". For TRC alone: define $\{t \mid P(t)\}$, explain tuple variable and predicate, give the example query with its result, and close with the equivalence to algebra.

Tuple calculus uses tuple variables, domain calculus uses domain variables, and both are non-procedural and equal in power to relational algebra.

Asked: [7 marks] (Jun 2024) Compare and contrast tuple-oriented and domain-oriented relational calculus. Asked: [7 marks] (Jun 2025) Describe about tuple relational calculus with suitable example.

Last-minute revision

Relation = set of tuples; degree = columns, cardinality = rows.
Primary key: unique and NOT NULL, one per table; foreign key references a primary key.
Intension = schema (stable); extension = instance (changes).
SQL groups: DDL, DML, DQL, DCL, TCL.
WHERE filters rows before GROUP BY; HAVING filters groups after.
Basic algebra operations: $\sigma, \pi, \cup, -, \times, \rho$.
$|P \times Q| = |P| \cdot |Q|$ and degrees add; here 9 tuples, 6 attributes.
Projection removes duplicates; union needs compatible relations.
Division answers "for all" queries: $R \div S$.
TRC: $\{t \mid P(t)\}$; DRC: $\{\langle x_1..x_n\rangle \mid P\}$.
Referential integrity: foreign key matches a primary key or is NULL.

Memory hooks

SPUDCR: Select, Project, Union, Difference, Cartesian, Rename.
WHERE before, HAVING after the group.
Intension = Intent (design); Extension = Existing data.
Tuple calculus is SQL; Domain calculus is QBE.
Division = "for ALL".

Coverage checklist

Domains: no past questions.
Tuples: no past questions.
Attributes: no past questions.
Relations: no past questions.
Characteristics of relations: no past questions.
Keys: primary and foreign key in SQL (Jun 2025).
Key attributes of relation: no past questions.
Relational database: no past questions.
Schemas: relational database schema and components (Jun 2024).
Integrity constraints: no past questions.
Referential integrity: no past questions.
Intension and Extension: difference with examples (Jun 2024).
Relational Query languages: SQL-DDL: SQL commands with example (Jun 2026).
DML: GROUP BY and HAVING (Jun 2023).
integrity constraints: no past questions.
Complex queries: no past questions.
various joins: no past questions.
indexing: no past questions.
triggers: no past questions.
assertions: no past questions.
Relational algebra and relational calculus: properties (Jun 2023), short note (Jun 2024), Cartesian product (Jun 2025), basic operations (Jun 2026).
Relational algebra operations like select: no past questions.
Project: no past questions.
Join: no past questions.
Division: division with example (Jun 2023).
outer union: no past questions.
Types of relational calculus i.e. Tuple oriented and domain oriented relational calculus and its operations: compare TRC and DRC (Jun 2024), TRC with example (Jun 2025).

Account

Database Management System (AD-402) - Unit 2 Short Notes

How unit 2 is examined

Domains

Tuples

Attributes

Relations

Characteristics of relations

Keys

Key attributes of relation

Relational database

Schemas

Integrity constraints

Referential integrity

Intension and Extension

Relational Query languages: SQL-DDL

DML

Integrity constraints: CHECK, NOT NULL, UNIQUE

Complex queries

various joins

indexing

triggers

assertions

Relational algebra and relational calculus

Relational algebra operations like select

Project

Join

Division

outer union

Types of relational calculus (tuple and domain)

Last-minute revision

Memory hooks

Coverage checklist