5. Relaties tussen tabellen

5.1. Vreemde sleutels

Een relationele database is een verzameling tabellen die onderling via relaties met elkaar zijn verbonden. Laten we een voorbeeld nemen dat is geïnspireerd op de vorige tabel [BIBLIO], waarvan de structuur als volgt was:

Een voorbeeld van de inhoud was als volgt:

Misschien willen we informatie over de verschillende auteurs van deze werken, bijvoorbeeld hun nom en prénom, hun geboortedatum en hun nationalité. Laten we zo’n tabel aanmaken. Klik met de rechtermuisknop op [DBBIBLIO / Tables] en kies vervolgens de optie [New Table]:

Laten we nu de volgende tabel [AUTEURS] aanmaken:

`id`	primaire sleutel van de tabel – dient om een rij op unieke wijze te identificeren
`nom`	naam van de auteur
`prénom`	voornaam van de auteur, indien van toepassing
`date_naissance`	zijn geboortedatum
`nationalite`	zijn land van herkomst

De inhoud van de tabel [AUTEURS] zou als volgt kunnen zijn:

Laten we teruggaan naar de tabel [BIBLIO] en de inhoud ervan:

In de kolom [AUTEUR] van de tabel is het niet meer nodig om de naam van de auteur te vermelden. Het is beter om het nummer (id) te vermelden dat hij heeft in de tabel [AUTEURS]. Laten we daarom een nieuwe tabel aanmaken met de naam [LIVRES]. Om deze aan te maken, gebruiken we het script [biblio.sql] dat in paragraaf 3.14 is aangemaakt. We laden dit script met de tool [Script Executive, Ctrl-F12]:

We passen het script voor het aanmaken van de tabel BIBLIO aan, zodat het overeenkomt met dat van de tabel LIVRES:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Unieke beperkingen ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Primaire sleutels ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

We vermelden alleen de wijzigingen:

regel 4: het veld [AUTEUR] in de tabel wordt een geheel getal. Dit getal verwijst naar een van de auteurs uit de eerder aangemaakte tabel [AUTEURS].
regels 11-19: de namen van de auteurs zijn vervangen door hun auteursnummers.
regel 29: de naam van de constraint is gewijzigd. Voorheen heette deze [ UNQ1_BIBLIO ]. Nu heet deze [ UNQ1_LIVRES ]. Deze naam kan willekeurig zijn. Het verdient echter de voorkeur dat de naam een betekenis heeft. Hier is daar geen aandacht aan besteed. De constraints op de verschillende velden en tabellen van een database moeten door middel van verschillende namen van elkaar worden onderscheiden. Ter herinnering: de constraint in regel 29 vereist dat een titel uniek is in de tabel.
regel 36: wijziging van de naam van de beperking op de primaire sleutel ID.

Laten we dit script uitvoeren. Als het lukt, krijgen we de volgende nieuwe tabel [LIVRES]:

Je kunt je afvragen of we hier uiteindelijk wel beter van zijn geworden. De tabel [LIVRES] bevat namelijk auteursnummers in plaats van hun namen. Aangezien er duizenden auteurs zijn, lijkt het lastig om een verband te leggen tussen een boek en de auteur. Gelukkig is de taal SQL er om ons te helpen. Hiermee kunnen we meerdere tabellen tegelijkertijd doorzoeken. Als voorbeeld laten we de query SQL zien, waarmee we de titels van de boeken uit de bibliotheek kunnen ophalen, gekoppeld aan de gegevens van hun auteurs. Laten we de editor SQL (F12) gebruiken om de volgende opdracht SQL uit te voeren:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

Het is nog te vroeg om deze opdracht SQL uit te leggen. We komen hier binnenkort op terug. Het resultaat van deze aanvraag is als volgt:

Elk boek is correct gekoppeld aan de auteur en de bijbehorende informatie.

Laten we even samenvatten wat we zojuist hebben gedaan:

we hebben twee tabellen met verschillende soorten informatie:
- de tabel AUTEURS bevat informatie over de auteurs
- de tabel LIVRES bevat informatie over de boeken die door de bibliotheek zijn aangeschaft
deze tabellen zijn aan elkaar gekoppeld. Een boek heeft altijd een auteur. Het kan er zelfs meerdere hebben. Hier is met dat geval geen rekening gehouden. Het veld [AUTEUR] van de tabel [LIVRES] verwijst naar een rij in de tabel [AUTEURS]. Dit noemen we een relatie.

