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;
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)