SQL 3 - primaire sleutel, refererende sleutel, JOIN ON, redundantie, ERD

• Informatica
• WO
• Studiejaar 4,5

SQL

Fundament

SQL

Fundament kerndomein C: Databases en SQL

Hoofdstuk 2.1 tmt 3.2

https://fundament-online.nl/leeromgeving/content.php?paragraaf_id=114878

Leerdoelen

Na deze les kun je:

• in je eigen woorden vertellen wat redundantie is;
  
• vertellen wat een primaire sleutel is;
• de kenmerken opnoemen van een primaire sleutel;
• uitleggen wat een vreemde of refererende sleutel is;
• een query met JOIN lezen en uitleggen;
  
• aangeven waar ERD voor staat.
  

Rode achtergrond betekent: klas is stil, dit zijn de leerdoelen en quizzes waarbij niet overlegd mag worden.

Doel van deze les is om het leren zichtbaar te maken voor zowel leerling als docent.

Eén of meerdere tabellen? 1/2

Een RDBMS heeft vaak meerdere tabellen

Waarom zijn meerdere tabellen efficiënter dan 1 tabel?

Eén of meerdere tabellen? 1/2

Een RDBMS heeft vaak meerdere tabellen

Waarom zijn meerdere tabellen efficiënter dan 1 tabel?

• minder dubbele gegevens (bv. meerdere mensen wonen op 1 adres en dan staat op elke rij het adres dubbel)

Eén of meerdere tabellen? 1/2

Een RDBMS heeft vaak meerdere tabellen

Waarom zijn meerdere tabellen efficiënter dan 1 tabel?

• minder dubbele gegevens (bv. meerdere mensen wonen op 1 adres en dan staat op elke rij het adres dubbel)
• sneller zoeken (want minder data)

Eén of meerdere tabellen? 1/2

Een RDBMS heeft vaak meerdere tabellen

Waarom zijn meerdere tabellen efficiënter dan 1 tabel?

• minder dubbele gegevens (bv. meerdere mensen wonen op 1 adres en dan staat op elke rij het adres dubbel)
• sneller zoeken (want minder data)
• centrale opslag van gegevens (vgl. met CSS)

Eén of meerdere tabellen? 2/2

Meerdere tabellen leidt dus tot meer efficiëntie

Maar hoe koppel je die tabellen aan elkaar?

Dit koppelen gebeurt via sleutels

Redundantie

Redundantie is het bestaan van dubbele gegevens:
dat zijn overtollige gegevens (dus gegevens die je teveel hebt, overtollig zijn)

Redundantie in dit geval is gevaarlijk, omdat je daarmee conflicten kan krijgen (bv. als 1 wordt aangepast en de ander niet, welke is dan correct?)

Sleutels

Er zijn twee soorten sleutels:

• primaire sleutels
    > maakt elke rij uniek
    > mag niet leeg zijn
• vreemde of refererende sleutels
    > verwijst naar primaire sleutel
    > legt de relatie tussen de verschillende tabellen

Primaire Sleutels

Een primaire sleutel in een tabel is dus uniek en moet ingevuld zijn (in Engels: primary key)

In de RDBMS van Magister is de primaire sleutel van de leerlingentabel bijvoorbeeld jouw leerlingnummer

Dat leerlingnummer is uniek voor jou bij het SMC

Vreemde Sleutels 1/2

Een vreemde (of refererende) sleutel in een tabel legt de relatie met een andere tabel (in Engels: foreign key)

In de RDBMS van Magister staat jouw huisadres niet in de leerlingentabel maar in een adrestabel

In de adrestabel staat als vreemde sleutel jouw leerlingnummer en daarmee is de relatie gelegd

Vreemde Sleutels 2/2

                                              (afbeelding uit PHPMyAdmin)

De sleutel leerlingID is de primaire sleutel in leerlingen

De kolom leerlingID is de vreemde sleutel in adressen

(INNER) JOIN ... ON 1/2

Om gegevens uit meerdere tabellen samen te selecteren waarbij de vreemde sleutel een... sleutelrol ... vervult (sorry), bestaat de JOIN ... ON clausule (INNER JOIN ... ON is hetzelfde!):

SELECT auteurs.auteurnr, boeken.titel 

FROM boeken

JOIN auteurs 

