How many guests live in Canada?

High Hopes Bed & Breakfast

Pauline Jones is the owner of High Hopes Bed & Breakfast in Whistler. Her Bed & Breakfast consists of four bedrooms that can be booked either online via an international

hotel booking site or over the phone. Each bedroom has its own name and

comes with either a Mountain or Lake view.

Guests come from all over the world and when they make a booking Pauline records this in an Excel spreadsheet, along with any special requirements. Many guests return year after year. Most guests will book 1 bedroom but some guests will book multiple rooms if they are in a group.

She also partners with a local activity company which offers outdoor excursions in the Whistler area, such as: skiing, mountaineering, cycling and rock-climbing. Pauline keeps a list of these activities along with their costs which is available to guests. If a guest chooses to book an outdoor excursion, they will be given a discount on their total booking.

Part A: Databases

Up until now, Pauline has been recording data in Excel Spreadsheets. She would like you to create a Database for her business.

The database should contain information on the following:

● Bedrooms

● Guests

● Bookings

● Outdoor activities

(A1) Design a relational database

In MS Word, create a diagram that shows the following information:

Entities/Attributes/Relationships

An example is ​shown here​. Note that your diagram should clearly identify primary and foreign keys as well as the type of relationship.

File name:​ Firstname_HH_Database_Design.docx

(A2) Create the Database in MS Access

Create a relational database in MS Access, creating tables for each entity and listing each attribute as a field. You will need to choose suitable data types.You can also set input masks and validation rules as appropriate. You will set up the relationships between the tables. You will also create forms for a more user friendly method of inputting data. You DO NOT need to add data into the tables.

(A3) Create Queries

Pauline would like to have queries set up to find out information such as:

● How many guests live in Canada

● Which customers have reserved more than 1 room

● Which customers have booked skiing as an outdoor activity (sort in descending order of

customer name)

File name:​ Firstname_HH_Database.accdb

Part B: Web Development

Pauline does not have her own website currently, she would like you to set up a sample “home” page which has information on the Bed & Breakfast, e.g. the different rooms and their features. A table can be used to hold images of the different rooms – all room images should be the same size. The page can also promote the additional services offered e.g. the outdoor excursions.

Headings, paragraphs and hyperlinks should also be used appropriately and the webpage should be designed with accessibility in mind.

How you design the page is entirely up to you – BUT it should follow general good web design principes, e.g. keep it simple, don’t overuse bright colours etc.

Content Styles

Aim to use a wide range of the tags Use both inline and internal CSS specified on the HTML Cheat sheet

File name: ​Firstname_HH_Web.html

       
Part C: Python

Pauline has asked for a program that can be used for potential guests to find out the cost of their stay. They will also be able to add the cost of outdoor activities if they wish to book one (or more). If the guest does book an outdoor activity, they will be entitled to a 10% discount of their total cost.

The program needs to ask the user how many rooms they would like to book. Each room is $120 a night. It also needs to ask how many nights they are staying. It then needs to ask how many outdoor activities they would like to book (if any). Each activity is $70.

The program will then need to calculate the total cost and output this in a suitable message. It will then decide whether the user is entitled to the 10% discount based on the information above. If they are, it will deduct 10% from the total and display a suitable message with the new total to the user. Otherwise, it will simply inform the guest that no discount is available.