EER Diagram 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.

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 requested date and the requested 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 endpoint, any intermediate stopover 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 the 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.

Answer rating: 100% (QA)

1Your ... View the full answer