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 theMySQL 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
|
|
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.