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