DBMS and SQL MCQs with answers and explanations

Given relation R(A, B, C, D) with functional dependencies: A -> B, B -> C, C -> D. Is R in 3NF? If not, decompose to 3NF.

Step 1: Find candidate key.
$A^{+}=\{A,B,C,D\}$ (since $A\to B$, $B\to C$, $C\to D$).
So $A$ is the only candidate key.

Step 2: Check for partial dependencies (2NF).
Since the key is single attribute $A$, there are no partial dependencies. $R$ is in 2NF.

Step 3: Check for transitive dependencies (3NF).
$A\to B$ and $B\to C$: This is a transitive dependency (non-prime $B$ determines non-prime $C$).
$B\to C$ and $C\to D$: This is another transitive dependency.
So $R$ is NOT in 3NF.

Step 4: Decompose to 3NF using synthesis algorithm.
From $A\to B$: Create $R_1(A,B)$ with key $A$
From $B\to C$: Create $R_2(B,C)$ with key $B$
From $C\to D$: Create $R_3(C,D)$ with key $C$

Step 5: Check if original key is preserved.
Yes, $A$ is in $R_1$.

Final decomposition: $R_1(A,B)$, $R_2(B,C)$, $R_3(C,D)$
All are in 3NF and dependency-preserving.

Two transactions: T1 reads A, writes A; T2 reads A, writes A. Show a schedule that results in Lost Update and explain how 2PL prevents it.

Step 1: Lost Update Example (non-serializable schedule):
Time 1: T1: Read(A) -> $A=100$
Time 2: T2: Read(A) -> $A=100$
Time 3: T1: $A=A+10$, Write(A) -> $A=110$
Time 4: T2: $A=A+20$, Write(A) -> $A=120$

Result: T1's update is lost! Correct should be 130 (100+10+20).

Step 2: How 2PL Prevents This:
In Two-Phase Locking:
- Growing phase: Acquire locks
- Shrinking phase: Release locks (no new locks after first release)

Step 3: 2PL Schedule:
Time 1: T1: Lock-S(A) -> Read(A)
Time 2: T1: Upgrade to Lock-X(A) -> T1 holds exclusive lock
Time 3: T2: Lock-S(A) -> BLOCKED (T1 has X lock)
Time 4: T1: Write(A), Unlock(A)
Time 5: T2: Lock-S(A) granted -> Read(A) gets 110
Time 6: T2: Upgrade to Lock-X(A) -> Write(A) = 130

Result: 2PL ensures serializability. Final value is correct: 130.

Given Employee(EID, Name, DeptID, Salary, ManagerID) and Department(DeptID, DName, Location). Write SQL to find employees who earn more than their department's average salary, and explain if an index would help.

Step 1: SQL Query:
SELECT e.EID, e.Name, e.Salary, d.DName
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employee e2
WHERE e2.DeptID = e.DeptID
);

Step 2: Alternative with JOIN:
SELECT e.EID, e.Name, e.Salary, d.DName, avg_sal.avg_salary
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
JOIN (
SELECT DeptID, AVG(Salary) as avg_salary
FROM Employee
GROUP BY DeptID
) avg_sal ON e.DeptID = avg_sal.DeptID
WHERE e.Salary > avg_sal.avg_salary;

Step 3: Index Analysis:
- Index on Employee(DeptID): Helps join and group by operations, significantly improves correlated subquery
- Index on Employee(Salary): Less helpful here (range comparison after join)
- Composite index on Employee(DeptID, Salary): Most effective - supports both grouping and salary comparison
- Index on Department(DeptID): Helps join (usually primary key, already indexed)

Step 4: Query Plan Considerations:
With proper indexing, optimizer can use index seek for department grouping, avoiding full table scan. The correlated subquery version may be rewritten as join by optimizer.

Convert this ER scenario to relational schema: University has Departments (DeptID, Name). Each Department has Professors (ProfID, Name, Specialization). A Professor belongs to exactly one Department. Professors teach Courses (CourseID, Title, Credits). A Course can be taught by multiple Professors, and a Professor can teach multiple Courses. Some Courses are prerequisites for others.

Step 1: Identify Entities and Relationships:
- Department: Strong entity
- Professor: Strong entity, belongs to Department (1:N)
- Course: Strong entity
- Teaches: M:N between Professor and Course
- Prerequisite: M:N self-referencing on Course

Step 2: Relational Schema:

Department(DeptID, Name)
Primary Key: DeptID

Professor(ProfID, Name, Specialization, DeptID)
Primary Key: ProfID
Foreign Key: DeptID REFERENCES Department(DeptID)
(1:N - Professor belongs to one Department)

Course(CourseID, Title, Credits)
Primary Key: CourseID

Teaches(ProfID, CourseID, Semester, Year)
Primary Key: (ProfID, CourseID, Semester, Year)
Foreign Key: ProfID REFERENCES Professor(ProfID)
Foreign Key: CourseID REFERENCES Course(CourseID)
(M:N relationship with its own attributes)

Prerequisite(CourseID, PrereqCourseID)
Primary Key: (CourseID, PrereqCourseID)
Foreign Key: CourseID REFERENCES Course(CourseID)
Foreign Key: PrereqCourseID REFERENCES Course(CourseID)
(M:N self-referencing relationship)

Step 3: Notes:
- Department-Professor is 1:N, so DeptID is stored in Professor
- Teaches is M:N, so it becomes a separate table
- Prerequisite is M:N on same entity, requiring two FKs to Course
- Composite PK in Prerequisite prevents duplicate prerequisites
- Semester and Year are descriptive attributes of the Teaches relationship

Which normal form eliminates partial dependencies?

Step 1: 1NF eliminates repeating groups and ensures atomic values.
Step 2: 2NF eliminates partial dependencies (non-key attributes must depend on the entire key).
Step 3: A partial dependency occurs when a non-key attribute depends on part of a composite key.
Answer: 2NF

Which type of join returns all records when there is a match in either left or right table?

Step 1: INNER JOIN returns only matching records from both tables.
Step 2: LEFT JOIN returns all records from left table and matched records from right.
Step 3: FULL OUTER JOIN returns all records when there is a match in either table, filling NULLs where no match exists.
Answer: FULL OUTER JOIN

Which SQL statement removes all records from a table but keeps the table structure?

Step 1: DELETE removes specific rows based on WHERE clause (can be rolled back).
Step 2: DROP removes the entire table including structure and data.
Step 3: TRUNCATE removes all rows quickly, cannot be rolled back in some DBMS, preserves table structure.
Answer: TRUNCATE