DROP SEQUENCE zchu_seq; DROP SEQUENCE doc_seq; CREATE SEQUENCE zchu_seq; -- Vytvoří posloupnost pro sdílený prim. key CREATE SEQUENCE doc_seq; -- Vytvoří posloupnost pro dokumenty. DROP TABLE MCHU; CREATE TABLE MCHU( id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'), cis INTEGER NULL UNIQUE, status CHAR(1) DEFAULT 'n' CHECK (status IN ('n','v','z') AND ( (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))), zalozeno DATE DEFAULT current_date NULL, vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno), zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno), rozloha NUMERIC(11,2) NULL CHECK ( (status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))), nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''), vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min < 1602) AND (status = 'v' >>> vyska_min IS NOT NULL)), vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max < 1602) AND (status = 'v' >>> vyska_max IS NOT NULL)), stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) CHECK (status = 'v' >>> stav_ochr IS NOT NULL), charakter_uzemi VARCHAR(250) NULL, lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL) ); DROP TABLE VCHU; CREATE TABLE VCHU( id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'), cis INTEGER NULL UNIQUE, status CHAR(1) DEFAULT 'n' CHECK (status IN ('n','v','z') AND ( (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))), zalozeno DATE DEFAULT current_date NULL, vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno), zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno), rozloha NUMERIC(11,2) NULL CHECK ( (status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))), nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''), vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min < 1602) AND (status = 'v' >>> vyska_min IS NOT NULL)), vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max < 1602) AND (status = 'v' >>> vyska_max IS NOT NULL)), stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) CHECK (status = 'v' >>> stav_ochr IS NOT NULL), charakter_uzemi VARCHAR(250) NULL, lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL) ); DROP TABLE PS; CREATE TABLE PS ( id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'), cis INTEGER NULL UNIQUE, puv_data BOOLEAN NOT NULL DEFAULT false CHECK (puv_data OR zalozeno IS NOT NULL), status CHAR(1) DEFAULT 'n' CHECK (status IN ('n','v','z') AND ( (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))), zalozeno DATE DEFAULT current_date NULL, vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno), zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno) ); DROP TABLE ZCHU_RezervacniKnihy; CREATE TABLE ZCHU_RezervacniKnihy ( id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'), -- teoreticky by bylo možné wf i na dokumentech v RK idoo INTEGER NOT NULL, -- id objektu ochrany taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')), -- bez parků isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''), typ CHAR(2) NOT NULL REFERENCES TypDocRK(kod), autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''), autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''), nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''), poznamka VARCHAR(300) NULL, rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani < EXTRACT(year FROM current_date)), zalozeno DATE DEFAULT current_date NOT NULL, vyrazeno DATE NULL CHECK (vyrazeno > zalozeno), isbn CHAR(12) NULL CHECK (check_ISBN(isbn)), zalozil INTEGER NOT NULL REFERENCES AOPKUzivatele(id), vyradil INTEGER NULL REFERENCES AOPKUzivatele(id) CHECK(vyradil IS NOT NULL AND vyrazeno IS NOT NULL), url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+') );