Mozgalica za Jun 2012

[ Zidar @ 25.05.2012. 21:56 ] @

neko se zalio da je majska mozgalica laka. Ebvo nesto za nijansu teze.

Data je tabela Guests (gosti, verovatno u hotelu)

Code:

CREATE TABLE Guests
(PersonID int NOT NULL PRIMARY KEY
, Vegetarian tinyint NOT NULL CONSTRAINT ckVegetarian CHECK (Vegetarian IN (0,1))
, Religion varchar(1) NOT NULL CONSTRAINT ckReligion CHECK (Religion IN ('A','B','C','D'))
, Meal varchar(4) CONSTRAINT ckMeal CHECK (Meal IN ('VEG','PORK','BEEF','CHICKEN'))
)

Meal je vrsta jela koja se dodeljuje svakom gostu. Data su sledeca tri dodatna uslova:

Kada je Vegetarian = 1 ona Meal sme da bude samo 'VEG' i nista drugo
Samo ko je Vegetarian = 1 dobija Meal 'VEG', ostali nece ni da cuju
Osobe (PersonID) cija je religija 'A' ili 'B' ne jedu prasetinu pa im se ne sme dati Meal = 'PORK'

Dozvoljene su CHECK constraint ali i menjanje strukture table, uvodjenje novih table, FK, sta god hocete.

Resenje testirati na sledecim podacima. Uradite INSERT, pa onda pokusajte sa UPDATE da dodelite razlicite MEal vrednosti razlicitim osobama.

Code:

INSERT INTO Guests (PersonID, Vegetarian, Religion, Meal)
SELECT 1,1,'A', NULL
UNION
SELECT 2,0,'A', NULL
UNION
SELECT 3,1,'B', NULL
UNION
SELECT 4,0,'B', NULL
UNION
SELECT 5,1,'C', NULL
UNION
SELECT 6,0,'C', NULL
UNION
SELECT 7,1,'D', NULL
UNION
SELECT 8,0,'D', NULL

Cujemo se u ponedeljak

[ Igor Gajic @ 26.05.2012. 10:23 ] @

SQL SERVER 2008 R2

Jedno resenje preko constraints. U tabeli je samo izmenjen Meal da bude VARCHAR(7),

Code (sql):

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Guests]') AND TYPE IN (N'U'))

DROP TABLE Guests;

GO

 CREATE TABLE Guests

 (PersonID INT NOT NULL PRIMARY KEY

 , Vegetarian tinyint NOT NULL CONSTRAINT ckVegetarian CHECK (Vegetarian IN (0,1))

 , Religion VARCHAR(1) NOT NULL CONSTRAINT ckReligion  CHECK (Religion IN ('A','B','C','D'))

 , Meal VARCHAR(7) CONSTRAINT ckMeal CHECK (Meal IN ('VEG','PORK','BEEF','CHICKEN')),

 CONSTRAINT ck_Vegie CHECK(

                                   (Vegetarian = 1 AND (Meal = 'VEG' OR Meal IS NULL))

                                   OR (Vegetarian = 0 AND (Meal IN ('PORK','BEEF','CHICKEN')) OR Meal IS NULL)

                                   ),

 CONSTRAINT ck_NoPigies CHECK(Religion IN ('A','B') AND (Meal <> 'PORK' OR Meal IS NULL)

                                   OR Religion IN ('C','D'))

 );

 INSERT INTO Guests (PersonID, Vegetarian, Religion, Meal)

 SELECT 1,1,'A', NULL

 UNION 

 SELECT 2,0,'A', NULL

 UNION 

 SELECT 3,1,'B', NULL

 UNION 

 SELECT 4,0,'B', NULL

 UNION

 SELECT 5,1,'C', NULL

 UNION 

 SELECT 6,0,'C', NULL

 UNION 

 SELECT 7,1,'D', NULL

 UNION 

 SELECT 8,0,'D', NULL;

SELECT * FROM Guests;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 1;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 1;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 1;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 1;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 2;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 2;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 2;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 2;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 3;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 3;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 3;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 3;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 4;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 4;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 4;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 4;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 5;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 5;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 5;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 5;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 6;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 6;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 6;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 6;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 7;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 7;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 7;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 7;

