DBMS

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->B, B->C, C->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 -> B and B -> C: This is a transitive dependency (non-prime B determines non-prime C).
B -> C and C -> D: This is another transitive dependency.
So R is NOT in 3NF.

Step 4: Decompose to 3NF using synthesis algorithm.
From A -> B: Create R1(A, B) with key A
From B -> C: Create R2(B, C) with key B
From C -> D: Create R3(C, D) with key C

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

Final decomposition: R1(A, B), R2(B, C), R3(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.

What does SQL stand for?

Step 1: SQL is the standard language for relational database management systems.
Step 2: The acronym stands for Structured Query Language.
Step 3: It was originally developed at IBM in the 1970s.
Answer: Structured Query Language

Which SQL statement is used to extract data from a database?

Step 1: SQL uses SELECT to query data from tables.
Step 2: The syntax is: SELECT column_name FROM table_name.
Step 3: SELECT * retrieves all columns from the table.
Answer: SELECT

Which SQL clause is used to filter records?

Step 1: The WHERE clause filters records based on specified conditions.
Step 2: Syntax: SELECT * FROM table WHERE condition.
Step 3: It is applied after the FROM clause but before GROUP BY.
Answer: WHERE