alter session set nls_language = English;
alter session set nls_territory = Canada;
alter session set nls_date_format = 'dd/mm/yyyy';

drop table Sale_Product;
drop table Product;
drop table Sale;
drop table Vendor;
drop table Customer;

create table Customer (
  ID          number(5,0) primary key,
  FName       varchar2(25) not null,
  LName       varchar2(25) not null,
  Phone       varchar2(20),
  Address     varchar2(60),
  City        varchar2(40),
  Province    varchar2(2)
);

create table Vendor (
  ID           number(5,0) primary key,
  CompanyName  varchar2(50) not null,
  ContactName  varchar2(50),
  Phone        varchar2(20)
);

create table Sale (
  ID           number(5,0) primary key,
  CustomerID   number(5,0) not null references Customer,
  SaleDate     date not null
);

create table Product (
  Code         varchar2(6) primary key,
  Description  varchar2(50) not null,
  VendorID     number(5,0) references Vendor,
  QOH          number(4,0) not null,
  Price        number(6,2) not null
);

create table Sale_Product (
  SaleID       number(5,0) references Sale,
  LineNumber   number(2,0),
	primary key (SaleID, LineNumber),
  ProductCode  varchar2(6) not null references Product,
  Units        number(4,0) not null,
  LineTotal    number(8,2) not null
);



