This is a assignment about MySQL.

The Script file is:

CREATE SCHEMA Tourista_DB; USE Tourista_DB;

-- Table `Region` -- ----------------------------------------------------- SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; START TRANSACTION; -- START TRANSACTION; DROP TABLE IF EXISTS `Booking` ; DROP TABLE IF EXISTS `Cottage_Facility` ; DROP TABLE IF EXISTS `Facility` ; DROP TABLE IF EXISTS `Cottage` ; DROP TABLE IF EXISTS `Customer` ; DROP TABLE IF EXISTS `Region` ;

CREATE TABLE IF NOT EXISTS `Region` ( `Region_Code` INT NOT NULL, `Region_Name` VARCHAR(45) NOT NULL, PRIMARY KEY (`Region_Code`) );

-- ----------------------------------------------------- -- Table `Customer` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Customer` ( `Customer_ID` INT NOT NULL, `Customer_LName` VARCHAR(45) NOT NULL, `Customer_FName` VARCHAR(45) NOT NULL, `Customer_MInitial` VARCHAR(5) NULL, `Customer_Email` VARCHAR(45) NULL, `Customer_Phone` VARCHAR(15) NULL, PRIMARY KEY (`Customer_ID`) );

-- ----------------------------------------------------- -- Table `Facility` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Facility` ( `Facility_ID` INT NOT NULL, `Facility_Name` VARCHAR(45) NOT NULL, `Facility_IsShared` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`Facility_ID`) );

-- ----------------------------------------------------- -- Table `Cottage` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Cottage` ( `Cottage_ID` INT NOT NULL, `Cottage_Name` VARCHAR(45) NOT NULL, `Cottage_NumRooms` INT NOT NULL, `Region_Code` INT NOT NULL, PRIMARY KEY (`Cottage_ID`), CONSTRAINT `fk_Cottage_Region` FOREIGN KEY (`Region_Code`) REFERENCES `Region` (`Region_Code`) );

-- ----------------------------------------------------- -- Table `Cottage_Facility` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Cottage_Facility` ( `CF_Num` INT NOT NULL, `Facility_ID` INT NOT NULL, `Cottage_ID` INT NOT NULL, PRIMARY KEY (`CF_Num`), CONSTRAINT `fk_Cottage_Facility_Facility1` FOREIGN KEY (`Facility_ID`) REFERENCES `Facility` (`Facility_ID`), CONSTRAINT `fk_Cottage_Facility_Cottage1` FOREIGN KEY (`Cottage_ID`) REFERENCES `Cottage` (`Cottage_ID`) );

