DBMS · Question paper

CET-2 · 9 Oct 2025

DBMS (C2311C4) · Continuous Evaluation Test (CET)-2 · 9 October 2025 · 30 marks · 12:30 – 2:00 pm ITM (SLS) Baroda University · B.Tech CSE/CSN/IT/AI&DS · Semester III, Odd Semester 2025. Questions transcribed from the original paper. Solutions are written by CampusLoop; check them against your class notes.

Q1. Multiple choice questions [6]

1. A relation is in 1NF if: a) It has no multivalued attributes · b) It has no transitive dependency · c) It has no partial dependency · d) Every attribute is a candidate key

Answer: (a) every attribute holds atomic (single) values.

2. In relational algebra, the σ (sigma) operator is used for: a) Projection · b) Selection · c) Join · d) Cartesian Product

Answer: (b) Selection (chooses rows that satisfy a condition).

3. A relation is in BCNF if: a) It is in 2NF and every determinant is a candidate key · b) It is in 3NF and every determinant is a candidate key · c) It is in 1NF only · d) It has no foreign keys

Answer: (b). For every non-trivial FD X → Y, X must be a super key.

4. Consider R(A, B, C) with A → B, B → C. Which is the highest normal form of R? a) 1NF · b) 2NF · c) 3NF · d) BCNF

Answer: (b) 2NF. The key is A (single attribute, so no partial dependency → 2NF), but A → B → C is a transitive dependency, so R is not in 3NF.

5. If π(A,B)(σ(A>5)(R)) is executed, in which order are operations applied? a) Projection first, then Selection · b) Selection first, then Projection · c) Both at the same time · d) Depends on the query optimizer

Answer: (b) the inner expression σ is evaluated first, then π.

6. Transitive dependency is removed in: a) 1NF · b) 2NF · c) 3NF · d) 4NF

Answer: (c) 3NF.

Q2. Answer any THREE out of FIVE [9]

Q2.1 Explain different aggregate functions with example.

Aggregate functions compute one value from a set of rows (ignoring NULLs, except COUNT(*)):

FunctionMeaningExample on Employee(salary, dept)
COUNTnumber of rows/valuesSELECT COUNT(*) FROM Employee;
SUMtotalSELECT SUM(salary) FROM Employee;
AVGaverageSELECT AVG(salary) FROM Employee;
MINsmallestSELECT MIN(salary) FROM Employee;
MAXlargestSELECT MAX(salary) FROM Employee;

Used with GROUP BY for per-group results and HAVING to filter groups:

SELECT dept, AVG(salary) FROM Employee GROUP BY dept HAVING AVG(salary) > 50000;

Q2.2 Explain canonical cover and extraneous attributes with examples.

A canonical cover Fc of F is a minimal set of FDs equivalent to F such that: every right side has a single attribute (or is combined per left side), no FD has an extraneous attribute, and no FD is redundant. Each left side is unique.

An attribute is extraneous if removing it does not change the closure of F.

  • Left side: in AB → C, A is extraneous if B⁺ (under F) already contains C.
  • Right side: in A → CD, C is extraneous if A → C can be derived from the remaining FDs.

Example: F = {A → BC, B → C, A → B, AB → C}.

  1. Combine left sides: A → BC (A → B merges in).
  2. In AB → C, A is extraneous because B → C exists → becomes B → C (duplicate, drop).
  3. In A → BC, C is extraneous because A → B and B → C give A → C. Fc = {A → B, B → C}.

Q2.3 What is fully and partial functional dependency? Explain with example.

For a composite key, say Enrol(RollNo, CourseID, StudentName, Grade) with key (RollNo, CourseID):

  • Full functional dependency: Y depends on the whole key and not on any proper subset. (RollNo, CourseID) → Grade is full: neither RollNo nor CourseID alone determines Grade.
  • Partial dependency: Y depends on part of the key. RollNo → StudentName is partial, because StudentName depends only on RollNo.

2NF removes partial dependencies by moving StudentName into Student(RollNo, StudentName).

Q2.4 Discuss the steps of query processing with a proper diagram.

SQL query → [1. Parsing & translation] → relational algebra expression → [2. Optimization] (uses statistics from the catalog) → execution plan → [3. Evaluation engine] (reads data) → query output

  1. Parsing and translation: check syntax, verify relation/attribute names against the data dictionary, translate SQL into a relational-algebra expression (query tree).
  2. Optimization: generate equivalent expressions and choose the cheapest evaluation plan using statistics (number of tuples, indexes) and cost estimates (disk I/O). Example: push selections down before joins.
  3. Evaluation: the query-execution engine executes the chosen plan (e.g. index scan + hash join) and returns the result.

Q2.5 Define super key, primary key and candidate key.

  • Super key: a set of one or more attributes that uniquely identifies each tuple. {RollNo}, {RollNo, Name}.
  • Candidate key: a minimal super key: no proper subset is a super key. {RollNo}, {Email}.
  • Primary key: the candidate key chosen by the designer to identify tuples; unique and NOT NULL. RollNo.

Q3. Answer any THREE out of FIVE [15]

Q3.1 Relational algebra for the Student table

