DBMS Assignment Solutions - BTCS501-18

Question 1: DBA Roles and Responsibilities

Key Responsibilities:

1. Database Design: Schema design, normalization, integrity constraints

2. Security Management: User authentication, access control, encryption

3. Performance Tuning: Query optimization, indexing, monitoring

4. Backup & Recovery: Regular backups, disaster recovery planning

5. Maintenance: Patches, updates, capacity planning

6. Support: Troubleshooting, user assistance, documentation

Question 2: Database Models Comparison

Model Structure Relationships Advantages Disadvantages

Fast access,
Hierarchical Tree 1:N Rigid, no M:N
simple

Flexible Complex
Network Graph M:N
relationships navigation

SQL, flexible,
Relational Tables All types Join overhead
standard

Object-
Objects All types OOP integration No standard
Oriented

Object- Tables +
All types Best of both Complex
Relational Objects

Question 3: University Database ER Design

Entities:

• STUDENT (PK: Student_Number, SSN) - Name, Address, Phone, DOB, Sex, Class

• DEPARTMENT (PK: Dept_Code, Name) - Office, Phone, College

• COURSE (PK: Course_Number) - Name, Description, Hours, Level

• SECTION (PK: Course#, Semester, Year, Section#) - Composite key

 • INSTRUCTOR (PK: Instructor_ID) - Name, Office

• GRADE_REPORT - Letter_Grade, Numeric_Grade

Relationships:

• MAJORS_IN (Student M:1 Department)

• MINORS_IN (Student M:1 Department) - Optional

• OFFERS (Department 1:M Course)

• HAS_SECTIONS (Course 1:M Section)

• TEACHES (Instructor 1:M Section)

• ENROLLED_IN (Student M:N Section) → GRADE_REPORT

Question 4: SQL Queries

i. Wipro employees and their cities:

SELECT W.Pname, L.City

FROM WORKS W JOIN LIVES L ON W.Pname = L.Pname

WHERE W.Cname = 'wipro';

ii. Infosys employees with salary > 50,000:

SELECT W.Pname, L.Street, L.City

FROM WORKS W JOIN LIVES L ON W.Pname = L.Pname

WHERE W.Cname = 'infosys' AND W.Salary > 50000;

iii. People living and working in same city:

SELECT DISTINCT W.Pname

FROM WORKS W JOIN LIVES L ON W.Pname = L.Pname

JOIN LOCATED_IN LC ON W.Cname = LC.Cname

WHERE L.City = LC.City;

iv. People not working for Infosys:

SELECT Pname FROM WORKS WHERE Cname != 'infosys';

v. Salary > all Oracle employees:

SELECT Pname FROM WORKS

WHERE Salary > (SELECT MAX(Salary) FROM WORKS WHERE Cname = 'Oracle');

vi. Companies in all Infosys cities:

SELECT DISTINCT LC1.Cname

FROM LOCATED_IN LC1

WHERE NOT EXISTS (

SELECT City FROM LOCATED_IN WHERE Cname = 'infosys'

EXCEPT

SELECT City FROM LOCATED_IN LC2 WHERE LC2.Cname = LC1.Cname

) AND LC1.Cname != 'infosys';

Question 5: Relational Algebra

a) Employees on COMP353:
π_Emp#(σ_{Pname='COMP353'}(PROJECT ⋈ ASSIGNED_TO))

b) Employee details on COMP353:

π_Emp#,Ename((σ_{Pname='COMP353'}(PROJECT)) ⋈
ASSIGNED_TO ⋈ EMPLOYEE)

c) Employees on database project:

π_Emp#,Ename((σ_{Pname='database'}(PROJECT)) ⋈
ASSIGNED_TO ⋈ EMPLOYEE)

d) Employees on both COMP353 AND COMP354: R1 ←

π_Emp#(σ_{Pname='COMP353'}(PROJECT ⋈ ASSIGNED_TO)) R2
← π_Emp#(σ_{Pname='COMP354'}(PROJECT ⋈ ASSIGNED_TO))
π_Emp#,Ename((R1 ∩ R2) ⋈ EMPLOYEE)

e) Employees working on all projects of employee 107: ASSIGNED_TO ÷

π_Project#(σ_Emp#=107(ASSIGNED_TO))

f) Employees NOT on COMP453: π_Emp#(EMPLOYEE) -

