Basic way to create tables in SQL

connect to sampleDatabase;

-- the FinancialProduct entity
DROP TABLE FinancialProduct;
CREATE TABLE FinancialProduct (
pid INTEGER NOT NULL,
type VARCHAR(12),
PRIMARY KEY (pid),
CONSTRAINT typecheck CHECK (type IN ('creditLine', 'creditCard', 'personalLoan')));

--the Customer entity
DROP TABLE Customer;
CREATE TABLE Customer (
cid INTEGER NOT NULL,
income INTEGER,
expenses INTEGER,
DOB DATE,
phone# VARCHAR(12),
email VARCHAR(40),
PRIMARY KEY (cid));

--the Bank entity
DROP TABLE Bank;
CREATE TABLE Bank (
bid INTEGER NOT NULL,
address VARCHAR(50),
phone# VARCHAR(12),
PRIMARY KEY (bid));

--the Application entity
DROP TABLE Application;
CREATE TABLE Application (
serial# INTEGER NOT NULL,
type VARCHAR(12),
PRIMARY KEY (serial#),
CONSTRAINT typecheck CHECK (type IN ('creditLine', 'creditCard', 'personalLoan')));

--the Address entity
DROP TABLE Address;
CREATE TABLE Address (
cid INTEGER NOT NULL,
street VARCHAR(20) NOT NULL,
number INTEGER NOT NULL,
city VARCHAR(20) NOT NULL,
postalCode VARCHAR(6),
PRIMARY KEY (street, number, city),
FOREIGN KEY (cid) REFERENCES Customer (cid)
ON DELETE CASCADE);


--the Name entity
DROP TABLE Name;
CREATE TABLE Name (
cid INTEGER NOT NULL,
last VARCHAR(15) NOT NULL,
first VARCHAR(10),
mi VARCHAR (10),
PRIMARY KEY (last),
FOREIGN KEY (cid) REFERENCES Customer (cid)
ON DELETE CASCADE);

--the named relationship set
DROP TABLE named;
CREATE TABLE named (
cid INTEGER NOT NULL,
last VARCHAR(15) NOT NULL,
PRIMARY KEY (cid, last),
FOREIGN KEY (cid) REFERENCES Customer (cid),
FOREIGN KEY (last) REFERENCES Name (last)
ON DELETE CASCADE);

--the livesIn relationship set
DROP TABLE livesIn;
CREATE TABLE livesIn (
street VARCHAR(20) NOT NULL,
number INTEGER NOT NULL,
city VARCHAR(20) NOT NULL,
cid INTEGER NOT NULL,
PRIMARY KEY (cid, street, number, city),
FOREIGN KEY (street, number, city) REFERENCES Address (street, number, city),
FOREIGN KEY (cid) REFERENCES Customer (cid)
ON DELETE CASCADE);

--the receives relationship set
DROP TABLE receives;
CREATE TABLE receives (
bid INTEGER NOT NULL,
serial# INTEGER NOT NULL,
date DATE,
PRIMARY KEY (bid, serial#),
FOREIGN KEY (bid) REFERENCES Bank (bid),
FOREIGN KEY (serial#) REFERENCES Application (serial#)
ON DELETE CASCADE);

--the submitsTo relationship set
DROP TABLE submitsApp;
CREATE TABLE submitsApp (
cid INTEGER NOT NULL,
bid INTEGER NOT NULL,
serial# INTEGER NOT NULL,
date DATE,
PRIMARY KEY (cid, bid, serial#),
FOREIGN KEY (cid) REFERENCES Customer (cid),
FOREIGN KEY (bid) REFERENCES Bank (bid),
FOREIGN KEY (serial#) REFERENCES Application (serial#)
ON DELETE CASCADE);

--the respondsTo relationship set
DROP TABLE respondsTo;
CREATE TABLE respondsTo (
cid INTEGER NOT NULL,
bid INTEGER NOT NULL,
serial# INTEGER NOT NULL,
date DATE,
conditions VARCHAR (30),
PRIMARY KEY (cid, bid, serial#),
FOREIGN KEY (cid) REFERENCES Customer (cid),
FOREIGN KEY (bid) REFERENCES Bank (bid),
FOREIGN KEY (serial#) REFERENCES Application (serial#)
ON DELETE CASCADE);

--the HAS relationship set
DROP TABLE HAS;
CREATE TABLE HAS (
pid INTEGER NOT NULL,
bid INTEGER NOT NULL,
serial# INTEGER NOT NULL,
cid INTEGER NOT NULL,
PRIMARY KEY (pid, bid, serial#, cid),
FOREIGN KEY (pid) REFERENCES FinancialProduct (pid),
FOREIGN KEY (bid) REFERENCES Bank (bid),
FOREIGN KEY (serial#) REFERENCES Application (serial#),
FOREIGN KEY (cid) REFERENCES Customer (cid)
ON DELETE CASCADE);

list tables;
connect reset;
terminate;