ICT211 Database Design

ICT211
Database Design
Task 2
Semester 1, 2020
ATMC ICT211 Database Design Task 2
Page 2 of 12
Assessment and Submission Details
Marks: 30 % of the Total Assessment for the Course
Due Date: Week 12, Wednesday 11:59 pm AEST
Assignments are to be submitted by SafeAssign.
DO NOT SUBMIT THE ASSIGNMENT TO THE COURSE CO-ORDINATOR OR TUTOR.
Submit your assignment to the link under Assessment -> Task 2 Submission on Blackboard.
The submission link will be open a week before the due date. Please follow the submission
instructions provided.
A draft submission area will be provided for you to check your assignment for academic
integrity purposes. Instructions to use the draft submission link and how to read the draft
SafeAssign report are in the Task 2 assessment area. After you have made any final edits
you will still need to submit the final assignment (via the Task 2 Submission folder) by the
due date set for it.
The assignment will be marked out of a total of 100 marks and forms 30% of the total
assessment for the course. ALL assignments will be checked for plagiarism and/or collusion
between individuals.
Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct,
Plagiarism and Collusion” guidelines. Academic Integrity Information.
Note: Each student MUST be able to produce a copy of their assignment and this copy
MUST be produced within 24 hours of it being requested by the Course Co-ordinator.
Failure to produce the second copy of the assignment when requested may result in loss of
marks or a fail grade for the assignment.
Requests for an extension to an assignment extension MUST be made prior to the date of
submission and requests made on the day of submission or after the submission date will
only be considered in exceptional circumstances.
ATMC ICT211 Database Design Task 2
Page 3 of 12
Case Study: The Australian Event Planning Co-operative
Background
Before having children Emily Plunkett had had two very different careers, one that she was
extremely good at and one that she absolutely loved. Emily was very good at advertising and
selling goods and services but her favourite occupation was organising weddings. In 2010,
facing the future as a single parent she decided to see if she could combine her two careers
and set up a business that could provide all the items needed to cater for small or large
celebrations. Emily’s idea proved so popular that she received many requests to form a cooperative that would provide a common national web platform to promote and provide
advice on organising events and offer all types of equipment and furnishings.
Emily enlisted the help of a local web developer Liam to build a platform for Australian
Event Planning Co-operative (AEPC). Liam did a great job and Emily was very pleased with
the new AEPC website. It was intuitive to navigate and provided plenty of information to
which the co-operative members could contribute. Emily was extremely pleased when
organisations from all over Australia asked to join the co-operative. However as the number
of members of the co-operative grew, customers and supply organisation owners noticed
data anomalies occurring and an increase in information retrieval time. Additionally order
information went missing and there were some difficulties in entering new information.
Emily decided she needed to talk to Liam to see how the issues could be resolved as she
didn’t want the reputation of AEPC to be marred. Liam admitted that whilst he was good at
designing websites he had less skill and experience at developing databases. As a result
Emily and Liam have asked you, as a database design consultant to make recommendations
for a way forward. Following several consultations and investigation of the current set-up
you realise that Liam had created a flat file database and entity relationships were not
logically defined. Your recommendation is to create a Customer Relationship Management
(CRM) style database to which specific adaptions could be made at a later date. As an
experienced database designer you are fully aware that the functionality Emily would like
will not be possible to achieve in the first project. You explain the need to define a core
database system which will provide a foundation for added functionality in the future.
User Requirements
The following provides what you, Emily and Liam agree would be a good core system for the
first database design project.
Emily believes that the core database entities are co-op members, customers, equipment
items and equipment orders. You generally agree however you also know that there will be
other regular and composite entities, and business rules which will determine the entity
relationships.
Emily explained that co-op members will have a unique member id, a member name, a
contact name, a date when they joined the co-op and an end date (in case they should leave
the co-op – it will be left blank by default), the name of the business, its address, phone
ATMC ICT211 Database Design Task 2
Page 4 of 12
number, and email address and a member description where they can describe themselves
and their speciality.
Liam explained that a customer will register on the AEPC website and must provide their
name, email address and location. The database must also allocate a customer id and a start
date. The customer may order equipment so there must be a delivery address as well.
When asked about equipment as a database entity Emily explained that each piece of
equipment has an item id, an item name, a style name, a pack size and a description.
Each type of equipment may be stocked by many co-op members and each co-op member
will stock many different types of equipment. The price of each item of equipment and the
pack sizes are standard across the co-operative. This entity will have the AEPC co-op
member id and the item id, the price, price date, unit shipping cost (described below for use
in a stored function), and an ‘in stock’ attribute.
Emily explained that customers may order items from any AEPC member. The order will
have an order id, customer id, AEPC member id, order date, order status, shipping date,
courier name, shipping cost multiplier and shipping reference number. Each order must
include one or more order items. Each order item relates to a piece of equipment which has
an item id, order id, item cost, quantity and unit shipping cost.
As pieces of equipment can be of various sizes and customers can order equipment from
any AEPC member shipping costs can vary (for example, a customer in Queensland may
place an order for something that can only be sourced in Victoria). To allow the customer to
get a rough estimate of the total costs, including shipping, a stored database function must
be created as described below. When a client submits an order through the AEPC website,
the order is saved into the database and an alert message is sent to the AEPC member
automatically through the messaging system.
Liam described the messaging system he had built into the AEPC website for customers to
ask questions and send alerts including notifying AEPC members of new customer orders,
etc. You decide to adjust the messaging system in the database to simplify and make it more
efficient as follows: a message entity will have a unique message id, customer id, and AEPC
member id. The message entity will also include a date stamp field and a message field. This
simple format will give Liam the flexibility to use it in many different ways on the website.
ATMC ICT211 Database Design Task 2
Page 5 of 12
Important requirements
Liam would like to use the sample code that you create. For this reason you MUST:
• Incorporate MySQL database and its corresponding SQL and procedural language,
• Sequence your script so that it can be run (and re-run) as one sequential script
without error
Required equipment/item cost calculation stored procedure / function
Customers can order items of equipment from any AEPC member. However shipping costs
for shipping the equipment within that AEPC member’s state is much cheaper than sending
them interstate. Emily has a shipping cost agreement among AEPC co-op members as
follows:
• If a customer orders items of equipment from AEPC members located within the customer’s
state or territory, the total price for the equipment to be delivered to the customer will be
the listed price plus the unit shipping cost.
• For all interstate purchases the customer will multiply the corresponding shipping multiplier
(table below) by the unit shipping cost.
For example:
• Dan from Loopville in Victoria (VIC) ordered 10 serving plates from an AEPC member in
Greenock (SA). The listed price for each serving plate is $5 plus $5 shipping cost. The total
cost to Dan would be (10 * $5) + (1.2 *(10 * $5) = $110. Shipping multiplier = 1.2.
• Georgia from Liverpool in New South Wales (NSW) ordered 10 packs of 10 personalised
coasters as thank-you gifts from an AEPC member in Alice Springs (NT). The listed price for
each pack of 10 coasters is $50 plus $10 shipping cost. The total cost to Georgia would be
(10 * $50) + (1.9 *(10 * $10) = $690. Shipping multiplier = 1.9.
A general calculation is as follows:
Item cost = item quantity * item price
Shipping cost = shipping multiplier * item quantity * item unit shipping cost
Total order cost = shipping cost + item cost
Required data
IMPORTANT NOTE – the following data MUST be used in your Part B SQL implementation.
A ZERO (0) mark will be awarded for Part B if the following data is not used as part of your
SQL implementation.
ATMC ICT211 Database Design Task 2
Page 6 of 12
AEPC Co-op Members

Items / Equipment

ATMC ICT211 Database Design Task 2
Page 7 of 12
Shipping Multiplier Table

ATMC ICT211 Database Design Task 2
Page 8 of 12
Assignment Requirements and Deliverables
Part A – Submitted as a MS Word Document:
• Entity Relationship Diagram in Crows Foot Notation
• Relational Schema – including Primary and Foreign Keys
• Note the ER diagram and Relational Schema can be combined when using
draw.io
• Supplementary Design requirements – for example but not limited to:
o information on length of identifiers, postcodes, names
o data attribute information (compulsory, variable length / type, etc.)
• Assumptions
Part B – Submitted as a single plain text file with name <studentNumber>_crm.sql,
containing all your SQL implementation:
IMPORTANT NOTES:
• Liam uses a MySQL database. Your Part B MUST work on a MySQL database
and be able to be demonstrated to Liam so he is able to apply your SQL
implementation into the AEPC website.
• Where you are asked to incorporate the exact data provided there will be a
Zero (0) mark awarded if different data is incorporated.
Instructions:
• CREATE TABLE statements for all tables including integrity constraints
• CREATE TRIGGER statements:
o Automatically insert a message to the Message table when a customer
order is placed
• CREATE FUNCTION / PROCEDURE
o implement the item cost calculation as a function or stored procedure
• INSERT INTO statements for populating the database:
o Incorporate the exact 6 AEPC member names given in the dataset
(make up email addresses and phone numbers)
o Incorporate the exact 11 items and the details given in the dataset
o Incorporate the Shipping Multiplier table into the database as an entity
/ table – it is recommended to use the abbreviated state name in the
database (Vic, Qld, NSW, etc.)
o Incorporate the pricing for items of equipment and their shipping costs
o Create at least 3 customer entries
o Create at least 3 customer orders
o Data may need to be inserted in a particular order to comply with
integrity constraints
ATMC ICT211 Database Design Task 2
Page 9 of 12
• SELECT statement/s that will produce the following data for a sample order
(you will need to have the data in the database for this query):
o The customer ORDER will include:
customer name and account number
order number / id and the total amount for the order
order date
At least three order items:
• item name
• quantity
• price
• shipping cost
• item total cost incorporating the item cost calculation
Function / Procedure (HINT a function is much easier to
call in a SELECT statement)
• SELECT statement that will produce an order report based on a date range and
co-op member (you will need to have the data in the database for this query):
o List of all orders between a start date and an end date for a particular
co-op member
o the report will be grouped by customer
o each line in the list will include customer name, customer state, order
number / id and total for that order
o will be ordered by oldest order at the top
Submission
The completed assignment is to be submitted by SafeAssign on or before the due date.
The assignment will be assessed according to the marking sheet (please see the marking
rubric at the end of this document). Late submission of the assignment will result in a
percentage deduction of marks in accordance with the submission penalties stated in
section 10.3 of the course outline. (This includes weekends.)
Assignment Return and Release of Grades
Assignment grades will be available on the course web site. An electronic assignment
marking sheet will be available.
Where an assignment is undergoing investigation for alleged plagiarism or collusion the
grade for the assignment and the assignment will be withheld until the investigation has
concluded.

ATMC ICT211 Database Design Task 2
Page 11 of 12

ATMC ICT211 Database Design Task 2
Page 12 of 12

The post ICT211 Database Design appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms