Assignment 2 1 I a R A

Assignment 2 1

I a) R (A 1, A 2) R = {(1, 2), (4, 3), (2, 9), (3, 8)} S (B 1, B 2, A 1) S = {(x, a, 1), (y, b, 1), (z, a, 4)} A 1, B 2 ( A 1 < 4 and B 1 x (S)) = A 1, B 2 ( { (y, b, 1) } ) = { (1, b) } 2

I b) R (A 1, A 2) R = {(1, 2), (4, 3), (2, 9), (3, 8)} S (B 1, B 2, A 1) S = {(x, a, 1), (y, b, 1), (z, a, 4)} A 2, B 1 ( R. A 1 > S. A 1 ( R S)) = { (3, x), (3, y), (8, x), (8, y), (9, x), (9, y)} R S (1, 2, x, a, 1) (1, 2, y, b, 1) (1, 2, z, a, 4) (4, 3, x, a, 1) (4, 3, y, b, 1) (4, 3, z, a, 4) (2, 9, x, a, 1) (2, 9, y, b, 1) (2, 9, z, a, 4) (3, 8, x, a, 1) (3, 8, y, b, 1) (3, 8, z, a, 4) R S) (4, 3, x, a, 1) (4, 3, y, b, 1) (2, 9, x, a, 1) (2, 9, y, b, 1) (3, 8, x, a, 1) (3, 8, y, b, 1) R. A 1 > S. A 1 ( 3

I c) R (A 1, A 2) R = {(1, 2), (4, 3), (2, 9), (3, 8)} S (B 1, B 2, A 1) S = {(x, a, 1), (y, b, 1), (z, a, 4)} R S = { (1, 2, x, a), (1, 2, y, b), (4, 3, z, a)} R S (1, 2, x, a, 1) (1, 2, y, b, 1) (1, 2, z, a, 4) (4, 3, x, a, 1) (4, 3, y, b, 1) (4, 3, z, a, 4) (2, 9, x, a, 1) (2, 9, y, b, 1) (2, 9, z, a, 4) (3, 8, x, a, 1) (3, 8, y, b, 1) (3, 8, z, a, 4) R S) (1, 2, x, a, 1) (1, 2, y, b, 1) (4, 3, z, a, 4) R. A 1 = S. A 1 ( 4

I d) R (A 1, A 2) R = {(1, 2), (4, 3), (2, 9), (3, 8)} S (B 1, B 2, A 1) S = {(x, a, 1), (y, b, 1), (z, a, 4)} A 2, B 1, B 2, S. A 1 (R S) = { (2, x, a, 1), (2, y, b, 1), (3, z, a, 4)} R S (1, 2, x, a, 1) (4, 3, x, a, 1) (2, 9, x, a, 1) (3, 8, x, a, 1) (1, 2, y, b, 1) (4, 3, y, b, 1) (2, 9, y, b, 1) (3, 8, y, b, 1) (1, 2, z, a, 4) (4, 3, z, a, 4) (2, 9, z, a, 4) (3, 8, z, a, 4) R S (1, 2, x, a, 1) (1, 2, y, b, 1) (4, 3, z, a, 4) 5

I e) R (A 1, A 2) R = {(1, 2), (4, 3), (2, 9), (3, 8)} S (B 1, B 2, A 1) S = {(x, a, 1), (y, b, 1), (z, a, 4)} R S = { (1, 2, x, a), (1, 2, y, b), (4, 3, z, a), (2, 9, null), (3, 8, null)} R S (1, 2, x, a, 1) (1, 2, y, b, 1) (1, 2, z, a, 4) (4, 3, x, a, 1) (4, 3, y, b, 1) (4, 3, z, a, 4) (2, 9, x, a, 1) (2, 9, y, b, 1) (2, 9, z, a, 4) (3, 8, x, a, 1) (3, 8, y, b, 1) (3, 8, z, a, 4) R S (1, 2, x, a, 1) (1, 2, y, b, 1) (4, 3, z, a, 4) (2, 9, null, null) (3, 8, null, null) 6

Assignment 2: II • • Relational Algebra Which tables? Join, Out Join, or No Join Common attributes for Join and Out Join? Which records (selection)? What attributes (projection)? Syntax (Standard Notations and Symbols) – Product: X – Join: 7

II (a) List all hotels Hotel (hotel. No, hotel. Name, city) * (Hotel) Use H for Hotel * (H) hotel. No, hotel. Name, city (Hotel) hotel. No (Hotel) Incorrect! All attributes when not specified. 8

b) List all single rooms with a price below $20 per night Room (room. No, hotel. No, type, price) (type = ‘Single’ and price < 20) (Room) 9

b) List all single rooms with a price below $20 per night * ( (type = ‘Single’ and price < 20) (Room) ) Projection is NOT Needed! (Room) (type = “Single” and price < 20) (Room) (type = ‘Single’ and price < $20) (Room) (type = Single and price < 20) 10

c) List the name and addresses of all guests Guest (guest. No, guest. Name, guest. Address) guest. Name, guest. Address (Guest) 11

d) List the price and type of all rooms at the Grosvenor Hotel Room (Room. No, Hotel. No, Type, Price) Hotel (Hotel. No, hotel. Name) price, type ( hotel. Name = ‘Grosvenor’ (Room Common attribute: Hotel. No price, type (Room Hotel)) (Hotel)) Incorrect! It’s the same as the following: (hotel. Name = ‘Grosvenor’) price, type ( (hotel. Name = ‘Grosvenor’) (Room Hotel)) 12

e) List all guests currently staying at the Grosvenor Hotel Guest (guest. No, guest. Name, guest. Address) Hotel (hotel. No, hotel. Name) Booking (hotel. No, guest. No, date. From, date. To, room. No) Guest. * ( (from. Date <= Current. Date and to. Date >= Current. Date) and hotel. Name = ‘Grosvenor’ (Guest Booking Hotel)) 13

e) List all guests currently staying at the Grosvenor Hotel Guest. * ( (from. Date <= Current. Date and to. Date >= Current. Date) and hotel. Name = ‘Grosvenor’ (Guest Booking Hotel)) Guest. * (Guest ( from. Date <= Current. Date and to. Date >= Current. Date Booking ) ( hotel. Name = ‘Grosvenor’ Hotel) ) 14

e) List all guests currently staying at the Grosvenor Hotel Guest (guest. No, guest. Name, guest. Address) Hotel (hotel. No, hotel. Name) Booking (hotel. No, guest. No, date. From, date. To, room. No) What is the common attribute between Guest and Hotel? Guest. * ( (from. Date <= Current. Date and to. Date >= Current. Date) and hotel. Name = ‘Grosvenor’ (Guest Hotel Booking) ) Guest. * ( (from. Date <= Current. Date and to. Date >= Current. Date) and hotel. Name = ‘Grosvenor’ (Guest (Hotel Booking))) 15

f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room if the room is occupied Room Guest Room. *, guest. Name (hotel. Name = ‘Grosvenor’ and from. Date <= Current. Date and Current. Date <= to. Date) ((Room Hotel) (Booking Guest)) Not correct! Room. *, guest. Name ( ( hotel. Name = ‘Grosvenor’ (Room ( (from. Date <= Current. Date and Current. Date Hotel) ) <= to. Date) (Booking Guest)) ) 16

Ctrl+G: 8. 5/10 Ctrl+I: Incorrect answer: -0. 5 Ctrl+S: Incorrect style: -0. 5 Ctrl+T: Missing table(s): -0. 5 Ctrl+N: Missing name: -1 Ctrl+N: Incorrect file name: -1 17