ON auteurs.auteurnr = boeken.auteurnr;

(INNER) JOIN ... ON 2/2

Belangrijke opmerkingen:

• FROM en JOIN geven de tabellen aan die worden samengevoegd, dus
   ze moeten verschillend zijn
• met ____.kolomnaam geef je de tabel aan van die kolomnaam
• om te vergelijken is de vreemde sleutel nodig

SELECT auteurs.auteurnr, boeken.titel 

FROM boeken

JOIN auteurs ON auteurs.auteurnr = boeken.auteurnr;

Opdracht: Join

1. Ga naar https://informatica.stmichaelcollege.nl/SQL/
2. Maak een JOIN ... ON statement zodat je de uitgeleende boeken ziet en de voornamen van de leerlingen
   die dat hebben gedaan
   hint:
   gebruik SELECT * FROM ... om de kolommen
   te vinden van leerlingen en adressen
   voornaam en achternaam uit leerlingen
   straatnaam en huisnummer uit adressen

SELECT leerlingen.voornaam, leerlingen.achternaam, adressen.straatnaam, adressen.huisnummer

FROM adressen

JOIN leerlingen ON adressen.leerlingID= leerlingen.leerlingID;

ERD 1/3

ERD is de afkorting voor Entity Relationship Diagram

Hiermee laat je de verbanden tussen entiteiten zien

De entiteiten in een ERD zijn de verschillende tabellen

ERD 2/3

Een ERD kan problemen zichtbaar maken doordat de relaties op een overzichtelijke manier worden weergegeven

Een ERD maken en up-to-date houden is dus belangrijk bij het opzetten en onderhouden van een RDBMS

ERD 3/3

Er zijn verschillende manieren om een ERD te maken:

• pen en papier
• whiteboard
• websites (Lucidchart.com, Draw.io)
• software (Microsoft Visio, MySQL Workbench)

Een tabel

PHPMyAdmin                         SQL output

             

Welke query laat alleen de achternamen van
alle leerlingen zien?

1. SELECT achternamen FROM leerlingen;
2. SELECT * FROM leerlingen;
3. SELECT achternaam FROM leerlingen;
4. SELECT achternaam FROM stmichael_v6;

Welke query selecteert alle
namen die beginnen
met een "B"?

Query voor selectie van achternamen van rijen geboren
voor 1940

ERD 3/3

Er zijn verschillende notaties in een ERD

Hiermee wordt de relatie aangegeven tussen sleutels

Opdracht # 2

1. Ga naar https://informatica.stmichaelcollege.nl
2. Klik op de SQL link
   
   
3. maak een query die alle achternamen beginnend met D laat zien in de tabel leerlingen
4. limit deze query tot 3
5. doe nogmaals de join

SELECT leerlingen.voornaam, leerlingen.achternaam, adressen.straatnaam, adressen.huisnummer

FROM adressen

JOIN leerlingen ON adressen.leerlingID= leerlingen.leerlingID;

Hoe zou je zoeken naar alle namen die beginnen met de letter 'A'?

WHERE name LIKE ...

1. '%A%'
2. '_A'
3. 'A%'
4. '%A'

Welke van de volgende zoekopdrachten geeft alle resultaten die eindigen met 'son'?

WHERE name LIKE ...

1. '%son'
2. '_son'
3. 'son%'
4. '%son%'

Leerdoelen

Nu kun je:

• in je eigen woorden vertellen wat redundantie is;
  
• vertellen wat een primaire sleutel is;
  
• de kenmerken opnoemen van een primaire sleutel;
  
• uitleggen wat een vreemde of refererende sleutel is;
  
• een query met JOIN lezen en uitleggen;
• aangeven waar ERD voor staat.
  

Rode achtergrond betekent: klas is stil, dit zijn de leerdoelen en quizzes waarbij niet overlegd mag worden.

Doel van deze les is om het leren zichtbaar te maken voor zowel leerling als docent.

Sponsopdracht

Klaar om wat meer te spelen met SQL?

Doe het SQL Murder Mystery spel!

https://mystery.knightlab.com/

Je begint met het vinden van de tabel kolommen/velden en gaat dan de informatie met INSERT toevoegen: murder - Jan.15,2018 - SQL City

Exit ticket

https://forms.gle/7J97NiRs9nqWjk5V6