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

CREATE TABLE Tari (

      ID_Tara int PRIMARY KEY,

      NumeTara nvarchar(50) NOT NULL UNIQUE

)

 

GO

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

)

GO

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

)

GO

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

)

 

GO

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')

GO

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')

GO

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)

 

GO

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)

 

GO

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