π_Emp#(σ_{Pname='COMP453'}(PROJECT ⋈ ASSIGNED_TO))

g) Employees on all projects: ASSIGNED_TO ÷ π_Project#(PROJECT)

Question 6: DBMS Comparison

Feature SQL Server DB2 MySQL Oracle

Vendor Microsoft IBM Oracle Oracle

Cost Medium-High High Free/Low Very High

Platform Windows, Linux Multi Multi Multi

Best For MS Stack Enterprise Web Apps Enterprise

Key Feature SSMS, T-SQL pureScale Open Source RAC, Security

Question 7: 3NF Normalization

Given FDs:

• JKL → MNP

• KM → Q

• KL → MNP

• KM → NP

• NK → P

Minimal Cover:

1. KL → M, N, P

2. KM → Q, N

3. NK → P

Candidate Key: JKL

3NF Tables:

1. R1(K, L, M, N, P) - Key: KL

2. R2(K, M, Q, N) - Key: KM

3. R3(N, K, P) - Key: NK

4. R4(J, K, L) - Key: JKL (preserves candidate key)

Question 8: CAR_SALE Normalization

CAR_SALE(Car#, Date_Sold, Salesman#, Commission%, Discount_Amount) PK:
(Car#, Salesman#) FDs: Date_Sold → Discount_Amount, Salesman# → Commission%

Current Form: 1NF only

• Not 2NF: Salesman# → Commission% (partial dependency)

• Not 3NF: Date_Sold → Discount_Amount (transitive dependency)

Normalized to 3NF:

1. CAR_SALE_TRANSACTION(Car#, Salesman#, Date_Sold) - PK: (Car#,

Salesman#)

2. SALESMAN(Salesman#, Commission%) - PK: Salesman#

3. DISCOUNT_SCHEDULE(Date_Sold, Discount_Amount) - PK: Date_Sold

Question 9: Query Tree

SQL:

SELECT P.Pnumber, P.Dnum, E.Lname, E.Address, E.Bdate

FROM PROJECT P, DEPARTMENT D, EMPLOYEE E

WHERE P.Dnum=D.dnumber AND D.Mgr_ssn=E.Ssn AND P.Plocation='Stafford';

Optimized Query Tree:

π(Pnumber, Dnum, Lname, Address, Bdate)

⋈(D.Mgr_ssn = E.Ssn)

/ \

⋈(P.Dnum=D.dnumber) EMPLOYEE

/ \

σ(Plocation= DEPARTMENT

'Stafford')

PROJECT

Optimizations Applied:
 1. Push selection down (filter PROJECT first)

2. Join smaller result sets first

3. Reduce intermediate result sizes

Question 10: Query Trees

i) Research department employees:

SELECT E.Fname, E.Lname, E.Address

FROM EMPLOYEE E JOIN DEPARTMENT D ON E.Dno = D.Dnumber

WHERE D.Dname = 'Research';

ii) Employee and supervisor names:

SELECT E.Fname, E.Lname, S.Fname AS Sup_Fname, S.Lname AS Sup_Lname

FROM EMPLOYEE E LEFT JOIN EMPLOYEE S ON E.Super_ssn = S.Ssn;

iii) Projects involving SMITH:

SELECT DISTINCT P.Pnumber

FROM PROJECT P

WHERE P.Pnumber IN (

SELECT W.Pno FROM WORKS_ON W

JOIN EMPLOYEE E ON W.Essn = E.Ssn

WHERE E.Lname = 'SMITH'

OR P.Dnum IN (

SELECT D.Dnumber FROM DEPARTMENT D

JOIN EMPLOYEE E ON D.Mgr_ssn = E.Ssn

WHERE E.Lname = 'SMITH'

);

iv) Project statistics for dept 5:

SELECT P.Pnumber, P.Pname, COUNT(*) AS Emp_Count

FROM PROJECT P JOIN WORKS_ON W ON P.Pnumber = W.Pno

JOIN EMPLOYEE E ON W.Essn = E.Ssn

WHERE E.Dno = 5

GROUP BY P.Pnumber, P.Pname;

Question 11: Query Plan & Cost

Query: Find students age 12-20 who checked out books by 'Olden Fames'

Physical Query Plan (No Indexes):

1. Nested Loop Join: Student × Checkout (filter age 12-20)

2. Nested Loop Join: Result × Book (filter author)

3. Project S.name

Cost Calculation:

• Student scan: 1,000 pages

• For each student matching age (assume 7,200 students): Checkout scan = 7,200
× 15,000 = 108M

• For each match: Book scan = matches × 5,000

• Total: ~108M+ page reads (extremely expensive)

Better Plan with Indexes:

• Index on Book.author → 100 books

• Index on Checkout.bid → direct lookups

• Index on Student.age → 7,200 students

• Estimated: ~25,000 page reads

Question 12: Conflict Serializability

Test Method: Build precedence graph, check for cycles

a) r1(X);r3(X);w1(X);r2(X);w3(X);

