**1. Identify the divisor:** Projecting department_name from Projects gives R&D, Marketing, Finance and HR. Division requires an employee to have an assignment in every one of these departments.
**2. Examine employee coverage:** Alice covers R&D and Marketing; Charlie covers Finance and R&D; Bob covers Marketing; David and Frank cover HR; Eve covers Finance. No employee covers all four departments.
**3. State the quotient:** The employee_name result is empty. Multiple projects within R&D do not substitute for covering a different department.
**Answer: D — an empty employee_name relation.**
A: **Incorrect:** Alice is missing Finance and HR.
B: **Incorrect:** Both Alice and Charlie lack full department coverage.
C: **Incorrect:** Neither Alice nor Bob covers all four departments.
D: **Correct:** No employee satisfies every required department.
**1. Separate the restrictions:** Country must be Australia or Germany, and Score must exceed a scalar subquery computing AVG(Score) across the entire Students table. Parentheses ensure the score test applies to either country.
**2. Check the supplied data:** Scores total 523, giving average 523/7≈74.7143. The qualifying rows are Frank 76, Robert 93 and Mike 84. D expresses the correct predicate and average scope.
**Answer: D — D — the parenthesized country test and global-average subquery.**
A: **Incorrect:** A Boolean OR cannot directly combine two standalone SELECT statements this way.
B: **Incorrect:** S is a column alias, not a table alias supporting S.Score; AVG is also misplaced in WHERE.
C: **Incorrect:** The bare string Germany is not the required Country comparison, and AVG is misplaced.
D: **Correct:** It uses explicit country comparisons and a scalar aggregate subquery.
**1. Filter the artist roles:** Retain Guitarist or Vocalist rows: bands 0,1,5,3. Drummer rows fail the role test.
**2. Apply the correlated EXISTS:** The band must also have YearFormed<1968. Bands 0 (1960),5 (1965),3 (1965) pass; band 1 (1970) fails. The output BandIDs are 0,5,3, in unspecified SQL order.
**Answer: A — BandIDs 0,5,3.**
A: **Correct:** It contains exactly the three qualifying BandIDs.
B: **Incorrect:** It wrongly includes the 1970 band and omits band 0.
C: **Incorrect:** It includes band 2 from a drummer row and omits band 5.
D: **Incorrect:** Its rows fail one or both of the role and year restrictions.
**1. Count the selected drivers:** Experience_Years≥10 selects Lewis Hamilton (15), Sebastian Vettel (14), and Valtteri Bottas (10). Each Team_ID matches exactly one Teams row.
**2. Count natural-join columns:** Drivers has four columns and Teams has three; their common Team_ID is merged. Hence the result has 4+3−1=6 columns and three rows.
**Answer: D — 3 rows and 6 columns.**
A: **Incorrect:** Only three drivers meet the experience requirement.
B: **Incorrect:** A natural join merges Team_ID rather than retaining two copies.
C: **Incorrect:** It has both the wrong row count and wrong column count.
D: **Correct:** It correctly combines three qualifying rows with six distinct columns.
**1. Identify the object and recipient:** The table is student, the grantee role is instructor, and the privilege is UPDATE.
**2. Apply GRANT syntax:** After creating the instructor role if needed, the privilege statement is GRANT UPDATE ON student TO instructor. C has this exact privilege–object–recipient order.
**Answer: C — GRANT UPDATE ON student TO instructor.**
A: **Incorrect:** It reverses the table and role.
B: **Incorrect:** It does not use valid table-privilege GRANT syntax.
C: **Correct:** It grants UPDATE on student to the instructor role.
D: **Incorrect:** ROLE is not inserted into this table-privilege GRANT statement.
**1. Define the view structure:** CREATE VIEW XYZ(name,dept_name) AS SELECT name,dept_name FROM instructor gives the required two-column view.
**2. Apply both restrictions:** LIKE %s matches names ending in s; AND dept_name=Biology requires the same row to be in Biology. A combines these correctly.
**Answer: A — A — CREATE VIEW ... AS ... WHERE name LIKE %s AND Biology.**
A: **Correct:** It uses AS, the suffix pattern and conjunction of both filters.
B: **Incorrect:** It uses TO instead of AS and the prefix pattern s%.
C: **Incorrect:** It uses ON instead of AS and a prefix rather than suffix pattern.
D: **Incorrect:** OR permits rows that meet only one requirement.
There might exist an item that has not been produced by any company
6406535116127.
There might exist a customer who has not ordered any item
6406535116128.
An item can be ordered by multiple customers
6406535116129.
An item cannot be produced by more than one company
Published solution
**1. Read mandatory participation and the producer arrow:** Items participates totally in Produces, and the arrow toward Company limits an item to one producer. Thus every item has exactly one company in this model. Customer’s double line to Orders requires every customer to order something.
**2. Read the ordering multiplicity:** There is no key-constraint arrow restricting an item to one customer in Orders, so multiple customers may order the same item. C and D are therefore the valid statements.
**Answer: C, D — C and D.**
A: **Incorrect:** Total item participation rules out an item with no producer.
B: **Incorrect:** Total customer participation rules out a customer with no order.
C: **Correct:** The ordering relationship permits multiple customers for an item.
D: **Correct:** The arrow toward Company limits each item to at most one company.
Logical level of schema is analogous to type information of a variable in a
program
6406535116131.
In physical level of schema,application programs hide details of data types
6406535116132.
The ability to modify the physical schema without changing the logical schema
is called Logical Data Independence
6406535116133.
Relational Algebra falls under the Pure class of languages used for proving
properties about computational power and for optimization
Published solution
**1. Distinguish schema levels:** A logical schema describes the structure and types of stored data, analogous to type information for a program variable. The physical schema describes storage structures and access organization.
**2. Apply the independence terminology:** Changing physical storage without altering the logical schema is physical data independence, not logical data independence. Relational algebra is a pure formal query language used to study expressiveness and justify query transformations.
**Answer: A, D — A and D.**
A: **Correct:** It correctly relates logical structure to type information.
B: **Incorrect:** It misattributes user/application abstraction of data to the physical storage level.
C: **Incorrect:** The described property is physical data independence.
D: **Correct:** It is a formal query language supporting reasoning about equivalence and optimization.
**1. Compute each department’s average salary:** Comp. Sci.=232000/3≈77333.33; Finance=85000; Music=40000; Physics=91000; History=61000; Biology=72000; Elec. Eng.=80000.
**2. Average the department averages:** There are seven groups. Their unweighted average is approximately 72333.33. It is not the overall employee-weighted salary mean.
**3. Count qualifying groups:** Comp. Sci., Finance, Physics and Elec. Eng. have average_salary≥72333.33. The query therefore returns four department-name rows.
**Answer: 4.**
**1. Build both derived relations:** S contains the 10000,7000 and 8000 loans because their amounts are at least 7000. T contains the 5000,7000 and 8000 loans because their dates are on or after 2025-02-01.
**2. Join on every common attribute:** Both derived schemas contain Bank_Manager and Loan_Amount, so NATURAL JOIN matches both columns. Only (Sumit,7000) and (Suresh,8000) occur in both.
**3. Count:** Two joined rows survive, so COUNT(*) returns the scalar 2.
**Answer: 2.**
**1. Use the team key:** Each Players.Team_id matches at most one Teams row because Teams.Team_id is a key. The left outer join preserves every player even if a match were absent.
**2. Apply the projection:** There are 50 unique Player_id values, and projection onto that key preserves all 50. The result cannot gain rows from unmatched teams in a left join.
**Answer: A — 50.**
A: **Correct:** Each of the 50 player IDs occurs in the result.
B: **Incorrect:** The result is keyed by players, not by the ten teams.
C: **Incorrect:** A left join does not add unmatched right-side teams.
D: **Incorrect:** The team key prevents multiplicative matches and the left side contains only 50 players.
**1. Identify dependent rows:** Players.Team_id and Points_table.Team_id reference Teams.Team_id. Without ON DELETE CASCADE, deleting a referenced Teams row first violates referential integrity when dependent rows exist.
**2. Delete children before their parent:** Remove the T004 player rows and points row in either order, then remove Teams.T004. Thus b→c→a and c→b→a are safe.
**Answer: C, D — C and D.**
**Convention note:** This uses the usual immediately checked foreign keys and existing references. Special deferred-constraint transactions or absent dependent rows could permit other orders, but are not the paper’s setting.
A: **Incorrect:** It deletes the referenced team before its children.
B: **Incorrect:** It leaves a points-table reference when attempting the team deletion.
C: **Correct:** It removes both child sets before the team.
D: **Correct:** The child deletions can occur in this reverse order as well.
**1. Read the parent-table schema:** Teams has Team_id as its primary key plus Team_name, Coach and ICC_ranking. Players and Points_table reference Teams; Teams does not need to reference its child tables.
**2. Compare the definitions:** B declares the required columns and primary key directly. C omits the key. A and D introduce malformed Team_id reference clauses and reverse the intended dependency direction.
**Answer: B — B — Teams with Team_id PRIMARY KEY.**
**Convention note:** The export splits option D between adjacent option text fields; its continuation was read from the source PDF. This does not change the valid choice.
A: **Incorrect:** It has an invalid extra reference clause and incorrectly makes Teams depend on Points_table.
B: **Correct:** It defines Teams with the required primary key and attributes.
C: **Incorrect:** It omits the primary-key constraint on Team_id.
D: **Incorrect:** It reverses the dependency toward Players and has malformed syntax rather than a proper foreign-key declaration.
