Database Management System · Quiz 1 · Sep 2024

**Answer: 2** **Topic:** Hashing — *Linear Probing Collision Resolution* ### Step-by-Step Solution **Step 1:** Insert the keys `6, 16, 80, 91, 145` in order into a 10-bucket hash table (indices `0` to `9`) using $h(k) = k \bmod 10$ and linear probing: - `6`: $6 \bmod 10 = 6 \implies$ placed at slot **`6`**. - `16`: $16 \bmod 10 = 6$ (occupied) $\to$ probes to slot **`7`**. - `80`: $80 \bmod 10 = 0 \implies$ placed at slot **`0`**. - `91`: $91 \bmod 10 = 1 \implies$ placed at slot **`1`**. - `145`: $145 \bmod 10 = 5 \implies$ placed at slot **`5`**. **Step 2:** Now insert `190`: - Home slot: $190 \bmod 10 = 0$ (occupied by `80`). - Probe +1: slot `1` (occupied by `91`). - Probe +2: slot **`2`** (free!). **Step 3:** Thus, `190` is inserted at location **2**. **Key Takeaway / Tip:** Because slots `0` and `1` are already taken by `80` and `91`, linear probing steps from `0 -> 1 -> 2` and places `190` at index `2`.

**Answer: C** **Topic:** Query Processing — *Stages of Relational Query Processing* ### Step-by-Step Solution **Step 1:** Recall the standard pipeline for executing a declarative SQL query in a DBMS: 1. **Parser and Translator**: Checks syntax/semantics and translates the SQL query into an internal relational algebra expression. 2. **Optimizer**: Explores equivalent execution plans using cost statistics and selects the lowest-cost execution plan. 3. **Evaluation Engine**: Executes the chosen query execution plan against the database and returns the **Query Output**. **Step 2:** Thus, the correct sequence is **Query $\to$ Parser and Translator $\to$ Optimizer $\to$ Evaluation Engine $\to$ Query Output**, which matches **C**. **Key Takeaway / Tip:** A query must be parsed and translated into relational algebra *before* the query optimizer can generate an execution plan for the evaluation engine. A: **Incorrect:** Option A (Query → Parser and Translator → Evaluation Engine → Optimizer → Query Output) does not satisfy the conditions derived in the step-by-step analysis for Stages of Relational Query Processing (correct choice: C). B: **Incorrect:** Option B (Query → Optimizer → Evaluation Engine → Parser and Translator → Query Output) does not satisfy the conditions derived in the step-by-step analysis for Stages of Relational Query Processing (correct choice: C). C: **Correct:** Option C (Query → Parser and Translator → Optimizer → Evaluation Engine → Query Output) matches our derived result under Stages of Relational Query Processing. A query must be parsed and translated into relational algebra *before* the query optimizer can generate an execution plan for the evaluation engine. D: **Incorrect:** Option D (Query → Evaluation Engine → Parser and Translator → Optimizer → Query Output) does not satisfy the conditions derived in the step-by-step analysis for Stages of Relational Query Processing (correct choice: C).

**Answer: B** **Topic:** Database Architecture — *Three-Schema Architecture (Physical, Logical, View Levels)* ### Step-by-Step Solution **Step 1:** Match each level of data abstraction in the ANSI/SPARC three-level architecture with its definition: - **1. Physical Level**: Describes *how* data is actually stored on physical storage media (files, indices, blocks) $\to$ **B** (`1-B`). - **2. Logical Level**: Defines *what* data is stored in the database, including the schema and relationships between tables $\to$ **A** (`2-A`). - **3. View Level**: Provides different external perspectives of the database tailored to specific user groups $\to$ **C** (`3-C`). **Step 2:** Therefore, **1-B, 2-A, 3-C** (**B**) is correct. **Key Takeaway / Tip:** Physical = *how* data is stored on disk (`B`); Logical = *what* tables/relationships exist (`A`); View = customized user views (`C`). A: **Incorrect:** Option A (1-A, 2-B, 3-C) does not satisfy the conditions derived in the step-by-step analysis for Three-Schema Architecture (Physical, Logical, View Levels) (correct choice: B). B: **Correct:** Option B (1-B, 2-A, 3-C) matches our derived result under Three-Schema Architecture (Physical, Logical, View Levels). Physical = *how* data is stored on disk (`B`); Logical = *what* tables/relationships exist (`A`); View = customized user views (`C`). C: **Incorrect:** Option C (1-B, 2-C, 3-A) does not satisfy the conditions derived in the step-by-step analysis for Three-Schema Architecture (Physical, Logical, View Levels) (correct choice: B). D: **Incorrect:** Option D (1-C, 2-A, 3-B) does not satisfy the conditions derived in the step-by-step analysis for Three-Schema Architecture (Physical, Logical, View Levels) (correct choice: B).

