PROG2110- Assignment 2 EER Diagram Used Car Dealership Solved

Overview
Wally Los Gatos, owner of Wally’s Wonderful World of Wheels, has hired you as a consultant to
design a database management system for his chain of stores that sell used vehicles. Wally requires this system to track details about inventory, employees, sales and suppliers.
 
Wally’s daughter, Wanda, has recently started to apply her college courses in web design to help her father’s business. The company’s website allows customers to review details on vehicles for sale, but more importantly, allows them to fill out a ‘request form’. This new form will add new information to the database.
 
Primary Goals
-        Demonstrate the use of an Enhanced Entity Relationship (EER) diagram

-        Demonstrate the modelling of relations and use of normalization within a diagram



Requirements
Based on the following information, draw an EER diagram and a diagram of the set of relations in 3rd normal form. Please indicate any assumptions that you have made in your diagrams!

After an initial meeting with Wally, you have developed a list of business rules and specification to begin the design of an the EER Model. Once the EER model is completed, ‘translate’ that model into a relation diagram:



·       Customers place orders through a dealership. Wally has three dealership locations (Cambridge, Kitchener and London), and may add more in the future

·       Wally would like to track the following about customers:
Name, Address, City, Province, Postal Code, Telephone, Date of Birth, Email, Social Insurance Number (SIN)

·       A customer may place many orders

·       A customer does not always have to order through the same dealer all the time

·       Customers often require financing/loans, and thus may need to have an account

·       Customers may have one or more accounts, although they may also have no accounts

·       The following information needs to be recorded about accounts:
Balance, Last Payment Date, Last Payment Amount, Interest, Term

·       A dealership may have many customers

·       Orders are composed of one or more vehicles from inventory

·       The following information about each order needs to be recorded:
Order Date, Final Sale Price, Credit Authorization Status

·       A vehicle is a unique item and can only be sold by one dealership (although it has happened that Wally has bought and resold the same vehicle at a different dealership)

·       We wish to record the following about each vehicle:
Vehicle Identification Number (VIN), Make, Model, Year, Colour, Km’s, Photo, Purchase Price, Sticker Price

·       Wally obtains vehicles from a range of suppliers, including auctions, bank foreclosures, other dealerships and private sellers

·       Every time a vehicle is purchased by Wally, his company pays a purchase price, and sells the vehicle at a ‘sticker price’

·       The sticker price is always 35% more than the purchase price (this is how Wally makes profit!)

·       Sales team members might agree to sell the vehicle for less than the sticker price, thus the order should track the final sale price

·       Wally employs fifty-six employees

·       He would like to track the following information about employees:
Name, Address (Street, City, Province, Postal Code), Telephone, Date of Hire, Title, Salary, Age, Manager, and attributes that vary depending on the employee’s role

·       If the employee’s role is that of a mechanic, we should track their Certification Number and Years of Experience

·       If the employee’s role is that of a salesperson, we should track their commission and total Vehicles Sold

·       If the employee’s role is that of a manager, we should track which employees they manage

·       Some employees join Wally’s business as general staff, and have no special attributes to track

·       Each employee works in one and only one dealership

·       Each employee may have one or more dependents. We wish to record the name of the dependent as well as the age and relationship