Relationele databanken ► De meeste databanken zijn relationeel. ► Gegevens in tabellen. ► Relationele model stoelt op de verzamelingenleer (leer der relaties). ► Relatie betekent hier “tabel”. ► Grote kracht van deze databases is SQL.
Verzamelingen ► Ongeordende serie gelijksoortige elementen. ► Wiskundige bewerkingen: → unie, intersectie (of doorsnede) en verschil: bewerkingen op elementen van hetzelfde type → product A = {1,2,3} B = {1,4,5} A υ B = {1,2,3,4,5} = BυA A ∩ B = {1} = B∩A A – B = {2,3} ≠ B – A = {4,5} A x B = {(1,1),(1,4),(1,5),(2,1),(2,4),(2,5),(3,1),(3,4),(3,5)}
► Venn-diagram.
Product ► Product kan dus ook met elementen van een verschillend type. A = {1,2,3} C = {x,y} A x C = {(1,x),(1,y),(2,x),(2,y),(3,x),(3,y)}
► Product is van een nieuw type. ► Ook cartesisch product genoemd.
Relaties ► Een relatie is een deelverzameling van het product van een aantal verzamelingen. A = {1,2,3} C = {x,y} A x C = {(1,x),(1,y),(2,x),(2,y),(3,x),(3,y)} {(2,x),(2,y),(3,y)} is een relatie
► Verband met tabel. nummer = {1,2,3} naam = {Jan,Mieke,Frank} geslacht = {M,V}
product = {(1,Jan,M),(2,Jan,M),(3,Jan,M),(1,Jan,V),(2,Jan,V),(3,Jan,V), (1,Mieke,M),(2,Mieke,M),(3,Mieke,M),(1,Mieke,V),(2,Mieke,V),(3,Mieke,V), (1,Frank,M),(2,Frank,M),(3,Frank,M),(1,Frank,V),(2,Frank,V),(3,Frank,V)}
student = {(1,Jan,M),(2,Mieke,V),(3,Frank,M)}
Tuples ► Tuple is een element van een verzameling, wat overeenkomt met een rij in een tabel. student = {(1,Jan,M),(2,Mieke,V),(3,Frank,M)} (1,Jan,M) = tuple 1 (2,Mieke,V) = tuple 2 (3,Frank,M) = tuple 3
nummer 1 2 3
naam Jan Mieke Frank
geslacht M V M
Relationele bewerkingen ► Relaties zijn verzamelingen: unie, intersectie, verschil en product zijn toepasbaar. ► Specifieke bewerkingen: selectie, projectie, join. ► Selectie selecteert bepaalde tuples uit relatie. ► Projectie selecteert bepaalde attributen. ► Join is koppeling op grond van de waarden van gemeenschappelijke attributen = één van de grote troeven van relationele databanken.
Talen voor relationele bewerkingen ► Vanaf jaren 70 ontwikkeld. Gebaseerd op de relationele algebra. ► Bijvoorbeeld “een projectie van de relatie 'persoon' op attribuut 'naam' gecombineerd met een selectie van alleen de mannelijke personen” werd geschreven als: project (select geslacht = 'M' from persoon) over naam
► In de loop der jaren talen uit relationele logica (waaronder SQL). Gemakkelijker te begrijpen. ► Standaardvorm = doelgroep gevolgd door voorwaarden = predikaten. select <doelgroep> where
select naam from persoon where geslacht='M'
Relationele bewerkingen in SQL ► SQL is gebaseerd op relationele logica. ► Veel versies van SQL. Eén standaard met per leverancier beperkingen en uitbreidingen. ► Laatste is standaard SQL:2008. Laatste grote uitbreidingen SQL:1999 (SQL3) en SQL:2003. ► In wat volgt hebben we het enkel over de opvragingsopdrachten in SQL (waarvoor de geziene bewerkingen worden gebruikt).
Unie, intersectie en verschil ► SQL opdrachten union, intersect en minus. ► Unie: “geef de namen van alle personen met nummer 1 en/of het nummer 3”. select naam from persoon where nummer=1 union select naam from persoon where nummer=3
► Intersectie: “geef de namen van de personen met de naam Jan én van het geslacht M” select naam from persoon where naam='Jan' intersect select naam from persoon where geslacht='M'
► Minus: “geef de namen van alle mannelijke personen die niet de naam Jan hebben” select naam from persoon where geslacht='M' minus select naam from persoon where naam='Jan'
Selectie en projectie
► Selectie = rijen uit een tabel. Bvb. “geef de namen van alle mannelijke personen”. select naam from persoon where geslacht='M'
► Projectie = kolommen uit een tabel. Bvb. “geef naam en nummer van alle personen” select naam, nummer from persoon
Product en join ► Product van 2 tabellen is meestal zinloos . ► Join (natural, equal) is de “zinnige” variant. select naam, nummer, geslacht, kot, persoon, van, tot from persoon, verblijf select naam, nummer, geslacht, kot, persoon, van, tot from persoon join verblijf on persoon.nummer=verblijf.persoon (joinvoorwaarde)
nummer 1 2 3
naam Jan Mieke Frank
geslacht M V M
kot 1 2 3 4 5
persoon 1 2 3 2 3
van 01/10/05 01/10/04 01/10/04 01/10/05 01/10/05
tot 30/06/05 30/06/05
Andere bewerkingen ► In select opdracht zijn op de meeste plaatsen ook de bekende rekenkundige bewerkingen mogelijk (+ - * / ^ sqr ...) alsook bewerkingen op letterreeksen (trim, upper, lower,...). Afhankelijk van het dialect. ► Edgar Codd is geestelijke vader van relationele model. Formuleerde aantal eisen waaraan relationeel model moest voldoen. ► SQL is dé wereldstandaard. Grondige kennis hiervan is onontbeerlijk.
Andere eisen ► DBMS is meer dan alleen maar relationeel. ► Grijpt in op alle niveaus. Voor elke opdracht wordt vertaald van gebruikersniveau via conceptueel niveau naar opslagniveau en omgekeerd. 1. De gebruiker typt de opdracht in. 2. Het gebruikersprogramma geeft de opdracht door aan de vraagtaal. 3. De vraagtaal vangt de opdracht op en interpreteert hem. 4. Is de opdracht syntactisch correct, dan vergelijkt het DBMS hem met het externe schema van de gebruiker. Hierbij is 'schema' databasejargon voor 'model'. 5. Levert 4 geen fouten op, dan vertaalt het DMBS de opdracht naar het conceptuele schema. 6. Het DBMS vergelijkt de opdracht met het conceptuele schema. 7. Indien er geen fout optreedt, vertaalt het DBMS de opdracht naar het interne schema. 8. Het DBMS voert de opdracht uit op de fysieke database. Hier vinden dus lees- of schrijfopdrachten op fysieke gegevensbestanden plaats. 9. Het DBMS vertaalt het resultaat van de opdracht naar een conceptueel en daarna naar een extern schema en geeft het door aan het programma dat de opdracht gaf. Dit programma zorgt voor een geschikte weergave van het resultaat aan de gebruiker.
► Resultaat is of melding of foutcode.
Performance ► Performance benchmarks = verzameling opdrachten. ► DBMS probeert performance te optimaliseren mbv query optimiser. Aan de hand van “vuistregels” een opdracht naar interne schema vertalen dat zo snel mogelijk is. ► De query optimiser zal soms foutief kiezen. In de grote databases zijn er daar mogelijkheden voor.
Transacties ► Is een logisch geheel van bewerkingen. ► Daarom moeten de bewerkingen bij elkaar gehouden worden. ► Meestal worden de bewerkingen eerst op een kopij uitgevoerd en pas op de echte data na afronding van de ganse transactie. ► Extra commando's commit en rollback.
Transacties voorbeeld Begintoestand twee rekeningen rekening saldo A 110 B 30 Er wordt 20 Euro overgeboekt van rekening A naar rekening B Er wordt daartoe eerst, na controle, 20 Euro gehaald van rekening A rekening A B
saldo 90 30
Tenslotte wordt de 20 Euro op rekening B bijgeboekt rekening A B
saldo 90 50
Transacties en geldigheidsregels ► Wijzigingen in een transactie kunnen integriteit verstoren. ► Controle integriteit na elke bewerking = onmiddellijke integriteitscontrole. ► Integriteitscontrole achteraf. ► Mogelijkheden afhankelijk van DBMS. Heeft ook met performantie te maken.
Gelijktijdigheid = concurrency ► Wanneer # toepassingen hetzelfde bestand benaderen op hetzelfde moment. ► Is één van de cruciale punten die grote databases onderscheidt van kleinere. ► Zware taak om integriteit te bewaren.
Concurrency voorbeeld Begintoestand drie rekeningen rekening saldo A 110 B 30 C 80 Er wordt 20 Euro overgeboekt van rekening A naar rekening B Er wordt tegelijkertijd 40 Euro overgeboekt van C naar A De gewenste eindtoestand is dus rekening A B C
saldo 130 50 40
Hoe kan dat in zijn werk gaan?
Concurrency voorbeeld (vervolg) Toepassing X Tijdstip Tijdstip Tijdstip Tijdstip Tijdstip Tijdstip Tijdstip Tijdstip
0 1 2 3 4 5 6 7
Toepassing Y Lees saldo C = 80 Boek 40 af van saldo C = 40
Lees saldo A = 110 Lees saldo A = 110 Boek 40 bij op saldo A = 150 Boek 20 af van saldo A = 90 Lees saldo B = 30 Boek 20 bij op saldo B = 50
De eindtoestand is dan: rekening A B C
saldo 90 50 40
Hoe concurrency oplossen? ► Gebruikelijk = locking. Zodra een bewerking begint op een tabel wordt die vergrendeld. Anderen moeten wachten. ► Nadeel = performantie. Daarom locking op verschillende niveaus mogelijk (tabel, record of zelfs attribuut). ► Enkel opvragen toepassingen → geen probleem. ► Deadlock. Zie voorbeeld.
Deadlock voorbeeld Toepassing X Tijdstip 0 Open bestand 1: succes Tijdstip 1 Bestand 1 gelocked Tijdstip 2 Open bestand 2: lukt niet W acht tot Y het vrijgeeft Tijdstip 3
Toepassing Y Open bestand 2: gelocked Lees saldo A = 110 Open bestand 1: lukt niet W acht tot X het vrijgeeft
Beveiliging ► Bescherming tegen opzettelijk misbruik. ► Mogelijkheden: 1. Niet beveiligen. De database is door iedereen publiekelijk te benaderen, bijvoorbeeld via het internet of andere elektronische netwerken. 2. Niet beveiligen, maar toegankelijkheid beperken doordat de database op een stand-alone computersysteem draait. 3. Beveiligen doordat de gebruiker iets moet onthouden, meestal een wachtwoord. Er moet ingelogd worden. 4. Beveiligen doordat de gebruiker iets moet bezitten, zoals een diskette, een pasje met code of gewoon een sleutel van een ruimte. 5. Beveiligen door de gegevens versleuteld op te slaan: encryptie ('geheimschrift'). Om de gegevens te kunnen lezen moet men de sleutel kennen die de gegevens ontcijferd. 6. Beveiligen door een combinatie van bovenstaande methodes toe te passen.
► Paswoorden: beperkingen. Encryptie steeds belangrijker.
Recovery ► Herstel van gegevens na rampen. Bvb. stroomstoring, brand, hardware uitval,... ► Er is dus feitelijk gegevensverlies. ► DBMS zelf slechts tot beperkte hoogte in staat zich daartegen te beveiligen. ► Slechts één oplossing: kopies op verschillende plaatsen (backups).
Data-dictionary: metagegevens ► Metagegevens = gegevens over gegevens. Opgeslagen in data-dictionary. ► Kwaliteit data-dictionary is een belangrijk onderscheid tussen databanken. ► Metagegevens over gebruikers, het gegevensmodel en de opslagstructuur. ► Gegevensmodel meest centraal. ► Goede pakketten: geïntegreerde data-dictionary.
Metagegevens kolommen ► de naam van de kolom; ► de tabel waarin de kolom voorkomt; ► het datatype, ofwel de domeindefinitie (liefst zo precies mogelijk, dus bijvoorbeeld niet "een letter" of "character (1)", maar {'M','m','V','d'}; ► andere geldigheidsregels, bijvoorbeeld of de kolom deel uitmaakt van een kandidaatsleutel, identificerende sleutel of foreign key; ► een toelichting op de betekenis, in de vorm van een korte tekst; ► een overzicht van de zichtbaarheid: welke toepassingen of databasegebruikers mogen de kolom lezen, welke wijzigen?
Metagegevens tabel ► de gebruiker die de tabel heeft gecreëerd; ► een overzicht van het geheugengebruik van de tabel.
Andere metagegevens ► een overzicht van gebruikers van de database; ► een overzicht van views voor elke gebruiker, met vermelding van de SQL-opdracht waarop de view is gebaseerd; ► een lijst van de tabellen en kolommen die toegankelijk zijn voor elke gebruiker; ► een lijst met privileges per gebruiker (bvb 'mag uitsluitend opvragen en niets wijzigen', 'mag wel gewone tabellen wijzigen maar geen data-dictionary-gegevens', 'mag alles',...) ► een overzicht van toepassingsprogramma's op de database; overzicht van opslagstructuur in het achtergrondgeheugen waarin staat welke delen van de schijf bezet zijn door welke tabellen, door welke gebruikers, en dergelijke.
Metadata in uitgebreidere zin ► Soms ook: allerlei gegevens over herkomst, aard en kwaliteit van gegevens. ► Als volgt in te delen: ► Identificatie: Wie is de maker van de dataset? Wie is de contactpersoon? Met welk doel zijn de gegevens verzameld? Welke tijdsperiode bestrijken ze? Waar zijn ze te vinden? Welke naam dragen ze? In welke vorm zijn ze beschikbaar? ► Gegevenskwaliteit: Voor welke doelen zijn de gegevens bruikbaar? Hoe precies zijn ze? Hoe betrouwbaar? Uit welke bronnen zijn ze verkregen? Wat is de geldigheidsduur? ► Gegevenstype: Dit betreft de domeinen waaruit de gegevens putten. Men kan dit specialiseren afhankelijk van de discipline waartoe de gegevens behoren. ► Gegevensmodel: Dit is - zoals we weten - de structuur van de gegevens in termen van entiteittypen, attributen en verbanden. ► Distributie: Hoe en waar en onder welke voorwaarden zijn de gegevens te verkrijgen? Zijn de gegevens gepubliceerd? Zo ja, onder welke naam? ► Metadata referentie-info: De metadata kennen hun meta-metadata: wie heeft de metadata opgesteld en wanneer zijn ze bijgewerkt?
Metadefinitie en datamanipulatie ► Behandelen gewone gegevens = datamanipulatie. Behandelen metagegevens = datadefinitie. ► SQL verenigt beide in één taal. ► Om redenen van eenvoud heeft SQL er aparte commando's voor zoals create. Opdracht Opvragen Toevoegen Wijzigen Verwijderen
Gewone gegevens select insert update delete
Metagegevens select create – grant alter drop
Oefening 1
Is het mogelijk een tabel met zichzelf te joinen? Zou het zin kunnen hebben?
Oefening 2 Als een aantal transacties tegelijk dezelfde tabel bevragen maar niet wijzigen, is er geen lock nodig. Indien één van deze transacties ook wijzigingen aanbrengt, is er dan wel een lock nodig op de tabel? Waarom?
Oefening 3 Leg uit waarom redundantie voor een DBMS noodzakelijk is voor recovery. Is het eenzelfde soort redundantie als de redundantie waar we in hoofdstuk 4 van spraken? Wat is het verschil?
