Database Management System · Quiz 1 · Jan 2026

**1. Identify what a schema contains:** A schema specifies tables, column types, keys and other structural constraints. **2. Match the language category:** CREATE, ALTER and DROP define or change this structure and belong to Data Definition Language (DDL). Data operations and access-control operations have different roles. **Answer: A — Data Definition Language.** A: **Correct:** DDL defines database objects and constraints. B: **Incorrect:** DML retrieves or changes rows rather than defining the schema. C: **Incorrect:** DCL grants or revokes access privileges. D: **Incorrect:** Only DDL is the category specifically responsible for schema definition.

**1. Establish the joins and filters:** Connect employee.emp_id to department.emp_id, then department.d_id to project.d_id. Keep departments named IT and projects whose status is pending, then output the employee name. **2. Compare the formalisms:** A uses natural joins on those shared identifiers, selection and name projection. B writes the same equalities and filters in tuple relational calculus. C reuses the employee identifier i and department identifier \(d_i\) across its domain tuples, which enforces the same joins. **3. Reject the unlinked product:** D takes a Cartesian product and filters only department name and project status. Without identifier equalities, it can attach unrelated employees to matching departments/projects. **Answer: A, B, C — A, B and C.** A: **Correct:** Its natural joins link the shared identifiers before filtering. B: **Correct:** It explicitly enforces both identifier matches and both restrictions. C: **Correct:** Its shared domain variables link the three relations and select the desired values. D: **Incorrect:** The Cartesian product has no employee-to-department or department-to-project join condition.

**1. Evaluate the first two branches:** The LIKE pattern %__a% requires an “a” preceded by at least two characters. Combined with age >25, it selects Karan, Rama, Sita, Jaya and Tara. The Delhi branch adds Naman, Uma, Jaya and Alok. **2. Apply set-operation order:** UNION ALL and EXCEPT associate left to right here. The final EXCEPT removes the under-25 rows, including Naman and Uma. EXCEPT without ALL also removes duplicate result tuples, so Jaya appears once. **3. State the final rows:** The six (name,city) pairs are Karan–Mumbai, Rama–Chennai, Sita–Mumbai, Jaya–Delhi, Tara–Mumbai and Alok–Delhi. Row order is not guaranteed without ORDER BY. **Answer: B — B — Karan, Sita, Jaya, Tara, Rama and Alok.** A: **Incorrect:** It incorrectly omits Sita and Alok. B: **Correct:** It contains precisely the six surviving pairs. C: **Incorrect:** It retains under-25 Naman and omits valid Rama and Alok. D: **Incorrect:** It retains Naman and omits Sita, Rama and Alok.

**1. Enforce the insert constraint:** The CHECK requires price≥50, so Toffee at 30 is rejected. The other five inserts succeed, assuming execution continues after that individual failure. **2. Apply every update condition:** The discounted price must be strictly greater than 60, and the name must match %_e%e%. Cheese has two eligible e characters and 90×0.8=72, so it updates. Paneer matches the pattern but 75×0.8=60 fails the strict comparison. Ghee would give 48 and fails it too. Butter has only one e and fails the pattern. **3. List final prices:** Dal 100, Ghee 60, Butter 80, Paneer 75 and Cheese 72 remain. **Answer: C — C — five rows; only Cheese changes to 72.** **Convention note:** The paper assumes independent statements or autocommit-style continuation after the failed insert. In a PostgreSQL transaction that aborts on the error, later statements require rollback or savepoint recovery. A: **Incorrect:** It wrongly retains the rejected Toffee and changes ineligible rows. B: **Incorrect:** It incorrectly discounts Paneer even though the test requires greater than 60. C: **Correct:** It leaves the first four prices unchanged and updates only Cheese. D: **Incorrect:** It changes Butter and Paneer despite the update predicates, and 48 would violate CHECK.

**1. Aggregate before testing the average:** Join Student with Marks by student_id, group by student ID and name, and compute AVG(marks) over all of each student’s courses. **2. Evaluate the supplied students:** Rahul averages (75+85)/2=80, Sneha averages (58+62)/2=60, and Amit averages 40. Strictly greater than 60 selects only Rahul. A’s CTE computes those averages and filters them in its outer query. **3. Identify the other mistakes:** Filtering individual marks before averaging changes the question. Aggregating without grouping does not produce a per-student result, and an aggregate cannot be used directly in the same query block’s WHERE condition. **Answer: A — A — the grouped CTE with outer average filter.** A: **Correct:** It groups correctly and filters the computed per-student average. B: **Incorrect:** It discards marks≤60 before averaging and also does not form a properly terminated CREATE VIEW followed by SELECT. C: **Incorrect:** It omits GROUP BY while selecting per-student nonaggregated columns. D: **Incorrect:** It incorrectly uses AVG in WHERE and omits the required grouping.

**1. Separate row counting from non-NULL counting:** COUNT(*) counts all eight rows. Department is NULL once, amount is NULL twice, and expense_name is NULL twice. Hence their counts are 7, 6 and 6. **2. Count distinct non-NULL departments:** Sales, HR, IT and Marketing give four values; NULL is excluded. The mapping is a=8, b=7, c=6, d=4, e=6. **Answer: D — a-8, b-7, c-6, d-4, e-6.** A: **Incorrect:** It counts NULL values as ordinary entries in b and c and as a distinct department in d. B: **Incorrect:** It overcounts amount and expense_name. C: **Incorrect:** It overcounts non-NULL columns and distinct departments. D: **Correct:** It matches all five counts.

