Database Management System · Quiz 2 · May 2026

**1. Interpret the correlated count:** For each student S, the subquery counts rows T whose Total_Marks are strictly greater than S.Total_Marks. With distinct marks, that count is the student’s descending rank minus one. **2. Apply the threshold:** The outer condition is count<3, allowing counts 0,1,2. These correspond to the highest, second-highest and third-highest marks. A fourth-ranked student has three higher-scoring students and is excluded. **3. State the result accurately:** The query returns the names of the top three students by marks, or all students if fewer than three exist. Without ORDER BY, it does not promise the display order of those names. **Answer: C — names of the students with the first three highest marks.** A: **Incorrect:** The fourth-ranked student has count=3 and fails the strict inequality. B: **Incorrect:** The condition selects three ranks, not just rank 4. C: **Correct:** Counts 0,1,2 select the first three descending ranks. D: **Incorrect:** Rank 3 is included, but ranks 1 and 2 are included too.

**1. Compute candidate keys:** The FDs are \(A\to BC,CD\to E,B\to D,E\to A\). A and E each determine all five attributes. BC determines D, then E and A; CD determines E, then A and B. The minimal keys are A,E,BC,CD. **2. Apply the 3NF test:** Every attribute is prime: A and E occur as singleton keys, and B,C,D occur in BC or CD. Therefore each nontrivial FD has a prime right-hand attribute, so R satisfies 3NF. **3. Test BCNF separately:** \(B^+=\{B,D\}\), so B is not a superkey. The nontrivial dependency B → D violates BCNF, even though its dependent D is prime. Hence the highest normal form is 3NF. **Answer: C — 3NF.** A: **Incorrect:** R satisfies the stronger 3NF conditions, so 1NF is not the highest. B: **Incorrect:** R also satisfies 3NF, making 2NF too low. C: **Correct:** All attributes are prime, while B → D prevents BCNF. D: **Incorrect:** B → D has a determinant that is not a superkey.

**1. Prove D2 is lossless:** First split R into ABC and ACDEF. Their intersection is AC, and \(AC\to B\), so AC determines ABC and the split is lossless. **2. Finish the D2 decomposition:** Split ACDEF into ADF and ACDE. The intersection AD determines E by AD → E and then F by E → F. Thus AD determines ADF, making this split lossless too. The final components are exactly ABC,ADF,ACDE. **3. Construct a counterexample for D1:** Consider two legal tuples \((a_1,b,c_1,d,e,f)\) and \((a_2,b,c_2,d,e,f)\), where the A values and C values differ. They satisfy AB → C, AC → B, BC → A, B → D, AD → E and E → F. **4. Show D1’s spurious recombination:** In the projections AB,BC,EF,ABDE, the common B permits a1 from one row to join with c2 from the other. The new tuple \((a_1,b,c_2,d,e,f)\) was absent originally, so D1 is lossy. Thus only D2 is lossless. **Answer: B — D2 is lossless but D1 is not.** A: **Incorrect:** D1 admits the displayed spurious A–C recombination; D2 is lossless. B: **Correct:** D2 passes both intersection tests, while the counterexample disproves D1. C: **Incorrect:** The D1 counterexample rules out both being lossless. D: **Incorrect:** D2 has a lossless binary-decomposition construction.

**1. Start with the known key:** studID determines all four attributes, so any set containing studID is a superkey. The question supplies no additional key dependencies. **2. Choose additional attributes freely:** Each of Sname, Age and Sex can independently be included or omitted. Therefore the superkeys containing studID number \(2^3=8\). **3. Enumerate for verification:** They are {studID}, {studID,Sname}, {studID,Age}, {studID,Sex}, {studID,Sname,Age}, {studID,Sname,Sex}, {studID,Age,Sex} and the set of all four attributes. **Answer: 8.** **Convention note:** The intended count assumes no other candidate key is implied beyond the stated studID key. Merely declaring a primary key does not logically rule out other independently specified unique determinants.

