Part 2 — Data Models, Languages & Relational Algebra

CLO2 · high value. The DDL/DCL/DML/TCL table is pure recall; the relational-algebra parts reward drawing the resultant table carefully.

Q3smallDefine ‘database language’ and fill the DDL / DCL / DML / TCL table (≥3 commands each). (1 + 2)

Acronym	Elaborated name	Purpose	Example commands (≥3)
DDL	Data Definition Language	Define / change schema structure	CREATE · ALTER · DROP · TRUNCATE · RENAME
DCL	Data Control Language	Control access / permissions	GRANT · REVOKE
DML	Data Manipulation Language	Work with the rows (query & modify)	SELECT · INSERT · UPDATE · DELETE
TCL	Transaction Control Language	Manage transactions / consistency	COMMIT · ROLLBACK · SAVEPOINT

Relational algebra — the operator toolkit

σ π ∪ − × ρ6 basics: Select (rows) · Project (columns) · Union · Difference · Cartesian product · Rename. Joins are × + σ.

Q4 — Employee & Department

The two given relations:

EID	EmpName	DeptID
1	Rahim	10
2	Karima	20
3	Sumon	10
4	Nila	30

4 tuples · 3 attributes

DeptID	DeptName	Location
10	HR	Dhaka
20	Finance	Chattogram
40	IT	Sylhet

3 tuples · 3 attributes

Spot the mismatch before you start. Employee depts = {10, 20, 30}; Department depts = {10, 20, 40}. So Nila (30) has no department and IT (40) has no employee — exactly what parts (b) and (c) test.

Q4(a)Write the relational algebra for the Cartesian product and draw the complete table. (3)

EID	EmpName	Employee.DeptID	Department.DeptID	DeptName	Location
1	Rahim	10	10	HR	Dhaka
1	Rahim	10	20	Finance	Chattogram
1	Rahim	10	40	IT	Sylhet
2	Karima	20	10	HR	Dhaka
2	Karima	20	20	Finance	Chattogram
2	Karima	20	40	IT	Sylhet
3	Sumon	10	10	HR	Dhaka
3	Sumon	10	20	Finance	Chattogram
3	Sumon	10	40	IT	Sylhet
4	Nila	30	10	HR	Dhaka
4	Nila	30	20	Finance	Chattogram
4	Nila	30	40	IT	Sylhet

Q4(b)Write the join that returns all information for employees who belong to a department. Give the resultant table. (2)

A natural / inner (equi) join on DeptID:

Employee ⋈ Department  ≡  σ[Employee.DeptID = Department.DeptID](Employee × Department)

Only matching departments survive, so Nila (30) drops out → 3 rows.

EID	EmpName	DeptID	DeptName	Location
1	Rahim	10	HR	Dhaka
2	Karima	20	Finance	Chattogram
3	Sumon	10	HR	Dhaka

3 tuples · 5 attributes

Q4(c)What is the output of Employee ⟕ Department, and what information does it give? (3)

Q4(d)Write the join to find all employees who live in Dhaka. Give the resultant table. (3)

σ[Location = 'Dhaka'](Employee ⋈ Department)

Dhaka is the HR department (DeptID 10), so its employees are Rahim and Sumon → 2 rows.

EID	EmpName	DeptID	DeptName	Location
1	Rahim	10	HR	Dhaka
3	Sumon	10	HR	Dhaka

2 tuples · 5 attributes

Join types — one picture to keep them straight

Filled region = rows kept. Left circle = Employee, right circle = Department.

Operator summary

Symbol	Name	Does
σ	Selection	Rows matching a predicate
π	Projection	Chosen columns; removes duplicates
×	Cartesian product	All row pairs of two relations
∪ · − · ∩	Set ops	Union · difference · intersection
⋈	Natural join	× then match same-named attributes
⟕ ⟖ ⟗	Outer joins	Keep unmatched left / right / both as NULL
ρ	Rename	Rename a relation / its attributes

Want to try these live on other data? Open the RA Playground →

Next: Part 3 — ER Diagram →