Aims

To gain experience in designing a database using Entity-Relationship (E-R) Diagram, Normalization, and Relational Database modelling techniques.

Learning Objectives

In the process of this assessment task you will:

• plan, schedule and execute project tasks with a view to improving your personal productivity;

• gain awareness of the typical challenges related to the design of practical databases;

• learn that database design is an iterative process; and

• use the E-R Diagrams, Normalization techniques, and Relational models to develop elegant conceptual and logical models for a database;

Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual.

Assignment

Designing a Database for Discerning Event Organisers (DEO)

Introduction

Discerning Event Organisers (DEO) is a private company, specializing in organizing catering services for a range of clients including individuals, businesses, schools and government departments. Examples of functions for which they organise catering include conferences, weddings, christenings, birthday parties and other milestone events e.g. end of school year functions. Natural growth in outsourcing of these services along with DEO's motto, "We aim to appease", has allowed DEO to grow into a busy successful company with a reputation for being flexible and listening to clients' wishes. DEO currently uses a hybrid electronic (using spreadsheets) and paper based system for managing their catering business but has realised for some time that it needs a modern computerized system for efficient and reliable management and documentation of its services.

DEO has hired you as a database professional to design and develop a database system which will meet DEO's needs as specified below. At this stage you have been commissioned to go through the process of conceptual and logical design of the database and include a component of the physical design - relational data structures conforming to the MySQL standards. At a later stage (the second assignment) you will be asked by DEO to create the database, populate it with valid data and run some queries to demonstrate its suitability for purpose.

Business Requirements

DEO would like to store information regarding their customers. Customers may either be individual, business, school or government customers. For all customers they would like to store the customer id, name, address details (including their location, postal and delivery address details), customer email address, customer phone number, contact name, contact phone number and contact email address. For each type of address they need to provide separate fields for street details, city, state and postcode. For business customers they would like to store the website URL of the customer, the sector(s) in which the business operates and the legal structure of that business e.g. private company, public company, trust, partnership. For school customers they would like to record what level the school is at e.g. pre-primary, primary, secondary, trade and what type the school is e.g. public, independent or religious. For government customers they would like to be able to store the level of government at which the government customer operates. For all these classifications of business, school and government customers, they should be stored in separate lookup entities that can be added to when required and thus allow the entry of a code against the customer designating the appropriate classification.

DEO has a number of different suppliers who provide them with the products that they require to conduct events. For each supplier they would like to have a record of the supplier's unique id, business name, website URL, supplier email address, supplier phone number, supplier contact name, supplier contact phone number, supplier contact email address and supplier address details (including their location, postal and pickup address details). For each type of address they need to provide separate fields for street details, city, state and postcode.

Each supplier may provide one or more of three types of product -food, alcohol or equipment - and one or more of any product within that type. For example a supplier supplying food may provide cooked meals, raw food such as fruit or pre-packed food e.g. chips, pretzels. A supplier supplying alcohol may supply any form of beer, wine or spirit in various forms e.g. keg, carton, bottles. Examples of equipment include furniture such as chairs, tables, or other items such as tablecloths, cutlery and crockery. At this stage, DEO would like the ability to add to the three product types in the future e.g. entertainment but they do not require any further classification within those types. Each product has a code that uniquely identifies it, a description and a type - food, alcohol or equipment. For each combination of supplier and product there is a record of the price (the expected charge to the customer) and the cost (what DEO pays for it).

DEO would like to be able to search their database to obtain a list of suppliers who supply particular products and make up orders of items and quantities for their events. The order itself will have a unique identifier with line items denoting the supplier product item being supplied, the quantity required and the price per unit. The price is usually the supplier product price but there is some discretion about what is actually charged to the customer. The order is a working document until the event is held; changes in what is required for the event and their prices can be made up until the day before the event but once that point is reached, all supplies on the order are charged to the client at the price on the order (multiplied by the quantity). For the sake of simplicity, DEO will worry about keeping the supplier and product records up to date, they will not need to know (where applicable) how many of each particular product is available, they will not need to keep an historical record of price and cost and the order is expected to be filled without complications.

For each event, DEO would like to store details such as the customer who they are organising the event for, the scheduled date and time of the event, where the event will be held (location name, street details, city, state and postcode), type of event (e.g. conference, wedding), how many people will be at the event, the expected event duration and the actual event duration. For each event they need to store information about the supplies (products) that they require and these requirements have been discussed in the last paragraph. DEO would also like to identify those staff members that have been assigned to each event and record the hours each staff member actually worked on each event. Each event will also have one staff member who takes on the role of event manager.

DEO would therefore also like to store information about their staff. Staff may be employed fulltime, part-time or on a casual basis. DEO would like some flexibility in designating new codes or altering the description of these employment types. DEO need to store contact information for the staff (name, contact phone, contact email), along with their Tax File Number (TFN) and pay rate per hour. They would also like to know what certifications the employees have. Example types include: "Responsible Serving of Alcohol", "Food Handling" and "Working with Children". DEO want to be able to record these classifications and add new types. For each certificate for each employee they want to store a unique code, identify its type, identify who awarded the certificate, the date it was granted and its expiry date. Each employee may hold none one or many certifications. Some staff may be supervisors of other staff members and may in turned be supervised by another staff member. Some staff members are neither supervised or supervise any others. This information also needs to be stored.

DEO 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.

Assessable Tasks

From the DEO business requirements specified above, prepare a document according to the following:

1. A completed copy of the SITE Assignment Coversheet.

2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.

3. A table of contents and automatically generated page numbers.

4. An entity relation (E-R) diagram using Crow's Foot notation. The diagram should include:

a) all entities, attributes, and relationships (including names and strength) ;

b) primary keys (underlined) and foreign keys (italic) identified;

c) cardinality and participation (optional / mandatory) symbols; and assumptions you have made, e.g., how you arrived at the cardinality and/or participation for those not mentioned or clear in the business description, etc.

5. Normalization status of the relations which identifies:

a) dependency diagram for each relation

b) the level of Normalization achieved for each relation

c) the reasons for any relation that is maintained NOT in 3NF.

6. Relational data structures that translate your E-R diagram which includes:

a) relation (table) names,

b) attribute (column ) names and field types (as required by WAMP),

c) primary and foreign keys identified;

7. A bibliography containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.