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

• Informatica
• WO
• Studiejaar 4,5

SQL

WHERE: LIKE

Met de LIKE operator kun je in combinatie met WHERE 

filteren (vergelijken) als je de waarde niet exact weet

LIKE heeft ook bijzondere tekens (zgn wildcards):

• % geeft je aan dat er iets voor/na/tussen KAN staan
• _ geeft aan dat er 1 karakter staat op die plek
• \_ geeft aan dat er een "_" op die plek moet staan
  
• \% geeft aan dat er een "%" op die plek moet staan

Commentaar 1/2

Ook in SQL kun je commentaar toevoegen

Dit doe je om achtergrond te geven bij je statement

Je kan aan het einde van elke regel commentaar op deze manier toevoegen:

SELECT * FROM Cust; -- dit is commentaar

SELECT * -- dit is ook commentaar
FROM Cust; -- dit is nog meer commentaar

Commentaar 2/2

Om meerdere rijen (of in een rij zelf) commentaar toe te voegen gebruik je: /* ... */

 

SELECT * FROM Cust; /* commentaar! */

SELECT * /* ook dit is commentaar */

FROM Cust; 

SELECT CustomerName, /*City,*/ Country FROM Customers;

Stof

SQL

InformaticaBaas 

D. PHP en SQL - SMC

Hoofdstuk 3.1

Leerdoelen

Na deze les kun je:

• uitleggen met welk teken in SQL een string wordt aangegeven;
• een aantal voorbeelden van een identificator geven;
• in je eigen woorden vertellen wat redundantie is;
• de kenmerken opnoemen van een primaire sleutel;
• uitleggen wat een vreemde of refererende sleutel is;
• een statement met JOIN schrijven, lezen en uitleggen;
  
• aangeven waar ERD voor staat en wat het is.
  

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.

string en identificator 1/2

In alle SQL-dialecten wordt ' ' gebruikt als string

De " " wordt over het algemeen gebruikt voor zogenaamde identificatoren

Ik wil dat je voor SQL ' ' gebruikt voor strings, " " zal ik fout rekenen op de toets en in het PO!

string en identificator 2/2

Een identificator is in SQL 

een naam die je geeft aan 

een database-object, zoals:

• een tabel
  
• een kolom
  
• een weergave (view)
  
• een index
  
• een schema
  
• een database

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)
• elk gegeven op één plek, dus één plek om te wijzigen 

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

https://informatica.stmichaelcollege.nl/SQL/

Een aantal opdrachten hierna maken gebruik van:

https://informatica.stmichaelcollege.nl/SQL/

Je logt daar in met je schoolmail (als hij het niet automatisch doet)

Er worden automatisch tabellen aangemaakt met je leerlingnummer

Met de knop "Maak/reset tabellen" kun je de tabellen terugzetten

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

FROM adressen

JOIN leerlingen ON adressen.leerlingID= leerlingen.leerlingID;

Like?

1. Ga naar https://informatica.stmichaelcollege.nl/SQL/oefenen.php
2. Druk op de knop: 
3. maak een statement die alle achternamen
   beginnend met D laat zien in de tabel ...._leerlingen
4. pas deze statement aan zodat hij 3 resultaten laat zien:
   
   
   
5. pas de statement aan zodat het LLNR tussen 100 en 200 moet zijn

select * from jklos_leerlingen WHERE achternaam LIKE 'D%' AND LLNR > 100 AND LLNR < 200 LIMIT 3;

valkuil LIMIT 3 na de 'D%'

Antwoord

In mijn geval is een juist statement:

SELECT * 

FROM jklos_leerlingen 

WHERE achternaam 

LIKE 'D%' AND LLNR > 100 AND LLNR < 200 LIMIT 3;

SELECT 

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel 

FROM 

jklos_auteurs 

JOIN 

jklos_boeken 

ON 

jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

Sleutels

Er zijn twee soorten sleutels:

• primaire sleutels (PK: primary key)
    > maakt elke rij uniek
    > mag niet leeg zijn
• vreemde of refererende sleutels (FK: foreign key)
    > verwijst meestal naar primaire sleutel in andere tabel
    > legt de relatie tussen de verschillende tabellen

Primaire Sleutels (PK)

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

De waarde van een primaire sleutel verandert in principe niet... waarom zou dat zo zijn?

Vreemde Sleutels (FK)

                                              (afbeelding 

                                               uit PHPMyAdmin)

De sleutel leerlingID is de primaire sleutel in leerlingen

De kolom leerlingID is de vreemde sleutel in adressen

Als je een primaire sleutel hebt toegekend
in een tabel in je RDBMS,
is het dan problematisch als je die wil veranderen?

(INNER) JOIN ... ON 1/2

Om gegevens uit meerdere tabellen samen te selecteren waarbij de vreemde sleutel een... sleutelrol ... vervult, 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 kunnen verschillend zijn (maar is niet verplicht)
• 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

Ga naar https://informatica.stmichaelcollege.nl/SQL/

1. gebruik SELECT * FROM ... om de kolommen te vinden van ..._auteurs en ..._boeken
2. maak gebruik van JOIN ... ON om het geboortejaar en achternaam van de auteur met de titel van elk boek in hetzelfde resultaat te laten zien:

SELECT 

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel 

FROM 

jklos_auteurs 

JOIN 

jklos_boeken 

ON 

jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

Antwoord

Een correct statement is (in mijn geval):

SELECT

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel

FROM

jklos_auteurs

JOIN

jklos_boeken

ON

jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

SELECT 

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel 

FROM 

jklos_auteurs 

JOIN 

jklos_boeken 

ON 

jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

ERD 1/3

ERD is de afkorting voor Entity Relationship Diagram

Met een ERD laat je de verbanden 

tussen entiteiten zien

Een entiteit is een ding waarover je 

gegevens bewaart (leerling, adres, boek)

In je database wordt elke entiteit 

meestal een tabel

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)

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 stmichae_v6;

Welke query geeft alle
achternamen in leerlingen
die beginnen met een "B"?

Maak selectie van achternamen van auteurs geboren
voor 1940

Leerdoelen

Nu kun je:

• uitleggen met welk teken in SQL een string wordt aangegeven;
  
• een aantal voorbeelden van een identificator geven;
  
• in je eigen woorden vertellen wat redundantie is;
• de kenmerken opnoemen van een primaire sleutel;
  
• uitleggen wat een vreemde of refererende sleutel is;
  
• een statement met JOIN lezen en uitleggen;
  
• aangeven waar ERD voor staat en wat het is.

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/

Exit ticket

https://forms.gle/7J97NiRs9nqWjk5V6