Database Management System · End Term · May 2026 · FN

**1. Understand the query requirements:** We have two tables: `Student(student_id, name, dept_id)` and `Department(dept_id, dept_name)`. We need to retrieve `name` from `Student` where: 1. The student's `name` **ends with the character `'a'`** — in SQL `LIKE` pattern matching, `'%'` matches any sequence of zero or more characters, so ending with `'a'` is written as `name LIKE '%a'`. 2. The student belongs to either the `'CSE'` **or** `'ME'` department — the subquery on `Department` must filter with `WHERE dept_name = 'CSE' OR dept_name = 'ME'` (or `dept_name IN ('CSE', 'ME')`). **2. Compare the four SQL options:** - **Option A:** Uses `WHERE name LIKE '%a'` and `WHERE dept_name = 'CSE' OR dept_name = 'ME'`. (Correct!) - **Option B:** Uses `name LIKE 'a%'`, which matches names *starting* with `'a'`, not ending with `'a'`. - **Option C:** Uses `dept_name = 'CSE' AND dept_name = 'ME'`, which is always false because a single department row cannot simultaneously be `'CSE'` and `'ME'`. - **Option D:** Uses `name LIKE '%a%'`, which matches names containing `'a'` *anywhere*, not specifically at the end. **Answer: A — SELECT name FROM Student WHERE name LIKE '%a' AND dept_id IN (SELECT dept_id FROM Department WHERE dept_name = 'CSE' OR dept_name = 'ME');** A: **Correct:** `LIKE '%a'` matches strings ending in `'a'`, and `dept_name = 'CSE' OR dept_name = 'ME'` selects the department IDs for both CSE and ME. B: **Incorrect:** `LIKE 'a%'` matches names that *begin* with `'a'` rather than end with `'a'`. C: **Incorrect:** Using `AND` between `dept_name = 'CSE'` and `dept_name = 'ME'` returns an empty set because no single department has both names. D: **Incorrect:** `LIKE '%a%'` matches any name that contains `'a'` at any position, not just names ending with `'a'`.

**1. Trace the SQL `JOIN` and `WHERE` filter:** We join `Employee E` with `Project P` on `project_id` and filter rows with `WHERE P.budget >= 80000`. Looking at the `Project` table: - `P101`: budget `80000` (\(\ge 80000\), kept) - `P102`: budget `120000` (\(\ge 80000\), kept) - `P103`: budget `90000` (\(\ge 80000\), kept) - `P104`: budget `60000` (\(< 80000\), **filtered out** by `WHERE`) **2. Group the remaining employee rows by `E.dept` and check `HAVING AVG(P.budget) >= 90000`:** After excluding employees on `P104` (`E104` and `E107`): - **CSE:** has 3 employees (`E101` on `P101=80000`, `E102` on `P102=120000`, `E103` on `P101=80000`). Average budget \(= \frac{80000 + 120000 + 80000}{3} = 93333.33 \ge 90000\) \(\implies\) **Kept (`CSE: 3`)**. - **ECE:** (`E104` on `P104` was removed by `WHERE`); remaining is 1 employee (`E105` on `P102=120000`). Average budget \(= 120000 \ge 90000\) \(\implies\) **Kept (`ECE: 1`)**. - **ME:** (`E107` on `P104` was removed by `WHERE`); remaining are 2 employees (`E106` on `P103=90000`, `E108` on `P103=90000`). Average budget \(= 90000 \ge 90000\) \(\implies\) **Kept (`ME: 2`)**. Sorted by `E.dept`, the output is `CSE: 3`, `ECE: 1`, `ME: 2`. **Answer: A — CSE: 3, ECE: 1, ME: 2.** A: **Correct:** After `WHERE P.budget >= 80000` removes `P104` rows, all three departments (`CSE` with 3 rows, `ECE` with 1 row, `ME` with 2 rows) satisfy `AVG(P.budget) >= 90000`. B: **Incorrect:** This omits `ME`, whose remaining employees on `P103` have an average budget of `90000 >= 90000`. C: **Incorrect:** This counts the `P104` rows that were already filtered out by `WHERE P.budget >= 80000`. D: **Incorrect:** This omits `CSE`, whose average budget across its 3 qualifying employees is \(280000 / 3 \approx 93333.33 \ge 90000\).

**1. Find the candidate key(s) of \(R(A, B, C, D)\):** We are given the functional dependencies: \[A \to B, \quad B \to C, \quad C \to D\] Computing the closure of \(A\): \[A^+ = \{A, B, C, D\}\] Since \(A\) does not appear on the right-hand side of any functional dependency, **\(A\) is the unique candidate key** of \(R\). Thus, \(A\) is the only prime attribute, and \(B, C, D\) are non-prime attributes. **2. Check the normal forms (2NF, 3NF, BCNF):** - **2NF:** Every non-prime attribute must be fully functionally dependent on every candidate key (no partial dependencies on a proper subset of a candidate key). Since the unique candidate key \(A\) consists of a **single attribute**, no partial dependency can exist! Thus, **\(R\) is in 2NF**. - **3NF:** For every non-trivial FD \(X \to Y\), either \(X\) must be a superkey or \(Y\) must be a prime attribute. Here, for \(B \to C\) and \(C \to D\), neither \(B\) nor \(C\) is a superkey, and neither \(C\) nor \(D\) is a prime attribute (these are transitive dependencies). Thus, **\(R\) is NOT in 3NF** (and therefore not in BCNF). **Answer: C — R is in 2NF but not 3NF.** A: **Incorrect:** In \(B \to C\) and \(C \to D\), the left-hand sides \(B\) and \(C\) are not superkeys, violating BCNF. B: **Incorrect:** The transitive dependencies \(B \to C\) and \(C \to D\) between non-prime attributes violate 3NF. C: **Correct:** Because the sole candidate key \(A\) is a single attribute, there are no partial dependencies (so \(R\) is in 2NF), but \(B \to C\) and \(C \to D\) violate 3NF. D: **Incorrect:** A relation with only single-attribute candidate keys cannot have partial dependencies and is automatically in 2NF.

**1. Construct the precedence (serialization) graph for schedule \(S\):** We are given: \[S : W_1(A),\; R_2(A),\; W_1(B),\; R_3(B),\; W_2(C),\; R_4(C),\; W_3(D),\; R_4(D)\] Find all conflicting pairs of operations (same data item, different transactions, at least one write): - On \(A\): \(W_1(A)\) precedes \(R_2(A) \implies\) directed edge **\(T_1 \to T_2\)**. - On \(B\): \(W_1(B)\) precedes \(R_3(B) \implies\) directed edge **\(T_1 \to T_3\)**. - On \(C\): \(W_2(C)\) precedes \(R_4(C) \implies\) directed edge **\(T_2 \to T_4\)**. - On \(D\): \(W_3(D)\) precedes \(R_4(D) \implies\) directed edge **\(T_3 \to T_4\)**. **2. Count the topological sorts of the precedence graph:** The precedence graph is a DAG where \(T_1\) must come first, \(T_4\) must come last, and \(T_2\) and \(T_3\) have no edges between them. Therefore, there are exactly **2** valid topological orderings (conflict-equivalent serial schedules): 1. \(T_1 \to T_2 \to T_3 \to T_4\) 2. \(T_1 \to T_3 \to T_2 \to T_4\) **Answer: B — 2.** A: **Incorrect:** Because \(T_2\) and \(T_3\) do not conflict with each other, either can precede the other between \(T_1\) and \(T_4\), giving 2 serial schedules. B: **Correct:** The precedence edges \(T_1 \to T_2\), \(T_1 \to T_3\), \(T_2 \to T_4\), and \(T_3 \to T_4\) allow exactly 2 topological sorts: \((T_1, T_2, T_3, T_4)\) and \((T_1, T_3, T_2, T_4)\). C: **Incorrect:** Only 2 linear extensions exist for this diamond-shaped partial order. D: **Incorrect:** \(T_1\) must precede both \(T_2, T_3\), and both must precede \(T_4\), leaving only \(2! = 2\) valid orderings.

