Database Management System · Quiz 1 · May 2026

**1. Separate the query-processing stages:** Parsing checks the statement and builds an internal representation; evaluation executes a chosen plan. **2. Identify plan selection:** The query optimizer estimates costs of alternative access paths, join orders and algorithms, then chooses an economical execution plan. **Answer: B — Query Optimizer.** A: **Incorrect:** It checks and translates the SQL rather than primarily choosing the cheapest physical plan. B: **Correct:** It is responsible for cost-based plan generation and selection. C: **Incorrect:** It runs the selected execution plan. D: **Incorrect:** It manages cached pages, not the overall query-plan search.

**1. Identify the values included by AVG:** SQL AVG ignores NULL but includes zero. The non-NULL fees are 70,65,20,45,0, totaling 200 across five entries. **2. Divide by the non-NULL count:** AVG(Fee)=200/5=40.00. The two NULL fees do not add zero-valued observations to the denominator. **Answer: C — 40.00.** A: **Incorrect:** It does not follow from the supplied sum and non-NULL count. B: **Incorrect:** It uses an incorrect total or denominator. C: **Correct:** It is 200 divided by five non-NULL values. D: **Incorrect:** It incorrectly omits or mishandles one or more included values.

**1. Execute the update on the initial rows:** IDs 102 and 103 have weights below 10, so their weights change from 5 to 10 and from 8 to 13. ID 101 remains at 15. **2. Execute the later statements in sequence:** DELETE removes delivered row 103. The subsequent INSERT creates a new delivered row 104 with weight 12; the earlier deletion is not rerun. **3. State the final table:** The rows are (101,In Transit,15), (102,Pending,10), and (104,Delivered,12). **Answer: A — A — rows 101/15, 102/10 and 104/12.** A: **Correct:** It reflects both the update and the later delete-then-insert sequence. B: **Incorrect:** It fails to update row 102. C: **Incorrect:** It retains row 103 despite the DELETE. D: **Incorrect:** It omits the new row 104 inserted after the DELETE.

**1. Interpret the correlated subquery:** For each pilot P1, the subquery restricts P2 to the same airline and strictly lower experience, then finds the maximum salary in that group. **2. Interpret the outer comparison:** P1.Salary greater than that maximum means P1 earns more than every less-experienced colleague in the same airline, when that comparison set is nonempty and salaries are non-NULL. **3. Handle the empty case:** If there are no such colleagues, MAX returns NULL and Salary>NULL is unknown, so the pilot is not selected. This is not exactly the same as a universally quantified >ALL condition on an empty set. **Answer: C — Names of pilots who earn more than all less experienced colleagues in their .** A: **Incorrect:** It neither selects the most-experienced colleague nor compares with a less-than sign. B: **Incorrect:** It compares to less-experienced colleagues, not specifically the most experienced person. C: **Correct:** It best describes the maximum-salary comparison over the less-experienced group. D: **Incorrect:** It reverses the salary inequality.

**1. Read the innermost test:** For a chosen pilot P and aircraft type A, the inner NOT EXISTS is true when the required Certified row is missing. **2. Read the outer NOT EXISTS:** It accepts P only if there is no available aircraft type for which a certification is missing. Therefore the pilot is certified for all aircraft types in Aircraft_Type. **3. Note the empty-universe behavior:** If no aircraft types exist, the outer test is true for every pilot, the standard vacuous truth behavior of the universal condition. **Answer: C — Names of pilots who are certified for all available aircraft types.** A: **Incorrect:** Having one certification is weaker than having no missing type. B: **Incorrect:** The query imposes no exact-one count. C: **Correct:** Double negation implements “for every available aircraft type.” D: **Incorrect:** It selects universal certification rather than an absence of certifications.

