1 / 16

Boange_MSY Database

Boange_MSY Database. By Sean Boange. Database Description.

march
Download Presentation

Boange_MSY Database

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. Boange_MSY Database By Sean Boange

  2. Database Description • This Database is modelled on the Computer Hardware Store MSY www.msy.com.au. At current they display the numerous products they sell in an very inefficient PDF file (As seen on the next Slide). Basically My Database functions in two ways: It stores all the related details of the products in 3-4 Main Tables and it also demonstrates the function of customers to be able to purchase these products.

  3. THE ERD

  4. 1:M categoryid | categoryname | hardwaretypeid ------------+-----------------------------+---------------- 0101 | Pentium 4 Socket 775pin M/B | 01 0102 | AMD Athlon64 754pin M/B | 01 0103 | AMD Athlon64 939pin M/B | 01 0104 | AMD AM2 940pin M/B | 01 (4 rows) Foreign Keys hardwaretypeid | hardwaretypename ----------------+-------------------- 01 | Motherboard 02 | CPU 03 | Memory 04 | Hard Disk Drive 05 | VGA Card 06 | DVD-ROM 07 | Flash Memory 08 | LCD Monitor 09 | Notebook Hard Disk (9 rows) Primary Key

  5. M:M cartid | customerid --------+------------ A100 | 1000 A200 | 1000 (2 rows) cartid | cartitemid --------+------------ A100 | 080101 A100 | 080102 A200 | 080101 A200 | 080102 (4 rows) Primary Key Foreign Keys Foreign Keys productid | productname -----------+----------------------------- 080101 | 17inch Viewsonic VA703B-8ms 080102 | 17inch LG L1719S-SF-8ms (2 rows) Primary Key

  6. Simple Query • Description:Find all customers living in NSW. • Query: • Select FirstName, LastName, State from BOANGE_MSY_Customer where state='NSW'; • Result:

  7. Natural Join • Description:Find all categories for CPU’s in the database. • Query: • SELECT * FROM boange_msy_category NATURAL JOIN boange_msy_hardwaretype Where hardwaretypename='CPU'; • Result: hardwaretypeid | categoryid | categoryname | hardwaretypename ----------------+------------+-------------------------------+------------------ 02 | 0201 | Intel Conroe (Core 2 Duo) | CPU 02 | 0202 | Intel Conroe (Core 2 Quad) | CPU 02 | 0203 | Intel Celeron D (3xx) | CPU 02 | 0204 | Intel P4 (5xx & 6xx) | CPU 02 | 0205 | Intel (D-8xx,D-9xx) Dual Core | CPU 02 | 0206 | AMD Sempron 940pin CPU | CPU 02 | 0207 | AMD Athlon64 AM2 940pin CPU | CPU 02 | 0208 | AMD 754 CPU | CPU (8 rows)

  8. Cross Product • Description:Find all categories for CPU in the database using cross product. • Query: • SELECT * FROM BOANGE_MSY_Category, BOANGE_MSY_HardwareType WHERE boange_msy_category.hardwaretypeid = boange_msy_hardwaretype.hardwaretypeid AND hardwaretypename='CPU'; • Result: hardwaretypeid | categoryid | categoryname | hardwaretypename ----------------+------------+-------------------------------+------------------ 02 | 0201 | Intel Conroe (Core 2 Duo) | CPU 02 | 0202 | Intel Conroe (Core 2 Quad) | CPU 02 | 0203 | Intel Celeron D (3xx) | CPU 02 | 0204 | Intel P4 (5xx & 6xx) | CPU 02 | 0205 | Intel (D-8xx,D-9xx) Dual Core | CPU 02 | 0206 | AMD Sempron 940pin CPU | CPU 02 | 0207 | AMD Athlon64 AM2 940pin CPU | CPU 02 | 0208 | AMD 754 CPU | CPU (8 rows)

  9. Group By • Description:Find which categories that contain more than 5 products in each of them. • Query: • SELECT CategoryName, count(*) FROM boange_msy_category, boange_msy_product WHERE boange_msy_category.categoryID = boange_msy_product.ProductCategoryID GROUPBY CategoryName HAVING count(*) > 5; • Result:

  10. Sub Query • Description:Find the highest price product. • Query: • SELECT ProductID, ProductName, ProductPrice FROM boange_msy_product WHERE productprice >= all (SELECT productprice FROM boange_msy_product); • Result:

  11. Self Join • Description:Show all Products that have the same price as ‘512M Samsung DDR400’ ($50.00). • Query: Select T1.ProductName, T1.ProductPrice FROMBOANGE_MSY_Product T1, BOANGE_MSY_Product T2 WHERE T1.ProductPrice = T2.ProductPrice AND T2.ProductName =‘512M Samsung DDR400’ AND T1.ProductName <> ‘512M Samsung DDR400’; • Result:

  12. Check Statements CONSTRAINT MSY_CartItemQty CHECK (CartItemQty > 0 ) CONSTRAINT MSY_CardType CHECK (CardType IN ('Visa','Mastercard','American Express','Diners Club')), CONSTRAINT MSY_CardExpiryDate CHECK (CardExpiryDate > 2006) CONSTRAINT MSY_CustomerPostcode CHECK (Postcode >=1000 and Postcode <= 9999) CONSTRAINT MSY_CategoryName_NotNull CHECK (CategoryName IS NOT NULL)

  13. Action Statements CONSTRAINT MSY_CategoryFK FOREIGN KEY (HardwareTypeID) REFERENCES BOANGE_MSY_HardwareType (HardwareTypeID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT MSY_CartItemFK FOREIGN KEY (CartID) REFERENCES BOANGE_MSY_Cart(CartID) ON DELETE RESTRICT ON UPDATE CASCADE,

  14. Use of View CREATE VIEW MthrBrdMem AS Select * From BOANGE_MSY_HardwareType natural join BOANGE_MSY_Category Where HardwareTypeName ='MotherBoard' OR HardwareTypeName ='Memory'; CREATE VIEW Select CategoryName, HardwareTypeName, From MthrBrdMem Where CategoryID=‘0101’ OR HardwareTypeName=‘Memory’ Order by HardwareTypeName;

  15. The End! This Presentation was Sponsored By Sean Boange Pty Ltd

More Related