UPDATE Guests SET Meal = 'VEG' WHERE PersonID = 8;

UPDATE Guests SET Meal = 'PORK' WHERE PersonID = 8;

UPDATE Guests SET Meal = 'BEEF' WHERE PersonID = 8;

UPDATE Guests SET Meal = 'CHICKEN' WHERE PersonID = 8;

[ djoka_l @ 26.05.2012. 10:25 ] @

Code (sql):

ALTER TABLE GUESTS ADD CONSTRAINT

CKMEAL CHECK (meal IS NULL OR vegetarian||religion||meal IN (

'1AVEG','1BVEG','1CVEG','1DVEG',

'0ABEEF','0ACHICKEN',

'0BBEEF','0BCHICKEN',

'0CPORK','OCBEEF','0CCHICKEN',

'0DPORK','0DBEEF','0DCHICKEN'));

Cela CREATE TABLE naredba (Oracle) uz primedbu Igora da meal treba da bude varchar2(7)

Code (sql):

CREATE TABLE  GUESTS 

   ( PERSONID NUMBER NOT NULL, 

     VEGETARIAN NUMBER(1) NOT NULL, 

     RELIGION VARCHAR2(1) NOT NULL, 

     MEAL VARCHAR2(7), 

      CONSTRAINT CKVEGETARIAN CHECK ( Vegetarian IN (0,1) ), 

      CONSTRAINT CKRELIGION CHECK ( Religion IN ('A','B','C','D') ), 

      CONSTRAINT PKGUESTS PRIMARY KEY (PERSONID), 

      CONSTRAINT CKMEAL CHECK ( meal IS NULL OR (vegetarian||religion||meal) IN (

'1AVEG','1BVEG','1CVEG','1DVEG',

'0ABEEF','0ACHICKEN',

'0BBEEF','0BCHICKEN',

'0CPORK','OCBEEF','0CCHICKEN',

'0DPORK','0DBEEF','0DCHICKEN'))

   );

_{[Ovu poruku je menjao djoka_l dana 26.05.2012. u 11:35 GMT+1]}

[ Zidar @ 28.05.2012. 15:13 ] @

Bravo, odgovori su stigli brze nego sto sam ocekivao (vikend, eh). Ima li neko ideju kako se ovo moze resiti na jos neki nacin, pomocu FK i/ili dekompozicije? Ili jos neki nacin da se napisu constraints?

Licno mi se resenja sa CHECK dopadaju u ovakvim slucajevima, iako ih je nekad tesko razumeti. Igor i Djoka su obavili odlican posao, ali za one koji nisu mogli sami da dodju do resenja citanje ponudjenih resenja nece doneti mnogo pomoci i uspeha u buducnosti. Pokusacu da dam generalnu ideju kako se ovakvi slucajevi resavaju, kako i zasto, i nadam se da ce CHECK constraints biti bar malkice citljiviji.

Ovde imamo uslove topa "Ako P onda Q", matematicki kazano P => Q

Tri uslova kja smo zadali, mogu s eiskazati ovako:
Ako je nek vegatarijanac, onda dobija Meal = 'Veg'
Ko nije vegatarijanac, ne moze dobiti Meal = 'Veg'
Ko ima religiju 'A' ili 'B', ne sme dobiti Meal = 'Pork'

To mozemo da napisemo preqo P i Q, ovako

P = "Osobe je vegeterijanac", Q = "Meal je 'Veg'", P=> Q postaje (Osobe je vegeterijanac) => (Meal je 'Veg')

P = "Osoba nije vegatarijanac", Q = "Meal ne sme biti 'Veg'", P=>Q postaje "Osoba nije vegatarijanac" => ( "Meal ne sme biti 'Veg'")

P = "Religija IN ('A','B')", Q = "Meal ne sme biti 'Pork'", P=> Q postaje "Religija IN ('A','B')", => "Meal ne sme biti 'Pork'"

Sva tri uslova su dakle P => Q. Kako napsiati CHECK za P=>Q? Ne zanm ni za jedan RDBMS gde postoji operator =>. Postoji AND , OR i NOT. Veliko pitanje je "Kako naisati P => Q pomocu AND, OR i NOT?" Na srecu, odgovor postoji, moze se naci cak i na vikipediji.

