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

Submit all files to Blackboard

Grading:

5% for each of the 4 tables

10% for covering foreign keys

10% for inserting data

10% for each query

10% comments to explain queries, describe tables

10% naming create and name the schema, table and attributes appropriately