4and5.2

ALTER TABLE Shipment

ADD CONSTRAINT Ship_Fkey_SID FOREIGN KEY(SID) REFERENCES Supplier(SID) ON UPDATE CASCADE ON DELETE CASCADE

ALTER TABLE Shipment

ADD CONSTRAINT Ship_Fkey_JID FOREIGN KEY(JID) REFERENCES Project(JID) ON UPDATE CASCADE ON DELETE CASCADE

ALTER TABLE Shipment

ADD CONSTRAINT Ship_Fkey_PID FOREIGN KEY(PID) REFERENCES PART(PID) ON UPDATE CASCADE ON DELETE CASCADE

4and5.3

CREATE FUNCTION ColorPartsInProject (@proj nvarchar(25), @color nvarchar(255)) RETURNS int

AS

BEGIN

DECLARE @count int

IF @proj IS NULL OR @color IS NULL

RETURN -1

-- verify that the SHIPMENT table exists to run our query

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Shipment')

RETURN -2

-- verify that the project exists in SHIPMENT table

IF NOT EXISTS(SELECT * FROM Shipment WHERE JID = @proj)

RETURN -3

-- see if there are any parts of that color for that project in the shipment table

IF (SELECT COUNT(Shipment.PID) FROM SHIPMENT,PART WHERE Shipment.PID = Part.PID AND Shipment.JID=@proj AND Part.Color=@color) = 0

RETURN 0

RETURN (SELECT SUM(Qty) FROM SHIPMENT WHERE JID=@proj AND PID IN (SELECT PID FROM PART WHERE Color=@color))

END

4and5.4

CREATE PROCEDURE Supplier_Shipment_Report @supplier nvarchar(25), @status int OUTPUT

AS

BEGIN

-- check for existence for required tables and

-- set error conditions

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Supplier')

SET @status = -5

ELSE IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Shipment')

SET @status = -4

ELSE IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Part')

SET @status = -3

ELSE IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Project')

SET @status = -2

ELSE IF NOT EXISTS(SELECT * FROM Supplier WHERE SID=@supplier)

SET @status = -1

ELSE

-- the default error condition

SET @status = -6

-- if anything other than default error return

IF @status <> -6

RETURN

CREATE TABLE temp

(

SupplierName nvarchar(25),

ProjectName nvarchar(25),

PartColor nvarchar(25),

SPJPColorQty int,

PJPColorQty int,

JQty int,

SPJPctQty float(2),

SJPctQty float(2)

)

-- all declarations

-- temporaries to hold each value

DECLARE @SupplierName nvarchar(255)

DECLARE @part nvarchar(25), @project nvarchar(25)

DECLARE @ProjectName nvarchar(255)

DECLARE @PartColor nvarchar(255)

DECLARE @SPJPColorQty int, @PJPColorQty int

DECLARE @JQty int, @SPJPctQty float(2), @SJPctQty float(2)

DECLARE PartsCursor CURSOR FOR

SELECT PID, Color FROM Part

OPEN PartsCursor

-- first get supplier’s name

SELECT @SupplierName = Name FROM Supplier WHERE SID=@supplier

-- outer loop parts table

FETCH NEXT FROM PartsCursor

INTO @part, @PartColor

WHILE @@FETCH_STATUS = 0

BEGIN

-- declare a cursor to go through projects

DECLARE ProjectsCursor CURSOR FOR

SELECT JID,Name FROM Project

OPEN ProjectsCursor

-- inner loop projects table

FETCH NEXT FROM ProjectsCursor

INTO @project, @ProjectName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SPJPColorQty = SUM(Qty) FROM Shipment, Part WHERE Shipment.PID = Part.PID AND Part.Color = @PartColor AND Shipment.SID = @supplier AND Shipment.JID = @project

SELECT @PJPColorQty = SUM(Qty) FROM Shipment, Part WHERE Shipment.PID = Part.PID AND Part.Color = @PartColor AND Shipment.JID = @project

SELECT @JQty = SUM(Qty) FROM Shipment WHERE Shipment.JID = @project

-- to prevent division by 0

-- convert to NULL

SELECT @SPJPctQty = CAST(@SPJPColorQty as float)/NULLIF(@PJPColorQty,0.0) * 100.0

SELECT @SJPctQty = CAST(@SPJPColorQty as float)/NULLIF(@JQty,0.0) * 100.0

-- ok now we have everything

-- insert into table

INSERT INTO temp VALUES(@SupplierName, @ProjectName, @PartColor, @SPJPColorQty, @PJPColorQty, @JQty, @SPJPctQty, @SJPctQty)

FETCH NEXT FROM ProjectsCursor

INTO @project, @ProjectName

END

-- close projects

CLOSE ProjectsCursor

DEALLOCATE ProjectsCursor

FETCH NEXT FROM PartsCursor

INTO @part, @PartColor

END

-- print the report sorted by ProjectName

-- and PartColor

SELECT * FROM temp ORDER BY ProjectName, PartColor ASC

-- now drop the temporary table

DROP TABLE temp

-- deallocate cursors

CLOSE PartsCursor

DEALLOCATE PartsCursor

-- set success

SET @status = 0

END

4and5.5

CREATE TABLE SHIPPEDPCOLORQTY

(Color nvarchar(25) PRIMARY KEY,

Qty int

)

CREATE TRIGGER Trig_PartsByColor

ON SHIPMENT

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

-- we need to have shipment,part to exist

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Part')

BEGIN

DELETE SHIPPEDPCOLORQTY

INSERT INTO SHIPPEDPCOLORQTY

SELECT Part.Color, Sum(Qty)

FROM Shipment,Part

WHERE Shipment.PID = Part.PID

GROUP BY Shipment.PID

END

END