daily pastebin goal
49%
SHARE
TWEET

answer 6

a guest Mar 18th, 2019 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Q6: DESIGN A RELATIONAL DATABASE SCHEMA FOR A DATABASE APPLICATION
  2. OF YOUR CHOICE:
  3.  
  4. a. DECLARE YOUR RELATIONS, USING THE SQL DDL
  5.  
  6. Hotel (hotelNo, hotelName, city)
  7. Room (roomNo, hotelNo, type, price)
  8. Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
  9. Guest (guestNo, guestName, guestAddress)
  10.  
  11. b. SPECIFY SEVERAL QUERIES IN SQL THAT ARE NEEDED BY YOUR
  12. DATABASE APPLICATION. (YOUR QUERIES SHOULD COVER THE
  13. FOLLOWING TOPICS (SIMPLE QUERIES, AGGREGATE FUNCTIONS,
  14. SUBQUERIES AND JOIN AND GROUPING). MINIMUM NUMBER OF
  15. QUERIES FOR EACH TYPE IS 5. SO, TOTAL NUMBER OF QUERIES
  16. REQUIRED IS 20 QUERIES).
  17.  
  18. Simple:
  19. - Select * from Hotel;
  20. - Delete From Guest Where guestName = ‘Fatin Noradilah’;
  21. - Update Room Set price =300Where price =299;
  22. - Insert Into Hotel Values (123, ‘Concorde Hotel’, ‘Kuala Lumpur’);
  23. - Delete table Guest;
  24.  
  25. Aggregate:
  26. - Select AVG (price) From Room;
  27. - Select COUNT (hotelNo) From Hotel;
  28. - Select MIN (price) From Room;
  29. - Select MAX (price) From Room;
  30. - Select * From Booking Order By hotelNo;
  31.  
  32. Subqueries
  33.  
  34. 1)SELECT price, type FROM room WHERE hotelNo = (SELECT hotelNo FROM hotel
  35. WHERE hotelName='Concorde');
  36.  
  37. 2)SELECT * from guest WHERE guestNo = (SELECT guestNo FROM booking WHERE
  38. dateFrom <= CURRENT_DATE and dateTo
  39. >=CURRENT_DATE and hotelNo = (SELECT
  40. hotelNo FROM hotel WHERE hotelName =
  41. 'Concorde'));
  42.  
  43. 3)SELECT r.* FROM room r LEFT JOIN (SELECT g.guestName, h.hotelNo, b.roomNo FROM
  44. guest g, hotel h, booking b WHERE g.guestNo
  45. = b.guestNo AND b.hotelNo = h.hotelNo AND
  46. hotelName = 'Concorde' AND dateFrom <=
  47. CURRENT_DATE AND dateTo >=
  48. CURRENT_DATE) AS XXX ON r.hotelNo =
  49. XXX.hotelNo AND r.roomNo = XXX.roomNo;
  50.  
  51. 4)SELECT SUM(price) FROM room WHERE (dateFrom <= CURRENT_DATE AND dateTo
  52. >= CURRENT_DATE) AND r.hotelNo =
  53. b.hotelNo AND r.roomNo = b.roomNo AND
  54. hotelName = 'Concorde');
  55.  
  56. 5)SELECT * FROM room r WHERE roomNo NOT IN (SELECT roomNo FROM booking b,
  57. hotel h WHERE (dateFrom <=
  58. CURRENT_DATE and dateTo >=
  59. CURRENT_DATE) and b.hotelNo=h.hotelNo
  60. and h.hotelName ='Concorde);
  61.  
  62. c. IMPLEMENT YOUR DATABASE USING ORACLE DBMS TOOL AND SQL
  63.  
  64. CREATE TABLE Hotel
  65. (hotelNo VARCHAR(6) NOT NULL,
  66. hotelName VARCHAR(30) NOT NULL,
  67. city VARCHAR(15),
  68. PRIMARY KEY (hotelNo));
  69.  
  70. CREATE TABLE Room
  71. (roomNo VARCHAR(3) NOT NULL,
  72. hotelNo VARCHAR(6) NOT NULL,
  73. type VARCHAR(20) NOT NULL,
  74. price int NOT NULL,
  75. PRIMARY KEY (roomNo, hotelNo));
  76.  
  77. CREATE TABLE Booking
  78. (hotelNo VARCHAR(6) NOT NULL,
  79. guestNo VARCHAR(15) NOT NULL,
  80. dateFrom DATE,
  81. dateTo DATE,
  82. roomNo VARCHAR(3) NOT NULL,
  83. PRIMARY KEY (hotelNo, guestNo, dateFrom));
  84.  
  85. CREATE TABLE Guest
  86. (guestNo VARCHAR(15) NOT NULL,
  87. guestName VARCHAR(30) NOT NULL,
  88. guestAddress VARCHAR(30) NOT NULL,
  89. PRIMARY KEY (guestNo));
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top