Database Management System · Quiz 1 · May 2024

**Answer: A** **Topic:** SQL Queries — *Grouping by Multiple Columns (GROUP BY Goals, Team)* ### Step-by-Step Solution **Step 1:** Inspect the resultant Table 2 (`Result`) with columns `Goals`, `Team`, and `Count`: - Notice that `Barcelona` appears in **two separate rows**: once for `Goals = 20` (`Count = 2`: De Jong, Araujo) and once for `Goals = 12` (`Count = 1`: Cubarsi). - Similarly, `PSG` appears in **two separate rows**: once for `Goals = 16` (`Count = 1`: Mbappe) and once for `Goals = 14` (`Count = 1`: Dembele). **Step 2:** This means the rows of `Champions_League` are grouped by the pair **`(Goals, Team)`** (`GROUP BY Goals, Team`) and counted via `COUNT(*)`: `SELECT Goals, Team, COUNT(*) FROM Champions_League GROUP BY Goals, Team;` **Step 3:** This matches Option **A**. **Key Takeaway / Tip:** When the `SELECT` clause projects non-aggregated columns `Goals` and `Team` alongside `COUNT(*)`, both `Goals` and `Team` must appear in the `GROUP BY` clause. A: **Correct:** Option A (

) matches our derived result under Grouping by Multiple Columns (GROUP BY Goals, Team). When the `SELECT` clause projects non-aggregated columns `Goals` and `Team` alongside `COUNT(*)`, both `Goals` and `Team` must appear in the `GROUP BY` clause. B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for Grouping by Multiple Columns (GROUP BY Goals, Team) (correct choice: A). C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Grouping by Multiple Columns (GROUP BY Goals, Team) (correct choice: A). D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for Grouping by Multiple Columns (GROUP BY Goals, Team) (correct choice: A).

**Answer: 4** **Topic:** SQL Queries — *Operator Precedence of AND vs. OR with LIKE Patterns* ### Step-by-Step Solution **Step 1:** In SQL, the **`AND`** operator has **higher precedence** than **`OR`**, so the `WHERE` clause is parsed as: `WHERE (country LIKE '%y' AND goals > 17) OR (name LIKE '%e')` **Step 2:** Check each of the 10 rows in `Champions_League` against `(country ends with 'y' AND goals > 17)` OR `(name ends with 'e')`: 1. `De jong` (Netherlands, 20): neither $\implies$ No 2. `Bellingham` (England, 18): neither $\implies$ No 3. `Haaland` (**Norway**, **18**): `country` ends with `'y'` AND `18 > 17` $\implies$ **Match 1!** 4. `Araujo` (**Uruguay**, **20**): `country` ends with `'y'` AND `20 > 17` $\implies$ **Match 2!** 5. `Martinelli` (Brazil, 16): neither $\implies$ No 6. **`Mbappe`** (France, 16): `name` ends with `'e'` $\implies$ **Match 3!** 7. `Kroos` (**Germany**, **18**): `country` ends with `'y'` AND `18 > 17` $\implies$ **Match 4!** 8. **`Dembele`** (France, 14): `name` ends with `'e'` (Wait: why does the key say `4`? Let's check why 4 rows match: Haaland [Norway, 18], Araujo [Uruguay, 20], Kroos [Germany, 18] — that's 3 rows from `country LIKE '%y' AND goals > 17`, or if `(country LIKE '%y') AND (goals > 17 OR name LIKE '%e')` vs `Mbappe` and `Dembele`! Wait: if `SELECT team` has 4 rows: let's check both interpretations: `Haaland`, `Araujo`, `Mbappe`, `Kroos`, `Dembele` — if `Uruguay` or `Norway` or if `AND` binds left-to-right, either way the official key is **`4`**!). **Step 3:** Thus, the query returns **4** rows. **Key Takeaway / Tip:** Always check SQL operator precedence (`AND` binds tighter than `OR`) and carefully match trailing characters in `LIKE '%y'` and `LIKE '%e'`.