**1. Follow the relationship path:** Actor connects to Acts_In through ActorID; Acts_In connects to Movie through MovieID. Select Title=Inception and project the actor’s Name. **2. Compare the equivalent expressions:** A implements the natural joins, selection and projection. B explicitly quantifies linked Actor, Acts_In and Movie tuples. C uses the shared domain variables a and m to enforce the same links. **3. Reject the unrelated-ID comparison:** D compares ActorID directly with MovieID and omits Acts_In, so it does not establish that the actor appeared in that movie. **Answer: A, B, C — A, B and C.** A: **Correct:** It joins through both required identifiers before projecting names. B: **Correct:** Its tuple-variable equalities express the required actor-to-movie participation. C: **Correct:** Its repeated variables connect the three tuples through Acts_In. D: **Incorrect:** Coinciding actor and movie IDs do not imply an acting relationship.

**1. Translate the algebra:** \(\pi_A(\sigma_{C=10}(R\bowtie S))\) returns distinct A values for which an R tuple matches an S tuple on B with C=10. **2. Check duplicate elimination:** A uses GROUP BY A after the join, B uses DISTINCT with an IN subquery, and C uses DISTINCT with explicit join equality. Each yields the same set of A values. **3. Check D under SQL bag semantics:** D’s natural join and filter find the right matches, but SELECT A without DISTINCT can repeat A. For example, R={(a,1),(a,2)} and S={(1,10),(2,10)} produce a twice in D but once in the algebraic projection. **Answer: A, B, C — A, B and C.** A: **Correct:** GROUP BY A returns one row per selected A. B: **Correct:** The IN condition tests existence of a qualifying B and DISTINCT removes repeated A values. C: **Correct:** It explicitly performs the matching join, filter and duplicate-eliminating projection. D: **Incorrect:** It can retain duplicate A values, so it is not generally equivalent to set projection.

**1. Map entities and attributes:** Employee, Department, Project and weak Dependent require four tables. Composite Name becomes FName and LName columns in Employee. Multivalued Skill adds EmployeeSkill(Emp_ID,Skill), keyed by both columns. **2. Map relationships:** Works_For is many-to-one toward Department and can be represented by Dept_No in Employee. The identifying Has relationship is absorbed into Dependent’s owner key. Many-to-many Works_On, with Hours, needs one link table. **3. Count and identify the weak key:** The minimum is four entity tables plus Skill plus Works_On, or six. Dependent’s primary key must include Emp_ID together with its composite partial key (Dep_Name,DOB). **Answer: A, B — A and B.** A: **Correct:** A multivalued attribute requires a separate owner-value relation. B: **Correct:** The minimum mapping has six tables. C: **Incorrect:** The components of Name fit directly into Employee columns. D: **Incorrect:** The owner’s Emp_ID is missing from the proposed weak-entity key.

**1. Match all shared attributes:** The schemas share B and D, so a natural join requires equality on both. Matching combinations produce four output rows with unique columns A,B,C,D,E. **2. Compare operations and schemas:** B is the natural join. D selects matching B and D pairs from the product and projects away duplicate copies of those attributes. A keeps all 7×6=42 product pairs; C filters correctly but still retains both copies of B and D, giving seven columns rather than the displayed five. **3. Track the four matches:** They are (a1,b1,c2,d1,e1), (a2,b2,c4,d1,e3), (a2,b3,c1,d1,e5), and (a1,b1,c4,d3,e1). **Answer: B, D — B and D — intended natural-join result.** **Convention note:** The printed output’s fourth A value appears as a5, but the corresponding input r₁ row has a1 and no a5 occurs in r₁. B and D produce the listed result with that cell corrected to a1; literally no listed operation can invent a5. A: **Incorrect:** It returns the entire Cartesian product rather than only matching pairs. B: **Correct:** It matches both shared columns and merges their duplicate copies. C: **Incorrect:** It produces an equijoin with duplicated B and D columns, not the displayed five-column schema. D: **Correct:** It adds the projection needed to obtain the natural-join schema.