**1. Identify the membership key:** StudentID determines StudentName; ClubID determines ClubName and MentorID; MentorID determines MentorName; and (StudentID,ClubID) determines FeePaid. The pair StudentID,ClubID therefore determines every attribute and is the candidate key. **2. Test whether either part is extraneous:** StudentID alone cannot determine ClubID or FeePaid. ClubID alone cannot determine StudentID or FeePaid. Since no other FDs hold, neither component can be removed from the FeePaid determinant. **3. Trace the transitive dependency:** The candidate key determines ClubID, which determines MentorID, which determines MentorName. Thus MentorName is transitively dependent on the key via nonkey attributes. **4. Explain the update anomaly:** ClubName is repeated for every membership in a club. A club-name change must be made consistently in all such rows, or inconsistent names for the same ClubID can appear. Having some key alongside each value does not eliminate this redundancy. **Answer: A, C, D — neither key part is extraneous; MentorName is transitively dependent; ClubName repetition permits update anomalies.** A: **Correct:** Neither singleton determinant’s closure includes FeePaid, so both components are necessary. B: **Incorrect:** ClubID does not determine the student or that student’s membership fee. C: **Correct:** The chain key → ClubID → MentorID → MentorName establishes transitive dependence. D: **Correct:** A club name repeated across memberships must be updated consistently in several rows. E: **Incorrect:** Partial and transitive dependencies still create redundancy despite the presence of key values.

**1. Distinguish seek from rotation:** Seek time moves the read-write head to the required track. The prompt says that movement is already complete, so no further seek is needed for this sector. **2. Identify the remaining wait:** The disk must rotate until the desired sector is under the head. This remaining angular wait is rotational latency. It is the appropriate fraction of a revolution, not necessarily a full revolution. **Answer: A, B — remaining wait is rotational latency, with no further seek required.** A: **Correct:** The desired sector is on the correct track but not yet under the head, which defines rotational latency. B: **Correct:** The head is already on the required track, so seek for this access is complete. C: **Incorrect:** Moving to another track would leave the required track and is unnecessary. D: **Incorrect:** Only the remaining angular fraction is needed; a nearly full rotation is a worst case, not every access.

**1. Apply A →→ B to the original pair:** For fixed A, B can recombine independently of the complementary block CDE. From (0,1,2,3,4) and (0,5,6,7,8), this forces \(t_3=(0,5,2,3,4)\) and \(t_4=(0,1,6,7,8)\). Thus option B is required. **2. Apply B →→ D within the B=1 group:** The first original tuple and t4 share B=1. Swap only D, keeping the complementary ACE block together. This forces \((0,1,2,7,4)\), which is option A, as well as (0,1,6,3,8). **3. Apply B →→ D within the B=5 group:** The second original tuple and t3 share B=5. Swapping D forces \((0,5,2,7,4)\), which is option D, and (0,5,6,3,8). **4. Explain why C is not forced:** Throughout these recombinations, C and E remain paired as (2,4) or (6,8). The proposed tuple (0,1,6,7,4) breaks that pairing. A relation containing all combinations of B∈{1,5}, D∈{3,7} and those two CE pairs satisfies both MVDs without containing option C. **Answer: A, B, D — (0,1,2,7,4), (0,5,2,3,4), and (0,5,2,7,4).** A: **Correct:** It follows by swapping D between the two B=1 tuples. B: **Correct:** It follows directly by recombining B under A →→ B. C: **Incorrect:** Neither MVD separates C from E; this proposed CE pair need not occur. D: **Correct:** It follows by swapping D in the B=5 group.

**1. Maintain all ancestor bounds:** Along a BST search path, moving left imposes an upper bound and moving right imposes a lower bound. Every later visited key must respect all earlier bounds, not only the immediately preceding comparison. **2. Validate A and D:** In A, 11→66 gives lower bound 11; 66→32 gives upper bound 66; 32→49 raises the lower bound to 32; 49→38 lowers the upper bound to 49. The final 38 is valid. In D, 61→52→14 imposes upper bound 52, then 14→17→43 raises the lower bound while staying below 52. **3. Reject B and C at the first contradiction:** In B, 60→50 enters 60’s left subtree, so later 70 is impossible. In C, 30→69 enters 30’s right subtree, so later 21 is impossible. **Answer: A, D — 11,66,32,49,38 and 61,52,14,17,43.** A: **Correct:** All keys satisfy the accumulated bounds; 38 can be the search target. B: **Incorrect:** After entering the left subtree of 60, a later node cannot be 70. C: **Incorrect:** After entering the right subtree of 30, a later node cannot be 21. D: **Correct:** All keys satisfy the ancestor bounds, with 43 a valid target.