**1. Find the candidate key(s) of \(R(A, B, C, D, E, F)\):** We are given the functional dependencies: \[AB \to CDE, \quad ABC \to EF, \quad E \to F\] Notice that attributes \(A\) and \(B\) never appear on the right-hand side of any FD, so every candidate key must contain \(AB\). Compute the attribute closure of \(AB\): - Start with \(\{A, B\}\). - Using \(AB \to CDE\), we add \(C, D, E\) to get \(\{A, B, C, D, E\}\). - Using \(E \to F\) (or \(ABC \to EF\)), we add \(F\) to get \((AB)^+ = \{A, B, C, D, E, F\}\). Thus, **\(AB\) is the unique candidate key** of \(R\). The prime attributes are \(\{A, B\}\) and the non-prime attributes are \(\{C, D, E, F\}\). **2. Check 2NF, 3NF, and BCNF:** - **2NF:** Check if any non-prime attribute is partially dependent on a proper subset of \(AB\) (i.e., on \(A\) alone or \(B\) alone). Here, \(A^+ = \{A\}\) and \(B^+ = \{B\}\), so neither \(A\) nor \(B\) determines any non-prime attribute! Thus, **\(R\) is in 2NF**. - **3NF:** Consider the FD \(E \to F\). Here, \(E\) is **not** a superkey, and \(F\) is **not** a prime attribute (it is a transitive dependency \(AB \to E \to F\)). Thus, **\(R\) is NOT in 3NF** (and not in BCNF). **Answer: B — R is in 2NF but not in 3NF.** A: **Incorrect:** The functional dependency \(E \to F\) is a transitive dependency between non-prime attributes, which violates 3NF. B: **Correct:** \(AB\) is the only candidate key and neither \(A\) nor \(B\) alone determines any attribute (so \(R\) is in 2NF), but \(E \to F\) violates 3NF. C: **Incorrect:** Every relation in BCNF is automatically in 3NF; it is impossible to be in BCNF without being in 3NF. D: **Incorrect:** Because \(E\) is not a superkey and \(F\) is non-prime, \(E \to F\) violates both 3NF and BCNF.

**1. Analyze the relations and query requirement:** We have \(R(A, B, C)\) and \(S(B, D, E)\), and we want to retrieve the values of attribute \(A\) for joined tuples where \(D > 10\). **2. Check each Relational Algebra expression:** - **Option A: \(\pi_A(\sigma_{D > 10}(R \bowtie S))\)** 1. First performs the natural join \(R \bowtie S\) on the common attribute \(B\), producing tuples with schema \((A, B, C, D, E)\). 2. Next applies the selection \(\sigma_{D > 10}\) to keep only tuples where \(D > 10\). 3. Finally projects onto attribute \(A\) via \(\pi_A\). This is completely valid and returns the exact required result! - **Option B: \(\sigma_{D > 10}(\pi_A(R \bowtie S))\)** — Invalid, because projecting \(\pi_A\) first removes attribute \(D\), so \(\sigma_{D > 10}\) cannot be evaluated afterward. - **Option C: \(\pi_A(\sigma_{D > 10}(R) \bowtie S)\)** — Invalid, because relation \(R(A, B, C)\) does not have attribute \(D\). - **Option D: \(\pi_A(R \bowtie \sigma_{D > 10}(\pi_B(S)))\)** — Invalid, because projecting \(\pi_B(S)\) removes attribute \(D\) before \(\sigma_{D > 10}\). **Answer: A — pi_A(sigma_{D > 10}(R bowtie S)).** A: **Correct:** Joining \(R \bowtie S\) includes both \(A\) and \(D\), allowing \(\sigma_{D > 10}\) to filter the rows before \(\pi_A\) projects attribute \(A\). B: **Incorrect:** Applying \(\pi_A\) first eliminates attribute \(D\), making the outer selection \(\sigma_{D > 10}\) invalid. C: **Incorrect:** Attribute \(D\) belongs to \(S(B, D, E)\), not \(R(A, B, C)\), so \(\sigma_{D > 10}(R)\) is invalid. D: **Incorrect:** Applying \(\pi_B(S)\) drops attribute \(D\), so \(\sigma_{D > 10}\) cannot be applied to the result.

**1. Interpret the `(min..max)` cardinality notation on the ER diagram:** We are given: `Student (0..N) ----- <enrolls> ----- (1..N) Course` Under the standard `(min..max)` participation notation placed next to each entity: - Next to `Student` is `(0..N)`: A student can enroll in a minimum of `0` courses and a maximum of `N` (many) courses (optional participation for `Student`). - Next to `Course` is `(1..N)`: A course must have a minimum of `1` student and can have up to `N` (many) students enrolled in it (mandatory/total participation for `Course`). **2. Match with the given statements:** Because `Course` has minimum cardinality `1` (`1..N`), **a course must have at least one student**. **Answer: C — A course must have at least one student.** A: **Incorrect:** The `(0..N)` constraint on `Student` indicates a minimum of `0`, so a student may enroll in zero courses. B: **Incorrect:** The upper bound `N` allows a student to enroll in multiple courses. C: **Correct:** The `(1..N)` cardinality constraint on `Course` specifies a minimum participation of `1` and maximum of `N`, meaning every course must have at least one student. D: **Incorrect:** The upper bound `N` in `(1..N)` allows a course to have many students.

**1. Compute the attribute closures under \(F = \{A \to B,\; B \to C,\; AC \to D,\; D \to E\}\):** Let us find \(A^+\) (the closure of \(A\)): 1. Start with \(A^+ = \{A\}\). 2. From \(A \to B\), add \(B\): \(A^+ = \{A, B\}\). 3. From \(B \to C\), add \(C\): \(A^+ = \{A, B, C\}\). 4. Now both \(A\) and \(C\) are in \(A^+\), so from \(AC \to D\), add \(D\): \(A^+ = \{A, B, C, D\}\). 5. From \(D \to E\), add \(E\): \(A^+ = \{A, B, C, D, E\}\). **2. Check which option is implied by \(F\):** - Since \(E \in A^+\), the functional dependency **\(A \to E\)** is implied by \(F\)! - For the other options: \(B^+ = \{B, C\}\) (does not contain \(A\)), \(C^+ = \{C\}\) (does not contain \(E\) because \(AC \to D\) also requires \(A\)), and \(E^+ = \{E\}\) (does not contain \(A\)). **Answer: A — A -> E.** A: **Correct:** Since \(A \to B\) and \(B \to C\), \(A\) determines \(AC\), which determines \(D\) (via \(AC \to D\)) and in turn \(E\) (via \(D \to E\)), proving \(A \to E\). B: **Incorrect:** \(B^+ = \{B, C\}\), so \(B\) does not determine \(A\). C: **Incorrect:** \(C^+ = \{C\}\) because \(AC \to D\) requires both \(A\) and \(C\), so \(C\) alone cannot determine \(D\) or \(E\). D: **Incorrect:** \(E\) never appears on the left-hand side of any FD in \(F\), so \(E^+ = \{E\}\).

**1. Test each candidate hash function on the given key-bucket pairs `(7 -> 1), (13 -> 8), (27 -> 6)`:** - **Option A: \(h(k) = (3k + 2) \bmod 11\)** - For \(k = 7\): \((3(7) + 2) \bmod 11 = 23 \bmod 11 = 1\) (matches bucket `1`!) - For \(k = 13\): \((3(13) + 2) \bmod 11 = 41 \bmod 11 = 8\) (matches bucket `8`!) - For \(k = 27\): \((3(27) + 2) \bmod 11 = 83 \bmod 11 = 6\) (since \(83 = 7 \times 11 + 6\), matches bucket `6`!) - **Option B: \(h(k) = (3k + 1) \bmod 11\)** - For \(k = 7\): \(22 \bmod 11 = 0 \neq 1\). - **Option C: \(h(k) = (2k + 2) \bmod 11\)** - For \(k = 7\): \(16 \bmod 11 = 5 \neq 1\). - **Option D: \(h(k) = (4k + 2) \bmod 11\)** - For \(k = 7\): \(30 \bmod 11 = 8 \neq 1\). **Answer: A — h(k) = (3k + 2) mod 11.** A: **Correct:** Substituting \(k = 7, 13, 27\) into \((3k + 2) \bmod 11\) yields \(23 \bmod 11 = 1\), \(41 \bmod 11 = 8\), and \(83 \bmod 11 = 6\), matching all three observations. B: **Incorrect:** For \(k = 7\), \((3(7) + 1) \bmod 11 = 22 \bmod 11 = 0 \neq 1\). C: **Incorrect:** For \(k = 7\), \((2(7) + 2) \bmod 11 = 16 \bmod 11 = 5 \neq 1\). D: **Incorrect:** For \(k = 7\), \((4(7) + 2) \bmod 11 = 30 \bmod 11 = 8 \neq 1\).

