Defining Entities and Data Modeling— Business Rules

Given the following business rules, construct an ER diagram so each rule is captured for the database. Presume each rule is to be treated individually. Construct an ER diagram for each rule.

a. A retail sales company prepares sales orders for its customers’ purchases. A customer can make many purchases, but a sales order is written for a single customer.

b. A retail sales company orders inventory using a purchase order. An inventory item may be ordered many times, and a purchase order may be created for more than one inventory item.

c. A company that sells antique cars prepares a sales order for each car sold. The inventory for this company consists of unique automobiles, and only one of these automobiles may be listed on a sales order.

d. A grocery store identifies returning customers via a plastic card that the clerk scans at the time of each purchase. The purpose of this card is to track inventory and to maintain a database of customers and their purchases. Obviously, a customer may purchase an unlimited number of items from the grocery store. Items are unique only by a UPC code, and each UPC code may be associated with many different customers.

e. A video rental store uniquely identifies each of its inventory items so customers can rent a movie and return the movie via a drop box, and the store can identify which copy of the movie was rented and returned. A customer is allowed to rent up to six movies at a time, but a copy of a movie can only be rented by one customer at a time.

Comprehensive Case

(Prepared by Katie Daley and Gail Freeston, Lehigh University)
D&F is a distributor of CDs and cassettes that offers benefits such as discount prices and an introductory offer of ten CDs or cassettes for a penny (not including the shipping and handling costs). Its primary target customers are college students; its main marketing strategy is constant deals to club members. The company’s main competitors in the industry are BMG and Columbia House; both offer similar promotions.
D&F started in 1993 with an office in Harrisburg, Pennsylvania, initially targeting college students in the surrounding area. The company realized there was a high demand for discounted music merchandise and the convenience of mail delivery within universities. After its second year, with a constant increase in customer orders, D&F relocated to Philadelphia, where it was located near more colleges and universities. The move has had a positive effect on net profits and demand, supporting the decision to continue the growth of the company. D&F recently expanded its facility to be able to fulfill a higher demand for its services. Its customer base ranges from areas as close as Villanova University to as far as Boston College. As of 2007, there were 103 employees. Their prior year’s gross sales were $125 million.
D&F’s market share is on the rise, but is not yet comparable to the magnitude of BMG and Columbia House. However, the corporation’s goals for the upcoming years include establishing itself as an industry player through increased customer satisfaction and loyalty. D&F is also considering the installation of a new information-processing system. This system will reengineer their current business functions by reducing loopholes in their internal control problems.
D&F receives CDs and cassettes from various wholesale suppliers and music store chains, totaling 32 suppliers nationwide. The office has its own warehouse, stores its own merchandise, and is responsible for replenishing the inventory. D&F has had no substantial problems in the past with their suppliers. On the other hand, it has encountered problems with excess inventory, stock-outs, and discrepancies with inventory records.
Revenue Cycle

Becoming a member of D&F Music Club involves calling the toll-free number and speaking with a sales representative, who establishes a new customer account. A customer’s account record contains his or her name, address, phone number, previous orders he or she made with the company, and a sequentially assigned unique customer account number.
Customers place orders by phone with a sales representative, who prepares a sales order record. John, in the billing department, reviews the sales orders, adds prices and shipping charges, and prints a copy (invoice) that is sent to the customer. John then adds a record to the sales journal to record the sale.
Chris, a warehouse employee, verifies the information on the sales order, picks the goods, prints the packing slip, and updates the inventory subsidiary ledger. Chris prepares the bill of lading for the carrier. The goods are then shipped.
Sandy in AR updates the customer accounts and general ledger control accounts. When customers make a payment on account, they send both the remittance advice (that was attached to the invoice) and a check with their account number on it. Scott, a mail room clerk, opens all the cash receipts. He separates the check and remittance advice and prepares a remittance list, which, along with the checks, is sent to the cash receipts department.
Laura, the cash receipts clerk, reconciles the checks with the remittance, updates the customer’s account and the general ledger, and then deposits the checks in the bank. She sends the deposit slip to Sandy in the accounting department.
Upon receiving the bank receipt, Sandy files it and updates the cash receipts journal to record the amount deposited. Upon the receipt of the CDs or cassettes ordered, the customer has a 15-day trial period. If, at the end of that period, he or she sends a payment, it is understood that the goods have been accepted. If, on the other hand, the customer is dissatisfied with the product for any reason, he or she can return it to D&F Music Club at no charge. However, to return the CD or cassette, the customer must call the company to obtain an authorization number. When the goods arrive, Chris prepares the return record and updates the inventory subsidiary ledger. Printed copies of the return record are sent to John and Sandy. John reviews the return record and updates the sales journal. Sandy credits the customer’s account and updates the general ledger to reverse the transaction.
Expenditure Cycle

The purchases system and the cash disbursements system comprise D&F Music Club’s expenditure cycle. The three departments within the purchasing system are the warehouse, purchasing, and accounting. The purchasing function begins in the warehouse, which stores the inventory of CDs and cassettes. Jim, the warehouse manager, compares inventory records with the various demand forecasts of each week, which the market research analyst teams provide, to determine the necessary orders to make. At the end of the week, Jim prepares the purchase requisition record.
Sara, the purchasing clerk, reviews the purchase requisitions, selects the suppliers, and prepares the purchase orders. Copies of the purchase orders are sent to the supplier and accounting.
When the shipment arrives, Chris, the warehouse clerk, working from a blind copy of the purchase order, counts and inspects the goods for damage. He then prepares a receiving report and updates the inventory records.
Upon receipt of the supplier’s invoice, Diana, the accounting clerk, compares it to the respective purchase order and receiving report. If the invoice is accurate, Diana creates an AP record, sets a due date to be paid, and updates general ledger accounts.
On the due date, Evan, the cash disbursements clerk, closes the AP record, cuts a check, and sends it to the supplier. He then updates the check register and the general ledger.

Required:

Assume that the manual system described is to be automated using a relational database system. Perform the following tasks. You may need to make assumptions about how certain automated activities will be performed.

a. List all candidate entities in the procedures described.

b. Identify the valid entities and explain why the rejected entities should not be modeled.

c. Create a data model of the processes showing entity associations.

d. Create a fully attributed model by adding primary keys, foreign keys, and data attributes. Normalize the model.

e. Prepare a data flow diagram of the system showing the data stores.