Designing a Database for Commonwealth Transport Services - Assignment

This document presents a comprehensive solution for a database design assignment, specifically tailored for the 1804ICT and 7003ICT courses focusing on data management and database design. The assignment involves designing a database for Commonwealth Transport Services, encompassing the creation of an Entity Relationship Diagram (ERD) to visually represent the database structure, detailing assumptions made during the design process to ensure consistency, and applying normalization techniques to optimize the database's structure. The solution includes the development of a relational database schema, outlining the tables, attributes, and relationships necessary to efficiently manage and store data. The document also provides acknowledgements and a bibliography, demonstrating a thorough approach to the assignment requirements.

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

Normalisation
In order to normalize the tables determined for the database of the BigM stores the following instructions
needs to considered
In order to make a table to 1 NF
For 2NF
The tables of the data base must be in the first normal form
All of the non-key attributes in the tables must be fully functionally dependent on primary key of the table.
And for 3NF
It is important that every non-prime attribute in the tables are non-transitively dependent on the key of the
table.
For the store table
The table consisted of the of the following
1NF
storeid storername phonenumber fax Email city street_addres
s
state Postcode
2NF
3NF
1804ICT & 7003ICT - Assignment Part 1 Page 5 of 11

For the Customer table
customernumber firstname lastname customeraddress phonenumber Postaladdress
2NF
For the order table
1NF
OrderID Customernumber Productid Storeid OrderDate Pickupdate
2NF
1804ICT & 7003ICT - Assignment Part 1 Page 6 of 11

For the Product table
1NF
productnumbe
r
producttyp
e
productdescription productbra
nd
productsize productprice Productcategory
2NF
For the employee table
employeeid firstname lastname Storeid EmployeeAddress
Inventory table
inventoryid productnumber last_update Storeid
1804ICT & 7003ICT - Assignment Part 1 Page 7 of 11

Relational Database Schema
Store table
storeid Integer Primary key
storername Varchar (15)
phonenumber Integer
fax Integer
Email Varchar (50)
city Varchar (20)
street_address Varchar (25)
state Varchar (15)
Postcode Varchar (10)
Inventory table
inventoryid Integer Primary key
productnumber Varchar (15)
last_update Date
Storeid Integer
Employee Table
employeeid Integer Primary key
firstname Varchar (15)
lastname Varchar (15)
EmployeeAddress Varchar (100)
StoreID Integer Foreign key Reference table Store
table
1804ICT & 7003ICT - Assignment Part 1 Page 8 of 11

Product table
productnumber Integer Primary key
producttype Varchar (15)
productdescription Varchar (70)
productbrand Varchar (25)
productsize Integer
productprice Integer
productcategory Varchar (15)
Order table
OrderID Integer Primary key
Customernumber Integer Foreign key Customer table
Productid Integer
Storeid Integer
OrderDate Date
Pickupdate Date
Customer table
customernumber Integer
firstname Varchar (25)
lastname Varchar (25)
State Varchar (15)
City Varchar (10)
street_address Varchar (50)
Postcode Varchar (25)
1804ICT & 7003ICT - Assignment Part 1 Page 9 of 11

Department table
DepartmentID Integer
Departmentname Text
Storeid Integer
ManagerID Integer Foreign key Reference EmployeeID
(employee table )
1804ICT & 7003ICT - Assignment Part 1 Page 10 of 11

Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, &
management. Cengage Learning.
Davie, B., Koponen, T., Pettit, J., Pfaff, B., Casado, M., Gude, N., Padmanabhan, A., Petty,
T., Duda, K. and Chanda, A., 2017. A database approach to sdn control plane design. ACM
SIGCOMM Computer Communication Review, 47(1), pp.15-26.
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Harrington, J.L., 2016. Relational database design and implementation. Morgan
Kaufmann.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases
and data warehouses. Prospect Press.
Köhler, H. and Link, S., 2018. SQL schema design: foundations, normal forms, and
normalization. Information Systems, 76, pp.88-113.
1804ICT & 7003ICT - Assignment Part 1 Page 11 of 11