Description
The purpose of this assignment is to give you practice with creating database structure using SQL and using the INSERT command to add data.
write a MySQL script to create the database structures necessary for the table descriptions below.  Once you have created the tables, add INSERT statements to your script load the tables with the data provided below. Submit your .sql file to Canvas.
Please keep in mind that you will need this database to complete assignment 8. Therefore, it is important that you create and populate the tables with the table definitions and sample data provided below.
Note: Some of the tables may seem to be missing columns that they might have in a real database (for example, an address and social security number in the Employee table).  This has been done to try to keep this assignment as short as possible. You must adhere to the table definitions and data provided below to get full credit.
Table definition for Grant_Source table:
| Grant_source | ||||
| grant_src | Char | 30 | PK |
Data for the Grant_source table:
| grant_src |
| NSF |
| Carnegie Foundation |
Table definition for Grant table
| Grant_table | ||||
| grant_numb | Char | 3 | PK | |
| grant_src | Char | 30 | FK
References Grant_source |
|
| total_amt | Numeric | 10, 2 | ||
| principal_researcher | Char | 3 | FK
References Employee (emp_numb) |
|
Data for the Grant table:
| grant_numb | source | total_amt | principal_researcher |
| 001 | NSF | 450000 | 001 |
| 002 | Carnegie Foundation | 30000 | 001 |
| 003 | NSF | 150000 | 002 |
| 004 | NSF | 75500 | 003 |
| 005 | Carnegie Foundation | 32750 | 004 |
Table definition for the Vendor table:
| Vendor | ||||
| vendor_numb | Char | 3 | PK | |
| vendor_name | Char | 40 | ||
| vendor_street | Char | 50 | ||
| vendor_city | Char | 20 | ||
| vendor_state | Char | 2 | ||
| vendor_zip | Char | 10 | ||
| vendor_phone | Char | 12 | ||
Data for the Vendor table:
| vendor_numb | vendor_name | vendor_street | ||
| vendor_city | vendor_
state |
vendor_zip | vendor_phone | |
| 001 | Archaeology Supply Co. | 85 Northland Highway | ||
| Newtown | MA | 02111 | 999-555-0211 | |
| 002 | Westview Camping, Inc. | 10876 Outer Ring Road | ||
| Westview | CA | 96123 | 998-555-6123 | |
| 003 | Charter Airlines | 25 Airport Way | ||
| Oldtown | GA | 42601 | 997-555-2601 | |
| 004 | Digger’s Paradise | 567 Hammondview | ||
| Eastview | TN | 73109 | 996-555-3109 | |
Table definition for the Purchase table:
| Purchase | ||||
| po_numb | Char | 6 | PK | |
| po_date | Date | |||
| grant_numb | Char | 3 | FK
References Grant |
|
| vendor_numb | Char | 3 | FK
References Vendor |
|
Data for the Purchase table:
| po_numb | po_date | grant_numb | vendor_numb |
| 000001 | 3-15-04 | 001 | 003 |
| 000002 | 3-21-04 | 001 | 002 |
| 000003 | 3-21-04 | 002 | 001 |
| 000004 | 3-25-04 | 004 | 001 |
| 000005 | 3-25-04 | 003 | 004 |
| 000006 | 4-2-04 | 005 | 004 |
Table definition for the Line_item table:
| Line_item | ||||
| po_numb | Char | 6 | PK | FK
References Purchase |
| item_description | Char | 30 | PK | |
| cost_each | Numeric | 8,2 | ||
| quantity | Integer | |||
Data for the Line_item table:
| po_numb | item_description | cost_each | quantity |
| 000001 | First class tickets to Mexico | 2500.10 | 6 |
| 000002 | 6-man tent | 109.00 | 4 |
| 000002 | Dining canopy | 209.95 | 2 |
| 000002 | Mosquito netting | 35.50 | 24 |
| 000002 | Camp stools | 18.50 | 24 |
| 000002 | Fully-equipped camping kitchen | 1500.95 | 2 |
| 000003 | Brush, size 0 | 4.95 | 15 |
| 000003 | Brush, size 2 | 5.95 | 15 |
| 000003 | G-pick | 15.80 | 15 |
| 000003 | Shovel, size 0 | 21.95 | 15 |
| 000003 | Dry specimen case, size S | 7.50 | 100 |
| 000003 | Dry specimen case, size M | 12.50 | 75 |
| 000003 | Dry specimen case, size L | 19.95 | 25 |
| 000004 | Sleeping bag | 110.95 | 10 |
| 000004 | 2-man tent | 185.95 | 5 |
| 000004 | G-pick | 15.80 | 20 |
| 000004 | Shovel, size 0 | 21.95 | 10 |
| 000004 | Brush, size 0 | 4.95 | 10 |
| 000004 | Brush, size 1 | 6.95 | 10 |
| 000005 | Twine, 1000 meters | 17.50 | 5 |
| 000005 | Broom, corn | 12.50 | 3 |
| 000005 | Canvas tent, one room, 20 x 15 | 609.00 | 2 |
| 000005 | Folding table | 125.95 | 15 |
| 000006 | Chemical toilet | 85.95 | 5 |
| 000006 | Latrine tent, 5-stall | 329.95 | 1 |
| 000006 | Tissue for chemical toilets | 1.25 | 100 |
Table Definition for the Dig table:
| Dig | ||||
| dig_numb | Char | 3 | PK | |
| grant_numb | Char | 3 | FK
References Grant |
|
| dig_description | Char | 30 | ||
| location | Char | 30 | ||
Data for the Dig table:
| dig_numb | grant_numb | dig_description | location |
| 001 | 002 | Excavating Eskimo ruins | Barrow, AK |
| 002 | 001 | Excavating a new pyramid | Giza, Egypt |
| 003 | 003 | Documenting cave paintings | Rural France |
| 004 | 005 | Excavating mammoth skeleton | Hyde Park, NY |
Table definition for the Employee table:
| Employee | ||||
| emp_numb | Char | 3 | PK | |
| first_name | Char | 15 | ||
| last_name | Char | 15 | ||
| emp_phone | Char | 12 | ||
Data for the Employee table:
| emp_numb | first_name | last_name | emp_phone |
| 001 | Idaho | Smith | 999-555-0001 |
| 002 | Leslie | Lewis | 999-555-0002 |
| 003 | Indigo | Jones | 999-555-0003 |
| 004 | Jackrabbit | Johnson | 999-555-0004 |
| 005 | Big Cheese | Boss | 999-555-0005 |
| 006 | Marian | Librarian | 999-555-0006 |
| 007 | Stays In | Clerk | 999-555-0007 |
| 008 | Loves To | Dig | 999-555-0008 |
| 009 | Starving | GraduateStudent | 999-555-0009 |
| 010 | Poor | GraduateStudent | 999-555-0010 |
| 011 | He Knows | More | 999-555-0011 |
| 012 | She Knows | More | 999-555-0012 |
Table definition for the Dig_assignment table:
| Dig_assignment | ||||
| dig_numb | Char | 3 | PK | FK
References Dig |
| emp_numb | Char | 3 | PK | FK
References Employee |
Data for the Dig_assignment table
| dig_numb | emp_numb |
| 001 | 001 |
| 001 | 008 |
| 001 | 009 |
| 001 | 010 |
| 002 | 001 |
| 002 | 011 |
| 002 | 012 |
| 003 | 002 |
| 004 | 004 |
| 004 | 003 |
| 004 | 011 |
| 004 | 012 |
