it is actually about Computer Technology

User Generated

Nqrywhzznu

Computer Science

Description

See the attachment .

Unformatted Attachment Preview

Database Management (COSC 420) PROJECT # 1 1. Develop a database for retail business containing the information of the following information sources, namely CUTOMER, INVENTORY, ORDERS and VENDOR. Draw the table diagram with the relationships among them, by connecting the shared attributes. Identify primary keys and foreign keys, and type of relationship (1:1, 1:N, N:M). While creating the tables and connecting them by references, pay attention to the order in which to create them. For example if you create INVENTORY before VENDOR, you will not be able to make reference to VENDOR in INVENTORY as required by INVENTORY.VNUMB. To overcome this either you could create VENDOR before INVENTORY or first create INVENTORY (without making any reference) and create VENDOR and connect them by ALTER command for INVENTORY. CUSTOMER -------------------------------------------------------------------------------------------------------------------------NAME Domain/Data type DESCRIPTION ---------------------------------------------------------------------------------------------------------------------------CUSTNO CHAR (5) Customer Number CUSTNAME VARCHAR(20) Customer Name ADDRESS VARCHAR(20) Customer Street Address CITY VARCHAR(12) City Name STATE CHAR(2) State Name ZIPCODE CHAR(5) Zipcode Number PHONE CHAR(12) (ddd)-ddd-dddd INVENTORY -------------------------------------------------------------------------------------------------------------------------NAME Domain/Data type DESCRIPTION ---------------------------------------------------------------------------------------------------------------------------PRODNO CHAR(5) Product Number DESCRIPTION VARCHAR(20) Product Description COST NUMBER(5,2) Unit Cost of the product ($) MARKUP NUMBER(5,2) % markup QTYONHAND NUMBER(5) VNUMB CHAR(5) Vendors number ORDERS -----------------------------------------------------------------------------------------------NAME Domain/Data type DESCRIPTION ----------------------------------------------------------------------------------------------CUSTNO CHAR(5) Customer Number PRODNO CHAR(5) Product Number QTYORD NUMBER(5) Quantity Ordered ORDDATE DATE Date of Order VENDOR --------------------------------------------------------------------------Name Domain Type Description ------------------------------- -------- ----------------------------------VNUMB CHAR(5) Vendor Number VNAME VARCHAR(20) Vendors name VADDRESS VARCHAR(30) address VCITY VARCHAR(12) city VSTATE CHAR(2) state VZIP CHAR(5) zip VPHONE CHAR(12) (aaa)-(eee)-(eeee) 2. Populate the tables with the following data. Use SQL INSERT commands. If you encountered any violations such key constraints, or referential integrity constraints, report them and fix them. CUSTOMER CUSTNO A1234 A2345 B1234 C1234 B1235 C1235 A9875 B5896 C7642 CUSTNAME Jones, John Smith,William Brown, James Adams, Mary Baker, Sue Johnson, Anne Miller, Jeff Morris, Joan Barry, Harold ADDRESS 123 Main St 234 Elm St 45 Maple St 9874 ST NW 5846 Braddock R 5241 Mass Ave SE 158 Oak St 1258 Duke St 26 Florida Ave S CITY Largo Lanham Arlington Wasington Fairfax Washington Laurel Alexandria Wasington STATE MD MD VA DC VA DC MD Va DC ZIPCODE 20745 20746 22123 20245 22154 20587 21584 22567 20583 INVENTORY PRODNO Z1234 Z1235 Y1234 Y1235 W1234 W1235 V1234 V1235 U1234 U1235 T9876 T8765 S2531 S5824 R2546 R6821 DESCRIPTION Sony Televion Sony Camcorder Hoover Vacuum Hoover Dustbust GE Microwave O GE Coffee Maker Timex Wristwat Bulova Wristwat Cannon Camera Minolta Camera Zenith VCR Zenith Stereo Oneida Silver Oneida Teapot Kodak Light Met Kodak Lens COST MARKUP QTYONHAND $399.00 17 5 $649.00 08 5 $129.00 12 10 $49.00 12 20 $95.00 14 10 $22.00 10 10 $33.00 15 20 $122.00 22 12 $356.00 19 10 $249.00 14 8 $236.00 25 10 $128.00 16 7 $965.00 35 12 $128.00 31 10 $9.98 05 12 $95.00 24 12 V_NUM 20006 20006 20004 20004 20007 20007 20001 20001 20008 20008 20002 20002 20001 20003 20003 20001 PHONE 301-123-4567 301-123-4568 703-123-5678 202-456-1234 703-589-7624 202-489-6247 301-965-8521 703-581-9357 202-975-4285 ORDERS CUSTNO A1234 A2345 B1234 C1234 A9875 B1235 C7642 B5896 C1235 A1234 B1235 C7642 A1234 B1234 C1235 A9875 C1235 B1235 PRODNO QTYORD ORDDATE Z1234 2 10/4/96 Z1235 3 10/8/96 U1234 2 9/30/96 V1235 6 9/28/96 R6821 9 9/15/96 S2531 8 10/8/96 U1235 2 10/29/96 T8763 5 9/8/96 W1234 4 10/4/96 W1235 2 9/28/96 S5824 3 9/26/96 V1213 5 10/6/96 Z1235 6 10/8/96 U1235 1 9/8/96 Y1234 2 10/14/96 Y1235 3 10/1/96 T9876 2 9/26/96 S2531 1 10/6/96 VENDOR VNUMB VNAME 20001 20002 20003 20004 Anderson and Sons 125 Third North Third 125 North StreetStreet Winona Robertson and Son 123 East Third Av Garvy and Daughter 3122 Northshore Drive Supply House Inc. 212 Anderson Blvd. 20005 20006 20007 20008 House of David Electric Systems Manohar Service Little Detroit House 3. VADDRESS 1776 Industrial Blvd. 1883 North East 888 University Blvd, 200 Starling Road VCITY VS VZIP VPHONE Winona MNMN 55987 55987 507-123-12 507-123-1234 Winona MN 55987 507-321-4321 St. Paul MN 55410 612-123-5555 Minneap MN 55402 612-777-1212 olis Bowie MD 22075 301-262-2001 Lanham MD 20712 301-552-3120 Edina MN 54998 612-926-8000 Detroit MI 33767 512-414-3300 Create the following queries and generate reports in a format that makes it easy to read. a) List all the customer info (CUSTNAME, CUSTPHONE, ADDRESS, CITY, STATE, ZIP) of the business in the ascending order of the last name. b) List all the inventory info (DESCRIPTION, COST, MARKUP) in ascending order of DESCRIPTION. c) List all the order info (PRODNO, QTYORD, ORDDATE) d) List all the customers (custname, product_description) who have ordered product in September 96 (using join on customer, orders and inventory) 4. Submit the project as a word file: a. Problem statement b. Design details (number of tables and their relationship as given in the text for university database) c. Program codes (SQL scripts) d. Results taken from the SQL console and formatted in a word file in an easy to read format) e. Conclusion Rubrics Problem Statement 5 Design Method 15 Program code/scripts 30 Results 40 Concluding Remarks 10
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags