Ask DBMS Expert


Home >> DBMS

Assignment -

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)

Case Study -

CQ Insurance (CQI) offers insurance policies to insure Home-buildings and Home-Contents of Australian residents. For each of their properties (Homes) of interest, the customers can apply for either a Home-buildings policy or a Home-contents policy or a combined Home-buildings-contents policy. CQI allows a customer to have only one of the above mentioned policies for each of his/her properties.

When new customers approach for an insurance policy (hereafter called as policy), CQI collects their details so that they can communicate with them in future. Each policy requires full premium payment prior to the coverage period of one year. At the time of preparing the policies, CQI collects the details of the property (Home) and/or items (Home contents) depending upon the type of the policy that the customer is interested in.

At the time when a customer lodges an insurance claim (hereafter called as claim) such as collapse of false ceiling, loss of home contents, etc., CQI ensures that the insurance policy has not lapsed prior to processing the claims and further verifies that the items claimed have been covered by the respective policy. A claim should be related to only one policy but each claim is not limited to only one item that has been covered by the policy. As part of the claim-processing for each of the claims, an assessor (appointed by CQI) prepares and submits an initial assessment and if required the assessor also prepares and submits a final assessment. The assessor's initial assessment may recommend either to further process or reject the related claim. The assessor's final assessment will provide the total amount that can be settled relating to the claim and may contain a memo that describes the recommended settlement amount for each of the items that have been claimed.  

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

In addition to the recommendations in the assessor's report, the following processes can also provide the reasons for rejection of the claim:

  • Verification to ascertain whether the claimed items have been covered by the respective policy or not
  • Ensuring that the policy is in force (has not lapsed) at the time of lodging the claim

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.

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 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 policy because it is assumed that the business does not record customer details until the customer opts for a policy 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)

How and what to submit:

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.

Attachment:- Assignment File.rar

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92745588
  • Price:- $75

Guranteed 36 Hours Delivery, In Price:- $75

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As