Ako smo popili kafu, odmorili se, sad idemo svezui u nove radne pobede. evo je tabela i CONSTRAINTS koji nam trebaju. Od svih constraints, samo se jedan FK moze pokazati na ER dijagramu, pa vidimo da je i ER diagramming nekompletna tehnika. Time sto ste napravili ispravan ER dijagram NISTE ZAVRSILI projaktovanje baze podataka. POtrebni su i CHECK constraints, UNIQUE, poneki AK i super-kay, kao sto cemo upravo videti:
Code:
IF Object_ID('zPromenaStatusa') IS NOT NULL DROp TABLE zPromenaStatusa
;
CREATE TABLE zPromenaStatusa
(
BrojIndeksa int NOT NULL
, SkolskaGodina int NOT NULL
, NoviStatus varchar(15) NOT NULL
, StariStatus varchar(15) NULL
, DatumPromene datetime NOT NULL
, DastumStarogStatusa datetime NULL
, RazlogPromene varchar(15) NULL
)
;
-- Ogranicimo promene statusa na dozvoljene kombinacije:
ALTER TABLE zPromenaStatusa
ADD CONSTRAINT FK_zPromenaStatusa_ValidTransition FOREIGN KEY (StariStatus, NoviStatus)
REFERENCES zDozvoljenePromeneStatusa (Iz_Statusa,U_Status)
;
-- Svaka tabela treba da ima PK
ALTER TABLE zPromenaStatusa
ADD CONSTRAINT PK_zPromenaStatusa PRIMARY KEY (BrojIndeksa, SkolskaGodina, DatumPromene)
;
-- Ovim sprecavamo da se isti status unese nekoliko puta zaredom
ALTER TABLE zPromenaStatusa
ADD CONSTRAINT UNQ_zPromenaStatusa UNIQUE (BrojIndeksa, SkolskaGodina, DastumStarogStatusa)
;
-- Super key, trebace nam u sledecm koraku, PK + NoviStatus :
ALTER TABLE zPromenaStatusa
ADD CONSTRAINT SK_zPromenaStatusa UNIQUE (BrojIndeksa, SkolskaGodina, DatumPromene, NoviStatus)
;
-- Ovim garantujemo redosled - StariStatus u jednom redu mora odgovarati NoviStatus u nekom od prethodnih redova
ALTER TABLE zPromenaStatusa
ADD CONSTRAINT FK_Redosled
FOREIGN KEY (BrojIndeksa, SkolskaGodina, DastumStarogStatusa, StariStatus)
REFERENCES zPromenaStatusa (BrojIndeksa, SkolskaGodina, DatumPromene, NoviStatus)
;
-- iskoristili smo super key :-)
-- Ovim garantujemo da ce prvi red biti uvek 'Upisan'
ALTER TABLE zPromenaStatusa -- NoviStaus = Upisan =>
ADD CONSTRAINT ck_PrviStatus_Upisan_Staro_NULL
CHECK ( NOT (NoviStatus = 'Upisan') OR (StariStatus IS NULL AND DastumStarogStatusa IS NULL ) )
;
ALTER TABLE zPromenaStatusa -- NoviStaus = Upisan =>
ADD CONSTRAINT ck_Staro_NULL_PrviStatus_Upisan
CHECK ( NOT (StariStatus IS NULL AND DastumStarogStatusa IS NULL ) OR (NoviStatus = 'Upisan') )
;
-- Ovim garantujemo redosled
ALTER TABLE zPromenaStatusa -- NoviStaus = Upisan =>
ADD CONSTRAINT ck_StariDatumPreNovog
CHECK (DastumStarogStatusa<=DatumPromene)
;
Unesimo nekoliko test redova:
Code:
-- testiranmo:
-- DELETE zPromenaStatusa
SELECT * FROM zPromenaStatusa
-- Upisao se student:
INSERT INTO zPromenaStatusa
(
BrojIndeksa, SkolskaGodina
, NoviStatus, StariStatus
, DatumPromene, DastumStarogStatusa
, RazlogPromene
)
SELECT
BrojIndeksa = 270, SkolskaGodina = 78
, NoviStatus = 'Upisan', StariStatus = NULL
, DatumPromene = '19781001', DastumStarogStatusa = NULL
, RazlogPromene = NULL
;
-- Student diplomirao:
INSERT INTO zPromenaStatusa
(
BrojIndeksa, SkolskaGodina
, NoviStatus, StariStatus
, DatumPromene, DastumStarogStatusa
, RazlogPromene
)
SELECT
BrojIndeksa = 270, SkolskaGodina = 78
, NoviStatus = 'Diplomirao', StariStatus = 'Upisan'
, DatumPromene = '19850625', DastumStarogStatusa = '19781001'
, RazlogPromene = NULL
;
SELECT * FROM zPromenaStatusa
/*
BrojIndeksa SkolskaGodina NoviStatus StariStatus DatumPromene DastumStarogStatusa RazlogPromene
----------- ------------- --------------- --------------- ----------------------- ----------------------- ---------------
270 78 Upisan NULL 1978-10-01 00:00:00.000 NULL NULL
270 78 Diplomirao Upisan 1985-06-25 00:00:00.000 1978-10-01 00:00:00.000 NULL
(2 row(s) affected)
*/
Da probamo neke greske da napravimo:
Code:
-- Da probamo gresku, da ispisemo students 27 godina posle diplomiranja:
INSERT INTO zPromenaStatusa
(
BrojIndeksa, SkolskaGodina
, NoviStatus, StariStatus
, DatumPromene, DastumStarogStatusa
, RazlogPromene
)
SELECT
BrojIndeksa = 270, SkolskaGodina = 78
, NoviStatus = 'Ispisan', StariStatus = 'Diplomirao'
, DatumPromene = '20130124', DastumStarogStatusa = '19850625'
, RazlogPromene = NULL
;
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_zPromenaStatusa_ValidTransition". The conflict occurred in database "Dejan", table "dbo.zDozvoljenePromeneStatusa".
The statement has been terminated.
-- Iz statusa 'Diplomirao' ne moze se preci u 'Ispisan' jer takva kombinacija ne postoji u tabeli zDozvoljenePromeneStatusa.
Upis u tabelu zPromeneStatusa je sada prilicno komplikovana stvar i neke je gresk tesko napraviti, cak i kada se trudimo. Evo nekoliko primera.
Code:
-- Da dodamo jedn red izmedju Upisa i Diplomiranja za naseg studenta:
INSERT INTO zPromenaStatusa
(
BrojIndeksa, SkolskaGodina
, NoviStatus, StariStatus
, DatumPromene, DastumStarogStatusa
, RazlogPromene
)
SELECT
BrojIndeksa = 270, SkolskaGodina = 78
, NoviStatus = 'Ispisan', StariStatus = 'Upisan'
, DatumPromene = '19801001', DastumStarogStatusa = '1978-10-01'
, RazlogPromene = 'Asdfg'
;
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UNQ_zPromenaStatusa'. Cannot insert duplicate key in object 'dbo.zPromenaStatusa'.
The statement has been terminated.
-- Znaci, nemoguce je naknadno ddoavati promene imedju postojecih!
-- Prvi status mora bit 'Upisan' i tada StariSTatus i DastumStarogStatusa moraju biti NULL
INSERT INTO zPromenaStatusa
(
BrojIndeksa, SkolskaGodina
, NoviStatus, StariStatus
, DatumPromene, DastumStarogStatusa
, RazlogPromene
)
SELECT
BrojIndeksa = 100, SkolskaGodina = 2005
, NoviStatus = 'ISpisan', StariStatus = NULL
, DatumPromene = '19801001', DastumStarogStatusa = NULL
, RazlogPromene = 'Asdfg'
;
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_PrviStatus_Upisan_Staro_NULL". The conflict occurred in database "Dejan", table "dbo.zPromenaStatusa".
The statement has been terminated.
Neko ce primetiti da je u pitanju de-normalizacija. Jeste, ali je strogo kontrolisana i daje mnogo robustnije resenje.
Modelovanje BP SS