**Answer: A** **Topic:** SQL Queries — *Cartesian Product (Cross Join) with a WHERE Clause* ### Step-by-Step Solution **Step 1:** Inspect the tables: - `Employee` has **4 columns** (`Emp_ID, Name, Dept_ID, Salary`) and 3 rows, of which **2 rows** have `salary > 65000` (`Mark` with `80000` and `Rory` with `90000`). - `Department` has **2 columns** (`Dept_ID, Dept_Name`) and **2 rows** (`Forensics` and `Cyber Crime`). **Step 2:** Analyze `SELECT * FROM Employee, Department WHERE salary > 65000`: - Because there is **no join condition** (`Employee.Dept_ID = Department.Dept_ID`) in the `WHERE` clause, `FROM Employee, Department` forms the **Cartesian product** of the 2 qualifying `Employee` rows with all 2 `Department` rows: $$\text{Rows} = 2 \times 2 = 4\text{ rows}$$ - `SELECT *` returns all columns from both tables: $$\text{Columns} = 4 + 2 = 6\text{ columns}$$ **Step 3:** Therefore, the query returns **4 rows and 6 columns** (**A**). **Key Takeaway / Tip:** Always check whether a comma-join `FROM Employee, Department` includes an equality join predicate in the `WHERE` clause; without one, it computes a full Cartesian product! A: **Correct:** Option A (4 rows and 6 columns) matches our derived result under Cartesian Product (Cross Join) with a WHERE Clause. Always check whether a comma-join `FROM Employee, Department` includes an equality join predicate in the `WHERE` clause; without one, it computes a full Cartesian product! B: **Incorrect:** Option B (2 rows and 5 columns) does not satisfy the conditions derived in the step-by-step analysis for Cartesian Product (Cross Join) with a WHERE Clause (correct choice: A). C: **Incorrect:** Option C (4 rows and 5 columns) does not satisfy the conditions derived in the step-by-step analysis for Cartesian Product (Cross Join) with a WHERE Clause (correct choice: A). D: **Incorrect:** Option D (2 rows and 6 columns) does not satisfy the conditions derived in the step-by-step analysis for Cartesian Product (Cross Join) with a WHERE Clause (correct choice: A).

**Answer: C** **Topic:** SQL Queries — *Correlated Subquery for Top-k Ranking* ### Step-by-Step Solution **Step 1:** Examine the correlated `WHERE` condition for a candidate student `S`: `WHERE (SELECT count(*) FROM Students AS T WHERE T.Total_Marks > S.Total_Marks) < 3` **Step 2:** Count how many students `T` have strictly higher marks than `S`: - For the **1st highest** mark: `0` students have higher marks ($0 < 3 \implies$ **Included**). - For the **2nd highest** mark: `1` student has higher marks ($1 < 3 \implies$ **Included**). - For the **3rd highest** mark: `2` students have higher marks ($2 < 3 \implies$ **Included**). - For the **4th highest** mark: `3` students have higher marks ($3 \not< 3 \implies$ Excluded). **Step 3:** Thus, the query returns the **names of the students with the first three highest marks** (**C**). **Key Takeaway / Tip:** The condition `count(T.marks > S.marks) < k` selects the top $k$ highest-scoring rows (counts $0, 1, \dots, k-1$), whereas `= k - 1` selects only the exact $k$-th highest row. A: **Incorrect:** Option A (Names of the students with the first four highest marks) does not satisfy the conditions derived in the step-by-step analysis for Correlated Subquery for Top-k Ranking (correct choice: C). B: **Incorrect:** Option B (Name of the student with the 4th highest mark) does not satisfy the conditions derived in the step-by-step analysis for Correlated Subquery for Top-k Ranking (correct choice: C). C: **Correct:** Option C (Names of the students with the first three highest marks) matches our derived result under Correlated Subquery for Top-k Ranking. The condition `count(T.marks > S.marks) < k` selects the top $k$ highest-scoring rows (counts $0, 1, \dots, k-1$), whereas `= k - 1` selects only the exact $k$-th highest row. D: **Incorrect:** Option D (Name of the student with the 3rd highest mark) does not satisfy the conditions derived in the step-by-step analysis for Correlated Subquery for Top-k Ranking (correct choice: C).

**Answer: D** **Topic:** SQL Queries — *Equivalence of > ALL and NOT EXISTS for Maximum Comparison* ### Step-by-Step Solution **Step 1:** Our goal is to find `F.ID` of faculty members whose salary is strictly greater than the highest salary in the `'Computer Science'` department ($F.\text{salary} > \max_{T \in \text{CS}} T.\text{salary}$): **Step 2:** Verify **Query Q1**: - `WHERE F.salary > ALL (SELECT DISTINCT salary FROM Faculty AS T WHERE T.department = 'Computer Science')` checks that `F.salary` is strictly greater than every salary in Computer Science, which is true if and only if `F.salary` exceeds the maximum salary in Computer Science — **Q1 is correct**. **Step 3:** Verify **Query Q2**: - `WHERE NOT EXISTS (SELECT * FROM Faculty AS T WHERE T.department = 'Computer Science' AND T.salary >= F.salary)` checks that there is **no** faculty member in Computer Science whose salary is $\ge F.\text{salary}$, which is logically equivalent to saying every Computer Science faculty member has salary $< F.\text{salary}$ — **Q2 is also correct**. **Step 4:** Therefore, **Both Q1 and Q2 are correct** (**D**). **Key Takeaway / Tip:** By De Morgan's Law for quantifiers, $\forall t \in S, \; x > t$ (`> ALL`) is logically identical to $\neg \exists t \in S, \; t \ge x$ (`NOT EXISTS ... >=`). A: **Incorrect:** Option A (Q1 is correct but Q2 is wrong) does not satisfy the conditions derived in the step-by-step analysis for Equivalence of > ALL and NOT EXISTS for Maximum Comparison (correct choice: D). B: **Incorrect:** Option B (Q2 is correct but Q1 is wrong) does not satisfy the conditions derived in the step-by-step analysis for Equivalence of > ALL and NOT EXISTS for Maximum Comparison (correct choice: D). C: **Incorrect:** Option C (Both Q1 and Q2 are wrong) does not satisfy the conditions derived in the step-by-step analysis for Equivalence of > ALL and NOT EXISTS for Maximum Comparison (correct choice: D). D: **Correct:** Option D (Both Q1 and Q2 are correct) matches our derived result under Equivalence of > ALL and NOT EXISTS for Maximum Comparison. By De Morgan's Law for quantifiers, $\forall t \in S, \; x > t$ (`> ALL`) is logically identical to $\neg \exists t \in S, \; t \ge x$ (`NOT EXISTS ... >=`).

**Answer: A, B, D** **Topic:** Entity-Relationship Diagrams — *Cardinality and Participation Constraints in ER Diagrams* ### Step-by-Step Solution **Step 1:** Read the cardinality arrows and double-line (total participation) constraints in the given ER diagram: - **`Employee` — `Works On` — `Project`**: There are no arrows (many-to-many $M:N$), so **an employee can work on more than one project** (**A is true**), and a project can have multiple employees (who may belong to different departments, so **B is true**). - **`Dependent` — `Dependent of` — `Employee`**: `Dependent` is connected to `Dependent of` with a **double line** (total participation) and an arrow pointing to `Employee`, meaning every dependent **must** be associated with an employee (**C is false**). - **`Employee` — `Works In` — `Department`**: Both `Employee` and `Department` have **double lines** (total participation) into `Works In`, so **every department must have at least one employee working in it** (**D is true**). **Step 2:** Therefore, **A, B, and D** are correct. **Key Takeaway / Tip:** In standard Silberschatz/Korth ER notation, a **double line** denotes total participation (every entity in that set must participate in at least one relationship instance). A: **Correct:** Option A (An employee can work on more than one project.) matches our derived result under Cardinality and Participation Constraints in ER Diagrams. In standard Silberschatz/Korth ER notation, a **double line** denotes total participation (every entity in that set must participate in at least one relationship instance). B: **Correct:** Option B (A project can have employees from more than one department, working on it.) matches our derived result under Cardinality and Participation Constraints in ER Diagrams. In standard Silberschatz/Korth ER notation, a **double line** denotes total participation (every entity in that set must participate in at least one relationship instance). C: **Incorrect:** Option C (A dependent, who is not dependent on any employee, can exist.) does not satisfy the conditions derived in the step-by-step analysis for Cardinality and Participation Constraints in ER Diagrams (correct choice: A, B, D). D: **Correct:** Option D (Every department must have at least one employee working in it.) matches our derived result under Cardinality and Participation Constraints in ER Diagrams. In standard Silberschatz/Korth ER notation, a **double line** denotes total participation (every entity in that set must participate in at least one relationship instance).

