Question

Model the following cases by ER diagrams. Identify the keys, attributes and relationship cardinalities, and convert them into table schemas. Provide necessary assumptions to support your model.

The following descriptions are about the inventory information. a) Each product item has ID, description, quantity in the inventory, unit price and a supplier. b) Suppliers have unique ID, addresses, phone numbers, and names. c) Each address is made up of a street address, a city, and a postcode. d) The purchase order may consist of one or more product items. e) Each purchase order has date, quantity for each items, total value

Answer 1

If you have any doubts, please give me comment...

CREATE TABLE Supplier(

ID INT NOT NULL PRIMARY KEY,

addr_street VARCHAR(50),

addr_city VARCHAR(50),

addr_post_code CHAR(5),

phone_number VARCHAR(15),

name VARCHAR(100)

);

CREATE TABLE Product(

ID INT NOT NULL PRIMARY KEY,

description VARCHAR(30),

quantity INT,

unit_price REAL(5,2),

supplierID INT,

FOREIGN KEY(supplierID) REFERENCES Supplier(ID)

);

CREATE TABLE PRODUCTORDER(

OrderNum INT NOT NULL PRIMARY KEY,

date DATE,

quantity INT,

total_value REAL(10,2)

);

CREATE TABLE ORDERCONSISTS(

ProductID INT,

OrderNum INT,

PRIMARY KEY(ProductID, OrderNum),

FOREIGN KEY(ProductID) REFERENCES Product(ID),

FOREIGN KEY(OrderNum) REFERENCES Order(OrderNum)

);