(P => Q) <=> ( (NOT P) OR Q)

Evo ga dakle resenje, P=> izrazeno preko NOT i OR! Ovo s enije predavalo u skoli kad sam ja to ucio, davne 1974 godine i prvi put sam video ovu tautologiju u knjizi Joe Celko's SQL for Smarties, pod nazivo, 'Smistru rule'. Posle sam to video kao deo matematicke logike u knjizi Applied MAthematics for Database professionals, de Haan/Koopelars, a onda i na wikipediji.

Ovako su napisane cinstraints:

Code:

"     Vegetarian = 1   => Meal = 'Veg'" mose da se napise kao:
NOT (Vegetarian = 1)  OR Meal = 'Veg'

-- i dobijamo:
ALTER TABLE Guests ADD CONSTRAINT ckVeggies
        CHECK ( NOT (Vegetarian = 1) OR Meal = 'Veg')

-- " Vegetarian <> 1 => Meal <> 'Veg'" moze da se napise kao
  NOT Vegetarian <> 1 OR meal <> 'Veg', ili ovako
  Vegatarin = 1 OR  Meal <> 'Veg', pa dobijamo

ALTER TABLE Guests ADD CONSTRAINT ckVegForVeetariansOnly
        CHECK ( Vegetarian = 1 OR  Meal <> 'Veg' )

-- Takodje,
"      Religion IN ('A','B') => (Meal <> 'Pork') " mozemo da psiemo
  NOT (Religion IN ('A','B')) OR (Meal <> 'Pork')
sto je isto sto i
NOT (Religion IN ('A','B') AND Meal = 'Pork')
pa dobijamo ovo:

ALTER TABLE Guests ADD CONSTRAINT ckNoPork
        CHECK ( NOT (Religion IN ('A','B') AND Meal = 'Pork'))

Ja sam izabrao da uprostim konacan izraz unotar CHECK, a mogao sam da napisem bilo koji od izraza kroz koje sam prosao. Tako sam dosao do 3 CHECK izraza, z a3 uslova. Primetita da dva uslova za vegatarijance us tvari kazu:

Ako i samo ako je neko vegetarijanc, ta osoba dobija Meal = 'veg'

ili

Vegetarijanac = 1 <=> Meal = 'Veg'
sto se opet prevodi na dve imlikacije:

Vegetarijanac = 1 => Meal = 'Veg'
i
Vegetarijanac = 1 <= Meal = 'Veg'

Ako pogledata sada pazljivo CHECK koji je dao Igor, prepoznacete ova tri uslova. Primetita da u mojim uslovima ne pominjemo NULL nigde. Ako kolona dozvoljava NULL vrednosti, onda su sve CHECK constraints tacne po definiciji za sve NULL vrednosti, pa ih ne moramo navoditi u specifikaciji CHECK uslova.

Na kraju, lepo je sto znamo da predstavimo P=>Q, ali treba biti obazriv. Ako imate puno P=>Q u strukturi tabele, onda verovatno nesto nije u redu sa dizajnom tabele. U konkretnom slucaju, deluje da je sve normalizovano OK, ali nije. Ko moze da vidi kako bi se ovo resilo uz pomoc FK i mozda razbijanja nekih kolona na subtipove? U tom slucaju nam ne trebaju CHECK uslovi, barem teorijski, a mozda i prakticno.

Za svaki slucaj, evo ponovo kod za kreiranje tablele i testiranje:

Code:

IF Object_ID('Guests') IS NOT NULL DROP TABLE  Guests
;
CREATE TABLE Guests
(PersonID int NOT NULL PRIMARY KEY
, Vegetarian tinyint NOT NULL CONSTRAINT ckVegetarian CHECK (Vegetarian IN (0,1))
, Religion varchar(1) NOT NULL CONSTRAINT ckReligion  CHECK (Religion IN ('A','B','C','D'))
, Meal varchar(7) CONSTRAINT ckMeal CHECK (Meal IN ('VEG','PORK','BEEF','CHICKEN'))
)
;
INSERT INTO Guests (PersonID, Vegetarian, Religion, Meal)
SELECT 1,1,'A', NULL
UNION
SELECT 2,0,'A', NULL
UNION
SELECT 3,1,'B', NULL
UNION
SELECT 4,0,'B', NULL
UNION
SELECT 5,1,'C', NULL
UNION
SELECT 6,0,'C', NULL
UNION
SELECT 7,1,'D', NULL
UNION
SELECT 8,0,'D', NULL
;
ALTER TABLE Guests ADD CONSTRAINT ckVeggies
        CHECK ( NOT (Vegetarian = 1) OR Meal = 'Veg')
;
ALTER TABLE Guests ADD CONSTRAINT ckVegForVeetariansOnly
        CHECK ( Vegetarian = 1 OR  Meal <> 'Veg' )
;
ALTER TABLE Guests ADD CONSTRAINT ckNoPork
        CHECK ( NOT (Religion IN ('A','B') AND Meal = 'Pork'))
;
SELECT * FROM Guests

   PersonID Vegetarian Religion Meal
----------- ---------- -------- -------
          1          1 A        NULL
          2          0 A        NULL
          3          1 B        NULL
          4          0 B        NULL
          5          1 C        NULL
          6          0 C        NULL
          7          1 D        NULL
          8          0 D        NULL

-- TESTIRANJE
-- This should work:
UPDATE Guests SET Meal = 'Veg' WHERE PersonID = 1    --  Person 1 is a vegetarion
-- It did work, (1 row(s) affected)

-- Thsi should fail:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 1
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckVeggies". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.

-- This should work:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 2    -- Person 2  hs relihgion A
-- Beef is OK for person 2, so (1 row(s) affected)

-- This should fail:
UPDATE Guests SET Meal = 'Pork' WHERE PersonID = 2
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckNoPork". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.

-- This should work:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 6    -- religion C, PORK OK, not a vegatarian
-- (1 row(s) affected)

UPDATE Guests SET Meal = 'Veg' WHERE PersonID = 6
-- (1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckVegForVeetariansOnly". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.

[ djoka_l @ 28.05.2012. 15:25 ] @

Citat:

Sva tri uslova su dakle P => Q. Kako napsiati CHECK za P=>Q? Ne zanm ni za jedan RDBMS gde postoji operator =>. Postoji AND , OR i NOT. Veliko pitanje je "Kako naisati P => Q pomocu AND, OR i NOT?" Na srecu, odgovor postoji, moze se naci cak i na vikipediji.

(P => Q) <=> ( (NOT P) OR Q)

Evo ga dakle resenje, P=> izrazeno preko NOT i OR! Ovo s enije predavalo u skoli kad sam ja to ucio, davne 1974 godine i prvi put sam video ovu tautologiju u knjizi Joe Celko's SQL for Smarties, pod nazivo, 'Smistru rule'. Posle sam to video kao deo matematicke logike u knjizi Applied MAthematics for Database professionals, de Haan/Koopelars, a onda i na wikipediji.

Ne znam kako ovo nisi učio. To što si napisao se zove disjunktivna normalna forma (http://en.wikipedia.org/wiki/Disjunctive_normal_form) i radi se iz matematičke logike u srednjoj školi. Radi se i na fakultetu iz arhitekture računara ili digitalne elektronike, kada, na primer, treba da napraviš neki dekoder. Napravi se istinitosna tablica (kako ulazi treba da se preslikaju na izlaze), onda se ta tablica predstavi u disjunktivnoj ili konjunktivnoj normalnoj formi, onda se uprosti, pa se nacrta šema...

[ Zidar @ 28.05.2012. 15:41 ] @

Djoko, ja sam u sredjnoj skoli ucio logiku 1974 godine (kad smo pobedili Zaire 9:0 na prvenstvu sveta u Nemackoj i kad je Katalinski dao spanicma cuveni go koji nas je odveo na svetsko prvenstvo) i veruj mi tada se to nije ucilo

A na fakultetu sam ucio betonske konstrukcije i gradjenje pristanista, nisam imao arhitekturu racunara, pa me i tu promasilo

A na Spectrumu i inije bilo nekih baza podataka

Sve u svemu, bravo za skolski sistem, bar se nesto promenilo nabolje i uci se nesto sto se pre 40 godina nije ucilo.

Uglavnom, gledajuci praksu s obe strane Atlanskog okeana, izgleda da je velika vecina to takodje promasila, baska sto u knjigama 'Teach yourself SQL..' ovoga nema, a nema ni u ozbiljnijim knjigama. Svi nekeko misle da 'znati SQL' znaci 'znati pisati komplikovane kverije', pa se ovaj deo logike potpuno zanemaruje. Ko bre jos postavlja constraints na tabelu... Uostalom, vidi koliko se ljudi javilo sa odgovorima na mozgalicu...

U svakom slucaju, hvala za link na wikipediji, ja sma imao drugi link, vidis, ima na vise mesta i opet ga ne vidimo

Na ovom linku pise P->Q <=> NOT P OR Q, dole gde je 'Applications' http://en.wikipedia.org/wiki/Truth_tables#Logical_implication

[ djoka_l @ 28.05.2012. 16:26 ] @

Uzgred, ovako se dobija da je P=>Q <=> NOT P OR Q

Iz istinitosne tablice za => napiše se donji izraz

dalje ću da OR predstavim kao + a AND kao puta (neću ga pisati)

zakon distributivnoti AND prema OR - AB+AC=A(B+C)

zakon isključenja trećeg - A OR NOT A = 1

zakon neutralnosti 1 prema operaciji AND - 1 AND A = A

zakon distributivnosti OR prema AND - A OR (B AND C) = (A OR B) AND (A OR C)

opet zakon isključenja trećeg

i na kraju rezultat

[ Igor Gajic @ 28.05.2012. 23:32 ] @

Ponovo SQL SERVER 2008 R2

Resenje zasnovano na FK's

Code (sql):

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Guests]') AND TYPE IN (N'U'))

DROP TABLE Guests;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Combinations]') AND TYPE IN (N'U'))

