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@yahoo.com | Visa | 13/06/2009 402256390 | 3 | Kuppal | Palaniappan | Kuppa@hotmail.com | Visa | 14/06/2011 403233590 | 10 | Azmat | Mazaharulla | azzy@yahoo.com | Visa | 10/06/2010 465276690 | 15 | Raymond | Lister | Ray@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_id=purchase.product_id and company.company_id=product.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_id=purchase.product_id and company.company_id=product.company_id group by productname having count(*) >= 1; productname | quantity -----------------+---------- All Bran | 1 Just Right | 2 White | 1 (3 rows)

10. FoodDirect.Com.Au Query Using Sub Query shsinha=> select productname, productprice from product where productprice <= all shsinha-> (select productprice from product natural join category shsinha(> 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 >= 2000) ); … CONSTRAINT CustomerCreditType CHECK (CreditCardType IN ('American Express','Visa','MasterCard')) ); … CONSTRAINT ProductQuantity CHECK ((ProductQuantity >= 30) AND (ProductQuantity<= 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 >= 30) AND (ProductQuantity<= 150)) );

13. FoodDirect.Com.Au Creating A View shsinha=> CREATE VIEW Frozen3 AS SELECT productname, productprice shsinha-> from product natural join category shsinha-> where category_id='8'; CREATE VIEW shsinha=> 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