Opleiding Kunstmatige Intelligentie cursus Databases voor AI

1. Opleiding Kunstmatige Intelligentiecursus Databases voor AI Tweede college Rolland: hoofdstuk 3 F. de Vries

2. College 2 Databases AI

3. Opdracht 1

4. Elementen van ERD • Entiteit, attribuut, relatie • Cardinaliteit van relatie • Zwakke entiteit • Sleutel, gecombineerde sleutel • Subtyping, specialisatie, generalisatie • ‘Ternary’ relatie, recursieve relatie • Grouping, aggregatie

5. Het relationele database model Literatuur: Rolland, “The Essence of Databases”, Hoofdstuk 3

6. Relationele Model • Een uniforme structuur, de ‘relation’ als een verzameling (set) van ’tuples’. • tuple: afbeelding van attributen op waarde-instanties uit het onderliggende domein. • Bijvoorbeeld tuple:Persoon(Naam, Vnaam, Straat, HNr, Stad, Postcode) • Met instantie van zo’n tuple: Persoon(de Vries, Jan, Brink, 6, Soest, 1018 WB) • De set van deze tuple-instanties vormt de relatie ‘Persoon’.

7. Terminologie • Relatie: • een tabel met kolommen en rijen(een set van tupels) • Attribuut: • de naam van een kolom(de (rol)naam van een element in een tupel) • Domein: • de verzameling mogelijke waarden voor een attribuut Naam - chars(20) Leeftijd - x:int, o<x,110 Kleur - x:{rood, groen, blauw} Dag - x:{mndg, dnsdag, … … , zndg}

8. Terminologie-2 • Tupel: • een rij in een relatie • Degree: • het aantal attributen in een relatie • Cardinaliteit: • het aantal tupels in een relatie • Relationele database: • een verzameling (genormaliseerde) relaties • Base relation: • Een expliciet, door de DBA gedefinieerde en benoemde relatie. De tuples zijn daadwerkelijk (fysiek) opgeslagen in de database.

9. Instanties van “Branch” en “Staff” relaties 6

10. Een database schema, als verzameling van relatie schema’s Branch(B#, Street, Area, City, Pcode, Tel, Fax) Staff(S#, Fname, Lname, Address, Tel, Pos, Sex, DOB, Salary, NIN, B#) Property_for_rent(P# , Street, Area, City, Pcode, Type, Rooms, Rent, O#, S#, B#) Renter(R#, Fname, Lname, Address, Tel, Pref, Max_rent, B#) Owner(O# , Fname, Lname, Address, Tel) Viewing(R#, P# , Date, Comment)

11. Eigenschappen van relaties • Elke relatie tabel heeft een unieke naam. • Elke cel binnen een relatie heeft precies één (atomaire) waarde. • Elke attribuut binnen een tabel heeft een unieke naam. • Waarden van één attribuut komen allen uit één domein. • Volgorde der attributen is van geen betekenis. • Volgorde der tupels is van geen betekenis. • Elk tupel is uniek, er zijn geen duplicaten.

12. Sleutels Elk element in de set (elke tupel in de relatie) moetuniek identificeerbaar zijn d.m.v. een sleutel • Super keys • Candidate keys • Primary keys • Alternate keys • ( Foreign keys )

13. Super keys • definitiesuper key • een super key is een attribuut dat, of een verzameling van attributen die een tuple uniek identificeert • Voorbeeld: sofinummer + naam + personeelsnr + … + …

14. Candidate keys • definitie candidate key • een candidate key is een super key die minimaal is (dwz geen enkele subset van de attributen is nog steeds zo’n sleutel) • uniek (identificeert een tupel op unieke wijze) • minimaal (irreducible: geen subset kan dat)

15. Super Key Candidate Key Primary Key = Alternate Keys Primary keys • definitie primary key • een primary key is een uitverkoren candidate key die gebruikt wordt om tuples binnen een relatie te identificeren

16. Foreign keys • definitie foreign key (verwijssleutel) • een foreign key is een attribuut (of een verzameling van attributen) in een tabel die verwijst naar (matcht met) een kandidaat-sleutel van (meestal) een andere tabel.

17. Missende waarden: null • Staat voor een attribuut met onbekende (of niet van toepassing zijnde) waarde. • Neemt onvolledige kennis of exceptionele waarde voor zijn rekening. • Speciaal symbool (i.e. is anders dan ø of spaties). • Bijvoorbeeld:viewing(CR56, PG4, 26-mei-98, null)

18. Relationele integriteit • Entiteit integriteit • een primary key kan nooit en nergens de waarde null krijgen (in een ‘base relation’) • Referentiële integriteit • Een foreign key waarde moet matchen met een candidate key waarde (m.a.w. die waarde moet bestaan), maar null is toegestaan … • Enterprise constraints • door de gebruiker toegevoegde beperkingen

19. Referentiële integriteit • Wat te doen als de plek waarnaar verwezen wordt wijzigt (UPDATE, DELETE) • Doe niets (inconsistente database). • Verbied. • CASCADE. • Set to NULL.

20. Referentiële integriteit, MsAccess

21. Mathematische relatie • Beschouw twee sets D1 and D2, metD1 = {a,b} en D2 = {1,3,5} • Het cartesiaans product D1 D2 is de set van alle geordende paren, waarbij het 1e element uit D1 en het 2e element uit D2komt. • M.a.w. alle mogelijke combinatieparen van elementen uit D1 en D2. • D1  D2 ={(a,1),(a,3),(a,5),(b,1),(b,3),(b,5)}

22. Mathematische relatie: definitie Dan ... • Elke willekeurige subset van een carthesiaans product is een relatie, bijvoorbeeld:R = {(a,1),(b,1)} • Je kan ook specificeren welke paren in de relatie zitten, bijvoorbeeld:R = {(x,y)| x  D1, y  D2 en y=1}

23. Mathematische relatie -2 • Eenvoudig model. • Komt goed overeen met hoe wij over de wereld denken (dingen die bestaan: ‘existentieel conjunctief’). • Er bestaan relationele talen die relaties manipuleren: • relationele algebra, • relationele calculus. • Een operatie op een relatie levert altijd weer een relatie op !! (closure).

24. n-domeinen relatie • D1, D2, . . ., Dn representeren n sets met het volgende Cartesiaans product: D1 D2. . .  Dn = {(d1, d2, . . . , dn) |d1  D1, d2  D2, . . . , dn  Dn} • Notatie: n X Di i=1 • Een relatie is dus vastgelegd door de domeinen waaruit de waardes gekozen worden.

25. Voorbeeld • geslacht - {man,vrouw} • stemgedrag - {PvdA, VVD, CDA} • opleiding - {LO, MBO, VWO, HO} • Vraag: • Wat is het cartesiaans product hiervan? • Wat is een mogelijke relatie?

26. Relationele algebra • Operatoren die werken op 1 of 2 tabellen en die een nieuwe tabel produceren • Dus … output van een operator kan input zijn voor een volgende operator • Dus … nesting van operatoren is mogelijk (vergelijk wiskundige operatoren)

27. Operatoren • vijf-tal basis operatoren: • Selectie (RESTRICT) • Projectie (PROJECT) • Cartesiaans product (TIMES) • Union (UNION) • Set difference (MINUS) • additionele operatoren (herleidbaar tot basisoperatoren) • JOIN, INTERSECT, DIVIDE

28. RESTRICT (~SELECT) Selecteer rijen uit een tabel onder bepaalde condities student: {collegekaart, naam, woonplaats} RESTRICT (student) woonplaats = “Amsterdam” AND naam = “Jan Janssen”; produceert een tuple-subset van studenten die aan beide criteria voldoen. Dit kan een lege verzameling zijn!

29. PROJECT Selecteer kolommen uit een tabel student: {collegekaart, naam, woonplaats} PROJECT naam, woonplaats (student); produceert een kopie van de student tabel waarin de kolom met het collekaartnummer is weggelaten.

30. Cartesiaans Product R TIMES S • Stel: relatie R is van graad n en heeft i tuples, en relatie S is van graad m met j tuples • Dan heeft het Cartesiaanse product • de graad n + m (i.e. n + m attributen) • cardinaliteit ij • In de praktijk wordt meestal een JOIN gebruikt: dit is een SELECT over een Cartesiaans product

31. Voorbeeld Cartesiaans product • Maak een lijst met de namen en commen-taren van alle huurders die een object bezocht hebben: (clNo, fname, lname(Client)) X (clNo, propNo,comment (Viewing))

32. (NATURAL)JOIN (client, viewing)

33. JOIN (familie) • Selectie uit een product waarbij de matchende attributen expliciet worden opgegeven. • De attributen moeten hetzelfde domein hebben student: {collegekaart, naam, woonplaats} inschrijving: {student_id, cursus} JOIN(student, inschrijving) collegekaart = student_id; produceert de tuple-subset van het product van twee tabellen, waarvoor geldt dat de twee attributen dezelfde waarde hebben binnen de tuple.

34. Andere relational-algebra operatoren R UNION S R MINUS S R INTERSECT S • Deze operatoren hebben de standaard set-theoretische betekenis. De relaties moeten compatible zijn (zelfde aantal attributen elk uit hetzelfde domein!!) • UNION en INTERSECT zijn commutatief, MINUS niet R DIVIDE S • Produceert een (sub)set van tupels uit R, gedefinieerd over attributen C die matchen met ELK tupel in S

35. Voorbeeld DIVIDE R DIVIDE S

36. INTERSECT is redundant • R INTERSECT S • The intersection operation consists of the set of all tuples that are in both R and S. • R and S must be union-compatible. • Expressed using basic operations R INTERSECT S = R MINUS (R MINUS S) S R RMINUSS 51 RINTERSECTS

37. Andere talen / non-procedureel ! • Relationele calculus • ‘Transformatie-talen’ zijn niet-procedurele talen die input data, gerepresenteerd in een relatie omzetten naar de gewenste output relatie (bijv.: SQL). • ‘Grafische (visuele) talenzijn niet-procedurele talen die de gebruiker een grafische voorstelling van de gegevens bieden. De gebruiker construeert een voorbeeld van wat hij wil hebben en het systeem geeft de gewenste uitput (bijv.: QBE).

38. Basis relaties en views • Base Relation • A named relation, corresponding to an entity in a conceptual schema, whose tuples are physically stored in database. • View • Dynamic result of one or more relational operations operating on the base relations to produce another relation.

39. Views • Een view ziet er voor de gebruiker uit als een echte tabel, maar deze is virtueel ! • Een view wordt dynamisch, op het moment dat ze nodig is, aangemaakt door het toepassen van een of meer operatoren (een ‘query’) op een of meer basis relaties. • Views zijn dynamisch. Veranderingen in de basis relaties worden onmiddellijk gepropageerd naar alle relaties die hierop gebaseerd zijn. • Als een VIEW gewijzigd wordt, dan MOETEN de onderliggende BASE relations gewijzigd worden (N.B.: dat is niet altijd mogelijk !).

40. Updating Views • Maar, er zijn restricties op het updaten van onderliggende relaties via een view: • Wel als: • Wel als er één enkele base-relation aan ten grondslag ligt, • Niet als: • Niet als er meerdere base-relations aan ten grondslag liggen, • Niet als het view is gemaakt met een SQL aggregatie of groep operator. Zie verder bij SQL

41. Purpose of Views • Provides a powerful and flexible security mechanism by hiding parts of database from certain users. • Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time. • It can simplify complex operations on base relations.

42. Wanneer is een database ‘Relational’ ? • Minimaal relationeel: • Relaties met attributen uit eenvoudige domeinen. • operaties: RESTRICT (SELECT), PROJECT en JOIN. • Relationeel compleet: • minimaal relationeel. • operaties: UNION en MINUS. • Volledig relationeel: • relationeel compleet. • door gebruiker definieerbare domeinen.