DATABANKEN SAMENVATTING
INHOUD
examen ................................................................................................................................................................ 3
W1 ........................................................................................................................................................................... 4
Voordelen van databasebenadering ................................................................................................................... 4
erd ....................................................................................................................................................................... 4
Entiteitstype .................................................................................................................................................... 5
relatietype ....................................................................................................................................................... 5
DATABASE ........................................................................................................................................................... 5
DBMS: .................................................................................................................................................................. 5
gebruikersprofielen ............................................................................................................................................. 5
relatiemodel ........................................................................................................................................................ 7
relationele database ....................................................................................................................................... 7
Sleutelattributen ............................................................................................................................................. 7
Integriteitsregels ............................................................................................................................................. 7
SQL ...................................................................................................................................................................... 8
W2 ........................................................................................................................................................................... 8
From .................................................................................................................................................................... 8
Select ................................................................................................................................................................... 9
Where.................................................................................................................................................................. 9
order by ............................................................................................................................................................. 10
FEtch .................................................................................................................................................................. 10
offset ................................................................................................................................................................. 11
W3 ......................................................................................................................................................................... 11
JOINS ................................................................................................................................................................. 11
Non-Equi join................................................................................................................................................. 12
Numerieke en datum functies .......................................................................................................................... 12
TO_CHAR ....................................................................................................................................................... 12
TO_DATE ....................................................................................................................................................... 12
Datum functies .............................................................................................................................................. 13
W4 ......................................................................................................................................................................... 13
Analytische functies .......................................................................................................................................... 13
Group by............................................................................................................................................................ 14
having ................................................................................................................................................................ 14
joins ................................................................................................................................................................... 14
1
Quinten Maas
, Gewone (inner) join ...................................................................................................................................... 14
Outer join ...................................................................................................................................................... 14
W5 ......................................................................................................................................................................... 16
Set operatoren .................................................................................................................................................. 16
Algemeen: ..................................................................................................................................................... 16
union ............................................................................................................................................................. 16
intersect ........................................................................................................................................................ 16
Except ............................................................................................................................................................ 16
tekstfuncties ...................................................................................................................................................... 17
Conditionele functies ........................................................................................................................................ 18
W1 ......................................................................................................................................................................... 19
tabel .................................................................................................................................................................. 19
tabelnaam ..................................................................................................................................................... 19
atribuutnaam ................................................................................................................................................ 19
gegevenstype ................................................................................................................................................ 19
defaultwaarde ............................................................................................................................................... 20
constrains ...................................................................................................................................................... 20
W2 ......................................................................................................................................................................... 23
DML ................................................................................................................................................................... 23
insert ............................................................................................................................................................. 24
update ........................................................................................................................................................... 24
delete ............................................................................................................................................................ 25
DML instructies en constraint ........................................................................................................................... 25
DDL .................................................................................................................................................................... 25
truncate ......................................................................................................................................................... 25
Drop table ..................................................................................................................................................... 26
ALTER table ................................................................................................................................................... 26
sequence ....................................................................................................................................................... 28
P4W3 ..................................................................................................................................................................... 29
subquerys .......................................................................................................................................................... 29
views ................................................................................................................................................................. 30
beperkingen .................................................................................................................................................. 31
P4W4 ..................................................................................................................................................................... 31
index .................................................................................................................................................................. 31
B-tree ............................................................................................................................................................ 31
Correlated Subqueries ...................................................................................................................................... 31
exists ............................................................................................................................................................. 32
2
Quinten Maas
, insert ............................................................................................................................................................. 32
update ........................................................................................................................................................... 32
delete ............................................................................................................................................................ 32
P4W5 ..................................................................................................................................................................... 33
Transacties ........................................................................................................................................................ 33
commit .......................................................................................................................................................... 33
Rollback ......................................................................................................................................................... 33
ACID ............................................................................................................................................................... 34
Locking .............................................................................................................................................................. 34
Security ............................................................................................................................................................. 35
privileges ....................................................................................................................................................... 37
inherit EN NOINHERIT ................................................................................................................................... 37
Set role .......................................................................................................................................................... 37
EXAMEN
Op public kan je rechtermuisknop > Diagrams > show visualition
Kijk ook naar de naslag dat is een uitgebreide ppt vd online les maar is wel te kennen
Je moet een ERD kunne lezen en NIET zelf kunne maken!!!
- Wel begrijpen
Je krijgt dag voor het examen het ERD om het al door te nemen en alles in orde te maken → geen tijd voor opt
examen
Order by is heel belangrijk voor punten te pakken opt examen
Titel van de kolom is ook een puntenpakker
Zorg da ge uw database runt via rechtermuisknop en dan run
Meerkeuzevragen
- 1 of 2 of 3 antwoorden
- Per fout antwoord minpunten
- Verschillende databanken
- Meerendeel vragen over querys
- Weinig echt echt theorie vragen
Open vragen
- Databank tss 19-20u de avond ervoor
- Kan ook screen nemen
10. beveiliging
3
Quinten Maas
, Nooit rechten direct aan een user geven, Op theorie! (meerkeuze)
Tips:
- Tabellen creeren W6
- Wijzigen tabellen die ene slide/overzichtstabel W7/P4W2
- Subqueries
- Views binnen de view aanpassen
- Indexen alleen op theorie
- Corr subqueries (verschil: corr subq geef lijn per lijn info door, checkt telkens lijn per lijn → ja dan
verschijnt het in de querie, nee dan niet)
- 10 transacties en locking enkel op theorie
- Beveiliging enkel op praktijk
W1
VOORDELEN VAN DATABASEBENADERING
Shared data:
- Alle applicaties kunnen de gegevens uit de databank gebruiken
Alle gegevens zijn gecentraliseerd en gerelateerd
Redundantie: verschillende plaatsen data bijhouden
Geringe redundantie: zorgen dat je data niet dubbel opslaat
- Sneller en makkelijker
- Minder kans op fouten
Data onafhankelijkheid:
- Gegevens worden in de databank beschreven en niet in de applicatie
o Wijziging vd gegevens vraagt nu geen wijziging in de applicatie
Integriteit: zorgen dat je data juist is en juist blijft
- Beperkte redundantie
o Weinig inconsistenties
- Constrains (beperkingen)
o Automatische controles
Veiligheidsbewaking:
- Gegevens gecentraliseerd → makkelijk te beveiligen
Opleggen standaarden
- Gegevens centraal → makkelijk afspraken maken
ERD
4
Quinten Maas
INHOUD
examen ................................................................................................................................................................ 3
W1 ........................................................................................................................................................................... 4
Voordelen van databasebenadering ................................................................................................................... 4
erd ....................................................................................................................................................................... 4
Entiteitstype .................................................................................................................................................... 5
relatietype ....................................................................................................................................................... 5
DATABASE ........................................................................................................................................................... 5
DBMS: .................................................................................................................................................................. 5
gebruikersprofielen ............................................................................................................................................. 5
relatiemodel ........................................................................................................................................................ 7
relationele database ....................................................................................................................................... 7
Sleutelattributen ............................................................................................................................................. 7
Integriteitsregels ............................................................................................................................................. 7
SQL ...................................................................................................................................................................... 8
W2 ........................................................................................................................................................................... 8
From .................................................................................................................................................................... 8
Select ................................................................................................................................................................... 9
Where.................................................................................................................................................................. 9
order by ............................................................................................................................................................. 10
FEtch .................................................................................................................................................................. 10
offset ................................................................................................................................................................. 11
W3 ......................................................................................................................................................................... 11
JOINS ................................................................................................................................................................. 11
Non-Equi join................................................................................................................................................. 12
Numerieke en datum functies .......................................................................................................................... 12
TO_CHAR ....................................................................................................................................................... 12
TO_DATE ....................................................................................................................................................... 12
Datum functies .............................................................................................................................................. 13
W4 ......................................................................................................................................................................... 13
Analytische functies .......................................................................................................................................... 13
Group by............................................................................................................................................................ 14
having ................................................................................................................................................................ 14
joins ................................................................................................................................................................... 14
1
Quinten Maas
, Gewone (inner) join ...................................................................................................................................... 14
Outer join ...................................................................................................................................................... 14
W5 ......................................................................................................................................................................... 16
Set operatoren .................................................................................................................................................. 16
Algemeen: ..................................................................................................................................................... 16
union ............................................................................................................................................................. 16
intersect ........................................................................................................................................................ 16
Except ............................................................................................................................................................ 16
tekstfuncties ...................................................................................................................................................... 17
Conditionele functies ........................................................................................................................................ 18
W1 ......................................................................................................................................................................... 19
tabel .................................................................................................................................................................. 19
tabelnaam ..................................................................................................................................................... 19
atribuutnaam ................................................................................................................................................ 19
gegevenstype ................................................................................................................................................ 19
defaultwaarde ............................................................................................................................................... 20
constrains ...................................................................................................................................................... 20
W2 ......................................................................................................................................................................... 23
DML ................................................................................................................................................................... 23
insert ............................................................................................................................................................. 24
update ........................................................................................................................................................... 24
delete ............................................................................................................................................................ 25
DML instructies en constraint ........................................................................................................................... 25
DDL .................................................................................................................................................................... 25
truncate ......................................................................................................................................................... 25
Drop table ..................................................................................................................................................... 26
ALTER table ................................................................................................................................................... 26
sequence ....................................................................................................................................................... 28
P4W3 ..................................................................................................................................................................... 29
subquerys .......................................................................................................................................................... 29
views ................................................................................................................................................................. 30
beperkingen .................................................................................................................................................. 31
P4W4 ..................................................................................................................................................................... 31
index .................................................................................................................................................................. 31
B-tree ............................................................................................................................................................ 31
Correlated Subqueries ...................................................................................................................................... 31
exists ............................................................................................................................................................. 32
2
Quinten Maas
, insert ............................................................................................................................................................. 32
update ........................................................................................................................................................... 32
delete ............................................................................................................................................................ 32
P4W5 ..................................................................................................................................................................... 33
Transacties ........................................................................................................................................................ 33
commit .......................................................................................................................................................... 33
Rollback ......................................................................................................................................................... 33
ACID ............................................................................................................................................................... 34
Locking .............................................................................................................................................................. 34
Security ............................................................................................................................................................. 35
privileges ....................................................................................................................................................... 37
inherit EN NOINHERIT ................................................................................................................................... 37
Set role .......................................................................................................................................................... 37
EXAMEN
Op public kan je rechtermuisknop > Diagrams > show visualition
Kijk ook naar de naslag dat is een uitgebreide ppt vd online les maar is wel te kennen
Je moet een ERD kunne lezen en NIET zelf kunne maken!!!
- Wel begrijpen
Je krijgt dag voor het examen het ERD om het al door te nemen en alles in orde te maken → geen tijd voor opt
examen
Order by is heel belangrijk voor punten te pakken opt examen
Titel van de kolom is ook een puntenpakker
Zorg da ge uw database runt via rechtermuisknop en dan run
Meerkeuzevragen
- 1 of 2 of 3 antwoorden
- Per fout antwoord minpunten
- Verschillende databanken
- Meerendeel vragen over querys
- Weinig echt echt theorie vragen
Open vragen
- Databank tss 19-20u de avond ervoor
- Kan ook screen nemen
10. beveiliging
3
Quinten Maas
, Nooit rechten direct aan een user geven, Op theorie! (meerkeuze)
Tips:
- Tabellen creeren W6
- Wijzigen tabellen die ene slide/overzichtstabel W7/P4W2
- Subqueries
- Views binnen de view aanpassen
- Indexen alleen op theorie
- Corr subqueries (verschil: corr subq geef lijn per lijn info door, checkt telkens lijn per lijn → ja dan
verschijnt het in de querie, nee dan niet)
- 10 transacties en locking enkel op theorie
- Beveiliging enkel op praktijk
W1
VOORDELEN VAN DATABASEBENADERING
Shared data:
- Alle applicaties kunnen de gegevens uit de databank gebruiken
Alle gegevens zijn gecentraliseerd en gerelateerd
Redundantie: verschillende plaatsen data bijhouden
Geringe redundantie: zorgen dat je data niet dubbel opslaat
- Sneller en makkelijker
- Minder kans op fouten
Data onafhankelijkheid:
- Gegevens worden in de databank beschreven en niet in de applicatie
o Wijziging vd gegevens vraagt nu geen wijziging in de applicatie
Integriteit: zorgen dat je data juist is en juist blijft
- Beperkte redundantie
o Weinig inconsistenties
- Constrains (beperkingen)
o Automatische controles
Veiligheidsbewaking:
- Gegevens gecentraliseerd → makkelijk te beveiligen
Opleggen standaarden
- Gegevens centraal → makkelijk afspraken maken
ERD
4
Quinten Maas
