DATABANKEN I
Academiejaar 2016 - 2017
Jakob Lierman
HoGent
,Jakob Lierman Databanken I
Inhoud
CONCEPTUEEL MODEL ...................................................................................................................................... 5
WAT IS EEN DATABASE? ............................................................................................................................................. 5
BOUWEN VAN EEN DATABASE ...................................................................................................................................... 5
CONCEPTUEEL MODEL ................................................................................................................................................ 5
ENTITY RELATIONSHIP MODEL ..................................................................................................................................... 6
Entiteittype....................................................................................................................................................... 6
Attribuuttype ................................................................................................................................................... 6
Relatietype ....................................................................................................................................................... 6
ERD (= ENTITY RELATIONSHIP DIAGRAM)...................................................................................................................... 7
CONCEPTUEEL MODEL – ZWAKKE ENTITEITEN .................................................................................................. 8
ZWAK ENTITEITTYPE ................................................................................................................................................... 8
RELATIE-ATTRIBUTEN ................................................................................................................................................. 8
HISTORIEK................................................................................................................................................................ 9
PROBLEMEN BIJ GEBRUIK ENTITY RELATIONSHIPMODEL ..................................................................................................... 9
CONCEPTUEEL MODEL – ENHANCED ENTITY RELATIONSHIP MODEL .............................................................. 11
STRUCTUURBEPERKING ERM .................................................................................................................................... 11
UITBREIDING ERM MET SPECIALISATIE / GENERALISATIE ................................................................................................. 11
Grafische voorstelling van een specialisatie .................................................................................................. 11
UITBREIDING ERM MET AGGREGATIE / COMPOSITIE ...................................................................................................... 12
Grafische voorstelling aggregatie .................................................................................................................. 12
Grafische voorstelling compositie .................................................................................................................. 12
EERM: VERBANDEN TUSSEN ENTITEITTYPEN ................................................................................................................. 12
LOGISCH MODEL ............................................................................................................................................. 13
RELATIONEEL MODEL ............................................................................................................................................... 13
Tupel .............................................................................................................................................................. 13
Attribuut......................................................................................................................................................... 13
Domein ........................................................................................................................................................... 13
Attribuuttype ................................................................................................................................................. 13
Relatie ............................................................................................................................................................ 13
ER-MODEL RELATIONEEL MODEL ........................................................................................................................... 14
REGELS .................................................................................................................................................................. 14
Elk tupel is uniek ............................................................................................................................................ 14
Kandidaatsleutel .......................................................................................................................................................... 14
Primaire sleutel ............................................................................................................................................................ 14
Alternatieve sleutel...................................................................................................................................................... 14
Elk attribuut is éénwaardig en atomair ......................................................................................................... 14
Verbanden tussen relaties worden gelegd aan de hand van vreemde sleutels ............................................ 14
Vreemde sleutel ........................................................................................................................................................... 14
MAPPING .............................................................................................................................................................. 14
Stappenplan ................................................................................................................................................... 14
Mapping van één op veel relaties................................................................................................................................ 15
Mapping van één op veel recursieve relaties .............................................................................................................. 15
Mapping van één op één relaties ................................................................................................................................ 16
Mapping van één op één unaire (recursieve) relaties ................................................................................................. 16
Mapping van veel op veel relaties ............................................................................................................................... 17
Mapping van zwakke entiteittypen ............................................................................................................................. 17
Mapping van meerwaardige attributen ...................................................................................................................... 18
Keuze van de primaire sleutel ..................................................................................................................................... 18
Mapping van specialisatie ............................................................................................................................. 19
2
, Jakob Lierman Databanken I
Mapping Mandatory, And ........................................................................................................................................... 19
Mapping Optional, And................................................................................................................................................ 20
Mapping Mandatory, Or .............................................................................................................................................. 20
Mapping Optional, Or .................................................................................................................................................. 21
Structuurbeperkingen .................................................................................................................................... 21
NORMALISATIE ............................................................................................................................................... 22
NORMALISATIE ....................................................................................................................................................... 22
Doel normalisatie ........................................................................................................................................... 22
Anomalieën .................................................................................................................................................... 23
Functionele onafhankelijkheid ....................................................................................................................... 23
Partiele afhankelijkheid ............................................................................................................................................... 23
Volledige functionele afhankelijkheid ......................................................................................................................... 23
Transitieve functionele afhankelijkheid ...................................................................................................................... 23
Identificeren van functionele afhankelijkheden.......................................................................................................... 24
Normalisatieproces ........................................................................................................................................ 24
Voorbereiding: verzamelen nodige gegevens door middel van gesprekken en documentatie ................................. 24
Stel R0 op en bepaal de functionele afhankelijkheden ............................................................................................... 24
Eerste normaalvorm .................................................................................................................................................... 24
Tweede normaalvorm.................................................................................................................................................. 25
Derde normaalvorm .................................................................................................................................................... 25
Het relationele model .................................................................................................................................... 26
VALIDEREN VAN RELATIES DOOR NORMALISATIE ............................................................................................................ 26
DATABANKEN GEKADERD ............................................................................................................................... 27
DEFINITIES ............................................................................................................................................................. 27
GEGEVENSMANAGEMENT ......................................................................................................................................... 27
Gegevensmanagement via bestanden .......................................................................................................... 27
Gegevensmanagement via een database ..................................................................................................... 27
Onderdelen .................................................................................................................................................................. 27
Modellen ...................................................................................................................................................................... 27
Rollen in een databasesysteem ................................................................................................................................... 28
Voordelen tegenover werken met bestanden ............................................................................................................ 28
Nadelen ........................................................................................................................................................................ 28
Voorwaarden voor een goede database ..................................................................................................................... 28
ARCHITECTUUR ....................................................................................................................................................... 28
SQL ................................................................................................................................................................. 30
SQL – STANDAARDEN EN DIALECTEN ........................................................................................................................... 30
SQL – OVERZICHT ................................................................................................................................................... 30
Data Definition Language (DDL) .................................................................................................................... 30
Data Manipulation Language (DML) ............................................................................................................. 30
Data Control Language (DCL) ........................................................................................................................ 30
DML – RAADPLEGEN VAN GEGEVENS .......................................................................................................................... 30
SELECT ............................................................................................................................................................ 30
FROM ............................................................................................................................................................. 30
SELECT … WHERE ........................................................................................................................................... 30
Vergelijkingsoperatoren .............................................................................................................................................. 30
Wildcards ..................................................................................................................................................................... 30
Logische operatoren .................................................................................................................................................... 31
Waarden in een interval .............................................................................................................................................. 31
Lijst van waarden ......................................................................................................................................................... 31
Testen op onbekende waarden (niet ingevulde velden) ............................................................................................. 32
SELECT + fomatteren resultaten .................................................................................................................... 32
SELECT … ORDER BY ..................................................................................................................................................... 32
SELECT DISTINCT/ALL ................................................................................................................................................... 33
3
Academiejaar 2016 - 2017
Jakob Lierman
HoGent
,Jakob Lierman Databanken I
Inhoud
CONCEPTUEEL MODEL ...................................................................................................................................... 5
WAT IS EEN DATABASE? ............................................................................................................................................. 5
BOUWEN VAN EEN DATABASE ...................................................................................................................................... 5
CONCEPTUEEL MODEL ................................................................................................................................................ 5
ENTITY RELATIONSHIP MODEL ..................................................................................................................................... 6
Entiteittype....................................................................................................................................................... 6
Attribuuttype ................................................................................................................................................... 6
Relatietype ....................................................................................................................................................... 6
ERD (= ENTITY RELATIONSHIP DIAGRAM)...................................................................................................................... 7
CONCEPTUEEL MODEL – ZWAKKE ENTITEITEN .................................................................................................. 8
ZWAK ENTITEITTYPE ................................................................................................................................................... 8
RELATIE-ATTRIBUTEN ................................................................................................................................................. 8
HISTORIEK................................................................................................................................................................ 9
PROBLEMEN BIJ GEBRUIK ENTITY RELATIONSHIPMODEL ..................................................................................................... 9
CONCEPTUEEL MODEL – ENHANCED ENTITY RELATIONSHIP MODEL .............................................................. 11
STRUCTUURBEPERKING ERM .................................................................................................................................... 11
UITBREIDING ERM MET SPECIALISATIE / GENERALISATIE ................................................................................................. 11
Grafische voorstelling van een specialisatie .................................................................................................. 11
UITBREIDING ERM MET AGGREGATIE / COMPOSITIE ...................................................................................................... 12
Grafische voorstelling aggregatie .................................................................................................................. 12
Grafische voorstelling compositie .................................................................................................................. 12
EERM: VERBANDEN TUSSEN ENTITEITTYPEN ................................................................................................................. 12
LOGISCH MODEL ............................................................................................................................................. 13
RELATIONEEL MODEL ............................................................................................................................................... 13
Tupel .............................................................................................................................................................. 13
Attribuut......................................................................................................................................................... 13
Domein ........................................................................................................................................................... 13
Attribuuttype ................................................................................................................................................. 13
Relatie ............................................................................................................................................................ 13
ER-MODEL RELATIONEEL MODEL ........................................................................................................................... 14
REGELS .................................................................................................................................................................. 14
Elk tupel is uniek ............................................................................................................................................ 14
Kandidaatsleutel .......................................................................................................................................................... 14
Primaire sleutel ............................................................................................................................................................ 14
Alternatieve sleutel...................................................................................................................................................... 14
Elk attribuut is éénwaardig en atomair ......................................................................................................... 14
Verbanden tussen relaties worden gelegd aan de hand van vreemde sleutels ............................................ 14
Vreemde sleutel ........................................................................................................................................................... 14
MAPPING .............................................................................................................................................................. 14
Stappenplan ................................................................................................................................................... 14
Mapping van één op veel relaties................................................................................................................................ 15
Mapping van één op veel recursieve relaties .............................................................................................................. 15
Mapping van één op één relaties ................................................................................................................................ 16
Mapping van één op één unaire (recursieve) relaties ................................................................................................. 16
Mapping van veel op veel relaties ............................................................................................................................... 17
Mapping van zwakke entiteittypen ............................................................................................................................. 17
Mapping van meerwaardige attributen ...................................................................................................................... 18
Keuze van de primaire sleutel ..................................................................................................................................... 18
Mapping van specialisatie ............................................................................................................................. 19
2
, Jakob Lierman Databanken I
Mapping Mandatory, And ........................................................................................................................................... 19
Mapping Optional, And................................................................................................................................................ 20
Mapping Mandatory, Or .............................................................................................................................................. 20
Mapping Optional, Or .................................................................................................................................................. 21
Structuurbeperkingen .................................................................................................................................... 21
NORMALISATIE ............................................................................................................................................... 22
NORMALISATIE ....................................................................................................................................................... 22
Doel normalisatie ........................................................................................................................................... 22
Anomalieën .................................................................................................................................................... 23
Functionele onafhankelijkheid ....................................................................................................................... 23
Partiele afhankelijkheid ............................................................................................................................................... 23
Volledige functionele afhankelijkheid ......................................................................................................................... 23
Transitieve functionele afhankelijkheid ...................................................................................................................... 23
Identificeren van functionele afhankelijkheden.......................................................................................................... 24
Normalisatieproces ........................................................................................................................................ 24
Voorbereiding: verzamelen nodige gegevens door middel van gesprekken en documentatie ................................. 24
Stel R0 op en bepaal de functionele afhankelijkheden ............................................................................................... 24
Eerste normaalvorm .................................................................................................................................................... 24
Tweede normaalvorm.................................................................................................................................................. 25
Derde normaalvorm .................................................................................................................................................... 25
Het relationele model .................................................................................................................................... 26
VALIDEREN VAN RELATIES DOOR NORMALISATIE ............................................................................................................ 26
DATABANKEN GEKADERD ............................................................................................................................... 27
DEFINITIES ............................................................................................................................................................. 27
GEGEVENSMANAGEMENT ......................................................................................................................................... 27
Gegevensmanagement via bestanden .......................................................................................................... 27
Gegevensmanagement via een database ..................................................................................................... 27
Onderdelen .................................................................................................................................................................. 27
Modellen ...................................................................................................................................................................... 27
Rollen in een databasesysteem ................................................................................................................................... 28
Voordelen tegenover werken met bestanden ............................................................................................................ 28
Nadelen ........................................................................................................................................................................ 28
Voorwaarden voor een goede database ..................................................................................................................... 28
ARCHITECTUUR ....................................................................................................................................................... 28
SQL ................................................................................................................................................................. 30
SQL – STANDAARDEN EN DIALECTEN ........................................................................................................................... 30
SQL – OVERZICHT ................................................................................................................................................... 30
Data Definition Language (DDL) .................................................................................................................... 30
Data Manipulation Language (DML) ............................................................................................................. 30
Data Control Language (DCL) ........................................................................................................................ 30
DML – RAADPLEGEN VAN GEGEVENS .......................................................................................................................... 30
SELECT ............................................................................................................................................................ 30
FROM ............................................................................................................................................................. 30
SELECT … WHERE ........................................................................................................................................... 30
Vergelijkingsoperatoren .............................................................................................................................................. 30
Wildcards ..................................................................................................................................................................... 30
Logische operatoren .................................................................................................................................................... 31
Waarden in een interval .............................................................................................................................................. 31
Lijst van waarden ......................................................................................................................................................... 31
Testen op onbekende waarden (niet ingevulde velden) ............................................................................................. 32
SELECT + fomatteren resultaten .................................................................................................................... 32
SELECT … ORDER BY ..................................................................................................................................................... 32
SELECT DISTINCT/ALL ................................................................................................................................................... 33
3