**1. Apply the price restriction:** All flights except F9 and F10 have price>5000, so eight rows survive selection. **2. Project and remove duplicate tuples:** The distinct (source,destination,airline) values are Delhi–Mumbai–Indigo, Delhi–Mumbai–AirIndia, Delhi–Chennai–Indigo, Mumbai–Delhi–Indigo and Mumbai–Delhi–AirIndia. **3. Count the result:** Classical relational-algebra projection uses set semantics, so repeated flight IDs do not create repeated projected tuples. The result has five tuples. **Answer: 5.**

**1. Find the deleted parent rows:** The predicate industry=Hindi selects directors 2 (Hirani) and 4 (Anurag Kashyap). **2. Apply the cascade:** Movie.director_id has ON DELETE CASCADE, so movies 202 and 206 referencing director 2 and movie 205 referencing director 4 are deleted automatically. **3. Identify survivors:** Movies 201 and 203 reference director 1, and movies 204 and 207 reference director 3. These four remain. **Answer: B — Only movies 201 (Baahubali), 203 (RRR), 204 (Roja), and 207 (Bombay) remain..** A: **Incorrect:** Directors 1 and 3 and their films are unaffected. B: **Correct:** It lists exactly movies 201,203,204,207. C: **Incorrect:** These are among the deleted films, not the survivors. D: **Incorrect:** The declared cascade handles the dependent rows instead of rejecting this deletion.

**1. Express the two monthly requirements separately:** Build the department-ID set for bookings of H0001 or H0002 in January, and the analogous set for February. A department must belong to both sets; it may choose a different permitted hall in each month. **2. Intersect, then retrieve names:** C uses INTERSECT between those two properly restricted sets, then selects deptName from deptsMaster for the common IDs. **3. Reject weakened or malformed alternatives:** An IN list containing Jan and Feb means either month, not both. OR also weakens the conditions, and D places a bare SELECT after AND rather than a valid Boolean subquery condition. **Answer: C — C — intersect the January and February department sets.** A: **Incorrect:** It accepts a booking in either month, so it does not require both. B: **Incorrect:** Its OR and UNION admit departments failing one or more requirements. C: **Correct:** It enforces a qualifying booking in each month through intersection. D: **Incorrect:** It is not valid SQL as printed and does not form the two correctly restricted sets.

**1. Interpret division:** R÷S returns A values that appear in R with every B value in S. Here S requires both x and y. **2. Inspect each A:** A=1 has x and y, so qualifies. A=2 has only x, so fails. A=3 has x, y and z, so qualifies; the extra z does not disqualify it. The result is {1,3}. **Answer: A — A values {1,3}.** A: **Correct:** It includes exactly 1 and 3. B: **Incorrect:** It unnecessarily excludes 3 despite both required values being present. C: **Incorrect:** It incorrectly includes 2, which lacks y. D: **Incorrect:** It includes the failing 2 and excludes the qualifying 3.

**1. Map the basic entities:** User, Group and Permission require three tables. User’s multivalued Contact_num requires a separate UserContact(user_id,contact_num) table, adding one. **2. Map the relationships:** user_permissions and group_permissions are many-to-many and each requires a link table, adding two. The arrow from user_groups toward User means many Groups to one User; that relationship can be represented by a user_id foreign key in Group. Group’s total participation makes that foreign key mandatory. **3. Total the minimum:** 3 entity tables + 1 multivalued-attribute table + 2 many-to-many link tables = 6. A separate user_groups table is not needed in the minimal mapping. **Answer: 6.**

**1. Recall weak-entity identification:** A weak entity depends on an owner and cannot be uniquely identified by its own attributes alone. Its relational identifier combines the owner’s key with its partial key/discriminator. **2. Check participation and notation:** It participates totally in the identifying relationship. In standard Chen notation, the discriminator has a dashed underline. Therefore B and D are true descriptions, and the question asks instead for the false ones, A and C. **Answer: A, C — A and C — the statements that are not true.** A: **Correct:** Selected because its claim of an independent own primary key is false in the weak-entity sense. B: **Incorrect:** Not selected: total participation in the identifying relationship is required. C: **Correct:** Selected because denying the need for identification through an owner contradicts the standard weak-entity model. D: **Incorrect:** Not selected: dashed underlining is the conventional partial-key notation.

**1. Compute department-average ratings:** IT=(4.5+3.8+4.9)/3=4.4; HR=(4.2+3.5)/2=3.85; Sales=(4.8+4.0+3.2)/3=4.0. **2. Apply the correlated comparison:** Above-average employees are Ram and Frank in IT, Thomas in HR, and Suresh in Sales. Ramesh equals his average and fails the strict > test. **3. Evaluate COUNT(*):** Four employees qualify, so the returned scalar count is 4. The aggregate query itself emits one result row containing that count. **Answer: 4.** **Convention note:** The question says “how many rows will the query return,” but its key asks for the count value 4. Literally this aggregate query without GROUP BY returns one row; four is the number of qualifying employee rows.

**1. Compare department means with the overall mean:** The ratings total 32.9 across eight employees, so the overall mean is 4.1125. Department means are IT 4.4, HR 3.85 and Sales 4.0. Only IT passes the HAVING comparison. **2. Select the department project maximum:** Within IT, project counts are Ram 5, Vikram 3 and Frank 7. Only Frank equals the maximum 7. Both query conditions therefore select Frank. **Answer: B — Frank.** A: **Incorrect:** Ram is in the qualifying department but does not have its maximum project count. B: **Correct:** Frank is the maximum-project employee in IT, the only qualifying department. C: **Incorrect:** Sales has mean rating below the overall mean. D: **Incorrect:** HR also has mean rating below the overall mean.