**1. Recall the checkpoint recovery rules for each transaction category:** When a crash occurs after a checkpoint: - **\(T_2\) (committed *before* the checkpoint):** All of its updates were already flushed to disk during the checkpoint. No recovery action is needed (ignore \(T_2\)). - **\(T_1\) (active at checkpoint, committed *before* crash) and \(T_3\) (started after checkpoint, committed *before* crash):** Since both transactions committed after the checkpoint and before the crash, their updates after the checkpoint might not have been flushed to disk yet. To ensure durability, **\(T_1\) and \(T_3\) need REDO**. - **\(T_4\) (active when the crash occurred — uncommitted):** Because \(T_4\) never committed, any changes it wrote to the database must be rolled back to ensure atomicity. Thus, **\(T_4\) needs UNDO**. **Answer: A — T_1, T_3 need REDO and T_4 needs UNDO.** A: **Correct:** Transactions that committed after the checkpoint (\(T_1, T_3\)) must be redone, while transactions still active at the crash (\(T_4\)) must be undone (and \(T_2\) committed before the checkpoint, requiring no action). B: **Incorrect:** Committed transactions (\(T_1, T_3\)) are never undone, and \(T_2\) already completed before the checkpoint. C: **Incorrect:** \(T_2\) committed prior to the checkpoint so its changes are already safely on disk and do not need REDO. D: **Incorrect:** Uncommitted transaction \(T_4\) must be undone, and \(T_2\) does not need REDO.

**1. Identify which transactions are undone vs. redone during crash recovery:** We start with initial values: \[A = 500, \quad B = 800, \quad C = 300, \quad D = 1000\] Looking at the log records before the crash at line 12: - **\(T_3\) has a `<T3, commit>` record (line 9):** So \(T_3\) is a committed transaction and is **redone**. Its update `<T3, D, 1000, 900>` sets **\(D = 900\)**. - **\(T_1, T_2, T_4\) have NO commit records before the crash:** All three uncommitted transactions are **undone** in reverse chronological order, restoring every data item they modified (`A`, `B`, and `C`) back to their original values before those transactions started: - Undo `<T4, C, 450, 550>` \(\implies C = 450\) - Undo `<T2, A, 700, 600>` \(\implies A = 700\) - Undo `<T1, C, 300, 450>` \(\implies C = 300\) - Undo `<T2, B, 800, 950>` \(\implies B = 800\) - Undo `<T1, A, 500, 700>` \(\implies A = 500\) **2. Compute the final expression \(2A + B - C + D\):** After recovery completes, the database values are \(A = 500,\; B = 800,\; C = 300,\; D = 900\). Substituting these values gives: \[2A + B - C + D = 2(500) + 800 - 300 + 900 = 1000 + 800 - 300 + 900 = 2400\] **Answer: 2400.**

**1. Analyze the functional dependencies on \(R(A, B, C, D, E, F, G)\):** We are given: \[C \to D, \quad D \to E, \quad E \to F, \quad F \to C\] **2. Identify essential attributes and equivalent cycle attributes:** - Notice that attributes **\(A, B, G\)** never appear on the right-hand side of any functional dependency! Therefore, **every candidate key must include \(A, B, G\)**. - Meanwhile, the remaining four attributes \(\{C, D, E, F\}\) form a directed cycle \(C \to D \to E \to F \to C\). Including **any single attribute** from \(\{C, D, E, F\}\) determines all four of them: \[C^+ = D^+ = E^+ = F^+ = \{C, D, E, F\}\] **3. List and count all candidate keys:** Combining the mandatory attributes \(ABG\) with any one attribute from \(\{C, D, E, F\}\) gives the exact set of minimal superkeys (candidate keys): 1. \(ABGC\) 2. \(ABGD\) 3. \(ABGE\) 4. \(ABGF\) Thus, there are **4** candidate keys. **Answer: 4.**

