DBMS: Sailing Bed and Breakfast Club

AUTHORS: SRUJIT BIRADAWADA, SATISH SADINEEDI

The Organization

The Sailing bed and breakfast club started back in late 1980s. The goals of the club are to efficiently provide cheap accommodation on the beaches for members and to provide a structure to allow members to socialize and network. To enable the members rate to remain low the bed and breakfast are available for the general public to use at higher rates. 

Present Practices 

Currently all the expenses & booking information are being maintained with in the paper system, which has following drawbacks: 

  • Number of staffs required to maintain all the paper works
  • Chances of manual errors
  • Difficult to track history records for customers
  • Manual & tedious efforts involved in calculating expenses for a customer 
  • Customer dis satisfaction 
  • Difficult to do demographic & geographic analysis 
  • Difficult for organization to run for a long term. 

What’s Needed Now?

What the club needs is something that will allow the booking officer to record all details of a booking accurately and quickly. They should be able to print this out as an invoice, which can post or email prior to the booking. The booking should then be capable of being transferred to a list containing all the bookings for that week. This list should allow the booking officer to quickly judge who can be accommodated and who can’t. Finally the list needs to be capable of being quickly communicated to the relevant caretaker.

To achieve above it is mandatory for an organization to maintain a BI System, which will help them to overcome above drawbacks and run the organization in a long-term. 

DESIGN 

Sailing bed and breakfast Model 

IMPLEMENTATION 

Description of Tables 

Creating Tables: 

CREATE TABLE Reservation_Details (  

Reservation_ID NUMBER(10) CONSTRAINT PK_RESERVATION_ID PRIMARY KEY, Members char(1),

Arrival_Date DATE,

Departure_Date DATE, 

No_Of_Adults NUMBER(10), 

No_Of_Children NUMBER(10), 

Room_Requirement_id NUMBER(10), 

Mode_Of_Payment VARCHAR2(20), 

Details_Of_Payment VARCHAR2(20), 

Customer_Id NUMBER(10) 

); 

CREATE TABLE Customer_Information 

Customer_ID NUMBER(10) CONSTRAINT PK_CUST_ID PRIMARY KEY, 

First_Name VARCHAR2(50),

Surname VARCHAR2(50), 

Address VARCHAR2(50),

Suburb VARCHAR2(50),

Postcode VARCHAR2(50),

Phone VARCHAR2(50), 

BAndB_Location VARCHAR2(50) 

); 

CREATE TABLE ROOM_Information

(

Unique_Room_Id NUMBER(10) CONSTRAINT PK_ROOM_ID PRIMARY KEY, 

Room_Type VARCHAR2(50),

Private_Bath char(1),

Cost_Per_Night NUMBER(10),

Room_Alias VARCHAR2(50),

Maximum_No_of_People NUMBER(10)

); 

CREATE TABLE EMPLOYEE_Information ( 

EMPLOYEE_ID NUMBER(10) CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY, 

First_Name VARCHAR2(50),

Surname VARCHAR2(50),

Address VARCHAR2(50), 

Suburb VARCHAR2(50), 

Postcode VARCHAR2(50), 

Phone VARCHAR2(50), 

Employee_hire_date DATE 

); 

CREATE TABLE Invoice

(

Inovice_ID NUMBER(10) CONSTRAINT PK_invoice_ID PRIMARY KEY, 

Customer_id NUMBER(10) ,

Unique_Room_Id NUMBER(10),

Employee_Id NUMBER(10),

Reservation_Id NUMBER(10),

No_Of_Adults NUMBER(10),

No_Of_Children NUMBER(10),

BAndB_Charges NUMBER(10),

Days_Stayed NUMBER(10),

Total_Charges NUMBER(10) 

);    

ALTER TABLE Invoice ADD CONSTRAINT FK_CUST_ID_Invoice FOREIGN KEY 

(Customer_id) REFERENCES Customer_Information (Customer_id);

ALTER TABLE Invoice ADD CONSTRAINT FK_EMP_ID_Invoice FOREIGN KEY 

(Employee_Id) REFERENCES EMPLOYEE_Information (EMPLOYEE_ID); 

ALTER TABLE Invoice ADD CONSTRAINT FK_ROOM_ID_Invoice FOREIGN KEY 

(Unique_Room_Id) REFERENCES ROOM_Information (Unique_Room_Id);

ALTER TABLE Invoice ADD CONSTRAINT FK_RESER_ID_Invoice FOREIGN KEY 

(Reservation_Id) REFERENCES Reservation_Details (Reservation_Id); 

Insertion of Data: 

INSERT INTO CUSTOMER_INFORMATION VALUES

(11,’CHAVA’,’SUNIL’,’1/115 BONGY GV.’,’CARRUM’,’3197′,’03-9756432′,’GEELONG’); 

