Database Management System · Quiz 1 · Jan 2025

**1. Identify the external abstraction layer:** A view exposes selected rows and columns to a particular user or application without exposing the full underlying database. **2. Connect it to restricted access:** The view level is therefore the abstraction level associated with limiting users to particular data views. Physical storage and the overall logical schema serve different purposes. **Answer: C — View level.** A: **Incorrect:** It describes storage implementation rather than user-specific data subsets. B: **Incorrect:** It describes the complete logical structure rather than a particular restricted view. C: **Correct:** It provides user-specific external views of data. D: **Incorrect:** It omits the external/view level asked for.

**1. Identify the privilege statement:** INSERT is the permission, student is the target table and instructor is the role receiving it. **2. Put these in SQL order:** GRANT INSERT ON student TO instructor supplies the required privilege. The preceding CREATE ROLE establishes the recipient if it does not already exist. **Answer: D — GRANT INSERT ON student TO instructor.** A: **Incorrect:** It reverses the table and role. B: **Incorrect:** It omits the correct ON-table syntax and misorders its elements. C: **Incorrect:** ROLE is not part of this table-privilege statement. D: **Correct:** It has the valid privilege, object and grantee order.

**1. Form the product and count excluded pairs:** Four Products rows and three Supplier rows give 12 pairs. The predicate is P.P_ID<>S.S_ID, not a foreign-key equality. P_IDs 1,2,3 each exclude exactly one equal supplier ID; P_ID 4 excludes none. Thus 12−3=9 pairs remain. **2. Count output attributes:** SELECT * from the comma-separated tables retains all 3+2=5 columns, including both qualified S_ID columns. It does not merge them as a natural join would. **Answer: D — 5 attributes and 9 rows.** A: **Incorrect:** It has the correct row count but wrongly merges a column. B: **Incorrect:** It incorrectly excludes four pairs instead of three. C: **Incorrect:** It has both the wrong column and row counts. D: **Correct:** It gives five columns and nine surviving product pairs.

**1. Evaluate the subquery:** Podium=P1 selects drivers with Points 92 and 64, so AVG(Points)=(92+64)/2=78. **2. Select above-average drivers and join their teams:** Points greater than 78 are 92,90,95,100 and 91: five drivers. Each has one matching team. **3. Preserve SQL multiplicity:** The query selects team ID and name without DISTINCT, so multiple qualifying drivers on the same team still create separate result rows. The count is five, not the number of distinct teams. **Answer: B — 5.** A: **Incorrect:** Five points values, not four, exceed 78. B: **Correct:** It counts the five qualifying drivers after their one-to-one team lookup. C: **Incorrect:** It ignores the points filter. D: **Incorrect:** It counts rows that fail the threshold.

**1. Track the tuple-calculus output:** The result has supplier ID x.sid=c.sid and part name x.pname=p.pname, with Mumbai location, price 5000, and catalog links to both supplier and part. **2. Match domain variables:** In the options, (m,n,o) is a part tuple, (p,q,r) a supplier tuple and (a,b,c) a catalog tuple. Correct output is (p,n), with m=a, p=b, r=Mumbai and c=5000. A has exactly these bindings. **Answer: A — A — supplier ID p and part name n with both catalog joins.** A: **Correct:** It outputs supplier ID and part name and enforces both catalog links. B: **Incorrect:** It outputs supplier name q rather than supplier ID p. C: **Incorrect:** It omits the catalog-to-part and catalog-to-supplier equalities. D: **Incorrect:** It has both the wrong supplier output field and missing joins.

**1. Preserve the indicated key:** The order relation’s identifier is o_id, so it must be the primary key; c_id and p_id are references, not substitute order identifiers. **2. Match the referenced tables:** c_id references customer and p_id references product. D declares column-name-then-type syntax, the correct primary key and both correctly directed foreign keys. Omitting referenced column lists uses the referenced tables’ primary keys. **Answer: D — D — o_id PRIMARY KEY; c_id→customer; p_id→product.** **Convention note:** ORDER is a reserved SQL keyword in systems such as PostgreSQL. For executable SQL, rename the table Orders or quote its name as "order" consistently. D is the intended schema-level choice after that shared identifier repair. A: **Incorrect:** It reverses the standard column-name/type order. B: **Incorrect:** It points p_id at customer and c_id at product. C: **Incorrect:** It makes c_id primary, incorrectly allowing only one order per customer. D: **Correct:** It uses the intended order identifier and correctly directed foreign keys.

