Lockers, dubbele rijen en de derde normaalvorm
Op het Mendelcollege worden 840 lockers verhuurd aan leerlingen. De administratie gebruikt één tabel Lockers met de kolommen Leerlingnummer, Naam, Klas, Lockernummer, Locatie en Geldig_tot. Een leerling kan maximaal twee lockers huren; dezelfde persoonsgegevens staan dan in twee rijen. In september wisselen 60 leerlingen van klas. De systeembeheerder past die wijziging alleen aan in de rij van hun eerste locker aan. Bij de jaarlijkse controle blijkt dat 23 leerlingen in twee verschillende klassen staan. Ook kan de administratie een nieuwe leerling die nog geen locker huurt niet invoeren zonder een lockernummer te verzinnen. De school wil de tabel opsplitsen en daarna een overzicht maken van het aantal verhuurde lockers per klas, maar alleen voor klassen met meer dan 30 lockers. De decaan vraagt zich af of de nieuwe structuur ook de mentor per klas kan bevatten, want elke klas heeft één mentor.
1. Leg met een concreet voorbeeld uit de gegevens uit waarom de huidige tabel een update-anomalie vertoont en waardoor de telling van 23 leerlingen in twee klassen kan ontstaan.
Read the worked answer
De tabel bevat dubbele gegevens: naam en klas van een leerling staan bij elke gehuurde locker opnieuw. Stel dat Soufiane (leerlingnummer 18452) twee lockers huurt en van 3A naar 3B gaat. De beheerder past volgens de casus alleen de rij van de eerste locker aan. De tweede rij blijft dan '3A' bevatten. Daardoor bestaat één persoon met twee verschillende klassen en is niet meer eenduidig uit de tabel af te leiden in welke klas hij zit. De telling van 23 ontstaat waarschijnlijk doordat de controle per leerling kijkt of dezelfde leerling in meerdere klassen voorkomt.
Formeel is Klas een eigenschap van Leerlingnummer, niet van Lockernummer. Doordat Klas bij elke locker wordt herhaald, is de tabel niet in derde normaalvorm: er is redundantie en een partiële update is mogelijk. De update-anomalie is dus het gevolg van het opslaan van dezelfde functionele afhankelijkheid op meerdere plaatsen.
2. Ontwerp een genormaliseerde structuur in derde normaalvorm. Geef per tabel de kolommen en sleutels aan en leg uit waarom de mentor zonder extra tabelprobleem kan worden opgenomen.
Read the worked answer
Een passende structuur is:
- Leerling(Leerlingnummer PK, Naam, Klascode FK)
- Klas(Klascode PK, Mentor)
- Locker(Lockernummer PK, Locatie)
- Verhuur(Leerlingnummer FK, Lockernummer FK, Geldig_tot, PK(Leerlingnummer, Lockernummer))
In Leerling hangt Naam af van Leerlingnummer en Klascode is een foreign key. In Klas hangt Mentor af van Klascode. In Locker hangt Locatie af van Lockernummer. In Verhuur hangt Geldig_tot af van de combinatie van leerling en locker; er zijn geen niet-sleutelattributen die van een deel van de sleutel afhangen.
De mentor kan in Klas worden opgenomen omdat Klascode → Mentor een functionele afhankelijkheid binnen die tabel is. Zou Mentor in Leerling staan, dan ontstaat een transitive afhankelijkheid: Leerlingnummer → Klascode → Mentor. Dat schendt de derde normaalvorm. Door Klas als aparte tabel te gebruiken, staat de mentor één keer per klas en verandert hij mee als de klas een andere mentor krijgt.
3. Schrijf een SQL-query die per klas het aantal verhuurde lockers telt voor het huidige schooljaar en alleen klassen toont met meer dan 30 verhuurde lockers. Licht toe hoe je dubbele lockers per leerling en aflopende huurcontracten behandelt.
Read the worked answer
SELECT k.Klascode, COUNT(*) AS AantalLockers
FROM Verhuur v
JOIN Leerling l ON l.Leerlingnummer = v.Leerlingnummer
JOIN Klas k ON k.Klascode = l.Klascode
WHERE v.Geldig_tot >= CURRENT_DATE
GROUP BY k.Klascode
HAVING COUNT(*) > 30;
De joins zijn nodig omdat de klas van een leerling in Leerling staat en de Klascode naar Klas verwijst. Het filter op Geldig_tot hoort in WHERE: aflopende contracten moeten vóór het groeperen worden verwijderd. HAVING filtert daarna de groepen, omdat je pas na COUNT(*) weet of een klas meer dan 30 lockers heeft.
COUNT(*) telt verhuurcontracten. Een leerling met twee lockers telt dus twee keer mee, en dat is hier gewenst: de vraag gaat over het aantal verhuurde lockers, niet over het aantal leerlingen. Als dezelfde locker door een dataprobleem dubbel in Verhuur zou staan, gebruik je COUNT(DISTINCT v.Lockernummer).