MMU Database Fundamentals Assignment

readme.md

TIS1101 Project

As you can see, the DDL/DML are split into 4 files.

Run them block by block to avoid issues.

booking_services_all.png

bookings_all.png

call_procedure_part1.png

call_procedure_part2.png

created_procedure.png

created_trigger_updatehotelrating1.png

created_trigger_updatehotelrating2.png

customers_all.png

extra_sql.png

hotels_all_after_inserting_reviews.png

hotels_all_default.png

reviews_all.png

rooms_all.png

Screenshot 2014-12-11 17.55.39.png

Screenshot 2014-12-11 17.55.56.png

Screenshot 2014-12-11 17.56.14.png

Screenshot 2014-12-11 17.56.46.png

Screenshot 2014-12-11 17.57.10.png

Screenshot 2014-12-11 17.57.29.png

Screenshot 2014-12-11 17.57.43.png

Screenshot 2014-12-11 17.58.00.png

Screenshot 2014-12-11 18.00.07.png

services_all.png

step01_create_db_and_tables.sql

-- create database
create database fakegoda;

connect to fakegoda;

-- create tables (remember use auto increment)

---- hotels
create table hotels(
	hotel_id int primary key not null generated always as identity (start with 1 increment by 1),
	hotel_name varchar(20),
	hotel_contact varchar(20),
	hotel_address varchar(100),
	hotel_rating decimal(3,1) with default 0
);

---- rooms
create table rooms(
	room_id int primary key not null generated always as identity (start with 1 increment by 1),
	hotel_id int not null,
	room_type varchar(200),
	room_price decimal(5,2) not null with default 0,
	foreign key (hotel_id) references hotels(hotel_id)
);

---- services
create table services(
	service_id int primary key not null generated always as identity (start with 1 increment by 1),
	hotel_id int not null,
	service_type varchar(20),
	service_price decimal(5,2) not null with default 0,
	foreign key (hotel_id) references hotels(hotel_id)
);

---- customers
create table customers(
	customer_id int primary key not null generated always as identity (start with 1 increment by 1),
	customer_name varchar(20),
	customer_ic bigint,
	customer_gender varchar(1),
	customer_contact varchar(20)
);

---- bookings
create table bookings(
	booking_id int primary key not null generated always as identity (start with 1 increment by 1),
	room_id int not null,
	customer_id int not null,
	booking_date date not null with default current date,
	stay int with default 1,
	extra varchar(255),
	foreign key (room_id) references rooms(room_id),
	foreign key (customer_id) references customers(customer_id)
);

---- reviews
create table reviews(
	review_id int primary key not null generated always as identity (start with 1 increment by 1),
	review_title varchar(255) not null,
	hotel_id int not null,
	customer_id int not null,
	review_rating int with default 3,
	review_comment varchar(3000), -- I am limited to 4096 bytes per row
	foreign key (hotel_id) references hotels(hotel_id),
	foreign key (customer_id) references customers(customer_id)
);

---- booking_services
create table booking_services(
	id int primary key not null generated always as identity (start with 1 increment by 1),
	booking_id int not null,
	service_id int not null,
	foreign key (booking_id) references bookings(booking_id),
	foreign key (service_id) references services(service_id)
);

step02_create_triggers_and_procedures.sql

-- views

---- a simple booking index UI for fakegoda to call them
create or replace view bookings_of_hotel as
	select booking_id, customer_name, customer_contact, booking_date, stay, extra
	from bookings
	left join customers
	on bookings.customer_id = customers.customer_id;

-- triggers

--- update hotels.hotel_rating on insert of reviews
create or replace trigger updateHotelRating1
	after insert on reviews
	referencing new as review
	for each row mode db2sql
	update hotels
		set hotel_rating = (select coalesce(avg(cast(review_rating as decimal(3,1))), 3.0)
							from reviews 
							where hotel_id = review.hotel_id)
		where hotel_id = review.hotel_id;

