Jenny Jirathammakul

1
Melbourne City Toyota

• Jenny Jirathammakul
• High Distinction Assignment
• 31061 - Database Principles
• Autumn, 2007

2
Melbourne City Toyota

• This database is based on the Melbourne City
  Toyota website http//www.melbcitytoyota.com.au/d
  efault.cfm?fuseactionusedcars.dsp_main
• Allows users to search the website for New
  Toyotas, Used Cars or Demonstration Cars.
• The Cars are sorted into Car Brands that is
  further arranged into the Car Style to make it
  easier to browse through the different
  categories.
• Extras are also included, such as Accessories
  and Parts, with New Cars if the User wished to
  look through additional things for a particular
  car.
• Enquiries by Users concerning the Cars are
  sorted by the CarID. This is to ensure that the
  Users enquiries would be specific to the
  particular car.

3
Melbourne City Toyota
4
Entity Relationship Diagram
5
One to Many Relationship 1M
This 1M relationship shows that one Car Style
has one or many cars with that style ? CarBrandID
1 2 show this association
CarStyle
CarBrandID CarBrand CarModel CarMake CarBody
1 Toyota RAV4 CV 4WD
2 Toyota Camry Sportivo Sedan
6 Honda Accord EXI 4WS Sedan
8 Lexus IS200 Sports Sedan
11 Suzuki Swift Cino Hatchback
Primary Key
Foreign Key
Car
CarID CarBrandID CarYear CarOwnType Colour Kilometres ListPrice InternetPrice Registration
N12345 1 2007 New Silver 31990
U36892 1 2006 Used Light Gold 75324 32990 27777 ULS948
U37771 1 2006 Used Light Teal 7499 35990 33777 UPZ685
N24689 2 2007 Used Black 33000
U36901 2 2004 Used White 144064 22990 21540 ULJ121
6
Many to Many Relationship MM
This MM relationship shows that many cars can
have many accessories because

• A Car can have many Accessories and

• An Accessory can be apart of many Cars

Extras
CarID AccessoryID PartsID
N12345 1 1
N12345 2 0
N24689 9 2
N24689 13 0
N24689 14 0
N54321 14 1
Accessory
AccessoryID AccessoryName AccessoryPrice
1 Safety Pack 750
2 Metallic Paint 300
9 New Camry Bootlip Spoilers 550
13 Leather Trim 1500
14 Moonroof 1650
Car
CarID CarBrandID CarYear CarOwnType Colour Kilometres ListPrice InternetPrice Registration
N12345 1 2007 New Silver 31990
U36892 1 2006 Used Light Gold 75324 32990 27777 ULS948
U37771 1 2006 Used Light Teal 7499 35990 33777 UPZ685
N24689 2 2007 New Black 33000
U36901 2 2004 Used White 144064 22990 21540 ULJ121
7
Simple Query of a Single Table

• List all cars manufactured by Honda

select from carstyle where carbrand Honda
order by carbrandid
CarBrandID CarBrand CarModel CarMake CarBody
6 Honda Accord EXI 4WS Sedan
7 Honda HRV Sport 4WD

• Show all Accessory Names starting with G

select accessoryname, accessoryprice from
accessory where accessoryname like G
AccessoryName AccessoryPrice
Genuine Toyota Park Assist 595
Genuine Toyota Protection Packs 180
Genuine Toyota Satellite Navagation System 3500
8
Query Natural Join

• Show EnquiriesID, CarID, UserName, CarOwnType
  and Question for the car U37975

select enquiriesid, carid, username, carowntype,
question
from users natural join enquiries natural join
car where carid U37975
EnquiriesID CarID UserName CarOwnType Question
3 U37975 Nicole Used Would the car get the same kind of since as a newly bought Toyota since it has only been used for a while
1 U37975 Tony Used What is the average Fuel consumption rate for this car
9
Cross-Product Join

• Show the carid, carbrand, carmodel and carmake,
  caryear of all cars that are hatchbacks

select carid, carbrand, carmodel, carmake,
caryear from car, carstyle
where car.carbrandid
carstyle.carbrandid and carbody
Hatchback
CarID CarBrand CarModel CarMake CarYear
N12345 Suzuki Swift Cino 1998
U36892 Toyota Yaris YR 2007
10
Group By

• Find out how many CarBrandIDs have registrations
  (that is, their registration is not null)

select carbrandid, count() from car
where registration is not null group by
carbrandid
CarBrandID Count
14 2
13 1
11 1
10 1
9 1
8 1
7 1
6 1
5 1
2 1
1 2
11
Group By with Having

