Docent: Martijn Hengelmolen

1
ltMdl01 hoorcollege 1gt

• Docent Martijn Hengelmolen
• Email Hengelmolen_at_initworks.com
• Aanwezig maandag, dinsdag

2
Inhoud Hoorcollege 1

• Waarom modelleren 1
• Voorbeeld tekstueel ontwerp
• Opdracht

3
Voorbeeld model

• Rechtopstaande holle cilinder

4
Voorbeeld model

• Rechtopstaande holle cilinder
• Onderkant is gesloten

5
Voorbeeld model

• Rechtopstaande holle cilinder
• Onderkant is gesloten
• Aan de zijkant zit een driekwart ovaal
• Ovaal is kleiner dan cilinder
• Kleur is wit

6
Voorbeeld model

• Een mok, waterkoker? Thermoskan?
• Vraag is dit eenduidig?

7
Opdracht

• Verzin een voorwerp
• Beschrijf dit voorwerp
• Geef tekstuele beschrijving aan mede student.

8
SELECT basisbegrippenhoofdstuk 5 van der Lans

• datatypen (num, alfanum, datum/tijd)
• systeemvariabelen (USER, TODAY)
• expressies (o.a. CASE)
• scalaire vs. set functies
• scalaire functies input is 1 rij
• set functies input is verzameling rijen
• casting

9
SELECT instructiedefinitie

• ltselect-instructiegt
• ltselect-componentgt
• ltfrom-componentgt
• ltwhere-componentgt
• ltgroup-by-componentgt
• lthaving-componentgt
• ltorder-by-componentgt

10
SELECT instructievoorbeeld

• SELECT PNAAM, LEEFTIJD kolom(men)
• FROM PATIENT tabel(len)
• WHERE LEEFTIJD gt 50 voorwaarde voor rij

11
SELECT instructievolgorde van uitvoeren
(intern)

• SELECT kolommen 3
• FROM tabellen 1
• WHERE voorwaarde voor rij 2
• ORDER BY kolom 4

1. FROM
2. WHERE
3. SELECT
4. ORDER BY
12
SELECT instructievolgorde (voorbeeld)

• SELECT ANO, ANAAM
• FROM A
• WHERE KLEURBlauw
• ORDER BY ANAAM

13
SELECT instructieFROM component (definitie)

• ltfrom-componentgt FROM lttabelrefgt
  ,lttabelrefgt...
• lttabelrefgt lttabelspecgt AS ltaliasgt
• lttabelspecgt ltusergt. tabelnaam

14
SELECT instructieFROM component (voorbeeld)

• SELECT teamnr, naamFROM teams AS t, spelers AS
  sWHERE t.spelersnr s.spelersnr
• Dit is een voorbeeld van een natural join

15
Natural join in stapjes1. FROM - input

• SELECT ....
• FROM teams AS t, spelers AS s

16
Natural join in stapjes2. FROM - resultaat

• SELECT ... FROM teams AS t, spelers AS s

17
Natural join in stapjes3. WHERE - input

• WHERE t.spelersnr s.spelersnr

18
Natural join in stapjes4. WHERE - resultaat

• SELECT ... FROM teams AS t, spelers AS s
• WHERE t.spelersnr s.spelersnr

19
Natural join in stapjes5. SELECT - resultaat

• SELECT teamnr, naam as aanvoerder
• FROM teams AS t, spelers AS s
• WHERE t.spelersnr s.spelersnr

20
SELECT instructieWHERE (pseudo-code)

• WHERE-resultaat
• for each ROW in FROM-resultaat doif
  conditiewaar thenWHERE-resultaat ROW
• endfor

21
SELECT instructieWHERE (operatoren etc)

• vergelijking lt gt lt gt ltgt
• AND, OR, NOT
• BETWEEN, IN, LIKE, NULL
• IN met subquery
• vergelijkingsoperator met subquery
• ANY, ALL
• EXISTS

22
SELECT instructieWHERE (voorbeelden)

• WHEREGEB_DATUM gt 1970-12-31 ANDNOT NAAM
  Haas
• WHERE GEB_DATUM BETWEEN 1960-1-1 AND
  1970-1-1
• WHERE NAAM IN (Smit,Jansen,Zwart)

23
SELECT instructieWHERE (IN met subquery)

• SELECT naam AS aanvoerderFROM spelersWHERE
  spelersnr IN (SELECT spelersnr FROM teams)

24
SELECT instructieWHERE (EXISTS)

• SELECT naam AS aanvoerderFROM spelersWHERE
  EXISTS(SELECT FROM teams WHERE
  spelersnrspelers.spelersnr)

25
SELECT instructieWHERE (ALL)

• SELECT naam is het oudst AS oudste,
  geb_datumFROM spelersWHERE geb_datum lt
  ALL(SELECT geb_datum FROM spelers)

26
SELECT instructieGROUP BY, HAVING

• volgend hoorcollege
• SET functies (COUNT, SUM, MIN, MAX, AVG, STDEV)
• GROUP BY, HAVING

27
Referentiele Integriteit

• spelernsnr in beide tabellen moeten
  gesynchroniseerd blijven

primary key
foreign key
28
Referentiele Integriteit refs aangeven bij
CREATE

• CREATE TABLE teams(teamnr SMALLINT NOT
  NULL,spelersnr SMALLINT NOT NULL,divisie CHAR(
  6) NOT NULL,PRIMARY KEY (teamnr),FOREIGN KEY
  (spelersnr) REFERENCES spelers (spelersnr))

