Samenvatting DBSQLF
Samenvatting op basis van de begrippenlijst DBSQLF
1.1. Basisbegrippen van databases
Toegang tot gegevens/authorisatie: Niet iedereen heeft gelijke rechten in de DB/geven van rechten, door beheerder geregeld. Welke gebruiker heeft toestemming voor welke acties
Mogelijke rechten voor de gebruiker zijn:
- lezen (select)
- wijzigen (update)
- verwijderen (delete)
(ook nog, maar niet genoemd in de woordenlijst: invoegen (insert))
Het beheer van de database en gebruik van de gegevens van de database geschiedt door middel van een databases managementsysteem (DBMS)
Vanuit de gewenste functionaliteit kunnen eisen gesteld worden waaraan een DB moet voldoen:
- eenduidige definitie: elke eigenschap van een object heeft maar één betekenis en is maar voor één interpretatie vatbaar.
- samenhang gegevens: regels van de relaties mogen niet overtreden worden.
De database wordt benaderd op drie niveau's die onafhankelijk van elkaar aangepast kunnen worden:
- intern niveau: fysieke opslag
- conceptueel niveau: beschrijving van de structuur van de DB (welke objecten, welke gegevens van de objecten, welke regels gelden er voor de gegevens en de relaties tussen de gegevens)
- extern niveau: wie of welke groep mag welke gegevens zien en hoe worden de gegevens gepresenteerd (applicaties en views op de DB)
index: techniek om snel de DB te kunnen doorzoeken door de fysieke opslag te indexeren.
fysieke gegevensonafhankelijkheid (of functionele gegevensonafhankelijkheid): onafhankelijkheid tussen intern niveau en coneptueel niveau. Als je de opslag/OS etc wijzigd verandert er niets aan de structuur van de DB
logische gegevensonafhankelijkheid: onafhankelijkheid tussen extern niveau en conceptueel niveau. Als je de attributen die worden opgeslagen van objecten aanpast, hoeven de views/applicaties niet worden aangepast. In de praktijk zul je echter wel minimaal één applicatie of view aanpassen, want je verandert de structuur van de DB omdat je in een applicatie/view die gegevens wil gebruiken.
objecten: dingen in de werkelijkheid die attributen hebben, ieder object is een tabel in de DB, één specifiek voorkomen van een object noemen we een record.
eigenschappen van een object: attributen, die gegevens die we van een object willen vastleggen.
navigeren: Een database doorzoeken om te kijken of records voldoen aan bepaalde voorwaarden. Dit verschilt per DB type, omdat de techniek waarmee relaties worden vastgelegd per DB-soort verschilt.
Tussen de tabellen kunnen verschillende relaties bestaan:
- 1-op-1 relatie: (g)een op (g)een relatie
- 1-op-N relatie: (g)een op veel relatie
- N-op-M relatie: veel op veel relatie
views van gegevens: selectie van de gevens in de DB op basis van gewenste functionaliteit en autorisatie.
2.0. Flat files (niet in begrippenlijst)
relaties: alleen geschikt voor één-op-één relaties tussen objecten. Als je relaties tussen objecten wil aangeven, moet je gegevens herhalen (redundantie) en dat wil je niet: kost veel ruimte en is foutgevoelig.
2.1. Hierarchische database
relaties: dit is een goede oplossing voor één-op-veel relaties. Alle records hebben maar één parent en om van child naar child te hoppen moet je via de parent. Dat wil zeggen, we kunnen de verbindingen tussen records niet zien in dit type DB.
Maar bij veel-op-veel relaties heb je ook hier redundantie van gegevens.
records: records zijn met elkaar verbonden dor links in een boomstructuur.
2.2. Netwerk database
relaties: Een child kan meerder parents hebben, dus veel-op-veel relatie kan ook worden weergegevens.
records: geen idee wat ik hierover kan vertellen.....
2.3. Relationele database
relaties: In een relationele Db gaat het juist om de relaties tussen objecten.
records: Per object (tabel) heb je records die met andere objecten relateerd zijn.
viewdefinitie: Je kunt een view op een DB definieeren, dan zie je alleen die attributen die nodig zijn voor een bepaald doel.(extern niveau)
tabeldefinitie: Bij de tabeldefinitie worden alle attributen die je van een object wil registreren betrokken. (Conceptueel niveau)
In dit soort databases gaat het om de relaties tussen de objecten.
De tabellen uit de vorige paragrafen kunnen in een model van een relationele database als volgt worden weergegeven:
I-Tracks module (code, naam) === "code" onderstreept ===
I-Tracks eisen (<code>, nummer, naam, percentage) === "<code>, nummer" onderstreept ===
I-Tracks docent (<code>, docent) === "<code>, docent" onderstreept ===
De onderstreepte (combinaties van) woorden geven aan welk onderdeel identificerend is voor de elementen van de tabel.
De woorden tussen de haakjes (< >) geven aan dat ze verwijzen naar een andere tabel, met name de tabel I-Tracks module.
3.1. Relationeel model
relatie (entiteit): beschrijving van een object in de werkelijkheid
attribuut: eigenschap die we van een relatie willen opslaan (naam en waarde)
attribuutnaam: naam/titel van een eigenschap van een relatie
attribuutwaarde: de waarde/inhoud die de eigenschap v.e. relatie aannneemt (van één instantie).
associatie (niet in termenlijst): relaties staan niet op zichzelf, maar zijn geassocieerd aan andere relaties. Een relatie kan ook een associatie hebben met zichzelf.
domein: Hiermee geven we aan welke waarden een attribuut mag bevatten. Bijvoorbeeld alle data tussen 1 jan 1970 en 1 jan 1990 als we het hebben over de geboortedatum van alle eerstejaars studenten.
kandidaatssleutel: de sleutel(s) die voldoen aan het principe dat ze kunnen zorgen voor de identificatie van tupels als uniek en aanwezig.
primaire sleutel: de sleutel die wordt gekozen uit de beschikbare kandidaatssleutels om tupels te identificeren. Kandidaatssleutels en dus ook primaire sleutels kunnen worden samengesteld uit meerdere attributen. Dan spreken we over samengestelde of gecombineerde sleutels.
Voor de keuze van de primaire sleutel kies je meestal de meest compacte.
vreemde sleutel: sleutel die de associatie tussen relaties aanduidt. Deze vreemde sleutel mag leeg zijn (NULL waarde), maar als ie gevuld is, moet dat zijn met een geldige waarde uit de associatie waar de vreemde sleutel naar verwijst. Deze foreign key hoeft niet dezelfde naam te hebben als de naam van de sleutel waar ie naar verwijst.
entiteitsintegriteit: iedere relatie moet een attribuut of combinatie van attributen hebben waarmee elk tupel als uniek en aanwezig te identificeren is. Dat attribuut is de primary key of primaire sleutel.
referentiele integriteit: relaties in een relationele DB staan niet op zichzelf, maar hebben associaties met andere relaties. Dat kan een 1-op-1, 1-op-veel of veel-op-veel relatie zijn. Deze associatie tussen relaties wordt aangegeven met een vreemde sleutel (foreign key). Deze vreemde sleutel is een verwijzing van de ene relatie naar de andere en wordt toegevoegd aan de relatie die de 1 in de 1-op-veel relatie voorsteld. De foreign key mag NULL zijn, maar als ie is ingevuld moet ie een geldige waarde uit de geassocieerde relatie bevatten, dat is referentiele integriteit.
Bij veel-op-veel relaties is het niet mogelijk direct een associatie tussen de beide relaties op te stellen, maar maak je een tussentabel(relatie), met twee 1-op-veel relaties. De primaire key van deze nieuwe relatie is ALTIJD de combinatie van de twee vreemde sleutels.
tupel: een instantie van een object, een rij in de tabel.
3.2. relationeel model interpreteren
(Zie 3.1 en onderstaande begrippen)
NULL-waarden:
- NULL: betreffende veld/attribuut van dit tupel is geen waarde ingevuld
- NOT NULL: bij het maken van een tabel aangeven met "NOT NULL" dat het betreffende attribuut altijd een (geldige?) waarde moet hebben.
De tabel waaruit de foreign key komt, kun je niet zomaar aanpassen omdat de "waarde" gebruikt wordt in een andere relatie en dan zou de referentiele integriteit in het geding komen. Daarom bestaan er update en delete regels.
Je kunt kiezen uit drie typen update en deleteregels:
1) Restricted (no action) DEFAULT:
Zolang er nog vreemde sleutels zijn die de waarde hebben van de betreffende primaire key, mag de primaire key niet gewijzigd worden.
Zolang er nog vreemde sleutels zijn die de waarde hebben van de betreffende primaire key, mag de tupel met betreffende primaire key niet verwijderd worden.
2) Cascades (waterval):
Als een primaire sleutel gewijzigd wordt, wordt ook de foreign key die naar deze primaire key wijst gewijzigd.
Als een primaire sleutel verwijderd wordt, wordt ook de tupel met de foreign key die hiernaar verwijst volledig verwijderd.
3) Nullifies
Alle verwijzigen in een vreemde sleutel worden leeg gemaakt (NULL).
4.1 Relationeel Database Management Systeem (DBMS)
DBMS
DML
DCL
DDL
5.1. Data Manipulation Language
DML gebruik je voor het manipuleren van de data/inhoud van de DB.
SQL statement: SQL is een standaardtaal (door ANSI ontwikkeld) waarmee je een relationele database kunt beheren en gebruiken. Een SQL statement is een "zin" waarmee je gegevens uit de DB haalt.
clausule: met een clausule geef je aan waaraan je resultaten moeten voldoen (beperkt de uitkomst).
DBMS: Database Management Systeem. Hiermee kun je DB beheren en gebruiken.
inhoud van een database: de tupels
integriteit (zie entiteitsintegriteit en referentiele integriteit).
kolom: een attribuut in z'n domein
rij: een tupel
object: een relatie
relationeel schema: notatie: modulenaam(primary key, attr1, attr2 etc) waarbij de primairy key onderstreept wordt.
Bij een samengestelde primairy key worden de delen van de key samen onderstreept.
selectie: deel van de inhoud van de tabel
tabel: een relatie
volgorde(?)
voorwaarde
Opdrachten:
- SELECT (opvragen gegevens)
- INSERT (invoegen tupel)
- DELETE (verwijderen tupel)
- UPDATE (wijzigen tupel)
Clausules
- SELECT (selecteer)
- SELECT * (selecteer alle attributen)
- FROM (van de DB)
- WHERE (waarvoor geldt)
- AND (en)
- OR (of)
- NOT (niet)
- DISTINCT (uniek)
- GROUP BY (groeperen per attribuut)
- HAVING: gebruik je bij een aggregatie met voorwaarden, bijvoorbeeld "geef het aantal onderwerpen per module waarbij het aantal onderwerpen minimaal 4 bedraagt:
SELECT code, count(*) FROM onderwerp GROUP BY code HAVING count(*) > 4;
- INSERT INTO (invoegen in)
- VALUES (waardes,in combinatie met INSERT INTO)
- DELETE FROM (verwijder van)
- UPDATE (wijzig)
- SET (in combinatie met Update)
functies
- SUM (som)
- AVG (gemiddelde)
- COUNT (aantal)
- MIN (minimum)
- MAX (maximum)
operatoren
- = : is gelijk aan
- > : is groter dan
- < : is kleiner dan
- >= : is groter of gelijk aan
- <= : is kleiner of gelijk aan
- <> : is ongelijk aan
(NOT) IN : (niet) in
subquery met [NOT] IN: gebruik je bijvoorbeeld bij de vraag :geef een overzicht van alle docenten die geen modules onderwijzen. Dit kun je niet in één query defineeren, dus moet je subquery maken:
SELECT naam FROM docent WHERE nummer NOT IN (SELECT nummer FROM docentmodule);
5.2. Data Definition language
DDL gebruik je voor het aangeven en onderhouden van de structuur van de DB.
gegevenstypen
- INTEGER: gehele getallen
- SMALLINT: kleine gehele getallen
- DATE: datum (jaar-maand-dag)
- TIME: tijd (hh:mm:ss)
- CHAR(n): string met vaste lengte n
- VARCHAR(n): string met variable lengte, met max lengte n
opdrachten
- CREATE TABLE: maak tabel
- DROP TABLE: verwijder tabel
- ALTER TABLE: wijzig tabel
PRIMARY KEY(code)
of PRIMARY KEY(code,nummer) (samengestelde key)
tabel vreemde sleutel: FOREIGN KEY (kolomnaamxp[,kolomnaamy]? REFERENCES tabelnaam
Waarbij tabelnaam MOET bestaan bij het definieren van een FOREIGN KEY.
Dus de volgorde van aanmaak van tabellen is belangrijk, eerst degenen zonder FOREIGN KEY, daarna de tabellen die daarnaar verwijzen.
Maken van een FOREIGN KEY is het implementeren van de referentiele integriteitsregels.
Bijvoorbeeld: CREATE TABLE tablenaam (kolomnaam1 gegevenstype1 NOT NULL, kolomnaam2, gegevenstype2) PRIMARY KEY kolomnaam1 REFERENCES tablenaam2 ON DELETE{NULLIFIES} ON UPDATE{CASCADES}
5.3. Data Control language
DCL gebruik je om autorisatie te regelen.
Bij autorisatie kun je twee benaderingen volgen:
1) positieve benadering: je mag alles behalve de rechten die je worden afgenomen.
2) negatieve benadering: je mag niet behalve de rechten die je worden toegekend. Dit verdient de voorkeur.
Bij autorisatie, met name als je GRANT en REVOKE opdrachten door elkaar gebruikt is de volgorde van de commando's van cruciaal belang. Het DBMS voert de opdrachten in volgorde na elkaar uit, en als de volgorde niet juist is, kan het zijn dat bepaalde opdrachten door vervolgopdrachten weer teniet gedaan worden.
opdrachten
- GRANT (toekennen)
- REVOKE (afnemen)
Clausules (rechten die je kunt toekennen of afnemen
- SELECT (select opdracht)
- UPDATE (aanpassen tupel)
- INSERT (invoegen tupel)
- DELETE (verwijderen tupel)
- ALTER (wijzigen tabel)
- ALL (alle rechten)
- ON (op de tabel..)
- TO (aan de persoon...bij GRANT)
- FROM (van de persoon .. bij REVOKE)
PUBLIC: algemeen aan iedereen rechten tegelijk toekennen kan met PUBLIC.
============================================
Officiële, volledige syntax beschrijving van de behandelde SQL statements.
(5.1)
DDL
??
(5.2)
DCL
GRANT {SELECT | INSERT | DELETE | UPDATE [kolomnaam] ? | INDEX | ALTER | ALL } ON tabelnaam TO {PUBLIC | gebruikernaam ? } [WITH GRANT OPTION]
REVOKE {SELECT | INSERT | DELETE | UPDATE | INDEX | ALTER | ALL } ON tabelnaam FROM {PUBLIC | gebruikernaam ? }.
(5.3)
DML
SELECT {[tabelnaam.]* | {[tabelnaam.]kolomnaam1 |
[,functie1]|berekening}[[tabelnaam.]kolomnaam2 |
[,functie2]|berekening]?}
FROM tabelnaam1 [alias] [, tabelnaam2 [alias]}?
[WHERE-clausule]
[GROUP BY [tabelnaam.]kolomnaam1 [, [tabelnaam2 ] ] ]
[HAVING functievoorwaarde1
[{AND|OR} functievoorwaarde2 ] ] ]
[WHERE voorwaarde1
[ {AND | OR} voorwaarde2 ] ?]
Voorwaarde
{{constante | berekening | [tabelnaam.]kolomnaam} {=|>|<|>=|<=|<>} {{constante | berekening | [tabelnaam.]kolomnaam|(subquery1)} |[tabelnaam.]kolomnaam {IS [NOT] NULL | [NOT] IN (subquery2) | [NOT] EXISTS subquery3) }
functievoorwaarde
{functie | constante } {=|>|<|>=|<=|<>} {functie | constante }
INSERT
INTO tabelnaam [ (kolomnaam1 [.kolomnaam2]) ]
{VALUES (waarde1 [,waarde2]..) | query }
DELETE
FROM tabelnaam
[WHERE-clausule]
==========================================================================