FFC Database

Project Specification

You have been commissioned by Barry from the Fantastic Fireworks Company (FFC), a business that organises fireworks displays for individuals and organisations. He has asked you to design a database to assist FFC with managing their booking, customer and supplier information. You are only required to provide the design of the database at this time.

FFC would like to store information regarding their customers. Customers may either be individuals or organisations. For individual customers they would like to store the contact name, address details (including their location, postal and delivery address details), email address and phone numbers (mobile, home, work). For their organisational customers they would like to store the organisation name, organisation type (e.g. corporation, government, club), a contact name, address details (including their location, postal and delivery address details), email address, website URL, and phone numbers (mobile, work). The location address and work phone number are always recorded for a customer. Postal and delivery addresses and mobile and home phone numbers are sometimes not available so these are optionally recorded.

FFC has a number of different suppliers who provide them with the products that enables them to conduct displays. For each supplier they would like to store their business name, contact name, address details (including their location, postal and delivery address details), and phone numbers (mobile, work).

Each supplier may provide a number of different products. For example a supplier may provide ready made fireworks such as rainbow skyrockets, bangers and thrillers or other items such as dispensing platforms or pontoons. They may also provide other equipment such as sound, lighting or electrical equipment. Suppliers may only provide one product or many different products. FFC would like to be able to search their database to obtain a list of suppliers who supply particular products, however, they do not need to know how many of each particular product the supplier stocks.

In the database FFC would also like to store information about their staff. Staff may be employed full‐time, part‐time or on a casual basis. FFC need to store contact information for the staff (address and phone), along with their TFN. They would also like to know what certifications the employees have (e.g. “Handling Explosives”, “Detonations”, “First Aid”). Each employee may hold a number of different skills/certifications. Some staff are supervisors of other staff members and this also needs to be stored.

For each display, FFC would like to store details such as where the display is to be held, the customer who they are organising the display for, the date and time of the display, the type of display and how many people are expected to attend. FFC specialises in organising displays for between 100 and 10,000 people. They can range from birthdays through to civic events. For each display they need to store information about the supplies that they require (eg types of fireworks, other equipment), the supplier that they will be using for the different products (they can use more than one), and how many of each product they require. FFC would also like to store details of the staff that have been assigned to each display. Each display will have one staff member who takes on the role of display manager but more than one staff member is often used.

Since it is a potentially dangerous activity, FFC needs to also know the insurance cost of each display. The insurance cost is levied in categories based on a risk assessment by the insurance underwriter. Assume there is a separate table for the categories and corresponding cost and the insurance underwriter advises the category of insurance required.

FFC understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record these.

Assignment Submission

You should produce a single pdf file, created using Word, containing all of the requirements as specified within this document. Your lecturer will provide details of how the file is to be submitted within lectures.

The design document should contain:

• A completed copy of the SITE Assignment Coversheet.
• An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.
• A table of contents and automatically generated page numbers.
• An entity relation diagram that models the problem which includes:
  • all entities, relationships (including names) and attributes;
  • primary (underlined) and foreign (italic) keys identified;
  • cardinality and participation (optional / mandatory) symbols; and
  • assumptions you have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the business description, etc.
  • The E‐R should be completed using the standards of this course (crow’s feet).
• Relational data structures that translates your E‐R diagram which includes:
  • relation names;
  • attribute names;
  • primary and foreign keys identified; and
  • for each relation the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
  • The data structures should be shown using the standards of this course.
• A relational database schema that translates your relational data structures which includes:
  • table names,
  • column names and field types
  • primary and foreign keys identified
• A bibliography, in APA format, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.