Assignment 1 Overview

For this assignment you must create an ER diagram, and a relational schema for a small business. The entities and relationships must be in third normal form. Timelines and Expectations

Percentage Value of Task: 20% Due: 5pm Mon, May 2, 2016 - 17:00 (Week 7)

Minimum time expectation: 12 hours Learning Outcomes Assessed

The following course learning outcomes are assessed by completing this assessment:

 K4. design a relational database for a provided scenario utilising tools and techniques  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 Details

You have been commissioned to create a database for BM Holdings. BM Holdings has two types of inventory, books and DVDs. They would like a database to manage their inventory, sales and

suppliers and customers. Some of the reports that BM Holdings would like to run from your database design include a list of

customers by suburb and a list of inventory by genre or other category. They would also like to apply discounts to items by inventory type (ie book or DVD) by genre or other category and apply discounts

to VIP customers and to bulk orders.

The MINIMUM entities you are expected to have are listed below. No normalisation has been undertaken on these entities, so they may be many to many relationships that are not resolved. Your submission is not expected to have many to many relationships left unresolved. You may add entities or attributes as you see fit.

including ER diagrams, relation models and normalisation Each inventory record will have a unique inventory ID, a title, a type (book or DVD), wholesale

price, number in stock, a supplier and a genre (for example Comedy, Drama, Sci-Fi, fantasy, Crime, etc)

Books will have a year of publication, one or more authors, a publisher, type (hardcover or

paperback), a markup, and discount.

DVDs will have a year released, one or more actors, a producer, a classification (G, PG, MA, etc), a critics rating of 0 to 5 stars, markup, and discount. Authors will have their name, place of birth, date of birth and (if applicable) their date of death

Actors will have their name, place of birth, date of birth and (if applicable) their date of death Publishers will have their name, address, contact, phone number and email address. CRICOS Provider No. 00103D Assignment Template.docx Page 1 of 3

Producers will have their name, place of birth, date of birth and (if applicable) their date of death

Suppliers will have a name, contact, phone number, fax number email address and address Customers will have a name, address, phone number, email address and discount

Submission Your submission should include:

 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 including the normal form that each entity is in and why that is optimal. Also a discussion of how normalisation was achieved for that entity.

 A list of relationships with all table names, attributes, primary and foreign keys indicated as per the conventions given in the lecture slides (ie entity/table names in capitals, attributes as

proper nouns, primary key underlined and foreign keys in italics.)  A database schema indicating the type and purpose of all attributes.

The assignment is to be submitted via the Assignment 1 submission box in Moodle. This is to be

found in the Assessments section of the course Moodle shell.