Design and develop conceptual data model and physical database

Background Information for Sample Database Project: Australian Manufacturing Company (AMC) requires a database system to manage their stores, employees, products, suppliers, patrons and orders. The following information has been gathered about current business activities of AMC.

1. AMC has many stores in different cities across Australia. Each store has unique store number and other details like name, contact details (phone number, email address and fax number) and location details (postal address composed of street number, suburb, state and postcode).

6. AMC sales different products like fashion, health, beauty, electronics etc. Each product is characterised by product number, name, brand, description and price.

7. Each product can be supplied by one or more supplier and each supplier can supply one or many products. AMC also keeps appropriate details of their suppliers.

Project Specification:

1. Part A: Entity Relationship Diagram (20 marks)

iv. Include participation (optional / mandatory) symbols if applicable. The ERD should be created as part of a Microsoft Word document. Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Microsoft Visio or try www.gliffy.com (Search for ERD gliffy to get started).

2. Part B: Normalisation (5 marks)

(1) Create tables according to your relational schema created in step 1. (5 Marks)

a. you are required to develop a demonstration prototype system (database) that stores data for AMC stores, employees, products, suppliers, patrons and orders. Create a new database called AMCDB.sql.

With the help of database system, Australian Manufacturing Company (AMC) manage their data with specific entity and attributes. In this relational database system, Department manages the Employee and store who are the employee of AMC. The store supervises the Employee with their respective details and the employee works under the store. When Employee work under the store, it determine the employee is full time employee or casual employee. Here, employees can payslip with payslip number. When an employee is working in a store then each product have their product number that contain an Order line which initially has order number that places a order of the customer with their respective details.

One to one relation-

• Employee to Department

• Department to Store

• Store to Product