• T1→T3 (r1(X) before w3(X))

• T1→T2 (w1(X) before r2(X))

• Serializable: T1→T2, T1→T3 (no cycle)

b) r1(X);r3(X);w3(X);w1(X);r2(X);

• T3→T1 (w3(X) before w1(X))

• T1→T2 (w1(X) before r2(X))

• Serializable: T3→T1→T2 (no cycle)

c) r3(X);r2(X);w3(X);r1(X);w1(X);

• T3→T1 (w3(X) before r1(X))

• T2→T1 (r2(X) before w1(X))

• Serializable: T2→T3→T1 or T3→T2→T1 (no cycle)

d) r3(X);r2(X);r1(X);w3(X);w1(X);

• T3→T1 (w3(X) before w1(X))

• T2→T3 (r2(X) before w3(X))

• T1→T3 (r1(X) before w3(X)) - CONFLICT

• Not Serializable: Cycle exists

Question 13: Railway Reservation Concurrency

Concurrency Problems:

1. Lost updates (double booking)

2. Dirty reads (cancelled reservations)

3. Phantom reads (seat availability)

Solutions:

• Locking: Two-phase locking (2PL)

o Exclusive locks on seat updates

o Shared locks on reads

• Isolation Levels: SERIALIZABLE for bookings

• Optimistic Concurrency: Version numbers on seats

• Pessimistic Locking: Lock seats during booking process

Deadlock Prevention:

1. Lock Ordering: Always lock in train→seat order

 2. Timeouts: Release locks after timeout

3. Deadlock Detection: Wait-for graph monitoring

4. Wait-Die/Wound-Wait: Timestamp-based schemes

Question 14: Airline Database Design

Entities:

1. CUSTOMER (Customer_ID, Name, Phone, Email, Address)

2. FLIGHT (Flight_ID, Flight_Number, Origin, Destination, Departure_Time,

Arrival_Time, Aircraft_Type)

3. RESERVATION (Reservation_ID, Booking_Date, Status, Total_Cost)

4. SEAT (Seat_ID, Seat_Number, Class, Price)

5. AIRCRAFT (Aircraft_ID, Model, Total_Seats, Manufacturer)

6. ROUTE (Route_ID, Origin, Destination, Distance)

Relationships:

• CUSTOMER makes RESERVATION (1:M)

• RESERVATION for FLIGHT (M:1)

• RESERVATION assigns SEAT (M:M)

• FLIGHT uses AIRCRAFT (M:1)

• FLIGHT follows ROUTE (M:1)

Schema:

CUSTOMER(Customer_ID PK, Name, Phone, Email, Address)

FLIGHT(Flight_ID PK, Flight_Number, Aircraft_ID FK, Route_ID FK,

Departure_Time, Arrival_Time, Status)

RESERVATION(Reservation_ID PK, Customer_ID FK, Flight_ID FK,

Booking_Date, Status, Total_Cost)

SEAT_ASSIGNMENT(Reservation_ID FK, Seat_ID FK, Flight_ID FK)

SEAT(Seat_ID PK, Aircraft_ID FK, Seat_Number, Class, Is_Available)

AIRCRAFT(Aircraft_ID PK, Model, Total_Seats)

ROUTE(Route_ID PK, Origin, Destination, Distance)

Constraints:

• Customer_ID, Flight_ID, Reservation_ID are PRIMARY KEYS

• Foreign Keys enforce referential integrity

• Seat_Number unique per Aircraft

• Status CHECK ('Confirmed', 'Cancelled', 'Pending')

• Is_Available BOOLEAN for seat status