DROP TABLE Combinations;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Meal]') AND TYPE IN (N'U'))

DROP TABLE Meal;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Religion]') AND TYPE IN (N'U'))

DROP TABLE Religion;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EaterType]') AND TYPE IN (N'U'))

DROP TABLE EaterType;

GO

CREATE TABLE Meal(

     MealID INT NOT NULL PRIMARY KEY,

     MealName VARCHAR(7)

);

INSERT INTO Meal VALUES (1, 'VEG');

INSERT INTO Meal VALUES (2, 'PORK');

INSERT INTO Meal VALUES (3, 'BEEF');

INSERT INTO Meal VALUES (4, 'CHICKEN');

CREATE TABLE Religion(

     ReligionID INT NOT NULL PRIMARY KEY,

     ReligionName VARCHAR(1)

);

INSERT INTO Religion VALUES (1, 'A');

INSERT INTO Religion VALUES (2, 'B');

INSERT INTO Religion VALUES (3, 'C');

INSERT INTO Religion VALUES (4, 'D');

CREATE TABLE EaterType

(

     EaterTypeID TINYINT NOT NULL PRIMARY KEY,

     EaterTypeName VARCHAR(20),

);

INSERT INTO EaterType VALUES (0, 'Other');

INSERT INTO EaterType VALUES (1, 'Vegetarian');

CREATE TABLE Combinations(

     CombinationID INT NOT NULL,

     EaterTypeID TINYINT NOT NULL,

     ReligionID INT NOT NULL,

     MealID INT,

     FOREIGN KEY (MealID) REFERENCES Meal(MealID),

     FOREIGN KEY (ReligionID) REFERENCES Religion(ReligionID),

     FOREIGN KEY (EaterTypeID) REFERENCES EaterType(EaterTypeID),

     PRIMARY KEY(EaterTypeID, ReligionID, MealID)

);  

-- Vegeterians = 1 => Meal = VEG

INSERT INTO Combinations VALUES (1, 1, 1, 1);

INSERT INTO Combinations VALUES (2, 1, 2, 1);

INSERT INTO Combinations VALUES (3, 1, 3, 1);