**1. Calculate the total number of sectors on the magnetic disk:** We are given: - Number of platters \(= 6\) - Surfaces per platter \(= 2\) - Tracks per surface \(= 2048 = 2^{11}\) - Sectors per track \(= 4096 = 2^{12}\) Multiplying these together gives the total number of sectors on the disk: \[\text{Total sectors} = 6 \times 2 \times 2^{11} \times 2^{12} = 12 \times 2^{23} = 3 \times 2^{25} \text{ sectors}\] **2. Divide total disk capacity by the total number of sectors:** We are given that the total disk capacity is \(24\text{ GB} = 24 \times 2^{30}\text{ bytes} = 3 \times 8 \times 2^{30} = 3 \times 2^{33}\text{ bytes}\). Therefore, the capacity of one sector is: \[\text{Sector size} = \frac{\text{Total capacity}}{\text{Total sectors}} = \frac{3 \times 2^{33}\text{ bytes}}{3 \times 2^{25}} = 2^{33 - 25} = 2^8 = 256\text{ bytes}\] **Answer: 256.**

**1. Understand the B+ tree of order 3 parameters:** Each leaf node holds at most \(2\) keys, and each internal node holds at most \(2\) search keys (and at most \(3\) child pointers). We insert `30, 22, 9, 41, 13, 5, 57` sequentially into an empty B+ tree: **2. Trace each insertion and count the node splits:** 1. **Insert `30`:** Leaf `[30]`. (0 splits) 2. **Insert `22`:** Leaf `[22, 30]`. (0 splits) 3. **Insert `9`:** Leaf `[9, 22, 30]` overflows (> 2 keys) \(\implies\) **Split #1 (leaf split)** into `[9]` and `[22, 30]` (or `[9, 22]` and `[30]`), creating root `[22]`. 4. **Insert `41`:** Goes into the right leaf `[22, 30, 41]`, which overflows \(\implies\) **Split #2 (leaf split)** into `[22]` and `[30, 41]`, pushing `30` up to the root `[22, 30]`. 5. **Insert `13`:** Goes into the first leaf `[9, 13]`. (No split; leaves are `[9, 13]`, `[22]`, `[30, 41]` under root `[22, 30]`.) 6. **Insert `5`:** Goes into the first leaf `[5, 9, 13]`, which overflows \(\implies\) **Split #3 (leaf split)** into `[5]` and `[9, 13]`, pushing `9` up to the root `[9, 22, 30]`. Now the root internal node has 3 keys (4 children), which overflows \(\implies\) **Split #4 (internal/root split)**, promoting `22` to a new root `[22]` with internal children `[9]` and `[30]`. 7. **Insert `57`:** Goes into the rightmost leaf `[30, 41, 57]`, which overflows \(\implies\) **Split #5 (leaf split)** into `[30]` and `[41, 57]`, pushing `41` up into the right internal node `[30, 41]` (which has 2 keys, so no further split). **3. Conclude:** A total of **5** node splits (4 leaf splits + 1 internal root split) occur during the insertion sequence. **Answer: 5.**

**1. Analyze the given functional dependencies on \(R(A, B, C, D, E, F)\):** We are given: \[BC \to F, \quad F \to A, \quad A \to B, \quad D \to E, \quad E \to D\] and the decomposition of \(R\) into \(R_1(A, B, C, F)\) and \(R_2(B, C, D, E)\). **2. Check what can be derived from \(\{BC\}^+\):** Let us compute the attribute closure of \(BC\): - Start with \(\{B, C\}\). - Using \(BC \to F\), add \(F\): \(\{B, C, F\}\). - Using \(F \to A\), add \(A\): \((BC)^+ = \{A, B, C, F\}\). Notice that: 1. Since \(A \in (BC)^+\), **\(BC \to A\) can be derived** from the given FDs! (Statement A is **correct**.) 2. Since \(E \notin (BC)^+\), \(BC \to E\) **cannot** be derived. (Statement B is **incorrect**.) **3. Check whether the decomposition into \(R_1(A, B, C, F)\) and \(R_2(B, C, D, E)\) is lossless:** A binary decomposition into \(R_1\) and \(R_2\) is **lossless** if and only if their common attributes \(R_1 \cap R_2\) form a superkey of \(R_1\) or \(R_2\). Here, \(R_1 \cap R_2 = \{B, C\}\), and we just showed that \((BC)^+ = \{A, B, C, F\} = R_1\)! Since \(BC \to R_1\), **the decomposition is lossless**. (Statement C is **correct** and Statement D is **incorrect**.) **Answer: A, C — BC -> A can be derived from the given functional dependencies, and the decomposition is lossless.** A: **Correct:** By transitivity from \(BC \to F\) and \(F \to A\), we immediately derive \(BC \to A\). B: **Incorrect:** The closure of \(BC\) is \(\{A, B, C, F\}\), which does not reach \(D\) or \(E\). C: **Correct:** Because \(R_1 \cap R_2 = BC\) and \((BC)^+ = \{A, B, C, F\} = R_1\), \(BC\) is a superkey of \(R_1\), guaranteeing a lossless join decomposition. D: **Incorrect:** Since \(R_1 \cap R_2 \to R_1\) holds, the decomposition is lossless, not lossy.

