db.sql

 drop database project2;
create database Project2;
use Project2;
 
Create table item(
Item_id int primary key,
Item_name char(100),
category char(100),
price decimal(5,2)
);
 
Create table Purchase_order(
po_num int primary key,
po_date datetime,
Sp_id int
);
 
Create table Shipment(
	
sh_id int ,
sh_date datetime,
payment char(100),
origin char (100),
destination char(100),
primary key (sh_id)
);

Create table sh_po (
po_num int ,
sh_id int ,
primary key (po_num, sh_id)
);
 
Create table Supplier(
Sp_id int primary key,
Sp_name char(100),
location char(100)
);
 
Create table Warehouse(
w_id int primary key,
location char(100),
w_type char(100)
);
 
Create table inventory(
w_id int,
po_num int,
qty int,
Primary key (w_id, po_num)
);
 
Create table po_item(
po_num int,
item_id int,
qty int,
Primary key (item_id, po_num)
);
 
Create table sh_item(
sh_id int,
Item_id int,
qty int,
Primary key (item_id, sh_id)
);


alter table sh_po
add constraint foreign key fk1 (po_num) references Purchase_order(po_num);


alter table sh_po
add constraint foreign key fk2 (sh_id) references Shipment(sh_id);

alter table inventory
add constraint foreign key fk3 (w_id) references Warehouse(w_id);

alter table inventory
add constraint foreign key fk4 (po_num) references Purchase_order(po_num);


alter table po_item
add constraint foreign key fk5 (po_num) references Purchase_order(po_num);


alter table po_item
add constraint foreign key fk6 (item_id) references item(item_id);


alter table sh_item
add constraint foreign key fk7 (Item_id) references Item(Item_id);


alter table sh_item
add constraint foreign key fk8 (sh_id) references Shipment(sh_id);



alter table Purchase_order
add constraint foreign key fk9 (Sp_id) references Supplier(Sp_id);