[Solved] Database Model-Assignment 5 Implement a Relational Design

Whatsapp Us

Scan or Click here to open the chat!%s

[Solved] Database Model-Assignment 5 Implement a Relational Design

$25

File Name: Database_Model-Assignment_5_Implement_a_Relational_Design.zip
File Size: 536.94 KB

SKU: [Solved] Database Model-Assignment 5 Implement a Relational Design Category: Tag:
5/5 - (1 vote)

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 FKReferences Grant_source
total_amt Numeric 10, 2
principal_researcher Char 3 FKReferences 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 Diggers 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 FKReferences Grant
vendor_numb Char 3 FKReferences 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 FKReferences 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 FKReferences 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 FKReferences Dig
emp_numb Char 3 PK FKReferences 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

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.

Related products

Shopping Cart
[Solved] Database Model-Assignment 5 Implement a Relational Design
$25