Document

<Mdl01 hoorcollege 1>
Docent: Martijn Hengelmolen
Email:
[email protected]
Aanwezig: maandag, dinsdag
1
Inhoud Hoorcollege 1



Waarom modelleren 1
Voorbeeld tekstueel ontwerp
Opdracht
2
Voorbeeld model

Rechtopstaande holle cilinder
3
Voorbeeld model


Rechtopstaande holle cilinder
Onderkant is gesloten
4
Voorbeeld model





Rechtopstaande holle cilinder
Onderkant is gesloten
Aan de zijkant zit een driekwart ovaal
Ovaal is kleiner dan cilinder
Kleur is wit
5
Voorbeeld model


Een mok, waterkoker? Thermoskan?
Vraag: is dit eenduidig?
6
Opdracht



Verzin een voorwerp
Beschrijf dit voorwerp
Geef tekstuele beschrijving aan mede
student.
7
SELECT basisbegrippen
hoofdstuk 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
8
SELECT instructie:
definitie
<select-instructie>::=
<select-component>
<from-component>
[<where-component>]
[<group-by-component>]
[<having-component>]
[<order-by-component>]
9
SELECT instructie:
voorbeeld
SELECT PNAAM, LEEFTIJD
FROM PATIENT
WHERE LEEFTIJD > 50;
PAT#
1234
2345
2346
1243
PNAAM
Jansen
Smit
Lopez
Peters
LEEFTIJD
21
56
35
89
[kolom(men)]
[tabel(len)]
[voorwaarde voor rij]
GESLACHT
M
V
V
M
ARTS
Schouten
van Dijk
Kleine
Oei
10
SELECT instructie:
volgorde van uitvoeren
(intern)
SELECT [kolommen]
FROM [tabellen]
WHERE [voorwaarde voor rij]
ORDER BY [kolom]
1. FROM
2. WHERE
3. SELECT
[3]
[1]
[2]
[4]
4. ORDER BY
a
b
11
SELECT instructie:
volgorde (voorbeeld)
1. FROM A
SELECT ANO, ANAAM
FROM A
WHERE KLEUR=‘Blauw’
ORDER BY ANAAM
ANO
A1
A2
A3
A4
A5
A6
ANAAM
Moer
Bout
Schroef
Schroef
Kamrad
Nok
KLEUR
Rood
Groen
Blauw
Rood
Blauw
Rood
GEW
12
17
17
14
12
19
PLAATS
Londen
Parijs
Rome
Londen
Parijs
Londen
2. WHERE KLEUR=‘Blauw’
ANO
A3
A5
ANAAM
Schroef
Kamrad
KLEUR
Blauw
Blauw
GEW
17
12
PLAATS
Rome
Parijs
3. SELECT ANO, ANAAM
ANO
A3
A5
ANAAM
Schroef
Kamrad
4. ORDER BY ANAAM
ANO
A5
A3
ANAAM
Kamrad
Schroef
12
SELECT instructie:
FROM component (definitie)
<from-component> ::=
FROM <tabelref> [{,<tabelref>}...]
<tabelref> ::=
<tabelspec> [[AS] <alias>]
<tabelspec> ::=
[<user>.] tabelnaam
13
SELECT instructie:
FROM component (voorbeeld)
SELECT teamnr, naam
FROM teams AS t, spelers AS s
WHERE t.spelersnr = s.spelersnr
Dit is een voorbeeld van een natural join
14
Natural join in stapjes:
1. FROM - input
teams
TEAMNR SPELERSNR DIVISIE
1
12
2
2
14
2
3
15
3
spelers
SPELERSNR
11
12
13
14
15
NAAM
Jansen
Pietersen
Smit
Vlas
Stolk
SELECT ....
FROM teams AS t, spelers AS s
15
Natural join in stapjes:
2. FROM - resultaat
SELECT ... FROM teams AS t, spelers AS s
TEAMNR
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
SPELERSNR
12
12
12
12
12
14
14
14
14
14
15
15
15
15
15
DIVISIE
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
SPELERSNR
11
12
13
14
15
11
12
13
14
15
11
12
13
14
15
NAAM
Jansen
Pietersen
Smit
Vlas
Stolk
Jansen
Pietersen
Smit
Vlas
Stolk
Jansen
Pietersen
Smit
Vlas
Stolk
16
Natural join in stapjes:
3. WHERE - input
WHERE t.spelersnr = s.spelersnr;
TEAMNR
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
SPELERSNR
12
12
12
12
12
14
14
14
14
14
15
15
15
15
15
DIVISIE
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
SPELERSNR
11
12
13
14
15
11
12
13
14
15
11
12
13
14
15
NAAM
Jansen
Pietersen
Smit
Vlas
Stolk
Jansen
Pietersen
Smit
Vlas
Stolk
Jansen
Pietersen
Smit
Vlas
Stolk
17
Natural join in stapjes:
4. WHERE - resultaat
SELECT ... FROM teams AS t, spelers AS s
WHERE t.spelersnr = s.spelersnr;
TEAMNR
1
2
3
SPELERSNR
12
14
15
DIVISIE
2
2
3
SPELERSNR
12
14
15
NAAM
Pietersen
Vlas
Stolk
18
Natural join in stapjes:
5. SELECT - resultaat
SELECT teamnr, naam as aanvoerder
FROM teams AS t, spelers AS s
WHERE t.spelersnr = s.spelersnr;
TEAMNR
1
2
3
AANVOERDER
Pietersen
Vlas
Stolk
19
SELECT instructie:
WHERE (pseudo-code)
WHERE-resultaat := [ ];
for each ROW in FROM-resultaat do
if conditie=waar then
WHERE-resultaat :+ ROW;
endfor;
20
SELECT instructie:
WHERE (operatoren etc)







