Car Club Database

1
Car Club Database

• Clay King and Jon Moberley

2
Purpose

• Store info for a car club
• Users
• Cars
• Upgrades
• Dyno graphs
• Timeslips
• Work logs
• Events
• Photos

3
Users

• Account required to post information
• Account not required to view
• Can be administrators
• Must provide a valid e-mail address to register

4
Cars

• Defined as VIN, make, model, year, date purchased
• Each belongs to one user
• Can have the following
• Upgrades
• Time slips (drag race statistics)
• Dyno graphs (performance testing statistics)
• Work logs (what users did and when)
• Photos

5
Timeslips

• Statistics of drag races
• Quarter mile time in seconds
• Sixty foot time in seconds
• Trap speed in MPH
• Also stored are date and time, track name
• Photo of time slip is required
• Admin approval required before becoming publicly
  viewable and entering rankings

6
Dyno Graphs

• Statistics of car performance
• Peak horsepower, and at what engine RPM
• Peak torque, and at what RPM
• Also stored are date and time, dyno location
• Photo of dyno graph printout is required
• Admin approval required before becoming publicly
  viewable and entering rankings

7
Events

• Defined as date and time, venue, description
• Each belongs to one user
• Must be administrator approved before appearing
  to the public

8
Photos

• Can be associated with
• Cars
• Upgrades
• Timeslips (required)
• Dyno graphs (required)
• Work logs
• photos of work performed

9
DBMS Choice

• Low cost (aka 0.00)
• Easily integrated into web apps
• Very scalable, supports many storage engines
• Runs on almost any platform
• Great support from user community

10
Contextual DFD
11
Entity-Relationship Model
12
3NF Relational Model
13
3NF Relational Model (contd)
14
Sample Queries HP Ranking

• Top 10 horsepowerSELECT users.fullname,
  cars.year, cars.make, cars.model,
  dyno_graphs.peak_hp, dyno_graphs.peak_hp_rpm FROM
  dyno_graphs INNER JOIN cars ON dyno_graphs.vin
  cars.vin INNER JOIN users ON cars.username
  users.username WHERE dyno_graphs.admin_approved
  'approved' ORDER BY dyno_graphs.peak_hp DESC
  LIMIT 10

Rafals Ferrari 360 ?
15
Sample Queries ¼ Mile Ranking

• SELECT users.fullname, cars.year, cars.make,
  cars.model, timeslips.quarter_mile_time,
  timeslips.trap_speed FROM timeslips INNER JOIN
  cars ON timeslips.vin cars.vin INNER JOIN users
  ON cars.username users.username WHERE
  timeslips.admin_approved 'approved' ORDER BY
  timeslips.quarter_mile_time ASC LIMIT 10

16
Sample Queries Timeline (Query)

• SELECT vin, date_time, 'timeslip' AS event_type,
  CONCAT("Quarter mile time ", quarter_mile_time,
  " _at_ ", trap_speed, "MPH") AS event_detail FROM
  timeslips WHERE admin_approved 'approved'
• UNION
• SELECT vin, date_time, 'dyno' AS event_type,
  CONCAT("Peak HP ", peak_hp, " _at_ ", peak_hp_rpm,
  "RPM, Peak TQ ", peak_tq, " _at_ ", peak_tq_rpm,
  "RPM") AS event_detail FROM dyno_graphs WHERE
  admin_approved 'approved'
• UNION
• SELECT vin, date_purchased AS date_time,
  'purchased' AS event_type, 'Car Purchased' AS
  event_detail FROM cars
• UNION
• SELECT vin, date_added AS date_time, 'upgrade' AS
  event_type, CONCAT('Upgraded added ',
  part_manufacturer_name, ' ', part_type_name) AS
  event_detail FROM car_has_upgrade
• UNION
• SELECT vin, date_time, 'work performed' AS
  event_type, CONCAT(LEFT(description, 100), '...')
  AS event_detail FROM work_logs
• ORDER BY vin ASC, date_time ASC

17
Sample Queries Timeline (Results)
18
The End

• Questions?