Intrebari generale

Re: Problema de modelare

rsocol — Sun, 22 Mar 2009 08:45:26 GMT

Cu mare întârziere, vin cu o soluţie posibilă:

CREATE TABLE Tari (

ID_Tara int PRIMARY KEY,

NumeTara nvarchar(50) NOT NULL UNIQUE

)

CREATE TABLE Judete (

ID_Judet int PRIMARY KEY,

ID_Tara int NOT NULL FOREIGN KEY REFERENCES Tari,

NumeJudet nvarchar(50) NOT NULL,

Prescurtare varchar(2) NULL,

UNIQUE (NumeJudet, ID_Tara),

UNIQUE (ID_Judet, ID_Tara) -- pt FK-uri

)

CREATE TABLE Localitati (

ID_Localitate int PRIMARY KEY,

ID_Tara int NOT NULL FOREIGN KEY REFERENCES Tari,

ID_Judet int NULL,

FOREIGN KEY (ID_Judet, ID_Tara) REFERENCES Judete (ID_Judet, ID_Tara),

NumeLocalitate nvarchar(50) NOT NULL,

UNIQUE (NumeLocalitate, ID_Tara, ID_Judet),

UNIQUE (ID_Localitate, ID_Tara), -- pt FK-uri

UNIQUE (ID_Localitate, ID_Judet), -- pt FK-uri

UNIQUE (ID_Localitate, ID_Tara, ID_Judet) -- pt FK-uri

)

CREATE TABLE Absolventi (

ID_Absolvent int PRIMARY KEY,

Nume nvarchar(30) NOT NULL,

Prenume nvarchar(50) NOT NULL,

UNIQUE (Nume, Prenume),

ID_Tara int NULL REFERENCES Tari,

ID_Judet int NULL REFERENCES Judete,

FOREIGN KEY (ID_Judet, ID_Tara) REFERENCES Judete (ID_Judet, ID_Tara),

ID_Localitate int NULL REFERENCES Localitati,

FOREIGN KEY (ID_Localitate, ID_Tara) REFERENCES Localitati (ID_Localitate, ID_Tara),

FOREIGN KEY (ID_Localitate, ID_Judet) REFERENCES Localitati (ID_Localitate, ID_Judet),

FOREIGN KEY (ID_Localitate, ID_Tara, ID_Judet) REFERENCES Localitati (ID_Localitate, ID_Tara, ID_Judet),

Adresa nvarchar(50) NULL

)

INSERT INTO Tari VALUES (1, N'România')

INSERT INTO Tari VALUES (2, N'Franţa')

INSERT INTO Tari VALUES (3, N'Germania')

INSERT INTO Tari VALUES (4, N'Egipt')

INSERT INTO Tari VALUES (5, N'S.U.A.')

INSERT INTO Tari VALUES (6, N'Canada')

INSERT INTO Judete VALUES (10,1,N'Prahova','PH')

INSERT INTO Judete VALUES (11,1,N'Dâmboviţa','DB')

INSERT INTO Judete VALUES (12,1,N'Argeş','AG')

INSERT INTO Judete VALUES (13,1,N'Braşov','BV')

INSERT INTO Judete VALUES (14,1,N'Teleorman','TR')

INSERT INTO Judete VALUES (20,5,N'California','CA')

INSERT INTO Judete VALUES (21,5,N'Florida','FL')

INSERT INTO Judete VALUES (22,5,N'Washington','WA')

INSERT INTO Judete VALUES (23,5,N'Oregon','OR')

INSERT INTO Localitati VALUES (101,1,10,N'Ploieşti') -- Prahova

INSERT INTO Localitati VALUES (102,1,11,N'Târgovişte') -- Dâmboviţa

INSERT INTO Localitati VALUES (103,1,12,N'Viişoara') -- exista o Viişoara în Argeş

INSERT INTO Localitati VALUES (104,1,11,N'Viişoara') -- şi altă Viiloara în Dâmboviţa

--INSERT INTO Localitati VALUES (105,1,20,N'Piteşti') -- nu merge, deoarece California nu e in Romania

INSERT INTO Localitati VALUES (105,1,12,N'Piteşti') -- merge, dacă precizez un judeţ din România

INSERT INTO Localitati VALUES (106,1,NULL,N'Iaşi') -- totuşi, pot preciza localitatea fără judeţ

INSERT INTO Localitati VALUES (107,1,14,N'Alexandria') -- există o Alexandria în Teleorman

INSERT INTO Localitati VALUES (108,4,NULL,N'Alexandria') -- şi altă Alexandria în Egipt

INSERT INTO Localitati VALUES (201,5,20,N'Los Angeles') -- California

INSERT INTO Localitati VALUES (202,5,21,N'Miami') -- Florida