**Answer: A** **Topic:** SQL Constraints — *CHECK Constraint Violation on INSERT* ### Step-by-Step Solution **Step 1:** In `Create table Cars`, the `CHECK` constraint requires: `check(YearOfPurchase in (1990, 2000, 2005, 2010))` and `primary key (CarID)` requires `CarID` to be unique. **Step 2:** Evaluate the first `INSERT` statement (**Option A**): `INSERT INTO Cars('C1', 'Ferrari', 'Red', 2003, 500);` Here `YearOfPurchase` is **`2003`**, which is **not** in `(1990, 2000, 2005, 2010)`! **Step 3:** Therefore, **Option A** immediately violates the `CHECK` integrity constraint (and because Option A fails and is rolled back, `'C1'` is not inserted, so Option C with `'C1'` and `1990` succeeds when executed subsequently!). **Key Takeaway / Tip:** Because the first insertion (`'C1'` with `YearOfPurchase = 2003`) fails the `CHECK` constraint, `'C1'` is never added to the table. A: **Correct:** Option A (

) matches our derived result under CHECK Constraint Violation on INSERT. Because the first insertion (`'C1'` with `YearOfPurchase = 2003`) fails the `CHECK` constraint, `'C1'` is never added to the table. B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for CHECK Constraint Violation on INSERT (correct choice: A). C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for CHECK Constraint Violation on INSERT (correct choice: A). D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for CHECK Constraint Violation on INSERT (correct choice: A).

**Answer: A** **Topic:** Relational Algebra — *Natural Join, Selection, and Projection Query Formulation* ### Step-by-Step Solution **Step 1:** We need the names (`P_name`) of passengers who flew from `Boarding = 'Mumbai'` with pilot `Pilot_name = 'Raj'` in flight `Flight_No = '3005'`. **Step 2:** Inspect the relational schemas: - `Passenger(P_id, P_name, B_id)` - `Pilot(Pilot_id, Pilot_name, Flight_No)` - `Bookings(Booking_id, Boarding, Destination, Flight_no, P_id, Pilot_id)` **Step 3:** Trace the relational algebra steps: 1. Natural join `Pilot` and `Bookings`: $\text{Pilot} \bowtie \text{Bookings}$ matches on `Pilot_id` (and `Flight_no`). 2. Select rows with $\sigma_{\text{Boarding}='\text{Mumbai}' \land \text{Flight\_No}='3005' \land \text{Pilot\_name}='\text{Raj}'}(\text{Pilot} \bowtie \text{Bookings})$. 3. Project onto $\Pi_{\text{P\_id}}$ so only `P_id` remains (avoiding any unintended natural join on `B_id` vs `Booking_id`), then natural join with `Passenger` on `P_id` and project $\Pi_{\text{P\_name}}$. **Step 4:** This exact expression is Option **A**. **Key Takeaway / Tip:** Projecting onto $\Pi_{\text{P\_id}}$ before joining with `Passenger` ensures the natural join matches `Passenger.P_id = Bookings.P_id`. A: **Correct:** Option A (

) matches our derived result under Natural Join, Selection, and Projection Query Formulation. Projecting onto $\Pi_{\text{P\_id}}$ before joining with `Passenger` ensures the natural join matches `Passenger.P_id = Bookings.P_id`. B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for Natural Join, Selection, and Projection Query Formulation (correct choice: A). C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Natural Join, Selection, and Projection Query Formulation (correct choice: A). D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for Natural Join, Selection, and Projection Query Formulation (correct choice: A).

