Database Management System · Quiz 2 · Jan 2025

Let us analyze the `players(Name, Team, Coach, Runs)` table: **1. Identify the Dependencies in `players`:** Looking at the 4 rows for `Sharma`: - `Team` uniquely determines `Coach` (`CSK -> Steven`, `MI -> Mahela`). - `Sharma` plays for two teams (`CSK`, `MI`) and has scored two distinct run values (`99`, `50`) in every combination (`(CSK, 99)`, `(MI, 50)`, `(MI, 99)`, `(CSK, 50)`). - Thus, for a given `Name`, `Runs` is independent of `(Team, Coach)`. That is, the multivalued dependency \( \text{Name} \twoheadrightarrow \text{Runs} \) and \( \text{Name} \twoheadrightarrow \{\text{Team}, \text{Coach}\} \) holds! **2. Decomposing into 4NF:** - Since `Name` is not a superkey of `players`, the non-trivial MVD \( \text{Name} \twoheadrightarrow \text{Runs} \) violates **Fourth Normal Form (4NF)**. - Decomposing on \( \text{Name} \twoheadrightarrow \text{Runs} \) splits `players` into: - **Table 2: `players1(Name, Runs)`** containing `(Sharma, 99)` and `(Sharma, 50)`. - **Table 3: `players2(Name, Team, Coach)`** containing `(Sharma, CSK, Steven)` and `(Sharma, MI, Mahela)`. **Answer: Option B (Table 2: players1(Name, Runs) and Table 3: players2(Name, Team, Coach)).** A: **Incorrect:** The original table violates 4NF because of the non-trivial multivalued dependency `Name ->> Runs`. B: **Correct:** Decomposes the independent multivalued attributes into `players1(Name, Runs)` and `players2(Name, Team, Coach)`, eliminating the MVD redundancy. C: **Incorrect:** `Table 4: players1(Name, Team, Runs)` omits the `(MI, 99)` and `(CSK, 50)` combinations, making the decomposition lossy. D: **Incorrect:** Loses the `(MI, 99)` and `(CSK, 50)` tuples upon natural join.

Let us find a **dependency-preserving BCNF decomposition** of `car(carEngineNo, model, fuelType, company, headQuarters, aadhaar, customer, state, purchaseDate)` with the minimum number of relations \( X \): **Given Functional Dependencies \( \mathcal{F} \):** 1. \( \text{carEngineNo} \rightarrow \text{model}, \text{fuelType}, \text{company} \) 2. \( (\text{carEngineNo}, \text{aadhaar}) \rightarrow \text{purchaseDate} \) 3. \( \text{company} \rightarrow \text{headQuarters} \) 4. \( \text{aadhaar} \rightarrow \text{customer}, \text{state} \) 5. \( \text{customer} \rightarrow \text{mobile} \) 6. \( \text{mobile} \rightarrow \text{aadhaar} \) **Step-by-Step Analysis of Determinants and Equivalent Keys:** - Notice that \( \text{aadhaar} \rightarrow \text{customer} \rightarrow \text{mobile} \rightarrow \text{aadhaar} \)! This forms a cycle of equivalent candidate keys for the customer entity: \( \text{aadhaar} \equiv \text{customer} \equiv \text{mobile} \). - Therefore, all customer FDs (\( \text{aadhaar} \rightarrow \text{customer}, \text{state} \); \( \text{customer} \rightarrow \text{mobile} \); \( \text{mobile} \rightarrow \text{aadhaar} \)) can be placed into a **single relation** \( R_1(\text{aadhaar}, \text{customer}, \text{mobile}, \text{state}) \), where every determinant (\( \text{aadhaar} \), \( \text{customer} \), \( \text{mobile} \)) is a candidate key of \( R_1 \)! Thus \( R_1 \) is in **BCNF**. - Next, for \( \text{company} \rightarrow \text{headQuarters} \), we form \( R_2(\text{company}, \text{headQuarters}) \) with key \( \text{company} \) (in **BCNF**). - Next, for \( \text{carEngineNo} \rightarrow \text{model}, \text{fuelType}, \text{company} \), we form \( R_3(\text{carEngineNo}, \text{model}, \text{fuelType}, \text{company}) \) with key \( \text{carEngineNo} \) (in **BCNF**). - Finally, for the candidate key of the whole relation \( (\text{carEngineNo}, \text{aadhaar}) \rightarrow \text{purchaseDate} \), we form \( R_4(\text{carEngineNo}, \text{aadhaar}, \text{purchaseDate}) \) with key \( (\text{carEngineNo}, \text{aadhaar}) \) (in **BCNF**). Thus, the minimum number of relations in BCNF that preserves all dependencies is **\( X = 4 \)**. **Answer: 4.** A: **Incorrect:** Because there are 4 distinct non-equivalent determinant groups (`(carEngineNo, aadhaar)`, `carEngineNo`, `company`, and `{aadhaar, customer, mobile}`), 3 relations cannot eliminate all BCNF violations. B: **Correct:** Grouping the equivalent keys `{aadhaar, customer, mobile}` with `state` gives 1 relation, plus 3 relations for `company`, `carEngineNo`, and `(carEngineNo, aadhaar)`, totaling 4 BCNF relations. C: **Incorrect:** `aadhaar`, `customer`, and `mobile` mutually determine each other and do not need to be split into separate tables. D: **Incorrect:** 4 relations suffice because `aadhaar`, `customer`, and `mobile` are all candidate keys of the customer relation.