**Answer: C, D** **Topic:** SQL DDL and Constraints — *Referenced Column Requirements for a FOREIGN KEY* ### Step-by-Step Solution **Step 1:** In the `nominee` table, `FOREIGN KEY (ins_id) REFERENCES insurance(ins_id) ON DELETE CASCADE` references `insurance(ins_id)`. **Step 2:** In SQL, the target column of a `FOREIGN KEY` reference **must be a candidate key** of the referenced table—meaning it must be declared with either a **`PRIMARY KEY`** constraint or a **`UNIQUE`** constraint: - **A**: `ins_id VARCHAR(20)` has neither `PRIMARY KEY` nor `UNIQUE` (Invalid). - **B**: `ins_id VARCHAR NOT NULL(20)` is invalid syntax and lacks `PRIMARY KEY`/`UNIQUE` (Invalid). - **C**: `ins_id VARCHAR(20) PRIMARY KEY` — **Valid!** - **D**: `ins_id VARCHAR(20) UNIQUE` — **Valid!** **Step 3:** Therefore, **C and D** can be used to create the `insurance` table. **Key Takeaway / Tip:** A foreign key can only reference a column (or set of columns) that is constrained to be `PRIMARY KEY` or `UNIQUE` in the parent table. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Referenced Column Requirements for a FOREIGN KEY (correct choice: C, D). B: **Incorrect:** Option B (

) does not satisfy the conditions derived in the step-by-step analysis for Referenced Column Requirements for a FOREIGN KEY (correct choice: C, D). C: **Correct:** Option C (

) matches our derived result under Referenced Column Requirements for a FOREIGN KEY. A foreign key can only reference a column (or set of columns) that is constrained to be `PRIMARY KEY` or `UNIQUE` in the parent table. D: **Correct:** Option D (

) matches our derived result under Referenced Column Requirements for a FOREIGN KEY. A foreign key can only reference a column (or set of columns) that is constrained to be `PRIMARY KEY` or `UNIQUE` in the parent table.

**Answer: 2** **Topic:** Relational Algebra — *Selection, Natural Join, and Projection Tuple Count* ### Step-by-Step Solution **Step 1:** Evaluate the relational algebra expression $\Pi_{\text{Title}}(\sigma_{\text{Genre}='\text{Fiction}'}(\text{Book}) \bowtie \sigma_{\text{Name}='\text{Jane Austen}'}(\text{Author}))$ step by step: 1. $\sigma_{\text{Name}='\text{Jane Austen}'}(\text{Author})$ selects the row `(AuthorID = 103, Name = 'Jane Austen')` from Table 2. 2. $\sigma_{\text{Genre}='\text{Fiction}'}(\text{Book})$ selects the rows with `Genre = 'Fiction'` from Table 1: BookID `2` (`Pride and Prejudice`, `AuthorID = 103`), BookID `3` (`The Great Gatsby`, `AuthorID = 104`), and BookID `5` (`The Catcher in the Rye`, `AuthorID = 103`). 3. Joining on `AuthorID = 103` keeps BookID `2` (`Pride and Prejudice`) and BookID `5` (`The Catcher in the Rye`). **Step 2:** Projecting onto `Title` yields **2** distinct titles (`'Pride and Prejudice'` and `'The Catcher in the Rye'`), so **2** tuples are returned. **Key Takeaway / Tip:** Filter each table first (`AuthorID = 103` and `Genre = 'Fiction'`) before matching on the common attribute `AuthorID`.

