Solutions to CS2855-24 Exam

1. (a) ER Diagram for Innovex IT Company

Entities & Relationships:

1. Employee:
   Attributes: employee_id (PK), full_name (unique), hire_date.
   Multi-valued attribute: email → Separate table for emails.
2. Position:
   Attributes: position_id (PK), level, salary.
3. Bonus:
   Weak entity (depends on Position). Attributes: bonus_id (partial key), date_granted, amount.
4. Works_In:
   Relationship between Employee and Position. Attributes: start_date (date employee takes up position).

Constraints:

• Cardinalities:
  • Employee to Works_In: One-to-Many (1 employee → N positions).
  • Position to Bonus: One-to-Many (1 position → N bonuses).
• Weak Entity: Bonus has a partial key bonus_id and depends on Position via total participation.

ER Diagram Sketch:

Employee (employee_id, full_name, hire_date)  
  ⎿ email (employee_id, email)  
Position (position_id, level, salary)  
  ⎿ Bonus (position_id, bonus_id, date_granted, amount)  
Works_In (employee_id, position_id, start_date)  

---

1. (b) Relational Model Conversion

Given: Train System ER Diagram (Partial)

• Entities: Company, Train, Line, Destination, Departure.

• Relationships: belongs, replaced_by, goes_to.

Resulting Tables:

1. Company (company_id, name, year_founded)

2. Train (train_id, registration_number, company_id (FK → Company))

3. Line (line_id, departure_station, arrival_station)

4. Destination (destination_id, place, arrival_time)

5. Departure (departure_id, line_id (FK → Line), train_id (FK → Train), schedule_time)

6. Replacement (old_train_id (FK → Train), new_train_id (FK → Train))

Foreign Keys:

• Train.company_id references Company.company_id.

• Departure.line_id references Line.line_id.

• Departure.train_id references Train.train_id.

---

2. (a) Relational Algebra for Driver Mileage

Query:
π_{DriverID}(σ_{vehiclemileage ≥ 20000}(Vehicle) ⨝ Transport)

Steps:

1. Join Vehicle and Transport on Registration.
2. Select vehicles with mileage ≥ 20000.
3. Project DriverID.

---

2. (b) SQL Statements

i. Store Driver Phones:
ALTER TABLE Driver ADD COLUMN phone_numbers VARCHAR(15)[];

ii. Count Distinct Vehicles per Driver:

SELECT DriverID, COUNT(DISTINCT Registration) AS num_vehicles  
FROM Transport  
GROUP BY DriverID;  

iii. Conditional Salary Increase:

UPDATE Driver  
SET salary = CASE  
  WHEN salary <= 20000 THEN salary * 1.05  
  WHEN salary BETWEEN 20001 AND 30000 THEN salary * 1.03  
  ELSE salary * 1.01  
END;  

iv. Drivers with >3 Trips per Vehicle:

SELECT v.Registration, d.name  
FROM Vehicle v  
JOIN Transport t ON v.Registration = t.Registration  
JOIN Driver d ON t.DriverID = d.DriverID  
GROUP BY v.Registration, d.name  
HAVING COUNT(*) > 3;  

v. Drivers with Salary <15K and Mileage ≤50K (Post-2019):

SELECT d.name  
FROM Driver d  
JOIN Transport t ON d.DriverID = t.DriverID  
JOIN Vehicle v ON t.Registration = v.Registration  
WHERE d.salary < 15000  
  AND v.vehiclemileage <= 50000  
  AND v.year > 2019;  

vi. Vehicles Above Avg Mileage with ≥3 Drivers:

SELECT v.Registration  
FROM Vehicle v  
JOIN Transport t ON v.Registration = t.Registration  
WHERE v.vehiclemileage > (SELECT AVG(vehiclemileage) FROM Vehicle)  
GROUP BY v.Registration  
HAVING COUNT(DISTINCT t.DriverID) >= 3;  

---

3. (a) Schedule Serializability

Given Schedule:

T1: R(B), R(A), W(A)  
T2: R(A), W(A), R(B), W(B), W(B), R(C)  

Conflict Graph:

• T1 → T2 (W(A) in T1 conflicts with R(A) in T2).
• T2 → T1 (W(B) in T2 conflicts with R(B) in T1).
  Result: Cycle exists → **Not serializable. **

---

3. (b) Transaction Outcomes

Final Values (After Time 18):

• T1: Reads A=10 → A=A-10 (A=0) → Writes A=20 after T2 reads A=0.

• T2: Sum starts at 0 → Adds A=0, B=20+20=40, C=40−10=30 → Sum = 0+40+30=70.

• A=20, B=40, C=30.

---

3. (c) Durability (ACID)**

Definition: Once a transaction is committed, its changes persist even after system failures (e.g., power loss). Achieved via logging (e.g., write-ahead logs).

---

3. (d) BCNF Definition**

A relation is in BCNF if for every non-trivial FD X → Y, X is a superkey (i.e., X uniquely determines all attributes in the relation).

---

4. (a) Candidate Key for R(A,B,C,D,E)**

FDs:

• AB → C
• BD → A
• DC → E

Closure Calculation:

1. Start with BD → BD → A (from BD → A), so BD+ = ABD.

2. Add C via AB → C (ABD+ = ABCD).

3. Add E via DC → E (ABCD → ABCDE).
   Result: BD is a candidate key.

---

4. (b) Lossless Join Test**

Decomposition: R1(ABC), R2(ABDE).
Common Attributes: AB.
Check: AB → C (holds in R1) and AB → DE (AB → A, BD → A is redundant). Since AB is a key in R1 and R2, the decomposition is lossless.