**1. Interpret the first set:** The first SELECT returns driver names whose salary is greater than every salary among Mercedes drivers. The inner aliases are local to the subquery, so they do not correlate it to one particular outer driver. **2. Interpret the subtracted set:** The second SELECT returns names of drivers whose salary is less than every Ferrari salary. EXCEPT removes those names from the first result. **3. Combine the conditions:** The intended description is greater than all Mercedes drivers, but not less than all Ferrari drivers. “Not less than all” means the universal less-than condition is false; with a nonempty, non-NULL comparison set, the salary is at least one Ferrari salary. **Answer: A — Names of all the drivers whose salary is greater than all Mercedes drivers but .** **Convention note:** SQL EXCEPT operates on the projected names. If distinct drivers share a name, removing one qualifying name removes that name from the first set regardless of which driver produced it. NULL salaries also require SQL three-valued logic; the offered description assumes ordinary non-NULL salaries and unambiguous names. A: **Correct:** It preserves the first universal condition and negates membership in the second set. B: **Incorrect:** It reverses the first comparison. C: **Incorrect:** It changes the excluded less-than condition into an unsupported greater-than-all Ferrari condition. D: **Incorrect:** It reverses both intended inequalities.

**1. Identify where each predicate can be pushed:** category belongs to product, and order_date belongs to order. The two restrictions can be applied separately to their own relations before joining, or sequentially after the join. **2. Check the non-equivalent expressions:** A tries to test order_date in product before that attribute exists; D tries to test category in order before that attribute exists. They are not well-typed expressions for the supplied schemas. **3. Recognize the equivalent ones:** B pushes each filter into its correct input; C applies the two filters sequentially to the joined relation. Both are equivalent to the target conjunction and must not be selected in this NOT-equivalent question. **Answer: A, D — A and D — the non-equivalent expressions.** A: **Correct:** Selected: order_date is absent from product at the selection point. B: **Incorrect:** Not selected: each predicate is correctly applied to its own relation before the join. C: **Incorrect:** Not selected: sequential selections implement the same conjunction. D: **Correct:** Selected: category is absent from order at the selection point.

**1. Read Produces:** Company and Items have total participation, so each company produces at least one item and every item has a producer. The arrow toward Company additionally gives each item at most one producer. **2. Read Orders:** Customer’s total participation requires every customer to order at least one item. Without a limiting arrow on this relationship, an item may be ordered by multiple customers. **3. Match the statements:** A, C and D follow. B conflicts with the producer key constraint. **Answer: A, C, D — A, C and D.** A: **Correct:** The company-side double line requires participation in Produces. B: **Incorrect:** The arrow toward Company prevents an item having multiple producers. C: **Correct:** The customer-side double line requires an order. D: **Correct:** Orders allows multiple customers to be associated with one item.

**1. Group by the requested category:** The average must be computed per grade, so GROUP BY grade is required. Grade A has (85+90+95)/3=90; grade B has (70+60)/2=65. **2. Sort the grouped rows:** ORDER BY AVG(marks) DESC and ORDER BY average_marks DESC are equivalent here because average_marks is the SELECT alias for that aggregate. B and C use valid clause order and give A–90 before B–65. **3. Reject misordered clauses:** ORDER BY must follow GROUP BY, not precede it. A additionally groups by marks instead of grade. **Answer: B, C — B and C.** A: **Incorrect:** It misorders the clauses and groups by the wrong attribute. B: **Correct:** It groups by grade and sorts by the aggregate expression. C: **Correct:** It groups by grade and sorts by the aggregate’s output alias. D: **Incorrect:** It places ORDER BY before GROUP BY, which is invalid clause order.

**1. Identify matching genres:** Genre LIKE F% selects Fantasy and Fiction. BookIDs 1,2,3 and 5 meet that condition; Adventure and Dystopian do not. **2. Apply the left outer join:** Books 1,2 and 3 have matching author IDs, while Book 5’s AuthorID 105 has no Author row. The left join still preserves that book with NULL author-side columns. **3. Count:** The genre condition uses the Book side and remains true for Book 5, so all four qualifying books produce output rows. Treat the tables as supplied even if some titles and author names differ from real-world bibliographic facts. **Answer: 4.**

**1. Count the Cartesian-product multiplicities:** student has five rows and course has four. The comma product contains 20 rows, and selecting ID gives each student ID four copies. There is no join predicate. **2. Apply EXCEPT ALL:** Subtracting SELECT ID FROM student removes one copy of each ID, leaving three copies of each, or 15 rows in the parenthesized result. **3. Apply the final plain EXCEPT:** Without ALL, EXCEPT uses set difference. Every remaining ID also occurs in student, so all are removed. The final result contains zero rows. **Answer: 0.**