Database Management System · Quiz 2 · Sep 2025

**1. Identify the connection function:** In psycopg2, the public factory for opening a PostgreSQL connection is psycopg2.connect(...). It returns a connection object that can then create cursors. **2. Match the keyword arguments:** Option B supplies database, user, password, host and port in lowercase. The supplied database is organization, the user is postgres, the server is localhost and its port is 5432. **3. Separate valid syntax from connection success:** These parameters are the correct call form. A successful actual connection additionally requires a running server, an existing database and valid credentials. **Answer: B — psycopg2.connect with lowercase database, user, password, host and port.** A: **Incorrect:** The capitalized connection keywords are not the accepted PostgreSQL parameter names. B: **Correct:** This uses connect with the correct lowercase connection parameters. C: **Incorrect:** psycopg2.connection is not the public connect factory; the low-level connection class does not accept this keyword call as a substitute. D: **Incorrect:** psycopg2 is a module and cannot itself be called as a connection function.

**1. Find the candidate key:** Write S=Song_Name, A=Artist, G=Genre, D=Duration and L=Album. The dependencies are \(S\to A\), \(A\to G\), and \(LS\to D\). Neither L nor S occurs on a right-hand side, so every key must contain both; \((LS)^+=\{L,S,A,G,D\}\), making LS the unique candidate key. **2. Test second normal form:** Artist is a nonprime attribute determined by S, a proper subset of the composite key LS. Therefore \(S\to A\) is a partial dependency and violates 2NF; \(S\to G\) follows transitively and is another partial dependency on the key. **3. Classify the remaining dependency:** \(A\to G\) is a nonkey-to-nonkey dependency in the transitive chain from S. Artist is not a proper subset of the candidate key, so that displayed FD is not itself the stated partial-key dependency. Assuming atomic attribute values, the relation is in 1NF but not 2NF. **Answer: C, D — the relation is in 1NF, and Song_Name → Artist violates 2NF.** A: **Incorrect:** The dependency from Song_Name alone to Artist violates 2NF. B: **Incorrect:** Artist is not a subset of the composite candidate key; this is a transitive dependency in the key-to-Genre chain. C: **Correct:** With the usual atomic-attribute assumption, the relation satisfies 1NF. D: **Correct:** Song_Name is a proper subset of the key and determines the nonprime Artist attribute.

**1. Use the definition of an FD on a table:** X → Y holds in this instance if every pair of rows agreeing on X also agrees on Y. A unique determinant value cannot create a counterexample. **2. Check repeated values:** All seven PlayerID values are unique, so PlayerID determines PlayerName and Team; adding PlayerName to that determinant still determines Runs. Team repeats only for India, and both corresponding rows have 100 runs. **3. Exhibit the failed dependency:** Rohit appears with India and Bangladesh, and Smith appears with Australia and Newzealand. Thus equal PlayerName does not imply equal Team. **Answer: A, B, D — PlayerID → PlayerName, Team; PlayerName, PlayerID → Runs; and Team → Runs.** **Convention note:** The question asks about the displayed table instance. An FD holding in one instance does not by itself establish a business rule that must hold in every future database state. A: **Correct:** Every PlayerID is unique, so it determines both listed attributes in this table. B: **Correct:** The determinant includes the unique PlayerID and therefore determines Runs. C: **Incorrect:** Rohit and Smith each occur with two different teams. D: **Correct:** The only repeated Team value is India, and both India rows have Runs=100.

**1. Separate execution from retrieval:** A cursor sends SQL to the server using execute(sql, parameters) for one operation, or executemany(sql, parameter_sets) for repeated executions with different parameter sets. **2. Identify fetch methods:** After a result-producing statement has already run, fetchall() retrieves all remaining result rows and fetchmany() retrieves a batch. These methods do not initiate the SQL statement. **Answer: A, B — cursor.execute() and cursor.executemany().** **Convention note:** The empty parentheses in the options name the methods; real execution calls must supply the SQL statement and any required parameters. A: **Correct:** execute submits a SQL statement for execution. B: **Correct:** executemany executes a statement repeatedly for the supplied parameter sets. C: **Incorrect:** fetchall retrieves rows from an existing result set. D: **Incorrect:** fetchmany retrieves a selected number of rows from an existing result set.

**1. Identify locally enforceable dependencies:** The components \(ABC,BD,CDE\) directly contain \(A\to BC\), \(B\to D\) and \(CD\to E\), respectively. **2. Test the remaining FD using full projections:** Under the original F, the CDE projection also includes \(E\to CD\), and the ABC projection includes \(BC\to A\). Starting from E under the union of projected FDs yields only E,C,D: there is no way to obtain B and then A. Thus \(E\to A\) is not dependency-preserved. **3. Prove losslessness after removing E → A:** Split the original relation into ABCD and CDE. The intersection CD determines all of CDE by \(CD\to E\), so the split is lossless. Next split ABCD into ABC and BD; the intersection B determines BD by \(B\to D\), so this split is also lossless. **4. Conclude both properties:** Removing \(E\to A\) does not destroy losslessness. In the stated decomposition, the first three FDs are preserved and the fourth is not. **Answer: C, D — C and D — lossless without E → A, with only the first three listed dependencies preserved.** A: **Incorrect:** E → A is not derivable from the union of the complete projected dependency sets. B: **Incorrect:** Both lossless intersection tests hold without E → A. C: **Correct:** The decomposition is still lossless after that dependency is removed. D: **Correct:** Each of the first three FDs is local, whereas the projected closure of E lacks A.

