Question

Instructions Try to answer all the questions using what you have learned in class. Please make your query general not data related

This schema is used for inventory management for an OEM

Part	Inventory
p_id Name Cost Supplier Location 1 Traction motor 200 Melco Japan 2 Alternator 400 kato USA 3 HVAC 300 Melco Japan	p_id Warehouse_id quantity 1 A1 100 2 A2 250 3 B1 300
Customer	Model
c_id Name Location CN Canada National Canada UP Union Pacific USA BT Bombardier Transport Sweden	Model p_id Quantity SD70Ace 1 60 SD70M 2 1 SD60 3 4

Orders

order_id C_id Model nbUnit Year 1 CN SD70Ace 50 2018 2 UP SD70Ace 60 2018

Hints: part p_id is the primary key for part. Part relation includes the part information and their suppliers.

Inventory tables list the parts, their quantities and warehouse_id they are stored in. Both p_id and warehouse_id form the primary key.

The customer table stores the customer information and uses c_id as primary key.

The model table includes the Model name and the part it constitutes. It uses Model and p_id as primary key.

Order includes all the order’s information, what Model customer orders, how many units and which year they place the order in.

• I. SQL DDL

1. Write an SQL statement that create a new table replacement that stores the customer’s name and the p_id, c_id and sale price. The primary key being the combination of part and customer id. The sale price has to be positive. No part id can be left blank or null. When a part is deleted from the part table it has to be removed from replacement table.
2. Write an SQL statement to add an attribute Model to relation replacement, and sets it with combination of p_id to be a foreign key in relation model. In case of model changes, this will reflect on the model when a model is no longer supported and removed this will be set to null.

• II. SQL Queries

1. Write an SQL query that returns the names of all the customers that bought either ‘SD70Ace’ or ‘ SD70 ACs’ models.
2. Write a query that selects all p_id needed for building models bought by ‘CN’
3. Write a query that returns the parts and their quantities that are not in stock for building the order 1.
4. Write a query that returns the cost of parts used for building an SD70Ace model
5. Write a query that selects the models to be removed if parts in stock are less than 10% of it over all parts
6. Select the average order size (average number of units) per customer for each model
7. Return the gross sale of supplier ‘Melco’
8. Return number of orders for customer outside the USA

• III. SQL updates

1. Write an SQL to add a new order for 25 units of SD60 bought by UP
2. Write an SQL operation that deletes all order made prior to 2000.
3. Write an SQL operation to increase parts price by 10% for US supplier and 15% for international suppliers
4. Update inventory increasing all part less than 100 by 20 parts in warehouse ‘A1’.

Answer 1

1. (PART 1)

CREATE TABLE REPLACEMENT(

P_ID NUMBER(3) NOT NULL,

C_ID VARCHAR(2) NOT NULL,

CUST_NAME(30),

COST NUMBER(4),

CONSTRAINT PK_PID_CID PRIMARY KEY (P_ID,C_ID),

CONSTRAINT CT_CK CHECK (COST>= 0),

CONSTRAINT PID_FK FOREIGN KEY(P_ID) REFERENCES PART(P_ID) ON DELETE CASCADE ON UPDATE CASCADE);

(PART 2)

ALTER TABLE REPLACEMENT

ADD MODEL VARCHAR9(10),

ADD CONSTRAINT MODEL_DELETE_RULE FOREIGN KEY(MODEL) REFERENCES MODEL(MODEL) ON DELETE SET NULL;

2.(1) SELECT CUSTOMER.NAME

FROM ORDERS LEFT OUTER JOIN CUSTOMER

ON ORDERS.C_ID=CUSTOMER.C_ID,

WHERE ORDERS.MODEL ='SD70Ace' OR ORDERS.MODEL='SD70 ACs';

(2) SELECT MODEL.P_ID

FROM ORDERS JOIN MODEL

ON ORDERS.MODEL=MODEL.MODEL

WHERE ORDERS.C_ID='CN';

(3) SELECT INVENTORY.P_ID,INVENTORY.QUANTITY

FROM ORDERS, MODEL, INVENTORY

WHERE ORDERS.MODEL=MODEL.MODEL AND MODEL.P_ID=INVENTORY.P_ID AND ORDER.ORDER_ID=1 AND (ORDERS.NB_UNIT*MODEL.QUANTITY)<INVENTORY.QUANTITY;

(4) SELECT PART.COST*MODEL.QUANTITY

FROM PART, MODEL

WHERE MODEL.P_ID=PART.P_ID AND MODEL.MODEL='SD70Ace';

(5)   DELETE MODEL.MODEL

FROM MODEL,INVENTORY

WHERE MODEL.P_ID=INVENTORY.P_ID AND (MODEL.QUANTITY/10)>INVENTORY.QUANTITY;

(7) SELECT PART.COST*MODEL.QUANTITY

FROM PART, MODEL, ORDERS

WHERE PART.P_ID=MODEL.P_ID AND MODEL.MOMDEL=ORDERS.MODEL AND PART.SUPPLIER='Melco' ;

(8) SELECT COUNT(C_ID)

FROM ORDERS, CUSTOMER

WHERE ORDERS.C_ID=CUSTOMER.CID AND CUSTOMER.LOCATION='USA';

3.(1) INSERT INTO ORDERS VALUES(3,'UP','SD60',25,2018);

(2) DELETE FROM ORDERS

WHERE ORDERS.YEAR<2000;

(3) UPDATE PART

SET COST=COST+(COST/10)

WHERE LOCATION='USA';

UPDATE PART

SET COST=COST+(COST*0.15)

WHERE LOCATION EXCEPT 'USA';

(4) UPDATE INVENTORY

SET QUANTITY=QUANTITY+10

WHERE QUANTITY<100 AND WAREHOUSE_ID='A1';