INSERT INTO Combinations VALUES (4, 1, 4, 1);

-- Religion IS A,B => Meal != PORK

INSERT INTO Combinations VALUES (5, 0, 1, 3);

INSERT INTO Combinations VALUES (6, 0, 1, 4);

INSERT INTO Combinations VALUES (7, 0, 2, 3);

INSERT INTO Combinations VALUES (8, 0, 2, 4);  

-- Religion C,D => Meal != VEG

INSERT INTO Combinations VALUES (9, 0, 3, 2);

INSERT INTO Combinations VALUES (10, 0, 3, 3);

INSERT INTO Combinations VALUES (11, 0, 3, 4);

INSERT INTO Combinations VALUES (12, 0, 4, 2);

INSERT INTO Combinations VALUES (13, 0, 4, 3);

INSERT INTO Combinations VALUES (14, 0, 4, 4);  

CREATE TABLE Guests

(

     PersonID INT NOT NULL PRIMARY KEY,

     EaterTypeID TINYINT NOT NULL,

     ReligionID INT NOT NULL,

     MealID INT,

     FOREIGN KEY (MealID) REFERENCES Meal(MealID),

     FOREIGN KEY (ReligionID) REFERENCES Religion(ReligionID),

     FOREIGN KEY (EaterTypeID) REFERENCES EaterType(EaterTypeID),

     FOREIGN KEY (EaterTypeID, ReligionID, MealID) REFERENCES Combinations(EaterTypeID, ReligionID, MealID)

);

  -- NULL ROWS

 INSERT INTO Guests (PersonID, EaterTypeID, ReligionID, MealID)

 SELECT 1,1,1, NULL

 UNION 

 SELECT 2,0,1, NULL

 UNION 

 SELECT 3,1,2, NULL

 UNION 

 SELECT 4,0,2, NULL

 UNION

 SELECT 5,1,3, NULL

 UNION 

 SELECT 6,0,3, NULL

 UNION 

 SELECT 7,1,4, NULL

 UNION 

 SELECT 8,0,4, NULL; 

-- test cases

UPDATE Guests SET MealID = 1 WHERE PersonID = 1;

UPDATE Guests SET MealID = 2 WHERE PersonID = 1;

UPDATE Guests SET MealID = 3 WHERE PersonID = 1;

UPDATE Guests SET MealID = 4 WHERE PersonID = 1;

UPDATE Guests SET MealID = 1 WHERE PersonID = 2;

UPDATE Guests SET MealID = 2 WHERE PersonID = 2;

UPDATE Guests SET MealID = 3 WHERE PersonID = 2;

UPDATE Guests SET MealID = 4 WHERE PersonID = 2;

UPDATE Guests SET MealID = 1 WHERE PersonID = 3;

UPDATE Guests SET MealID = 2 WHERE PersonID = 3;

UPDATE Guests SET MealID = 3 WHERE PersonID = 3;

UPDATE Guests SET MealID = 4 WHERE PersonID = 3;

UPDATE Guests SET MealID = 1 WHERE PersonID = 4;

UPDATE Guests SET MealID = 2 WHERE PersonID = 4;

UPDATE Guests SET MealID = 3 WHERE PersonID = 4;

UPDATE Guests SET MealID = 4 WHERE PersonID = 4;

UPDATE Guests SET MealID = 1 WHERE PersonID = 5;

UPDATE Guests SET MealID = 2 WHERE PersonID = 5;

UPDATE Guests SET MealID = 3 WHERE PersonID = 5;

UPDATE Guests SET MealID = 4 WHERE PersonID = 5;

UPDATE Guests SET MealID = 1 WHERE PersonID = 6;

UPDATE Guests SET MealID = 2 WHERE PersonID = 6;

UPDATE Guests SET MealID = 3 WHERE PersonID = 6;

UPDATE Guests SET MealID = 4 WHERE PersonID = 6;

UPDATE Guests SET MealID = 1 WHERE PersonID = 7;

UPDATE Guests SET MealID = 2 WHERE PersonID = 7;

UPDATE Guests SET MealID = 3 WHERE PersonID = 7;

UPDATE Guests SET MealID = 4 WHERE PersonID = 7;

