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

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/default.cfm?fuseaction=usedcars.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 – 1:M This 1:M relationship shows that one Car Style has one or many cars with that style  CarBrandID 1 & 2 show this association CarStyle Primary Key Foreign Key* Car

6. : Many to Many Relationship – M:M This M:M 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 Accessory Car

7. : Simple Query of a Single Table • List all cars manufactured by Honda select * from carstyle where carbrand = ‘Honda’ order by carbrandid; • Show all Accessory Names starting with ‘G’ select accessoryname, accessoryprice from accessory where accessoryname like ‘G%’;

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’;

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’;

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;

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(*) > 1;

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 < 2000 and kilometres <= (selectmin(kilometres) from car where caryear < 2000);

13. : Self-Join • List the CarID of cars that have the same colour ‘Black’ select c1.colour, c2.carid from car c1, car c2where c1.carid = ‘U38523’ and c1.colour = c2.colour;

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 > 1900) AND (CarYear <= 2007)), • CONSTRAINT Car_ListPrice CHECK (ListPrice > 0), • CONSTRAINT Car_InternetPrice CHECK ((InternetPrice > 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;

18. : Querying a View • Show all 4WDs that are not Toyota from the created view ‘specs’ select * from specs where brand <>‘Toyota’;