Directions (Q31–35): Sailors S(sid, sname, rating, age): (22, Dustin, 7, 45), (29, Brutus, 1, 33), (31, Lubber, 8, 55.5), (32, Andy, 8, 25.5), (58, Rusty, 10, 35), (64, Horatio, 7, 35), (71, Zorba, 10, 16), (74, Horatio, 9, 35), (85, Art, 3, 25.5), (95, Bob, 3, 63.5). Reserves R(sid, bid, day): (22, 101), (22, 102), (22, 103), (22, 104), (31, 102), (31, 103), (31, 104), (64, 101), (64, 102), (74, 103). Boats B(bid, bname, color): (101, Interlake, blue), (102, Interlake, red), (103, Clipper, green), (104, Marine, red).
Which relational algebra query computes the names of sailors who have reserved a red and a green boat?
Pick an option to check yourself, or open the answer below.
Show answer & explanation
Correct answer: A. ρ(Tempred, π_sid((σ_color='red' Boats) ⋈ Reserves)); ρ(Tempgreen, π_sid((σ_color='green' Boats) ⋈ Reserves)); π_sname((Tempred ∩ Tempgreen) ⋈ Sailors)
Explanation
Compute the sids that reserved a red boat and the sids that reserved a green boat separately, intersect them, then join with Sailors. Intersecting boats (no boat is both colours) or selecting color = red ∧ green gives nothing.
More Database Management Systems questions
- Let R(ABCDEFGH) be a relation schema and F = {A → B, ABCD → E, EF → G, EF → H, ACDF → EG}. The minimal cover of this set of dependencies is
- A trigger is
- In reference to big data, consider the following databases: (A) Memcached (B) CouchDB (C) InfiniteGraph.
- Match List I with List II. List I: A. BCNF, B. 3NF, C. 2NF, D. 4NF. List II: I. Removes multivalued dependency, II. Not always dependency p…
- Statement I: Conservative 2PL is a deadlock-free protocol. Statement II: Thomas's write rule enforces conflict serializability. Statement I…
- Statement I: Composite attributes cannot be divided into smaller subparts. Statement II: Complex attributes are formed by nesting composite…