Let us find the candidate key(s) and check the normal forms of \( R(A, B, C, D, E) \) with \( \mathcal{F} = \{A \rightarrow B, \; A \rightarrow C, \; AB \rightarrow D, \; C \rightarrow E\} \): **1. Find Candidate Key(s):** - Attribute \( A \) never appears on the right-hand side of any FD, so \( A \) must be in every candidate key. - Compute closure \( \{A\}^+ \): - \( A \rightarrow B, C \implies \{A, B, C\} \) - \( AB \rightarrow D \implies \{A, B, C, D\} \) - \( C \rightarrow E \implies \{A, B, C, D, E\} \) - Since \( \{A\}^+ = \{A, B, C, D, E\} \), the **only candidate key is \( A \)**. - Prime attribute: \( \{A\} \). Non-prime attributes: \( \{B, C, D, E\} \). **2. Check Normal Forms:** - **1NF:** Assumed atomic attributes \( \rightarrow \) in **1NF**. - **2NF:** Since the only candidate key \( A \) is a **single attribute**, there can be no partial dependencies on a proper subset of \( A \) (note \( AB \rightarrow D \) reduces to \( A \rightarrow D \) since \( A \rightarrow B \)). Thus, \( R \) is in **2NF**! - **3NF:** Consider \( C \rightarrow E \). Here \( C \) is **not a superkey**, and \( E \) is **not a prime attribute** (this is a transitive dependency \( A \rightarrow C \rightarrow E \)). Thus, \( R \) violates **3NF**. Therefore, the highest normal form achieved by \( R \) is **2 NF**. **Answer: 2 NF.** A: **Incorrect:** Because the sole candidate key `A` is a single attribute, there are no partial dependencies, so the relation satisfies 2NF. B: **Correct:** `A` is the only candidate key (single attribute, so in 2NF), but `C -> E` is a transitive dependency of non-prime `E` on non-prime `C`, violating 3NF. C: **Incorrect:** `C -> E` violates 3NF because `C` is not a superkey and `E` is not a prime attribute. D: **Incorrect:** Since `R` violates 3NF, it also violates BCNF.

