To gain experience in designing a database using Entity Relationship Diagram (ERD), Normalisation, and Relational Database modelling techniques.

School of Information & Communication Technology
Trimester 1, 2021 Assignment Part 1:
Designing a Database for BigM
To gain experience in designing a database using Entity Relationship Diagram (ERD), Normalisation, and
Relational Database modelling techniques.
Learning Objectives
In the process of this assessment task you will:
• plan, schedule and execute project tasks with a view to improving your personal and group productivity;
• gain awareness of the typical challenges related to the design of practical databases;
• learn that database design is an iterative process; and
• use the ERD, Normalisation techniques, and Relational models to develop elegant logical models for a
Due date: Week 6, 23 Apr Fri 5:00 pm
One submission per group, file named as ‘a1- Student_ID .pdf’.
Any student’s ID in the group is fine.
Need Assignments Hel…
Each group member will have a short presentation or viva.
Late submission: Any submission after the due date will receive a deduction of 10% per day. Standard
university policy will apply for all late submissions. See the course website/profile for detail.
Marks: (a) Group submission: a total 100 marks and it is worth 7% out of the total assessment, plus (b)
individual viva/presentation: 3% out of the total assessment.
Extensions: An extension will only be considered with supporting documentation from a health professional
and if the problem/illness occurred within the week prior to the due date. If an extension is granted the
extension will then equal the number of days specified on the doctor’s certificate, with a maximum limit of
five (5) working days.
Authorship: This assignment is a Group assignment and it shall be completed by the students in each group
only. The final submission must be identifiably the work of the individual group members. Breaches of this
requirement will result in an assignment not being accepted for assessment and may result in the offending
student or students being required to present before the Disciplinary Committee.
1804ICT & 7003ICT Page 1 Designing a Database for BigM
The proprietors of BigM have approached you and asked if you could design a database to help them manage
their business. The management has commissioned you (in your capacity as a Database Management System
consultant) to analyse, design and develop an appropriate conceptual data model and relational database
schema, based on the following information gathered about the current business activities.
Business Rules (for both 2814ict and 7003ICT students):
• BigM operates stores in many cities in Australia. Stores are referenced by store number. BigM also keeps
store name, phone, fax, email for each store.
• Each store has several departments, for example, finance, accounts, sales, customer service etc. A
department can appear in many stores. For each department, there is a department ID, department name,
phone, and e-mail address.
• Each department has several employees. For each employee, BigM keeps a record of their employee ID,
first name, last name, phone, date of birth, start date of his/her work, hourly rate, annual salary, and tax file
• Each store is managed by an employee as a store manager and each department is supervised by an
employee. The department supervisor is also the supervisor for all the staffs within that department.
• Each store may be assigned a supervising store where all training, payroll, server application and help desk
are located.
• BigM sells different products like CDs, cloths, computers, etc. For each product it stores product number,
product description, product size and price.
• Customer may place orders in the store. Customer details are always taken at each order. A customer is
referenced by a customer number, customer first & last names, and phone number, if available.
• A customer may order more than one product at a time, and they may order multiple copies of the same
product. BigM also records the date a product arrives and the date when it is picked up by the customer. Note
that these dates may be different for each product.
Additional business rules for 7003ICT students only:
• Each supervising store generates pay slips for all staffs (in this store and other stores being supervised) on a
fortnightly basis. For each pay slip, the store records a pay ID, supervising store ID, employee ID, pay date,
number of hours and the gross payment.
• An inventory of the number of each product in each store is kept. BigM keeps track of the quantity of each
product that is on order, as well as the number currently available in each store.
BigM understands that they may not have provided you with sufficient information. If you need to make
assumptions about their organisation, please ensure that you record them.
2814ICT & 7003ICT Page 2 of 3
Assessable Tasks
a) Group Submission
From the BigM business requirements specified above, prepare a document according to the followings:
1. Use the supplied template for your Assignment submission.
2. An appropriate title page that includes the signatures of all students in the group and an acknowledgement
of all students and staff you have spoken to about the assignment.
Need Assignments Hel…
3. A table of contents and page numbers.
4. A logical ERD using the Crow’s Foot notation only. The diagram should include:
a) all entities, attributes, and relationships (including names);
b) primary keys (underlined) and foreign keys (italic) identified;
c) cardinality and modality symbols; and
d) assumptions you have made, e.g., how you arrived at the cardinality and/or participation for those not
mentioned or clear in the business description, etc.
5. Normalisation of the relations which identifies:
a) covert each table to a relation schema;
b) dependency diagram or functional dependencies for each relation;
c) the level of Normalisation achieved for each relation; and
d) the reasons for any relation that is NOT maintained in 3NF.
6. A bibliography, containing all resources used to complete the assignment. If no resources have been used
please indicate this appropriately.
b) Individual Presentation / Viva
In the week of the submission due date, students in each group will be required to attend the workshop/lab inperson/online. The tutor will assess each student via a brief presentation / viva.
Assessment Criteria and Marking Overview
Task a: The Group Submission Marks
How clear and well-presented your submission is.
Adherence to the standard of the course, assumptions made, inclusion of correct primary and foreign keys,
appropriate entities, relationships, and attributes. 60
Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you
consider optimal. 30
Total 100
Out of 7% of the total assessment 7
Task b: Individual Presentation / Viva Marks
Correct answers to questions related to the assignment / submission