**1. Analyze the conflicting operations in schedule \(S\):** Looking at the operations of \(T_1\) and \(T_2\) in chronological order: - \(T_1\) performs \(r_1(A), w_1(A)\) before \(T_2\) performs \(r_2(A), w_2(A)\) \(\implies\) conflict edge **\(T_1 \to T_2\)** on data item \(A\). - \(T_2\) performs \(r_2(B)\) before \(T_1\) performs \(w_1(B)\) (and \(T_2\) later performs \(w_2(B)\)) \(\implies\) conflict edge **\(T_2 \to T_1\)** (and \(T_1 \to T_2\)) on data item \(B\). **2. Check Serializability and Two-Phase Locking (2PL):** - Because \(T_1\) writes \(A\) before \(T_2\) reads/writes \(A\), while \(T_2\) reads \(B\) before \(T_1\) writes \(B\) (and \(T_2\) writes \(B\) after \(T_1\) writes \(B\)), the precedence graph has a cycle \(T_1 \leftrightarrow T_2\) (and the final state/reads do not match either serial order \(T_1 T_2\) or \(T_2 T_1\)). Thus, **the schedule is NOT serializable** (Statement D is **correct**, C is **incorrect**). - Since every Two-Phase Lockable (2PL) schedule is guaranteed to be conflict serializable, a schedule that is not conflict serializable **cannot be 2-phase lockable** (specifically, \(T_1\) would have to unlock \(A\) before \(T_2\) locks \(A\), and \(T_2\) would have to unlock \(B\) before \(T_1\) locks \(B\) and then re-acquire a lock on \(B\) for its final \(w_2(B)\), violating 2PL). Thus, **the schedule is NOT 2-phase lockable** (Statement B is **correct**, A is **incorrect**). **Answer: B, D — The schedule is not 2-phase lockable, and the schedule is not Serializable.** A: **Incorrect:** In \(T_2\), reading \(B\) before \(w_1(B)\) and writing \(B\) after \(w_1(B)\) would require releasing and re-acquiring a lock on \(B\), which violates 2PL. B: **Correct:** Because \(T_1\)'s write \(w_1(B)\) is sandwiched between \(T_2\)'s \(r_2(B)\) and \(w_2(B)\), no valid 2PL lock/unlock sequence can produce \(S\). C: **Incorrect:** The schedule has a cycle between \(T_1\) and \(T_2\) and is not equivalent to either \(T_1 \to T_2\) or \(T_2 \to T_1\). D: **Correct:** Neither serial schedule \(T_1 T_2\) nor \(T_2 T_1\) is equivalent to \(S\), so \(S\) is not serializable.

**1. Construct the bitmaps for each distinct value of `Team` across the 8 rows:** The `Team` column across rows 1 to 8 (`101` to `108`) is: `[A, B, A, C, B, A, C, B]` - **Bitmap for `Team A`** (`1` where Team is `A`, at rows 1, 3, 6): `10100100` (Statement A is **correct**!) - **Bitmap for `Team B`** (`1` where Team is `B`, at rows 2, 5, 8): `01001001` (Statement B is **correct**!) - **Bitmap for `Team C`** (`1` where Team is `C`, at rows 4, 7): `00010010` **2. Evaluate the bitwise operations in Statements C and D:** - **Statement C (`Team_A OR Team_C`):** \[10100100 \text{ OR } 00010010 = 10110110\] (Statement C is **correct**!) - **Statement D (`NOT Team_A`):** Flipping every bit of `Team_A = 10100100` gives: \[\text{NOT } 10100100 = 01011011 \neq 01011001\] (Notice the 7th bit of `10100100` is `0`, so its bitwise negation has `1` in the 7th position, not `0`. Thus Statement D is **incorrect**.) **Answer: A, B, C — Bitmap for A is 10100100, bitmap for B is 01001001, and Team_A OR Team_C produces 10110110.** A: **Correct:** Rows 1, 3, and 6 belong to Team A, giving the 8-bit vector `10100100`. B: **Correct:** Rows 2, 5, and 8 belong to Team B, giving the 8-bit vector `01001001`. C: **Correct:** Bitwise OR of `10100100` (Team A) and `00010010` (Team C) sets bits 1, 3, 4, 6, and 7, producing `10110110`. D: **Incorrect:** Bitwise NOT of `10100100` is `01011011` (the 7th bit is `1`, not `0`).