UPDATE Guests SET MealID = 1 WHERE PersonID = 8;

UPDATE Guests SET MealID = 2 WHERE PersonID = 8;

UPDATE Guests SET MealID = 3 WHERE PersonID = 8;

UPDATE Guests SET MealID = 4 WHERE PersonID = 8;

Naizgled komplikovanije, ali omogucuje vecu fleksibilnost pogotovo u slucaju kada se tabela Meals cesto dopunjuje/menja.

[ Zidar @ 29.05.2012. 13:51 ] @

Sta reci nego - bravo za Igora. Vredi ponoviti:

Citat:

Naizgled komplikovanije, ali omogucuje vecu fleksibilnost pogotovo u slucaju kada se tabela Meals cesto dopunjuje/menja.

Kljucna je tabela Combinations, gde je Igor definisao sve dozvoljene kombinacje za meal, Religiion i EaterType. Veoma dobra opaska da je resenje sa FK pogodno kad se meals ili bilo koji drugi parametar menjaju. Na srecu, u praksi imamo cesto staticne uslove, ili tkave da se dugo vremena ne menjaju, pa mozemo pribeci i nesto jednostavnijim resenjima, uz minimalnu dozu denormalizacije. Stos je da treba nekako proceniti da li su uslovi taticni ili se menjaju, a to dolazi sa iskustvom.

Ja sam imao resenje ali ni izbliza ovako dobro kao Igorovo. Ako bude vremena, prilozicu, ali samo zato da se ukaze na ono sto ne valja u resenju.

Za Igorovo resenje jos jednom, svako postovanje.

Ima li jos neka varijanta, dopuna, poboljsanje? Cilj mozgalica je ne da nadjemo najbrze i najbolje resenje, nego sto vise mogucih resenja, pa da sutra imamo veci izbor, da mozemo pokriti razlicite situacije.

[ sosingus @ 31.05.2012. 09:41 ] @

Evo jedno drugacije resenje u Firebird 2.1.3 - sa domains, exceptions i triggers (umesto check constraint). Generalno trigeri su ok ako uticu samo na tabelu za koju su vezani, ili za logovanje. Meni su za ovakvu situaciju korisni jer su citljiviji.
PersonId je Person_ID jer firebird nije case sensitive. Drop if exists i slicno u Firebird-u ne postoji tako da je taj deo malo neobican...ali izvodljiv.

DDL

Code:

SET TERM ^;

EXECUTE BLOCK
AS
BEGIN

    IF (EXISTS (SELECT * FROM rdb$relations WHERE rdb$relation_name = 'GUESTS')) THEN
      EXECUTE STATEMENT 'drop table guests';

    IF (EXISTS (SELECT * FROM rdb$exceptions WHERE rdb$exception_name = 'E_VEGS_GET_VEG_ONLY') ) THEN
      EXECUTE STATEMENT 'drop exception E_VEGS_GET_VEG_ONLY';

    IF (EXISTS (SELECT * FROM rdb$exceptions WHERE rdb$exception_name = 'E_EVERYONE_BUT_VEGS') ) THEN
      EXECUTE STATEMENT 'drop exception E_EVERYONE_BUT_VEGS';

    IF (EXISTS (SELECT * FROM rdb$exceptions WHERE rdb$exception_name = 'E_NO_PORK') ) THEN
      EXECUTE STATEMENT 'drop exception E_NO_PORK';

    IF (EXISTS (SELECT * FROM rdb$fields WHERE rdb$field_name = 'D_VEGETARIAN') ) THEN
      EXECUTE STATEMENT 'drop domain D_VEGETARIAN';

    IF (EXISTS (SELECT * FROM rdb$fields WHERE rdb$field_name = 'D_RELIGION') ) THEN
      EXECUTE STATEMENT 'drop domain D_RELIGION';

    IF (EXISTS (SELECT * FROM rdb$fields WHERE rdb$field_name = 'D_MEAL') ) THEN
      EXECUTE STATEMENT 'drop domain D_MEAL';

END^

SET TERM ;^

CREATE DOMAIN D_VEGETARIAN AS SMALLINT
  CHECK (VALUE IN (0,1));

