This project worth 10 marks, will be distributed as following:

a.	Design the database, following an ER model.	(2 marks)
b.	Normalize the tables ( each table should be in 3NF at least)	(2 marks)
c.	Use MySQL or any other database to create the normalized tables.	(2 marks)
d.	Populate your tables with 5 rows at least.	(2 marks)
e.	Execute the sample requested queries.	(2 marks)

Each student has to submit one report about his/her project via the Blackboard contains the following:

ER Diagram.

All schemas before and after normalization.

All SQL statements of :

creating tables

inserting data in tables

queries.

Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.

Project 1

LSBU Outward Bound

LSBU runs an Outward Bound C-e-n-t-r-e based in the Lake District wishes to build a database to store and manage information about its business. The Outward Bound C-e-n-t-r-e takes bookings from clients for adventure holidays and team building events.

Clients are organisations such as schools, youth groups and businesses. Information stored about clients includes their unique client number, the clients name (School name, business name, etc.), and the name and contact details of the person representing the client organisation.

The C-e-n-t-r-e has a number of accommodation options: a four star hotel; a hostel with large dormitories for children and smaller shared rooms for supervising adults; and a small camping ground. The name and type of these options is stored, along with the total number of people they can accommodate.

Each booking has a unique booking number, the date that booking was made, and the dates of the stay. When making a booking, clients state how many people will be staying, and what type of accommodation is required. They also say whether they will use the restaurant or self-catering facilities. The booking will also include any additional facilities required by the client.

The center contains a number of additional facilities for hire. These include lecture theatres, teaching rooms and computing labs with Internet access. Portable multi-media projection and presentation equipment are also available for hire.

There are various outdoor pursuits facilities including: obstacle course, climbing-wall with safety equipment, paintball-wargames, canoes, hill walking etc. Details of these various facilities, including any hire charges, need to be stored on the database. During any booking, the client may use these facilities. The date when a facility is booked for use must be recorded.

Use of outdoor pursuit facilities is supervised by one or more trained members of staff. Staff may be trained to supervise more than one outdoor pursuit facility. Each member of staff has a unique staff number, and their name and contract details (full or part time) are also recorded.

SQL Queries:

Write a query to find which Customer has made the most bookings in the last 6 last months.

Write a query to find what is the average age of staff who are assigned to work on the outdoor pursuits.

Write a query to produce a list of all customers who have rented self-catering accommodation in the past year.

Write a stored procedure to calculate a final bill for a given customer who has used various facilities during their stay.

Write a stored procedure to provide a monthly report showing the total revenue raised by each outdoor pursuit.