Entity-relationship diagram and converting ERD to tables.
ERDs should be drawn with entities, relationships, attributes and primary key of each entity, cardinality of each relationship, and attributes of each relationship (if any). Avoid using foreign keys as attributes of an entity if possible.
Note that primary key of an entity is not always specifically given. Since each entity must have a primary key, you must give each entity a primary key even if what the PK may be is not specifically spelled out in the data requirements.
There is no need to convert the ERDs into tables for the first problem.
Problem 1: The database should track a health insurance company's various plans offered and all policies established. The following detailed information requirements should be documented by the ERD.
• The insurance company offers multiple health insurance plans. Each plan has a unique plan code name and a maximum plan year coverage amount (for example, the insurance plan may pay out up to $5 million during the plan year).
• A health insurance plan is not offered in all states. Database needs to store all states in which a plan is available as well as the starting and ending dates of this availability.
• When a subscriber obtains a health insurance plan, the company establishes a policy number for it. Database needs to store a unique policy number, policy effective date, policy expiration date and what type of health care plan a policy is established under.A policy is offered under only one health insurance plan.
• Each subscriber has only one insurance policy with this insurance company.
• A subscriber of a policy, i.e., the insured, can be an individual or a family. If the insured is a family, database needs to store social security numbers, names, phone numbers, and addresses of all insureds. One of the family member is designated as the primary insured.
Problem 2: Convert the following ERD into a set of tables. Be sure to identify the primary key for each table you write. Identify foreign keys in each table after conversion, if any.
When you convert a relationship, be sure to use a different font for all attributes added to a table. If hand-writing, circle all attributes added as a result of relationship conversion. List all new tables added at the end.