**1. Check D1 dependency preservation:** D1 has schemas ABCD, BC, CE and EFG. The component ABCD enforces \(AB\to C\) and \(B\to D\); CE enforces \(C\to E\); EFG enforces \(E\to FG\). Every given FD is therefore preserved. **2. Check D1 losslessness:** Joining CE to EFG is lossless on intersection E because E determines EFG. Joining their union CEFG to ABCD is lossless on C because C determines EFG. The remaining BC relation is a projection of ABCD and adds no spurious tuples. Thus D1 is lossless. **3. Check D2 dependency preservation:** D2 has ABD, BCE and EFG. B determines D, C determines E, and E determines FG locally. But \(AB\to C\) is not derivable from these projections: the projected closure of AB stops at ABD. **4. Check D2 lossiness:** The join of BCE and EFG is lossless, but its intersection with ABD is only B, which determines neither side. A concrete legal original instance with fixed B,D and two different A,C values produces crossed A–C combinations after projection and join. Thus D2 is lossy and not dependency-preserving. **Answer: A, C — D1 is lossless and dependency-preserving; D2 is lossy and not dependency-preserving.** A: **Correct:** D1 is lossless and each original FD is enforceable within a component. B: **Incorrect:** D1 is lossless, so the “lossy” part is false. C: **Correct:** D2 loses AB → C and can introduce spurious A–C combinations. D: **Incorrect:** D1 is lossless, so both cannot be lossy.

**1. Compute the candidate keys:** With \(N\to P,Q\to M,OP\to Q,M\to NO\), M determines N,O,P,Q and Q determines M. OP and NO also determine every attribute. These are the minimal keys: M, Q, OP and NO. **2. Identify prime attributes:** Every attribute belongs to at least one candidate key: M and Q are singleton keys, O and P belong to OP, and N belongs to NO. Thus there are no nonprime attributes, making the 2NF requirement automatically satisfied; R also satisfies 3NF. **3. Test BCNF:** \(N^+=\{N,P\}\), which is not the whole relation. Therefore the nontrivial dependency \(N\to P\) has a determinant that is not a superkey and violates BCNF. **4. Check the preservation claim:** A standard 3NF synthesis can preserve all dependencies. This relation is already in 3NF, so saying preservation is impossible is false. **Answer: C, D — N → P violates BCNF, and R is in 2NF.** A: **Incorrect:** N → P violates BCNF because N is not a superkey. B: **Incorrect:** Dependency-preserving 3NF decomposition is possible; the relation itself already satisfies 3NF. C: **Correct:** N determines only N,P and is not a superkey. D: **Correct:** All attributes are prime, so there can be no partial dependency of a nonprime attribute.

**1. Find indispensable key attributes:** The FDs are \(AB\to CD,D\to F,F\to GH\). Attributes A,B,E never occur on a right-hand side, so every candidate key must include all three. **2. Compute the closure:** Starting from ABE, use AB → CD, then D → F, then F → GH. This obtains every attribute in ABCDEFGH. Removing A, B or E prevents recovering that missing attribute, so ABE is the unique candidate key. **3. Test normal form:** A,B,E are prime. AB is a proper subset of key ABE and determines nonprime C,D, so a partial dependency violates 2NF. Atomic attributes ensure only 1NF here. **Answer: C, D — A and B are prime; ABE is a superkey.** A: **Incorrect:** AB cannot determine E, and E alone cannot determine the remaining attributes; neither is a candidate key. B: **Incorrect:** AB → CD is a partial dependency of nonprime attributes on the key ABE. C: **Correct:** A and B both belong to the candidate key ABE. D: **Correct:** ABE determines all attributes and is therefore a superkey, indeed a candidate key.

**1. Apply the multivalued-dependency rule:** For a fixed X, \(X\twoheadrightarrow Y\) requires all combinations of that X-group’s Y values and Z values to be present. In a three-attribute schema the complementary dependency \(X\twoheadrightarrow Z\) expresses the same independence. **2. Complete the X=a group:** Existing rows include (a,b,d), (a,c,d) and (a,b,e). Because Y takes b,c and Z takes d,e, the missing fourth combination must be (a,c,e). Thus A=e. **3. Inspect the X=x group:** Both rows have the same Y value y, so any Z choices B and z already produce all combinations with that single Y. There is no requirement that B match a value in the X=a group. Therefore B=n and B=d are both allowed. **Answer: A, D — A=e with either B=n or B=d.** A: **Correct:** A=e supplies the required missing combination; B=n is unrestricted in its own X group. B: **Incorrect:** A=d leaves the necessary (a,c,e) row missing. C: **Incorrect:** A=c fails to provide (a,c,e) and creates additional unpaired Z values. D: **Correct:** A=e completes the first group, and B=d is permitted in the second.

