I wanted to ask a question about how to use stored procedure instead of normal selecting stuff (e.g. select * from . where . ), include the use of cursor. And also, I have created a type ( servicePackage ) to specify the name of each services (many service provided by the hotel). After that, I have to go through with the stored procedure part which is the code can be seen below:
CREATE TYPE servicePackage AS TABLE (SERVICE_NAME CHAR(20)); CREATE PROCEDURE usp_createPackage -- INPUT PARAMETERS @ADVERTISEMENT_ID INT, @ADV_DESC VARCHAR(100), @START_DATE DATETIME, @END_DATE DATETIME, @PRICE DECIMAL(7,2), @EMPLOYEE_NAME CHAR(20), -- OUTPUT PARAMETER @ADV_PACKAGE_ID INT AS BEGIN SELECT @ADVERTISEMENT_ID, @ADV_DESC, @START_DATE, @END_DATE, @PRICE, @EMPLOYEE_NAME FROM ADVERTISEMENT INNER JOIN EMPLOYEE ON ADVERTISEMENT.ADVERTISEMENT_ID = EMPLOYEE.EMPLOYEE_NAME END From these code behind, I can't produce anything instead of I need to have an output parameters from advertised packaged. enter image description here (This picture shows that the specification to create a stored procedure) SQL Script (Hotel Management System)
CREATE TABLE HOTEL ( HOTEL_ID CHAR(10) CONSTRAINT PK_HOTEL_ID PRIMARY KEY, HOTEL_NAME CHAR(30) NOT NULL, HOTEL_ADDRESS VARCHAR(50) NOT NULL, HOTEL_COUNTRY CHAR(20) NOT NULL, HOTEL_PHONENO INT NOT NULL, HOTEL_DESCRIPTION VARCHAR(50) NOT NULL ); INSERT INTO HOTEL VALUES ('H001', 'LEISUREASIA', '10 HOLLAND ROAD', 'SINGAPORE', 87853515, 'CONFIRMED'); CREATE TABLE FACILITY_TYPE ( FACILITY_TYPE_ID CHAR(10) CONSTRAINT PK_FACILITY_TYPE_ID PRIMARY KEY, FACILITY_TYPE_NAME VARCHAR(20) NOT NULL, FACILITY_TYPE_DESC VARCHAR(50) NOT NULL, CAPACITY INT CHECK (CAPACITY > 0) NOT NULL, BASE_COST DECIMAL(7,2) CHECK (BASE_COST > 0) NOT NULL, COST_PERIOD DECIMAL(7,2) CHECK (COST_PERIOD > 0) NOT NULL ); INSERT INTO FACILITY_TYPE VALUES ('FT001', 'STANDARD ROOM', 'TWIN SIZE BED OR KING SIZE BED', 2, 50.00, 1000.00); INSERT INTO FACILITY_TYPE VALUES ('FT002', 'DELUXE ROOM', 'TWIN SIZE BED OR KING SIZE BED', 2, 70.00, 1000.00); INSERT INTO FACILITY_TYPE VALUES ('FT003', 'FAMILY ROOM', 'TWO TWIN SIZE BED AND ONE KING SIZE BED', 4, 90.00, 1000.00), ('FT004', 'CONFERENCE HALL 1', 'MULTI-PURPOSE HALL', 50, 500.00, 5000.00), ('FT005', 'CONFERENCE HALL 2', 'MULTI-PURPOSE HALL', 100, 1000.00, 5000.00), ('FT006', 'MEETING ROOM 1', 'ALL PURPOSE ROOM', 10, 50.00, 500.00), ('FT007', 'MEETING ROOM 2', 'ALL PURPOSE ROOM', 20, 100.00, 500.00); CREATE TABLE FACILITY ( FACILITY_ID CHAR(10) CONSTRAINT PK_FACILITY_ID PRIMARY KEY, FACILITY_NAME CHAR(20) NOT NULL, FACILITY_DESC VARCHAR(50) NOT NULL, FACILITY_STATUS CHAR(20) NOT NULL, HOTEL_ID CHAR(10) NOT NULL, CONSTRAINT FK_HOTEL_ID FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL(HOTEL_ID) ); INSERT INTO FACILITY VALUES ('F001', 'ROOMS', 'NEED BOOKING DETAILS', 'OK', 'H001'), ('F002', 'CONFERENCE VENUES', 'NEED BOOKING DETAILS', 'OK', 'H001'), ('F003', 'SWIMMING POOL', 'FREE OF CHARGE BY MEMBER', 'OK', 'H001'), ('F004', 'GYMS', 'FREE OF CHARGE BY MEMBER', 'OK', 'H001'), ('F005', 'RESTAURANTS', 'INTERNATIONAL FOODS (BREAKFAST/LUNCH/DINNER)', 'OK', 'H001'), ('F006', 'BUSINESS LOUNGE', 'FREE OF CHARGE', 'OK', 'H001'), ('F007', 'SIGHTSEEING', 'FREE OF CHARGE', 'OK', 'H001'), ('F008', 'ROOM SERVICE', 'FREE OF CHARGE', 'OK', 'H001'), ('F009', 'BAR', 'FREE OF CHARGE', 'OK', 'H001'), ('F010', 'RECEPTION', '24HR RECEPTION', 'OK', 'H001'); CREATE TABLE SERVICE_CATEGORY ( SERVICE_CATEGORY_ID CHAR(10) CONSTRAINT PK_SERVICE_CATEGORY_ID PRIMARY KEY, SERVICE_CATEGORY_NAME CHAR(20) NOT NULL, SERVICE_CATEGORY_DESC VARCHAR(50) NOT NULL, SERVICE_CATEGORY_TYPE CHAR(20) NOT NULL ); INSERT INTO SERVICE_CATEGORY VALUES ('SC001', 'FOOD&MEALS', 'PROVIDED BY THE HOTEL', 'RESTAURANT'); INSERT INTO SERVICE_CATEGORY VALUES ('SC002', 'ACCOMODATION', 'PROVIDED BY THE HOTEL', 'HOTEL'); INSERT INTO SERVICE_CATEGORY VALUES ('SC003', 'EVENT VENUES', 'PROVIDED BY THE HOTEL', 'PLACE'); INSERT INTO SERVICE_CATEGORY VALUES ('SC004', 'GYM', 'PROVIDED BY THE HOTEL', 'FITNESS CENTER'); INSERT INTO SERVICE_CATEGORY VALUES ('SC005', 'ENTERTAINMENT', 'PROVIDED BY THE HOTEL', 'SERVICE INDUSTRY'); INSERT INTO SERVICE_CATEGORY VALUES ('SC006', 'SITE-SEEING TOURS', 'PROVIDED BY THE HOTEL', 'SERVICE INDUSTRY'); INSERT INTO SERVICE_CATEGORY VALUES ('SC007', 'ROOM SERVICE', 'PROVIDED BY THE HOTEL', 'SERVICE INDUSTRY'); CREATE TABLE SERVICE ( SERVICE_ID CHAR(10) CONSTRAINT PK_SERVICE_ID PRIMARY KEY, SERVICE_NAME CHAR(20) NOT NULL, SERVICE_DESC VARCHAR(100) NOT NULL, SERVICE_RESTRICTIONS VARCHAR(100) NOT NULL, SERVICE_NOTES VARCHAR(100) NOT NULL, SERVICE_COMMENTS VARCHAR(100) NOT NULL, SERVICE_STATUS CHAR(20) NOT NULL, AVAILABLE_TIMES TIME NOT NULL, SERVICE_CAPACITY INT CHECK (SERVICE_CAPACITY>0) NOT NULL, SERVICE_CATEGORY_ID CHAR(10) NOT NULL, HOTEL_ID1 CHAR(10) NOT NULL, CONSTRAINT FK_SERVICE_CATEGORY_ID FOREIGN KEY (SERVICE_CATEGORY_ID) REFERENCES SERVICE_CATEGORY(SERVICE_CATEGORY_ID), CONSTRAINT FK_HOTEL_ID1 FOREIGN KEY (HOTEL_ID1) REFERENCES HOTEL(HOTEL_ID) ); INSERT INTO SERVICE VALUES ('S001', 'BREAKFAST', 'MEALS', 'NO FOOD AND DRINK FROM OUTSIDE', 'FOR ONE PERSON', 'INTERNATIONAL FOODS', 'OK', '07:00', '500', 'SC001', 'H001'); INSERT INTO SERVICE VALUES ('S002', 'LUNCH', 'MEALS', 'NO FOOD AND DRINK FROM OUTSIDE', 'FOR ONE PERSON', 'INTERNATIONAL FOODS', 'OK', '12:00', '500', 'SC001', 'H001'); INSERT INTO SERVICE VALUES ('S003', 'DINNER', 'MEALS', 'NO FOOD AND DRINK FROM OUTSIDE', 'FOR ONE PERSON', 'INTERNATIONAL FOODS', 'OK', '18:00', '500', 'SC001', 'H001'); INSERT INTO SERVICE VALUES ('S004', 'LAUNDRY', 'CLOTHING SERVICE', 'NO RESTRICTIONS', 'DRY CLEAN OR NORMAL LAUNDRY', 'ANY KINDS OF CLOTHES', 'OK', '08:00', '1000', 'SC007', 'H001'); INSERT INTO SERVICE VALUES ('S005', 'VENUES', 'EVENT VENUES CAN BE BOOK ONE MONTH BEFORE THE ACTUAL EVEMT', 'NO RESTRICTIONS', 'FOR MANY PERSON', 'PLACES PROVIDED BY THE HOTEL', 'OK', '07:00', '1000', 'SC003', 'H001'); INSERT INTO SERVICE VALUES ('S006', 'TAXIS', '24HR BOOKING TAXI', 'NO RESTRICTIONS', 'FOR ONE OR MANY PERSON', 'TRANSPORTATION', 'OK', '06:00', '500', 'SC002', 'H001'); INSERT INTO SERVICE VALUES ('S007', 'AIRPORT SHUTTLE BUS', 'BASED ON THE SCHEDULE', 'NO RESTRICTION', 'FOR ONE OR MANY PERSON', 'FREE SERVICE PROVIDED BY THE HOTEL', 'OK', '06:00', '500', 'SC002', 'H001'); INSERT INTO SERVICE VALUES ('S008', 'RENT-A-CAR', 'EARLY BOOKING IS PROVIDED AND CAN CHOOSE OF DIFFERENT CARS (E.G. SEDAN, SUV, MPV)', 'NO RESTRICTION', 'FOR ONE OR MANY PERSON', 'TRANSPORTATION', 'OK', '06:00', '500', 'SC002', 'H001'); INSERT INTO SERVICE VALUES ('S009', 'TOURS', 'EARLY BOOKING IS PROVIDED', 'NO RESTRICTION', 'FOR ONE OR MANY PERSON', 'TOUR GUIDE', 'OK', '08:00', '500', 'SC007', 'H001'); CREATE TABLE ADVERTISEMENT ( ADVERTISEMENT_ID INT CONSTRAINT PK_ADVERTISEMENT_ID PRIMARY KEY, ADVERTISEMENT_NAME CHAR(100) NOT NULL, ADV_DESC VARCHAR(100) NOT NULL, START_DATE DATETIME NOT NULL, END_DATE DATETIME NOT NULL, PRICE DECIMAL(7,2) CHECK (PRICE>0) NOT NULL, INCLUSIONS VARCHAR(100) NOT NULL, EXCLUSIONS VARCHAR(100) NOT NULL, STATUS CHAR(10) NOT NULL ); INSERT INTO ADVERTISEMENT VALUES (1, 'HOLIDAY PACKAGE 3D2N', 'THIS PACKAGE FITS PERFECTLY FOR A WEEKEND GETAWAYS','05-JUNE-2018', '07-JUNE-2018', 250.00, 'BREAKFAST FOR TWO', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (2, 'HOLIDAY PACKAGE 5D4N', 'ENJOY THE WEEKEND OFF WITH THIS PACKAGE','05-JUNE-2018', '09-JUNE-2018', 500.00, 'BREAKFAST FOR TWO', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (3, 'FAMILY PACKAGE 3D2N', 'ENJOY YOUR BONDING TIME WITH YOUR FAMILY WITH THIS PACKAGE','05-JUNE-2018', '07-JUNE-2018', 450.00, 'BREAKFAST FOR FOUR', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (4, 'FAMILY PACKAGE 5D4N', 'THIS PACKAGE IS PERFECT FOR FAMILY A WEEK LONG BONDING','05-JUNE-2018', '09-JUNE-2018', 900.00, 'BREAKFAST FOR FOUR', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (5, 'BUSINESS PACKAGE 3D2N', 'ENJOY THIS PACKAGE TO SUIT YOUR BUSINESS NEEDS','05-JUNE-2018', '07-JUNE-2018', 500.00, 'BREAKFAST FOR TWO, MEETING ROOM 1 (2HR/DAY)', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (6, 'SCHOOL-HOLIDAY 3D2N', 'THIS PACKAGE IS GREAT FOR CHILDREN TO ENJOY THE WEEKEND DURING SCHOOL HOLIDAY','05-JUNE-2018', '07-JUNE-2018', 200.00, 'BREAKFAST FOR TWO', 'DINNER', 'OK'); INSERT INTO ADVERTISEMENT VALUES (7, 'SCHOOL TRIP 3D2N', 'THIS PACKAGE IS PERFECT FOR SCHOOL TRIP','05-JUNE-2018', '07-JUNE-2018', 5000.00, '10 ROOMS INCL BREAKFAST,LUNCH, DINNER, TOUR GUIDE 3D', 'TAXI AND RENT-A-CAR', 'OK'); CREATE TABLE ADV_PACKAGE ( ADV_PACKAGE_ID INT CONSTRAINT PK_ADV_PACKAGE_ID PRIMARY KEY, ADVERTISEMENT_ID INT NOT NULL, CONSTRAINT FK_ADVERTISEMENT_ID FOREIGN KEY (ADVERTISEMENT_ID) REFERENCES ADVERTISEMENT(ADVERTISEMENT_ID) ); INSERT INTO ADV_PACKAGE VALUES (1, 1); INSERT INTO ADV_PACKAGE VALUES (2, 2); INSERT INTO ADV_PACKAGE VALUES (3, 3); INSERT INTO ADV_PACKAGE VALUES (4, 4); INSERT INTO ADV_PACKAGE VALUES (5, 5); INSERT INTO ADV_PACKAGE VALUES (6, 6); INSERT INTO ADV_PACKAGE VALUES (7, 7); CREATE TABLE ADV_SERVICE ( ADV_SERVICE_ID INT CONSTRAINT PK_ADV_SERVICE_ID PRIMARY KEY, ADVERTISEMENT_ID1 INT NOT NULL, CONSTRAINT FK_ADVERTISEMENT_ID1 FOREIGN KEY (ADVERTISEMENT_ID1) REFERENCES ADVERTISEMENT(ADVERTISEMENT_ID) ); INSERT INTO ADV_SERVICE VALUES (1, 1); INSERT INTO ADV_SERVICE VALUES (2, 2); INSERT INTO ADV_SERVICE VALUES (3, 3); INSERT INTO ADV_SERVICE VALUES (4, 4); INSERT INTO ADV_SERVICE VALUES (5, 5); INSERT INTO ADV_SERVICE VALUES (6, 6); INSERT INTO ADV_SERVICE VALUES (7, 7); CREATE TABLE PACKAGE_SERVICE ( ADVERTISEMENT_ID1 INT CONSTRAINT PK_ADVERTISEMENT_ID1 PRIMARY KEY, SERVICE_ID CHAR(10) NOT NULL, CONSTRAINT FK_SERVICE_ID FOREIGN KEY (SERVICE_ID) REFERENCES SERVICE(SERVICE_ID), ); INSERT INTO PACKAGE_SERVICE VALUES (1, 'S001'); INSERT INTO PACKAGE_SERVICE VALUES (2, 'S002'); INSERT INTO PACKAGE_SERVICE VALUES (3, 'S003'); INSERT INTO PACKAGE_SERVICE VALUES (4, 'S004'); INSERT INTO PACKAGE_SERVICE VALUES (5, 'S005'); INSERT INTO PACKAGE_SERVICE VALUES (6, 'S006'); INSERT INTO PACKAGE_SERVICE VALUES (7, 'S007'); INSERT INTO PACKAGE_SERVICE VALUES (8, 'S008'); INSERT INTO PACKAGE_SERVICE VALUES (9, 'S009'); CREATE TABLE RELATION_FACILITYTYPE_SERVICE ( FACILITY_TYPE CHAR(10) CONSTRAINT PK_FACILITY_TYPE PRIMARY KEY, SERVICE_ID1 CHAR(10), CONSTRAINT FK_SERVICE_ID1 FOREIGN KEY (SERVICE_ID1) REFERENCES SERVICE(SERVICE_ID) ); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT001', 'S001'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT002', 'S002'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT003', 'S003'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT004', 'S004'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT005', 'S005'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT006', 'S006'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT007', 'S007'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT008', 'S008'); INSERT INTO RELATION_FACILITYTYPE_SERVICE VALUES ('FT009', 'S009'); CREATE TABLE CUSTOMER ( CUSTOMER_ID CHAR(10) CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY, CUSTOMER_NAME CHAR(20) NOT NULL, CUSTOMER_ADDRESS VARCHAR(50) NOT NULL, CUSTOMER_PHONENO INT NOT NULL, CUSTOMER_COUNTRY CHAR(20) NOT NULL, CUSTOMER_EMAIL_ADDRESS VARCHAR(50) NOT NULL ); INSERT INTO CUSTOMER VALUES ('C001', 'KEVIN', '10 RIVER VALLEY ROAD', 85881252, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C002', 'DAVID', '312 RIVER VALLEY ROAD', 64872515, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C003', 'HARRY', '2 RIVER VALLEY CLOSE', 85881252, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C004', 'LEONARD', '32 JURONG EAST ROAD', 53251548, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C005', 'EDWIN', '155 HOLLAND ROAD', 98784565, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C006', 'MICHELLE', '80 WOODLANDS ROAD', 96923511, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C007', 'STACY', '77 TAMPINES ROAD', 91232587, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C008', 'VANIA', '35 SERANGOON ROAD', 87843521, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C009', 'MONICA', '225 TIONG BAHRU ROAD', 93665251, 'SINGAPORE', '[email protected]'); INSERT INTO CUSTOMER VALUES ('C010', 'MICHAEL', '8 TOA PAYOH ROAD', 87878888, 'SINGAPORE', '[email protected]'); CREATE TABLE EMPLOYEE ( EMPLOYEE_ID CHAR(10) CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY, EMPLOYEE_NAME CHAR(20) NOT NULL, EMPLOYEE_PHONENO INT NOT NULL, EMPLOYEE_EMAIL_ADDRESS VARCHAR(50) NOT NULL, EMPLOYEE_POSITION CHAR(20) NOT NULL ); INSERT INTO EMPLOYEE VALUES ('E001', 'BIMA', 88888080, '[email protected]', 'MANAGER'); INSERT INTO EMPLOYEE VALUES ('E002', 'SHEILLA', 86501234, '[email protected]', 'STAFF'); INSERT INTO EMPLOYEE VALUES ('E003', 'FERDY', 60512354, '[email protected]', 'STAFF'); INSERT INTO EMPLOYEE VALUES ('E004', 'VANESSA', 98574512, '[email protected]', 'FINANCE'); INSERT INTO EMPLOYEE VALUES ('E005', 'ARIEF', 91023514, '[email protected]', 'DIRECTOR'); CREATE TABLE RESERVATION ( RESERVATION_ID CHAR(10) CONSTRAINT PK_RESERVATION_ID PRIMARY KEY, R_NAME CHAR(20) NOT NULL, R_STATUS CHAR(20) NOT NULL, R_TYPE CHAR(20) NOT NULL, R_PLACEDATETIME DATETIME NOT NULL, R_DISCOUNT_PERCENTAGE INT NOT NULL, R_DESCRIPTION VARCHAR(50) NOT NULL, CUSTOMER_ID CHAR(10), EMPLOYEE_ID CHAR(10), CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID), CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID) ); INSERT INTO RESERVATION VALUES ('R001', 'RESERVATION 1', 'CONFIRMED', 'ONLINE', '01-JUNE-2018', 0, 'HOTEL RESERVATION', 'C001', 'E002'); INSERT INTO RESERVATION VALUES ('R002', 'RESERVATION 2', 'CONFIRMED', 'PHONE', '01-JUNE-2018', 0, 'HOTEL RESERVATION', 'C002', 'E002'); INSERT INTO RESERVATION VALUES ('R003', 'RESERVATION 3', 'CONFIRMED', 'PHONE', '02-JUNE-2018', 0, 'HOTEL RESERVATION', 'C003', 'E003'); INSERT INTO RESERVATION VALUES ('R004', 'RESERVATION 4', 'CONFIRMED', 'IN-PERSON', '05-JUNE-2018', 0, 'HOTEL RESERVATION', 'C004', 'E003'); INSERT INTO RESERVATION VALUES ('R005', 'RESERVATION 5', 'CONFIRMED', 'IN-PERSON', '05-JUNE-2018', 0, 'HOTEL RESERVATION', 'C005', 'E002'); INSERT INTO RESERVATION VALUES ('R006', 'RESERVATION 6', 'CONFIRMED', 'ONLINE', '25-MAY-2018', 0, 'HOTEL RESERVATION', 'C006', 'E002'); INSERT INTO RESERVATION VALUES ('R007', 'RESERVATION 7', 'CONFIRMED', 'PHONE', '26-MAY-2018', 0, 'HOTEL RESERVATION', 'C007', 'E003'); INSERT INTO RESERVATION VALUES ('R008', 'RESERVATION 8', 'CONFIRMED', 'PHONE', '27-MAY-2018', 0, 'HOTEL RESERVATION', 'C008', 'E003'); INSERT INTO RESERVATION VALUES ('R009', 'RESERVATION 9', 'CONFIRMED', 'ONLINE', '28-MAY-2018', 0, 'HOTEL RESERVATION', 'C009', 'E003'); INSERT INTO RESERVATION VALUES ('R010', 'RESERVATION 10', 'CONFIRMED', 'ONLINE', '29-MAY-2018', 0, 'HOTEL RESERVATION', 'C010', 'E002'); CREATE TABLE BOOKING_DETAILS ( BOOKING_DETAILS_ID CHAR(10) CONSTRAINT PK_BOOKING_DETAILS_ID PRIMARY KEY, BD_START_DATE DATETIME NOT NULL, BD_END_DATE DATETIME NOT NULL, BD_QUANTITY INT CHECK (BD_QUANTITY>0) NOT NULL, BD_TOTAL_PRICE DECIMAL(7,2) CHECK (BD_TOTAL_PRICE>0) NOT NULL, ADVERTISEMENT_ID2 INT, RESERVATION_ID CHAR(10), CONSTRAINT FK_ADVERTISEMENT_ID2 FOREIGN KEY (ADVERTISEMENT_ID2) REFERENCES ADVERTISEMENT(ADVERTISEMENT_ID), CONSTRAINT FK_RESERVATION_ID FOREIGN KEY (RESERVATION_ID) REFERENCES RESERVATION(RESERVATION_ID) ); INSERT INTO BOOKING_DETAILS VALUES ('BD001', '05-JUNE-2018', '07-JUNE-2018', 2, 550.00, 1, 'R001'); INSERT INTO BOOKING_DETAILS VALUES ('BD002', '05-JUNE-2018', '09-JUNE-2018', 3, 950.00, 2, 'R002'); INSERT INTO BOOKING_DETAILS VALUES ('BD003', '05-JUNE-2018', '07-JUNE-2018', 3, 800.00, 3, 'R003'); INSERT INTO BOOKING_DETAILS VALUES ('BD004', '05-JUNE-2018', '09-JUNE-2018', 2, 450.00, 4, 'R004'); INSERT INTO BOOKING_DETAILS VALUES ('BD005', '05-JUNE-2018', '07-JUNE-2018', 4, 500.00, 5, 'R005'); INSERT INTO BOOKING_DETAILS VALUES ('BD006', '05-JUNE-2018', '07-JUNE-2018', 2, 500.00, 6, 'R006'); INSERT INTO BOOKING_DETAILS VALUES ('BD007', '05-JUNE-2018', '07-JUNE-2018', 1, 7550.00, 7, 'R007'); INSERT INTO BOOKING_DETAILS VALUES ('BD008', '05-JUNE-2018', '07-JUNE-2018', 1, 1050.00, 3, 'R008'); INSERT INTO BOOKING_DETAILS VALUES ('BD009', '05-JUNE-2018', '07-JUNE-2018', 2, 525.00, 2, 'R009'); INSERT INTO BOOKING_DETAILS VALUES ('BD010', '05-JUNE-2018', '07-JUNE-2018', 2, 780.00, 2, 'R010'); CREATE TABLE FACILITY_BOOKING ( FACILITY_BOOKING_ID INT CONSTRAINT PK_FACILITY_BOOKING_ID PRIMARY KEY, FB_NAME CHAR(10) NOT NULL, FB_START_DATE DATETIME NOT NULL, FB_END_DATE DATETIME NOT NULL, FB_DESC VARCHAR(100) NOT NULL, FACILITY_ID CHAR(10), BOOKING_DETAILS_ID CHAR(10), CONSTRAINT FK_FACILITY_ID FOREIGN KEY (FACILITY_ID) REFERENCES FACILITY(FACILITY_ID), CONSTRAINT FK_BOOKING_DETAILS_ID FOREIGN KEY (BOOKING_DETAILS_ID) REFERENCES BOOKING_DETAILS(BOOKING_DETAILS_ID) ); INSERT INTO FACILITY_BOOKING VALUES (1, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD001'); INSERT INTO FACILITY_BOOKING VALUES (2, 'ROOM', '05-JUNE-2018', '09-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD002'); INSERT INTO FACILITY_BOOKING VALUES (3, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD003'); INSERT INTO FACILITY_BOOKING VALUES (4, 'ROOM', '05-JUNE-2018', '09-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD004'); INSERT INTO FACILITY_BOOKING VALUES (5, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD005'); INSERT INTO FACILITY_BOOKING VALUES (6, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD006'); INSERT INTO FACILITY_BOOKING VALUES (7, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD007'); INSERT INTO FACILITY_BOOKING VALUES (8, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD008'); INSERT INTO FACILITY_BOOKING VALUES (9, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD009'); INSERT INTO FACILITY_BOOKING VALUES (10, 'ROOM', '05-JUNE-2018', '07-JUNE-2018', 'HOTEL ROOM', 'F001', 'BD010'); CREATE TABLE PAYMENT ( PAYMENT_ID INT CONSTRAINT PK_PAYMENT_ID PRIMARY KEY, P_TYPE CHAR(50) NOT NULL, P_TOTAL_PRICE DECIMAL(7,2) NOT NULL, RESERVATION_ID1 CHAR(10), CONSTRAINT FK_RESERVATION_ID1 FOREIGN KEY (RESERVATION_ID1) REFERENCES RESERVATION(RESERVATION_ID) ); INSERT INTO PAYMENT VALUES (1, 'CREDIT CARD', 550.00, 'R001'); INSERT INTO PAYMENT VALUES (2, 'CREDIT CARD', 950.00, 'R002'); INSERT INTO PAYMENT VALUES (3, 'CREDIT CARD', 800.00, 'R003'); INSERT INTO PAYMENT VALUES (4, 'CREDIT CARD', 450.00, 'R004'); INSERT INTO PAYMENT VALUES (5, 'CREDIT CARD', 500.00, 'R005'); INSERT INTO PAYMENT VALUES (6, 'CASH', 500.00, 'R006'); INSERT INTO PAYMENT VALUES (7, 'CREDIT CARD', 7550.00, 'R007'); INSERT INTO PAYMENT VALUES (8, 'CREDIT CARD', 1050.00, 'R008'); INSERT INTO PAYMENT VALUES (9, 'CREDIT CARD', 525.00, 'R009'); INSERT INTO PAYMENT VALUES (10, 'CREDIT CARD', 955.00, 'R010'); CREATE TABLE RELATION_CUSTOMER_BOOKING ( CUSTOMER_ID1 CHAR(10) CONSTRAINT PK_CUSTOMER_ID1 PRIMARY KEY, BOOKING_DETAILS_ID1 CHAR(10) NOT NULL, CUSTOMER_ID2 CHAR(10), CONSTRAINT FK_CUSTOMER_ID1 FOREIGN KEY (CUSTOMER_ID2) REFERENCES CUSTOMER(CUSTOMER_ID), CONSTRAINT FK_BOOKING_DETAILS_ID1 FOREIGN KEY (BOOKING_DETAILS_ID1) REFERENCES BOOKING_DETAILS(BOOKING_DETAILS_ID) ); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C001', 'BD001', 'C001'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C002', 'BD002', 'C002'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C003', 'BD003', 'C003'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C004', 'BD004', 'C004'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C005', 'BD005', 'C005'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C006', 'BD006', 'C006'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C007', 'BD007', 'C007'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C008', 'BD008', 'C008'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C009', 'BD009', 'C009'); INSERT INTO RELATION_CUSTOMER_BOOKING VALUES ('C010', 'BD010', 'C010'); CREATE TABLE OTHERCHARGES ( OTHERCHARGES_ID CHAR(10) CONSTRAINT PK_OTHERCHARGES_ID PRIMARY KEY, OC_NAME CHAR(50) NOT NULL, OC_TYPE CHAR(50) NOT NULL, OC_PRICE DECIMAL(7,2) NOT NULL, BOOKING_DETAILS_ID2 CHAR(10) NOT NULL, CONSTRAINT FK_BOOKING_DETAILS_ID2 FOREIGN KEY (BOOKING_DETAILS_ID2) REFERENCES BOOKING_DETAILS(BOOKING_DETAILS_ID) ); INSERT INTO OTHERCHARGES VALUES ('OC001', 'NOTHING', 'NOTHING', 0.00, 'BD001'); INSERT INTO OTHERCHARGES VALUES ('OC002', 'NOTHING', 'NOTHING', 0.00, 'BD002'); INSERT INTO OTHERCHARGES VALUES ('OC003', 'NOTHING', 'NOTHING', 0.00, 'BD003'); INSERT INTO OTHERCHARGES VALUES ('OC004', 'NOTHING', 'NOTHING', 0.00, 'BD004'); INSERT INTO OTHERCHARGES VALUES ('OC005', 'NOTHING', 'NOTHING', 0.00, 'BD005'); INSERT INTO OTHERCHARGES VALUES ('OC006', 'NOTHING', 'NOTHING', 0.00, 'BD006'); INSERT INTO OTHERCHARGES VALUES ('OC007', 'NOTHING', 'NOTHING', 0.00, 'BD007'); INSERT INTO OTHERCHARGES VALUES ('OC008', 'NOTHING', 'NOTHING', 0.00, 'BD008'); INSERT INTO OTHERCHARGES VALUES ('OC009', 'NOTHING', 'NOTHING', 0.00, 'BD009'); INSERT INTO OTHERCHARGES VALUES ('OC010', 'DINNER FOR THREE', 'BUFFET', 175.00, 'BD010');