29
Referentiele Integriteitrefererende acties

• default (SOLID)
• ON UPDATE RESTRICT
• ON DELETE RESTRICT(dwz wijzigen/verwijderen van
  een spelersnr in de SPELERS tabel wordt
  tegengehouden als spelersnr in TEAMS voorkomt)

30
Referentiele Integriteitrefererende acties

• alternatief 1
• ON UPDATE CASCADE
• ON DELETE CASCADE(dwz update/delete van een
  spelersnr in de SPELERS tabel triggert
  automatischeen update/delete in TEAMS)
• alternatief 2
• ON UPDATE SET NULL
• ON DELETE SET NULL

31
Referentiele Integriteitvragen

• wat is de beste oplossing voor SPELERS en TEAMS?
• ON UPDATE restrict, cascade, set null
• ON DELETE restrict, cascade, set null
• wat is de beste oplossing voor SPELERS en
  BOETES?
• ON UPDATE restrict, cascade, set null
• ON DELETE restrict, cascade, set null

32
Database Ontwerp

• Wat is het?
• Hoe doe je het?
• Voorbeeld
• Normaliseren (1NF, 2NF, 3NF, BCNF)
• Opdracht

33
Database ontwerp Wat is het ?

• het bepalen van de tabellen en hun kolommen die
  nodig zijn om bepaalde gegevens op te slaan
  (structuur)
• logisch ontwerp (niet fysiek)
• C. J. Date database design is still very much
  of an art, not a science
• gegevens integriteit

34
Database ontwerp Hoe doe je het?

• ontwerpmethoden (zie Kroenke)
• E/R (entity-relationship)
• semantisch object model
• controle
• normaliseren

35
Database ontwerp voorbeeld administratie van
uitgeleende boeken (1)

• voor wie
• de eigenaar van de boeken
• functie
• het geven van een actueel overzicht van alle
  uitgeleende boeken bovendien per boek
• aan wie (het boek is uitgeleend)
• sinds wanneer (het boek is uitgeleend)

36
Database ontwerp voorbeeld administratie van
uitgeleende boeken (2)

• bedenk eerst hoe je het zonder ge-automatiseerd
  systeem zou doen!

?
37
Database ontwerp voorbeeld administratie van
uitgeleende boeken (3)

• bedenk eerst hoe je het zonder ge-automatiseerd
  systeem zou doen!
• schrift met 1 regel per uitgeleend boek(auteur,
  titel, lener_naam, lener_telnr, sinds)
• vgl. database met 1 tabeluitgeleende boeken

38
Database ontwerp voorbeeld administratie van
uitgeleende boeken (4)

• problemen
• wijzigen van telnr op meerdere plaatsen
• bij terugbrengen boek ook telnr weg
• hoe komt dit ?
• afhankelijkheid lener_naam -gt lener_telnr
• lener_naam is een determinant van lener_telnr

39
Database ontwerp voorbeeld administratie van
uitgeleende boeken (5)

• oplossing 2 tabellen
• schrift met uitgeleende boeken
• adresboekje (of GSM telefoon) naam telnr

40
Normalisatie 1NF (first normal form)

• Definitie 1NFEen tabel is in 1NF als voor elke
  waarde van die tabel elke rij precies 1 waarde
  voor elke attribuut heeftvoorbeeld in de
  tabel leners heeft elke rij 1 naam en 1 telnr

41
Normalisatie 2NF

• Definitie 2NF(aanname er is slechts 1
  kandidaat sleutel die de primaire sleutel
  is)Een tabel is in 2NF als deze in 1NF is, en
  elk niet-sleutel attribuut (op de een of andere
  manier) afhankelijk is van de primaire sleutel

42
Normalisatie 2NF
PK
43
Normalisatie 2NF
PK
PK
44
Normalisatie 3NF

• Definitie 3NF(aanname er is slechts 1
  kandidaat sleutel die de primaire sleutel
  is)Een tabel is in 3NF als deze in 2NF is, en
  elk niet-sleutel attribuut niet-transitief
  afhankelijk is van de primaire sleutel

45
Normalisatie 3NF
PK
2NF
3NF
46
Normalisatie BCNF (Boyce/Codd Normal Form)

• (informele) definitieeen tabel is in BCNF als
  de enige determinanten kandidaat-sleutels
  zijn(dwz alle afhankelijkheidspijlen beginnen
  bij kandidaat-sleutels)

47
Normalisatie BCNF (Boyce/Codd Normal Form)
48
Normalisatie BCNF (Boyce/Codd Normal Form)
49
Normalisatieconclusie

• Wat zijn we eigenlijk aan het doen?
• Normalisatie is een kwestie van gezond
  verstand!
• De zojuist besproken methoden zijn
  geformaliseerd gezond verstand!
• Met wat ervaring weet je intuïtief wanneer
  tabellen BCNF zijn

50
Opdracht (5 minuten)

• Breng in BCNF

51
Opdracht

• Afhankelijkheden

52
Opdrachtoplossing (BCNF)

• aannames
• docentnaam is uniek, vaknaam is uniek
• 1 vak wordt door 1 docent gegeven

53
Zelfstudie 2

• hoofdstuk 1 t/m 9 en 17 uit van der Lans
  inclusief opgaven (niet allemaal)
• doe de opgaven uitgedeeld op het college (zie ook
  create_art_lev_etc.sql om de tabellen aan te
  maken)