INSERT INTO Localitati VALUES (203,5,NULL,N'Seattle') -- Washington

INSERT INTO Localitati VALUES (204,5,22,N'Redmond') -- Washington

INSERT INTO Localitati VALUES (205,5,21,N'Redmond') -- Oregon

INSERT INTO Localitati VALUES (301,2,NULL,N'Paris') -- Franţa

--INSERT INTO Localitati VALUES (401,NULL,NULL,N'Berlin') -- nu merge, trebuie sa precizez ţara (în tabela Localitati)

INSERT INTO Absolventi VALUES (1001,N'Popescu',N'Ion',1,10,101,N'Bd.Republicii nr.1') -- Ploieşti, Jud.Prahova, România

INSERT INTO Absolventi VALUES (1002,N'Popescu',N'George',NULL,NULL,101,N'Str.Ştefan cel Mare nr.200') -- Ploieşti, fără ţară sau judeţ (se subînţelege Jud.Prahova, România)

--INSERT INTO Absolventi VALUES (1003,N'Popescu',N'Marian',2,NULL,101,N'Str.Ştefan cel Mare nr.200') -- dar nu merge Ploieşti, Franţa

INSERT INTO Absolventi VALUES (1003,N'Popescu',N'Marian',1,NULL,101,N'Str.Ştefan cel Mare nr.200') -- însă merge Ploieşti, România (chiar fără judeţ)

INSERT INTO Absolventi VALUES (1004,N'Ionescu',N'Marian',NULL,14,107,NULL) -- Alexandria, Jud.Teleorman

INSERT INTO Absolventi VALUES (1005,N'Ionescu',N'Victor',1,NULL,107,NULL) -- Alexandria, România (se subînţelege Jud.Teleorman)

INSERT INTO Absolventi VALUES (1006,N'Georgescu',N'Marian',4,NULL,108,NULL) -- Alexandria, Egipt

--INSERT INTO Absolventi VALUES (1007,N'Georgescu',N'Victor',2,NULL,107,NULL) -- nu merge Alexandria, Franţa

INSERT INTO Absolventi VALUES (1007,N'Georgescu',N'Victor',NULL,NULL,107,NULL) -- Alexandria (se subînţelege România, Jud.Teleorman)

INSERT INTO Absolventi VALUES (1008,N'Georgescu',N'Alexandru',NULL,NULL,108,NULL) -- Alexandria (se subînţelege Egipt)

INSERT INTO Absolventi VALUES (1009,N'Marinescu',N'Mihai',3,NULL,NULL,NULL) -- Germania

INSERT INTO Absolventi VALUES (1010,N'Vasilescu',N'Ion',1,13,NULL,NULL) -- Jud.Braşov, România

INSERT INTO Absolventi VALUES (1011,N'Vasilescu',N'George',NULL,12,NULL,NULL) -- Jud.Argeş (se subînţelege România)

DROP TABLE Absolventi, Localitati, Judete, Tari

Această variantă încearcă să includă în tabela Judete şi subdiviziunile altor ţări (de exemplu statele din S.U.A.), deşi în alte ţări subdiviziunile pot fi mai complexe (de exemplu, într-un stat din S.U.A. există şi County-uri, iar în unele sunt şi alte subdiviziuni mai mici: Township, Borough, etc). Dacă dorim să permitem toată structura ierarhică, atunci soluţia propusă de Remus ar putea fi mai potrivită. Dacă vrem să limităm numărul de atribute la 3 (ţară, judeţ, localitate) (plus o adresă nestructurată, format din stradă, număr, bloc, apartament, etc), atunci soluţia de mai sus ar putea rezolva în mod elegant problema, permiţând precizarea sau omiterea oricărui atribut (dintre cele trei), însă nepermiţând combinaţii incorecte.

Ceea ce ar fi de observat în soluţia folosită mai sus este că:
1. putem face un foreign key pe o coloană care permite null-uri, valorile nule fiind permise, chiar dacă nu apar în tabela referită (multă lume ştie asta);
2. putem face un foreign key compus din mai multe coloane (ceva mai puţină lume ştie asta);
3. putem face un foreign key compus din mai multe coloane, iar pentru un anumit rând o coloană poate fi completată şi cealaltă poate fi nulă, în acest caz valorile fiind de asemenea permise chiar dacă nu apar în tabela referită (puţină lume se gândeşte la asta).

Răzvan

Re: Problema de modelare

lmsasu — Tue, 24 Feb 2009 06:05:22 GMT

Ai inteles exact, de aceea nu pot sa aplic FN3.

Exemple: tara cu inregistrarea (1, Romania)
judet cu inregistrarile (1, Brasov, 1), (2, SIbiu, 1)
localitate cu: (1, Codlea, 1), (2, Rasnov, 1)
absolvent cu inregistrarile:
(1, '0268 123123', null, 2, 1, null, null)
(2, '0268 223123', null, null, 1, 'strada de acasa', null)