De relatie die de tabel [LIVRES] met de tabel [AUTEURS] verbindt, is in feite een soort beperking: een rij in de tabel [LIVRES] moet altijd een auteursnummer hebben dat voorkomt in de tabel [AUTEURS]. Als een rij in [LIVRES] een auteursnummer zou hebben dat niet voorkomt in de tabel [AUTEURS], zouden we in een abnormale situatie terechtkomen waarin we de auteur van een boek niet zouden kunnen achterhalen.

De tabel SGBD kan controleren of aan deze voorwaarde altijd wordt voldaan. Hiervoor voegen we een voorwaarde toe aan de tabel [LIVRES]:

De koppeling tussen het veld [AUTEUR] in de tabel [LIVRES] en het veld [ID] in de tabel [AUTEURS] wordt een vreemde-sleutelkoppeling genoemd. Het veld [AUTEUR] van de tabel [LIVRES] wordt in de bovenstaande wizard „vreemde sleutel” of „foreign key” genoemd. Het definiëren van een vreemde sleutel houdt in dat de waarde van kolom [c1] van tabel [T1] moet voorkomen in kolom [c2] van tabel [T2]. De kolom [c1] wordt de „vreemde sleutel” van de tabel T1 genoemd ten opzichte van de kolom [c2] van de tabel [T2]. De kolom [c2] is vaak de primaire sleutel van de tabel [T2], maar dat is niet verplicht.

We definiëren de vreemde sleutel [AUTEUR] van de tabel [LIVRES] op het veld [ID] van de tabel [AUTEURS] als volgt:

naam van de constraint: vrij
kolom „vreemde sleutel”, hier de kolom [AUTEUR] van de tabel [LIVRES]
tabel waarnaar de vreemde sleutel verwijst. Hier moet de kolom [AUTEUR] van de tabel [LIVRES] een waarde hebben in de kolom [ID] van de tabel [AUTEURS]. Er wordt dus verwezen naar de tabel [AUTEURS].
kolom waarnaar door de vreemde sleutel wordt verwezen. In dit geval de kolom [ID] van de tabel [AUTEURS].

We valideren deze beperking:

Als alles goed gaat, wordt deze geaccepteerd:

Wat is het gevolg van deze nieuwe vreemde-sleutelbeperking? Laten we met de editor SQL (F12) proberen een rij in te voegen in de tabel LIVRES met een niet-bestaand auteursnummer:

De bovenstaande bewerking [INSERT] probeerde een boek in te voegen met een niet-bestaand auteursnummer (100). De uitvoering van de query is mislukt. Het bijbehorende foutbericht geeft aan dat er een schending is geweest van de vreemde-sleutelbeperking "FK_LIVRES_AUTEURS". Dit is de beperking die we zojuist hebben gedefinieerd.

5.2. Join-bewerkingen tussen twee tabellen

Laten we, nog steeds in de database [DBBIBLIO] (of een andere database, dat maakt niet uit), twee testtabellen aanmaken met de namen TA en TB, die als volgt zijn gedefinieerd:

Tabel TA

- ID: primaire sleutel van de tabel TA

- DATA: willekeurige gegevens

Tabel TB

- ID: primaire sleutel van de tabel TB

- IDTA: vreemde sleutel van de tabel TB die verwijst naar de kolom ID van de tabel TA. Een waarde uit de kolom IDTA van de tabel TA moet dus voorkomen in de kolom ID van de tabel TA

- VALEUR: willekeurige gegevens

In de editor SQL (F12) gaan we SQL-opdrachten uitvoeren die tegelijkertijd gebruikmaken van de twee tabellen TA en TB.

SQL>select * from TA,  TB

