Designing a Database for Commonwealth Transport Services

1804ICT – DATA MANAGEMENT
7003ICT – DATABASE DESIGN
School of Information & Communication
Technology
Trimester 2, 2018
Assignment Part 1:
Designing a Database for
Commonwealth Transport
Services
ASSIGNMENT TITLE: _________________________________________________________
Student ID _________________Student Name ____________________________________________
Course Code ______________Course Name ____________________________________________
Date Submitted ____________Lecturer’s Name __________________________________________
Tutor’s Name ________________________________________________________________
Marks obtained: ________. [For marker to fill up.]
PLAGIARISM
Plagiarism: occurs when the work of another is represented, intentionally or unintentionally, as
one's own original work, without appropriate acknowledgement of the author or the source. See
more at https://www.griffith.edu.au/academic-integrity/information-for-students/what-is-plagiarism.
Plagiarism is a serious offence. Refer to the following document on Student Academic Misconduct:
http://policies.griffith.edu.au/pdf/Student%20Academic%20Misconduct%20Policy.pdf.
Declaration
Except where appropriately acknowledged, this assignment is my own work, has been
expressed in my own words and has not previously been submitted for assessment. I have also
retained a copy of this assessment piece for my own records.
Signature:_________________________________________
Date: __________________________________________
1804ICT & 7003ICT - Assignment Part 1 Page 1

Note: If you are submitting the assessment online at L@G, you do not need to sign and scan
this page. Your online submission will be considered as your signature above.
Table of Contents
Acknowledgements:...............................................................................................................2
Entity Relationship Diagram..................................................................................................3
Assumptions...........................................................................................................................4
Normalisation.........................................................................................................................5
Relational Database Schema..................................................................................................8
Bibliography.........................................................................................................................11
Acknowledgements:
[List names of staff and students you have discussed with about this assessment.]
1)
2)
3)
1804ICT & 7003ICT - Assignment Part 1 Page 2 of 11

Entity Relationship Diagram
1804ICT & 7003ICT - Assignment Part 1 Page 3 of 11

Assumptions
While developing the databases schema for the organization following assumptions are made in order to
make the proposed database consistent one. Following are list of the assumptions;
 Each of the productID, storeID, employeeID, inventoryID, deprtmentID will be unique for every
new entry as they will be used as the primary as well as foreign keys among the tables.
 A customer can be related to multiple number of stores as they can buy products from different
stores situated at different locations.
 Inventories will have a minimum reorder level for different products it stores. This is important in
order to maintain the availability of the products in the stores.
 The pickup date of the products by the customers should not be ago than the arrival date of the
product at the store.
 Each employee can work under a certain department of a specific store.
 For a specific store there will be only one store manager and similarly there will be only one
supervisor for the specific department at a store.
 The payslips for the employees in different stores will be generated fortnightly. Hence time
between two payslips of an employee.
 The store manager should be an employee of the store.
 The order for the product cannot be placed which is not available in the inventory or under the
reorder level.
 As any customer can order more than one product or multiple number of same products thus the
order table may contain multiple similar productsIDs related to a specific order and specific
cutomerID.
1804ICT & 7003ICT - Assignment Part 1 Page 4 of 11