PhoneSource

Conceptual model (with entities, relationships, attributes, and cardinalities) and a minimal relational database for the following object system:

PhoneSource is a company that sells several kinds of telephones to customers. It has only one store location. Product offerings include traditional corded phones, cordless phones, cellular phones, etc.Each type of phone comes in various models; each model has a different stock number.Although cellular phones must be programmed with unique information for each customer, PhoneSource does not offer cellular service or the corresponding programming.Therefore PhoneSource does not need to keep track of each individual phone; it is sufficient to track them by model number.PhoneSource has several salespeople who are paid on commission.Although each customer is assigned to one particular salesperson, if a customer's assigned salesperson is not available when the customer comes in, another salesperson may assist that customer. The resulting commission is split between the two salespeople.A customer's assigned salesperson may be unavailable because of a sick day or vacation day or because he or she is helping another customer to whom the same salesperson is assigned. One sale may be for several models of phones. No sale is allowed without the customer paying at least 25% down at the time of the sale; the cash receipts are processed by the salesperson that is assisting the customer.Most customers pay the 25%, and then make 3 more equal monthly installments.Some customers pay in full at the time of the sale. A customer's payment may include payments for multiple sales. Salespersons handle all cash receipts from customers.PhoneSource has only one cash account into which all cash receipts are deposited (including those from sources other than sales, such as bank loans).Salespeople, customers, and inventory items are often input to the system before any transactions involving them occur; however, a customer cannot be entered without assigning a salesperson to him or her. Below, PhoneSource has identified attributes that need to be included in its information system (don't add or subtract any attributes).Beside each attribute is an abbreviation (in bold), which you may use in your conceptual model and in your relational database tables:

-phone-stock-number (phone#)-salesperson-name (sp-name)

-salesperson-address (sp-add) -customer-ship-to-address (c-ship)

-phone-description (p-desc) -sale-date (s-date)

-salesperson-SSN (sp#)-salesperson-telephone (sp-ph#)

-customer-number (cus#)-customer-name (c-name)

-sale-invoice-number (sale#)-customer-billing-address (c-bill)

-total-quantity-on-hand of each phone type (qoh) -phone-item-actual-selling-price (act-price)

-phone-item-standard-cost (std-cost)-phone-item-list-price (list-price)

-total-dollar-amount-of-sale (s-amt)-remittance-advice-number (ra#)

-cash-account-number (c-acct#)-cash-receipt-date (cr-date)

-cash-account-balance (c-bal)-remittance-advice-total-amount (ra-amt)

-cash-receipt-amount-applied-to-a-sale (cr-app)-location-of-cash-account (c-loc)

-quantity-sold of a phone (on a particular sale) (qty-sold)