**1. Use the schema dependencies:** For \(R(A,B,C)\) with \(A\to B,B\to C\), A determines all attributes and is the unique candidate key under the stated FD set. **2. Test normal forms:** The singleton key A has no proper nonempty subset, so there is no partial-key dependency and R satisfies 2NF. But B → C has a non-superkey determinant and a nonprime dependent, violating 3NF. The highest normal form is therefore 2NF. **3. Separate data coincidences from constraints:** Both instances satisfy the same schema FDs. Distinct values in one current instance do not establish additional semantic FDs for every legal instance, and repeated values alone do not violate a normal form. **Answer: B — the highest normal form is 2NF for both instances.** A: **Incorrect:** Repeated values alone do not change the schema’s dependency structure or normal form. B: **Correct:** Under the supplied FDs, both instances use the same schema whose highest normal form is 2NF. C: **Incorrect:** Accidental uniqueness in one instance does not make every determinant a schema-level key. D: **Incorrect:** Repetition does not itself create a partial dependency or break 2NF.

**1. Identify the result’s outer structure:** fetchmany(size) retrieves a batch of remaining rows from the cursor’s current result set. With an ordinary psycopg2 cursor, each row is represented as a tuple and the batch is a list. **2. Distinguish related methods:** fetchone() returns a single tuple or None. fetchmany() returns a list of up to the requested number of tuples, uses cursor.arraysize when size is omitted, and returns an empty list when no rows remain. **Answer: B — a list of tuples.** A: **Incorrect:** A standard fetchmany call does not return one dictionary. B: **Correct:** The default cursor returns a list containing row tuples. C: **Incorrect:** A single row tuple is the usual fetchone result, not fetchmany’s batch result. D: **Incorrect:** Dictionary-like rows require a specially configured cursor factory; they are not the default assumed here.

**1. Define the two required sets:** Find CS instructors who taught in Fall 2024 and, separately, CS instructors who taught in Spring 2025. “Both” requires membership in the intersection of these sets. **2. Use the query that intersects them:** Option C filters each semester-year pair in its own SELECT and combines the results with INTERSECT. SQL INTERSECT removes duplicates, so no additional DISTINCT is necessary. **3. Reject row-level conjunction and union:** A teaching row cannot simultaneously have semester Fall and Spring, or year 2024 and 2025. Conversely, UNION and a broad IN predicate accept an instructor who taught in only one required term, so they implement “either,” not “both.” **Answer: C — the INTERSECT query.** **Convention note:** This follows the question’s projection onto distinct names. If different instructors can share a name, identifying the same instructor in both terms should intersect instructor IDs first and then select names; a name-only intersection can otherwise combine two namesakes. A: **Incorrect:** The independent IN filters admit either semester in either year and do not require both target terms. B: **Incorrect:** UNION returns instructors appearing in either term rather than requiring both. C: **Correct:** INTERSECT retains only names present in both filtered result sets and removes duplicates. D: **Incorrect:** The conjunction asks the same teaching row to have two different semester/year values and cannot match.

**1. Read the relationship constraints from the diagram:** Published By has arrows toward both Book and Publisher and total participation on both sides, making it a mandatory one-to-one relationship. Book and Publisher can therefore be merged without adding missing-value placeholders; Royalty Percentage goes into that merged relation. **2. Handle the other structures:** Edition is a weak entity on the many side of its identifying relationship with Publisher, so it remains a separate relation carrying its owner reference. Languages is a multivalued attribute of Written By and needs a separate relation to store individual language values. **3. Map the self-reference without a mandatory extra table:** Recommends has a one-side arrow toward Author; its many-side author rows can hold the reference to the recommending/recommended author according to the two roles. A one-to-many self-reference does not inherently require a separate relationship table. **4. Count the intended minimal relations:** The paper’s minimal mapping has four relations: the merged Book–Publisher relation, Author, Edition, and the Written By/Language association relation. Published By does not need its own table, and Publisher cannot be merged with its multiple Edition rows as a single entity record. **Answer: D — the intended minimal model has 4 tables.** **Convention note:** This uses the diagram’s intended textbook mapping, treating the Written By multivalued-language relation as the association storage and Recommends as a self-reference. The figure does not specify Edition’s discriminator or the semantics of language-less associations; a fully executable production schema would need those details. A: **Incorrect:** A separate Published By table is unnecessary because the mandatory one-to-one entities can be merged with Royalty Percentage. B: **Incorrect:** A one-to-many self-reference can be represented by a self-referencing foreign key; it does not inherently require a fifth table. C: **Incorrect:** A publisher can have multiple editions, so a flat merger would repeat publisher data or require a different representation. D: **Correct:** The intended minimum after the mandatory one-to-one merge is four relations.

