ITECH 1006 / 5006
Database Management Systems School of Science, Information Technology and Engineering
Assignment 1 – Development of an ER Diagram and Database Implementation
Course Objectives:
This assessment task focuses on the following course objective: • use entity-relationship diagrams to design a relational database.
Due Date:
Thursday of Week 6 at 17:00 (see Course Description for further dates and times).
Project Specification
You have been commissioned by Julie from ABC Event Organisers to design a database to assist them with managing their booking, customer and supplier information. You are only required to provide the design of the database at this time. ABC would like to store information regarding their customers. Customers may either be individual or corporate customers. 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 corporate customers they would like to store the business name, a contact name, address details (including their location, postal and 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. 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 furniture such as chairs, tables, or other items such as table clothes, cutlery and crockery. Suppliers may only provide one product or many different products. ABC 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 ABC would also like to store information about their staff. Staff may be employed full-time, part-time or on a casual basis. ABC 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. For example: “Responsible Serving of Alcohol”, “Food Handling”, or any relevant police checks such as “Working with Children”. Each employee may hold a number of different 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 are organising 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
CRICOS Provider No. 00103D
Assignment 1 Specification
Page 1 of 4
ITECH 1006 / 5006
Database Management Systems School of Science, Information Technology and Engineering functions. For each event they need to store information about the supplies that they require (eg tables, chairs, sets of cutlery), 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. ABC would also like to store details of the staff that have been assigned to each event. Each event will have one staff member who takes on the role of event manager. ABC 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: 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 diagram that models the problem which includes: a. all entities, relationships (including names) and attributes; b. primary (underlined) and foreign (italic) keys identified; c. cardinality and participation (optional / mandatory) symbols; and d. 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). 5. Relational data structures that translates your E-R diagram which includes: a. b. c. d. 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. e. The data structures should be shown using the standards of this course.
6. A relational database schema that translates your relational data structures which includes: a. table names, b. column names and field types c. primary and foreign keys identified 7. A bibliography, in APA format, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately. Your assignment should be completed according to the University of Ballarat General Guide for the Presentation of Academic Work. This is available online at the following web address: http://www.ballarat.edu.au/generalguide
CRICOS Provider No. 00103D
Assignment 1 Specification
Page 2 of 4
ITECH 1006 / 5006
Database Management Systems School of Science, Information Technology and Engineering
Example of submission Example: A student can borrow many books from library and a book can be borrowed by many students. 1. An entity relation diagram that models the problem which includes: STUDENT StudentID FirstName Surname StreetName Suburb Postcode BOOK BookID Title involves Author Publisher Edition Involved in ISBN Price YearPublished
BOOKLOAN StudentID borrows BookID LoanDate Due borrowed by
2. Relational data structures that translates your ER diagram STUDENT(StudentID, FirstName, Surname, StreetName, Suburb, Postcode) BOOK(BookID, Title, Author, Publisher, Edition, ISBN, Price, YearPublished) BOOKLOAN(StudentID, BookID, LoanDate, Due) Notes: STUDENT is in 2NF. There is a transitive functional dependency among StudentId, Suburb and PostCode. Suburb doesn’t introduce big? redundancy (only one attribute and there is no potential redundancy), there is no need to decompose this table into two. The relational data structure of BOOK and BOOKLOAN is in 3NF. Assumptions: • A student can borrow many books but some students may not borrow any book • A book can be borrowed by many readers but may never be borrowed 3. A relational database schema Table Name Student BookLoan Book Field StudentID FirstName Surname StudentID BookID LoanDate BookID Title Author Publisher Edition ISBN Price YearPublished Type Text(9) text(30) text(30) Text(9) Int Date Number Text(30) Text(30) Text(30) Text(4) Text Double Date(year) Description Primary key Primary key; foreign key reference to Student.StudentID Primary key; foreign key reference to Book.BookID Primary key; Format: dd/mm/yyyy Primary key
CRICOS Provider No. 00103D
Assignment 1 Specification
Page 3 of 4
ITECH 1006 / 5006
Database Management Systems School of Science, Information Technology and Engineering
Assessment Criteria
1. How clear and well presented your submission is. 2. E-R diagram: adherence to our standard, assumptions made, inclusion of correct primary and foreign keys, appropriate entities, relationships, and attributes. 3. Normalisation: appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 4. Conversion of E-R diagram to relational schema: schema is a correct translation of the E-R submitted with appropriate tables, columns, and primary keys.
Assignment 1 – Marking Overview
ER Diagram Relational Structure Relational Schema Documentation Total End of the document / 15 /5 /5 /5 / 30 (worth 20% overall)
CRICOS Provider No. 00103D
Assignment 1 Specification
Page 4 of 4 