OverviewThe purpose of this task is to develop student’s skills in designing and implementing a relationaldatabase for a given case study.Timelines and ExpectationsPercentage Value of Task: 20% (75 marks)Due: Week 7 – Friday, May 3, 2019 at 4pm (16:00)Minimum time expectation: Preparation for this task will take approximately 20 hoursLearning Outcomes AssessedThe following course learning outcomes are assessed by completing this assessment:K4. Design a relational database for a provided scenario utilising tools and techniques includingER diagrams, relation models and normalisation.K5. Describe relational algebra and its relationship to Structured Query Language (SQL).A1. Design and implement a relational database using a database management system.Assessment DetailsBackgroundYou have been commissioned by Julie from ABC Event Organisers to design a database to assist them with managingtheir booking, customer and supplier information. At this stage you have only been commissioned to provide the designof the database.ABC would like to store information regarding their customers. Customers may either be individual or corporatecustomers. 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 corporatecustomers they would like to store the business name, a contact name, address details (including their location, postaland delivery address details), email address, website URL, and phone numbers (mobile, work).ABC has a number of different suppliers who provide them with the products that they require to organise events. Foreach 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 furniture such as chairs,tables, or other items such as table clothes, cutlery and crockery. Suppliers may only provide one product or manydifferent products. ABC would like to be able to search their database to obtain a list of suppliers who supply particularproducts, however, they do not need to know how many of each particular product the supplier stocks.In the database ABC would also like to store information about their staff. Staff may be employed full-time, part-time oron a casual basis. ABC need to store contact information for the staff (address and phone), along with their TFN. TheyITECH 2004 DATA MODELLINGCRICOS Provider No. 00103D ITECH 2004 Assignment 1 Sem2 2019 Page 2 of 3would also like to know what certifications the employees have. For example: “Responsible Serving of Alcohol”, “FoodHandling”, or any relevant police checks such as “Working with Children”. Each employee may hold a number ofdifferent skills and certifications. Some staff are supervisors of other staff members and this also needs to be stored.For each event ABC would like to store details such as where the event is to be held, the customer who they areorganising the event for, the date and time of the event, the type of event and how many people will be at the event.ABC specialises in organising weddings, christenings, birthday parties and work functions. For each event they need tostore information about the supplies that they require (eg tables, chairs, cutlery), the supplier that they will be using forthe different products (they can use more than one), and how many of each product they require. ABC would also liketo store details of the staff that have been assigned to each event. Each event will have one staff member who takeson the role of event manager.RequirementsThis assignment should be presented in a report format, including the following items:• An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships,cardinality and participation indicated. All many to many relationships should be resolved.• A discussion of normalisation, the normal form that each entity is in, how normalisation wasachieved, and why that is optimal. All entities to be in 3NF, unless there is strong justificationfor leaving in 2NF.• A list of relationships with all table names, attributes, primary and foreign keys indicated asper the conventions given in the lecture slides (i.e. entity/table names in capitals, attributes asproper nouns, primary key underlined and foreign keys in italics).• A database schema indicating the type and purpose of all attributes.
