{63}
2.2 Entity Relationship Diagram
2.3 Datastructures
BOOKINGS (bookingID, bookingCustomer, bookingDate, bookingType, dateBooked, bookingStaff, status, partySize, paid, contactCustomer, bookingNotes)
Attribute / Description / Data Type / Length / ExamplebookingID / Primary Key. A unique ID that is assigned to each individual booking. / Autonumber / Long Integer / 243
bookingCustomer / Foreign Key. The customer ID of the customer who has made the booking. / Integer / Long Integer / 13
bookingDate / The date the booking is planned for. / Date/Time / Short Date / 29/07/2010
bookingType / Foreign Key. The ID of the type of booking. / Integer / Long Integer / 9
dateBooked / The date the booking was originally made. / Date/Time / Short Date / 26/07/2010
bookingStaff / Foreign Key. The ID of the member of staff who made the booking. / Integer / Long Integer / 1001
status / The current status of the booking. / String / 11 / Definite
partySize / The size of the party. / Integer / Long Integer / 12
paid / Whether the customer has paid for their booking. / Boolean / (1 bit) / TRUE
contactCustomer / Whether the customer needs to be contacted to confirm their booking. / Boolean / (1 bit) / TRUE
bookingNotes / Any additional notes about the booking. / Memo / (<63,999)
CUSTOMER (customerID, customerForename, customerSurname, telephone, email, member, NAYCaffiliated, squashMember, customerNotes)
customerID / Primary Key. A unique ID for each customer. / Autonumber / Long Integer / 278
customerForename / The forename or initial of the customer. / String / 25 / Joe
customerSurname / The surname of the customer, or organisation name. / String / 25 / Bloggs
telephone / A contact number for the customer. / String / 11 / 01604786945
email / The customer’s email address. / String / 50 / joe.bloggs@
hotmail.co.uk
member / Whether the customer is a member of Benham. / Boolean / (1 bit) / TRUE
NAYCaffiliated / Whether the customer is affiliated to the NAYC. / Boolean / (1 bit) / TRUE
squashMember / Whether the customer has a squash membership. / Boolean / (1 bit) / FALSE
customerNotes / Any notes relating to the customer. / Memo / (<63,999)
STAFF (staffID, staffForname, staffSurname, title, employed, password)
staffID / Primary Key. A unique ID for each customer. / Integer / Long Integer / 1001
staffForename / The forename of the employee. / String / 25 / David
staffSurname / The surname of the employee. / String / 25 / Jones
title / The employee’s title. / String / 4 / Mr
employed / Whether the employee is still employed by the organisation. / Boolean / (1bit) / TRUE
password / An encrypted password for the employee. / String / 20 / xQb&z@$IU
TYPE (typeID, description, typePrice, openSession)
Attribute / Description / Data Type / Length / ExampletypeID / Primary Key. A unique ID to identify each type of event. / Autonumber / Long Integer / 11
description / A description of the type of event. / String / 50 / Badminton
typePrice / Foreign Key. The price code for the event. / Integer / Long Integer / 2
openSession / Whether the booking is an open session. / Boolean / (1 bit) / FALSE
PRICE (priceCode, priceDescription, priceNAYCoffPeak, priceNAYCpeak, priceMembersOffPeak, priceMembersPeak, priceNonMembersOffPeak, priceNonMembersPeak, priceUnit)
priceCode / Primary Key. A unique ID to identify each price code. / Autonumber / Long Integer / 2
priceDescription / A description of the price code. / String / 50 / Archery and Fencing
priceNAYCoffPeak / The price for NAYC affiliates during an off-peak period. / Real (Currency) / Double / £43.00
priceNAYCpeak / The price for NAYC affiliates during an peak period. / Real (Currency) / Double / £43.00
priceMembersOffPeak / The price for members during an off-peak period. / Real (Currency) / Double / £43.00
priceMembersPeak / The price for members during a peak period. / Real (Currency) / Double / £43.00
priceNonMembersOffPeak / The price for non-members during an off-peak period. / Real (Currency) / Double / £52.00
priceNonMembersPeak / The price for members during a peak period. / Real (Currency) / Double / £52.00
priceUnit / The unit the price is calculated for. / String / 9 / per court
linkCourts (linkCourtsCourt, linkCourtsBooking)
Attribute / Description / Data Type / Length / ExamplelinkCourtsCourt / Foreign Key. The court ID number. / Integer / Long Integer / 9
linkCourtsBooking / Foreign Key. The booking ID number. / Integer / Long Integer / 46
linkTime (linkTimeTime, linkTimeBooking)
linkTimeTime / Foreign Key. The time slot ID number. / Integer / Long Integer / 14
linkTimeBooking / Foreign Key. The booking ID number. / Integer / Long Integer / 35
COURTS (courtID, description)
Attribute / Description / Data Type / Length / ExamplecourtID / Primary Key. A unique ID to identify each court. / Autonumber / Long Integer / 4
description / A description of the court. / String / 15 / Court 4
TIME (timeID, timeSlot, length)
Attribute / Description / Data Type / Length / ExampletimeID / Primary Key. A unique ID to identify each court. / Autonumber / Long Integer / 5
timeSlot / The start time of the time slot. / Date/Time / Short Time / 11:30
length / The length (in hours) of the time slot. / Real / Double / 0.5
SQUASH (squashBookingID, squashCustomer, squashDate, squashCourt, squashTimeSlot, squashDateBooked, squashStaff, squashType, status, paid, contactCustomer, squashNotes)
squashBookingID / Primary Key. A unique ID that is assigned to each individual squash booking. / Autonumber / Long Integer / 74
squashCustomer / Foreign Key. The customer ID of the customer who has booked the squash court. / Integer / Long Integer / 23
squashDate / The date the booking is planned for. / Date/Time / Short Date / 21/11/2010
squashCourt / The squash court that is booked. / String / 15 / Court 1
squashTimeSlot / The start time of the squash booking slot. / Date/Time / Short Time / 11:15
squashDateBooked / The date the booking was originally made. / Date/Time / Short Date / 18/11/2010
squashStaff / Foreign Key. The ID of the member of staff who made the booking. / Integer / Long Integer / 1001
squashType / Foreign Key. The ID of the type of booking. / Integer / Long Integer / 9
status / The current status of the booking. / String / 11 / Definite
paid / Whether the customer has paid for the hire of the court. / Boolean / (1 bit) / TRUE
contactCustomer / Whether the customer needs to be contacted to confirm the booking. / Boolean / (1 bit) / FALSE
squashNotes / Any additional notes about the booking. / Memo / (<63,999)
2.4 Validation and Error Messages
A number of fields specified in the datastructures in the previous question require the field to be validated to ensure the data entered is sensible, reasonable and complete. Below are all fields requiring validation, along with the validation rule and the error message to be displayed in the event that the system deems the data to be invalid. Default values are also defined.
BOOKING
Attribute / Validation Type / Validation Rule / Error MessagebookingCustomer / Lookup check. / Customer ID must exist in CUSTOMER table. / This customer does not exist! Their record may have been deleted or their data may not have been entered onto the system.
bookingDate / Presence check. / Data must be entered in field. / A date is required for the booking.
bookingDate / Range Check (with optionally user override) / Date must be no more than seven days in advance for members and three days in advance for non-members. The method must be able to be overridden for big events and parties. / For members:
This customer is a member! They cannot normally book more than seven days in advance. Do you wish to override this?
For non-members:
This customer is not a member! They cannot book more than three days in advance. Do you wish to override this?
bookingDate / Format Check / Must be in the format dd/mm/yyyy / Please enter a date in the format ‘dd/mm/yyyy’.
bookingType / Lookup Check / Type ID must exist in TYPE table. / This booking type does not exist in the list of stored types! Please select a valid booking type.
dateBooked / Default value / Equals the current date. / N/A
dateBooked / Format Check / Must be in the format dd/mm/yyyy / Please enter a date in the format ‘dd/mm/yyyy’
bookingStaff / Lookup Check / Staff ID must exist in STAFF table. / This member of staff does not exist in the staff database!
bookingStaff / Default Value / The staff ID of the member of staff who is currently logged onto the system. / N/A
status / Lookup Check / Must be selected from list of ‘Definite’, ‘Provisional’ or ‘Invoice’. / Please select one of the available options!
CUSTOMER
Attribute / Validation Type / Validation Rule / Error MessagecustomerSurname / Presence Check / Data must be stored in this field / A customer surname is required!
telephone / Format and Length Check / Must be between 5 and 11 characters in length. All characters must be numeric. No Spaces. Begins with a zero. / This phone number does not appear to conform to the expected format. Please enter a different number.
email / Format Check / Must have an ‘@’ symbol in and have at least one dot (.) after the ‘@’ symbol. / The email address you have entered does not appear to be valid. Please enter another.
TYPE
Attribute / Validation Type / Validation Rule / Error MessagetypePrice / Lookup check. / Must exist in PRICE table. / This price code does not exist in the price list!
STAFF
Attribute / Validation Type / Validation Rule / Error MessagestaffID / Presence Check / Data must be stored in this field. / A unique staff ID number is required!
staffForename / Presence Check / Data must be stored in this field. / A forename must be entered for this member of staff!
staffSurname / Presence Check / Data must be stored in this field. / A surname must be entered for this member of staff!
title / Lookup Check / Must be selected from a list of ‘Mr’, ‘Mrs’, ‘Miss’ or ‘Ms’. / Please select one of the available options.
password / Length Check / Must be at least 6 characters long. / Please choose a password that is at least six characters long!
linkCourts
Attribute / Validation Type / Validation Rule / Error MessagelinkCourtsCourt / Lookup check. / Must exist in COURTS table. / This court does not exist in the database!
linkCourtsCourt / Presence Check / Data must be entered in this field. / A related court is required!
linkCourtsBooking / Lookup check. / Must exist in BOOKINGS table. / This booking does not exist!
linkCourtsBooking / Presence Check / Data must be entered in this field. / A related booking is required!
TIME
Attribute / Validation Type / Validation Rule / Error MessagetimeSlot / Format Check / Must be in the format ‘hh:mm’ / Please enter a time in the format ‘hh:mm’ (24 hour clock).
linkTime
Attribute / Validation Type / Validation Rule / Error MessagelinkTimeTime / Lookup check. / Must exist in TIME table. / This time slot does not exist in the database!
linkTimeTime / Presence Check / Data must be entered in this field. / A related time slot is required!
linkTimeBooking / Lookup check. / Must exist in BOOKINGS table. / This booking does not exist!
linkTimeBooking / Presence Check / Data must be entered in this field. / A related booking is required!
SQUASH
Attribute / Validation Type / Validation Rule / Error MessagesquashCustomer / Lookup check. / Customer ID must exist in CUSTOMER table. / This customer does not exist! Their record may have been deleted or their data may not have been entered onto the system.
squashDate / Presence check. / Data must be entered in field. / A date is required for the booking.
squashDate / Range Check (with optionally user override) / Date must be no more than seven days in advance for members and three days in advance for non-members. The method must be able to be overridden. / For members:
This customer is a member! They cannot normally book more than seven days in advance. Do you wish to override this?
For non-members:
This customer is not a member! They cannot book more than three days in advance. Do you wish to override this?
squashDate / Format Check / Must be in the form dd/mm/yyyy / Please enter a date in the format ‘dd/mm/yyyy’
squashCourt / Lookup Check / Must be selected from a list: ‘Court 1’, ‘Court 2 (Glass)’, ‘Court 3’ / Please select a court from the list of available courts.
squashCourt / Presence Check / A court must be selected. / Please select a court!
squashTimeSlot / Lookup Check / Selected from a list of 45 minute interval time slots from 9:45am to 9:00pm / Please select a time slot form the list of available time slots.
squashTimeSlot / Presence Check / A time slot must be selected. / Please select a time slot for this booking.
squashTimeSlot / Format Check / Must be in the format hh:mm / Please enter a time in the format ‘hh:mm’ (24 hour clock).
squashType / Lookup Check / Type ID must exist in TYPE table. / This booking type does not exist in the list of stored types! Please select a valid booking type.
squashType / Default Value / Equals the type ID for squash. / N/A
squashDateBooked / Format Check / Must be in the format dd/mm/yyyy / Please enter a date in the format ‘dd/mm/yyyy’
squashDateBooked / Default value / Equals the current date. / N/A
squashStaff / Lookup Check / Staff ID must exist in STAFF table. / This member of staff does not exist in the staff database!
squashStaff / Default Value / The staff ID of the member of staff who is currently logged onto the system. / N/A
status / Lookup Check / Must be selected from list of ‘Definite’, ‘Provisional’ or ‘Invoice’. / Please select one of the available options!
Generic Error Messages
Any code used during the development of the system needs to have error traps to prevent unexpected errors from interfering with the system. A generic error message must be generated to display the error number and description so that the user can consult the documentation to see if the error is listed in the troubleshooting section, or in the event that the problem cannot be solved, a computer support technician could be contacted to try and resolve the problem. Details of the error can then be more easily transferred to the relevant parties to ensure they can resolve the problem as efficiently and effectively as possible.