Erratum: the 1st GSLC I posted below is also for BB02 (not only for BA02). I am sorry for my mistake.
Thank you very much
---ooo000ooo---
My dear students of database systems class (BA02), below are exercises for GSLC I. I give you some rules to answer the questions and submit answers.
This is GSLC I for Database Systems Class (BA02). The rules applied for this GSLC are as follows:
1. Each student may answer only two questions for maximum (note: maximum only two questions, you don't need to answer all questions). You may also answer only one question. Every correct answer will worth 5 points added to mid-exam. Therefore if you have 2 correct answers from 2 questions you will get 10 additional points for you mid-exam. If only 1 correct answer you will get 5 points.
2. Choose questions and answer as quickly as possible. If there are redundant answers then I will only consider the first correct answers (i will check the timestamps). The algorithm applied here is 'first-correct-answer-first-get-points'
3. If the questions have been taken by a student and you think that the answers are correct then please don't take the same questions. Thus you avoid redundant answers from the same questions. Yet, if you think the answers are incorrect you can take the questions and submit your answers.
4. There are a lot of questions in these exercises, let me clarify:
4.8 has 6 questions (a through f)
4.9. has 6 questions as well (because it is about the equivalent problems of 4.8)
4.10. has 4 questions
4.11. equivalent problems of 4.10 (thus is 4 questions)
4.12. has 7 questions
4.13 has 1 question
5. You may answer either via comment field in this site/blog or via binusmaya forum. Deadline is 23rd of March.
Thank you very much
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete4.10.a List all hotelName in London
ReplyDelete4.12.d Phi Room.price, Room.type (Room JOIN Room.hotelNo = Hotel.hotelNo (Sigma Hotel.hotelName = Grosvenor Hotel(Hotel))
From BB02 - 1701314886
4.10.b list all hotel with the price above 50
ReplyDelete4.8.b list all hotel and room
BB02 1701297684
4.8.a
ReplyDeleteProduce a list of price for all rooms with only show the hotelNo details
4.10.c
List all hotelName which have branched tuple between Booking (B) and Hotel (H) with the same hotelNo and also between Booking (B) and Guest (G) with the same guestNo which consist 'John Smith' as the guestName. In other words, they will list the hotelName with 'John Smith' as the guestName.
BB02
1701313473
Nova Jingga
This comment has been removed by the author.
ReplyDelete4.10d
ReplyDeleteList all hotelName, guestName, dateFrom from B1, and dateFrom from B2 which have the same hotelNo with hotelNo from B1, the same guestNo with guestNo from B1, the same hotelNo from B2 with hotelNo from B1, and the same guestNo from B2 with guestNo from B1, but not in the same dateFrom
4.12a sigma Hotel
Andrew Jason
1701301832
BB02
4.8c List all hotelName where the price is greater than 50
ReplyDelete4.8d list all Guest and show the Booking.dateTo details only when Booking.dateTo value is greater than 1 jan 2002
Ignatius Irvan
1701291604
BB02
4.8 e list complete details of all hotels that have room price greater than 50
ReplyDelete4.8 d list all guest and any of their bookings after 1 Jan 2002
Josua Reynaldo
1701300823
BB02
This comment has been removed by the author.
ReplyDeleteSorry sir several notation can't be view in this comment so i upload the picture of the answer to google docs.
ReplyDeletehere is the link
4.12(b) https://drive.google.com/open?id=0BwL0S_oZZpJdVlFrV2daUFgzRzQ&authuser=0
4.12(c) https://drive.google.com/open?id=0BwL0S_oZZpJdNm03LWk2RE5xdjA&authuser=0
Aldo Makmur
1701316273
BB02
Vincent Iskandar - BB02 - 1701302103
ReplyDelete4.12.e. ∏Guest(Guest JOIN Guest.guestNo = Booking.guestNo (Booking JOIN Booking.hotelNo = Hotel.hotelNo(Σ Hotel.hotelName = Grosvenor Hotel(Hotel)))
4.12.g. ∏Guest.guestNo, Guest.guestName, Guest.guestAddress(Guest JOIN Guest.guestNo = Booking.guestNo (Booking JOIN Booking.hotelNo = Hotel.hotelNo(Σ Hotel.hotelName = Grosvenor Hotel(Hotel)))
Vincent Iskandar - BB02 - 1701302103
Delete4.12.e. RA: Guest з guestNo (σdateFrom ≤ ‘01-01-02’ ˄ dateTo ≥ ‘01-01-02’ (
Booking з hotelNo (σhotelName = ‘Grosvenor Hotel’(Hotel))))
(substitute ‘01-01-02’ for today’s date).
TRC: {G |Guest(G) ˄ ((ƎB)( ƎH) (Booking(B) ˄ Hotel(H) ˄ B.dateFrom ≤ ‘01-01-02’)
(B.dateTo ≥ ‘01-01-02’) ˄ (B.guestNo = G.guestNo) ˄
(B.hotelNo = H.hotelNo) ˄ (H.hotelName = ‘Grosvenor Hotel’)))}
DRC: {guestNo, guestName, guestAddress | (ƎhotelNo, guestNo1, dateFrom, dateTo,
hotelNo1, hotelName, city)
(Guest(guestNo, guestName, guestAddress) ˄
Booking(hotelNo, guestNo1, dateFrom, dateTo) ˄
Hotel(hotelNo1, hotelName, city) ˄ (guestNo = guestNo1) ˄
(dateFrom ≤ ‘01-01-02’ ˄ dateTo ≥ ‘01-01-02’) ˄
(hotelNo = hotelNo1) ˄hotelName = ‘Grosvenor Hotel’))}
4.12.g. RA: ΠguestNo, guestName, guestAddress(Guest з guestNo (σdateFrom ≤ ‘01-01-02’ ˄ dateTo ≥ ‘01-01-02’ (
Booking 3 hotelNo (σhotelName=‘Grosvenor Hotel’(Hotel)))))
(substitute ‘01-01-02’ for today’s date).
TRC: {G | Guest(G) ˄ ((B) (H) (Booking(B) ˄Hotel(H) ˄(B.guestNo = G.guestNo) ˄
(B.hotelNo = H.hotelNo) ˄H.hotelName = ‘Grosvenor Hotel’) ˄
(B.dateFrom ≤ ‘01-01-02’ ˄ B.dateTo≥ ‘01-01-02’) ))}
DRC: {guestNo, guestName, guestAddress |
((ƎhNo, gNo, dFrom, dTo, rNo, hNo1, hName, city)
(Guest(guestNo, guestName, guestAddress) ˄
Booking(hNo, gNo, dFrom, dTo, rNo) ˄ Hotel(hNo1, hName, city) ˄
(guestNo = gNo) ˄ (hNo = hNo1) ˄hName = ‘Grosvenor Hotel’) ˄
(dFrom ≤ ‘01-01-02’ ˄ dTo ≥ ‘01-01-02’) ))}
Adriel Edison Boantua Aritonang - BB02 - 1701327081
ReplyDelete4.9.a.
TRC: {R.hotelNo | Room(R) ˄ R.price > 50}
DRC: {hotelNo | (ƎrNo, typ, prce) (Room (rNo, hotelNo, typ, prce) ˄ prce > 50)}
4.9.b.
TRC: {H, R | Hotel(H) ˄ (ƎR) (Room(R) ˄ (H.hotelNo = R.hotelNo))}
DRC: {hNo, hName, cty, rNo, hNo1, typ, prce | (Hotel(hNo, hName, cty) ˄ Room(rNo, hNo1, typ, prce) ˄ (hNo = hNo1))}
4.8f
ReplyDeleteThis will produce a relation containing the names of all guest who have booked all hotels in London.
4.9c
TRC: {H.hotelName | Hotel(H) (R) (Room(R) (H.hotelNo R.hotelNo)
(R.price > 50))}
DRC: {hotelName | (hNo, cty, rNo, hNo1, typ, prce)
(Hotel(hNo, hotelName, cty) Room(rNo, hNo1, typ, prce)(hNo = hNo1)
(prce > 50))}
BB02
Colllin Power Kariman
1701324810
ReplyDelete4.9d Guest 5 (dateTo ‘1-Jan-2002’ (Booking))
TRC: {G.guestNo, G.guestName, G.guestAddress, B.hotelNo, B.dateFrom,
B.dateTo, B.roomNo | Guest(G) (B) (Booking(B)
(G.guestNo B.guestNo) (B.dateTo > ‘1-Jan-2002’))}
DRC: {guestNo, guestName, guestAddress, hotelNo, dateFrom, dateTo, roomNo |
(gNo1) (Guest(guestNo, guestName, guestAddress)
Booking(hotelNo, gNo1, dateFrom, dateTo, roomNo)
(guestNo = gNo1) (dateTo ‘1-Jan-2002’)))}
4.9e Hotel 1 Hotel.hotelNo Room.hotelNo (price 50 (Room)) )
TRC: {H.hotelNo, H.hotelName, H.city | Hotel(H) (R) (Room(R)
(H.hotelNo R.hotelNo) (R.price > 50))}
DRC: {hotelNo, hotelName, city | (rNo, hNo1, typ, prce)
(Hotel(hotelNo, hotelName, city) Room(rNo, hNo1, typ, prce)
(hotelNo = hNo1) (prce > 50))}