Student(RollNo, Name, Branch, SPI) with rows (101, Raj, CE, 6), (102, Meet, ME, 8), (103, Harsh, EE, 7), (104, Punit, CE, 9).

  1. RollNo, Name and SPI of all students in the CE branch: π RollNo, Name, SPI (σ Branch = 'CE' (Student)) → (101, Raj, 6), (104, Punit, 9)
  2. Name and Branch of students whose SPI is more than 8 and who belong to CE: π Name, Branch (σ SPI > 8 ∧ Branch = 'CE' (Student)) → (Punit, CE)
  3. Name, Branch and SPI of students in CE or ME with SPI more than 8: π Name, Branch, SPI (σ (Branch = 'CE' ∨ Branch = 'ME') ∧ SPI > 8 (Student)) → (Punit, CE, 9). (Meet has SPI exactly 8, so is excluded.)
  4. Name and Branch of students whose SPI is between 7 and 9: π Name, Branch (σ SPI ≥ 7 ∧ SPI ≤ 9 (Student)) → (Meet, ME), (Harsh, EE), (Punit, CE)

Q3.2 What is normalization? Need, and the normal forms.

Normalization is the process of organising relations to reduce redundancy and avoid insert, update and delete anomalies, by decomposing them according to functional dependencies.

Need: removes duplicate data; prevents anomalies (e.g. a department's HOD stored in every student row must be updated everywhere); keeps data consistent; saves storage.

  • 1NF: all attributes atomic; no repeating groups.
  • 2NF: 1NF + no partial dependency of a non-prime attribute on part of a candidate key.
  • 3NF: 2NF + no transitive dependency (for X → A, X is a super key or A is prime).
  • BCNF: for every non-trivial X → Y, X is a super key.
  • 4NF: BCNF + no non-trivial multivalued dependencies.
  • 5NF: no join dependency that is not implied by candidate keys.

Q3.3 Closure for R(A, B, C, D, E), F = {A → BC, CD → E, B → D, E → A}

Attribute closures:

  • A⁺ = {A, B, C} → (B → D) {A, B, C, D} → (CD → E) {A, B, C, D, E}
  • E⁺ = {E, A} → then as above → {A, B, C, D, E}
  • CD⁺ = {C, D, E} → (E → A) {A, C, D, E} → (A → BC) {A, B, C, D, E}
  • BC⁺ = {B, C, D} → (CD → E) {B, C, D, E} → (E → A) {A, B, C, D, E}
  • B⁺ = {B, D}; C⁺ = {C}; D⁺ = {D} (not keys)
  1. Candidate keys: A, E, CD, BC (each is minimal; no single attribute among B, C, D is a key).
  2. Canonical cover: split A → BC into A → B, A → C and test each FD for extraneous attributes and redundancy. In CD → E, neither C⁺ nor D⁺ contains E, so no extraneous attribute; none of the FDs can be derived from the others. Fc = {A → BC, CD → E, B → D, E → A} (same as F).
  3. Prime attributes: A, B, C, D, E (every attribute appears in some candidate key). Non-prime attributes: none.

Q3.4 Discuss the various types of join operations. Why are joins required?

Joins combine rows of two tables based on a related column; they are needed because normalization stores related data in separate tables.

  • Inner join (theta / equi join): rows that match in both tables. SELECT * FROM Emp e JOIN Dept d ON e.dept_id = d.dept_id;
  • Natural join: equi-join on all common attributes, keeping one copy of each.
  • Left outer join: all rows of the left table, matching rows from the right, NULLs where no match.
  • Right outer join: all rows of the right table.
  • Full outer join: all rows from both, NULLs where no match.
  • Cross join: Cartesian product (every combination).
  • Self join: a table joined with itself (e.g. employee and manager).

Q3.5 Normalize the Student table (1NF → 2NF → 3NF → BCNF)

Given F = {Student_ID → Student_Name, Dept_ID; Dept_ID → Dept_Name, HOD_Name; HOD_Name → Dept_ID}.

1NF: Subjects is multivalued ("DBMS, OS, SE"), so put one subject per row. Key becomes (Student_ID, Subject):

Student_IDStudent_NameSubjectDept_IDDept_NameHOD_Name
101RajDBMSD1ComputerDr. A
101RajOSD1ComputerDr. A
101RajSED1ComputerDr. A
102MeeraCND2ITDr. B
102MeeraAID2ITDr. B
103AmitDBMSD1ComputerDr. A
103AmitCND1ComputerDr. A

2NF: Student_Name, Dept_ID (and through it Dept_Name, HOD_Name) depend only on Student_ID, part of the key → partial dependency. Decompose:

  • Student(Student_ID, Student_Name, Dept_ID, Dept_Name, HOD_Name)
  • Student_Subject(Student_ID, Subject)

3NF: Student_ID → Dept_ID → Dept_Name, HOD_Name is transitive. Decompose:

  • Student(Student_ID, Student_Name, Dept_ID) (101 Raj D1, 102 Meera D2, 103 Amit D1)
  • Department(Dept_ID, Dept_Name, HOD_Name) (D1 Computer Dr. A, D2 IT Dr. B)
  • Student_Subject(Student_ID, Subject)

BCNF: In Department, the FDs are Dept_ID → Dept_Name, HOD_Name and HOD_Name → Dept_ID. Both Dept_ID and HOD_Name are candidate keys of Department (HOD_Name⁺ = {HOD_Name, Dept_ID, Dept_Name}), so every determinant is a super key. Student and Student_Subject are also in BCNF. The 3NF design is already in BCNF.