Consider the following database schema:

Question

1. Supplier(SName, ItemName, Price)—supplier SName sells item ItemName at Price

2. Customer(CName, Address)—customer CName lives at Address.

3. Order(CName, SName, ItemName, Qty)—customer CName has ordered Qty of item ItemName from supplier SName.

4. Item(ItemName, Description)—information about items.

(a) Draw the E-R diagram from which the above schema might have been derived. Specify the keys.

(b) Suppose now that you want to add the following constraint to this diagram: Every item is supplied by some supplier . Modify the diagram to accommodate this constraint. Also show how this new diagram can be translated back to the relational model.



(c) Repeat parts (a) and (b) in UML.

Answer 1

(a) The key of Supplier is SName, ItemName;ofCustomer is CName; of Item is ItemName;andofOrder is CName, SName, ItemName (and,
possibly, Qty, if multiple orders for the same item from the same supplier are allowed).
(b) One possible relational tables corresponding to this E-R diagram:
```
CREATE TABLE Customer (
CName CHAR(10) NOT NULL,
Address CHAR(20),
PRIMARY KEY(CName));

CREATE TABLE Supplier (
SName CHAR(10) NOT NULL,
PRIMARY KEY(SName));

CREATE TABLE Item (
ItemName CHAR(10) NOT NULL,
Description CHAR(20),
Price FLOAT,
SName CHAR(10) NOT NULL,
PRIMARY KEY(ItemName, SName),
FOREIGN KEY(SName) references Supplier);
CREATE TABLE Order (
CName CHAR(10) NOT NULL,
SName CHAR(10) NOT NULL,
ItemName CHAR(10) NOT NULL,
Qty INTEGER,
PRIMARY KEY(CName, SName, ItemName),
FOREIGN KEY(CName) REFERENCES Customer,
FOREIGN KEY(SName) REFERENCES Supplier,
FOREIGN KEY(ItemName) REFERENCES Item);
```