**1. Evaluate each of the 4 statements about RAID levels:** - **Statement 1:** *"In RAID 2, the Hamming code is used for parity."* **Correct!** RAID 2 uses bit-level striping with a Hamming error-correcting code across check disks. - **Statement 2:** *"RAID 4 has a striping unit of disk block instead of a single bit."* **Correct!** RAID 4 uses **block-level** striping with a dedicated parity disk. - **Statement 3:** *"The Write performance of RAID 5 is poorer than that of RAID 6."* **Incorrect!** RAID 5 maintains only **1** distributed parity block (requiring 4 disk I/Os per small write), whereas RAID 6 maintains **2** independent distributed parity blocks (P and Q, requiring 6 disk I/Os per small write). Thus, RAID 6 has poorer write performance than RAID 5, not the other way around. - **Statement 4:** *"RAID 10 provides better throughput and latency than all other RAID levels except RAID 0."* **Correct!** RAID 10 (striped mirrors) avoids parity calculation/read-modify-write overhead while striping across mirrored pairs, giving the best performance among all fault-tolerant RAID levels. **Answer: D — Statement 1, 2 and 4 are correct.** A: **Incorrect:** Statement 3 is false because RAID 6 must update two parity blocks per write, making its write performance slower than RAID 5. B: **Incorrect:** Statement 3 is false, and Statements 2 and 4 are also true. C: **Incorrect:** Statement 3 is false while Statement 2 is true. D: **Correct:** Statements 1, 2, and 4 are true, while Statement 3 is false because RAID 5 has better write performance than RAID 6.

**1. Recall the cost formula for Block Nested-Loop Join under worst-case memory availability:** When memory is at its worst case (only 3 buffer blocks available: 1 for the outer relation, 1 for the inner relation, and 1 for the output), for an outer relation with \(b_r\) blocks and an inner relation with \(b_s\) blocks, the total number of block transfers is: \[\text{Total block transfers} = b_r + b_r \times b_s\] (And the number of seeks is \(2 b_r = 2 \times 300 = 600\).) **2. Substitute the given values and solve for \(b_s\):** We are given: - Outer relation (`employee`) size: \(b_r = 300\) blocks - Total block transfers \(= 18300\) Substitute into the block transfer equation: \[300 + 300 \times b_s = 18300\] \[300 \times b_s = 18300 - 300 = 18000\] \[b_s = \frac{18000}{300} = 60\] **Answer: 60.**

**1. Understand Full, Differential, and Incremental backups:** - **Full Backup (Monday):** Copies the entire database as of Monday. - **Differential Backup (Thursday):** Copies all changes made **since the last Full backup** (i.e., everything changed on Tuesday, Wednesday, and Thursday, superseding Tuesday's and Wednesday's incremental backups!). - **Incremental Backups (Friday, Saturday):** Each incremental backup copies only the changes made **since the immediately preceding backup** (of any type). **2. Determine which backup sets are needed after a crash on Sunday (before Sunday's backup):** To restore the database up to Saturday's state: 1. Load **Monday's Full Backup** (1st set). 2. Load **Thursday's Differential Backup** to bring the database up to Thursday in one step (2nd set — skipping Tuesday and Wednesday!). 3. Load **Friday's Incremental Backup** (3rd set). 4. Load **Saturday's Incremental Backup** (4th set). Thus, a minimum of **4** backup sets must be loaded. **Answer: 4.**