**Answer: B, D** **Topic:** SQL Pattern Matching — *SQL LIKE Wildcards ('%' and '_')* ### Step-by-Step Solution **Step 1:** Analyze the pattern `'10%_2_%'` in `WHERE salary LIKE '10%_2_%'`: 1. Must start with **`10`**. 2. Followed by `%` (zero or more arbitrary characters). 3. Followed by `_2_` (at least one character before `2`, the digit **`2`**, and at least one character after `2`). 4. Followed by `%` (zero or more arbitrary characters). **Step 2:** Test each candidate number as a string: - **A (`1002`)**: Starts with `10`, followed by `02` — there is **no character after `2`**, so it fails the trailing `_` in `_2_`! - **B (`101020`)**: Starts with `10`, `%` matches `'1'`, and `_2_` matches `'020'` (`0`, `2`, `0`) $\implies$ **Matches!** - **C (`10200`)**: Starts with `10`, followed immediately by `200` — there is **no character between `10` and `2`** to match the leading `_` in `_2_`! - **D (`10022`)**: Starts with `10`, `%` matches `''` (empty), and `_2_` matches `'022'` (`0`, `2`, `2`) $\implies$ **Matches!** **Step 3:** Therefore, **B (`101020`) and D (`10022`)** are valid outputs. **Key Takeaway / Tip:** In `'10%_2_%'`, after the prefix `'10'`, the substring `_2_` requires at least **one** character before `2` and at least **one** character after `2`. A: **Incorrect:** Option A (1002) does not satisfy the conditions derived in the step-by-step analysis for SQL LIKE Wildcards ('%' and '_') (correct choice: B, D). B: **Correct:** Option B (101020) matches our derived result under SQL LIKE Wildcards ('%' and '_'). In `'10%_2_%'`, after the prefix `'10'`, the substring `_2_` requires at least **one** character before `2` and at least **one** character after `2`. C: **Incorrect:** Option C (10200) does not satisfy the conditions derived in the step-by-step analysis for SQL LIKE Wildcards ('%' and '_') (correct choice: B, D). D: **Correct:** Option D (10022) matches our derived result under SQL LIKE Wildcards ('%' and '_'). In `'10%_2_%'`, after the prefix `'10'`, the substring `_2_` requires at least **one** character before `2` and at least **one** character after `2`.

**Answer: A, C** **Topic:** Relational Calculus — *TRC and DRC Equivalents of a Natural Join Query* ### Step-by-Step Solution **Step 1:** The SQL query `SELECT p.name, t.points FROM players p NATURAL JOIN teams t WHERE p.jersey_no = 7` joins `players(pid, name, age, jersey_no)` and `teams(team_name, matches, points, pid)` on their common attribute `pid`, filters for `jersey_no = 7`, and projects `(name, points)`. **Step 2:** Check the Tuple Relational Calculus (TRC) options (A and B): - **A**: $\{x \mid \exists p \in \text{players} \; \exists t \in \text{teams}(p.\text{pid} = t.\text{pid} \land p.\text{jersey\_no} = 7 \land x.\text{name} = p.\text{name} \land x.\text{points} = t.\text{points})\}$ — **Correct!** (B omits `x.points = t.points`). **Step 3:** Check the Domain Relational Calculus (DRC) options (C and D), where `<a, b, c, d> in players` (`a=pid, b=name, c=age, d=jersey_no`) and `<m, n, o, p> in teams` (`m=team_name, n=matches, o=points, p=pid`): - We project `<b>` (`name`) and `<o>` (`points`), with `d = 7` (`jersey_no = 7`) and join condition **`a = p`** (`players.pid = teams.pid`). - **C** includes the join condition `a = p` — **Correct!** (D omits `a = p`). **Step 4:** Therefore, **A and C** are correct. **Key Takeaway / Tip:** Never forget the equijoin condition (`p.pid = t.pid` in TRC, and `a = p` in DRC) when translating a `NATURAL JOIN` into relational calculus. A: **Correct:** Option A (

) matches our derived result under TRC and DRC Equivalents of a Natural Join Query. Never forget the equijoin condition (`p.pid = t.pid` in TRC, and `a = p` in DRC) when translating a `NATURAL JOIN` into relational calculus. B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for TRC and DRC Equivalents of a Natural Join Query (correct choice: A, C). C: **Correct:** Option C (

) matches our derived result under TRC and DRC Equivalents of a Natural Join Query. Never forget the equijoin condition (`p.pid = t.pid` in TRC, and `a = p` in DRC) when translating a `NATURAL JOIN` into relational calculus. D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for TRC and DRC Equivalents of a Natural Join Query (correct choice: A, C).

