Question
Implementation/SQL Introduction: This assignment continues working with the overall supply chain theme of hw1 but uses some data that seems to be from real-life supply-chain
Implementation/SQL
Introduction:
This assignment continues working with the overall supply chain theme of hw1 but uses some data that seems to be from real-life supply-chain management. The data being represented is so different that we will only keep the schema from hw1. After doing this assignment, you should:
Appreciate how important business rules are for representing requirements
Be able to represent relationships in SQL by using foreign key constraints
Be able to write SQL queries with simple joins
The assignment:
Use the same schema as in hw1. No need to repeat the CREATE schema statement since it already exists, but please continue to set the path so the grader will be able to run your scripts smoothly
This time, the design will be based on the data at: https://brunel.figshare.com/articles/dataset/Supply_Chain_Logistics_Problem_Dataset/7558679 Links to an external site.. We will not use the whole document for this assignment, but download the spreadsheet and compare the columns (which are explained more in section 4 of the paper) with those of the diagram we used for hw1.
The names in the dataset are generic, like PORT09, presumably because data is an asset for companies. Note that this is only based on a few thousand orders, so it does not have to be representative of the full dataset.
Create a script sql to update the schema. Some constraints cannot be added until certain records are inserted.
Use a DROP TABLE IF EXISTS sc_order; command to delete the existing table if it is still there
NOTE: you will not be able to do these requirements exactly in the order listed. As with the sample CreateCompanyPG.sql file that covers the textbook example, you will have to put some of these commands in insert2.sql because you have tables referencing each other
Define the tables OrderList, Warehouses, ProductsPerPlant and PlantPorts.The OrderList table should omit columns D (Carrier) through I (Customer)
OrderID is the key it needs to be an appropriate type to store the values listed
Warehouses (which for their problem are also called Plants) combines the WhCosts and WhCapacities tabs
Plant ID is the key
ProductsPerPlant needs both Plant Code (which is the ID) and Product ID as its key this table covers which plants can supply which products
PlantPorts needs both Plant Code (which is the ID) and Port as the key
Besides defining the PRIMARY KEY constraints, define all appropriate FOREIGN KEY constraints for now, you do not need to worry about the CASCADING clauses
Create a script insert2.sql to insert records into the tables.
You do not have to use the exact data in the spreadsheet, but you should use similar values in particular, the Order IDs are *big* numbers
Insert enough records so that:
Some (but not all) plants use more than one port
Some ports take deliveries from more than one plant
Some products are ordered more than once
Some products are ordered from more than one plant
Create a script query2.sql to do the following:
List plants that use a particular port (there should be at least two, but not all of the plants)
Compute the number of days it will take for a particular order to be produced by the plant (the capacity indicates how many products can be manufactured in one day)
List the costs of manufacturing a particular order at different plants (assume that an order will be satisfied by exactly one plant)
List the ports that might be used when ordering a particular product
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started