INSERT INTO CUSTOMER_INFORMATION VALUES

(12,’AHUJA’,’KOMAL’,’ALPINE VIEW’,’LOT LYNDHURST’,’3970′,’03- 93652738′,’GEELONG’);

INSERT INTO CUSTOMER_INFORMATION VALUES

(1001,’ANDREW’,’JAMES’,’23 GROENS ROAD’,’HEALESVILLE’,’3776′,’03- 56784904′,’CORIO’);

INSERT INTO CUSTOMER_INFORMATION VALUES

(1002,’SMITH’,’BATES’,’1/115 BONGY GV.’,’CARRUM’,’3197′,’03-9756432′,’CORIO’); INSERT INTO CUSTOMER_INFORMATION VALUES 

(1003,’CLARK’,’JUSTINE’,’ALPINE VIEW’,’LOT LYNDHURST’,’3970′,’03- 93652738′,’GEELONG’); 

Employee_Information: 

insert into employee_information

values

(

10, 

‘Seth’ ,

‘Vinay’,

’23 Groens Road’,

‘Healesville’,

‘3776’,

’03-56784904′,

to_date(’01/15/2013′,’mm/dd/yyyy’)

insert into employee_information 

values

(

11, 

‘Chava’, 

‘Sunil’,

‘1/115 Bongy Gv.’,

‘Carrum’,

‘3197’,

’03-9756432′,

to_date(’02/17/2012′,’mm/dd/yyyy’)

 )

insert into employee_information 

values

(

12,

‘Ahuja’,

‘Komal’,

‘Alpine View’,

‘Lot Lyndhurst’,

‘3970’,

’03-93652738′,

to_date(’03/15/2015′,’mm/dd/yyyy’)

Reservation_Details 

insert into Reservation_Details 

values(

10001 ,

0, 

to_date(’04/26/2014′,’mm/dd/yyyy’),

to_date(’04/27/2014′,’mm/dd/yyyy’) , 

1, 

1,

101 ,

‘Credit Card’ , 

‘2387’ ,

1001

); 

insert into Reservation_Details

values(

10002 ,

0, 

to_date(’04/25/2015′,’mm/dd/yyyy’) , 

to_date(’04/28/2015′,’mm/dd/yyyy’) , 

2, 

0,

103 ,

‘Credit Card’ ,

 ‘2384’ ,

1002 

);

insert into Reservation_Details 

values(

10003 ,

0,

to_date(’06/21/2015′,’mm/dd/yyyy’),

to_date(’06/23/2015′,’mm/dd/yyyy’) , 

0,

2,

102 ,

‘DD’ ,

‘ABCDD456’ ,

1003

); 

ROOM_INFORMATION

INSERT INTO ROOM_INFORMATION 

VALUES

(101,

‘SINGLE’, 

‘N’,

100,

‘SEA FACED’, 

4

);

INSERT INTO ROOM_INFORMATION 

VALUES

(

102,

‘DOUBLE’, 

‘Y’, 

200,

‘SEA FACED’, 

6

); 

INSERT INTO ROOM_INFORMATION 

VALUES

(103,

‘QUEEN KING’, 

‘Y’,

300,

‘SEA FACED’, 

7

); 

INVOICE: 

INSERT INTO INVOICE VALUES (1, 1001, 101, 10, 10001, 1, 1, 75, 1, 175); 

INSERT INTO INVOICE VALUES (2, 1002, 102, 12, 10002, 2, 0, 80, 3, 680); 

INSERT INTO INVOICE VALUES (3, 1003, 103, 10, 10003, 0, 2, 80, 2, 680); 

Creating Table reservation_audit: 

CREATE TABLE reservation_audit 

( Reservation_ID number(10), 

Members char(1),

Arrival_Date DATE,

Departure_Date DATE,

No_Of_Adults NUMBER(10), 

No_Of_Children NUMBER(10), 

Room_Requirement_id NUMBER(10), 

Mode_Of_Payment VARCHAR2(20), 

Details_Of_Payment VARCHAR2(20), 

Customer_Id NUMBER(10), 

reason_of_cancellation varchar2(500), 

date_of_cancellation date ); 

Creating table Invoice_audit: 

create table Invoice_audit 

(Inovice_ID number(10), 

Customer_id number(10), 

Unique_Room_

Id number(10), 

Employee_Id number(10), 

Reservation_Id number(10), 

No_Of_Adults number(10), 

No_Of_Children number(10), 

BAndB_Charges number(10), 

old_Days_Stayed number(10), 

old_Total_Charges number(10), 

new_Days_Stayed number(10), 

new_Total_Charges number(10), 

user_name varchar2(50),

 date_of_update date) 

Trigger to make an entry in audit table to audit all the reservations cancellations 

CREATE OR REPLACE TRIGGER reservation_cancelled 

AFTER DELETE

ON Reservation_Details 

FOR EACH ROW 

DECLARE 

v_reason varchar2(500); 

BEGIN 

— Find username of person performing the DELETE on the table 

SELECT ‘Due to Poor Room Service’ INTO v_reason

FROM dual;

— Insert record into audit table 

INSERT INTO reservation_audit

Reservation_ID,

Members, 

Arrival_Date,

Departure_Date,

No_Of_Adults,

No_Of_Children,

Room_Requirement_id,

Mode_Of_Payment,

Details_Of_Payment,

Customer_Id,

reason_of_cancellation,

date_of_cancellation

)

VALUES ( 

:old.Reservation_ID,

:old.Members,

:old.Arrival_Date,

:old.Departure_Date,

:old.No_Of_Adults,

:old.No_Of_Children,

:old.Room_Requirement_id,

:old.Mode_Of_Payment,

:old.Details_Of_Payment,

:old.Customer_Id,

v_reason, 

sysdate); 

END; 

Trigger to track any update made in Invoice fact table & corresponding entry in audit table with old & new values

CREATE OR REPLACE TRIGGER Invoice_after_update

AFTER UPDATE 

ON Invoice

FOR EACH ROW 

DECLARE 

v_username varchar2(10); 

BEGIN

SELECT ‘Srikant’ INTO v_username FROM dual; 

INSERT INTO Invoice_audit

(

Inovice_ID ,

Customer_id ,

Unique_Room_Id ,

Employee_Id ,

Reservation_Id ,

No_Of_Adults ,

No_Of_Children ,

BAndB_Charges ,

old_Days_Stayed ,

old_Total_Charges ,

new_Days_Stayed ,

new_Total_Charges,

user_name,

date_of_update

)

VALUES

(

:old.Inovice_ID , 

:old.Customer_id , 

:old.Unique_Room_Id , 

:old.Employee_Id , 

:old.Reservation_Id , 

:old.No_Of_Adults , 

:old.No_Of_Children , 

:old.BAndB_Charges ,

:old.Days_Stayed , 

:old.Total_Charges , 

:new.Days_Stayed,

:new.Total_Charges,

v_username,

sysdate );

END; 

–View to see all customers & their respective reservation details visited after 01-Jan-1990– 

CREATE OR REPLACE VIEW V_SHOW_CUSTOMER_RESERVATION AS 

SELECT ARRIVAL_DATE, 

DEPARTURE_DATE,

NO_OF_ADULTS,

NO_OF_CHILDREN,

ROOM_REQUIREMENT_ID,

MODE_OF_PAYMENT,

DETAILS_OF_PAYMENT,

FIRST_NAME,

SURNAME, 

ADDRESS, 

SUBURB, 

POSTCODE, 

PHONE, 

BANDB_LOCATION

FROM RESERVATION_DETAILS, 

CUSTOMER_INFORMATION WHERE RESERVATION_DETAILS.CUSTOMER_ID = 

CUSTOMER_INFORMATION.CUSTOMER_ID

AND ARRIVAL_DATE BETWEEN TO_DATE(’01-01-1990′, ‘DD-MM-YYYY’) AND 

SYSDATE 

–VIEW TO SEE ALL CUSTOMERS & THIER RESPECTIVE INVOICE & DATES OF RESERVATIONS AMOUNT DETAILS VISITED AFTER 01-JAN-1990–

CREATE OR REPLACE VIEW V_SHOW_CUSTOMER_INVOICE AS

SELECT INVOICE.NO_OF_ADULTS , 

INVOICE.NO_OF_CHILDREN , 

INVOICE.BANDB_CHARGES , 

INVOICE.DAYS_STAYED , 

INVOICE.TOTAL_CHARGES , 

FIRST_NAME,

SURNAME, 

ADDRESS,

SUBURB, 

POSTCODE,

PHONE, 

BANDB_LOCATION,

ARRIVAL_DATE

DEPARTURE_DATE,

MODE_OF_PAYMENT,

DETAILS_OF_PAYMENT,

FROM INVOICE, 

CUSTOMER_INFORMATION,RESERVATION_DETAILS WHERE RESERVATION_DETAILS.CUSTOMER_ID = 

CUSTOMER_INFORMATION.CUSTOMER_ID

AND INVOICE.CUSTOMER_ID=CUSTOMER_INFORMATION.CUSTOMER_ID 

AND ARRIVAL_DATE BETWEEN TO_DATE(’01-01-1990′, ‘DD-MM-YYYY’) AND SYSDATE 

—DBMS CURSOR TO UPDATE THE NUMBER OF DAYS STAYED BY 1 LESS VALUE DUE TO SOME WRONG DATE INSERTION IN THE INVOICE TABLE—

DECLARE 

TOTAL_ROWS NUMBER(2); 

BEGIN

UPDATE INVOICE 

SET DAYS_STAYED = DAYS_STAYED – 1 

WHERE CUSTOMER_ID IN (10001,10002); 

COMMIT;

IF SQL%NOTFOUND THEN 

DBMS_OUTPUT.PUT_LINE(‘NO INVOICES UPDATES’); 

ELSEIF SQL%FOUND THEN 

TOTAL_ROWS := SQL%ROWCOUNT; 

DBMS_OUTPUT.PUT_LINE(TOTAL_ROWS || ‘ INVOICES UPDATED ‘); 

END IF; 

END; 

—DBMS PROCEDURE TO UPDATE THE NUMBER OF DAYS STAYED WITH A NEW INPUT VALUE CORRESPONDING TO A INPUT CUSTOMER ID

CREATE OR REPLACE PROCEDURE PRC_UPDATE_STAYED_DAYS(IN_NEW_DAYS_STAYED IN NUMBER, 

IN_CUSTOMER_ID IN NUMBER) IS V_NEW_DAYS_STAYED NUMBER(10); 

V_CUSTOMER_ID NUMBER(10); 

BEGIN

V_NEW_DAYS_STAYED := IN_NEW_DAYS_STAYED; V_CUSTOMER_ID := IN_CUSTOMER_ID; 

UPDATE INVOICE

SET DAYS_STAYED = V_NEW_DAYS_STAYED 

WHERE CUSTOMER_ID = V_CUSTOMER_ID; 

COMMIT; 

END PRC_UPDATE_STAYED_DAYS; 

—DBMS PL SQL Program TO UPDATE THE NUMBER OF DAYS STAYED WITH A prompt input VALUE CORRESPONDING TO A prompt INPUT CUSTOMER ID DECLARE 

V_NEW_DAYS_STAYED NUMBER(10); 

V_CUSTOMER_ID NUMBER(10); 

BEGIN

V_NEW_DAYS_STAYED := &IN_NEW_DAYS_STAYED; V_CUSTOMER_ID := &IN_CUSTOMER_ID; 

UPDATE INVOICE

SET DAYS_STAYED = V_NEW_DAYS_STAYED 

WHERE CUSTOMER_ID = V_CUSTOMER_ID; COMMIT; 

END;

 / 

Report 

— SQL QUERY TO PULL A REPORT BASED ON CUSTOMER INVOICE & RESERVATION ARRIVAL & DEPARTURE DATE DETAILS–

SELECT CUSTOMER_INFORMATION.FIRST_NAME, 

CUSTOMER_INFORMATION.SURNAME,

CUSTOMER_INFORMATION.ADDRESS,

CUSTOMER_INFORMATION.SUBURB,

CUSTOMER_INFORMATION.POSTCODE,

CUSTOMER_INFORMATION.PHONE, CUSTOMER_INFORMATION.BANDB_LOCATION, 

INVOICE.DAYS_STAYED,

INVOICE.TOTAL_CHARGES,

RESERVATION_DETAILS.ARRIVAL_DATE, RESERVATION_DETAILS.DEPARTURE_DATE, RESERVATION_DETAILS.MODE_OF_PAYMENT, RESERVATION_DETAILS.DETAILS_OF_PAYMENT 

FROM INVOICE, 

CUSTOMER_INFORMATION,

RESERVATION_DETAILS

WHERE CUSTOMER_INFORMATION.CUSTOMER_ID = INVOICE.CUSTOMER_ID AND RESERVATION_DETAILS.RESERVATION_ID = INVOICE.RESERVATION_ID; 

CONCLUSION 

What did you learn? 

The main learning from this assignment is that there should be strong BI DWH system designed for any organization to enable all the stake holders to perform a wider reporting. Wider reporting will help organization as well to grow & maintain customer satisfaction. It will also help for higher management to perform segment value analysis based on customer’s geographical & demographical value segments & allure the customers by providing them attractive offers and this will help us to make regular customers visit more regularly & rare customer to attract by the offers. 

Where can improvements be made? 

There should be in build reports by IT department for all the stake holders which should pull the data: 

  • Quick
  • Accurate
  • Prompt based reports to provide them flexibility to change prompts for dates, location etc 

Where did you fall short? 

  • There should be associated OLTP system to enable organization & customers to perform day to day level transactions quickly because considering the fact a BI System cannot fulfill the same purpose as an OLTP system. 
  • Need of a Web interface for a customer perspective to check the availabilities & make reservation online. 

Leave a Reply

Your email address will not be published. Required fields are marked *