Reference no: EM133133533

ICT105 - Data Modelling and Database Systems

Assessment - Database Design

Overview

The purpose of this assignment is to develop the student's skills in designing a relational database for a given case study.

Learning Outcome 1: Understand the fundamental concepts of relational databases, describe the different models of database management system.

Learning Outcome 2: Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization.

Learning Outcome 3: Interpret entity relationship diagrams to implement database.

Background
You have been appointed to create database for Toy community store. Toy community is an online store and has two types of inventories, toys and books. They would like database to manage their inventory, manufacturers, wholesalers, customer, and sales.
Some of the reports that Toy community store would like to run from your database design include a list of customers by city and a list of inventories by price or another category. They would also like to apply discounts to items by inventory type and apply discounts to VIP customers and to bulk orders. No normalisation has been undertaken on these entities, so there may be many to many relationships that are not resolved. Your submission should have all many to many relationships resolved. You may add entities or attributes as you see fit.

The minimum entities you are expected to have been listed below:

• Each inventory record will have a unique inventory ID, a title, a type (toys or books), retail price, number in stock, year, item code
• Suppliers will have their supplier ID, name, address, city, state, contact, product, TypeofGoods, paymentmethods
• Customers will have their customer ID, Name, Address, city, phone, email, Password, creditcard, billingaddress, shippingaddress, dateentered
• Wholesalers will have their name, address, contact, phone number, fax number, email address
• Orders will have orderID, customerID, paymentDate, orderDate, ShipDate, ShipperID

Requirements
Assignment should be presented in a report format including the following sections
1. Design your ER Diagram with all the entity names, attribute names, primary and foreign keys, relationships, cardinality, and participation indicated. You can add entities or attributes as you see fit. All many to many relationships must be resolved.

2. A discussion on normalization, the normal form that each entity is in, how normalization was achieved and why that is optimal.

3. A list of relationships with all table names, attributes, primary and foreign keys indicated as per the conventions given in the webinar slides (i.e., entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics).

4. A database schema indicating the type and purpose of all attributes.
A suggested structure for the report is given below: Titlepage
Table of Contents
Chapter 1. Introduction Chapter 2. ER Diagram Chapter 3. Relational Schema Chapter 4. Normalization
Chapter 5. Conclusion References

Reference
You need to provide reference if necessary, using APA format. Must include minimum 5 references.

Attachment:- Data Modelling and Database Systems.rar