CS:4400 - Group 2 Project Website
Daniel Dagle, Yiqi Liu, Eric Trautsch
Spring 2023
Showcase of all Tables and their tuples
Inventory Table
| part_id |
storage_area_id |
cost_per_unit |
quantity |
| 1 |
1 |
15000.0 |
22 |
| 1 |
5 |
15500.0 |
8 |
| 2 |
1 |
3000.0 |
20 |
| 3 |
2 |
2000.0 |
15 |
| 4 |
3 |
5000.0 |
12 |
| 5 |
4 |
1000.0 |
25 |
Outgoing Table
| customer_id |
employee_id |
storage_area_id |
part_id |
quantity |
profit_per_unit |
placed_on |
completed_on |
| 1 |
2 |
1 |
1 |
2 |
2000.0 |
2023-04-01 10:00:00 |
2023-04-02 12:30:00 |
| 2 |
5 |
1 |
2 |
5 |
500.0 |
2023-04-10 14:30:00 |
2023-04-11 16:45:00 |
| 3 |
2 |
2 |
3 |
3 |
800.0 |
2023-04-15 09:00:00 |
2023-04-16 10:15:00 |
| 4 |
5 |
3 |
4 |
1 |
1000.0 |
2023-04-20 13:30:00 |
2023-04-21 15:00:00 |
| 5 |
2 |
4 |
5 |
10 |
200.0 |
2023-04-22 11:00:00 |
2023-04-23 12:00:00 |
Incoming Table
| part_id |
storage_area_id |
employee_id |
supplier_id |
cost_per_unit |
quantity |
ordered_on |
received_on |
| 1 |
1 |
3 |
1 |
13000.0 |
22 |
2023-03-01 10:00:00 |
2023-03-10 12:30:00 |
| 2 |
1 |
3 |
2 |
2500.0 |
10 |
2023-03-15 14:30:00 |
2023-03-20 16:45:00 |
| 3 |
2 |
4 |
3 |
1800.0 |
8 |
2023-03-25 09:00:00 |
2023-03-28 10:15:00 |
| 4 |
3 |
4 |
4 |
4500.0 |
6 |
2023-04-05 13:30:00 |
2023-04-10 15:00:00 |
| 5 |
4 |
3 |
5 |
800.0 |
15 |
2023-04-12 11:00:00 |
2023-04-18 12:00:00 |
Part Table
| part_id |
description |
weight |
manufacturer |
material_type |
| 1 |
Aircraft Engine |
1500.0 |
EngineCorp |
Metal |
| 2 |
Landing Gear |
250.0 |
GearInc |
Aluminum |
| 3 |
Fuel Tank |
100.0 |
TankCo |
Composite |
| 4 |
Avionics System |
50.0 |
AvionicsTech |
Electronics |
| 5 |
Wing Flap |
75.0 |
FlapWorks |
Composite |
StorageArea Table
| storage_area_id |
area |
capacity |
location |
| 1 |
A1 |
30 |
North |
| 2 |
A2 |
1200 |
North |
| 3 |
B1 |
1500 |
South |
| 4 |
B2 |
800 |
South |
| 5 |
C1 |
500 |
East |
| 6 |
C2 |
900 |
East |
| 7 |
D1 |
2000 |
West |
| 8 |
D2 |
1100 |
West |
| 9 |
E1 |
750 |
North |
| 10 |
E2 |
600 |
North |
Customer Table
| customer_id |
name |
address |
phone |
email |
| 1 |
Customer One |
987 Cedar St, Anytown |
555-123-9876 |
customerone@email.com |
| 2 |
Customer Two |
654 Spruce St, Anytown |
555-234-8765 |
customertwo@email.com |
| 3 |
Customer Three |
321 Birch St, Anytown |
555-345-7654 |
customerthree@email.com |
| 4 |
Customer Four |
198 Willow St, Anytown |
555-456-6543 |
customerfour@email.com |
| 5 |
Customer Five |
567 Poplar St, Anytown |
555-567-5432 |
customerfive@email.com |
Supplier Table
| supplier_id |
name |
address |
phone |
email |
| 1 |
Supplier One |
123 Main St, Anytown |
555-111-1234 |
supplierone@email.com |
| 2 |
Supplier Two |
456 Oak St, Anytown |
555-222-2345 |
suppliertwo@email.com |
| 3 |
Supplier Three |
789 Elm St, Anytown |
555-333-3456 |
supplierthree@email.com |
| 4 |
Supplier Four |
321 Maple St, Anytown |
555-444-4567 |
supplierfour@email.com |
| 5 |
Supplier Five |
654 Pine St, Anytown |
555-555-5678 |
supplierfive@email.com |
Employee Table
| employee_id |
name |
address |
phone |
office_num |
email |
title |
hire_date |
| 1 |
John Doe |
111 First St, Anytown |
555-321-0987 |
A1 |
johndoe@email.com |
Manager |
2022-01-01 |
| 2 |
Jane Smith |
222 Second St, Anytown |
555-432-1987 |
A2 |
janesmith@email.com |
Sales |
2022-02-15 |
| 3 |
Jim Brown |
333 Third St, Anytown |
555-543-2987 |
A3 |
jimbrown@email.com |
Warehouse Worker |
2022-03-01 |
| 4 |
Sue Green |
444 Fourth St, Anytown |
555-654-3987 |
A4 |
suegreen@email.com |
Warehouse Worker |
2022-04-15 |
| 5 |
Tom White |
555 Fifth St, Anytown |
555-765-4987 |
A5 |
tomwhite@email.com |
Sales |
2022-05-01 |
Showcase of Views
inventory_summary View
| part_id |
description |
manufacturer |
material_type |
total_quantity |
total_value |
| 1 |
Aircraft Engine |
EngineCorp |
Metal |
30 |
454000.0 |
| 2 |
Landing Gear |
GearInc |
Aluminum |
20 |
60000.0 |
| 3 |
Fuel Tank |
TankCo |
Composite |
15 |
30000.0 |
| 4 |
Avionics System |
AvionicsTech |
Electronics |
12 |
60000.0 |
| 5 |
Wing Flap |
FlapWorks |
Composite |
25 |
25000.0 |
customer_sales_summary View
| customer_id |
customer_name |
total_sales |
total_profit |
| 1 |
Customer One |
2 |
4000.0 |
| 2 |
Customer Two |
5 |
2500.0 |
| 3 |
Customer Three |
3 |
2400.0 |
| 4 |
Customer Four |
1 |
1000.0 |
| 5 |
Customer Five |
10 |
2000.0 |
Showcase of SQL Queries
Find Part Query
Where can I find part id a specific part in the warehouse to pick and add to an outgoing shipment? (Using part_id 1 as an example)
| part_id |
description |
storage_area_id |
area |
location |
quantity |
| 1 |
Aircraft Engine |
1 |
A1 |
North |
22 |
| 1 |
Aircraft Engine |
5 |
C1 |
East |
8 |
Find Part Value
What is the total value of all parts of a certain type stored in the warehouse? (Using part id 1)
| part_id |
total_value |
| 1 |
454000.0 |
Find Empty Storage Areas
Which storage areas can accommodate a new part type? (of part id = 1)
| storage_area_id |
area |
capacity |
location |
| 6 |
C2 |
900 |
East |
| 7 |
D1 |
2000 |
West |
| 8 |
D2 |
1100 |
West |
| 9 |
E1 |
750 |
North |
| 10 |
E2 |
600 |
North |
Highest Outgoing Demand
What part is in the highest outgoing demand? (involves join, aggregation, and subquery)
| part_id |
description |
manufacturer |
material_type |
total_outgoing_quantity |
| 5 |
Wing Flap |
FlapWorks |
Composite |
10 |
Profits This Year
What are our profits for the current year? (involves join and aggregation)
This concludes our project website. Thank you!