Question

Basics AutoCare is a small auto service business with two locations in a Midwest (U.S.) city. The business is based on providing routine maintenance at fixed cost to car owners. The owners have decided to computerize their operations to allow them to better track their business. Data to be gathered for each service visit includes service ID, service item, service item cost, vehicle owner, owner telephone number, vehicle make and model, vehicle year, and date of service.

Assignments

1. Create an ERD for the auto service system.
2. Create 3NF table designs for the system.
3. For each of the entities identified, design tables and identify the possible candidate keys, the primary key, a probable foreign key, and potential secondary keys.
4. Use sample data to populate the fields for three records.

Answer 1

ERD for the auto service system

• Attributes which are underlined are primary keys
• Service_cost is a derived attribute as it is derived by mutliplying item_cost and item_Qty_Used atrributes

3NF table Design showing Primary keys as bold and underlined while foreign keys are refered as (FK)

Table Design using SQL DDL statements using MS-SQL server Express 2012 Free Edition

Note

• The question did not specify which RDBMS tool to create tables. So I created using MS SQL Server SSMS tool  
• Please create tables at your side using the below DDL statements and in the order i have posted below
• Please insert at least 3 records as per the below DDL query order
• The tables in order of creation and data insertion as below
  
  • VehicleOwner
  • Vehicle
  • Item
  • Service
  • Service_Item
• Possible candidate key for VehicleOwner is Phone_Num
• VIN of the vehicle is the natural surrogate key
• Model can also be candidate key when a make and model togher needed to be defined as composite primary key instead of VIN
• Probable foreing keys mentioned in the diagram
• There is no potential secondary keys
• Create your own Database like CarServiceDB