**1. Write the two sets:** \(X=\{P\to R,T\to S,PQ\to R,R\to QS\}\), while \(Y=\{P\to QR,T\to S,R\to Q,Q\to S\}\). “Y covers X” means every FD in X follows from Y. **2. Show Y covers X:** From Y, P determines R directly by decomposition; augmentation gives PQ → R. T → S is present. Also R → Q and Q → S give R → QS. Thus every member of X is implied. **3. Disprove the reverse cover:** Under X, the closure of Q is only Q. Hence X does not imply Q → S, which belongs to Y. Therefore X and Y are not equivalent, and X cannot be a canonical cover of Y. **Answer: B — Y covers X.** A: **Incorrect:** Equivalence would require both cover directions, but X does not imply Q → S. B: **Correct:** Every dependency in X follows from Y by decomposition, augmentation and transitivity. C: **Incorrect:** The X-closure of Q does not contain S. D: **Incorrect:** A canonical cover must be equivalent to the original set, which X is not.

**1. Maintain recency rather than insertion order:** There are three slots and the reference string is 4,5,2,5,3,4,2,3,3,5. On a hit, move the accessed block to the most-recent position; on a full-buffer miss, evict the least recently used block. **2. Trace every access:** Writing the buffer from least to most recent, the states are: 4 → [4] miss; 5 → [4,5] miss; 2 → [4,5,2] miss; 5 → [4,2,5] hit; 3 → [2,5,3] miss; 4 → [5,3,4] miss; 2 → [3,4,2] miss; 3 → [4,2,3] hit; 3 → [4,2,3] hit; 5 → [2,3,5] miss. **3. Count hits:** Hits occur at the fourth, eighth and ninth references. The total is 3 hits and 7 misses across 10 references. **Answer: 3.**

**1. Find the Enrollment key:** Student_ID and Course_ID never occur on the right-hand sides of the supplied FDs. Together they determine all other attributes, so \((Student\_ID,Course\_ID)\) is the candidate key. **2. Identify partial dependencies:** Student_ID alone determines Student_Name. Course_ID alone determines Course_Name and Dept_Name. Each determinant is a proper subset of the composite key and each dependent is nonprime, so these three displayed FDs violate 2NF. **3. Distinguish the transitive link:** Dept_Name → Dept_Head is a nonkey-to-nonkey FD rather than a direct dependency on a key subset. In the full relation, Course_ID → Dept_Head also follows transitively and is a derived partial dependency, but it is not the displayed fourth FD itself. **Answer: A, B, C — the three dependencies from Student_ID or Course_ID alone.** A: **Correct:** Student_ID is a proper subset of the key and determines nonprime Student_Name. B: **Correct:** Course_ID is a proper subset of the key and determines nonprime Course_Name. C: **Correct:** Course_ID is a proper subset of the key and determines nonprime Dept_Name. D: **Incorrect:** Dept_Name is not a subset of the candidate key; this displayed FD is the transitive link.

**1. Preserve which student takes which course:** The original key is \((Student\_ID,Course\_ID)\). A correct decomposition must retain this pairing as well as removing the nonprime attributes that depend on only one key component. **2. Separate the partial dependencies:** Students(Student_ID,Student_Name) has a singleton key. Departments(Course_ID,Course_Name,Dept_Name,Dept_Head) also has a singleton key Course_ID and is therefore in 2NF, although Dept_Name → Dept_Head still prevents its 3NF. **3. Retain the association:** Enroll(Course_ID,Student_ID) contains the original pair and has no nonprime attributes, so it is in 2NF. Joining it with Students on Student_ID and Departments on Course_ID is lossless because those joining attributes determine the corresponding descriptive rows. **4. Select the complete decomposition:** Only the fourth option retains all attributes, removes the partial dependencies from every component and keeps the student-course association. **Answer: D — Students, Departments keyed by Course_ID, and Enroll(Course_ID, Student_ID).** **Convention note:** The shared Enrollment schema and FDs are supplied in the DBMS passage in this bundle. The Departments relation is in 2NF, which is all the question requires; it need not be in 3NF. A: **Incorrect:** The student-course association is lost; joining these two disjoint schemas produces all combinations. B: **Incorrect:** Students still contains the composite key Student_ID,Course_ID and the partial dependency Student_ID → Student_Name. C: **Incorrect:** Courses still has Course_ID → Course_Name as a partial dependency, and no relation retains Course_ID → Dept_Name. D: **Correct:** All three components are in 2NF, and Enroll preserves the original student-course pairs.