Reference no: EM132280087
Objectives
• To develop a conceptual data model diagram
• To perform logical design (optional)
Assignment Specification
In this assignment, youneed to perform the followingtasks for the given case study:
• Draw an Entity Relationship (ER) diagram
• Perform logical design(optional)
• Create3NF Relations(optional)
Please note that the above mentioned optional components,logical design and 3NF relations, will not be marked, however those tasks are expected to enrich your skills in the creation of database in future.
Case Study
CQParcels (CQPS) is well known for its parcel handling business in Australia. It collectsthe parcels from its customers and then transports and delivers them to the respective specified delivery addresses in Australia.
When anew customer(sender of the parcels)requestsfor parcel deliveries, CQPS collectsmany details that are related to them. Among the many information that is required to be collected at that time, the contact phone and email details of the customerare considered to be vital.
Whenever a customer requests a parcel delivery, the parcel-delivery information such as the delivery address and receiver details are collected. Subsequently, one of CQPS employees collects the parcel from the sender, verifies whether the address for delivery is mentioned on the parcel, and then creates an invoice for the charges related to its delivery.
For each parcel-delivery, CQPS also maintains the details of the employee involved, the date and time of collection/pick up and delivery. Though most of the parcels are delivered to the specified addressees, some of them occasionally are not deliverable due to incorrect/incomplete delivery address. Such parcels are returned to the senders.
When an undelivered parcelis not returned to the sender/customer within a month of collection, then the customer can lodge a compensation claim to CQPS. The compensation claims are verified and settled by CQPS based on their records.
Assignment Requirements
In this assignment,you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionallyperform logical design to produce appropriate3NF Relations.
The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study.
Your ERDmust
• show all necessary entities, attributes andrelationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes, ifany
• show participation andcardinality
• show associative entities, ifappropriate
• show weak entities, if any
• use the notation described in the settext
• use consistent and appropriate naming for entities and attributes as specified in the unit text book
2 Assumptions and Business rules
Provide assumptions and business rules relevant to your ERD.
3 Create Logical Design (Optional):
If you would like to perform this optional component of this assignment, you can map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide all the relationsin the following format:
Customer (CustomerId,CustomerName,Street,Suburb,State,PostCode,Email)
Invoice (InvoiceNumber,InvoiceDate, Total, CustomerId)
foreignkey(CustomerId)referencesCustomer(CustomerId)