DBMS Exam Notes: Constraints, SQL, Normalization & Relational Algebra

Posted on Oct 14, 2026 in Business Sciences

PAPER 1 — Database Management Systems

Q1. Integrity Constraints

Integrity constraints are rules that maintain the correctness and consistency of data.

1. Primary Key

• Uniquely identifies each row.

• Cannot contain NULL.

• Example:

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30)
);

2. Unique Key

• Ensures all values in a column are unique.

• Generally allows NULL depending on the DBMS.

Email VARCHAR(50) UNIQUE

3. NOT NULL

• Ensures a column must have a value.

Name VARCHAR(30) NOT NULL

4. CHECK

• Restricts values according to a condition.

Age INT CHECK (Age >= 18)

5. Foreign Key

• Maintains referential integrity between tables.

DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

Purpose: These constraints prevent invalid, duplicate, missing, or inconsistent data.

Q2. SQL Commands

Suppose we have:

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30),
    Marks INT
);

CREATE

Creates a database object/table.

CREATE TABLE Student(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30),
    Marks INT
);

INSERT

Adds records.

INSERT INTO Student VALUES (101, 'Rahul', 85);

DELETE

Deletes selected records.

DELETE FROM Student
WHERE RollNo = 101;

TRUNCATE

Deletes all rows from a table while retaining the table structure.

TRUNCATE TABLE Student;

COMMIT

Permanently saves transaction changes.

COMMIT;

ROLLBACK

Undoes uncommitted transaction changes.

ROLLBACK;

In standard exam classification, CREATE/TRUNCATE are DDL, INSERT/DELETE are DML, and COMMIT/ROLLBACK are TCL.

Q3. Functional Dependencies and Their Applications

A functional dependency (FD) describes a relationship between attributes.

If A → B, then the value of A uniquely determines the value of B.

Applications

1. Finding candidate keys

   • FDs help determine which attributes can uniquely identify tuples.

2. Normalization

   • Used to identify partial and transitive dependencies.

   • Helps convert relations into 2NF, 3NF and BCNF.

3. Removing redundancy

   • Prevents repeated storage of the same information.

4. Avoiding anomalies

   • Helps prevent insertion, deletion and update anomalies.

5. Database design

   • Helps determine appropriate table structures and relationships.

Q4. Attribute Closure and Candidate Keys

Given: R(A, B, C, D, E, H)

FDs:

• A → BC

• BC → D

• C → D

• D → A

Attribute Closures

A⁺

• Start: A⁺ = {A}

• Using A → BC: A⁺ = {A, B, C}

• Using C → D: A⁺ = {A, B, C, D}

• Using D → A: nothing new.

Result: A⁺ = {A, B, C, D}

B⁺

• No FD starts with B.

Result: B⁺ = {B}

C⁺

• C → D, D → A, A → BC

Result: C⁺ = {A, B, C, D}

D⁺

• D → A, A → BC, C → D

Result: D⁺ = {A, B, C, D}

E⁺ and H⁺

• Neither occurs on the RHS of any FD.

Result: E⁺ = {E}, H⁺ = {H}

Candidate Keys

Notice that E and H never appear on the RHS, so both must be present in every candidate key.

We need one of A, C, D to obtain A, B, C, D.

Candidate Keys = {AEH, CEH, DEH}

For example, (AEH)⁺ gives A, B, C, D, E, H, hence all attributes. Similarly, (CEH)⁺ = R and (DEH)⁺ = R.

Q5(a). SQL Queries

Given:

Emp(Eid, Ename, Salary, Address, Dept_id, Age)
Dept(Dept_id, Dname, Address)

(a) Employees whose address is BHOPAL

SELECT Ename
FROM Emp
WHERE Address = 'BHOPAL';

(b) Employees working for CSE department

SELECT E.Ename
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE D.Dname = 'CSE';

(c) Employee IDs of employees older than 30

SELECT Eid
FROM Emp
WHERE Age > 30;

(d) Names of employees whose address is same as department address

SELECT E.Ename
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE E.Address = D.Address;

(e) Average salary of employees in each department

SELECT Dept_id, AVG(Salary) AS Avg_Salary
FROM Emp
GROUP BY Dept_id;

(f) Department ID and department name of employees having salary > 50,000

