1. Learning SQL part 3 - relations

In dit gedeelte gaan we onze database uitbreiden. Op de eerste plaats gaan we ruimtereizen maken met ruimtereizigers. Een ruimtereis heeft een bepaalde duur, een vertrekpunt en een bestemming. De ruimtereizigers worden toegevoegd aan deze reizen evenals de bemanning (de astronauten of cosmonauten). Iedere ruimtereis vindt plaats met een bepaald soort ruimteschip waarbij, afhankelijk van de reis, een bepaalde specificatie van het ruimteschip hoort. Bijvoorbeeld een reis naar Mars vergt een ander ruimteschip dan een reis naar de maan.

In de navolgende lesonderwerpen zullen we de deze database gaan ontwerpen. Eerst maken we een technisch ontwerp met behulp van strokendiagrammen en vervolgens zullen we aan de hand van het ontwerp de database gaan maken.

2. De database updaten

Het technisch ontwerp wat les 1 is genoemd gaan we nu uitwerken. Gaandeweg kom je tijdens de lessen achter of je een goed ontwerp hebt gemaakt. Om de techniek erachter meer helder te krijgen gaan we nu eerst onze bestaande database updaten. Dat is vanwege het volgende:

Zoals dat wel vaker gaat heeft de leiding van de ruimtereizen achteraf besloten dat de nationaliteit met drie letters moet worden aangegeven in plaats van het voluit geschreven land. Deze landafkortingen zijn te vinden op de Complete List of Country & Dialing Codes

We gaan daarom de database updaten met een updatequery die de volgende syntax heeft:

UPDATE
SET =<waarde1>, .....
WHERE <naamvandekolom;>=<waarde>;

Op de sqlupdate pagina van W3Sschools vindt je een aantal voorbeelden om UPDATE uit te proberen.

3. Strokendiagrammen

Een technisch ontwerp kun je realiseren door te werken met strokendiagrammen. Het is vergelijkbaar met de kop van de tabel. Het strokendiagram van de tabel astronauts ziet er als volgt uit

Deze structuur klopt met de tabel. Klik in phpMyAdmin op Structure om te vergelijken. Als het goed is zie je het volgende:

 Je ziet dat het strokendiagram precies klopt met de structuur in de database. De verticale tweezijdige pijl ⇔ onder id geeft aan dat dit een PRIMARY KEY is. In phpMyAdmin zie je een sleuteltje naast id staan.

 

Op www.gliffy.com → Start Drawing kun je zo´n strokendiagram gemakkelijk maken. De 8 tips die automatisch worden getoond leren je hoe je ermee kunt werken.

Een strokendiagram kun je in Gliffy maken met  een zogenaamde three by three table die kunt vinden onder het tabje Containers.  Een tweezijdige pijl vind je bij de Basic Shapes. Uiteindelijk kun je de file exporteren. Dit kan echter niet als je niet bent ingelogd. Als je Google for Education gebruikt kun je gemakkelijk inloggen met de rode Google knop. Als alternatief kun je ook een screenshot maken en bewerken in Paint (we leggen dit niet verder uit).

4. Redundante informatie

Als je opdracht 3.1 goed hebt beantwoord kom je erachter dat het niet zo handig is om één onderdeel, de nationaliteit, voor ieder record apart te wijzigen. Bij 12 records is het nog wel te doen maar bij 12.000 of 12.000.000 records niet meer. De reden hiervan is dat er redundante informatie in de database staat. In de onderstaande afbeelding wordt dit verder verduidelijkt.

 

In de afbeelding is ook te zien dat bij een redundante structuur kan het ook voorkomen dat gegevens onderling niet meer kloppen. Je hebt bijvoorbeeld de ene keer het land in meervoud aangegeven en de andere keer in enkelvoud. Dat noemen we inconsistentie.

Hoe gaan we dat oplossen? Door een aparte tabel te maken met nationaliteiten en deze te koppelen aan de tabel astronauts. Dit leer je in het volgende lesonderwerp

5. Een nieuwe tabel voor nationality

We zullen vanaf nu de database verder gaan uitbreiden en op de juiste wijze ontwerpen.We starten met het maken van een strokendiagram. Merk op dat we nu de kolom nationality een KEY hebben gegeven. Omdat deze verwijst naar een andere tabel heet dit een FOREIGN KEY.

Je ziet dat we nu een tweede tabel maken waarbij in de tabel de kolom nationality verwijst naar deze tabel.  We kunnen dit ontwerp nog verbeteren door de tabel nationality uit te breiden. We zetten de de 2-letterige en 3-letterige landcode erbij waardoor we uiteindelijk dit ontwerp krijgen.

De database aanpassen

Nu we klaar zijn met het ontwerp (tot hier) kunnen we een aanpassing doen aan de database. We beginnen met een extra tabel nationality die de volgende constraint heeft.

 

CREATE TABLE nationality (

id int(4) NOT NULL,

iso varchar(2) NOT NULL,

un varchar(3) NOT NULL,

nationality varchar(50) NOT NULL,

PRIMARY KEY (id)

)

ENGINE=InnoDB

DEFAULT CHARSET=latin1;

 

