1. Een index instellen

In het uiteindelijk ontwerp in deel 4 staan relaties. Beschouw bijvoorbeeld de tabel astronauts. Deze heeft een FOREIGN KEY op (de tabel) nationality. Om een FOREIGN KEY te kunnen  instellen verlangt MySQL eerst een index. Dit kun je eenvoudig instellen met phpMyAdmin. Je kunt het ook in je constraint opnemen. De hieronder getoonde afbeelding en constraint van de tabel astronauts maken dat duidelijk. Daarin wordt de kolom lastname gebruikt om een index te maken (en later weer gedropt).

constraint van astronauts met index

CREATE TABLE IF NOT EXISTS nasa.astronauts (
  id int(4) NOT NULL AUTO_INCREMENT,
  firstname varchar(75) NOT NULL,
  lastname varchar(75) NOT NULL,
  nationality int(3) NOT NULL,
  PRIMARY KEY (id),
  KEY nationality (nationality)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hoe kom je aan de constraints van de database?

In deel 1 heb je geleerd om de database te exporteren.  De constraint van de tabel astronauts zie je hierboven. Je kunt de opdracht maken.

 

2. Learning SQL part 5 - Making it work

In deel 4 heb je (deels) de database opgebouwd. In dit deel gaan we de database opnieuw opbouwen. Zie dit deel ook als een herhaling. Bewaar je eigen database door deze te exporteren naar een bestand.

We zullen nu, aan de hand van het ontwerp, laten zien hoe de database wordt gemaakt. Dit deel is niet meer zo moeilijk. Het is alleen belangrijk om vertrouwd te raken met de SQL-taal en alles in de juiste volgorde te doen. 

Wat als eerste?

Het inrichten van de database kan in de volgende volgorde:

1. Begin met het maken van de hoofdtabellen.  Dat zijn de tabellen astronauts, spacetravelers en spacetrips (als je deze al hebt hoef je deze uiteraard niet meer te maken).
2. Vervolgens voeg je daaraan toe de subtabellen nationality en locations. Vergeet niet om een PRIMARY KEY in te stellen.
3. Als de primaire sleutels goed staan ga je een index instellen waar een FOREIGN KEY moet komen;

De volgende stap is om deze subtabellen te vullen met content. Je werkt hierbij als het ware van "buiten naar binnen". Eerst de subtabel en daarna de hoofdtabel.

3. De koppeltabellen maken

De volgende stap is het maken van de koppeltabellen. Deze hebben een PRIMARY KEY over twee tabellen. Hoe ga je dat instellen? In phpMyAdmin is dat niet zo moeilijk. Vink volgens onderstaande afbeelding in de tabel de twee kolommen aan en klik daarna op PRIMARY KEY.

De tabel heeft nu een PRIMARY KEY over twee kolommen.

Waarom een PRIMARY KEY over twee kolommen?

Een PRIMARY KEY over twee kolommen kan erg nuttig zijn. Het is namelijk nu niet mogelijk dezelfde astronaut aan dezelfde ruimtereis te koppelen. Omdat de PRIMARY KEY uniek moet zijn is er maar één combinatie mogelijk en dat is precies de bedoeling.

4. Relaties leggen

De volgende stap is het maken van de relaties. We zullen in deze stap vol gebruik maken van de mogelijkheden van phpMyAdmim door de het gebruik van de Designer. Open deze in je database.

Je ziet nu het volgende:

Waarschijnlijk staan jouw tabellen rommelig. Fatsoeneer deze eerst zoals bovenstaande afbeelding. Klik ook op de Angelar button. Met deze knop kun je de lijntjes van de relaties mooi in een hoek laten lopen. Klik als laatste op de bewaarknop zodat de posities van de tabellen blijven bewaard.

We zullen nu een relatie leggen van de tabel spacetrips naar de koppeltabel spacetrips_spacetravelers. Klik hiervoor op Create relations (zie bovenstaande afbeelding). Klik daarna eerst op de referenced key van de tabel spacetrips en vervolgens op de FOREIGN KEY van de tabel spacetrips_spacetravelers. Hierna krijg je vraag welke soort relatie moet worden gemaakt en dat wordt ON UPDATE CASCEDE (en ON DELETE RESTRICT maar dat staat standaard in de database al zo ingesteld dus dit mag je ook open laten). Zie onderstaande afbeelding. De relatie wordt daarna gemaakt.

 

Merk op dat de tabel spacetravelers een smal bolletje heeft en de tabel spacetrips_spacetravelers een dikker half bolletje. Dit betekent een een-op-veel relatie. Maar omdat het gaat naar een koppeltabel wordt het uiteindelijk een-veel-op-veel relatie.

 

De uiteindelijke database in Designer

5. Informatie in en uit de database

In dit lesonderdeel ga je zelf meedoen met het maken van de lessen. De opdracht is om SQL-opdrachten te verzinnen in je database. Een voorbeeld hiervan is als volgt: geef alle reizen die vertrekken van mars in combinatie met de reizigers (en de ruimtevaarders als je deze extra moeilijk wilt maken). Kun je nog meer van dit soort opdrachten verzinnen? Zo ja, dan worden ze hieronder gepubliceerd. Geef wel meteen de oplossing erbij.

Zet de opdrachten in je (Google) werkstuk en op deze manier bereidt je gezamelijk je examen of tentamen voor. Voor de beoordeling van je examen of tentamen telt alleen je eigen gegeven opdracht mee. De andere opdrachten zijn om te oefenen.

 

6. Oefenexamen 1 - De taartenbakkerij

Er zijn bakkerijen en zij maken taarten. De taarten worden verkocht aan klanten die bij de bakkerij bekend zijn en staan geregistreerd. Er zijn 1 of meerdere bakkers aan een bakkerij verbonden en zij hebben allen een bepaald level. Er bestaan 5 levels beginnend bij stagiaire en eindigend bij topbakker. De taarten die ze verkopen hebben een smaak, een kleur en een prijs. Tevens moet er per bakkerij een voorraad worden bijgehouden zodat op de webshop klanten kunnen zien of de taarten voorradig zijn.

In de database wordt ook bijgehouden aan wie wordt verkocht en aan de hand van deze bestellingen kan een factuur worden uitgedraaid. Tevens kan er aan het eind van het jaar een jaarrekening worden opgemaakt.

Maak deze database eerst in ontwerp en daarna in MySQL. Vul de taarten, klanten (10), smaken(5) en kleuren(5) en geef alle queries die bovenstaande vragen beantwoorden.

7. Oefenexamen 2 - Een muziekbibliotheek maken

Er is in de stad een muziekbibliotheek waar je ouderwets platen kunt lenen. Platen zijn weer opnieuw in trek. Je kunt lid worden van deze bibliotheek. De bibliotheek heeft dus een ledenlijst. Er zijn verschillende soorten abonnementen: een gratis abonnement waar je maximaal 2 platen per jaar kunt lenen, een betaald goedkoop abonnement waarbij je 2 platen per week kunt lenen en een plusabonnememt waarbij je 10 platen per week mag lenen.

In de databases moet het mogelijk zijn de geleende platen te registreren inclusief een de datum wanneer ze zijn uitgeleend en wanneer de platen terug moeten zijn. Je kunt daarbij gebruik maken van het formaat DATE in MySQL.

De database bevat dus ook een lijst van platen. Een plaat kent een titel, een artiest en een release datum.

Als een plaat wordt uitgeleend dan moet ook de uitlener - diegene die aan de balie zit - worden vermeld.

8. Oefenexamen 3 - De schaakvereniging

Ontwerp  een database voor de administratie van de schaakvereniging "Schaken is Plezier". Met deze administratie wil de vereniging de activiteiten van de vereniging vastleggen. Van ieder lid (member) wordt opgeslagen het (unieke) lidnummer, naam, adres, postcode, woonplaats en telefoonnummer.

De vereniging SiP draait op basis van vrijwilligers. Leden kunnen één of meerdere functies (positions) vervullen in de vereniging zoals bijvoorbeeld voorzitter, materiaalbeheerder, jeugdtrainer, bar etc (je mag meer functies verzinnen). Er wordt een lijst van wie welke functies heeft bijgehouden.

Er zijn twee soorten leden: recreatieve- en competiteleden(recreational/competition). De laatste groep doet in teamvorm mee aan een interne competitie (league). In de competitie wordt een ranglijst (ranking) bijgehouden wie het sterkste team is. Teams van de vereniging kunnen elkaar uitdagen tegen elkaar te spelen. In de database moet het mogelijk zijn deze wedstrijden te registreren en te koppelen aan het sterkste team.

Ontwerp deze database in Gliffy of op papier en geef de contrains van de database.

9. Feedback van leerlingen

Dit onderdeel is uitgeschakeld door de docent.

10. Learning SQL part 5 - oplossingen van opdrachten

Dit onderdeel is uitgeschakeld door de docent.