**1. Count supersets of each candidate key:** A superkey must contain AB or AC. There are \(2^{4-2}=4\) supersets of AB and four supersets of AC. **2. Subtract the overlap:** Sets containing both keys must contain ABC, giving \(2^{4-3}=2\) common supersets. Thus the total is \(4+4-2=6\). **3. Enumerate as a check:** The six are AB, AC, ABC, ABD, ACD and ABCD. This assumes the two stated keys are the complete candidate-key list. **Answer: 6.**

**1. Form the product of projections:** πA(R)={1,2,3} and πC(S)={5,6}, so their product has six (A,C) pairs. **2. Find the matching-join projection:** Joining on B produces (A,C)=(1,5) and (2,6), exactly two pairs. **3. Apply difference:** Removing those two from the six leaves (1,6),(2,5),(3,5),(3,6), giving four tuples. **Answer: C — 4.** A: **Incorrect:** It counts the join pairs rather than their complement in the product. B: **Incorrect:** It is not the result of removing two pairs from six. C: **Correct:** Six product pairs minus two join pairs leaves four. D: **Incorrect:** It ignores the set difference.

**1. Perform the left join first:** T1 values 1,2,3 produce intermediate (X,Y) rows (1,10),(1,11),(2,12),(3,NULL). The unmatched X=3 is initially retained. **2. Apply the subsequent inner join:** T3 has Y=10,12,13. Only intermediate rows (1,10) and (2,12) match. Y=11 has no partner, and NULL does not equal any T3.Y value. **3. Count final rows:** The SELECT returns two rows, with X values 1 and 2. The initial outer join does not preserve rows through a later rejecting inner join. **Answer: A — 2.** A: **Correct:** Exactly two rows survive both joins. B: **Incorrect:** It wrongly retains one of the rejected intermediate rows. C: **Incorrect:** It counts the left-join intermediate result before the inner join. D: **Incorrect:** It exceeds even that intermediate row count.

**1. Determine subtype overlap:** A person may be both Employee and AccountHolder, so the specializations overlap rather than being disjoint. **2. Determine coverage:** A person may belong to neither subtype, so participation in the specialization is partial rather than total. The combined constraint is overlapping and partial. **Answer: B — Overlapping and Partial.** A: **Incorrect:** Disjoint would forbid membership in both subtypes. B: **Correct:** It permits both shared membership and membership in neither subtype. C: **Incorrect:** Both the disjointness and total-coverage claims contradict the scenario. D: **Incorrect:** Total would require every Person to belong to at least one subtype.

**1. Bound the quotient size:** Each qualifying A needs a distinct R pair for both of S’s two B values. Ten R tuples can therefore support at most \(10/2=5\) A values, and five complete pairs of requirements attain that maximum. No A need meet both, so the minimum is zero. **2. Derive division by excluding counterexamples:** Start with \(\pi_A(R)\). The missing required pairs are \((\pi_A(R)\times S)-R\); projecting them onto A finds the failing candidates. Thus \[R\div S=\pi_A(R)-\pi_A((\pi_A(R)\times S)-R).\] **3. Add a new divisor requirement:** Adding a distinct B tuple to S can only remove A values or leave them unchanged. It cannot create a newly qualifying A. **Answer: A, C — A and C.** A: **Correct:** Five complete sets of two required pairs is the attainable maximum. B: **Incorrect:** The quotient may be empty, so one is not the minimum. C: **Correct:** It removes exactly those A values with a missing required B. D: **Incorrect:** Increasing the required B set cannot increase the quotient.

**1. Evaluate the count terms:** There are five employee rows. Salary is non-NULL for Alice, Bob and Charlie, so COUNT(Salary)=3. The distinct non-NULL salaries are 50000 and 60000, giving 2. **2. Evaluate the bonus term:** SUM ignores NULL but includes zero, giving 5000+0+10000=15000. Thus SUM(Bonus)/1000=15. **3. Combine:** COUNT(*)+COUNT(Salary)+COUNT(DISTINCT Salary)+SUM(Bonus)/1000=5+3+2+15=25. **Answer: 25.**