SQL Integridade e Consistência

exercicios.sql

CREATE TABLE states (
	id SERIAL PRIMARY KEY,
	name text NOT NULL
);

CREATE TABLE cities (
	id SERIAL PRIMARY KEY,
	name text NOT NULL,
	"stateId" INTEGER NOT NULL REFERENCES states(id)
);

CREATE TABLE customerAdresses (
	id SERIAL PRIMARY KEY,
	"customerId" INTEGER UNIQUE NOT NULL REFERENCES customers(id),
	street TEXT NOT NULL,
	number INTEGER NOT NULL,
	complement TEXT NOT NULL,
	"postalCode" INTEGER NOT NULL,
	"cityId" INTEGER NOT NULL REFERENCES cities(id)
);

CREATE TABLE customers (
	id SERIAL PRIMARY KEY,
	"fullName" TEXT NOT NULL,
	cpf INTEGER NOT NULL,
	email TEXT NOT NULL,
	password TEXT NOT NULL
);

CREATE TABLE "bankAccount"(
	id SERIAL PRIMARY KEY,
	"customerId" INTEGER NOT NULL REFERENCES customers(id),
	"accountNumber" INTEGER NOT NULL,
	agency INTEGER NOT NULL,
	"openDate" DATE NOT NULL DEFAULT NOW(),
	"closeDate" DATE
)

CREATE TYPE trans AS ENUM ('deposit', 'withdraw');
drop type transactions

CREATE TABLE transactions (
	id SERIAL PRIMARY KEY,
	"bankAccountId" INTEGER NOT NULL REFERENCES "bankAccount"(id),
	amount INTEGER NOT NULL,
	type trans NOT NULL,
	descriptions TEXT NOT NULL,
	cancelled BOOL NOT NULL DEFAULT false
	
)

CREATE TABLE "creditCard"(
	id SERIAL PRIMARY KEY,
	"bankAccountId" INTEGER NOT NULL REFERENCES "bankAccount"(id),
	name TEXT NOT NULL,
	number INTEGER NOT NULL,
	"securityCode" INTEGER NOT NULL,
	"expirationMonth" INTEGER NOT NULL,
	"expirationYear" INTEGER NOT NULL,
	password TEXT NOT NULL,
	limite INTEGER NOT NULL
)

CREATE TYPE typephone AS ENUM ('landline', 'mobile');

CREATE TABLE "customerPhones"(
id SERIAL PRIMARY KEY,
	"customerId" INTEGER NOT NULL REFERENCES customers(id),
	number INTEGER NOT NULL,
	type typephone NOT NULL  
);