drop table invoice_lines;
drop table invoices;
drop table parts;
drop table customers;

create table parts (
id int not null primary key,
description varchar(30) not null,
price decimal not null);

create table customers (
id int not null primary key,
name varchar(30) not null,
create_date timestamp not null);

create index cust_name_index on customers (name);

create table invoices (
id int not null primary key,
cust_id int not null,
invoice_date date not null,
paid char(1) default 'N',
constraint cust_inv_fk foreign key (cust_id) references customers (id));

create index cust_invs on invoices (cust_id, invoice_date);

create table invoice_lines (
inv_id int not null,
line_no int not null,
part_no int not null,
quantity int not null,
primary key (inv_id, line_no),
constraint inv_lines_fk foreign key (inv_id) references invoices (id),
constraint part_inv_lines_fk foreign key (part_no) references parts (id));
