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. A single device is uniquely identified by an IMEI number. 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 color of each phone in its inventory. The company keeps track of all phone models that are compatible with its plans, but only has individual device level data for the phones in its inventory. Each phone model is identified by the combination of manufacturer, model and series. 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 at all in the case of those phones that it does not sell). A service plan is priced allocation of minutes, text and data that is applicable 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 certain 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 record of the contract 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. A contract is uniquely identified by an agreement code. Contracts are described by a start and end date and a phone number. Contracts are renewable on an annual basis 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 contract. 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 billthe 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 is stored. Of course, customers pay bills. A specific phone (this not with respect to a phone model but to a single device identified by IMEI) may be sold to several customers (true in the case where a customer returns the phone and the company resells it). Returns are not managed by this database. A customer may, over time, purchase several phones. The purchase date and purchase price of the phone are recorded. Draw an (E)ERD for this description. Show all FK and PK symbols, decompose an M:N relationships. (30 Points)