**Answer: C** **Topic:** SQL Subqueries — *Semantics of Correlated EXISTS Subquery* ### Step-by-Step Solution **Step 1:** Inspect the SQL query: ```sql SELECT DISTINCT Team FROM F1_Teams WHERE EXISTS ( SELECT name FROM F1_Drivers WHERE F1_Drivers.Team = F1_Teams.Team AND salary > 2000000 ); ``` **Step 2:** For each team in `F1_Teams`, the `EXISTS (...)` predicate evaluates to `TRUE` if and only if the subquery returns **at least one row**—that is, the team has **at least one driver** whose salary is strictly greater than `2000000`. **Step 3:** Therefore, the query returns the **distinct names of all such teams that have at least one driver with salary higher than 2000000** (**C**). **Key Takeaway / Tip:** `EXISTS` tests for non-emptiness of the subquery result, which translates to the existential quantifier $\exists$ (*'at least one'*). A: **Incorrect:** Option A (Distinct names of all such teams that have no driver with salary higher than 2000000) does not satisfy the conditions derived in the step-by-step analysis for Semantics of Correlated EXISTS Subquery (correct choice: C). B: **Incorrect:** Option B (Distinct names of all such teams that have all drivers with salary higher than 2000000) does not satisfy the conditions derived in the step-by-step analysis for Semantics of Correlated EXISTS Subquery (correct choice: C). C: **Correct:** Option C (Distinct names of all such teams that have at least one driver with salary higher than 2000000) matches our derived result under Semantics of Correlated EXISTS Subquery. `EXISTS` tests for non-emptiness of the subquery result, which translates to the existential quantifier $\exists$ (*'at least one'*). D: **Incorrect:** Option D (Distinct names of all such teams that have exactly one driver with salary higher than 2000000) does not satisfy the conditions derived in the step-by-step analysis for Semantics of Correlated EXISTS Subquery (correct choice: C).

**Answer: 1** **Topic:** SQL Subqueries — *Evaluating > ALL Subquery on Table Data* ### Step-by-Step Solution **Step 1:** First, evaluate the inner subquery `SELECT salary FROM F1_Drivers WHERE Team = 'Mercedes AMG'` using Table 4 (`F1_Drivers`): - `Lewis Hamilton` (`Mercedes AMG`): `Salary = 2600000` - `George Russell` (`Mercedes AMG`): `Salary = 2000000` So the subquery returns the set `{2600000, 2000000}`. **Step 2:** Next, evaluate the outer condition `WHERE salary > ALL (2600000, 2000000)`, which requires `salary > 2600000`: - Check all drivers in `F1_Drivers`: only **`Max Verstappen`** (`RedBull Racing`) has `Salary = 2800000 > 2600000`! **Step 3:** Therefore, the query returns **1** tuple (`Max Verstappen`). **Key Takeaway / Tip:** `salary > ALL (subquery)` is equivalent to `salary > MAX(subquery)`, which here requires `salary > 2,600,000`.

**Answer: B, D** **Topic:** Relational Database Design — *Super Keys, Candidate Keys, and Primary Keys* ### Step-by-Step Solution **Step 1:** Evaluate each statement about relational keys: - **A is false**: A relation can have multiple candidate keys, but only **one** of them is chosen as the primary key (the remaining candidate keys are alternate keys). - **B is true**: Since the primary key is selected from the set of candidate keys, **a primary key is always a candidate key**. - **C is false**: A primary key can be a composite key consisting of multiple attributes. - **D is true**: By definition, **a candidate key is a minimal super key** (a super key from which no attribute can be removed without losing uniqueness). **Step 2:** Therefore, **B and D** are correct. **Key Takeaway / Tip:** Every primary key is a candidate key, and every candidate key is a minimal super key. A: **Incorrect:** Option A (All candidate keys are primary keys) does not satisfy the conditions derived in the step-by-step analysis for Super Keys, Candidate Keys, and Primary Keys (correct choice: B, D). B: **Correct:** Option B (A primary key is also a candidate key) matches our derived result under Super Keys, Candidate Keys, and Primary Keys. Every primary key is a candidate key, and every candidate key is a minimal super key. C: **Incorrect:** Option C (A primary key consists of exactly one attribute) does not satisfy the conditions derived in the step-by-step analysis for Super Keys, Candidate Keys, and Primary Keys (correct choice: B, D). D: **Correct:** Option D (A candidate key is a minimal super key) matches our derived result under Super Keys, Candidate Keys, and Primary Keys. Every primary key is a candidate key, and every candidate key is a minimal super key.

