CREATE TABLE customer (
Refc SERIAL,
title varchar(3) NOT NULL,
fname varchar(25) NOT NULL,
lname varchar(25) NOT NULL,
address varchar(50) NOT NULL,
phone varchar(7) NOT NULL,
nic varchar(14) NOT NULL,
CONSTRAINT customer_pkey PRIMARY KEY ( Refc ),
CONSTRAINT vehicle_Refc_key UNIQUE (Refc)
);
--INSERT INTO customer VALUES(1,'Mr','Atish Koomar','Rajub','School Lane Chamouny,Savanne','6455673','F090234201723');
--INSERT INTO customer VALUES(2,'Mr','Vikesh','Hosanee','Rose-Hill','9675645','H2202872401243');
------------------------------------------------------------------------------
CREATE TABLE vehicle (
Refv integer NOT NULL,
DOA date NOT NULL,
make varchar(30) NOT NULL,
vtype varchar(15) NOT NULL,
origin varchar(30) NOT NULL,
description varchar(50) NOT NULL,
vyear date NOT NULL,
colour varchar(15) NOT NULL,
purchase_price double precision NOT NULL,
reserved boolean NOT NULL,
sold boolean NOT NULL,
engine varchar(35) NOT NULL,
speed_trans integer NOT NULL,
CONSTRAINT vehicle1_pkey PRIMARY KEY (Refv),
CONSTRAINT vehicle1_Refv_key UNIQUE (Refv)
);
--INSERT INTO vehicle values(1,'2008-02-15','Subaru Imprezza','Car','Japan','4-Door Handle Cosmic wheels support','2007-01-01','Grey-Black',1200000,false,false,'3000CC',6);
--INSERT INTO vehicle values(2,'2008-02-22','Nissan Skyline','Car','China','4-Door-Cosmic wheels-Tuned Sporty Look','2007-12-09','Red',1500000,false,false,'3500CC',6);
------------------------------------------------------------
CREATE TABLE spareparts (
Refs integer NOT NULL,
DOA date NOT NULL,
origin varchar(15) NOT NULL,
description varchar(50) NOT NULL,
stype varchar(18) NOT NULL,
make varchar(20) NOT NULL,
buying_price double precision NOT NULL,
numstock integer NOT NULL,
sold boolean NOT NULL,
reserved boolean NOT NULL,
CONSTRAINT spareparts_pkey PRIMARY KEY (Refs) ,
CONSTRAINT spareparts_Refs_key UNIQUE (Refs)
);
--INSERT INTO spareparts VALUES(1,'2008-04-28','Indonesia','Sporty Aerodynamic Wipers','4x4','Toyota',1500,2,false,false);
--INSERT INTO spareparts VALUES(2,'2008-03-02','China','Fly Wheel','Car','Zenith',3500,5,false,false);
CREATE TABLE spsales (
salesid integer NOT NULL,
Refs integer references spareparts(Refs) NOT NULL,
Refc integer references customer(Refc) NOT NULL,
tax real NOT NULL,
Cur_date date NOT NULL,
selling_price real NOT NULL,
qty int NOT NULL,
CONSTRAINT spsales_pkey PRIMARY KEY (salesid) ,
CONSTRAINT spareparts_salesid_key UNIQUE (salesid));
--INSERT INTO spsales VALUES(1,1,1,15.5,CURRENT_DATE,3700.05,1);
--INSERT INTO spsales VALUES(2,2,2,15.5,CURRENT_DATE,300.50,2);
CREATE TABLE Vehisale (
SALESID integer NOT NULL,
Refv integer references vehicle(Refv) NOT NULL,
Refc integer references customer(Refc) NOT NULL,
tax real NOT NULL,
Cur_date date NOT NULL,
selling_price real NOT NULL,
CONSTRAINT Vehisale_pkey PRIMARY KEY (SALESID) ,
CONSTRAINT Vehisale_SALESID_key UNIQUE (SALESID));
--INSERT INTO Vehisale VALUES(1,1,1,15.5,CURRENT_DATE,1700000);
--INSERT INTO Vehisale VALUES(2,2,2,15.5,CURRENT_DATE,1200000);