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.

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 including ER 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 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.

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. 

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.