• Display car colours that have more than one car
  having that colour

select colour, count() as total from car
group by colour
having count() gt 1
Colour Total
Silver 3
Black 3
White 4
12
Sub-Query

• List the carid, carbrand, carmodel, carmake,
  kilometres of the car with the minimum amount of
  kilometres driven that was manufactured before
  the year 2000

select carid, carbrand, carmodel, carmake,
kilometres from car natural join carstyle
where caryear lt 2000
and kilometres lt (select
min(kilometres) from car where caryear lt 2000)
CarID CarBrand CarModel CarMake Kilometres
U38412 Suzuki Swift Cino 95876
13
Self-Join

• List the CarID of cars that have the same
  colour Black

select c1.colour, c2.carid
from car c1, car c2
where c1.carid U38523
and c1.colour c2.colour
Colour CarID
Black N24689
Black U38523
Black U37947
14
Data Integrity CHECK Statements
Check Statements prevents the SQL from creating
data that does not conform to the guidelines that
have been put into place and an error would thus
occur because of a breach of the contraints

• These CHECK statements make sure that the values
  entered into the INSERT statements do not breach
  the restrictions placed upon them
• CONSTRAINT Car_Year CHECK ((CarYear gt
  1900) AND (CarYear lt 2007)),
• CONSTRAINT Car_ListPrice CHECK (ListPrice gt
  0),
• CONSTRAINT Car_InternetPrice CHECK
  ((InternetPrice gt 0) OR (InternetPrice IS NULL))
• This CHECK statements make sure that the values
  entered into the INSERT statements include only
  these types of values, that is, the only values
  that can be entered into Car Brand are only those
  types of car brands to ensure that misspellings
  do not occur
• CONSTRAINT CarStyle_Brand CHECK (CarBrand IN
  (
• 
  'Alfa Romeo','Audi','BMW','Chrysler','Citroen',
• 
  'Daewoo','Daihatsu','Ford','Holden','Honda',
• 
  'Hyundai','Isuzu','Jeep','Land Rover','Lexus',
• 
  'Mazda','Mercedes-Benz','Mitsubishi','Nissan',
• 
  'Peugeot','Proton','Renault','Saab','Smart',
• 
  'Subaru','Suzuki','Toyota','Volkswagen','Volvo'))

15
Action Statements ON DELETE RESTRICT
ON DELETE RESTRICT prevents the deletion of
data that is being used by other categories in
another table

• These Restrictions prevent the deletion of
  AccessoryIDs and PartsIDs if they are currently
  being used by other categories in another table
• CONSTRAINT ExtrasFK_aid FOREIGN KEY (AccessoryID)
  REFERENCES Accessory (AccessoryID)
• ON DELETE RESTRICT
• ON UPDATE CASCADE,
• CONSTRAINT ExtrasFK_pid FOREIGN KEY (PartsID)
  REFERENCES Parts (PartsID)
• ON DELETE RESTRICT
• ON UPDATE CASCADE

16
Action Statements ON DELETE CASCADE
ON DELETE CASCADE allows data to be deleted
from a table that will delete itself in another
table as well

• This deletion of CarID in a table will also be
  deleted in the extras table also
• CONSTRAINT ExtrasFK_cid FOREIGN KEY (CarID)
  REFERENCES Car (CarID)
• ON DELETE CASCADE
• ON UPDATE CASCADE,

17
Creating a View

• Create a view that shows the car style , year,
  kilometres, ownership type and registration of
  4WDs

create view specs (carid, year, brand, model,
make, kms, owntype, rego) as select carid,
caryear, carbrand, carmodel, carmake, kilometres,
carowntype, registration from car, carstyle where
car.carbrandid carstyle.carbrandid and carbody
4WD
select from specs
CarID Year Brand Model Make Kms OwnType Rego
N12345 2007 Toyota RAV4 CV New
U37975 2004 Ford Territory TS 47088 Used SZS937
U38601 1999 Honda HRV Sport 123000 Used PUR303
U36892 2006 Toyota RAV4 CV 75324 Used ULS948
U37771 2006 Toyota RAV4 CV 7499 Used UPZ685
18
Querying a View

• Show all 4WDs that are not Toyota from the
  created view specs

select from specs where brand ltgtToyota
CarID Year Brand Model Make Kms OwnType Rego
U37975 2004 Ford Territory TS 47088 Used SZS937
U38601 1999 Honda HRV Sport 123000 Used PUR303