Description
Jelly Stone Travel Vehicles sells new and used recreational vehicles.
When new vehicles arrive at Jelly Stone Travel Vehicles from the
manufacturer, a new vehicle record is created. Included in the new
vehicle record is the following information: vehicle identification
number (VIN), name, model, year, name of manufacturer, and cost
(which is the amount paid to the manufacturer).
When a customer arrives at Jelly Stone Travel Vehicles, she works
with a salesperson to discuss a vehicle purchase. The customer can
purchase a new or used vehicle. On the new vehicle the customer can
add options like a microwave, special lighting, fridge, stove, better
seats, wi-fi etc.
When the purchase has been agreed to, a sales invoice is completed by
the salesperson. The invoice summarizes the details of the purchase.
It will include all customer information, information on the vehicle
being purchased and any options (if any), information on the trade-in
vehicle and the trade-in dollar amount allowed (if a trade in
exists). If the customer requests dealer-installed options, they
will be listed on the invoice as well as the price. The invoice also
summarizes the final price, plus any applicable taxes (8.5%) and
license fees. The transaction concludes with a customer signature on
the sales invoice. Assume that the selling price is the price the
product sells for. There is no negotiating a lower price. This
applies to options and the vehicle.
Customers are assigned a customer ID when they make their first
purchase from Jelly Stone Travel Vehicles. Name, address, and phone
number are recorded for the customer. If there is a trade-in vehicle
it is described by a serial number, make, model, and year. Dealer
installed options are described by an option code, description, cost
from the manufacturer and selling price.
Each invoice will list just one customer and one vehicle sold. It is
rare but if a customer wants 2 vehicles then it requires two invoices
be prepared. A person does not become a customer until they purchase
a vehicle. Over time, a customer may purchase a number of vehicles
from Jelly Stone Travel Vehicles.
Every invoice must be filled out by only one salesperson. A new
salesperson may not have sold any vehicles, but experienced
salespeople have sold many vehicles.
A customer may decide to have no options added to the vehicle, or may
choose to add many options. The optional equipment (stove, fridge,
fire extinguisher) is stored in the warehouse. An option like a fire
extinguisher can be installed on any type of vehicle.
A customer may trade in only one vehicle toward the purchase of a new
vehicle. The trade in vehicle may be sold later to another customer,
who later trades it in on another Jelly Stone Travel Vehicle. The
same vehicle over time can be sold several times.
Draw the ERD and list the tables and attributes. Then convert the ERD
into tables in 3NF. Underline the primary keys and list the foreign
keys and which tables they reference.