**1. Identify the key and violating dependencies:** The schema is ABCDEFGHIJ, with \(AB\to C,D\to IJ,A\to DE,F\to GH\). A,B,F do not appear on any right-hand side, and ABF determines every attribute, so ABF is the candidate key. **2. Construct a lossless BCNF decomposition:** Use the five relations \(R_1(D,I,J),R_2(A,D,E),R_3(F,G,H),R_4(A,B,C),R_5(A,B,F)\). One lossless split sequence separates DIJ on determinant D, then ADE on A, then FGH on F, then ABC on AB, leaving the key relation ABF. **3. Verify BCNF and preservation:** D is a key of DIJ, A of ADE, F of FGH and AB of ABC. ABF has no nontrivial projected dependencies. Hence every component is in BCNF, and all four original dependencies are enforceable locally. **4. Count the components:** The standard complete, dependency-preserving BCNF decomposition has five subtables, so C is the intended answer. It also disproves either claim that preserving the FDs is impossible. **Answer: C — 5 subtables in the standard complete BCNF decomposition.** **Convention note:** The count refers to the natural decomposition shown here. Decompositions are not unique in general, and one can add redundant projection tables without changing correctness; “5” is not a claim that every conceivable schema representation must have exactly five relations. A: **Incorrect:** The displayed BCNF decomposition preserves all original FDs. B: **Incorrect:** A dependency-preserving BCNF decomposition also satisfies 3NF, so this impossibility claim is false. C: **Correct:** The standard decomposition consists of DIJ, ADE, FGH, ABC and ABF, all in BCNF. D: **Incorrect:** The standard construction needs the residual key relation ABF in addition to the four dependency relations.

**1. Split the only multiattribute right-hand side:** Replace AB → CD with AB → C and AB → D. Together with AF → D, DE → F, C → G, F → E and G → A, this gives seven singleton-right-hand-side FDs. **2. Check for extraneous left-hand attributes:** A and B alone do not determine C or D. A and F alone do not determine D. D and E alone do not determine F. Thus no attribute can be removed from AB, AF or DE in the relevant dependencies. **3. Check redundancy one dependency at a time:** Removing AB → C prevents AB from obtaining C; removing AB → D leaves AB with A,B,C,G but no D or F. Removing AF → D leaves AF with A,F,E but no D. Removing DE → F leaves DE without F. Removing each of C → G, F → E or G → A likewise leaves its right-hand attribute unreachable from its determinant. **4. Count the minimal cover:** No left-hand reduction or FD deletion is possible, so these seven dependencies already form a minimal singleton-RHS cover. The unsplit display has six lines, but the question explicitly requires singleton right-hand sides. **Answer: 7.**

**1. Apply the replacement rules:** On every hit, update access recency. On a full-buffer miss, LRU evicts the least recently accessed resident block, whereas MRU evicts the most recently accessed resident block. The reference string has 14 accesses. **2. Trace LRU:** For references 4,2,3,4,5,2,7,3,5,2,5,3,4,2, the hit/miss sequence is M,M,M,H,M,M,M,M,M,M,H,H,M,M. Hits occur at accesses 4,11,12, so x=3. **3. Trace MRU:** The hit/miss sequence is M,M,M,H,M,H,M,H,H,M,M,H,M,M. Hits occur at accesses 4,6,8,9,12, so y=5. The recency updates after hits matter; replacement is not based merely on insertion order. **4. Evaluate the expression:** The source asks for \(10x+y\), so \(10(3)+5=35\). **Answer: 35.**

**1. Find singleton candidate keys:** C → D → AE and A → B, so C determines all attributes. Similarly D → AE → B, then EB → C, so D is also a singleton key. **2. Find keys without C or D:** Such a key must first enable EB → C. E cannot be obtained before D unless E is initially present. B can be supplied initially or obtained from an initial A. Hence BE and AE are the two minimal composite keys. **3. Verify minimality:** Neither A, B nor E alone determines all five attributes. In AE, A gives B and EB then gives C,D; in BE, EB immediately gives C,D,A. Each pair is therefore minimal. **4. Exclude additional keys:** Any set containing C or D is nonminimal unless it is that singleton. Without either, it must contain E and either A or B, so it contains AE or BE. The complete list is C,D,AE,BE, totaling 4. **Answer: 4.**