**Answer: 7** **Topic:** SQL Joins — *Maximum and Minimum Rows in a LEFT OUTER JOIN* ### Step-by-Step Solution **Step 1:** In `Instructor(InsID, ...)` (`12` rows, where `InsID` is the primary key, so all 12 `InsID` values in `Instructor` are distinct) and `Teaches(InsID, CID, Semester, Year)` (`8` rows), analyze `Instructor LEFT OUTER JOIN Teaches ON Instructor.InsID = Teaches.InsID`: **Step 2:** Find the **maximum** number of rows $A$: - Each of the $8$ rows in `Teaches` can match at most $1$ instructor in `Instructor` (since `Instructor.InsID` is unique). - To maximize the total output rows, all $8$ rows of `Teaches` match the **same single instructor** (producing $8$ joined rows for that 1 instructor), while the remaining $12 - 1 = 11$ instructors have no matches in `Teaches` and each produce $1$ `NULL`-padded row: $$A = 8 + (12 - 1) = 19\text{ rows}$$ **Step 3:** Find the **minimum** number of rows $B$: - To minimize the total output rows, the $8$ rows of `Teaches` match $8$ **distinct** instructors in `Instructor` (producing $1$ row for each of those $8$ instructors), while the remaining $12 - 8 = 4$ instructors produce $1$ `NULL`-padded row each: $$B = 8 + (12 - 8) = 12\text{ rows}$$ **Step 4:** Compute $A - B$: $$A - B = 19 - 12 = 7$$ **Key Takeaway / Tip:** In a `LEFT OUTER JOIN` where the left table has $m = 12$ unique keys and the right table has $n = 8$ foreign-key rows, the row count ranges from $B = \max(m, n) = 12$ (spread across $8$ keys) to $A = n + (m - 1) = 19$ (concentrated on $1$ key), so $A - B = n - 1 = 8 - 1 = 7$!

**Answer: A, C** **Topic:** SQL Queries — *Pattern Matching (LIKE), GROUP BY, and Joins* ### Step-by-Step Solution **Step 1:** Match each question requirement to the correct SQL query: 1. **Names starting with `'A'` and having at least 4 characters**: `'A'` followed by at least 3 characters (`___`) and zero or more additional characters (`%`) is written `LIKE 'A___%'` $\implies$ **Query `a`** (`1-a`; whereas `LIKE 'A___'` in `b` matches *exactly* 4 characters). 2. **Number of players in each team**: Requires grouping by `TeamID` (`GROUP BY TeamID`) $\implies$ **Query `d`** (`2-d`). 3. **Team names that have players with last name `'Smith'`**: Both the explicit `INNER JOIN ... ON t.TeamID = p.TeamID WHERE p.LastName = 'Smith'` (**Query `e`**) and the implicit join `FROM Team t, Player p WHERE t.TeamID = p.TeamID AND p.LastName = 'Smith'` (**Query `f`**) are equivalent and correct (`3-e` and `3-f`). **Step 2:** Therefore, both **A (`1-a, 2-d, 3-f`)** and **C (`1-a, 2-d, 3-e`)** are correct. **Key Takeaway / Tip:** In SQL `LIKE`, each underscore `_` matches **exactly one** character and `%` matches **zero or more** characters, so `'at least 4 characters starting with A'` is `'A___%'`. A: **Correct:** Option A (1-a, 2-d, 3-f) matches our derived result under Pattern Matching (LIKE), GROUP BY, and Joins. In SQL `LIKE`, each underscore `_` matches **exactly one** character and `%` matches **zero or more** characters, so `'at least 4 characters starting with A'` is `'A___%'`. B: **Incorrect:** Option B (1-b, 2-c, 3-e) does not satisfy the conditions derived in the step-by-step analysis for Pattern Matching (LIKE), GROUP BY, and Joins (correct choice: A, C). C: **Correct:** Option C (1-a, 2-d, 3-e) matches our derived result under Pattern Matching (LIKE), GROUP BY, and Joins. In SQL `LIKE`, each underscore `_` matches **exactly one** character and `%` matches **zero or more** characters, so `'at least 4 characters starting with A'` is `'A___%'`. D: **Incorrect:** Option D (1-a, 2-e, 3-f) does not satisfy the conditions derived in the step-by-step analysis for Pattern Matching (LIKE), GROUP BY, and Joins (correct choice: A, C).

