FoodDirect'Com'Au - PowerPoint PPT Presentation

Title: FoodDirect'Com'Au


1
FoodDirect.Com.Au
BY SHIVAM SINHA
2
This database is inspired by the Food Direct
Website www.FoodDirect.com.au It allows users
to do their shopping online (Cereal, Apples,
Chocolate etc) by Categories and Companies. Each
Customer has a Mobile Number which Uniquely
identifies Them as well as a Delivery I.D Each
product that the Customer purchases has a product
ID as well as a purchase ID which is then
delivered to the customer using the Delivery ID.
3
FoodDirect.Com.Au - ERD
4
FoodDirect.Com.Au
One To Many Relationship
Primary Key
company_id companyname ----------------------
--------- 1 Kelloggs
2 Sanitarium 3
Uncle Tobys 4 1824

• One Company can have many Products

Foreign Key
product_id productname productprice
productquantity company_id
category_id ------------------------------------
------------------------------------------------
------------------------ 1 All
Bran 5.94 30
1 2
2 Coco Pops 3.03
41
1 2 3 Corn Flakes
6.66 43
1 2
4 Froot Loops
5.90 55
1 2 5 Just
Right 6.05
34 1
2 6 Corn Flakes
3.45 76
2 2 7
Weet bix 4.05
32 2
2 8 Oats
3.25 63
3 2
5
FoodDirect.Com.Au
Many To Many Relationship
delivery_id address
postcode --------------------------
---------------------------------------
1 31 Johnny Street, Lidcome
2454 2 59 Michael Jackson
Street, Town Hall 2000 3 20
Raymond Lister Lane, Ulitmo 2001
4 32 Knox Street, Lidcome 2454
One Delivery can be for many products A Product
can have many deliveries
product_id productname productprice
productquantity company_id
category_id ------------------------------------
------------------------------------------------
----------------- 1 All Bran
5.94 30
1 2 2 Coco Pops
3.03
41 1 2
3 Corn Flakes 6.66
43 1
2 4 Froot Loops
5.90 55
1 2
purchase_id mobilenumber product_id
delivery_id ------------------------------------
------------------------- 1
401256934 12 1
2 401256934 1
1 3 401256934
5 1
6
FoodDirect.Com.Au
Query On a Single Entity/ table
Select all customers who have a Visa Credit Card
select from customer where creditcardtype
'Visa'
mobilenumber delivery_id firstname
lastname email
creditcardtype creditcardexpiry ----------------
----------------------------------------------
------------------------------------------------
------------- 403256490 2
John Fox
Johnny_at_yahoo.com Visa 13/06/2009
402256390 3 Kuppal
Palaniappan Kuppa_at_hotmail.com Visa
14/06/2011 403233590 10
Azmat Mazaharulla azzy_at_yahoo.com
Visa 10/06/2010 465276690
15 Raymond Lister
Ray_at_yahoo.com Visa 16/06/2008
7
FoodDirect.Com.Au
Query Using a Natural Join
Show the List of Products which have been
purchases
select purchase_id,productname,companyname from
purchase natural join product natural join
company
purchase_id productname companyname ------
----------------------------------
1 White Helgas
2 All Bran Kelloggs
3 Just Right Kelloggs
8
FoodDirect.Com.Au
Query Using A Cross Product
select purchase_id,productname,companyname from
purchase,product, company where
product.product_idpurchase.product_id and
company.company_idproduct.company_id
purchase_id productname companyname ---------
------------------------------------
1 White Helgas
2 All Bran Kelloggs
3 Just Right Kelloggs
9
FoodDirect.Com.Au
Group By Using Having
show the number of Products that have been
Purchased
INSERT INTO Purchase VALUES ('4','0403256490','5'
,1)
select productname, count() as Quantity from
purchase,product, company where
product.product_idpurchase.product_id and
company.company_idproduct.company_id group by
productname having count() gt 1
productname quantity -------------------------
-- All Bran 1 Just Right
2 White 1 (3 rows)
10
FoodDirect.Com.Au
Query Using Sub Query
shsinhagt select productname, productprice from
product where productprice lt all shsinha-gt
(select productprice from product natural join
category shsinha(gt where category_id '7')
productname productprice ---------------------
------ Apples 0.45 (1 row)
11
FoodDirect.Com.Au
Data Integrity CHECK Constraints
CREATE TABLE Delivery ( Delivery_ID Integer
NOT NULL UNIQUE, Address Varchar NOT
NULL, PostCode Char(4) NOT NULL, CONSTRAINT
DeliveryPk PRIMARY KEY (Delivery_ID), CONSTRAINT
CustomerPostCode CHECK (PostCode gt
2000) ) CONSTRAINT CustomerCreditType CHECK
(CreditCardType IN ('American Express','Visa','Ma
sterCard')) ) CONSTRAINT ProductQuantity
CHECK ((ProductQuantity gt 30) AND
(ProductQuantitylt 150))
12
FoodDirect.Com.Au
SQL Syntax for Actions
( MobileNumber Integer NOT NULL
UNIQUE, Delivery_ID Integer NOT
NULL, FirstName Varchar NOT NULL, LastName
Varchar NOT NULL, Email Varchar NOT
NULL, CreditCardType Varchar NOT
NULL, CreditCardExpiry Varchar NOT
NULL, CONSTRAINT CustomerPk PRIMARY KEY
(MobileNumber), CONSTRAINT CustomerDelFk FOREIGN
KEY (Delivery_ID) REFERENCES Delivery ON DELETE
CASCADE ON UPDATE CASCADE, CONSTRAINT
CustomerCreditType CHECK (CreditCardType IN
('American Express','Visa','MasterCard')) )
CONSTRAINT ProductPk PRIMARY KEY
(Product_ID), CONSTRAINT ProductFk FOREIGN KEY
(Category_ID) REFERENCES Category ON DELETE
CASCADE ON UPDATE CASCADE, CONSTRAINT
ProductQuantity CHECK ((ProductQuantity gt 30)
AND (ProductQuantitylt 150)) )
13
FoodDirect.Com.Au
Creating A View
shsinhagt CREATE VIEW Frozen3 AS SELECT
productname, productprice shsinha-gt from product
natural join category shsinha-gt where
category_id'8' CREATE VIEW shsinhagt select
from Frozen3 productname
productprice -----------------------------------
Chicken Fillets 7.45 Chicken
Fillets 6.10 Chicken Fillets
4.05 Chocolate Icecream 4.65
Chocolate Icecream 4.54 Pies
6.30 (6 rows)
14
FoodDirect.Com.Au
THE END