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.