Merk op dat we nu geen AUTO_INCREMENT hebben ingesteld en van de kolom id de PRIMARY KEY hebben gemaakt. De id kolom wordt het nummer van de Verenigde Naties waarop een land wordt geïdentificeerd en is een uniek nummer. Deze kolom is dus heel geschikt als PRIMARY KEY. 

 

We zullen nu ook met een INSERT query een land invoeren:

 

INSERT INTO nasa.nationality (

id,
iso,
un,
nationality

)
VALUES (

840, 'US', 'USA', 'United States');

Merk op dat we nu nasa.nationality gebruiken. Zolang je binnen de database werkt is dat niet nodig maar je zult merken dat het in een werkende applicatie soms nodig is om ook de database aan te geven. Merk ook op dat de INSERT query klopt met de waardes. Dus id is een int en de overige waardes zijn Strings (varchar).

De relatie tussen de tabel astronauts en de tabel nationality zullen we in het volgend lesonderdeel gaan leggen.

6. De nieuwe tabel koppelen

We gaan nu binnen de database de twee tabellen koppelen. Hierbij een opmerking. Het is niet persé nodig om dit binnen de database te doen. Je zou ook een koppeling kunnen realiseren met de juiste query. Zo'n query kun je maken met het JOIN sleutelwoord (daarover later meer). In het algemeen is het echter beter om de database zoveel mogelijk het werk te laten doen in plaats van de bovenliggende code. Je applicatie wordt er sneller van.

Aan de slag

Als eerste gaan we de tabel astronauts gereed maken om een relatie aan te kunnen brengen. Zoals we kunnen zien in het strokendiagram heeft deze tabel een FOREIGN KEY nodig. De database is ontworpen op snelheid en heeft daarom ook een INDEX nodig. De index stellen we in met de volgende query.

ALTER TABLE astronauts ADD INDEX (nationality);

Omdat in de tabel nationality al een PRIMARY KEY ingesteld staat zijn we nu in staat om een relatie te leggen (anders zou de database dat niet accepteren). Eerst  echter nog wat informatie over tabeltypen.

tabeltypen

MySQL ondersteunt een aantal verschillende tabeltypen. We noemen dat storage-engines. Standaard wordt een tabel met het type InnoDB aangemaakt.Voordat InnoDB de standaard was werd de MyISAM engine als standaard gebruikt. Het verschil tussen InnoDB and MyISAM is dat innoDB gebruik kan maken van een FOREIGN KEY. Daarnaast is InnoDB transaction-safe.Het is daarom beter om InnoDB te gebruiken.

 

We zijn dus dankzij InnoDB in staat om om een FOREIGN KEY aan te maken. Dit doen we met de volgende query:

ALTER TABLE astronauts

ADD FOREIGN KEY (nationality)

REFERENCES nasa.nationality(id)

ON DELETE RESTRICT

ON UPDATE CASCADE;

 

Dit betekent het volgende:

ALTER TABLE astronauts →Pas de tabel aan en ...

ADD FOREIGN KEY (nationality)→...voeg een FOREIGN KEY toe aan de tabel astronauts in de kolom nationality...

REFERENCES nasa.nationality(id)→...waarbij uit de database nasa-tabel nationality de kolom num_un wordt gekoppeld...

ON DELETE RESTRICT→..met een restrictie als er in de kolom num_un nog een waarde bestaat.  Dit niet mag worden verwijderd in de tabel astronauts.

ON UPDATE CASCADE ; →Als in de tabel nationality een waarde wordt gewijzigd dan wordt dit ook automatisch (CASCADE) gewijzigd in de gekoppelde tabel.

7. Voorwaarden van InnoDB

Misschien had je zelf al een oplossing. In de tabel astronauts wilden we met de kolom nationality, wat op dat moment nog een varchar is, een koppeling maken naar de PRIMARY KEY van de tabel nationality wat een int is. Een koppeling maken tussen twee verschillende typen kolommen is onmogelijk. We moeten daarom eerst zorgen dat deze typen kolommen overeen komen. Dat doen we door een aanpassing te doen in de tabel astronauts. Je kunt dit doen met de hiernavolgende queries.

ALTER TABLE nasa.astronauts DROP nationality;→Pas de tabel astronauts aan door de kolom nationality weg te gooien.

 

ALTER TABLE nasa.astronauts ADD nationality INT( 3 ) NOT NULL; →Pas de tabel astronauts aan door een kolom erbij te maken.

 Als je alles zorgvuldig hebt gedaan heb je, als het goed is, een relatie gelegd tussen de tabel astronauts en de tabel nationality.Dit kunnen we ook aantonen met de volgende afbeelding.

Als je klikt op More en daarna op Designer kun je dezelfde afbeelding zien. Klopt deze in je eigen database? Je zult nu alleen de landeninformatie nog moeten invoeren.

 

8. Joins

Nu we de koppeling rond hebben komt in dit laatste deel aan de orde hoe je de gecombineerde informatie, dus astronauten en landinformatie uit de database kunt halen. Dit vindt over het algemeen plaats met een JOIN.  Op de sql_join pagina kun je leren hoe zo´n JOIN in zijn werk gaat.