DBMS Week 3 Graded Assignment Solutions

💾 Database Management Systems Week 3 Graded Assignment Solutions

Version: v1_t12025 —
Complete step-by-step SQL analysis and solutions covering Subqueries, Set Operators, Triggers, and Referential Integrity.

This downloadable PDF bundle provides complete, detailed solutions for the third week of the Database Management Systems course. The assignment focuses on writing and analyzing complex SQL queries involving nested subqueries, advanced comparison logic, and database constraint behavior.

Core Assignment Set (10 Questions)

The solutions cover advanced query structure, relational algebra, and database constraints:

• Q1 & Q2 (Hall Booking Schema): Construct nested SQL queries to find departments that booked in January but not February (using NOT IN), or booked the same hall in both months (using INTERSECT).
• Q3 (Country Capital Schema): Use UNION within a subquery to find capitals of countries in Asia or Europe.
• Q4 & Q5 (Employee Schema):
  • Q4: Find employees whose salary exceeds every employee in ‘Sales’ using > ALL.
  • Q5: Identify the designation with the lowest salary in ‘Purchase’ using MIN(salary).
• Q6 (NAT): Predict a missing value in a table so that a filtering query returns a specific employee name.
• Q7: Analyze ON DELETE CASCADE behavior in a self-referential table tracking “multiple of” relationships.
• Q8 (NAT): Estimate the maximum row count from a NATURAL JOIN between employee and designation.
• Q9 (MCQ): Predict the result set from a multi-table query using JOIN, WHERE, IN, NOT IN, and ORDER BY.
• Q10: Evaluate a PostgreSQL trigger function (bonus_fun) that conditionally updates ebonus for new employees in the ‘R/D’ department.

File Format:

• ☑ v1_t12025: Complete solution set (PDF).