SELECT DISTINCT D.Dept_id, D.Dname
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE E.Salary > 50000;

Q5(b). Company Database — SQL

Given:

Employee(employee_name, street, city)
Works(employee_name, company_name, salary)
Company(company_name, city)
Manages(employee_name, manager_name)

(a) Names of all employees who work for State Bank

SELECT employee_name
FROM Works
WHERE company_name = 'State Bank';

(b) Names of cities of residence of employees working for HDFC Bank

SELECT E.city
FROM Employee E
JOIN Works W
ON E.employee_name = W.employee_name
WHERE W.company_name = 'HDFC Bank';

(c) Employees who do not work for State Bank or ICICI Bank

SELECT employee_name
FROM Works
WHERE company_name NOT IN ('State Bank', 'ICICI Bank');

(d) Employees who belong to the same city where they are working

SELECT E.employee_name
FROM Employee E
JOIN Works W
ON E.employee_name = W.employee_name
JOIN Company C
ON W.company_name = C.company_name
WHERE E.city = C.city;

(e) Employees whose manager has the same name as employee

SELECT employee_name
FROM Manages
WHERE employee_name = manager_name;

(f) Highest salary among employees working in each company

SELECT company_name, MAX(salary) AS Highest_Salary
FROM Works
GROUP BY company_name;

Q6(a). BCNF Problem

Given: R(A, B, C, D, E)

FDs:

• A → B

• BC → E

• ED → A

Candidate Keys

BCD

• BC → E, then ED → A

• BCD⁺ = ABCDE

CDE

• ED → A, then A → B

• CDE⁺ = ABCDE

Candidate Keys = {BCD, CDE}

Prime attributes: B, C, D, E. Non-prime attribute: A.

Highest Normal Form

Consider ED → A. ED is a proper subset of candidate key CDE, while A is non-prime. This is a partial dependency, so the relation is not in 2NF.

Highest Normal Form = 1NF

BCNF Decomposition

Step 1: A → B

A is not a superkey, so this violates BCNF. Decompose:

• R1(A, B)

• R2(A, C, D, E)

Step 2: In R2(A, C, D, E)

FD: ED → A. ED is not a superkey of R2, so decompose:

• R3(A, D, E)

• R4(C, D, E)

Valid BCNF decomposition: R1(A, B), R3(A, D, E), R4(C, D, E)

Each resulting relation is in BCNF.

Q6(b). Conflict Serializability

Schedule: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)

A precedence graph has one node per transaction and an edge Ti → Tj when conflicting operations of Ti precede those of Tj. An acyclic graph means the schedule is conflict-serializable.

Conflicts

• On X: r3(X) → w1(X), so T3 → T1

• On Y: r2(Y) → w3(Y), so T2 → T3

• On Z: w2(Z) → r1(Z) and w2(Z) → w1(Z), so T2 → T1

Graph

T2 ─────→ T3 ─────→ T1
 │
 └────────────────→ T1

There is no cycle, so the schedule is conflict-serializable.

Equivalent serial order: T2 → T3 → T1

PAPER 2 — DBMS

Q1. ER Model vs Relational Model

Q2. File Processing System vs DBMS

Limitations of File Processing System

1. Data redundancy

2. Data inconsistency

3. Difficult data access

4. Data isolation

5. Integrity problems

6. Security problems

7. Concurrent-access problems

8. Poor backup and recovery

Why DBMS?

A DBMS provides security, data integrity, concurrency control, backup/recovery, reduced redundancy, data independence and efficient querying.

Q3. Candidate Keys and Superkeys

Given: R(A, B, C, D, E)

FDs: A → B, BC → E, ED → A

Candidate Key 1: BCD

BC → E, then ED → A, so BCD⁺ = ABCDE. Therefore BCD is a candidate key.

Candidate Key 2: CDE

ED → A and A → B, so CDE⁺ = ABCDE. Therefore CDE is a candidate key.

Number of Superkeys

Every superset of a candidate key is a superkey.

• For BCD: 2^(5−3) = 4 supersets

• For CDE: 2^(5−3) = 4 supersets

• Common supersets are supersets of BCDE: 2^(5−4) = 2

Total distinct superkeys = 4 + 4 − 2 = 6

