Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DEPARTMENT OF COMPUTING

COMP1350 2021 – ASSIGNMENT ONE

Introduction to Database Design and Management

Database Design Assignment

Case Background

Meraki Delivery Company has been in the trucking business for the last 30 years. They started storing data in a file-based system and then transitioned into using spreadsheets. As years progressed, their business has grown exponentially leading to an increase in the number of customers and volume of data. You have been recently employed a database model to replace the current spreadsheets.

You have been provided with the following business rules about Meraki Delivery Company

Customers: Every customer needs to be uniquely identified in the system. Other details which the system needs to store about a customer include their name, address, and phone number. Some customers may sign up to the loyalty program to become a 'VIP customer' in which case the date they signed up and the VIP discount percentage they will receive should be recorded.

Trucks: Every truck is uniquely identified within Meraki using a primary identifier. Other details of a truck include the “vehicle identification number” (VIN) of the truck, the registration number, the colour, the year it was purchased and the purchase price. Every truck is of a particular make and model. Every “make” of truck is identified with a unique identifier. Meraki keeps a record of the manufacturers where they can purchase certain makes of trucks. For the manufacturer, the name of company, a contact person name in the manufacturer’s company, email, and phone number are recorded. A manufacturer can potentially provide many different truck models. A model number is unique for every truck make and this means a model number will be reused for every make of the truck. The name and year of the model are also stored. There are two types of trucks: light trucks and heavy-duty trucks. The load capacity needs to be recorded for heavy-duty trucks.

Services: Every delivery service available is uniquely identified using a primary identifier. Other details of a service include the name, cost, and the maximum distance the service can cover. A delivery service can be either a short transport or a long transport. Light trucks are used for short transport only, and heavy trucks are used for long transport services only. The history (a from date and a to date) of which transports have used which trucks need to be stored as well.

Locations: Every location being serviced by the company is uniquely identified using a primary identifier. The name of the location, post code, and state are recorded for them. Some locations are fuel stops in which case, the name of the fuel stop and their facilities are stored.

Staffing: Every Meraki staff member is provided with a unique staff number. The company also needs to keep track of other details about their staff members like their name and phone number. There are two types of staff that need to be tracked in the system: Full-time Staff and Casual Staff. For full-time staff, the year they started, and their annual salary need to be recorded. For casual staff, the year they started and hourly rate needs to be recorded. Truckers are employed full time only and their licence number and any preferences they may have for driving short/long trips are recorded.

Booking Requests: Customers can make a booking request for one of the services provided. Every request that comes in is identified using a primary identifier. Other details captured are the request date and the request details. After going over the booking request, the admin staff at Meraki creates a schedule for the booking request. The schedule would include the following: the truck, type of transport/service, the details of the admin staff who has created the schedule, locations involved (a starting point, an end point, any intermediate stop over points), a trucker and any extra staff (who may be full-time/part-time) required during the schedule. For the stop-over points, the start and end time, along with the reason for stop has to be recorded by the trucker. The schedule would also include the start date, the end date, and the status of the schedule. Every booking request generates two invoices- an initial one that requests a down payment and a second one that bills the remainder of the amount. Invoice numbers are unique, and they belong to a particular booking. Payments are made on the invoice and the company requires a single payment to be made within 3 days from the invoice date. Every payment is uniquely identified using a primary identifier. Payments are only made using a PayPal link and hence the PayPal reference number is also recorded.

Task Description

Task 1- EER Diagram (60 marks)

Based on the business rules, you are expected to construct an Enhanced-ER (EER) diagram. The EER diagram should include entities, attributes, and identifiers. You are also expected to show the relationships among entities using cardinality and constraints. You may choose to add attributes on the relationships (if there are any) or create an associative entity, when necessary. Your diagram should also specify the complete (total) and disjoint (mutually exclusive) constraints on the generalization/specialization.

Task 2- Logical Transformation (40 marks)

Based on your EER, perform a logical transformation. Please use 8a for your step8 to keep the process simple. Please note, if there are errors in the EER diagram, this will impact your marks in the transformation. However, the correctness of the process will be taken into account.

What to include in your report (as a PDF)

● To present your answers to these tasks, please use the template provided in the assignment folder.

● Fill out the details provided on the first page of the template.

● For Task 1, attach the image in the document of the EER model you have created. You can use a tool of your choice to generate the diagram. Hand-drawn diagrams will not be accepted.

● For Task 2, write the answers under the right space in the template. Steps have to be clear with primary and foreign keys listed explicitly.

● Assumptions: If you have any assumptions, please list them down in the first page of the report. (e.g. about relationships between entities in your EER Model). Please note only make assumptions if something is not clear. Assumptions will only be valid if they are not contradicting something which is given in the case study.

● If your images lack clarity, you will not be given any marks. Tutors can zoom in to check the diagram but should not have to deal with images that are blurry / fuzzy. Whatever is submitted is the final submission. So, please make sure your image is readable.

What to Submit for the Assignment

● One pdf document with all the answers included. Please do not submit multiple files.

● You can submit many times up until the deadline (so get an early version in just in case)

Please submit your work on iLearn (there is a Turnitin submission link in the Assignments section) as a report (as a .pdf ONLY) by 11 am Monday 06 September 2021. You need to rename the template provided using this format (e.g. StudentName_StudentID.pdf).

A note about the submission deadline:

The submission deadline is a deadline, not a goal! You will have other assignments due around the same time... and this is not an assignment you will be able to complete at the last minute!

Marking Rubric