Let us check the normal form of each decomposed relation \( R_1, R_2, R_3 \) under \( \mathcal{F} = \{\text{StudentID}, \text{CourseID} \rightarrow \text{Grade}, \; \text{CourseID} \rightarrow \text{InstructorID}, \; \text{InstructorID} \rightarrow \text{Department}, \; \text{Department} \rightarrow \text{Salary}\} \): **1. Check \( R_1(\text{StudentID}, \text{CourseID}, \text{Grade}) \):** - Candidate key is \( (\text{StudentID}, \text{CourseID}) \), and the only non-trivial FD is \( (\text{StudentID}, \text{CourseID}) \rightarrow \text{Grade} \). - Thus \( R_1 \) is in BCNF (and 3NF, 2NF). **2. Check \( R_2(\text{CourseID}, \text{InstructorID}) \):** - Candidate key is \( \text{CourseID} \), and the only FD is \( \text{CourseID} \rightarrow \text{InstructorID} \). - Thus \( R_2 \) is in BCNF (and 3NF, 2NF). **3. Check \( R_3(\text{InstructorID}, \text{Department}, \text{Salary}) \):** - Candidate key of \( R_3 \) is \( \text{InstructorID} \) (single attribute, so **no partial dependencies** \( \rightarrow \) in **2NF**). - However, \( R_3 \) has the transitive dependency \( \text{InstructorID} \rightarrow \text{Department} \rightarrow \text{Salary} \), where \( \text{Department} \) is not a superkey of \( R_3 \) and \( \text{Salary} \) is not a prime attribute. - Therefore, \( R_3 \) is in **2NF**, but **violates 3NF**! Thus, the new database structure has achieved **2NF** (having eliminated the partial dependency \( \text{CourseID} \rightarrow \text{InstructorID} \) from the original table, while \( R_3 \) remains in 2NF). **Answer: 2NF.** A: **Incorrect:** Separating `CourseID -> InstructorID` into `R2` eliminated the partial dependency on the composite key `(StudentID, CourseID)`, raising the schema from 1NF to 2NF. B: **Correct:** All three tables have no partial dependencies (so the schema is in 2NF), but `R3(InstructorID, Department, Salary)` still has the transitive dependency `Department -> Salary`, violating 3NF. C: **Incorrect:** `R3` violates 3NF because `Department -> Salary` is a transitive dependency. D: **Incorrect:** Since `R3` is not in 3NF, the schema is not in BCNF.

In Python, the standard library used to connect to a PostgreSQL database is **`psycopg2`**. **Syntax of `psycopg2.connect()`:** - The function is `psycopg2.connect(...)` (not `dbconnect`, `postgresql.connect`, or `connect.psycopg2`). - It takes lowercase keyword arguments: `database`, `user`, `password`, `host`, and `port`: ```python connection = psycopg2.connect( database = dbname, user = username, password = pwd, host = address, port = portnum ) ``` **Answer: connection = psycopg2.connect(database = dbname, user = username, password = pwd, host = address, port = portnum) (Option B).** A: **Incorrect:** `psycopg2` does not have a `dbconnect` function; the method is `connect`. B: **Correct:** Calls `psycopg2.connect()` with the valid lowercase keyword arguments `database`, `user`, `password`, `host`, and `port`. C: **Incorrect:** Uses `postgresql.connect` with capitalized parameter names (`Database`, `User`, `Host`, `Port`), which is invalid in `psycopg2`. D: **Incorrect:** Reverses the module and function name as `connect.psycopg2(...)`.

A functional dependency \( X \rightarrow Y \) holds in a table instance if whenever two rows agree on \( X \), they also agree on \( Y \). Let us check each option on Table 8 (`Players`): - **Option A (\( \text{PlayerID} \rightarrow \text{PlayerName} \))**: Every row has a distinct `PlayerID` (`001` through `007`). Since no two rows have the same `PlayerID`, `PlayerID` uniquely determines every other column! (**Holds**) - **Option B (\( \text{PlayerID}, \text{PlayerName} \rightarrow \text{Runs} \))**: Since `PlayerID` is unique across all rows, the pair `(PlayerID, PlayerName)` is also unique across all rows, so it functionally determines `Runs`. (**Holds**) - **Option C (\( \text{PlayerName}, \text{Team} \rightarrow \text{Runs} \))**: Let us check the pairs of `(PlayerName, Team)`: `(Virat, India)`, `(Rohit, India)`, `(Smith, Australia)`, `(Jason, England)`, `(Rohit, Bangladesh)`, `(Asif, Pakistan)`, `(Smith, Newzealand)`. Notice that all 7 pairs of `(PlayerName, Team)` are distinct! Therefore, `(PlayerName, Team) -> Runs` holds. (**Holds**) - **Option D (\( \text{Team}, \text{Runs} \rightarrow \text{PlayerID} \))**: Look at Row 1 (`001, Virat, India, 100`) and Row 2 (`002, Rohit, India, 100`). Both have `(Team, Runs) = (India, 100)`, but they have **different** `PlayerID` values (`001` vs `002`)! Thus, \( \text{Team}, \text{Runs} \rightarrow \text{PlayerID} \) is **violated**. **Answer: Options A, B, and C.** A: **Correct:** `PlayerID` has unique values (`001` to `007`) in every row, so `PlayerID -> PlayerName` holds. B: **Correct:** Since `PlayerID` is unique, `(PlayerID, PlayerName)` is also unique and determines `Runs`. C: **Correct:** Every `(PlayerName, Team)` combination in the table is distinct, so `(PlayerName, Team) -> Runs` holds. D: **Incorrect:** Violated by rows 1 and 2, which both have `(Team, Runs) = (India, 100)` but different `PlayerID` values (`001` and `002`).

Let us construct the **Binary Search Tree (BST)** by inserting `15, 14, 20, 10, 23, 17, 16, 2, 1, 6` in order: **1. Step-by-Step Insertion (Root at Level 0):** - **`15`**: Root (Level 0). - **`14`**: `< 15` \( \rightarrow \) left child of `15` (Level 1). - **`20`**: `> 15` \( \rightarrow \) right child of `15` (Level 1). - **`10`**: `< 15, < 14` \( \rightarrow \) left child of `14` (Level 2). - **`23`**: `> 15, > 20` \( \rightarrow \) right child of `20` (Level 2). - **`17`**: `> 15, < 20` \( \rightarrow \) left child of `20` (Level 2). - **`16`**: `> 15, < 20, < 17` \( \rightarrow \) left child of `17` (Level 3). - **`2`**: `< 15, < 14, < 10` \( \rightarrow \) left child of `10` (Level 3). - **`1`**: `< 15, < 14, < 10, < 2` \( \rightarrow \) left child of `2` (Level 4). - **`6`**: `< 15, < 14, < 10, > 2` \( \rightarrow \) right child of `2` (Level 4). **2. Evaluate the Options:** - **Option A**: The deepest nodes (`1` and `6`) are at level 4 (4 edges from root `15`: `15 -> 14 -> 10 -> 2 -> 1`), so the **height of the BST is 4**. (**Correct**) - **Option B**: The **leaf nodes** (nodes with no children) are `1`, `6`, `16`, and `23`. Their sum is: \[ 1 + 6 + 16 + 23 = 46 \] (**Correct**) - **Option C**: Node `17` has left child `16`, so `17` is **not** a leaf node. (**Incorrect**) - **Option D**: `15` is at level 0, `20` is at level 1, and `23` (right child of `20`) is at **level 2**, not level 1. (**Incorrect**) **Answer: Options A and B.** A: **Correct:** The longest path from the root (`15 -> 14 -> 10 -> 2 -> 1` or `6`) has 4 edges, so the height is 4. B: **Correct:** The leaf nodes are `1, 6, 16, 23`, and their sum is `1 + 6 + 16 + 23 = 46`. C: **Incorrect:** Node `17` is not a leaf node because `16` is its left child (and `1` is also a leaf node). D: **Incorrect:** With root `15` at level 0 and `20` at level 1, node `23` is at level 2.

A relation is in **Boyce-Codd Normal Form (BCNF)** if and only if for every non-trivial functional dependency \( X \rightarrow Y \), the determinant \( X \) is a **superkey** of the relation. Let us check each option: - **Option A: \( R(A, B, C) \) with \( \mathcal{F} = \{A \rightarrow BC\} \)** Here \( \{A\}^+ = \{A, B, C\} \), so \( A \) is a superkey. The only FD has superkey \( A \) on the LHS \( \rightarrow \) **in BCNF**. - **Option B: \( S(X, Y, Z) \) with \( \mathcal{F} = \{X \rightarrow Y, \; Z \rightarrow X\} \)** - Closure \( \{Z\}^+ = \{Z, X, Y\} \), so the only candidate key is **\( Z \)**. - In the FD \( X \rightarrow Y \), the determinant \( X \) has closure \( \{X\}^+ = \{X, Y\} \neq \{X, Y, Z\} \), so \( X \) is **not a superkey**! - Therefore, \( S \) is **not in BCNF**. - **Option C: \( T(P, Q, R) \) with \( \mathcal{F} = \{P \rightarrow Q, \; Q \rightarrow R, \; R \rightarrow P\} \)** Since \( P \rightarrow Q \rightarrow R \rightarrow P \), every single attribute \( P \), \( Q \), and \( R \) has closure \( \{P, Q, R\} \) and is a candidate key! Thus all determinants are superkeys \( \rightarrow \) **in BCNF**. - **Option D: \( U(M, N, O, P, Q) \) with \( \mathcal{F} = \{M \rightarrow NOP, \; P \rightarrow Q\} \)** - Closure \( \{M\}^+ = \{M, N, O, P, Q\} \), so **\( M \)** is the only candidate key. - In the FD \( P \rightarrow Q \), \( \{P\}^+ = \{P, Q\} \), so \( P \) is **not a superkey**! - Therefore, \( U \) is **not in BCNF**. **Answer: Options B and D.** A: **Incorrect:** `A` determines `B` and `C`, so `A` is a candidate key and `R(A, B, C)` is in BCNF. B: **Correct:** The only candidate key of `S` is `Z`, so the dependency `X -> Y` (where `X` is not a superkey) violates BCNF. C: **Incorrect:** `P`, `Q`, and `R` all mutually determine one another and are all candidate keys, so `T` is in BCNF. D: **Correct:** The only candidate key of `U` is `M`, so `P -> Q` (where `P` is not a superkey) violates BCNF.

We are given the relation \( Z(M, N, O, P, Q, R, S, T) \) (8 attributes) and functional dependencies: \[ \mathcal{F} = \{M \rightarrow N, \; O \rightarrow Q, \; P \rightarrow QR, \; N \rightarrow P, \; R \rightarrow S\} \] **1. Find the Candidate Key(s) of \( Z \):** - Look for attributes that **never appear on the right-hand side (RHS)** of any FD in \( \mathcal{F} \): - RHS attributes: \( N, Q, R, P, S \). - Attributes missing from RHS: **\( M, O, T \)**. - Therefore, \( \{M, O, T\} \) must be part of **every** candidate key! - Let us compute the attribute closure \( \{M, O, T\}^+ \): - Start with \( \{M, O, T\} \) - \( M \rightarrow N \implies \{M, O, T, N\} \) - \( N \rightarrow P \implies \{M, O, T, N, P\} \) - \( P \rightarrow QR \implies \{M, O, T, N, P, Q, R\} \) - \( R \rightarrow S \implies \{M, O, T, N, P, Q, R, S\} = Z \)! - Since \( \{M, O, T\}^+ \) contains all 8 attributes of \( Z \), **\( MOT \) is the unique candidate key** of \( Z \). **2. Count the Total Number of Superkeys:** - Any subset of attributes of \( Z \) is a superkey if and only if it contains the unique candidate key \( \{M, O, T\} \). - The remaining attributes not in \( \{M, O, T\} \) are \( \{N, P, Q, R, S\} \), which is a set of \( 8 - 3 = 5 \) attributes. - Each of these 5 attributes can either be included or excluded independently, giving: \[ \text{Total Superkeys} = 2^5 = 32 \] **Answer: 32.**

We are given \( Z(M, N, O, P, Q, R, S, T) \) with \( \mathcal{F} = \{M \rightarrow N, \; O \rightarrow Q, \; P \rightarrow QR, \; N \rightarrow P, \; R \rightarrow S\} \), and two decompositions: - \( D_1 = (M, N), \; (M, O, P, Q), \; (Q, R, S, T) \) - \( D_2 = (M, N, P, Q, R), \; (N, O, Q), \; (T, R, S) \) **1. Check Dependency Preservation of \( D_1 \):** - Consider the functional dependency \( N \rightarrow P \) (or \( P \rightarrow R \)). - In \( D_1 \): - \( R_{11}(M, N) \): only has \( M, N \) (projected FD: \( M \rightarrow N \)). - \( R_{12}(M, O, P, Q) \): has \( M \rightarrow P, \; O \rightarrow Q, \; P \rightarrow Q \), but does not contain \( N \) or \( R \). - \( R_{13}(Q, R, S, T) \): has \( R \rightarrow S \), but does not contain \( P \) (and \( Q \not\rightarrow R \)). - Using the projected FDs of \( D_1 \), the closure of \( N \) is \( \{N\}^+ = \{N\} \) (since \( N \not\rightarrow M \)), so \( N \rightarrow P \) is **lost**! Also, the closure of \( P \) is \( \{P, Q\} \), so \( P \rightarrow R \) is **lost**! Thus, **\( D_1 \) is NOT dependency preserving**. **2. Check Dependency Preservation of \( D_2 \):** - In \( D_2 \): - \( (M, N, P, Q, R) \) preserves \( M \rightarrow N \), \( N \rightarrow P \), and \( P \rightarrow QR \). - \( (N, O, Q) \) preserves \( O \rightarrow Q \). - \( (T, R, S) \) preserves \( R \rightarrow S \). - Every single FD in \( \mathcal{F} \) is directly preserved in one of the relations of \( D_2 \)! Thus, **\( D_2 \) is dependency preserving**. **Answer: D2 is dependency preserving but D1 is not.** A: **Incorrect:** `D1` loses `N -> P` and `P -> R`, whereas `D2` preserves all dependencies. B: **Correct:** `D2` preserves every FD in `F` across its three relations, whereas `D1` fails to preserve `N -> P` and `P -> R`. C: **Incorrect:** `D1` is not dependency preserving. D: **Incorrect:** `D2` is dependency preserving.