Q4. ER Diagram → Relational Model

From the diagram, the entities are approximately M, P and N. N is shown as a weak entity (double rectangle), with R2 as its identifying relationship.

M

M(M1, M2, M3)

where M1 is the primary key.

P

P(P1, P2)

where P1 is the primary key.

Relationship R1

If R1 is between M and P:

R1(M1, P1)

For an M:N relationship, PK = (M1, P1). Both are foreign keys.

N — Weak Entity

Because N is weak and R2 is its identifying relationship, N uses the owner’s key plus its partial key.

N(P1, N1, N2)

Primary key: (P1, N1), where P1 is a foreign key referencing P, N1 is the partial key, and N2 is an attribute of N.

Q5(a). Three-Schema Architecture

          External Level
        /       |       \
     View 1   View 2   View 3
             ↓
       Conceptual Level
             ↓
        Internal Level
             ↓
       Physical Storage

1. External Level

• User/application view.

• Different users can have different views.

• Hides unnecessary details.

2. Conceptual Level

• Describes the complete logical database.

• Contains entities, relationships and constraints.

• Independent of physical storage.

3. Internal Level

• Describes how data is physically stored.

• Includes files, indexes, blocks and storage structures.

Data Independence

Physical Data Independence: Ability to change the internal/physical schema (file organization, indexes, storage devices) without changing the conceptual schema.

Logical Data Independence: Ability to change the conceptual schema (adding attributes or relations) without affecting external views or programs.

Logical independence is harder to achieve than physical independence.

Q5(b). Constraints in Database Design

(i) Cardinality / Mapping Constraints

Defines how many entities can participate in a relationship: 1:1, 1:N, N:1, M:N.

Example: DEPARTMENT 1 ── N EMPLOYEE (one department can have many employees).

(ii) Participation Constraints

• Total participation: Every entity must participate (double line).

• Partial participation: Participation is optional (single line).

(iii) Integrity Constraints

Rules that ensure correctness of data: primary key, foreign key, unique, NOT NULL and CHECK constraints.

EMPLOYEE(EmpID PRIMARY KEY, DeptID FOREIGN KEY)

Q6(a). Relational Algebra

Employee

Salary

(i) Employee ⋈ Salary

Natural join on EMP_CODE:

(ii) Employee × Salary

Cartesian product gives 4 × 4 = 16 tuples. Each employee is paired with every salary record (for example, Rahul with 101/50000, 102/30000, 103/45000, 104/35000, and so on for Sahil, Harsh and Kunal).

(iii) π EMP_NAME, SALARY (Employee ⋈ Salary)

(iv) Employee LEFT OUTER JOIN Salary

Since every employee has a matching salary, the output equals the natural join:

A left outer join retains all tuples from the left relation even when no matching tuple exists on the right.

Q6(b). Relational Algebra

Department

Employee

(a) Employees with salary > 70,000, showing only EmpID and EmpName

π EmpID, EmpName (σ Salary > 70000 (Employee))

(b) Names of employees working in departments located in New York

π EmpName (σ Location = ‘New York’ (Employee ⋈ Employee.Department = Department.DeptName Department))

HR is located in New York. Both Alice and Charlie work in HR.

(c) Employees working in IT but not HR

π EmpName (σ Department = ‘IT’ (Employee))

(d) All combinations of employees and departments where department names don’t match

σ Employee.Department ≠ Department.DeptName (Employee × Department)

There are 4 × 4 = 16 total combinations. Four combinations have matching department names (Alice–HR, Bob–IT, Charlie–HR, Diana–Marketing), so 16 − 4 = 12 valid combinations.

⭐ Most Important Answers to Memorize

Paper 1

• Q4 candidate keys: AEH, CEH, DEH

• Q6(a) candidate keys: BCD, CDE

• Q6(a) highest normal form: 1NF

• BCNF decomposition: AB, ADE, CDE

• Q6(b) serial order: T2 → T3 → T1

Paper 2

• Q3 candidate keys: BCD, CDE

• Number of superkeys: 6

• Q6(b):

  • Salary > 70,000 → Charlie, Diana

  • New York departments → Alice, Charlie

  • IT but not HR → Bob

  • Unequal employee/department combinations → 12 tuples