- Create the bookings table, Booking number, Customer ID, Date, Event Code, guest arrival time, deposit paid, staff ID.
- Create the EVENTS table. EVENTS (event code, event type, age, venue, max number, min number, cost per head)
- Link the two tables by using foreign key (event code)
2. Its not easy to query the database, e.g. they need to sort the database using different criteria.
2.1 All the events held in the terror zone
2.2 All events that hold at least 50 people and cost less than £30 per head
2.3 Bookings made for the 7th and 8th May 2003
2.4 Details of bookings for birthdays for kids.
3. The new system should be able to generate reports based on different queries
3.1 generate reports about all the events held in the terror zone.
3.2 Generate reports about all events that hold at least 50 people and cost less than £30 per head.
3.3 Generate report about Bookings made for the 7th and 8th May 2003
3.4 Generate a report about details of Bookings for birthdays for kids.
4. There should be templates available on the system to control the different rides.
4.1 Create a spreadsheet template for the new ride.
4.2 Enter a formula to calculate the total time for the first feature.
4.3 Copy the formula down for all the other features.
4.4 Insert a formula that uses a function to calculate the total runtime.
4.5 Make the name of the park larger than the rest of the text.
4.6 Make sure that the spreadsheet is clearly presented and easy to use.
5. The spreadsheet should be able to control running cost, loading time, queuing time, and duration for ride.
5.1 Create a spreadsheet to show the rides, run-times and capacity.
5.2 Enter a label ‘Load-time’ in the column next to ‘Total time’.
5.3 Enter a formula to calculate the total load-time for each ride.
5.4 The formula will use the value in ‘‘Load-time per person’’ cell (using an absolute cell reference or a named cell).
5.5 Use another column to add the load-time to the run-time to give the total ride time for each ride.
5.6 Sort the spreadsheet. Make sure that all columns are correctly sorted.
5.7 In a suitable cell, enter a formula that uses a function to calculate the average number of people waiting at any time.
5.8 Format the cell to numeric, 0 decimal places.
6.The new system should be able to produce charts showing all the information to be included in reports.
- Use the spreadsheet to create a suitable bar/column chart on a separate sheet.
- Legends should not be used.
- The title, axis and label should be added to graph
2.5 System requirements
Hardware requirements
Software requirements
Section 2.6 Input-processing-output
Objectives 1,2 and 3
Objectives 4,5 and 6
Section 3.3 Designs
Objectives 1,2 and 3
Data dictionary: Include
http://www.edexcel.org.uk/virtualcontent/69144.pdf
EVENTS Table
BOOKINGS Table
Field Name Data Type Length/Format
Event Code Text 3
Event Type Text 20
Age Text 8
Venue Text 20
Max Number Numeric Integer
Min Number Numeric Integer
Cost per head Currency £ sign 2 d.p.
Field Name Data Type Length/Format
Booking Num Text 5
Cust ID Text 7
Date Date/Time dd/mm/yyyy
Event Code Numeric 3
Guests Numeric Integer
Arrival Time Date/Time hh:mm
Deposit Paid Currency £ sign 0 d.p.
Staff ID Text 4
Screen Input forms
Bookings input form
Bookings number
Customer ID
Date
Event Code
Entity Relationship diagram
On EVENT can be booked many times, the relationship between EVENT and BOOKINGS
3.2 Screen input forms
Objectives 1,2 and 3
EVENTS Table
BOOKINGS Table
Field Name Data Type Length/Format
Event Code Text 3
Event Type Text 20
Age Text 8
Venue Text 20
Max Number Numeric Integer
Min Number Numeric Integer
Cost per head Currency £ sign 2 d.p.
Field Name Data Type Length/Format
Booking Num Text 5
Cust ID Text 7
Date Date/Time dd/mm/yyyy
Event Code Numeric 3
Guests Numeric Integer
Arrival Time Date/Time hh:mm
Deposit Paid Currency £ sign 0 d.p.
Staff ID Text 4
3.3 Spreadsheet design
Objectives 4,5 and 6
One EVENT can be booked many times, the relationship between EVENT and BOOKING is one-to-many.
EVENT is on the one side of the relationship and the BOOKINGS are on the other side.
Section 4 Implementation
Database (task1)
To create a BOOKINGS table (to include: bookings number,
Additional Requirements
- Create a chart showing in % the amount of time people spend on a ride (loading/ride times)
Search for all adult events.
Display cost per head
Deposit
Event
Date of events
- Additional IF statement
That checks if waiting times are reasonable or unreasonable