Re: Problema de modelare

B_gd_n[ ]Sahlean — Mon, 23 Feb 2009 22:17:38 GMT

lmsasu:
... null pe determinanti?

In principiu, determinanţii (nu determinaţii !) devin chei primare. Ori, cheile primare nu pot conţine valori null.

Prezintă nişte exemple ...

Re: Problema de modelare

lmsasu — Mon, 23 Feb 2009 20:40:31 GMT

@B_gd_n[ ]Sahlean Corect ca daca ai dependentele functionale tranzitive nu esti in FN3. Problema la mine este ca atributele din partea stanga sunt
uneori nule iar in acest caz nu mai am dependente. Daca nu era posibilitatea de null, intrebarea nu mai avea sens. Problema e: cum modelez, daca e posibil sa am si null pe determinanti?

Re: Problema de modelare

rremus — Mon, 23 Feb 2009 20:32:42 GMT

Tara/judet/localitate este un model ierarhic, si ca atare este mai bine reprezentat cu o singura tabela (entity_id, entity_type, parent_id, entity_name). Modelarea ierarhica permite sa adaugi noi nivele (sector de ex.) fara sa schimbi codul aplicatiei si poate reprezenta foarte usor problema care o ai tu (lipsa de granularitate a informatiei). Student are o singura cheie, referind enityt_id, si poate fi un id de tara, de judet, de oras sau de satuc. Si decind cu CTE interogarile ierarhice sint floare la ureche.

Re: Problema de modelare

B_gd_n[ ]Sahlean — Mon, 23 Feb 2009 18:00:38 GMT

Daca se pleaca de la dependentele functionale (DF) :

id_localitate -> nume_localitate , id_judet
id_judet -> nume_judet , id_tara
id_tara -> nume_tara

si

id_absolvent -> id_localitate

atunci

id_absolvent -T-> id_judet
si
id_absolvent -T-> id_tara
sunt DF tranzitive

Concluzia este ca tb. sa elimini cheiele externe id_judet si id_tara din Absolvent pt. a avea FN3.

Re: Problema de modelare

lmsasu — Mon, 23 Feb 2009 15:28:27 GMT

Cascade la update si set null la delete. Inregistrarile de absolventi raman, cele demografice se pot sterge (din tabla de absolventi sau din tablele de judete/tari/localitati).

Chestiunea cu Paris din Congo - asta o pot trata la nivel de business layer. Drept e ca ar fi fain sa fie si la BD~~, poate ceva triggere? are sens?~~ dar cum?

Re: Problema de modelare

rsocol — Mon, 23 Feb 2009 15:28:13 GMT

Pe de altă parte, sunt de acord că design-ul nu e optim şi are alte probleme (de exemplu permite oraşul Paris, din judeţul Prahova, Germania). O să mă gândesc în astă seară la o variantă mai bună.

Răzvan

Re: Problema de modelare

rsocol — Mon, 23 Feb 2009 15:25:22 GMT

De ce ai avea nevoie de cascade delete în relaţiile cu tabela absolvent? Dacă încerci să ştergi o ţară în care există absolvenţi vrei să se şteargă automat toţi acei absolvenţi ? Eu cred că e mai bine să permită ştergerea unei ţări doar dacă nu există absolvenţi din acea ţară (adică faci foreign key-ul, dar fără cascade).

Răzvan

Problema de modelare

lmsasu — Mon, 23 Feb 2009 15:18:05 GMT

Salut,

am tabelele: tara (cu id_tara PK), judet (id_tara FK, id_judet PK), localitate (id_judet FK, id_localitate PK). Trebuie sa modelez entitatea "absolvent" in care printre altele sa memoreze adresa lui actuala (doar 1 adresa). Problema pleaca din faptul ca poate nu vrea sa precizeze localitatea ci numai judetul si tara, sau poate nu judet si nu localitate ci numai tara, etc. Cum ati modela voi asta?

O varianta (cu bube multiple) e cea data in figura http://profile.imageshack.us/user/lmsasu/images/detail/#509/cicluri.jpg (sorry, linkul nu imi mere altfel). Probleme: cicluri (redundanta, uneori doar, ca sa nu mai zic ca nu e chiar FN3 tot timpul...), daca vreau sa specific pe legatura intre (sa zicem) judet si absolventi ca la delete se pune null iar la update sa fie cascade, imi tipa serverul cu eroare: "Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750" etc. Chiar vreau legaturile astea, cu tot cu relatii. O alta varianta este sa pun table intermediare intre absolvent si (sa zicem) judete, dar tot ciluri apar... voi vedeti alta solutie?

Multumesc,
Lucian