Tom owns a small recreational trailer business in a suburban community located close to the mountains. The community is relatively small but growing at a fast rate. Tom's business is growing, not because of his effective sales style and personality, but by growth of the community. Currently, Tom's competition has been nearly nonexistent, but as the area grows he expects to encounter increasing competition.

Tom sells mostly trailers for vacationing and camping. When customers arrive on Tom's lot, they are greeted by a salesperson. The salesperson may show the customers the trailers on the lot, but the salesperson need not be present during the entire showing. Depending on customer preference, the salesperson will either take the customer on a tour or the customer may roam the lot freely, inspecting trailers at their leisure.

Since recreational trailers are fairly large-ticket items, customers will often leave the lot without making a purchase, only to return another day after making the decision to purchase a trailer. When a customer decides to make a purchase, the salesperson initiates a series of procedures to properly document the order and sale transaction. First, the salesperson determines the model of the selected trailer and offers the customer a list of options that correspond to the particular model. The customer may (1) purchase a trailer off the lot with no added features, (2) purchase a trailer off the lot with additional features, or (3) special order a trailer that is not currently on the lot.

In most cases, customers do not pay cash for their trailers. If, however, the customer pays cash, a simple sales contract is prepared and the customer drives off with his or her trailer. The majority of the customers use an installment method of purchase. Before an installment purchase is authorized, the customer's credit must be verified to determine credit worthiness.

With an installment purchase, an installment agreement is prepared in addition to the sales contract. Tom has arranged financing through a local bank for all installment sales. When an installment sale is made, the bank sends Tom a lump-sum payment equal to the price of the trailer. Instead of making payment to Tom, customers pay the bank plus interest. In either case, Tom receives a lump-sum payment for each trailer sold, whether that lump-sum comes from the customer or from the bank.

Once the credit is approved, the customer can take delivery of the trailer. This involves a delivery person who checks the trailer before delivering it to the customer. The customer may pick up the trailer or have it delivered by Tom.

Required:

Tom's Trailer Sales has identified the following events of interest: Customer Looks at Trailers; Customer Orders Trailer; Deliver Trailer; and Receive Payment.

a. Identify the resources, agents, and locations associated with these events.

b. List all direct relationships with cardinalities.

c. Draw an REAL model with cardinalities.

d. Create any needed tables, complete with key attributes, foreign keys, and non-key attributes. (Your instructor may require you to perform this step using a DMBS).

Suggested Solution:

a. NOTES: On your REAL model, you may have a direct relationship between Look and Order. This is acceptable. We decided to omit it between there is the chance there are numerous visits that do not lead to orders, and this data, though useful, is often incomplete. We can use queries to analyze the relationship between orders and looks (visits) by searching for a common customer number. Also, your model may specify more than one delivery person per delivery. Finally, we assumed only one trailer per order and delivery (sale) event.

Event / Resources / Agents
Customer looks at trailers / Trailer / Customer,
Salesperson
Customer orders trailer / Trailer, Accessories / Customer, Salesperson, Credit Manager
Tom's delivers trailer / Trailer, Accessories / Delivery Person, Customer
Customer sends in payment / Cash / Cashier,
Customer, Bank

b. Direct Relationships

Customer looks at trailer - Salesperson

Customer looks at trailer - Customer

Customer looks at trailer - Trailer

Customer orders trailer - Credit Manager

Customer orders trailer - Salesperson

Customer orders trailer - Customer

Customer orders trailer - Trailer

Customer orders trailer - Accessories

Tom's delivers trailer - Delivery person

Tom's delivers trailer - Customer

Tom's delivers trailer - Trailer

Tom's delivers trailer - Accessories

Customer sends in payment - Cashier

Customer sends in payment - Customer

Customer sends in payment - Cash

Customer orders trailer - Tom's delivers trailer

Tom's delivers trailer - Customer sends in payment

Cash – Bank


c.

d.

LOOK - Visit #, [Salesperson Employee #], [Customer #], Date, Time

ORDER - Order #, [Salesperson Employee #], [Customer #], [Credit Manager Employee #], [Trailer #], Date, Time, Delivery instructions, Cancel by date, Negotiated trailer price (excluding accessories)

DELIVER - Sale #, [Order #], [Delivery Person Employee #], [Customer #], Date, Time, Customer pickup or delivered, Sales tax, Salesperson commission, [Trailer #], Trailer Vehicle Identification Number, Trailer price (excluding accessories)

RECEIVE PAYMENT - Cash receipt #, [Cash account #], [Cashier Employee #], [Customer#], Date, Time, Amount received

LOOK/TRAILER - [Visit #], [Trailer #]

ORDER/ACCESSORIES - [Order #], [Accessory #], Quantity, Negotiated price each

DELIVER/ACCESSORIES - [Sale #], [Accessory #], Quantity, Price Each

DELIVER/RECEIVE PAYMENT—[Sale #], [Cash Receipt #]

CUSTOMER - Customer #, Name, Street address, City, State, Zip, Telephone #, Credit rating, Credit limit

TRAILER - Trailer #, Description, Make, Model, Year, Product specification, Current Price, Available accessories (memo field)

ACCESSORIES - Accessory #, Description, Product specification, Current Price

CASH - Cash Account #, [Bank #], Type of account (e.g., checking, savings, CD, etc.), Beginning Balance, Beginning Balance date

BANK - Bank #, Bank name, Contact person, Bank street address, City, State, Zip, Bank phone #, Bank fax #

EMPLOYEE* - Employee #, Name, Street address, City, State, Zip, Telephone #, Birth date, Marital Status, Job code (e.g., salesperson, delivery person, cashier, etc.), Start date, Salary, Comments

*NOTE: You can create separate tables for the various types of employees or create field name aliases when using employee fields. These options are important when using some database software because they simplify your ability to embed referential integrity specifications.

e. Recording

Record customer visit to look at trailers

Record customer order

Record delivery of trailer to customer

Record cash receipt (customer payment)

Maintaining

Maintain Employee data

Maintain Cash account data

Maintain Bank data

Maintain Customer data

Maintain Trailer data

Maintain Accessories data

Reporting (Examples)

Report correlation between customers looking at trailers and customers ordering trailers

Report time between an order event and a delivery event

Report salesperson commissions