Transcript Cars Guide

Where dream cars are found
CARS GUIDE
Peter Ebeid
CARS GUIDE




(NOT A STREET DIRECTORY)
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.
SEARCH ENGINE
SEARCH ENGINE
VEHICLE PROFILE
ENTITY RELATIONSHIP DIAGRAM
LAYOUT OF OF DATABASE (ERD)
RELATIONSHIPS BETWEEN TABLES
ONE-TO-MANY RELATIONSHIP
1:M

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

Example (con’t)

Entity Relation Diagram:
Variant
VariantID
ModelID*
Series
ManifactureYear
BodyStyle
EngineCapacity
Transmission
1:M
Model
m
1
ModelID
Make
Model
ONE-TO-MANY RELATIONSHIP

1:M
Example (con’t)
Model
(carsguide_Model)

Tables:
1
modelid | make
|
model
---------+---------+----------1 | Holden | Commodore
2 | Holden | Barina
3 | Toyota | Camry
4 | Toyota | Corolla
5 | Hyundai | Elantra
(5 rows)
Variant
(carsguide_Variant)
m
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)
MANY-TO-MANY RELATIONSHIP
M:M

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


Example (con’t)
Entity Relation Diagram:
Buyer
BuyerID
BuyerName
BuyerAddress
BuyerPhoneNumber
BuyerEmail
Vehicle
Inquiry
1
m
M:M
InquiryID
BuyerID
VehicleID
EnquiryMessage
m
1
VehicleID
VariantID*
SellerID*
Price
RegistrationNumber
RegistrationEndDate
Colour
Milage
VIN
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.
MANY-TO-MANY RELATIONSHIP

Example (con’t)

Tables:
1
M:M
Buyer
(carsguide_Buyer)
buyerid |
buyername
|
buyeraddress
| buyerphonenumber |
buyeremail
---------+----------------+-------------------------------------+------------------+----------------------1 | Ted Richards
| 155 Owens Dr, Kensington, NSW, 2173 | 0413258874
| [email protected]
2 | Maria Szwechki | 6 Handon St, Villagewood, QLD, 4015 | 0498783215
| [email protected]
3 | Lianne Edwards | 28 Kidman Hmy, Dalwich, NSW, 2358
| 0468457985
| [email protected]
(3 rows)
Inquiry
(carsguide_Inquiry)
m
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
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
QUERIES IN SQL
SIMPLE SELECT QUERY

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’.
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
| [email protected]
Omar El-Ameer | 259 Amnandan St, Alexanderia, NSW, 2050 | 0297758933
| [email protected]
(2 rows)
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)
CROSS JOIN


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’.
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)
GROUP BY

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.
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)
HAVING

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.
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)
SUB QUERY

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.
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)
SELF JOIN

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.
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)
CHECK STATEMENTS

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:
CONSTRAINT

carsguide_Vehicleodel_Trans
CHECK (lower(Transmission) IN ('manual','automatic','semi-auto'))
If the user attempts to enter the following they will get an error:
INSERT INTO carsguide_Variant VALUES(7, ‘FX’, 2006, ‘Hatchback’, 2.0, ‘Hatchback’);
ACTION STATEMENTS

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.
CONSTRAINT
carsguide_Variant_FK_Mo
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(ModelID)
REFERENCES carsguide_Model
ACTOIN STATEMENTS

Example con’t
Model
(carsguide_Model)
If this is deleted
These will
automatically be
deleted
modelid | make
|
model
---------+---------+----------1 | Holden | Commodore
2 | Holden | Barina
3 | Toyota | Camry
4 | Toyota | Corolla
5 | Hyundai | Elantra
(5 rows)
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)
ACTION STATEMENTS

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:
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
This will
automatically be
deleted
Standard Features
(carsguide_SandardFeatures)
vehicleid | featureid
-----------+----------2 |
14
3 |
17
3 |
18
4 |
12
4 |
13
6 |
17
7 |
15
(7 rows)
USING VIEWS

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.
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)
FANCY A LAMBORGHINI MURCIELAGO?
Buy it for Just $685,565 from carsguide.com.au