**Answer: D** **Topic:** SQL Set Operations — *Duplicate Elimination in SQL UNION* ### Step-by-Step Solution **Step 1:** The first `SELECT` retrieves `FirstName` of players in the `'Warriors'` team, and the second `SELECT` retrieves `FirstName` of players in the `'Lakers'` team. **Step 2:** In SQL, the `UNION` operator (unlike `UNION ALL`) combines the results of two queries—meaning players in **either** `'Warriors'` **or** `'Lakers'`—and **automatically eliminates duplicate rows**. **Step 3:** Therefore, the query returns the **unique first names of players who are either in the Warriors team or in the Lakers team** (**D**). **Key Takeaway / Tip:** `UNION` performs set union (eliminating duplicates) for `'either A or B'`, whereas `INTERSECT` finds `'both A and B'` and `UNION ALL` retains duplicates. A: **Incorrect:** Option A (First names of players who are in either the Warriors or the Lakers team) does not satisfy the conditions derived in the step-by-step analysis for Duplicate Elimination in SQL UNION (correct choice: D). B: **Incorrect:** Option B (First names of players who are in both the Warriors and the Lakers teams) does not satisfy the conditions derived in the step-by-step analysis for Duplicate Elimination in SQL UNION (correct choice: D). C: **Incorrect:** Option C (Unique First names of players who are in both the Warriors and the Lakers teams) does not satisfy the conditions derived in the step-by-step analysis for Duplicate Elimination in SQL UNION (correct choice: D). D: **Correct:** Option D (Unique First names of players who are either in the Warriors team or in the Lakers team) matches our derived result under Duplicate Elimination in SQL UNION. `UNION` performs set union (eliminating duplicates) for `'either A or B'`, whereas `INTERSECT` finds `'both A and B'` and `UNION ALL` retains duplicates.

**Answer: D** **Topic:** SQL Joins — *Counting Rows Returned by a FULL OUTER JOIN* ### Step-by-Step Solution **Step 1:** Inspect the `team_id` values in `players` (7 rows) and `team` (5 rows): - Every one of the **7 rows** in `players` (`Cric-1` $\times 2$, `Cric-3` $\times 1$, `Cric-2` $\times 2$, `Cric-5` $\times 2$) matches a unique `team_id` in `team`, producing **7 matched rows**. - Check which rows in `team` (`Cric-1, Cric-2, Cric-3, Cric-4, Cric-5`) have **no matching players** in `players`: `Cric-4` (`NCB, Nepal`) has 0 players in `players`, so `FULL OUTER JOIN` adds **1 unmatched row** (`Cric-4` padded with `NULL`s for the player columns). **Step 2:** Total rows returned: $$7 + 1 = 8\text{ rows}$$ which matches **D** (`8`). **Key Takeaway / Tip:** In a `FULL OUTER JOIN`, count all matched join pairs ($7$) plus any unmatched left rows ($0$) plus any unmatched right rows (`Cric-4`: $1$) to get $7 + 0 + 1 = 8$. A: **Incorrect:** Option A (7) does not satisfy the conditions derived in the step-by-step analysis for Counting Rows Returned by a FULL OUTER JOIN (correct choice: D). B: **Incorrect:** Option B (5) does not satisfy the conditions derived in the step-by-step analysis for Counting Rows Returned by a FULL OUTER JOIN (correct choice: D). C: **Incorrect:** Option C (9) does not satisfy the conditions derived in the step-by-step analysis for Counting Rows Returned by a FULL OUTER JOIN (correct choice: D). D: **Correct:** Option D (8) matches our derived result under Counting Rows Returned by a FULL OUTER JOIN. In a `FULL OUTER JOIN`, count all matched join pairs ($7$) plus any unmatched left rows ($0$) plus any unmatched right rows (`Cric-4`: $1$) to get $7 + 0 + 1 = 8$.

