--Invoice Database 

drop table description;
drop table invoice;
drop table website;
drop table customer;


CREATE TABLE Customer
(customerID   char(3)  Primary Key,
firstName     char(30),
lastName      char(30), 
company       char(50),
streetNumber  char(10),
streetName    char(30),
city          char(20),
prov          char(20),
postal        char(7),      
phone         char(14),      
phone2        char(14),     
cell          char(14),      
home          char(14),      
fax           char(14),      
customerDate  date     NOT NULL,
domain        char(40) REFERENCES website (domain));


CREATE TABLE website
(domain         char(40) PRIMARY KEY,
customerID      char(3)  REFERENCES Customer (customerID),
dateRegistered  date     NOT NULL,
dateExpires     date     NOT NULL,
amountPaid      number(6,2));


CREATE TABLE invoice
(invoiceNumber char(4)     PRIMARY KEY,
customerID     char(3)     REFERENCES customer (customerID),
dateIssued     date        NOT NULL,
dateDue        date        NOT NULL,
datePaid       date,
status         char(10)    CHECK (status IN ('paid','unpaid','part-paid')) NOT NULL,
descriptionID  char(4)     REFERENCES description (descriptionID) NOT NULL,
subTotal       number(6,2) NOT NULL,
PSTexempt      char(3),     
totalPST       number(6,2)
totalDue       number(6,2) NOT NULL,
comments       varchar2(3000));


CREATE TABLE description
(descriptionID char(4)        PRIMARY KEY,
invoiceNumber  char(4)        REFERENCES invoice(invoiceNumber) NOT NULL,
product        varchar2(1000) NOT NULL,
price          number(6,2)    NOT NULL,
PSTexempt      char(3)        CHECK (PSTexempt IN ('yes','no')) NOT NULL,
PSTamount      number(6,2));