Database Management System · Quiz 1 · May 2025

**1. Apply WHERE before grouping:** Amount>3000 retains East 5000, West 7000 and 4000, and North 3500. East amounts 3000 and 2000 are removed. **2. Group and apply HAVING:** The filtered sums are East 5000, West 11000 and North 3500. Only West exceeds 6000, so the output is (West,11000). **Answer: C — West — 11000.** A: **Incorrect:** East is incorrectly computed using a row that WHERE excludes. B: **Incorrect:** North’s sum fails HAVING. C: **Correct:** Only West has a filtered sum above 6000. D: **Incorrect:** East’s correct filtered sum is 5000 and fails HAVING.

**1. Perform the update:** Amit’s marks rise from 80 to 90 and Ravi’s from 70 to 80, because their original marks are below 85. Neha remains at 90. **2. Execute deletion and insertion:** The existing Neha row with roll_no 2 is deleted. A new row (4,Neha,95) is then inserted. **3. State the final contents:** (1,Amit,90), (3,Ravi,80), and (4,Neha,95). The later insertion is not subject to the earlier UPDATE or DELETE again. **Answer: A — A — Amit 90, Ravi 80, new Neha 95.** A: **Correct:** It shows both updates, removal of old Neha and insertion of new Neha. B: **Incorrect:** It fails to apply the marks update. C: **Incorrect:** It wrongly retains the old Neha row. D: **Incorrect:** It shows the original table and ignores all changes.

**1. Preserve result attributes and join links:** TRC 1 returns supplier ID and part name for a Mumbai supplier with a 5000 catalog entry linked to that part; DRC e repeats the part-ID and supplier-ID variables and returns the same pair. TRC 2 returns the ID of a Suspension part with a linked 5000 catalog row; DRC a does the same. **2. Match the remaining queries:** TRC 3 returns part ID and supplier name, with both catalog links, Suspension and price 5000; DRC f matches it. TRC 4 returns a Mumbai supplier’s ID with a linked 5000 catalog entry; DRC b enforces the supplier link. **3. Distinguish tempting alternatives:** g and h omit required catalog joins, c omits the supplier-to-catalog equality, and d leaves variables unquantified. Thus the mapping is 1-e,2-a,3-f,4-b. **Answer: B — 1-e, 2-a, 3-f, 4-b.** A: **Incorrect:** It uses d instead of the properly quantified a and c instead of the linked b. B: **Correct:** It preserves all four outputs, predicates and joins. C: **Incorrect:** g and h admit unrelated tuples because join equalities are missing. D: **Incorrect:** It combines missing-join and quantification errors.

**1. Read the correlated restriction:** For each employee E1, select E2 rows in the same department with less experience, then compute their maximum salary. **2. Read the comparison:** E1.Salary greater than that maximum means the employee earns more than all less-experienced colleagues in that department, provided a nonempty comparable set exists. **3. Handle SQL’s empty aggregate:** With no such colleague, MAX is NULL and the outer > comparison is unknown, excluding the employee. The query is not an unconditional mathematical “for all” over an empty set. **Answer: C — Names of employees who earn more than all less experienced colleagues in .** **Convention note:** The schema is named Employees but the query uses Employee. The interpretation assumes this naming mismatch is a typo; literally the referenced relation must exist for execution. A: **Incorrect:** It neither uses a less-than comparison nor selects the most experienced person. B: **Incorrect:** It compares to the less-experienced group, not the most experienced person. C: **Correct:** It is the intended interpretation of exceeding that group’s maximum. D: **Incorrect:** It reverses the greater-than test.

**1. Inspect the missing-enrollment test:** For a given student S and course C, the inner NOT EXISTS is true when no Enrollment row links that student to that course. **2. Negate the existence of a missing course:** The outer NOT EXISTS accepts only students for whom there is no available course lacking enrollment. This is enrollment in every available course. If Course is empty, every student qualifies by vacuous truth. **Answer: C — Names of students who have enrolled in all available courses.** A: **Incorrect:** At least one course is not enough when other courses remain unenrolled. B: **Incorrect:** The query has no exact-one condition. C: **Correct:** It expresses that every course has a matching enrollment. D: **Incorrect:** It is the opposite of the inner pattern for missing enrollment.

**1. Construct the required divisor:** Select Professor rows named Smith, join them to Teaches through ProfId, and project CourseId. This produces the set of courses taught by the named professor(s). **2. Apply division to compatible attributes:** The dividend must retain both StudentId and CourseId from Enrolled. Dividing by the required CourseId set yields StudentIds covering every required course. Join those IDs to Student and project Name. **3. Compare alternatives:** A follows these steps. B finds students with at least one matching course. C projects CourseId away before division, and D joins Professor to Course without the Teaches relationship. **Answer: A — A — division by Smith’s taught-course set.** **Convention note:** Under classical active-domain division, an empty divisor returns StudentIds present in the dividend, not necessarily every Student row. The usual nonempty course-set interpretation is intended. A: **Correct:** It divides the student-course pairs by the required course set and recovers names. B: **Incorrect:** It implements existential participation, not coverage of every course. C: **Incorrect:** The dividend has lost the CourseId needed for division. D: **Incorrect:** Professor-to-Course association must be established through Teaches.

