Perform logical design to produce appropriate 3nf relations

Assignment Help Database Management System

Reference no: EM132277168

Objectives

• To develop a conceptual data model diagram
• To perform logical design (optional)

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study:

• Draw an Entity Relationship (ER) diagram
• Perform logical design (optional)
• Create 3NF 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 collects the parcels from its customers and then transports and delivers them to the respective specified delivery addresses in Australia.

When a new customer (sender of the parcels) requests for parcel deliveries, CQPS collects many 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 customer are 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 parcel is 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.

Note:

The attributes of the possible entities have not been mentioned clearly. Hence, you need to assume at least two relevant attributes for each of the entities that you will identify for your ER diagram.

The delivery charge for a parcel need not be assumed as calculated value (i.e., it can be assumed to be a stored attribute).

Assignment Requirements

In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF 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.

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 ERD must
• show all necessary entities, attributes and relationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes, if any
• show participation and cardinality
• show associative entities, if appropriate
• show weak entities, if any
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes as specified in the unit text book

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ERD accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer makes an order with them. To get yourself started, ask yourself, 'If I were running this business, what things I need to keep a list of?' Write those things down. For each thing, what information would you need

to record about it? How can it be identified? The answers to these questions will help you to develop your ERD.

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 relations in the following format:
Customer (CustomerId, CustomerName, Street, Suburb, State, PostCode, Email)
Invoice (InvoiceNumber, InvoiceDate, Total, CustomerId)
foreign key (CustomerId) references Customer(CustomerId)

Attachment:- Case.rar

Reference no: EM132277168

Questions Cloud

Describes how and where reciprocal inhibition might be used : Describes how and where reciprocal inhibition might be used to improve patient outcomes in quality and improve patient safety.

Run frequency distribution tables and descriptive statistics : SPSS Assignment - For all variables Q1-Q8, including "Teacher_Satisfaction," run frequency distribution tables and descriptive statistics

What has to go right for each of the strategies to work : What could go wrong and what are the consequences? What has to go right for each of the strategies to work?

Are hispanic and latin consumers different : Are Hispanic and Latin consumers different? Do they acculturate similarly or differently in the U.S.?

Perform logical design to produce appropriate 3nf relations : COIT20247 - Database Design and Development - cq university - perform logical design to produce appropriate 3NF Relations - draw the ER diagram (ERD)

Entrepreneur give someone starting their own business : What advice would the entrepreneur give someone starting their own business?

What are some of the ways quality is measured in healthcare : Complete this discussion in a 150 - 300 response. Discuss the role of quality in healthcare organizations. Evaluate the need for quality by answering.

Key elements you need to address in bill of material : What are the key elements you need to address in a Bill of Material?

How would you summarize the results of the frequency tables : Using the Generalization, Example, Exception framework, how would you summarize the results of the frequency tables

Reviews

len2277168

4/8/2019 2:18:47 AM

Marking Criteria: Item Maximum Marks 1 Identified entities (including super type and sub type entities), associative entities, weak entities (if any) and named appropriately 6 2 Provided appropriate attributes 6 3 Provided identifiers/identifying attributes 3 4 Drawn correct relationships between entities and named them appropriately 5 5 Used proper symbols for • entities including super type and sub type; associative entities; weak entities if any • relationships • identifying attributes • min and max cardinalities for the relationships 6 6 Provided appropriate justification for assumptions and business rules 4 Penalty due to plagiarism/copying Late Penalty: Less 5% of available marks per calendar day Total 30

4/8/2019 2:18:40 AM

• Before submitting your assignment, review the marking criteria that are available in the assignment specification. Ensure that all the criteria are addressed in your submission. • After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure that they are correct. • Penalty related to late submission and plagiarism is handled as per the university policies.

4/8/2019 2:18:31 AM

Important points • You must work individually on this assignment. • The model solution to this assignment will be released approximately TWO (2) weeks after the due date. • Assignments that are submitted after the release of the model solution will receive zero marks.

4/8/2019 2:18:23 AM

You must make an electronic submission for this assignment using the appropriate assignment submission link in the unit web site. You should submit a Microsoft Word document containing: • A copy of your ERD. • A list of business rules and assumptions including their justifications. • 3NF Relations – optional Your document should contain appropriate identifying information (your student number, name, unit number, etc.) and should contain appropriate headings for each section.

Write a Review

Required(*) Message

User Account

All Pages