TRY IT OUT 6

The Relational Automotive Group (RAG) is a growing business with 6 dealership locations in Utah. They are in need of a database to manage their vehicle inventory, sales, services, parts, employees and customer information. In the following try it out we will break down their overall needs into 3 parts and make 3 different sections of their ERD.

PART I

In this first section we are going to focus on the employees. We will begin by identifying the many to many relationships and in the final step we will break those relationships up by using a fourth table that we will name EMPLOYMENT. Read through the following scenario to get started:

Each dealership has a different location that can hire several employees. The employees can work at any of the 6 locations and fill different positions like Salesperson, Mechanic, Manager, Office Worker, etc. (over their employment history)..

Can you list the 3 major entity sets? (Do not count the associative tables at this point)

Using these 3 tables youve identified, list all the two-pair combinations that form Many to Many connections.

___________ Hires (or could hirel over time) MANY _________ ____________ Holds (or could hold over time) MANY ___________

__________ Fills (or could be fill over time) MANY _______________

___________ Can be scheduled (over time at) MANY ____________

Using the Many to Many Combinations you identified above, how do the 3 original tables youve identified come together to form a 4th Associative Table that we will call EMPLOYMENT

Besides the need to store basic information common to all employees (name, address,

etc.), RAG also wants to be able to store individual employment records based on the alignment of an employees particular position at one of the dealership locations (including the start and end dates of that position, as well as the employees pay rate noting if it this rate is considered salary or hourly, as well as an additional (optional) field to store a commission rate (%) in case the employee is eligible for commissions as well (or in lieu of a pay rate).

Draw the four entity sets (with their attributes), and use Crows Feet notation to show their relationships. All many to many relationships should be eliminated by using the associative table EMPLOYMENT.

PART II

Some employees (sales people positions) can sell vehicles to a customer. Since some salespeople may only work for a commission on a vehicle sale, its important we keep accurate sales invoices between the customer, vehicle and the employee who sold the vehicle. Each Vehicle is tracked by a unique VIN# and has other information (such as but not limited to

the year, mileage, and retail (selling) price - which is different from the price actually paid.

Can you list the 3 major entity sets? (Do not count the associative tables at this point)

Using these 3 tables youve identified, list all the two-pair combinations that form Many to Many connections.

____________ SELLS (or could sell over time) MANY________________ ____________ BUYS (or could buy over time) MANY ____________

____________ IS SOLD (or could be resold over time) TO MANY ____________ and ____________

A couple more considerations:

A salesperson may sell many vehicles, but each vehicle is sold by only one salesperson.

A customer may buy many vehicles, but each vehicle is bought by only one customer.

A salesperson writes a single invoice for each vehicle he or she sells.

A customer gets an invoice for each vehicle he or she buys.

So, using the Many to Many Combinations you identified above, how do the 3 original tables youve identified come together to form a 4th Associative Table known as INVOICE?

Draw the four entity sets (with their attributes), and use Crows Feet notation to show their relationships. All many to many relationships should be eliminated by using the associative table INVOICE.

Some employees (maintenance personnel positions) are responsible for servicing vehicles. The maintenance people conduct warranty work and perform periodic services and repairs (like oil changes, tire rotations, emission testing, and general repairs not covered under warranty). These employees create a service ticket with one or more associated service expense line entries involved with the service ticket, noting whether a particular service line item was covered under warranty or not.

While it may be clear you will need a SERVICE_TICKET table, what may not be clear is whether the SERVICE (like Oil Change) should be stored as an attribute in the SERVICE_TICKET, or as a separate entity set.

To test whether SERVICE should be stored as an attribute in the SERVICE_TICKET, ask yourself

Does the SERVICE_TICKET contain only ONE service (remember you can only store ONE value in an attribute) or could it possibly contain more than one service?

Assuming it could possibly contain more than one service (like an Oil Change and a Tire Rotation), then we MUST create a new Entity Set to hold these multiple values associated with the SERVICE_TICKET. What table would you propose creating and what attributes would this table contain? Hint: (think of your Home Depot or Walmart receipt. Its one Ticket (receipt) with one or more individual line items for the things you purchase/

The service ticket has one or more service line item details

Now that we have a service ticket table and a service line item table, lets think about the parts.

A vehicle that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a

fuel injector nozzle does not require providing new parts). However, things like Tires,

Hoses, and Belts are considered parts. Could each service line item for example an oil change require many parts?

10) Draw the four entity sets (with their attributes), and use Crows Feet notation to show these relationships. To help you get started ServiceTicket and ServiceLineItem are two of the entities, you will need to add two additional tables to show the parts relationship to the ServiceLineItem. Keep in mind that many parts could apply to each Service line item and service line items could reference many parts. How do we resolve this many to many relationship?