De opdracht SQL maakt, na het trefwoord FROM, gebruik van de twee tabellen TA en TB. De bewerking FROM TA, TB zorgt ervoor dat er tijdelijk een nieuwe tabel wordt aangemaakt waarin elke rij van de tabel TA wordt gekoppeld aan elke rij van de tabel TB. Als de tabel TA dus NA rijen heeft en de tabel TB NB rijen heeft, zal de resulterende tabel NA × NB rijen bevatten. Dit is te zien op de bovenstaande schermafbeelding. Bovendien bevat elke rij de kolommen van beide tabellen. De kolommen coli die in de volgorde [SELECT col1, col2, ... FROM ...] zijn opgegeven, geven aan welke kolommen moeten worden behouden. Hier geeft het sleutelwoord * aan dat alle kolommen van de resulterende tabel worden opgevraagd. Soms wordt gezegd dat de resulterende tabel van de vorige opdracht SQL het cartesiaanse product is van de tabellen TA en TB.

Hierboven is elke rij van de tabel TA gekoppeld aan elke rij van de tabel TB. Over het algemeen wil men aan een rij van TA de rijen van TB koppelen die een relatie met deze rij hebben. Deze relatie neemt vaak de vorm aan van een vreemde-sleutelbeperking. Dat is hier het geval. Aan een rij in de tabel TA kunnen de rijen uit de tabel TB worden gekoppeld die voldoen aan de relatie TB.IDTA=TA.ID. Er zijn verschillende manieren om dit op te vragen:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

De vorige opdracht SQL is vergelijkbaar met de vorige, maar met twee verschillen:

de resultaatregels van het cartesiaanse product TA x TB worden gefilterd door een clausule WHERE die aan een regel uit de tabel TA, alleen de rijen uit de tabel TB die voldoen aan de relatie TB.IDTA=TA.ID
worden alleen bepaalde kolommen opgevraagd met de syntaxis [T.col], waarbij T de naam van een tabel is en col de naam van een kolom in die tabel. Deze syntaxis maakt het mogelijk om de ambiguïteit weg te nemen die zou kunnen ontstaan als twee tabellen kolommen met dezelfde naam zouden hebben. Wanneer deze onduidelijkheid niet bestaat, kan de syntaxis [col] worden gebruikt zonder de tabel van deze kolom te specificeren.

Het resultaat is als volgt:

Hetzelfde resultaat kan worden verkregen met de volgende opdracht SQL:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

De term [inner join] is de oorsprong van de naam „interne join” die aan dit soort bewerkingen tussen twee tabellen wordt gegeven. We zullen zien dat er ook een „externe join” bestaat. Bij een interne join heeft de volgorde van de tabellen in de query geen invloed op het resultaat: FROM TA inner join TB is gelijk aan FROM TB inner join TA.

De voorgaande opdracht SQL neemt in de resultatentabel alleen de rijen van de tabel TA op waarnaar door ten minste één rij van de tabel TB wordt verwezen. De rij TA [3, data3] verschijnt dus niet in het resultaat, omdat er geen rij in TB naar verwijst. Het kan zijn dat men alle rijen van TA wil, ongeacht of ze al dan niet worden gerefereerd door een rij uit TB. In dat geval gebruikt men een externe join tussen de twee tabellen:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

We hebben hier te maken met een linkse externe join („left outer join”). Om de term „FROM TA left outer join TB” te begrijpen, moet je je een join voorstellen met de tabel TA aan de linkerkant en de tabel TB aan de rechterkant. Alle rijen uit de linkertabel komen terecht in het resultaat van een linkse externe join, zelfs die waarbij de join-relatie niet wordt gecontroleerd. Deze join-relatie is niet noodzakelijkerwijs een vreemde-sleutelbeperking, hoewel dit wel het meest voorkomende geval is.

In de volgende volgorde:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

is de tabel TB de „linkerzijde“ in de externe join. Alle rijen van TB komen dus voor in het resultaat:

In tegenstelling tot de interne join is de volgorde van de tabellen hier dus wel van belang. Er bestaan ook rechtse externe joins:

FROM TA left outer join TB is gelijk aan FROM TB right outer join TA: de tabel TA staat aan de linkerkant
FROM TB left outer join TA is gelijk aan FROM TA right outer join TB: de tabel TB staat aan de linkerkant

Nu we de basisprincipes van het gelijktijdig verwerken van meerdere tabellen kennen, kunnen we ingaan op complexere zoekbewerkingen in databases.