-- ----------------------------------------------------- -- Table `Booking` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Booking` ( `Booking_ID` INT NOT NULL, `Booking_Date` DATE NOT NULL, `Booking_Price` INT NOT NULL, `Customer_ID` INT NOT NULL, `Cottage_ID` INT NOT NULL, PRIMARY KEY (`Booking_ID`), CONSTRAINT `fk_Booking_Customer1` FOREIGN KEY (`Customer_ID`) REFERENCES `Customer` (`Customer_ID`), CONSTRAINT `fk_Booking_Cottage1` FOREIGN KEY (`Cottage_ID`) REFERENCES `Cottage` (`Cottage_ID`) );

-- ----------------------------------------------------- -- Data Insertion -- ----------------------------------------------------- insert into Region values (7821,'Halifax'); insert into Region values (7822,'Bear River'); insert into Region values (7823,'Pictou'); insert into Region values (9021,'Sherbrooke'); insert into Region values (9022,'Digby'); insert into Region values (9023,'Kingston'); insert into Region values (9024,'Shelburne');

insert into Cottage values (1,'Atlantic Beach Resort Cottage 1',4,9021); insert into Cottage values (2,'Atlantic Beach Resort Cottage 2',3,9021); insert into Cottage values (3,'Atlantic Beach Resort Cottage 3',2,9021); insert into Cottage values (4,'Green Ocean Cottage 20',3,9024); insert into Cottage values (5,'Green Ocean Cottage 30',2,9024); insert into Cottage values (6,'Green Ocean Cottage 40',1,9024); insert into Cottage values (7,'Surf Cottage 1',2,7821); insert into Cottage values (8,'Surf Cottage 2',3,7821); insert into Cottage values (9,'Lagoona',4,7823); insert into Cottage values (10,'Pine Resort A',1,9021);

insert into Customer values (10010,'DalFCS','Student','','myemail@mymail.com','9021234567'); insert into Customer values (10011,'Perkins','Bob','','bob@whatmail.ca','5901293456'); insert into Customer values (10012,'Oscar','Charlie','','coscar@nomail.org','6222257079'); insert into Customer values (10013,'Sybil','Dave','G','devensy007@mymail.com','4140069876'); insert into Customer values (10014,'Evenfield','Eve','S.Y','eve@whymail.ca','2121129987'); insert into Customer values (10015,'Mitchell','Frank','','frankmitchell@nomail.org','2134567890'); insert into Customer values (10016,'Damon','Grace','K','gracep@mymail.com','9024346565'); insert into Customer values (10017,'Singh','Robin','C','rsingh@yahoo.com ','9011237745');

insert into Facility values (1,'Coffee Machine',0); insert into Facility values (2,'Refrigerator',0); insert into Facility values (3,'Air Conditioner',0); insert into Facility values (4,'Stove',0); insert into Facility values (5,'Parking',0); insert into Facility values (6,'Pool',1); insert into Facility values (7,'Playground',1); insert into Facility values (8,'Gym',1); insert into Facility values (9,'Sauna',1); insert into Facility values (10,'Beach',1);

insert into Cottage_Facility values (1,1,1); insert into Cottage_Facility values (2,2,1); insert into Cottage_Facility values (3,3,1); insert into Cottage_Facility values (4,1,2); insert into Cottage_Facility values (5,2,2); insert into Cottage_Facility values (6,3,2); insert into Cottage_Facility values (7,1,3); insert into Cottage_Facility values (8,2,3); insert into Cottage_Facility values (9,3,3); insert into Cottage_Facility values (10,6,3); insert into Cottage_Facility values (11,7,3); insert into Cottage_Facility values (12,10,3); insert into Cottage_Facility values (13,1,9); insert into Cottage_Facility values (14,6,9); insert into Cottage_Facility values (15,10,9); insert into Cottage_Facility values (16,1,10); insert into Cottage_Facility values (17,10,10); insert into Cottage_Facility values (18,2,4); insert into Cottage_Facility values (19,5,4);

insert into Booking values (1,'2018/01/15',594,10010,9); insert into Booking values (2,'2018/01/15',384,10015,1); insert into Booking values (3,'2018/01/15',1176,10012,4); insert into Booking values (4,'2018/01/10',300,10010,3); insert into Booking values (5,'2018/01/10',144,10011,3); insert into Booking values (6,'2018/01/01',144,10016,3); insert into Booking values (7,'2017/12/24',654,10010,9); insert into Booking values (8,'2017/12/22',654,10014,9); insert into Booking values (9,'2017/12/20',588,10012,7); insert into Booking values (10,'2017/12/15',180,10010,10);

COMMIT;

Q1: List the first and last names of all customers who have ever booked a cottage.

Q2: For all customers with bookings, list their first name, their last name, the name of the cottage they booked, and the date of each of their booking. (Note: Some customers may have more than one bookings. List them all).

Q3: List the last name of each customer and the number of their bookings with Tourista.

Q4: List the name of each cottage and the number of facilities it has.

Q5: List the names of cottage Lagoonas shared facilities.

Q6: List the unique names (i.e. no duplicates) of unshared facilities in all cottages that were ever booked.

Q7: List all details of the cottages in Halifax.

Q8: List the difference between the highest and lowest booked price of all bookings.

Q9: List the names of all regions with the average, minimum and maximum price of their booked cottages (Regions with no cottages booked are excluded).

Q10: All bookings for Tourista are done through a website that charges a commission of 5% of the booked price. Tourista management wants to find out the total commission owed for each cottage. List the name of each cottage along with the number of times that cottage has ever been booked, the total income from those bookings and the websites total commission from that cottage.

INSTRUCTIONS "Tourista Nova Scotia" is a cottage rental company that owns cottages across several Regions of Nova Scotia, including Halifax. The company owns several cottages in some regions, but does not have a presence in all regions of the province. Each cottage may provide additional facilities like internet, coffee machine, or a small kitchen. However, certain facilities like pools, gyms and kids' play areas are shared by many cottages. Clients can book one or more cottages at a time. Tourista asked you to implement a database that stores data of their customers, cottages and bookings. The complete script for implementing and populating the database has been provided for you (as a script file in Brightspace) so that you can create it in Mysql database. The same script has been used to create and populate the database in this assignment Entity Relationship Diagram (ERD) of the database showing the various tables, their Primary and Foreign keys as well as relationships is shown below. Region Region_Code INT Region_Name VARCHAR(45) Cottage Cottage_ID INT Cottage_Name VARCHAR(45) Cottage_NumRooms INT Region Code INT HE----- H----- Cottage_Facility CF_Num INT Facility_ID INT Cottage_ID INT Indexes Indexes Indexes Customer Customer_ID INT Customer LName VARCHAR(45) Customer_FName VARCHAR(45) Customer_MInitial VARCHAR(5) Customer Email VARCHAR(45) Customer_Phone VARCHAR(15) Booking Booking_ID INT Booking Date DATE Booking Price INT Customer_ID INT Cottage ID INT Indexes Facility Facility_ID INT Facility_Name VARCHAR(45) Facility_IsShared BOOLEAN Indexes Indexes Data retrieval for informational and reporting purposes is one of the primary reason for having databases. SELECT queries in relational databases retrieve useful information that enables users to make knowledgable decisions about their business or organization. Tourista's management has no idea of SQL queries, so they have asked their database developer (i.e. you) to provide the following data for insights to their business that will help them with their key business decisions. In a nutshell, for each question in this assignment, you need to write a SELECT query that returns the desired results. Please note that the table and column names, and the column labels (if applicable) are case-sensitive. You can examine your query's test results to identify and fix errors in your queries. Helpful Hints: [1] It will be helpful if you print out the ERD shown above, and [2] It will be quicker if you use MySQL to write and run the queries first, the copy and paste your answers here and verify your answer using the testcases given for each