**Answer: B, C** **Topic:** Relational Calculus — *Tuple and Domain Relational Calculus Equivalents of a Join Query* ### Step-by-Step Solution **Step 1:** The SQL query selects `p.name` for players who belong to the team named `'BCCI'` (`p.team_id = t.team_id AND t.name = 'BCCI'`). **Step 2:** Check the Tuple Relational Calculus (TRC) options (A and B): - **A** omits `x.name = p.name`, leaving the result tuple variable `x` unconstrained — Incorrect. - **B**: $\{x \mid \exists p \in \text{players}, \exists t \in \text{team}(p.\text{team\_id} = t.\text{team\_id} \land t.\text{name} = '\text{BCCI}' \land x.\text{name} = p.\text{name})\}$ — **Correct!** **Step 3:** Check the Domain Relational Calculus (DRC) options (C and D), where `team(team_id, name, ranking, country)` is `<a, b, c, d>` and `players(team_id, player_id, name, ranking)` is `<m, n, o, p>`: - The player's name is the 3rd attribute of `players`, which is **`o`** (whereas `b` is the team's name `'BCCI'`). - **C** projects `<o>` with join condition `a = m` and `b = 'BCCI'` — **Correct!** (D projects `<b>`, the team name, instead of `<o>`). **Step 4:** Therefore, **B and C** are equivalent to the SQL query. **Key Takeaway / Tip:** In DRC, check the attribute position in `players(team_id, player_id, name, ranking)`: `<m, n, o, p>` puts `players.name` at variable `o`. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Tuple and Domain Relational Calculus Equivalents of a Join Query (correct choice: B, C). B: **Correct:** Option B (

) matches our derived result under Tuple and Domain Relational Calculus Equivalents of a Join Query. In DRC, check the attribute position in `players(team_id, player_id, name, ranking)`: `<m, n, o, p>` puts `players.name` at variable `o`. C: **Correct:** Option C (

) matches our derived result under Tuple and Domain Relational Calculus Equivalents of a Join Query. In DRC, check the attribute position in `players(team_id, player_id, name, ranking)`: `<m, n, o, p>` puts `players.name` at variable `o`. D: **Incorrect:** Option D (

) does not satisfy the conditions derived in the step-by-step analysis for Tuple and Domain Relational Calculus Equivalents of a Join Query (correct choice: B, C).

**Answer: B** **Topic:** SQL Views — *Querying a SQL View with a WHERE Clause* ### Step-by-Step Solution **Step 1:** First, determine what rows are in the view `BestTeam` (`t.ranking < 4`, which selects `Cric-1` [ranking 1, India] and `Cric-2` [ranking 3, Australia]): - From `Cric-1`: `('BCCI-11', 'Virat', 'India')` and `('BCCI-23', 'Rohit', 'India')` - From `Cric-2`: `('ACB-12', 'David', 'Australia')` and `('ACB-29', 'Smith', 'Australia')` **Step 2:** We want to display only the two rows `('BCCI-11', 'Virat')` and `('BCCI-23', 'Rohit')`: - **A (`SELECT player_id, name FROM BestTeam`)**: Returns all 4 rows (includes David and Smith). - **B (`SELECT player_id, name FROM BestTeam where player_id like 'B%'`)**: Filters `BestTeam` to `player_id` starting with `'B'`, returning the exact 2 BCCI rows! - **C and D**: Start with the invalid keyword `VIEW` instead of `SELECT`. **Step 3:** Therefore, **B** is correct. **Key Takeaway / Tip:** Once a view is created with `CREATE VIEW`, it is queried using standard `SELECT ... FROM ViewName` syntax, never `VIEW ... FROM ViewName`. A: **Incorrect:** Option A (

) does not satisfy the conditions derived in the step-by-step analysis for Querying a SQL View with a WHERE Clause (correct choice: B). B: **Correct:** Option B (

) matches our derived result under Querying a SQL View with a WHERE Clause. Once a view is created with `CREATE VIEW`, it is queried using standard `SELECT ... FROM ViewName` syntax, never `VIEW ... FROM ViewName`. C: **Incorrect:** Option C (

) does not satisfy the conditions derived in the step-by-step analysis for Querying a SQL View with a WHERE Clause (correct choice: B). D: **Incorrect:** Option D (

      **AppDev1** **Section Id :** 64065369406 **Section Number :** 7 **Section type :** Online **Mandatory or Optional :** Mandatory **Numb...) does not satisfy the conditions derived in the step-by-step analysis for Querying a SQL View with a WHERE Clause (correct choice: B).