Database Model-Assignment 5 Implement a Relational Design Solved

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