1. Draw an ERD diagram for the following situation. Show or mention entities, attributes, cardinalities, and relationships, Show relationship integrity with primary and foreign keys. Name relationships. Resolve many to many relationships, if any. (20 Points)

A telephone company wants to create a database to manage certain aspects of customer billing. The company sells mobile phones as well as mobile plans for those phones. The company keeps track of each phone it has in its inventory. An IMEI number uniquely identifies a single device. No two phones (even if they are of the same brand and model) can have the same IMEI. In addition to the IMEI, the company keeps track of the list price, the condition (new or refurbished), and the color of each phone in its inventory. The company keeps track of all phone models compatible with its plans but only has individual device-level data for the phones in its inventory. Each phone model is identified by the manufacturer, model, and series combination. The company keeps track of the release date of the phone model and its features. For each phone model, the company may carry several phones in its inventory or none (in the case of those phones that it does not sell). A service plan is priced allocation of minutes, text, and data that applies to one or more phones. These plans are described by their standard price, the number of voice minutes, the number of text messages, and the amount of data allotted per month by subscribers. When specific plans are activated on certain phones, a surcharge (an additional monthly fee) is added to the plan price. While some plans are available for customers with any phone model, other plans are only valid with a subset of phone models.

When a customer subscribes to a plan, a contract record must be stored. If the customer subscribes to a plan with a phone purchased from the company, the IMEI of the phone must be stored. A customer may purchase a plan without ever buying a phone from the company, and the company will activate that plan for whichever phone the customer has. However, any phone purchased from the company must be accompanied by a plan from the company. An agreement code uniquely identifies a contract. Contracts are described by a start and end date and a phone number. Contracts are renewable annually even when the customer does not purchase a new phone. Sometimes a customer purchases several plans to accommodate their family members (family plans). In these cases, one contract is designated the parent contract and is associated with all other contracts in the family plan. A non-parent contract within a family plan can be associated with at most one-parent contracts. Of course, most contracts will not be purchased as part of a family arrangement.

A contract results in several monthly bills. A bill is identified by a bill number. Bills are described by the total voice, text, and data costs for the billing period. These values are computed by a separate system but are to be stored within this database. The date of the bill, the start and end dates of the periods that the bill covers, and the due date of the bill are also stored. A bill can be paid in multiple installments. The date and amount of each payment are stored. Of course, customers pay bills. A specific phone (this not with respect to a phone model but a single device identified by IMEI) may be sold to several customers (true when a customer returns the phone, and the company resells it). This database does not manage returns. A customer may, over time, purchase several phones. The purchase date and purchase price of the phone are recorded.