vergelijking: = < > <= >= <>
AND, OR, NOT
BETWEEN, IN, LIKE, NULL
IN met subquery
vergelijkingsoperator met subquery
ANY, ALL
EXISTS
21
SELECT instructie:
WHERE (voorbeelden)



WHERE
GEB_DATUM > ‘1970-12-31’
AND
NOT NAAM = ‘Haas’;
WHERE
GEB_DATUM BETWEEN ‘1960-1-1’ AND
‘1970-1-1’;
WHERE
NAAM IN (‘Smit’,’Jansen’,’Zwart’);
22
SELECT instructie:
WHERE (IN met subquery)

SELECT naam AS aanvoerder
FROM spelers
WHERE spelersnr IN
(SELECT spelersnr FROM teams);
23
SELECT instructie:
WHERE (EXISTS)

SELECT naam AS aanvoerder
FROM spelers
WHERE EXISTS
(SELECT * FROM teams
WHERE spelersnr=spelers.spelersnr);
24
SELECT instructie:
WHERE (ALL)

SELECT
naam+’ is het oudst’ AS oudste,
geb_datum
FROM spelers
WHERE geb_datum <= ALL
(SELECT geb_datum FROM spelers);
25
SELECT instructie:
GROUP BY, HAVING

volgend hoorcollege:


SET functies
(COUNT, SUM, MIN, MAX, AVG, STDEV)
GROUP BY, HAVING
26
Referentiele Integriteit

spelernsnr in beide tabellen moeten
gesynchroniseerd blijven
foreign key
teams
TEAMNR SPELERSNR
1
12
2
14
3
15
primary key
DIVISIE
2
2
3
spelers
SPELERSNR
11
12
13
14
15
NAAM
Jansen
Pietersen
Smit
Vlas
Stolk
27
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));
28
Referentiele Integriteit
refererende 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)
29
Referentiele Integriteit
refererende acties

alternatief 1:



ON UPDATE CASCADE
ON DELETE CASCADE
(dwz update/delete van een spelersnr
in de SPELERS tabel triggert automatisch
een update/delete in TEAMS)
alternatief 2:


ON UPDATE SET NULL
ON DELETE SET NULL
30
Referentiele Integriteit
vragen

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]
31
Database Ontwerp





Wat is het?
Hoe doe je het?
Voorbeeld
Normaliseren (1NF, 2NF, 3NF, BCNF)
Opdracht
32
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
33
Database ontwerp :
Hoe doe je het?

ontwerpmethoden (zie [Kroenke]):



E/R (entity-relationship)
semantisch object model
controle:

normaliseren
34
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)
35
Database ontwerp voorbeeld:
administratie van uitgeleende boeken (2)

bedenk eerst hoe je het zonder geautomatiseerd systeem zou doen!
?
36
Database ontwerp voorbeeld:
administratie van uitgeleende boeken (3)

bedenk eerst hoe je het zonder geautomatiseerd systeem zou doen!


schrift met 1 regel per uitgeleend boek
(auteur, titel, lener_naam,
lener_telnr, sinds)
vgl. database met 1 tabel:
‘uitgeleende boeken’
37
Database ontwerp voorbeeld:
administratie van uitgeleende boeken (4)
auteur
Damasio
Edelman
Singh
Kaufman
Maddox
Horgan

titel
The Feeling of What Happens
Bright Air, Brilliant Fire
The Code Book
At Home in the Universe
Wat we nog niet weten
The End of Science
lener_telnr
020-6123123
020-6456456
040-8345345
020-6123123
020-6123123
040-8345345
sinds
15-5-2001
15-6-2001
25-5-2001
15-8-2001
22-9-2001
15-2-2001
problemen:



lener_naam
Wim
Gijs
Tim
Wim
Wim
Tim
wijzigen van telnr op meerdere plaatsen
bij terugbrengen boek ook telnr weg
hoe komt dit ?


afhankelijkheid : lener_naam -> lener_telnr
lener_naam is een determinant van lener_telnr
38
Database ontwerp voorbeeld:
administratie van uitgeleende boeken (5)
uitgeleende boeken
auteur
titel
lener
sinds
Damasio The Feeling of WhatWim
Happens 15-5-2001
Edelman Bright Air, Brilliant Fire
Gijs
15-6-2001
Singh
The Code Book
Tim
25-5-2001
Kaufman At Home in the Universe
Wim
15-8-2001
Maddox Wat we nog niet weten
Wim
22-9-2001
Horgan
The End of ScienceTim
15-2-2001

