andmebaas.sql

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 12.0 		*/
/*  Created On : 16-nov-2015 14:50:55 				*/
/*  DBMS       : PostgreSQL 						*/
/* ---------------------------------------------------- */

/* Drop Tables */

DROP TABLE IF EXISTS tootaja_staatus CASCADE
;

DROP TABLE IF EXISTS tootaja_roll CASCADE
;

DROP TABLE IF EXISTS tootaja CASCADE
;

DROP TABLE IF EXISTS riik CASCADE
;

DROP TABLE IF EXISTS organisatsiooni_tyyp CASCADE
;

DROP TABLE IF EXISTS organisatsioon CASCADE
;

DROP TABLE IF EXISTS kaup CASCADE
;

DROP TABLE IF EXISTS kauba_staatus CASCADE
;

DROP TABLE IF EXISTS kauba_kategooria CASCADE
;

/* Create Tables */

CREATE TABLE tootaja_staatus
(
	tootaja_staatuse_kood smallint NOT NULL,
	nimetus varchar(50)	 NULL,
	kirjeldus varchar(50)	 NOT NULL,
	CONSTRAINT PK_tootaja_staatus PRIMARY KEY (tootaja_staatuse_kood),
	CONSTRAINT UK_tootaja_staatus_on_unikaalne UNIQUE (nimetus),
	CONSTRAINT CHK_tootaja_staatuse_nimetus_ei_ole_tyhi CHECK (nimetus !~ '^\s*$')
)
;

CREATE TABLE tootaja_roll
(
	tootaja_rolli_kood integer NOT NULL,
	nimetus varchar(50)	NULL,
	kirjeldus varchar(50)	 NOT NULL,
	CONSTRAINT PK_tootaja_roll PRIMARY KEY (tootaja_rolli_kood),
	CONSTRAINT UK_tootaja_rolli_nimetus_on_unikaalne UNIQUE (nimetus)
)
;

CREATE TABLE tootaja
(
	tootaja_kood serial NOT NULL,
	isikukood char(11)	 NOT NULL,
	tootaja_rolli_kood integer NOT NULL,
	tootaja_staatuse_kood smallint NOT NULL DEFAULT 1,
	e_mail varchar(255)	 NOT NULL,
	eesnimi varchar(50)	 NOT NULL,
	perenimi varchar(50)	 NOT NULL,
	parool char(60)	 NOT NULL,
	CONSTRAINT PK_tootaja PRIMARY KEY (tootaja_kood),
	CONSTRAINT UK_e_mail_on_unikaalne UNIQUE (e_mail),
	CONSTRAINT UK_isikukood_on_unikaalne UNIQUE (isikukood),
	CONSTRAINT CHK_eesnimi_on_tahed CHECK (eesnimi ~ '^[[:alpha:] \-]+$'),
	CONSTRAINT CHK_isikukood_on_11_numbrit CHECK (isikukood ~ '^[0-9]{11}$'),
	CONSTRAINT CHK_email_on_korrektses_formaadis CHECK (e_mail ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
	CONSTRAINT CHK_perenimi_on_tahed CHECK (perenimi ~ '^[[:alpha:] \-]+$'),
	CONSTRAINT FK_tootaja_tootaja_roll FOREIGN KEY (tootaja_rolli_kood) REFERENCES tootaja_roll (tootaja_rolli_kood) ON DELETE Cascade ON UPDATE Cascade,
	CONSTRAINT FK_tootaja_tootaja_staatus FOREIGN KEY (tootaja_staatuse_kood) REFERENCES tootaja_staatus (tootaja_staatuse_kood) ON DELETE Cascade ON UPDATE Cascade
)
;

CREATE TABLE riik
(
	riigi_kood char(2)	 NOT NULL,
	nimi varchar(70)	 NOT NULL,
	CONSTRAINT PK_riik PRIMARY KEY (riigi_kood),
	CONSTRAINT UK_riik_on_unikaalne UNIQUE (nimi),
	CONSTRAINT CHK_riigi_nimi_ei_ole_tyhi CHECK (nimi !~ '^\s*$'),
	CONSTRAINT CHK_riigi_kood_on_2_tahte CHECK (riigi_kood ~ '^[A-Z]{2}$')
)
;

CREATE TABLE organisatsiooni_tyyp
(
	organisatsiooni_tyybi_kood smallint NOT NULL,
	organisatsiooni_tyybi_nimetus varchar(50) NULL,
	CONSTRAINT PK_organisatsiooni_tyyp PRIMARY KEY (organisatsiooni_tyybi_kood),
	CONSTRAINT UK_organisatsiooni_tyybi_nimetus_on_unikaalne UNIQUE (organisatsiooni_tyybi_nimetus),
	CONSTRAINT CHK_organisatsiooni_tyybi_nimetus_ei_ole_tyhi CHECK (organisatsiooni_tyybi_nimetus !~ '^\s*$')
)
;

CREATE TABLE organisatsioon
(
	organisatsiooni_kood serial NOT NULL,
	organisatsiooni_tyybi_kood smallint NOT NULL,
	riik char(2)	 NOT NULL,
	aadress varchar(255)	 NOT NULL,
	e_mail varchar(255)	 NOT NULL,
	nimi varchar(255)	 NOT NULL,
	registri_kood varchar(255)	 NOT NULL,
	tarneaeg decimal(4,2) NULL,
	hinne decimal(3,2) NULL,
	CONSTRAINT PK_organisatsioon PRIMARY KEY (organisatsiooni_kood),
	CONSTRAINT UK_org_on_riigiti_unikaalne UNIQUE (registri_kood,riik),
	CONSTRAINT CHK_organisatsiooni_tarneaeg_pole_negatiivne CHECK (tarneaeg >= 0),
	CONSTRAINT CHK_organisatsiooni_hinne_on_0_kuni_5 CHECK (hinne >= 0 AND hinne <= 5),
	CONSTRAINT CHK_organisatsiooni_email_on_korrektne CHECK (e_mail ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
	CONSTRAINT CHK_organisatsiooni_registri_kood_on_alphanum CHECK (registri_kood ~ '^[A-Z0-9]+$'),
	CONSTRAINT CHK_tarneaeg_ja_hinne_not_null CHECK (tarneaeg IS NOT NULL OR hinne IS NOT NULL),
	CONSTRAINT FK_organisatsioon_organisatsiooni_tyyp FOREIGN KEY (organisatsiooni_tyybi_kood) REFERENCES organisatsiooni_tyyp (organisatsiooni_tyybi_kood) ON DELETE Cascade ON UPDATE Cascade,
	CONSTRAINT FK_organisatsiooni_riik FOREIGN KEY (riik) REFERENCES riik (riigi_kood) ON DELETE Cascade ON UPDATE Cascade
)
;

CREATE TABLE kauba_staatus
(
	kauba_staatuse_kood smallint NOT NULL,
	nimetus varchar(255) NULL,
	kirjeldus varchar(255)	 NULL,
	CONSTRAINT PK_kauba_staatus PRIMARY KEY (kauba_staatuse_kood),
	CONSTRAINT UK_kauba_staatuse_nimetus_on_unikaalne UNIQUE (nimetus),
	CONSTRAINT CHK_kauba_staatuse_nimetus_ei_ole_tyhi CHECK (nimetus !~ '^\s*$')
)
;

CREATE TABLE kauba_kategooria
(
	kauba_kategooria_kood integer NOT NULL,
	ylem_kategooria integer NULL,
	nimetus varchar(255) NULL,
	kirjeldus varchar(255)	 NULL,
	CONSTRAINT PK_kauba_kategooria PRIMARY KEY (kauba_kategooria_kood),
	CONSTRAINT UK_kauba_kategooria_nimetus_ja_ylemk_on_unikaalne UNIQUE (nimetus, ylem_kategooria),
	CONSTRAINT CHK_kauba_kategooria_nimetus_ei_ole_tyhi CHECK (nimetus !~ '^\s*$'),
	CONSTRAINT FK_kauba_ylemkategooria FOREIGN KEY (ylem_kategooria) REFERENCES kauba_kategooria (kauba_kategooria_kood) ON DELETE Cascade ON UPDATE Cascade
)
;

CREATE TABLE kaup
(
	kauba_kood varchar(20)	 NOT NULL,
	kauba_staatus smallint NOT NULL,
	kauba_kategooria integer NOT NULL,
	tarnija integer NOT NULL,
	tootja integer NOT NULL,
	tootaja integer NOT NULL,
	nimetus varchar(255) NULL,
	hind decimal(19,2) NOT NULL,
	korgus integer NULL,
	laius integer NULL,
	pikkus integer NULL,
	pildi_aadress varchar(255)	 NULL,
	CONSTRAINT PK_kaup PRIMARY KEY (kauba_kood),
	CONSTRAINT UK_kauba_nimetus_on_unikaalne UNIQUE (nimetus),
	CONSTRAINT CHK_kauba_kood CHECK (kauba_kood ~ '^\d+$'),
	CONSTRAINT CHK_kauba_hind_pole_negatiivne CHECK (hind >= 0),
	CONSTRAINT CHK_kauba_mootmed_on_positiivsed CHECK (korgus > 0 AND pikkus > 0 AND laius > 0),
	CONSTRAINT CHK_kauba_pildi_aadress_on_url CHECK (pildi_aadress ~* '^(https?|ftp):\/\/[^\s\/$.?#].[^\s]*$'),
	CONSTRAINT FK_kauba_tootja FOREIGN KEY (tootja) REFERENCES organisatsioon (organisatsiooni_kood) ON DELETE Cascade ON UPDATE Cascade,
	CONSTRAINT FK_kauba_kategooria FOREIGN KEY (kauba_kategooria) REFERENCES kauba_kategooria (kauba_kategooria_kood) ON DELETE Restrict ON UPDATE Restrict,
	CONSTRAINT FK_kauba_staatus FOREIGN KEY (kauba_staatus) REFERENCES kauba_staatus (kauba_staatuse_kood) ON DELETE Restrict ON UPDATE Restrict,
	CONSTRAINT FK_kauba_tarnija FOREIGN KEY (tarnija) REFERENCES organisatsioon (organisatsiooni_kood) ON DELETE Cascade ON UPDATE Cascade,
	CONSTRAINT FK_kauba_tootaja FOREIGN KEY (tootaja) REFERENCES tootaja (tootaja_kood) ON DELETE Restrict ON UPDATE Restrict
)
;

/* Create Primary Keys, Indexes, Uniques, Checks */

CREATE INDEX IXFK_tootaja_tootaja_roll ON tootaja (tootaja_rolli_kood ASC)
;

CREATE INDEX IXFK_tootaja_tootaja_staatus ON tootaja (tootaja_staatuse_kood ASC)
;

CREATE INDEX INDEX_tootaja_eesnimi ON tootaja (eesnimi ASC)
;

CREATE INDEX INDEX_tootaja_perenimi ON tootaja (perenimi ASC)
;

CREATE INDEX IXFK_organisatsioon_organisatsiooni_tyyp ON organisatsioon (organisatsiooni_tyybi_kood ASC)
;

CREATE INDEX FK_organisatsiooni_riik ON organisatsioon (riik ASC)
;

CREATE INDEX FK_kauba_kategooria ON kaup (kauba_kategooria ASC)
;

CREATE INDEX FK_kauba_staatus ON kaup (kauba_staatus ASC)
;

CREATE INDEX FK_kauba_tarnija ON kaup (tarnija ASC)
;

CREATE INDEX FK_kauba_tootaja ON kaup (tootaja ASC)
;

CREATE INDEX FK_kauba_tootja ON kaup (tootja ASC)
;

CREATE INDEX IXFK_kauba_ylemkategooria ON kauba_kategooria (ylem_kategooria ASC)
;