create database workshop_day03;
use workshop_day03;

create table customers(
   id int unsigned primary key auto_increment,
   name varchar (40)
);

create table customer_address (
   id int unsigned primary key auto_increment,
   street varchar(40) not null,
   city varchar(40) not null,
   cust_id int unsigned,
   constraint cust_id_fk foreign key(cust_id) references customers(id)
);

create table shipment(
  id int unsigned primary key auto_increment,
  name varchar(40)
);

create table orders(
 id int unsigned primary key auto_increment,
 item_name varchar(40) not null,
 item_quantity int unsigned not null,
 cust_id int unsigned not null,
 shipment_id int unsigned,
 constraint cust_order_id_fk foreign key(cust_id) references customers(id),
 constraint shipment_order_id_fk foreign key (shipment_id) references shipment(id)
);