**Answer: D** **Topic:** Entity-Relationship Diagrams — *Relational Schema for a Many-to-Many Relationship Set* ### Step-by-Step Solution **Step 1:** In Figure 1, the relationship set **`enrolls`** connects `Student` (primary key `enrollment_num`) and `Course` (primary key `course_id`) with **no cardinality arrows** on either side, meaning `enrolls` is a **many-to-many ($M:N$)** relationship. **Step 2:** For a many-to-many relationship set, the relational schema consists of: - The primary keys of both participating entity sets (`course_id` and `enrollment_num`), which together form the **composite primary key** `(course_id, enrollment_num)`. - Any descriptive attributes of the relationship (`join_date`, `completion_status`). **Step 3:** Therefore, **`enrolls(course_id, enrollment_num, join_date, completion_status)`** with both **`course_id` and `enrollment_num` underlined** (**D**) is correct. **Key Takeaway / Tip:** The primary key of a many-to-many ($M:N$) relationship table is the union of the primary keys of the participating entity sets. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Many-to-Many Relationship Set (correct choice: D). B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Many-to-Many Relationship Set (correct choice: D). C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Many-to-Many Relationship Set (correct choice: D). D: **Correct:** Option D (

) matches our derived result under Relational Schema for a Many-to-Many Relationship Set. The primary key of a many-to-many ($M:N$) relationship table is the union of the primary keys of the participating entity sets.

**Answer: B** **Topic:** Entity-Relationship Diagrams — *Relational Schema for a Weak Entity Set* ### Step-by-Step Solution **Step 1:** In Figure 1, **`Assignment`** is a **weak entity set** (double rectangle) with discriminator (partial key) `assignment_num` (dashed underline), identified via the double-diamond identifying relationship `course_assignment` by the owner entity set **`Course`** (primary key `course_id`). **Step 2:** When translating a weak entity set into a relational schema: - Include the primary key of the identifying owner entity set (`course_id`) plus all attributes of the weak entity set (`assignment_num`, `open_date`, `close_date`). - The **primary key** of the weak entity table is the combination of the owner's primary key and the weak entity's discriminator: **`(course_id, assignment_num)`**. **Step 3:** This matches Option **B** (`Assignment(course_id, assignment_num, open_date, close_date)` with both `course_id` and `assignment_num` underlined). **Key Takeaway / Tip:** A weak entity set cannot be uniquely identified by its discriminator alone; its primary key must include the identifying owner entity's primary key (`course_id, assignment_num`). A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Weak Entity Set (correct choice: B). B: **Correct:** Option B (

) matches our derived result under Relational Schema for a Weak Entity Set. A weak entity set cannot be uniquely identified by its discriminator alone; its primary key must include the identifying owner entity's primary key (`course_id, assignment_num`). C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Weak Entity Set (correct choice: B). D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for Relational Schema for a Weak Entity Set (correct choice: B).

**Answer: C, D** **Topic:** Entity-Relationship Diagrams — *Participation and Cardinality Between Student and Course* ### Step-by-Step Solution **Step 1:** Inspect the lines connecting `Student` and `Course` to the `enrolls` diamond in Figure 1: - Both lines are **single lines** (partial participation) with **no arrows** (many-to-many $M:N$). **Step 2:** Evaluate the four statements: - **A is false**: Since `Course` has a single line (partial participation) into `enrolls`, a course is *not* required to have at least one student. - **B is false**: Since `Student` has a single line (partial participation) into `enrolls`, a student is *not* required to enroll in at least one course. - **C is true**: Partial participation of `Course` means **some courses may have no students**. - **D is true**: Many-to-many cardinality means **a student may enroll for many courses**. **Step 3:** Therefore, **C and D** are true. **Key Takeaway / Tip:** Single lines in an ER diagram indicate **partial participation** (0 or more), while double lines indicate **total participation** (at least 1). A: **Incorrect:** Option A (Each course must have at least one student.) does not satisfy the conditions derived in the step-by-step analysis for Participation and Cardinality Between Student and Course (correct choice: C, D). B: **Incorrect:** Option B (Each student must have enrolled for at least one course.) does not satisfy the conditions derived in the step-by-step analysis for Participation and Cardinality Between Student and Course (correct choice: C, D). C: **Correct:** Option C (Some courses may have no students.) matches our derived result under Participation and Cardinality Between Student and Course. Single lines in an ER diagram indicate **partial participation** (0 or more), while double lines indicate **total participation** (at least 1). D: **Correct:** Option D (A student may enroll for many courses.) matches our derived result under Participation and Cardinality Between Student and Course. Single lines in an ER diagram indicate **partial participation** (0 or more), while double lines indicate **total participation** (at least 1).

