Question

The next question uses the following relations, which model an inventory management system

product(product_id, name, price)

inventory(product_id, count)

shipments(shipment_id, product_id, count, delivery_date)

8. (10 points) Write the table creation queries for the above relations, along with the indicated primary keys and the obvious foreign key relationships, and a check constraint restricting the "count" and "price" columns to non-negative values.

Answer 1

For the product table, the product id is a primary key, name is a string and price a positive number hence
CREATE TABLE product {
product_id int,
name varchar(255),
price int CHECK (price >= 0),
PRIMARY KEY (product_id)
}

Inventory also has product_id as its primary key but its also a foreign key. Also, it needs check on count.
CREATE TABLE inventory{
product_id int,
count int CHECK (count >= 0),
PRIMARY KEY (product_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
}

shipments has shipment_id as primary key, product_id as foreign key, count must be non-negtive.
CREATE TABLE shipments{
shipment_id int,
product_id int,
count int CHECK (count >= 0),
delivery_date DATE,
PRIMARY KEY (shipment_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
}

Comment in case of any doubts.