1 / 13

Overview of MSY Technology Database for Stock Management

This document provides an overview of the MSY Technology database, designed to manage product stock across nine stores in Australia. It supports the organization of computer parts inventory, detailing relationships between stock, products, branches, and categories. Key functionalities include querying product categories, generating reports on available stocks, and leveraging SQL for actions like foreign key constraints and views for specific brand and price information. This system enhances efficiency in stock management and improves customer service.

karan
Télécharger la présentation

Overview of MSY Technology Database for Stock Management

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