Consider an enterprise in which di?erent projects use parts supplied by various suppliers. De?ne the appropriate tables along with the corresponding foreign-key constraints. De?ne triggers that ?re when a project changes a supplier for a part; when a supplier discontinues a part; or when a project stops using a part.

Question

What will be an ideal response?

Answer 1

```
CREATE TABLE Contract (
PartId INTEGER,
SupplierId INTEGER,
ProjectId INTEGER,
PRIMARY KEY (PartId, SupplierId, ProjectId),
FOREIGN KEY (PartId, SupplierId) REFERENCES Supplies
FOREIGN KEY (PartId) REFERENCES Part
FOREIGN KEY (ProjectId) REFERENCES Project
FOREIGN KEY (SupplierId) REFERENCES Supplier )
```

In addition, we have a table Supplies, which tells which supplier supplies what, and the relations Part, Project, and Supplier, which describe the corresponding entities.

```
CREATE TRIGGER ProjectChangedSupplier
BEFORE UPDATE OF SupplierId ON Contract
REFERENCING NEW AS N
FOR EACH ROW
INSERT INTO Log
VALUES (CURRENT_DATE, ’Supplier Change’, ProjectId,
PartId, SupplierId, N.SupplierId)
```

```
CREATE TRIGGER SupplierDiscontinuedPart
AFTER DELETE ON Supplies
REFERENCING OLD AS O
FOR EACH ROW
UPDATE Contract
SET SupplierId = NULL
WHERE PartId = O.PartId AND SupplierId=O.SupplierId
```

```
CREATE TRIGGER ProjectStoppedUsingPart
AFTER DELETE ON Contract
REFERENCING OLD AS O
FOR EACH ROW
WHEN (NOT EXISTS (
SELECT *
FROM Contract C
WHERE C.PartId = O.PartId ))
INSERT INTO Log
VALUES (CURRENT_DATE, ’Stopped part use’, ProjectId,
PartId, NULL, O.SupplierId)
```