leners
naam
Tim
Wim
Gijs
telnr
040-8345345
020-6123123
020-6456456
oplossing: 2 tabellen


schrift met uitgeleende boeken
adresboekje (of GSM telefoon): naam + telnr
39
Normalisatie:
1NF (first normal form)

Definitie 1NF:
Een tabel is in 1NF als voor elke waarde
van die tabel elke rij precies 1 waarde
voor elke attribuut heeft
voorbeeld:
in de tabel leners heeft
elke rij 1 naam en 1 telnr
40
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
41
Normalisatie: 2NF
PK
leverbare artikelen
LNO
ANO
L1
A1
L1
A2
L1
A3
L1
A4
L1
A5
L1
A6
L2
A1
L2
A2
L3
A2
L4
A2
L4
A4
L4
A5
HOEV
STATUS
300
20
200
20
400
20
200
20
100
20
100
20
300
10
400
10
200
10
200
20
300
20
400
20
PLAATS
Londen
Londen
Londen
Londen
Londen
Londen
Parijs
Parijs
Parijs
Londen
Londen
Londen
42
Normalisatie: 2NF
PK
PK
leverbare artikelen
LNO
ANO
L1
A1
L1
A2
L1
A3
L1
A4
L1
A5
L1
A6
L2
A1
L2
A2
L3
A2
L4
A2
L4
A4
L4
A5
leveranciers
LNO
STATUS
L1
20
L2
10
L3
10
L4
20
HOEV
300
200
400
200
100
100
300
400
200
200
300
400
PLAATS
Londen
Parijs
Parijs
Londen
43
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 niettransitief afhankelijk is van de primaire
sleutel
44
Normalisatie: 3NF
PK
leveranciers
LNO
STATUS
L1
20
L2
10
L3
10
L4
20
PLAATS
Londen
Parijs
Parijs
Londen
2NF
leveranciers
LNO
PLAATS
L1
Londen
L2
Parijs
L3
Parijs
L4
Londen
plaatsen
PLAATS
Londen
Parijs
STATUS
20
10
3NF
45
Normalisatie: BCNF
(Boyce/Codd Normal Form)

(informele) definitie:
een tabel is in BCNF als de enige
determinanten kandidaat-sleutels zijn
(dwz alle afhankelijkheidspijlen
beginnen bij kandidaat-sleutels)
46
Normalisatie: BCNF
(Boyce/Codd Normal Form)
leverbare artikelen
LNO
LNAAM
L1
Smith
L1
Smith
L1
Smith
L1
Smith
L1
Smith
L1
Smith
L2
Jones
L2
Jones
L3
Blake
L4
Clark
L4
Clark
L4
Clark
ANO
A1
A2
A3
A4
A5
A6
A1
A2
A2
A2
A4
A5
HOEV
300
200
400
200
100
100
300
400
200
200
300
400
47
Normalisatie: BCNF
(Boyce/Codd Normal Form)
leverbare artikelen
LNO
ANO
L1
A1
L1
A2
L1
A3
L1
A4
L1
A5
L1
A6
L2
A1
L2
A2
L3
A2
L4
A2
L4
A4
L4
A5
HOEV
300
200
400
200
100
100
300
400
200
200
300
400
leveranciers
LNO
LNAAM
L1
Smith
L2
Jones
L3
Blake
L4
Clark
TELNR
010-1231233
010-2342345
010-3453456
010-4564567
48
Normalisatie:
conclusie




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
49
Opdracht (5 minuten)

Breng in BCNF:
cijfers
SNR
1
2
3
1
2
3
3
SNAAM
Jansen
Smit
Pietersen
Jansen
Smit
Pietersen
Pietersen
VAK
CIJFER
databases
cryptografie
Java
Java
databases
cryptografie
C
7
8
9
6
7
8
9
DOCENT D_TELNR
Gerritsen
4111
de Vlas
4112
Meima
4113
Meima
4113
Gerritsen
4111
de Vlas
4112
Meima
4113
50
Opdracht

Afhankelijkheden:
cijfers
SNR
1
2
3
1
2
3
3
SNAAM
Jansen
Smit
Pietersen
Jansen
Smit
Pietersen
Pietersen
VAK
CIJFER
databases
cryptografie
Java
Java
databases
cryptografie
C
7
8
9
6
7
8
9
DOCENT D_TELNR
Gerritsen
4111
de Vlas
4112
Meima
4113
Meima
4113
Gerritsen
4111
de Vlas
4112
Meima
4113
51
Opdracht
oplossing (BCNF)

aannames:


cijfers
SNR
1
2
3
1
2
3
3
docentnaam is uniek, vaknaam is uniek
1 vak wordt door 1 docent gegeven
VAK
CIJFER
databases
cryptografie
Java
Java
databases
cryptografie
C
7
8
9
6
7
8
9
studenten
SNR
1
2
3
SNAAM
Jansen
Smit
Pietersen
docenten
DOCENT D_TELNR
Gerritsen
4111
de Vlas
4112
Meima
4113
vakken
VAK
DOCENT
databases Gerritsen
cryptografiede Vlas
Java
Meima
C
Meima
52
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)
53