To give you practical experience in database modelling, normalization and writing SQL…

Aim:

To give you practical experience in database modelling, normalization and writing SQL

statements to query a relational database

Assignment Summary:

The project specification details provided in this document and you must use only given

specifications to come up with your solution. Make appropriate assumptions where ever required.

Please make sure that your group must meet your lecturer/tutor at least twice to update the

progress of your assignment.

Students require to submit 5 minutes individual video presentation, incuding slides to

Moodle.

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.

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

There are many departments within each store, for example, HR, accounts, finance and

sales etc. each department is uniquely identified by department number. AMC also

records department title, phone number and email address.

Each store has a number of employees. Each department is supervised by a department

supervisor and each store is managed by a store manager who both are employees of

AMC. Each department has a single supervisor only i.e. the department supervisor is

also the supervisor for all the employees work within that department.

Each employee is referenced by unique employee number, name (first and last names),

address, mobile number, email, TFN, salary and joining date. An employee s salary is

recorded differently as per his or her employment type. Employees can be employed as

fulltime or casual basis. For a fulltime staff, annual salary is recorded and hourly rate for

a casual staff.

Each store creates payslips for all employees work in that store on a weekly basis. As a

minimum payslip number, no of hours worked, employee detail, store detail and gross

pay are recorded.

AMC sales different products like fashion, health, beauty, electronics etc. Each product

is characterised by product number, name, brand, description and price.

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.

Each store maintains an inventory of their products available for sale. The quantity of

each product available in store and the quantity ordered are kept.

AMC patron visits the store and shops in the store. Each customer is characterised by

customer number, name (first and last names), mobile number and address.

A patron may place orders. An order contains one or many products at a time. A store

keeps track of order number, order date, product(s) ordered and quantity ordered for

each product.

Attachments: