Tip specijalne veze - generalizacija

[ VerbatimBOT @ 28.03.2007. 12:30 ] @

Pozdrav!
Evo jedan kolega sa mog smera je postavio pitanje na našem forumu, ali obzirom da tamo još nema odgovora, postavio sam isto pitanje i ovde:

Citat:

kako se pravi u menagment studiu (sqlu) generalizacija(tj specijalizacija)
ja uradih ovo
napravio 1 tabelu koja je nadtip i 4 podtip tabele ,svuda stavio isti kljuch(isto ime) ali kako da ih povezem,to nije foreign key primary key veza.neshto sam chachkao u diagramu i ako uspem neshto da uradim,pri upisu reda u podtip,taj red se ne pojavljuje u nadtipu! shta treba urdaiti da bi to tako funkcionisalo??
napomena da mi je potrebna 0,1 generalizacija.tj. da se red moze ali ne mora nalaziti u podtipu al ako se nalazi da se nalazi u max jednom!!

aj hvala puno

[ chachka @ 28.03.2007. 15:23 ] @

Code:

CREATE TABLE osobe
(
  sifra_osobe CHAR(13) NOT NULL,
  ime_osobe VARCHAR(15) NOT NULL,
  vrsta_socijalnog_osiguranja CHAR(3), -- NULL znaci da je osoba
                                       -- bez socijalnog osiguranja

  CONSTRAINT pk_oso
    PRIMARY KEY (sifra_osobe),

  CONSTRAINT ch_oso
    CHECK (vrsta_socijalnog_osiguranja IN
            ('RAD', -- radnicko
             'PEN', -- penzionersko
             'BOR'  -- boracko
            )
          ),

  CONSTRAINT un_oso
    UNIQUE (sifra_osobe, vrsta_socijalnog_osiguranja)
);

CREATE TABLE radnici
(
  sifra_osobe CHAR(13) NOT NULL,
  vrsta_socijalnog_osiguranja CHAR(3) NOT NULL,
  podaci_o_radniku VARCHAR(100), -- specijalni podaci o radniku

  CONSTRAINT pk_rad
    PRIMARY KEY (sifra_osobe, vrsta_socijalnog_osiguranja),

  CONSTRAINT ch_rad
    CHECK (vrsta_socijalnog_osiguranja = 'RAD'),

  CONSTRAINT ch_rad_oso
    FOREIGN KEY (sifra_osobe, vrsta_socijalnog_osiguranja)
    REFERENCES Osobe (sifra_osobe, vrsta_socijalnog_osiguranja)
);

CREATE TABLE penzioneri
(
  sifra_osobe CHAR(13) NOT NULL,
  vrsta_socijalnog_osiguranja CHAR(3) NOT NULL,
  podaci_o_penzioneru VARCHAR(100), -- specijalni podaci o penzioneru

  CONSTRAINT pk_pen
    PRIMARY KEY (sifra_osobe, vrsta_socijalnog_osiguranja),

  CONSTRAINT ch_pen
    CHECK (vrsta_socijalnog_osiguranja = 'PEN'),

  CONSTRAINT ch_pen_oso
    FOREIGN KEY (sifra_osobe, vrsta_socijalnog_osiguranja)
    REFERENCES Osobe (sifra_osobe, vrsta_socijalnog_osiguranja)
);

CREATE VIEW sve_o_radnicima
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_radniku
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       r.podaci_o_radniku
  FROM osobe AS o
       INNER JOIN
       radnici AS r
         ON o.sifra_osobe = r.sifra_osobe;

CREATE VIEW sve_o_penzionerima
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_penzioneru
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       p.podaci_o_penzioneru
  FROM osobe AS o
       INNER JOIN
       penzioneri AS p
         ON o.sifra_osobe = p.sifra_osobe;

Ovde problem moze da predstavlja dozvoljen NULL u koloni vrsta_socijalnog_osiguranja tabele osobe, odnosno pojavljivanje te kolone u okviru UNIQUE indeksa. Kako ovo prevazici imamo u temi SQL mozgalica broj 1.

Podaci se unose prvo u tabelu osobe pa tek onda u tabelu npr. penzioneri. Moze se napraviti i izmenjivi VIEW (pomocu RULE-ova, TRIGGER-a, ili neki treci nacin zavisi od sistema za upravljanje bazom) te se podaci mogu unositi direktno u njega.

[ VerbatimBOT @ 28.03.2007. 17:24 ] @

Hvala druže!
Živ bio! :)

[ mahatma @ 29.03.2007. 16:29 ] @

ja sam postavio pitanje :))

propustio sam ovaj query kroz sql ali opet nedobijem ono na shta sam mislio!!

kad unesem osobu i stavim recimo PEN kad shifru osiguranja,taj red se ne pojavi u tabeli penzioneri!!jel to moguce nekako realizovati pomocu kljucheva ili mora da se pravi neka procedura ili triger??
oduvek sam hejtovo generalizaciju,sad ispada da je nauka napraviti je!

[ VerbatimBOT @ 29.03.2007. 22:11 ] @

Koliko ja shvatam i ne treba da ti se unos nadtipa pojavi u podtipu. Pod tip samo nasleđuje atribute nadtipa, ali oni ostaju unutar nadtipa.
Ako neko zna precizno objašnjenje, neka ga ta, ja izlomih pamet ovde... :)

[ mahatma @ 30.03.2007. 10:43 ] @

podtip nasledjuje atrubute nadtipa ,da to je ok,i ima neke sebi specifichne,ali u chemu je njegov smisao tj smisao tabele koja bleji prazna??valjda treba pri unosu u nadtip on sam da skapira(aha PEN stavicu ga u tabelu penzioneri),ili mozda obrnuto da prilikom unosa u podtip taj unos bude automatski i u tabeli nadtipa!

ako neko zna nek prosvetli nas gushtere :)

[ VerbatimBOT @ 30.03.2007. 11:53 ] @

Citat:

mahatma: ali u chemu je njegov smisao tj smisao tabele koja bleji prazna??

Čemu poenta ako iste podatke imaš u nadtipu i podtipu? To su samo dupli podaci. Drugo, po tome bi onda koliko god da imaš podtipova, toliko bi duplirao podatke koji su već uneti u nadtip.
Milsim da to treba shvatiti kao nasleđivanje klasa. Atributi nadtipa, ostaju u toj tabeli, ali se u podtipu mogu koristiti korišćenjem odgovarajućeg JOIN upita.

[ mahatma @ 30.03.2007. 15:43 ] @

ima logike u pravu si!!kreten sam! i nema shta da se pojavi u podtipu osim kljucha!

[ VerbatimBOT @ 30.03.2007. 23:43 ] @

Ključa + ona polja koja su specifična samo za taj podtip.
Ma nisi kreten, nego kolega! :)

[ mahatma @ 31.03.2007. 13:45 ] @

ne...mislim nema shta da se pojavi u podtipu osim kljucha iz nadtipa! :)

tnx na pomoci u svakom sluchaju!!

[ chachka @ 02.04.2007. 13:14 ] @

Prvo:
Napravio sam gresku kada sa umesto LEFT OUTER JOIN koristio INNER JOIN u pogledima. Dopuna datog skripta glasi:

Code:

DROP VIEW sve_o_radnicima;
DROP VIEW sve_o_penzionerima;

CREATE VIEW sve_o_radnicima
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_radniku
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       r.podaci_o_radniku
  FROM osobe AS o
       LEFT OUTER JOIN
       radnici AS r
         ON o.sifra_osobe = r.sifra_osobe
WHERE o.vrsta_socijalnog_osiguranja = 'RAD';

CREATE VIEW sve_o_penzionerima
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_penzioneru
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       p.podaci_o_penzioneru
  FROM osobe AS o
       LEFT OUTER JOIN
       penzioneri AS p
         ON o.sifra_osobe = p.sifra_osobe
WHERE o.vrsta_socijalnog_osiguranja = 'PEN';

Drugo:

Citat:

mahatma: kad unesem osobu i stavim recimo PEN kad shifru osiguranja,taj red se ne pojavi u tabeli penzioneri!!

Tacno je da se ne pojavljuje u tabeli penzioneri, a i sta bi to trebalo da se pojavi? Eventualno DEFAULT vrednosti, ako su zadate. Ovo se moze postici trigerima. Svakako savetujem da se pogledi naprave izmenljivim, te da se oni koriste za DML naredbe. Nazalost ne znam kako se ovo radi u MSSQL Server-u.

Trece:
Postoje i alternativni modeli, npr:

Code:

CREATE TABLE osobe_2
(
  sifra_osobe CHAR(13) NOT NULL,
  ime_osobe VARCHAR(15) NOT NULL,
  vrsta_socijalnog_osiguranja CHAR(3), -- NULL znaci da je osoba
                                       -- bez socijalnog osiguranja
  podaci_o_radniku VARCHAR(100), -- specijalni podaci o radniku
  podaci_o_penzioneru VARCHAR(100), -- specijalni podaci o penzioneru

  CONSTRAINT pk_oso_2
    PRIMARY KEY (sifra_osobe),

  CONSTRAINT ch_oso_2
    CHECK (vrsta_socijalnog_osiguranja IN
            ('RAD', -- radnicko
             'PEN', -- penzionersko
             'BOR'  -- boracko
            )
          )
);

CREATE VIEW sve_o_radnicima_2
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_radniku
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       o.podaci_o_radniku
  FROM osobe_2 AS o
WHERE o.vrsta_socijalnog_osiguranja = 'RAD';

CREATE VIEW sve_o_penzionerima_2
(
  sifra_osobe,
  ime_osobe,
  vrsta_socijalnog_osiguranja,
  podaci_o_penzioneru
)
AS
SELECT o.sifra_osobe, o.ime_osobe, o.vrsta_socijalnog_osiguranja,
       o.podaci_o_penzioneru
  FROM osobe_2 AS o
WHERE o.vrsta_socijalnog_osiguranja = 'PEN';

U ovom modelu se svi podaci trpaju u jednu tabelu. Model je laksi za pocetnike, ali tezi (u smislu konvergira) da ima veliki broj kolona u jednoj tabeli. Takodje mnogo tih kolona moze da ima NULL vrednost. Dalje, podaci postaju nekoegzistenti, jer kako treba tumaciti:

Code:

INSERT INTO osobe_2
       (sifra_osobe, ime_osobe, vrsta_socijalnog_osiguranja,
        podaci_o_radniku, podaci_o_penzioneru)
VALUES ('1', 'Srdjan Mijatov', 'RAD',
        NULL, 'Penzioner od 1997-me, mesecna penzija 15000 Din');

Radnik sam, a ujedno u podacima o penzionerima pise da sam penzioner?!?

Da bi se izbegli ovakvi konfuzni podaci moraju se praviti dodatni CHECK CONSTRAINT-i. Mada ja ovakav model nikada nebih koristio (zbog nekoegzistentnosti + gadljiv sam na NULL-ove), smatram da i ovde upotreba izmenljivih pogleda umnogome olaksava zivot.

[ mahatma @ 03.04.2007. 14:58 ] @

ovaj poslednji model je stvarno nakaza :)

a gore mi nije jasno zashto si koristio left outer join,shta si time dobio,tj zashto tako treba?!

stavio si lef outer sa uslovom WHERE!zar to nije u ovom sluchaju isto kao i inner bez takvog uslova?

hvala na savetima,lepo je kad neko hoce da pomogne i uputi!

[ chachka @ 03.04.2007. 22:35 ] @

Nije greska. Upotreba INNER JOIN ili LEFT OUTER JOIN zavisi od toga sta zelimo postici i da li su specijalni podaci o npr. radniku obavezni ili ne. Evo malo podataka za primer.

Code:

INSERT INTO osobe (sifra_osobe, ime_osobe, vrsta_socijalnog_osiguranja,)
VALUES ('1', 'Srdjan Mijatov', 'RAD');
INSERT INTO osobe (sifra_osobe, ime_osobe, vrsta_socijalnog_osiguranja,)
VALUES ('2', 'Dragan Mijatov', 'RAD');
INSERT INTO osobe (sifra_osobe, ime_osobe, vrsta_socijalnog_osiguranja,)
VALUES ('3', 'Dobrila Mijatov', 'PEN');

INSERT INTO radnici(sifra_osobe, vrsta_socijalnog_osiguranja, podaci_o_radniku)
VALUES ('1', 'RAD', 'programer');

COMMIT;

Znaci imamo podatke o tri osobe, od koje su dve radnici, i imamo podatak o zanimanju jednog od radnika.