**1. Identify independent uniqueness constraints:** EmpID identifies the employee; Email and PAN must each be unique separately. Name and Department must be allowed to repeat. **2. Use appropriate SQL constraints:** B assigns PRIMARY KEY to EmpID and individual UNIQUE constraints to Email and PAN, leaving Name and Department unrestricted. DISTINCT is a query-result modifier, not the column constraint used in A or C. A table also cannot have the three separate primary keys in D. **Answer: B — B — EmpID PRIMARY KEY; Email and PAN individually UNIQUE.** **Convention note:** If every employee must supply Email and PAN, add NOT NULL to those columns. UNIQUE alone ordinarily permits NULL values; B captures the intended uniqueness requirements for supplied values. A: **Incorrect:** DISTINCT is not the required column uniqueness constraint syntax. B: **Correct:** It declares the required primary and alternate uniqueness constraints. C: **Incorrect:** It incorrectly substitutes DISTINCT for the EmpID primary key. D: **Incorrect:** It attempts multiple independent primary keys.

**1. Apply AVG’s NULL rule:** Include prices 70,65,20,45 and 0, but omit the two NULLs. The sum is 200 and the number of included values is five. **2. Compute the average:** AVG(price)=200/5=40.00. Zero contributes to the count even though it contributes nothing to the sum. **Answer: C — 40.00.** A: **Incorrect:** It is not the sum divided by the five non-NULL observations. B: **Incorrect:** It uses an incorrect divisor or sum. C: **Correct:** It correctly averages the five known prices. D: **Incorrect:** It mishandles the zero or another supplied value.

**1. Identify the aggregated relationship:** The E1–R1–E2 aggregate is identified by the participating entity keys a1 and b1, since the diagram shows no arrow making either key sufficient alone. R1’s c1 is descriptive, not a key. **2. Map the higher relationship A1:** A1 connects the aggregate to E3, whose key is e1, and has its own attribute d1. Therefore its relation stores a1,b1,e1,d1; e2 and c1 remain in their own entity/relationship relations. **Answer: D — a1,b1,e1,d1.** A: **Incorrect:** c1 does not identify the aggregate, and a1,b1 are missing. B: **Incorrect:** It unnecessarily repeats c1 and nonkey E3 attribute e2. C: **Incorrect:** It unnecessarily includes E3’s nonkey attribute e2. D: **Correct:** It contains both aggregate keys, the E3 key and A1’s own attribute.

**1. Build two author-ID sets:** Join Book and Writes on bookid, filter Science Fiction and project authorid; repeat for Fantasy. **2. Require both kinds of authorship:** Intersect these author-ID sets. An author may satisfy the two conditions through different books, so the genre tests must not both be imposed on a single joined tuple. **Answer: A — A — intersection of the two author-ID sets.** A: **Correct:** The intersection requires membership in both author sets. B: **Incorrect:** It requires one scalar genre to equal two different strings simultaneously. C: **Incorrect:** Union accepts authors of either genre rather than both. D: **Incorrect:** Adding Author does not fix the contradictory same-row genre conjunction.

**1. Identify the parser/translator stage:** It checks the form and meaning of the SQL and translates it into an internal query representation such as a relational-algebra tree. **2. Separate later responsibilities:** Cost-based join reordering belongs to optimization, actual query execution to the evaluation engine, and transaction logs to recovery/transaction management. **Answer: B — Translate high-level queries into relational algebra.** A: **Incorrect:** This is an optimizer task. B: **Correct:** It is the intended parser/translator role. C: **Incorrect:** This is an execution-engine task. D: **Incorrect:** This is a recovery/log-management task.

**1. Read relationship constraints:** Works has no key-constraint arrows, so instructors and courses may relate many-to-many. Manages points toward instructors, so each course has at most one manager, while one instructor may manage multiple courses. **2. Read the weak entity:** Health is drawn with a double rectangle and is identified through its owning instructor. An ownerless health record cannot remain in this modeled weak entity set. **3. Count a minimal relational mapping:** Instructor, Course and Health require three tables; Works adds a link table. Manages can be represented by a manager foreign key in Course, and the identifying relationship is absorbed in Health. The minimum is four, not five. **Answer: A, B, C — A, B and C.** **Convention note:** The weak-entity dependency is conceptual; a concrete SQL implementation must enforce it with suitable deletion constraints or application actions. The diagram alone is not executable ON DELETE CASCADE SQL. A: **Correct:** Works is many-to-many in the diagram. B: **Correct:** The modeled weak Health record depends on its instructor owner. C: **Correct:** The arrow constrains courses to one manager, not managers to one course. D: **Incorrect:** A separate Manages table is not required in the minimal mapping; four tables suffice.