create or replace trigger updateHotelRating2
	after delete on reviews
	referencing old as review
	for each row mode db2sql
	update hotels
		set hotel_rating = (select coalesce(avg(cast(review_rating as decimal(3,1))), 3.0)
							from reviews 
							where hotel_id = review.hotel_id)
		where hotel_id = review.hotel_id;

create or replace trigger updateHotelRating3
	after update of review_rating on reviews
	referencing new as review
	for each row mode db2sql
	update hotels
		set hotel_rating = (select coalesce(avg(cast(review_rating as decimal(3,1))), 3.0)
							from reviews 
							where hotel_id = review.hotel_id)
		where hotel_id = review.hotel_id;

-- stored procedures

--- get list of most profitable bookings
create procedure getMostProfitableBookings
	(in topn int default 10)
begin
	declare c1 cursor with return for
	select booking.*, 
		booking.stay * 
		(select room_price from rooms where room_id = booking.room_id) + 
		(select coalesce(sum(service_price), 0) from services where service_id in 
		(select service_id from booking_services where booking_id = booking.booking_id))
		as total_amount 
		from bookings booking
		order by total_amount desc
		limit 0, topn;
	open c1;
end@

-- call getMostProfitableBookings(15)

step03_insert_mock_data.sql

-- insert mock data

---- hotels
insert into hotels
	(hotel_name, hotel_contact, hotel_address)
values
	('The Shophouse', '62988721', '48, Arab St'),
	('The Little Red Dot', '62947098', '125, Lavender St'),
	('Wink Hostel', '62222940', '8A, Mosque St');

select * from hotels;

---- rooms
insert into rooms
	(hotel_id, room_type, room_price)
values
	-- Shophouse
	(1, '6 Bed Dorm', 30),
	(1, '8 Bed Dorm', 24),
	(1, '12 Bed Dorm', 22),
	(1, 'Suite', 120),
	-- Lil Reddot
	(2, 'Single Room', 100),
	(2, 'Double Room', 150),
	(2, 'Giant Room', 230),
	-- Wink
	(3, 'Pod', 50),
	(3, 'Female Pod', 60),
	(3, 'Pod with Wifi', 70);

select * from rooms;

---- services
insert into services
	(hotel_id, service_type, service_price)
values
	(1, 'Snooker', 10),
	(1, 'Internet', 20),
	(1, 'Towel Rental', 15),
	(2, 'Conference Room', 100),
	(2, 'Massage', 30),
	(2, 'Business Center', 20),
	(3, 'Towel Rental', 10),
	(3, 'Soap', 5);

select * from services;

---- customers
insert into customers
	(customer_name, customer_ic, customer_gender, customer_contact)
values
	('Anonoz', 288990, 'M', '91746417'),
	('Howard', 884260, 'M', '94724950'),
	('Mildred', 755557, 'F', '35757978'),
	('Arthur', 311874, 'M', '23063544'),
	('Julia', 057249, 'F', '89479598'),
	('Arthur', 623247, 'F', '03923712'),
	('Julie', 376216, 'M', '38942905');

select * from customers;

---- bookings
insert into bookings
	(room_id, customer_id, booking_date, stay, extra)
