Oracle Table Creation

An example creating/deleting oracle tables and sequences

drop table Account cascade constraints;
drop sequence acc_seq;

drop table Company cascade constraints;
drop sequence comp_seq;

drop Table Rating cascade constraints;

drop Table Location cascade constraints;

drop table TradeType cascade constraints;
drop sequence trade_seq;


drop table Does cascade constraints;
drop table WorksIn cascade constraints;


-- entities

CREATE TABLE Account (
	aID CHAR(25) PRIMARY KEY,
	aFName VARCHAR(50) NOT NULL,
	aLName VARCHAR(50) NOT NULL,
	aEmail VARCHAR(50) UNIQUE NOT NULL,
	aPassword VARCHAR(25) NOT NULL
);

CREATE TABLE Location (
	lID CHAR(25) PRIMARY KEY,
	lCity VARCHAR(50) NOT NULL,
	lProv VARCHAR(2) NOT NULL
);


CREATE TABLE Company (
	coID CHAR(25) PRIMARY KEY,
	aID CHAR(25) REFERENCES Account,
	lID CHAR(25) REFERENCES Location, -- location of head office
	coName vARCHAR(50) NOT NULL,
	coPhone VARCHAR(10),
	coEmail VARCHAR(50),
	coAddress VARCHAR(50),
	coAcType VARCHAR(2),
	coDesc VARCHAR(100)
);


CREATE TABLE Rating (
	rID char(25) PRIMARY KEY,
	coID char(25) REFERENCES Company,
	rateNum char(1),
	rateComment VARCHAR (100)
);

CREATE TABLE TradeType (
	tID CHAR(25) PRIMARY KEY,
	tName VARCHAR(50) UNIQUE NOT NULL
);


-- relations

CREATE TABLE Does (
	tID CHAR(25),
	coID CHAR(25),
	CONSTRAINT does_pk PRIMARY KEY(tID, coID),
	CONSTRAINT does_fk FOREIGN KEY(tID) REFERENCES TradeType,
	CONSTRAINT does_fk1 FOREIGN KEY(coID) REFERENCES Company
);

CREATE TABLE WorksIn (
	lID CHAR(25),
	coID CHAR(25),
	CONSTRAINT worksin_pk PRIMARY KEY (lID, coID),
	CONSTRAINT worksin_fk FOREIGN KEY(lID) REFERENCES Location,
	CONSTRAINT worksin_fk1 FOREIGN KEY(coID) REFERENCES Company
);

-- sequences

CREATE SEQUENCE trade_seq
START WITH 100
INCREMENT BY 1;

CREATE SEQUENCE acc_seq
START WITH 100
INCREMENT BY 1;

CREATE SEQUENCE loc_seq
START WITH 100
INCREMENT BY 1;

CREATE SEQUENCE rate_seq
START WITH 100
INCREMENT BY 1;

CREATE SEQUENCE comp_seq
START WITH 100
INCREMENT BY 1;