create table customers
(cust_no int not null primary key, name varchar(128), address varchar(128),
 city varchar(128), state char(2), zip char(9), last timestamp
);

create table orders
(cust_no int not null, ord_no int not null, ord_date timestamp,
 primary key (cust_no, ord_no),
 foreign key (cust_no) references customers
 on delete no action on update cascade);

create table ord_lines
(cust_no int not null, ord_no int not null, line_no int not null, item varchar(128), qty int,
 primary key (cust_no, ord_no, line_no),
 foreign key (cust_no, ord_no) references orders
 on delete cascade on update cascade);

create table shipments
(cust_no int not null, ord_no int not null, ship_no int not null, ship_date timestamp,
 primary key (cust_no, ord_no, ship_no),
 foreign key (cust_no, ord_no) references orders
 on delete no action on update cascade);

create table ship_lines
(cust_no int not null, ord_no int not null, ship_no int not null, line_no int not null, ord_line int, qty int,
 primary key (cust_no, ord_no, ship_no, line_no),
 foreign key (cust_no, ord_no, ship_no) references shipments
 on delete cascade on update cascade,
 foreign key (cust_no, ord_no, ord_line) references ord_lines
 on delete no action on update cascade);

create table log
(cust_no int not null, ord_no int not null, ship_no int not null, line_no int not null, type1 int not null,
 log_date timestamp,
 primary key (cust_no, ord_no, ship_no, line_no, type1));

create table surrogate_key
(table_name varchar(128)  not null primary key, id bigint);

insert into surrogate_key values('customers', 0);