Let us calculate the total storage capacity of the magnetic disk step by step: **1. Given Disk Parameters:** - Number of platters \( = 16 = 2^4 \) - Surfaces per platter \( = 2 = 2^1 \) - Tracks per surface \( = 1024 = 2^{10} \) - Sectors per track \( = 512 = 2^9 \) - Bytes per sector \( = 1024 = 2^{10}\text{ bytes} \) **2. Compute Total Capacity in Bytes:** \[ \text{Total Capacity} = 16 \times 2 \times 1024 \times 512 \times 1024\text{ bytes} \] \[ \text{Total Capacity} = 2^4 \times 2^1 \times 2^{10} \times 2^9 \times 2^{10} = 2^{34}\text{ bytes} \] **3. Convert Bytes to Gigabytes (GB):** Since \( 1\text{ GB} = 2^{30}\text{ bytes} \): \[ \text{Capacity in GB} = \frac{2^{34}\text{ bytes}}{2^{30}\text{ bytes/GB}} = 2^4\text{ GB} = 16\text{ GB} \] **Answer: 16.**

Let us calculate the **disk access time** (\( \text{seek time} + \text{rotational latency} \)) step by step: **1. Given Parameters:** - Rotational speed \( = 5000\text{ RPM} \) (revolutions per minute) - Seek time \( = 5\text{ ms} \) **2. Calculate Time for One Full Revolution:** - In \( 1\text{ minute} = 60\text{ seconds} = 60,000\text{ ms} \), the disk completes \( 5000 \) revolutions. - Time for 1 full revolution: \[ T_{\text{rev}} = \frac{60,000\text{ ms}}{5000} = 12\text{ ms} \] **3. Calculate Average Rotational Latency:** Average rotational latency is the time required for **half a revolution**: \[ \text{Rotational Latency} = \frac{1}{2} \times T_{\text{rev}} = \frac{12\text{ ms}}{2} = 6\text{ ms} \] **4. Compute Total Disk Access Time:** \[ \text{Disk Access Time} = \text{Seek Time} + \text{Rotational Latency} = 5\text{ ms} + 6\text{ ms} = 11\text{ ms} \] **Answer: 11.**