values
	(1, 6, '2014-12-03', 4, 'Pellentesque at nulla.'),
	(2, 4, '2014-12-03', 4, 'Quisque arcu libero, rutrum ac, lobortis vel, dapibus at, diam.'),
	(3, 1, '2014-12-01', 5, 'Mauris lacinia sapien quis libero.'),
	(4, 2, '2014-12-05', 3, 'Duis bibendum, felis sed interdum venenatis, turpis enim blandit mi, in porttitor pede justo eu massa.'),
	(5, 5, '2014-12-03', 5, null),
	(6, 2, '2014-12-05', 2, null),
	(7, 3, '2014-12-02', 2, 'Ut at dolor quis odio consequat varius.'),
	(8, 5, '2014-12-04', 4, null),
	(9, 6, '2014-12-02', 2, null),
	(10, 3, '2014-12-06', 3, 'Suspendisse potenti.'),
	(3, 5, '2014-12-03', 1, 'In hac habitasse platea dictumst.'),
	(1, 4, '2014-12-01', 3, 'Praesent blandit.'),
	(4, 6, '2014-12-02', 3, null),
	(8, 5, '2014-12-06', 4, 'Morbi vestibulum, velit id pretium iaculis, diam erat fermentum justo, nec condimentum neque sapien placerat ante.'),
	(6, 1, '2014-12-05', 3, 'Donec ut dolor.'),
	(7, 5, '2014-12-02', 2, 'Phasellus in felis.'),
	(2, 4, '2014-12-04', 2, null),
	(2, 4, '2014-12-05', 3, null),
	(9, 4, '2014-12-02', 4, null),
	(10, 5, '2014-12-05', 3, null),
	(5, 4, '2014-12-05', 1, 'Integer tincidunt ante vel ipsum.'),
	(5, 1, '2014-12-04', 6, 'In blandit ultrices enim.'),
	(4, 7, '2014-12-03', 5, 'Suspendisse potenti.'),
	(3, 5, '2014-12-02', 4, 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.'),
	(2, 1, '2014-12-06', 3, 'Aliquam augue quam, sollicitudin vitae, consectetuer eget, rutrum at, lorem.');

select * from bookings;

---- reviews -- please create the trigger below first
insert into reviews
	(hotel_id, customer_id, review_title, review_rating, review_comment)
values
	(1, 6, 'Morbi porttitor lorem id ligula.', 3, 'Phasellus in felis. Donec semper sapien a libero.'),
	(2, 4, 'Nunc nisl.', 4, 'Mauris lacinia sapien quis libero. Nullam sit amet turpis elementum ligula vehicula consequat.'),
	(2, 1, 'Sed sagittis.', 5, 'Nunc nisl. Duis bibendum, felis sed interdum venenatis, turpis enim blandit mi, in porttitor pede justo eu massa.'),
	(2, 2, 'Nam nulla.', 4, 'Duis mattis egestas metus. Aenean fermentum. Donec ut mauris eget massa tempor convallis.'),
	(1, 5, 'Suspendisse potenti.', 2, null),
	(3, 2, 'In hac habitasse platea dictumst.', 1, null),
	(1, 3, 'Vivamus metus arcu, adipiscing molestie, hendrerit at, vulputate vitae, nisl.', 3, null),
	(3, 5, 'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.', 3, 'Curabitur at ipsum ac tellus semper interdum. Mauris ullamcorper purus sit amet nulla. Quisque arcu libero, rutrum ac, lobortis vel, dapibus at, diam.'),
	(2, 6, 'Nullam varius.', 2, null),
	(2, 3, 'Sed ante.', 1, null);

select * from reviews;
select * from hotels; -- check hotel_rating column is updated by trigger

---- booking_services
insert into booking_services
	(booking_id, service_id)
values
	(1, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 1) order by rand() limit 0, 1)),
	(3, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 3) order by rand() limit 0, 1)),
	(6, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 6) order by rand() limit 0, 1)),
	(8, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 8) order by rand() limit 0, 1)),
	(14, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 14) order by rand() limit 0, 1)),
	(17, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 17) order by rand() limit 0, 1)),
	(20, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 20) order by rand() limit 0, 1)),
	(23, (select service_id from services where hotel_id = (select (select hotel_id from rooms where room_id = booking.room_id) from bookings booking where booking_id = 23) order by rand() limit 0, 1));

select * from booking_services; -- used rand() function, everyone's db2 may differ

step04_extras.sql

-- which customer spent most of their life in hotel?
select c.customer_id, c.customer_name, b.stay_sum
from customers c
right join (
	select customer_id, sum(stay) as stay_sum
	from bookings
	group by customer_id
	having sum(stay) > 10
) as b
on c.customer_id = b.customer_id
order by b.stay_sum desc;