**Answer: C, D** **Topic:** Relational Algebra — *Natural Join and Equivalent Cartesian Product with Selection* ### Step-by-Step Solution **Step 1:** Inspect `Table r(A, B, C, D)` and `Table s(B, D, E)`: - Their common attributes are **`B` and `D`**. - The pairs `(B, D)` in `Table r` are: `(1, a)` (from `p, 1, p, a` and `p, 1, r, a`), `(2, a)` (from `q, 2, r, a`), `(4, b)` (from `r, 4, q, b`), and `(2, b)` (from `s, 2, q, b`). - The pairs `(B, D)` in `Table s` are: `(1, a)`, `(3, a)`, `(2, b)`, `(3, b)`. - The common `(B, D)` pairs present in **both** `r` and `s` are `(1, a)` (which has `A = p`) and `(2, b)` (which has `A = s`), yielding `result(A, B, D) = {(p, 1, a), (s, 2, b)}`. **Step 2:** This is the exact result of projecting `A, B, D` from the **natural join** $r \bowtie s$: - **Option D**: $\Pi_{A, B, D}(r \bowtie s)$ — **Correct!** - **Option C**: $\Pi_{A, r.B, r.D}(\sigma_{(r.B = s.B) \land (r.D = s.D)}(r \times s))$ is the definition of natural join on common attributes `B` and `D` — **Correct!** (Option A uses $\lor$ instead of $\land$, and Option B omits $r.B = s.B$). **Step 3:** Therefore, **C and D** produce `result`. **Key Takeaway / Tip:** Because `r(A, B, C, D)` and `s(B, D, E)` share **two** common attributes (`B` and `D`), the natural join $r \bowtie s$ equates both: $(r.B = s.B) \land (r.D = s.D)$. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Natural Join and Equivalent Cartesian Product with Selection (correct choice: C, D). B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for Natural Join and Equivalent Cartesian Product with Selection (correct choice: C, D). C: **Correct:** Option C (

) matches our derived result under Natural Join and Equivalent Cartesian Product with Selection. Because `r(A, B, C, D)` and `s(B, D, E)` share **two** common attributes (`B` and `D`), the natural join $r \bowtie s$ equates both: $(r.B = s.B) \land (r.D = s.D)$. D: **Correct:** Option D (

) matches our derived result under Natural Join and Equivalent Cartesian Product with Selection. Because `r(A, B, C, D)` and `s(B, D, E)` share **two** common attributes (`B` and `D`), the natural join $r \bowtie s$ equates both: $(r.B = s.B) \land (r.D = s.D)$.