We are given the relation \( \text{Library}(\text{BookID}, \text{Title}, \text{Author}, \text{Publisher}, \text{Year}) \) with functional dependencies: \[ \mathcal{F} = \{\text{BookID} \rightarrow \text{Title}, \text{Author}, \quad \text{Author}, \text{Year} \rightarrow \text{Publisher}, \quad \text{Publisher} \rightarrow \text{Year}\} \] **1. Identify Essential Attribute(s):** - Notice that `BookID` never appears on the right-hand side of any FD in \( \mathcal{F} \). Therefore, **`BookID` must be part of every candidate key**. - Let us find the closure of `BookID` alone: \[ \{\text{BookID}\}^+ = \{\text{BookID}, \text{Title}, \text{Author}\} \] Since `Publisher` and `Year` are missing, `(BookID)` alone is **not** a superkey. **2. Extend `BookID` with Minimal Attributes:** - **Try `(BookID, Year)` (Option B):** - `BookID` gives `Title, Author`. - Now we have `(Author, Year)`, which determines `Publisher` (via `Author, Year -> Publisher`)! - Thus \( \{\text{BookID}, \text{Year}\}^+ = \{\text{BookID}, \text{Year}, \text{Title}, \text{Author}, \text{Publisher}\} \). Since `(BookID)` alone is not a superkey, **`(BookID, Year)` is a minimal superkey (Candidate Key)**! - **Try `(BookID, Publisher)` (Option D):** - `BookID` gives `Title, Author`. - `Publisher` gives `Year` (via `Publisher -> Year`). - Thus \( \{\text{BookID}, \text{Publisher}\}^+ = \{\text{BookID}, \text{Publisher}, \text{Title}, \text{Author}, \text{Year}\} \). Since neither `(BookID)` nor `(Publisher)` alone is a superkey, **`(BookID, Publisher)` is also a Candidate Key**! - **Check `(BookID, Author, Year)` (Option C):** - This is a proper superset of the candidate key `(BookID, Year)`, so it is a superkey but **not minimal** (not a candidate key). **Answer: (BookID, Year) and (BookID, Publisher) (Options B and D).** A: **Incorrect:** `{BookID}+ = {BookID, Title, Author}`, which does not determine `Publisher` or `Year`. B: **Correct:** `{BookID, Year}+` determines `Title, Author` (from `BookID`) and `Publisher` (from `Author, Year`), and is minimal. C: **Incorrect:** `Author` is already determined by `BookID`, so `(BookID, Author, Year)` is a non-minimal superkey. D: **Correct:** `{BookID, Publisher}+` determines `Title, Author` (from `BookID`) and `Year` (from `Publisher`), and is minimal.

We are given the relation \( \text{Library}(\text{BookID}, \text{Title}, \text{Author}, \text{Publisher}, \text{Year}) \) with functional dependencies: - \( \text{BookID} \rightarrow \text{Title}, \text{Author} \) - \( \text{Author}, \text{Year} \rightarrow \text{Publisher} \) - \( \text{Publisher} \rightarrow \text{Year} \) **1. Condition for Lossless Join Decomposition into \( L_1 \) and \( L_2 \):** A decomposition of \( R \) into \( L_1 \) and \( L_2 \) is **lossless** if and only if: 1. \( L_1 \cup L_2 = R \) (all attributes of \( R \) are preserved), and 2. \( (L_1 \cap L_2) \rightarrow L_1 \) or \( (L_1 \cap L_2) \rightarrow L_2 \) holds in \( \mathcal{F}^+ \) (the common attributes form a superkey for at least one of the two relations). **2. Testing Option B:** - \( L_1(\text{BookID}, \text{Title}, \text{Author}) \) and \( L_2(\text{BookID}, \text{Year}, \text{Publisher}) \). - Union: \( L_1 \cup L_2 = \{\text{BookID}, \text{Title}, \text{Author}, \text{Year}, \text{Publisher}\} = \text{Library} \). - Intersection: \( L_1 \cap L_2 = \{\text{BookID}\} \). - Closure of intersection: Since \( \text{BookID} \rightarrow \text{Title}, \text{Author} \), we have \( \{\text{BookID}\}^+ = \{\text{BookID}, \text{Title}, \text{Author}\} = L_1 \). Thus \( L_1 \cap L_2 \rightarrow L_1 \), making Option B a lossless join decomposition! **Answer: L1(BookID, Title, Author), L2(BookID, Year, Publisher) (Option B).** A: **Incorrect:** Here `L1 ∩ L2 = {BookID}`. The closure `{BookID}+ = {BookID, Title, Author}` covers neither `L1(BookID, Author, Year)` nor `L2(Publisher, Title, BookID)`. B: **Correct:** The common attribute is `BookID`, and since `BookID -> Title, Author`, `L1 ∩ L2` is a superkey of `L1(BookID, Title, Author)`, guaranteeing a lossless join. C: **Incorrect:** `L1 ∩ L2 = ∅` (no common attributes), so joining them produces a Cartesian product (lossy). D: **Incorrect:** `L1 ∩ L2 = {Publisher}`. Since `{Publisher}+ = {Publisher, Year}`, it does not determine all of `L1` or `L2` (since `Title` is in `L2`), so it is lossy.