Final Project: Due by 11:59 p.m. on 10/20/2019
For the course final project, your task is to design a database structure for a large retailer (“Torero Goods”) that imports products from foreign suppliers (mainly China). Products are transported inside large containers (twenty-equivalent-unit or TEU). The individual steps of the entire process are shown in
Figure 1.
The Problem
Torero Goods is having trouble scheduling the unloading activities at its warehouse. The containers need to be unloaded and returned back to the port to avoid accessorial charges that have contributed to rising operational costs and ultimately hurting the bottom line. One of the biggest issues is the inability to quickly query the data so that the warehouse managers can schedule the unloading of containers. Currently, the unloading schedule is based on the purchase order (PO) so other information about the products such as the type and piece counts are often not considered when making decisions. . The CEO has hired you to solve the issue by creating a more transparent, efficient database so the warehouse managers can better schedule the unloading operations.
The current master data for containers are captured in the file “Master_v2.xlsx” 1
Final Project MSBN 504, Fall 2019
Figure 1. Steps in the container shipment
Figure 2. Picture of a container vessel and twenty-equivalent unit (TEU) container
Final Project MSBN 504, Fall 2019
You do not need to utilize all of the columns from the master data in your recommended database structure.
You can rename the existing attributes.
There is currently no attribute for an “unload time”. For example, there is no way of knowing whether a container was unloaded at 6 a.m. or 7 a.m. Torero Goods currently creates a daily unloading schedule like the one below manually:
Table 1. Example of an unload schedule on a weekday
Time
Purchase Order (PO)
6 a.m.
06136880028
6 a.m.
06136880024
7 a.m.
05236880033
8 a.m.
Lunch
8:30 a.m.
07056880013
8:30 a.m.
07056880004
9:30 a.m.
07076880005/ 07571957128
10:30 a.m.
07076880007
11:00 a.m.
06216880038
12:00 p.m.
06236880005/04
12:30 p.m.
06236880016
1:00 p.m.
07076880008 /03
1:30 p.m.
06206880016
For simplicity, assume one-hour intervals starting at 6 a.m. and ending at 2 p.m. (so last unload is at 1 p.m.) for any given weekday. Essentially, your database design must allow the creation of an unload schedule as a result of queries executed by the user. Therefore, there must be some mechanism to create a serialized key to identify the unload time slot for a given PO(s) and/or container(s). Furthermore, the database must be designed so that unload time slot information can be easily be joined with information such as type and piece count of a container. Note that the example table above (Table 1) is not what we want as an output.
Deliverables
Your submission should include:
1) ER diagram showing the relationship between tables/attributes
2) A short write up describing the tables and their attributes. There should be sample
tuples in the tables and an explanation/justification for your design. This should explain why/how you aim to address the stated primary problem using your recommended data structure design.
2
Final Project MSBN 504, Fall 2019
3) SQL scripts that creates the database structure. The script(s) should create all of the tables relevant to the problem. Examples include:
a. Script should specify any constraints necessary
b. Script should specify any primary/foreign key constraints
4) Any triggers that would update the tables whenever necessary
5) Any views that would help with the operations
6) Set of SQL queries to address the user scenarios listed in the “User Scenario” section.
User Scenario
Your database design must take into the following objectives and constraints. It must explicitly include a section with sample SQL scripts to address the user scenarios listed below.
User must be able to organize the data so that a query will output a single day’s list of unload time slots with other information such as the associated PO(s), containers, type, piece count, family, OOS, CBO, LFD.
User must be able to query the list of containers and their associated POs (and other information) based on the last free day (LFD).
User must be able to query the list of POs and/or containers that contains OOS and/or CBO.
Additional Comments
It is strongly recommended that you do not simply go through the normalization process with the master table – this would be very confusing and an inefficient use of your time. Rather, you should use your common sense and intuition to come up with the list of tables and their attributes based on the function. That is, how should the tables and attributed be arranged so that the main business task at hand can be more easily accomplished? Once you have a basic structure, then you can think about normalization methods. Additional items to consider:
Do we really need all of the attributes provided in the master table for the main business function? Could we potentially use surrogate keys on separate tables to store nonessential information?
What is the main function of the unload schedule table? Perhaps a log of activities?
You DO NOT have to use all of the data in the original master datasheet.
You have the freedom to create as many tables as possible and additional key
attributes to make the database function properly.
3
Reviews
There are no reviews yet.