Prva varijanta VIEW-a (INNER JOIN varijanta) vraca rezultat:

Druga varijanta VIEW-a (LEFT OUTER JOIN varijanta) vraca rezultat:

Code:

sifra_osobe   ime_osobe        vrsta_socijalnog_osiguranja   podaci_o_radniku
-----------   --------------   ---------------------------   ----------------
          1   Srdjan Mijatov   RAD                           programer
          2   Dragan Mijatov   RAD                           <NULL>

Znaci razlika je da li nam je dovoljno da nekoga oznacimo u tabeli 'osobe' kao radnika, ili zahtevamo i da se ta osoba pojavi u tabeli 'radnici'.

Da u drugoj verziji nema klauzule WHERE onda bi rezultat obuhvatio i osobe oznacene sa 'PEN'.

_{[Ovu poruku je menjao chachka dana 03.04.2007. u 23:53 GMT+1]}

[ mahatma @ 05.04.2007. 15:43 ] @

da da da!
skapirao sam! extra!

hvala ti puno!

[ mahatma @ 24.04.2007. 13:18 ] @

prvo shvatio sam da mi treba 1,1 generalizacija tj. da svaki red koji se unese u nadtip mora se uneti i u podtip!

interesuje me kako se u tom sluchaju realizuju kljuchevi,ustavri ako bi mogao neko da mi kaze kako se u svim sluchajevima reazliju kljuchevi:
0,1-to je vec pokazano
1,1
0,m
1,m

to mi nije bash najjasnije oko generalizacije,i nije mi toliko bitno u ovom trenutku koliko sledece,ali voleo bih da znam!

shta bih ja zeleo i shta mi trenutno treba!da mi kljuch u glavnoj tabeli bude identity,tj da se sam puni,ali takodje da se i kljuchevi u podtipovima sami pune!kako to realizovati?
ne mogu i njih da stavim da su identity jer ce u jednom trenutku zabagovati !!npr. napunim pet redova penzionera(osobe i penzioneri krecu od 1 i ID ce doci do 5 i sve radi),i onda hocu da ubacim radnika ali njegov ID krece od 1 a osoba je sada na 6!
e to mi nije jasno kako da realizujem! meni je palo na pamet neki triger ali kako

aj hvala puno!

[ chachka @ 25.04.2007. 11:28 ] @

1:1 - Ako hoces da ti svaki red u nadtipu ima tacno jedan red u podtipu onda: START-ujes TRANSACTION, INSERT-ujes podatke u nadtip, INSERT-ujes podatke u odgovarajuci podtip, ako je sve proslo bez greske COMMIT-ujes transakciju, a ako se pojavila greska ROLLBACK-ujes transakciju.

Ovo se moze uraditi izmenljivim pogledima, storovanom procedurom, treigerom(?).

0:N, 1:N specijalizacije se realizuju kao i bilo koje druge relacije.

[ mahatma @ 25.04.2007. 13:47 ] @

aha..ok

a jel bi mogao da mi odgovorish na drugi deo pitanja ,kako da mi se sami pue kljuchevi u nadtipu i podtipu??

[ chachka @ 25.04.2007. 14:29 ] @

Ne mogu ti odgovoriti na drugi deo pitanja, jer ja ne upotrebljavam surogatne kljuceve.

[ mahatma @ 26.04.2007. 09:48 ] @

hehe ok!

meni je to baza za sajt tako da treba da mi bude automatsko punjenje kljucha,ne treba korisnik koji unosi red da unosi josh i kljuch :)

[ chachka @ 26.04.2007. 12:36 ] @

Ne treba ni baza da unosi kljuc, ali ce nas ovo odvesti u vecnu raspravu za i protiv surogatnih kljuceva.

Po meni ako neces da zamaras korisnika, zamori aplikaciju za unos podataka. Pogledaj hi-low mehanizam za kreiranje oid-a od Scott Ambler-a.

[ mahatma @ 26.04.2007. 18:49 ] @

pazi ja sam zelen u ovome i to mi je prvi projekat..pa pokushavam da ga napravim kako tako,samo da radi!

tnx u svakom slushaju pogledacu ovo o chemu prichash!

samojedno pitanje...shta je oid?? :D