Database 2 Guide
Styn van de Haterd
2017-13-12
,Contents
1 Introductie 1
2 Database Files 2
2.1 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.2 information schema . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.3 performance schema . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.4 sys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.5 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
3 Storage Engines 4
3.1 Verschillen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
3.2 Instellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4 Subqueries 5
4.1 Correlated Subquery . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.2 Embedded Subquery . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.3 Subquery Regels . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Insert Queries 7
5.1 Auto-Increment veld . . . . . . . . . . . . . . . . . . . . . . . . . 7
5.2 Insert Queries vanuit Select . . . . . . . . . . . . . . . . . . . . . 7
5.2.1 Insert Queries vanuit Select met Subquery . . . . . . . . . 8
6 Update Queries 8
6.1 Update Queries met Subquery . . . . . . . . . . . . . . . . . . . 8
7 Delete Queries 9
7.1 Gevaren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
8 Indexes 10
8.1 Voordelen/Nadelen Indexes . . . . . . . . . . . . . . . . . . . . . 10
8.2 Composite Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 10
9 Create/Alter/Drop Index Queries 11
10 Create/Alter/Drop Table Queries 12
11 Universum van Discussie 13
11.1 Voorbeelden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
11.2 Criteria van Entiteittypen . . . . . . . . . . . . . . . . . . . . . . 15
11.2.1 Het bepalen van een Entiteitstypen . . . . . . . . . . . . . 15
11.2.2 Eigenschappen . . . . . . . . . . . . . . . . . . . . . . . . 16
11.2.3 Rollen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
11.2.4 Regels en Eisen . . . . . . . . . . . . . . . . . . . . . . . . 17
11.2.5 Genericiteit . . . . . . . . . . . . . . . . . . . . . . . . . . 17
, 11.2.6 Entiteiten Volgen . . . . . . . . . . . . . . . . . . . . . . . 18
11.2.7 Bedrijfsproces Ondersteunen . . . . . . . . . . . . . . . . 19
11.3 Criteria van Attribuuttypen . . . . . . . . . . . . . . . . . . . . . 20
11.3.1 Een feit, twee attribuuttype . . . . . . . . . . . . . . . . . 20
11.3.2 Datatype kiezen . . . . . . . . . . . . . . . . . . . . . . . 20
11.4 Criteria van Relaties . . . . . . . . . . . . . . . . . . . . . . . . . 21
11.4.1 Genericiteitswijziging . . . . . . . . . . . . . . . . . . . . 21
11.4.2 Beinvloeding Entiteittypen . . . . . . . . . . . . . . . . . 21
11.4.3 Veel Relaties . . . . . . . . . . . . . . . . . . . . . . . . . 22
11.4.4 Sterk/Zwakke Relaties . . . . . . . . . . . . . . . . . . . . 22
11.4.5 Voorbeeld . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
11.5 Stappenplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.1 Stap 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.2 Stap 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.3 Stap 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.4 Stap 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.5 Stap 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.6 Stap 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.7 Stap 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.8 Stap 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.9 Stap 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.10 Stap 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.11 Stap 11 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.12 Stap 12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.13 Stap 13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
11.5.14 Stap 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
12 Normaliseren 28
12.1 Normaliseren Stappenplan . . . . . . . . . . . . . . . . . . . . . . 29
12.1.1 Stap 1 : 1e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.2 Stap 2 : 2e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.3 Stap 3 : 3e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.4 Nadelen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
13 Excel 30
13.1 Omzetten naar EER . . . . . . . . . . . . . . . . . . . . . . . . . 30
14 Surrogaat Sleutels 31
14.1 Voordelen/Nadelen . . . . . . . . . . . . . . . . . . . . . . . . . . 31
15 Unique Indexes 32
15.1 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
16 Fysiek Ontwerp 33
16.1 Genereren via MySQL Workbench . . . . . . . . . . . . . . . . . 33
16.2 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
,17 Views 34
17.1 Nut van Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
17.2 Views Updaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17.2.1 Voorwaarden Update . . . . . . . . . . . . . . . . . . . . . 36
17.2.2 Voorwaarden Insert . . . . . . . . . . . . . . . . . . . . . 36
17.3 Check Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.4 Order By . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
18 Transactions, COMMIT, ROLLBACK 38
19 ACID Compliant 39
20 ON UPDATE/ON DELETE 39
20.1 Opties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
21 USER MANAGEMENT en PRIVILEGES 41
21.1 Limit to Hosts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
21.2 Account Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
21.3 Administrative Roles . . . . . . . . . . . . . . . . . . . . . . . . . 42
21.4 Schema privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
21.5 Account Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
21.6 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
,1 Introductie
Ik heb deze studiegids geschreven voor het leren van het vak Databases 2. Hi-
ervoor heb ik alle relevante theorie uit de presentaties bij elkaar gevoegd zodat
het terug vinden en het leren van die theorie makkelijker verloop dan elke keer
de powerpoints openen.
1
, 2 Database Files
*.frm =>Table format (hoe de tabel eruit ziet).
*.ibd =>De inhoud van de tabel en zijn indexen.
Hoe het in de files staat is echter helemaal afhankelijk van de storage engine.
2.1 Metadata
Informatie over de database. (in tegenstelling tot de inhoud van de database)
Zoals:
• *.frm files (table format, hoe een tabel eruit ziet)
• information schema
• performance schema
• sys schema
information schema en performance schema zijn default verborgen
2.2 information schema
Verzameling tabellen die opgeslagen is in het geheugen. (via de MEMORY
storage engine)
Worden gevuld bij het starten van de MySQL Server.
Bevat informatie over de verschillende databases op die server.
Is direct aan te roepen. Bijvoorbeeld:
SELECT * FROM INFORMATION_SCHEMA.FILES;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS;
Of met het SHOW commando. Bijvoorbeeld:
SHOW STATUS;
SHOW INDEX FROM fun4all.student;
SHOW PRIVILEGES;
2
Styn van de Haterd
2017-13-12
,Contents
1 Introductie 1
2 Database Files 2
2.1 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.2 information schema . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.3 performance schema . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.4 sys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.5 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
3 Storage Engines 4
3.1 Verschillen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
3.2 Instellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4 Subqueries 5
4.1 Correlated Subquery . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.2 Embedded Subquery . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.3 Subquery Regels . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Insert Queries 7
5.1 Auto-Increment veld . . . . . . . . . . . . . . . . . . . . . . . . . 7
5.2 Insert Queries vanuit Select . . . . . . . . . . . . . . . . . . . . . 7
5.2.1 Insert Queries vanuit Select met Subquery . . . . . . . . . 8
6 Update Queries 8
6.1 Update Queries met Subquery . . . . . . . . . . . . . . . . . . . 8
7 Delete Queries 9
7.1 Gevaren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
8 Indexes 10
8.1 Voordelen/Nadelen Indexes . . . . . . . . . . . . . . . . . . . . . 10
8.2 Composite Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 10
9 Create/Alter/Drop Index Queries 11
10 Create/Alter/Drop Table Queries 12
11 Universum van Discussie 13
11.1 Voorbeelden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
11.2 Criteria van Entiteittypen . . . . . . . . . . . . . . . . . . . . . . 15
11.2.1 Het bepalen van een Entiteitstypen . . . . . . . . . . . . . 15
11.2.2 Eigenschappen . . . . . . . . . . . . . . . . . . . . . . . . 16
11.2.3 Rollen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
11.2.4 Regels en Eisen . . . . . . . . . . . . . . . . . . . . . . . . 17
11.2.5 Genericiteit . . . . . . . . . . . . . . . . . . . . . . . . . . 17
, 11.2.6 Entiteiten Volgen . . . . . . . . . . . . . . . . . . . . . . . 18
11.2.7 Bedrijfsproces Ondersteunen . . . . . . . . . . . . . . . . 19
11.3 Criteria van Attribuuttypen . . . . . . . . . . . . . . . . . . . . . 20
11.3.1 Een feit, twee attribuuttype . . . . . . . . . . . . . . . . . 20
11.3.2 Datatype kiezen . . . . . . . . . . . . . . . . . . . . . . . 20
11.4 Criteria van Relaties . . . . . . . . . . . . . . . . . . . . . . . . . 21
11.4.1 Genericiteitswijziging . . . . . . . . . . . . . . . . . . . . 21
11.4.2 Beinvloeding Entiteittypen . . . . . . . . . . . . . . . . . 21
11.4.3 Veel Relaties . . . . . . . . . . . . . . . . . . . . . . . . . 22
11.4.4 Sterk/Zwakke Relaties . . . . . . . . . . . . . . . . . . . . 22
11.4.5 Voorbeeld . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
11.5 Stappenplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.1 Stap 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.2 Stap 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.3 Stap 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.4 Stap 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
11.5.5 Stap 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.6 Stap 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.7 Stap 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.8 Stap 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.5.9 Stap 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.10 Stap 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.11 Stap 11 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.12 Stap 12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
11.5.13 Stap 13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
11.5.14 Stap 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
12 Normaliseren 28
12.1 Normaliseren Stappenplan . . . . . . . . . . . . . . . . . . . . . . 29
12.1.1 Stap 1 : 1e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.2 Stap 2 : 2e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.3 Stap 3 : 3e Normaalvorm . . . . . . . . . . . . . . . . . . 29
12.1.4 Nadelen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
13 Excel 30
13.1 Omzetten naar EER . . . . . . . . . . . . . . . . . . . . . . . . . 30
14 Surrogaat Sleutels 31
14.1 Voordelen/Nadelen . . . . . . . . . . . . . . . . . . . . . . . . . . 31
15 Unique Indexes 32
15.1 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
16 Fysiek Ontwerp 33
16.1 Genereren via MySQL Workbench . . . . . . . . . . . . . . . . . 33
16.2 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
,17 Views 34
17.1 Nut van Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
17.2 Views Updaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17.2.1 Voorwaarden Update . . . . . . . . . . . . . . . . . . . . . 36
17.2.2 Voorwaarden Insert . . . . . . . . . . . . . . . . . . . . . 36
17.3 Check Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.4 Order By . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
18 Transactions, COMMIT, ROLLBACK 38
19 ACID Compliant 39
20 ON UPDATE/ON DELETE 39
20.1 Opties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
21 USER MANAGEMENT en PRIVILEGES 41
21.1 Limit to Hosts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
21.2 Account Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
21.3 Administrative Roles . . . . . . . . . . . . . . . . . . . . . . . . . 42
21.4 Schema privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
21.5 Account Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
21.6 Vraag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
,1 Introductie
Ik heb deze studiegids geschreven voor het leren van het vak Databases 2. Hi-
ervoor heb ik alle relevante theorie uit de presentaties bij elkaar gevoegd zodat
het terug vinden en het leren van die theorie makkelijker verloop dan elke keer
de powerpoints openen.
1
, 2 Database Files
*.frm =>Table format (hoe de tabel eruit ziet).
*.ibd =>De inhoud van de tabel en zijn indexen.
Hoe het in de files staat is echter helemaal afhankelijk van de storage engine.
2.1 Metadata
Informatie over de database. (in tegenstelling tot de inhoud van de database)
Zoals:
• *.frm files (table format, hoe een tabel eruit ziet)
• information schema
• performance schema
• sys schema
information schema en performance schema zijn default verborgen
2.2 information schema
Verzameling tabellen die opgeslagen is in het geheugen. (via de MEMORY
storage engine)
Worden gevuld bij het starten van de MySQL Server.
Bevat informatie over de verschillende databases op die server.
Is direct aan te roepen. Bijvoorbeeld:
SELECT * FROM INFORMATION_SCHEMA.FILES;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS;
Of met het SHOW commando. Bijvoorbeeld:
SHOW STATUS;
SHOW INDEX FROM fun4all.student;
SHOW PRIVILEGES;
2