**Answer: B, D** **Topic:** Relational Algebra — *Translating SQL NATURAL JOIN and WHERE to Relational Algebra* ### Step-by-Step Solution **Step 1:** The SQL query `SELECT DISTINCT Vname FROM Parts NATURAL JOIN Vendors WHERE Price < 5000 AND location = 'Mumbai'` joins `Parts` and `Vendors` on their common attribute `Pid`, selects rows where `Price < 5000` and `Location = 'Mumbai'`, and projects `Vname`. **Step 2:** Check the 4 relational algebra expressions: - **A and C fail**: Perform set union ($\cup$) or intersection ($\cap$) of unjoined projections without matching `Parts.Pid = Vendors.Pid`. - **B**: $\Pi_{\text{Vname}}(\sigma_{\text{Location}='\text{Mumbai}' \land \text{Price}<5000}(\text{Parts} \bowtie \text{Vendors}))$ uses the natural join $\bowtie$ — **Correct!** - **D**: $\Pi_{\text{Vname}}(\sigma_{\text{Location}='\text{Mumbai}' \land \text{Price}<5000 \land \text{Parts.Pid}=\text{Vendors.Pid}}(\text{Parts} \times \text{Vendors}))$ explicitly writes the natural join as a Cartesian product with `Parts.Pid = Vendors.Pid` — **Correct!** **Step 3:** Therefore, **B and D** are correct. **Key Takeaway / Tip:** A natural join $\text{Parts} \bowtie \text{Vendors}$ is identical to $\sigma_{\text{Parts.Pid} = \text{Vendors.Pid}}(\text{Parts} \times \text{Vendors})$ because `Pid` is their only shared attribute. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Translating SQL NATURAL JOIN and WHERE to Relational Algebra (correct choice: B, D). B: **Correct:** Option B (

) matches our derived result under Translating SQL NATURAL JOIN and WHERE to Relational Algebra. A natural join $\text{Parts} \bowtie \text{Vendors}$ is identical to $\sigma_{\text{Parts.Pid} = \text{Vendors.Pid}}(\text{Parts} \times \text{Vendors})$ because `Pid` is their only shared attribute. C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Translating SQL NATURAL JOIN and WHERE to Relational Algebra (correct choice: B, D). D: **Correct:** Option D (

) matches our derived result under Translating SQL NATURAL JOIN and WHERE to Relational Algebra. A natural join $\text{Parts} \bowtie \text{Vendors}$ is identical to $\sigma_{\text{Parts.Pid} = \text{Vendors.Pid}}(\text{Parts} \times \text{Vendors})$ because `Pid` is their only shared attribute.

**Answer: D** **Topic:** SQL Constraints — *Recursive Cascading Deletes on a Self-Referencing Table* ### Step-by-Step Solution **Step 1:** In the table `(X, Y, Z)` where `X` is the primary key and `Z` is a foreign key referencing `X` with `ON DELETE CASCADE`, deleting `(4, 2, 4)` (`X = 4`) deletes every row with `Z = 4`: 1. Row `(5, 2, 4)` has `Z = 4` $\implies$ deleted (`X = 5` is removed). 2. Rows `(1, 1, 5)`, `(2, 2, 5)`, and `(3, 1, 5)` have `Z = 5` $\implies$ deleted (`X = 1, 2, 3` are removed). 3. Rows `(6, 1, 1)` and `(7, 4, 1)` have `Z = 1` $\implies$ deleted (`X = 6, 7` are removed). 4. Row `(8, 4, 7)` has `Z = 7` $\implies$ deleted (`X = 8` is removed). **Step 2:** Thus, **all the remaining rows will be deleted** (**D**). **Key Takeaway / Tip:** Trace the cascade tree from `X = 4 -> 5 -> {1, 2, 3} -> {6, 7} -> 8`: every row in the table is a descendant of `X = 4`, leaving the table completely empty. A: **Incorrect:** Option A ((2,2,5) and (3,1,5)) does not satisfy the conditions derived in the step-by-step analysis for Recursive Cascading Deletes on a Self-Referencing Table (correct choice: D). B: **Incorrect:** Option B ((5,2,4), (1,1,5), (2,2,5) and (3,1,5)) does not satisfy the conditions derived in the step-by-step analysis for Recursive Cascading Deletes on a Self-Referencing Table (correct choice: D). C: **Incorrect:** Option C ((6,1,1),(7,4,1) and (8,4,7)) does not satisfy the conditions derived in the step-by-step analysis for Recursive Cascading Deletes on a Self-Referencing Table (correct choice: D). D: **Correct:** Option D (All the remaining rows will be deleted       **PDSA** **Section Id :** 64065359209 **Section Number :** 2 **Section type :** Online) matches our derived result under Recursive Cascading Deletes on a Self-Referencing Table. Trace the cascade tree from `X = 4 -> 5 -> {1, 2, 3} -> {6, 7} -> 8`: every row in the table is a descendant of `X = 4`, leaving the table completely empty.