1 / 13

Overview of Database

THE DATABASE OF MSY TECHNOLOGY PTY. LTD PRODUCT Ronald Diningrat High Distinction Assignment Autumn 2007. Overview of Database. This database is designed for MSY Product Stock. http://www.msy.com.au/Parts/PARTS.pdf Has 9 store around Australia. Selling computer parts

karan
Download Presentation

Overview of 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. THE DATABASE OF MSY TECHNOLOGY PTY. LTD PRODUCTRonaldDiningrat High Distinction AssignmentAutumn 2007

  2. Overview of Database • This database is designed for MSY Product Stock. • http://www.msy.com.au/Parts/PARTS.pdf • Has 9 store around Australia. • Selling computer parts • The database is used to organise the stock from all over the store • Store, Stock, Product and Product category.

  3. ERD Stock BranchName* ProductNo* Available Product ProductNo BrandName BrandType ProductCategoryNo* Price Store BranchName Street Suburb State Postcode Tel Fax WorkingHours ProductCategory ProductCategoryNo Category Subcategory

  4. 1:m Relationship

  5. m:m Relationship

  6. A Simple query of a single table • List the table of product categroy

  7. Natural join • List branch name, product no. and brand type from table stock and product

  8. Cross product

  9. Group by • List the branch name and stock available in each store.

  10. Sub Query • List the brand name and brand type of product that has price cheaper that ML-2010

  11. Check Constraint CONSTRAINT State CHECK (State IN ( 'NSW', 'VIC', 'QLD', 'ACT', 'WA', 'SA', 'NT', 'TAS' ))

  12. SQL Syntax for Actions • CONSTRAINT FKProduct FOREIGN KEY (ProductCategoryNo) REFERENCES PRODUCTCATEGORY • ON DELETE CASCADE • ON UPDATE CASCADE • CONSTRAINT FKStock2 FOREIGN KEY (ProductNo) REFERENCES PRODUCT • ON DELETE CASCADE • ON UPDATE CASCADE

  13. Create View • Create a table contains brand and price of ASUS

More Related