1 / 28

Cars Guide

Where dream cars are found. Cars Guide. Peter Ebeid. When you go to buy a car, you can jump in your family’s car and go to the nearest car dealership. However, like many other shopping exercises, it can be done online, and carsguide.com.au is where to do it!

keefe-levine
Download Presentation

Cars Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Where dream cars are found Cars Guide Peter Ebeid

  2. When you go to buy a car, you can jump in your family’s car and go to the nearest car dealership. However, like many other shopping exercises, it can be done online, and carsguide.com.au is where to do it! Cars Guide is an online database exhibiting cars for sale, both old and new. The site allow you to browse through numerous car photos and read car specifications. If you want to sell your car, posting its profile on the website raises its profile considerably. Cars Guide (Not a street directory)

  3. Search Engine

  4. Search Engine

  5. Vehicle Profile

  6. Entity Relationship Diagram

  7. Layout of of database (ERD)

  8. Relationships between Tables

  9. One to many relationship (1:m) occurs when one record in a table is related to multiple records in another table. • Example: a car model has many variants, while a variant is only specific to one model. • Hence, a model table has a 1 to many relationship with the variant table. One-to-Many Relationship 1:m

  10. Example (con’t) Entity Relation Diagram: One-to-Many Relationship 1:m Variant Model VariantID ModelID* Series ManifactureYear BodyStyle EngineCapacity Transmission ModelID Make Model 1 m

  11. Example (con’t) Tables: One-to-Many Relationship 1:m Model (carsguide_Model) modelid | make | model ---------+---------+----------- 1 | Holden | Commodore 2 | Holden | Barina 3 | Toyota | Camry 4 | Toyota | Corolla 5 | Hyundai | Elantra (5 rows) 1 Variant (carsguide_Variant) variantid | modelid | series | manifactureyear | bodystyle | enginecapacity | transmission -----------+---------+-----------------+-----------------+-----------+----------------+-------------- 1 | 1 | SS VXII | 2002 | Sedan | 5.7 | Automatic 2 | 2 | Swing SB | 1995 | Hatchback | 1.4 | Manual 3 | 3 | Sportivo MCV36R | 2005 | Sedan | 3.0 | Automatic 4 | 1 | Executive VT | 1998 | Sedan | 5.0 | Automatic 5 | 4 | Ascent ZZE122R | 2003 | Sedan | 1.8 | Manual 6 | 5 | FX | 2003 | Hatchback | 2.0 | Automatic (6 rows) m

  12. Many to many relationship (1:m) occurs when one record in a table a is related to multiple records in another table b; at the same time one record in table b is related to many tables in table a. • Example: A potential buyer has an inquiry about a number of vehicles. Also, One vehicle can have many inquiries about it. • Hence, a buyer table has a many to many (indirect) relationship with the vehicle table. Many-to-Many Relationship m:m

  13. Example (con’t) Entity Relation Diagram: Many-to-Many Relationship m:m Vehicle Buyer Inquiry VehicleID VariantID* SellerID* Price RegistrationNumber RegistrationEndDate Colour Milage VIN BuyerID BuyerName BuyerAddress BuyerPhoneNumber BuyerEmail InquiryID BuyerID VehicleID EnquiryMessage m 1 m 1 Associative Entity m:m • Many-to-many relationships in the second normal form and onwards have associative entities (such as Inquiry) to preserve the relationship between two entities while preventing the replication of data.

  14. Example (con’t) Tables: Many-to-Many Relationship m:m Buyer (carsguide_Buyer) buyerid | buyername | buyeraddress | buyerphonenumber | buyeremail ---------+----------------+-------------------------------------+------------------+----------------------- 1 | Ted Richards | 155 Owens Dr, Kensington, NSW, 2173 | 0413258874 | rich_ted@yehoo.com 2 | Maria Szwechki | 6 Handon St, Villagewood, QLD, 4015 | 0498783215 | maria21@zoolander.com 3 | Lianne Edwards | 28 Kidman Hmy, Dalwich, NSW, 2358 | 0468457985 | ledwards@semis.com (3 rows) 1 Inquiry (carsguide_Inquiry) inquiryid | buyerid | vehicleid | enquirymessage -----------+---------+-----------+------------------------------- 1 | 2 | 7 | What is the VIN Number? 2 | 2 | 2 | Is there any rust on the body? 3 | 3 | 7 | Is Nobel white like green? (1 row) m m Vehicle (carsguide_Vehicle) vehicleid | variantid | sellerid | price | registrationnumber | registrationenddate | colour | milage | vin -----------+-----------+----------+-------+--------------------+---------------------+-------------+--------+------------------- 1 | 1 | 1 | 19999 | RJU102 | 2008-04-05 | Green Mica | 140000 | 6H8VRN35KX9582366 2 | 2 | 1 | 5999 | 667GZN | 2008-03-19 | WHITE | 172019 | W0L000078S4198834 3 | 3 | 5 | 22500 | 607IMZ | 2007-07-03 | Gold | 41133 | 6T153XK360X305463 4 | 1 | 4 | 18990 | AZQ36H | 2008-10-02 | White | 110077 | 6H8VXK69F2L891546 5 | 4 | 2 | 12500 | YJN691 | 2007-12-03 | Champagne | 96015 | 6H8VTK69MWL366676 6 | 5 | 5 | 15990 | SKK376 | 2007-09-05 | Silver | 61373 | AHT53ZEC206504277 7 | 6 | 3 | 12600 | UUL277 | 2008-05-03 | Nobel White | 30100 | (7 rows) 1

  15. Queries in SQL

  16. A Select statement allows the user to view a number of columns in a table or multiple tables with specific conditions. Hence, the output of the query depends on the columns chosen (FROM) and the condition (WHERE). Example: the user wants to choose a seller who has NSW contact number, hence they choose a telephone number starting with ‘02’. Simple Select Query SELECT SellerName, SellerAddress, SellerPhoneNumber, SellerEmail FROM carsguide_Seller WHERE SellerPhoneNumber LIKE ‘02%’; Result sellername | selleraddress | sellerphonenumber | selleremail ----------------+------------------------------------------+-------------------+------------------ Michael Heige | 3/17-28 Harmoney Dr, Mortdale, NSW, 2165 | 0295785612 | mh1955@semis.com Omar El-Ameer | 259 Amnandan St, Alexanderia, NSW, 2050 | 0297758933 | omar@yehoo.com (2 rows)

  17. Natural Join • A natural join allows for tables to join from common column, so that the primary key of the first table matches the foreign key of the other. • Example: if a user wanted to view all the vehicle models (not the individual vehicle) for sale, they would select the vehicle model and variant names from the model and variant tables, however, the tables must be joined first by their common key in order to have the right match up between ,model and variant. SELECT * FROM carsguide_Model NATURAL JOIN carsguide_Variant WHERE lower(BodyStyle) = ‘sedan’; Result modelid | make | model | variantid | series | manifactureyear | bodystyle | enginecapacity | transmission ---------+--------+-----------+-----------+-----------------+-----------------+-----------+----------------+-------------- 1 | Holden | Commodore | 4 | Executive VT | 1998 | Sedan | 5.0 | Automatic 1 | Holden | Commodore | 1 | SS VXII | 2002 | Sedan | 5.7 | Automatic 3 | Toyota | Camry | 3 | Sportivo MCV36R | 2005 | Sedan | 3.0 | Automatic 4 | Toyota | Corolla | 5 | Ascent ZZE122R | 2003 | Sedan | 1.8 | Manual (4 rows)

  18. A cross join is also used to join tables together, except the user decides how the tables will be joined by equating similar columns. Example: the user want to find all common standard features between a ‘Toyota Camry’ and a ‘Holden Commodore’. Cross Join SELECT DISTINCT carsguide_Features.FeatureID, carsguide_Features.FeatureDescription FROM carsguide_Model CamryModel, carsguide_Model CommodoreModel, carsguide_Variant CamryVariant, carsguide_variant CommodoreVariant, carsguide_StandardFeatures CamrySF, carsguide_StandardFeatures CommodoreSF, carsguide_Features WHERE CamryModel.Make = 'Toyota' AND CamryModel.Model ='Camry' AND CommodoreModel.Make = 'Holden' AND CommodoreModel.Model = 'Commodore' AND CamryVariant.ModelID = CamryModel.ModelID AND CommodoreVariant.ModelID = CommodoreModel.ModelID AND CamrySF.VariantID = CamryVariant.VariantID AND CommodoreSF.VariantID = CommodoreVariant.VariantID AND carsguide_Features.FeatureID = CamrySF.FeatureID AND carsguide_Features.FeatureID = CommodoreSF.FeatureID; Result featureid | featuredescription -----------+-------------------- 1 | Automatic windows 2 | Power Steering 4 | CD Player 5 | Alloy Wheels 8 | Air COnditioning 9 | Radio (6 rows)

  19. A group by in a table allows records in a table to be collapsed into one row based on a column with a common variable. For example, if the user wanted to find the number of extra features for each cars on sale, they would need to list the all the features in the extra features table and the number of features as an extra column. Group By SELECT VehicleID, count(FeatureID) FROM carsguide_Vehicle NATURAL JOIN carsguide_ExtraFeatures GROUP BY VehicleID ORDER BY VehicleID; Result vehicleid | count -----------+------- 2 | 1 3 | 2 4 | 2 6 | 1 7 | 1 (5 rows)

  20. When using aggregate functions in a query, having acts like a ‘where’ in determining the condition used. Example: the user wants to list all the sellers who are selling two or more vehicles. Having SELECT SellerName, count(carsguide_Vehicle.SellerID) AS NumberOfVehicles FROM carsguide_Seller NATURAL JOIN carsguide_Vehicle GROUP BY carsguide_Vehicle.SellerID, SellerName HAVING count(carsguide_Vehicle.SellerID) >= 2; Result sellername | numberofvehicles ---------------+------------------ Minah Jubakee | 2 Don Adams | 2 (2 rows)

  21. A sub query is a query that is used inside another query, usually to find a singular result which is used in the outer query. Example: a user want to find all vehicles with less milage than the average milage for all vehicle on sale. Sub Query SELECT VehicleID, Milage FROM carsguide_Vehicle WHERE milage < (SELECT AVG(Milage)FROM carsguide_Vehicle); Result vehicleid | milage -----------+-------- 3 | 41133 6 | 61373 7 | 30100 (3 rows)

  22. A self join is a form of cross join where a table is joined to its self as a result of the need to find two different rows in the table with matching elements. Example: A buyer wants to find out who else is selling car model as them, for how much and how much milage it has. The self joint is for the seller and the vehicle tables. Self Join SELECT SellerB.SellerName, VehicleB.Milage, VehicleB.Price FROM carsguide_Seller SellerA, carsguide_Seller SellerB, carsguide_Vehicle VehicleA, carsguide_Vehicle VehicleB WHERE VehicleA.SellerID = SellerA.SellerID AND VehicleB.SellerID = SellerB.SellerID AND SellerA.SellerName = 'Don Adams' AND VehicleB.VariantID = VehicleA.VariantID AND SellerA.SellerID <> SellerB.SellerID; Result sellername | milage | price ----------------+--------+------- Omar El-Ameer | 110077 | 18990 (1 row)

  23. Check statements prevent the database user from entering data which can potentially corrupt the database or data that is not logical for its field, such as text for a numerical field. Example: The developer wants to ensure that the user can only enters manual, automatic or semi-auto for vehicle transmission, hence the following constrain: If the user attempts to enter the following they will get an error: CHECK STATEMENTS CONSTRAINT carsguide_Vehicleodel_Trans CHECK (lower(Transmission) IN ('manual','automatic','semi-auto')) INSERT INTO carsguide_Variant VALUES(7, ‘FX’, 2006, ‘Hatchback’, 2.0, ‘Hatchback’);

  24. Action statements are also a measure used to prevent the corruption of data when there is a change or a deletion of one of the records in one table. These actions correct the relating data in the other associated tables. Example: A user decides to delete a vehicle Model from the database; as a consequence, the variants of that model will also be deleted. Action Statements CONSTRAINT carsguide_Variant_FK_Mo FOREIGN KEY(ModelID) REFERENCES carsguide_Model ON DELETE CASCADE ON UPDATE CASCADE,

  25. Example con’t Actoin Statements Model (carsguide_Model) modelid | make | model ---------+---------+----------- 1 | Holden | Commodore 2 | Holden | Barina 3 | Toyota | Camry 4 | Toyota | Corolla 5 | Hyundai | Elantra (5 rows) If this is deleted Variant (carsguide_Variant) These will automatically be deleted variantid | modelid | series | manifactureyear | bodystyle | enginecapacity | transmission -----------+---------+-----------------+-----------------+-----------+----------------+-------------- 1 | 1 | SS VXII | 2002 | Sedan | 5.7 | Automatic 2 | 2 | Swing SB | 1995 | Hatchback | 1.4 | Manual 3 | 3 | Sportivo MCV36R | 2005 | Sedan | 3.0 | Automatic 4 | 1 | Executive VT | 1998 | Sedan | 5.0 | Automatic 5 | 4 | Ascent ZZE122R | 2003 | Sedan | 1.8 | Manual 6 | 5 | FX | 2003 | Hatchback | 2.0 | Automatic (6 rows)

  26. Example: If feature is deleted from the features list, the reference to this feature will also be deleted from standards features and the extra features tables, hence: Action Statements Features (carsguide_Features) featureid | featuredescription -----------+-------------------- 1 | Automatic windows 2 | Power Steering 3 | Leather Interior 4 | CD Player 5 | Alloy Wheels 6 | Airbags 7 | Power Mirrors 8 | Air Conditioning 9 | Radio 10 | Power Windows 11 | Cruise Control 12 | Alarm 13 | Anti-Theft Device 14 | Tinted Windows 15 | ABS 16 | Central Locking 17 | Subwoofer 18 | Sport Suspension (18 rows) If this is deleted Standard Features (carsguide_SandardFeatures) vehicleid | featureid -----------+----------- 2 | 14 3 | 17 3 | 18 4 | 12 4 | 13 6 | 17 7 | 15 (7 rows) This will automatically be deleted

  27. Views allow the user to view a result of a statement that is used frequently without having to write that statement every time. Unlike the tables, the view only exists as long as the database is loaded. Example, the user wants to view the full specifications of a vehicles on sale. Using Views CREATE VIEW carsguide_Search_V AS SELECT Make, Model, Series, ManifactureYear, BodyStyle, EngineCapacity, Transmission FROM carsguide_Model NATURAL JOIN carsguide_Variant; Search Result (carsguide_Search_V) make | model | series | manifactureyear | bodystyle | enginecapacity | transmission ---------+-----------+-----------------+-----------------+-----------+----------------+-------------- Holden | Commodore | SS VXII | 2002 | Sedan | 5.7 | Automatic Holden | Barina | Swing SB | 1995 | Hatchback | 1.4 | Manual Toyota | Camry | Sportivo MCV36R | 2005 | Sedan | 3.0 | Automatic Holden | Commodore | Executive VT | 1998 | Sedan | 5.0 | Automatic Toyota | Corolla | Ascent ZZE122R | 2003 | Sedan | 1.8 | Manual Hyundai | Elantra | FX | 2003 | Hatchback | 2.0 | Automatic (6 rows)

  28. Fancy a Lamborghini Murcielago? Buy it for Just $685,565 from carsguide.com.au

More Related