Due Today
Brian99Application: Creating and Populating a Database
Suppose you have a small retail store and decide to create a database to track sales. You have information about recent sales that you want to use for testing the database. View all or part of the MySQL tutorial on Lynda.com to learn how to install and create a database in MySQL. If necessary, install MySQL on your computer. Then, create the tables for tracking the sales in the database shown below. Include all the constraints you consider necessary to maintain the integrity of the database. Then, for each named table, insert the listed data.
CUSTOMERS (ID, NAME(LAST FIRST), ADDRESS, CURRENT BALANCE, CREDIT LIMIT, SALES REP ID) |
|||||||||
124 |
ADAMS |
SALLY |
481 |
OAK |
LANSING |
MI 49224 |
818.75 |
1000 |
3 |
256 |
SAMUELS |
ANN |
215 |
PETE |
GRANT |
MI 49219 |
21.5 |
1500 |
6 |
311 |
CHARLES |
DON |
48 |
COLLEGE |
IRA |
MI 49034 |
825.75 |
1000 |
12 |
315 |
DANIELS |
TOM |
914 |
CHERRY |
KENT |
MI 48391 |
770.75 |
750 |
6 |
405 |
WILLIAMS |
AL |
519 |
WATSON |
GRANT |
MI 49219 |
402.75 |
1500 |
12 |
412 |
ADAMS |
SALLY |
16 |
ELM |
LANSING |
MI 49224 |
1817.5 |
2000 |
3 |
522 |
NELSON |
MARY |
108 |
PINE |
ADA |
MI 49441 |
98.75 |
1500 |
12 |
567 |
DINH |
TRAN |
808 |
RIDGE |
HARPER |
MI 48421 |
402.4 |
750 |
6 |
587 |
GALVEZ |
MARA |
512 |
PINE |
ADA |
MI 49441 |
114.6 |
1000 |
6 |
622 |
MARTIN |
DAN |
419 |
CHIP |
GRANT |
MI 49219 |
1045.75 |
1000 |
3 |
SALES REPS (ID, NAME(LAST FIRST), ADDRESS, TOTAL COMMISSION, COMMISSION RATE) |
||||||||
3 |
JONES |
MARY |
123 |
MAIN |
GRANT |
MI 49219 |
2150 |
.05 |
6 |
SMITH |
WILLIAM |
102 |
RAYMOND |
ADA |
MI 49441 |
4912.5 |
.07 |
12 |
DIAZ |
MIGUEL |
419 |
HARPER |
LANSING |
MI 49224 |
2150 |
.05 |
ORDERS(ID, ORDER DATE, CUSTOMER, SHIPPING DATE) |
12489 02-JUL-11 124 22-JUL-11 |
12491 02-JUL-11 311 22-JUL-11 |
12494 04-JUL-11 315 12-JUL-11 |
12495 04-JUL-11 256 22-AUG-11 |
12498 05-JUL-11 522 |
12500 05-JUL-11 124 22-AUG-11 |
12504 05-JUL-11 522 |
ORDER LINES (ORDER ID, PART ID, NUMBER ORDERED, QUOTED PRICE) |
||
12489 AX12 11 |
21.95 |
|
12491 BT04 1 |
149.99 |
|
12491 BZ66 1 |
399.99 |
|
12494 CB03 4 |
279.99 |
|
12495 CX11 2 |
22.95 |
|
12498 AZ52 2 |
12.95 |
|
12500 BT04 1 |
149.99 |
|
12504 CZ81 2 |
325.99 |
|
PARTS (PART ID, PART DESCRIPTION, UNITS ON HAND, CLASS, WAREHOUSE NUMBER, UNIT PRICE) |
|||
AX12 IRON |
104 HW 3 |
24.95 |
|
AZ52 DARTBOARD |
20 SG 2 |
12.95 |
|
BH22 CORNPOPPER |
95 HW 3 |
24.95 |
|
BT04 GAS GRILL |
11 AP 2 |
149.99 |
|
BZ66 WASHER |
52 AP 3 |
39.99 |
|
CA14 GRIDDLE |
78 HW 3 |
39.99 |
|
CB03 BIKE |
44 SG 1 |
299.99 |
|
CX11 BLENDER |
112 HW 3 |
22.95 |
|
CZ81 TREADMILL |
68 SG 2 |
349.95 |
|
Requirement for the paper:
Write a query or queries to print the table description and the contents of each table, and copy and paste the results to a file called sales.
Write a 1- to 2-page paper describing each constraint you created and why it is necessary.
Application: Creating and Populating a Database
Suppose you have a small retail store and decide to create a database to track sales. You
have information about recent sales that you want to use for testing the database. View
all or part of the
MySQL tutorial on Lynda.com
to learn how to install and create a
database in MySQL. If necessary, install MySQL on your computer. Then, create the
tables for trackin
g the sales in the database shown below. Include all the constraints you
consider necessary to maintain the integrity of the database. Then, for each named table,
insert the listed data.
CUSTOMERS (ID, NAME(LAST FIRST), ADDRESS, CURRENT BALANCE, CREDIT LIM
IT,
SALES REP ID)
124
ADAMS
SALLY
481
OAK
LANSING
MI
49224
818.75
1000
3
256
SAMUELS
ANN
215
PETE
GRANT
MI
49219
21.5
1500
6
311
CHARLES
DON
48
COLLEGE
IRA
MI
49034
825.75
1000
12
315
DANIELS
TOM
914
CHERRY
KENT
MI
48391
770.75
750
6
405
WILLIAMS
AL
519
WATSON
GRANT
MI
49219
402.75
1500
12
412
ADAMS
SALLY
16
ELM
LANSING
MI
49224
1817.5
2000
3
522
NELSON
MARY
108
PINE
ADA
MI
49441
98.75
1500
12
567
DINH
TRAN
808
RIDGE
HARPER
MI
48421
402.4
750
6
587
GALVEZ
MARA
512
PINE
ADA
MI
49441
114.6
1000
6
Application: Creating and Populating a Database
Suppose you have a small retail store and decide to create a database to track sales. You
have information about recent sales that you want to use for testing the database. View
all or part of the MySQL tutorial on Lynda.com to learn how to install and create a
database in MySQL. If necessary, install MySQL on your computer. Then, create the
tables for tracking the sales in the database shown below. Include all the constraints you
consider necessary to maintain the integrity of the database. Then, for each named table,
insert the listed data.
CUSTOMERS (ID, NAME(LAST FIRST), ADDRESS, CURRENT BALANCE, CREDIT LIMIT,
SALES REP ID)
124 ADAMS SALLY 481 OAK LANSING MI 49224 818.75 1000 3
256 SAMUELS ANN 215 PETE GRANT MI 49219 21.5 1500 6
311 CHARLES DON 48 COLLEGE IRA MI 49034 825.75 1000 12
315 DANIELS TOM 914 CHERRY KENT MI 48391 770.75 750 6
405 WILLIAMS AL 519 WATSON GRANT MI 49219 402.75 1500 12
412 ADAMS SALLY 16 ELM LANSING MI 49224 1817.5 2000 3
522 NELSON MARY 108 PINE ADA MI 49441 98.75 1500 12
567 DINH TRAN 808 RIDGE HARPER MI 48421 402.4 750 6
587 GALVEZ MARA 512 PINE ADA MI 49441 114.6 1000 6