CREATE DOMAIN D_RELIGION AS VARCHAR(1)
  CHECK (VALUE IN ('A','B','C','D'));
CREATE DOMAIN D_MEAL AS VARCHAR(7)
  CHECK (VALUE IN ('VEG','PORK','BEEF','CHICKEN'));

CREATE TABLE GUESTS (
    PERSON_ID INTEGER NOT NULL,
    VEGETARIAN D_VEGETARIAN NOT NULL,
    RELIGION D_RELIGION NOT NULL,
    MEAL D_MEAL);

ALTER TABLE guests
ADD CONSTRAINT PK_GUESTS
PRIMARY KEY (PERSON_ID);

CREATE EXCEPTION E_VEGS_GET_VEG_ONLY 'Vegeterijancima se sluzi samo VEG jelo.';
CREATE EXCEPTION E_EVERYONE_BUT_VEGS 'Gosti koji nisu vegeterijanci ne zele VEG jelo.';
CREATE EXCEPTION E_NO_PORK 'Gostu religija ne dozvoljava upotrebu svinjetine.';

SET TERM ^ ;

CREATE OR ALTER trigger guests_bi0 for guests
active before insert or update position 0
AS
begin

  IF ((new.vegetarian = 1) AND (new.meal <> 'VEG')) THEN
    EXCEPTION e_vegs_get_veg_only;

  IF ((new.vegetarian = 0) AND (new.meal = 'VEG')) THEN
    exception e_everyone_but_vegs;

  IF (new.religion in ('A', 'D') AND (new.meal = 'PORK')) THEN
    EXCEPTION e_no_pork;

end
^

SET TERM ; ^

Podaci i test

Code:

INSERT INTO Guests (Person_ID, Vegetarian, Religion, Meal)
SELECT 1,1,'A', NULL
FROM rdb$database
UNION
SELECT 2,0,'A', NULL
FROM rdb$database
UNION
SELECT 3,1,'B', NULL
FROM rdb$database
UNION
SELECT 4,0,'B', NULL
FROM rdb$database
UNION
SELECT 5,1,'C', NULL
FROM rdb$database
UNION
SELECT 6,0,'C', NULL
FROM rdb$database
UNION
SELECT 7,1,'D', NULL
FROM rdb$database
UNION
SELECT 8,0,'D', NULL
FROM rdb$database;

-- TESTIRANJE
-- This should work:
UPDATE Guests SET Meal = 'VEG' WHERE Person_ID = 1;    --  Person 1 is a vegetarion
-- It did work, (1 row(s) affected)

-- This should fail:
/*******************************************************************************
The next statement causes the following error:

E_VEGS_GET_VEG_ONLY.
Vegeterijancima se sluzi samo VEG jelo.
At trigger 'GUESTS_BI0' line: 6, col: 53.
*******************************************************************************/
UPDATE Guests SET Meal = 'BEEF' WHERE Person_ID = 1;

-- This should work:
UPDATE Guests SET Meal = 'BEEF' WHERE Person_ID = 2;    -- Person 2  hs relihgion A
-- Beef is OK for person 2, so (1 row(s) affected)

-- This should fail:
/*******************************************************************************
The next statement causes the following error:

E_NO_PORK.
Gostu religija ne dozvoljava upotrebu svinjetine.
At trigger 'GUESTS_BI0' line: 12, col: 59.
*******************************************************************************/
UPDATE Guests SET Meal = 'PORK' WHERE Person_ID = 2;

-- This should work:
UPDATE Guests SET Meal = 'BEEF' WHERE Person_ID = 6;    -- religion C, PORK OK, not a vegatarian
-- (1 row(s) affected)

/*******************************************************************************
The next statement causes the following error:

E_EVERYONE_BUT_VEGS.
Gosti koji nisu vegeterijanci ne zele VEG jelo.
At trigger 'GUESTS_BI0' line: 9, col: 52.
*******************************************************************************/
UPDATE Guests SET Meal = 'VEG' WHERE Person_ID = 6;

Na slican nacin bi se mogla implementirati provera preko FK taman prema Ivanovom primeru, ali nemam vremena trenutno.