<Mdl01 hoorcollege 1>

1. <Mdl01 hoorcollege 1> Docent: Martijn Hengelmolen Email: Hengelmolen@initworks.com Aanwezig: maandag, dinsdag

2. Inhoud Hoorcollege 1 • Waarom modelleren 1 • Voorbeeld tekstueel ontwerp • Opdracht

3. Voorbeeld model • Rechtopstaande holle cilinder

4. Voorbeeld model • Rechtopstaande holle cilinder • Onderkant is gesloten

5. Voorbeeld model • Rechtopstaande holle cilinder • Onderkant is gesloten • Aan de zijkant zit een driekwart ovaal • Ovaal is kleiner dan cilinder • Kleur is wit

6. Voorbeeld model • Een mok, waterkoker? Thermoskan? • Vraag: is dit eenduidig?

7. Opdracht • Verzin een voorwerp • Beschrijf dit voorwerp • Geef tekstuele beschrijving aan mede student.

8. SELECT basisbegrippenhoofdstuk 5 [van der Lans] • datatypen (num, alfanum, datum/tijd) • systeemvariabelen (USER, TODAY) • expressies (o.a. CASE) • scalaire vs. set functies: • scalaire functies: input is 1 rij • set functies: input is verzameling rijen • casting

9. SELECT instructie:definitie <select-instructie>::= <select-component> <from-component> [<where-component>] [<group-by-component>] [<having-component>] [<order-by-component>]

10. SELECT instructie:voorbeeld SELECT PNAAM, LEEFTIJD [kolom(men)] FROM PATIENT [tabel(len)] WHERE LEEFTIJD > 50; [voorwaarde voor rij]

11. a b SELECT instructie:volgorde van uitvoeren (intern) SELECT [kolommen] [3] FROM [tabellen] [1] WHERE [voorwaarde voor rij] [2] ORDER BY [kolom] [4] 1. FROM 2. WHERE 3. SELECT 4. ORDER BY

12. 1. FROMA 2. WHERE KLEUR=‘Blauw’ 3. SELECT ANO, ANAAM 4. ORDER BY ANAAM SELECT instructie:volgorde (voorbeeld) SELECT ANO, ANAAM FROM A WHERE KLEUR=‘Blauw’ ORDER BY ANAAM

13. SELECT instructie:FROM component (definitie) <from-component> ::=FROM <tabelref> [{,<tabelref>}...] <tabelref> ::=<tabelspec> [[AS] <alias>] <tabelspec> ::=[<user>.] tabelnaam

14. SELECT instructie:FROM component (voorbeeld) SELECT teamnr, naamFROM teams AS t, spelers AS sWHERE t.spelersnr = s.spelersnr Dit is een voorbeeld van een natural join

15. Natural join in stapjes:1. FROM - input SELECT .... FROM teams AS t, spelers AS s

16. Natural join in stapjes:2. FROM - resultaat SELECT ... FROM teams AS t, spelers AS s

17. Natural join in stapjes:3. WHERE - input WHERE t.spelersnr = s.spelersnr;

18. Natural join in stapjes:4. WHERE - resultaat SELECT ... FROM teams AS t, spelers AS s WHERE t.spelersnr = s.spelersnr;

19. Natural join in stapjes:5. SELECT - resultaat SELECT teamnr, naam as aanvoerder FROM teams AS t, spelers AS s WHERE t.spelersnr = s.spelersnr;

20. SELECT instructie:WHERE (pseudo-code) WHERE-resultaat := [ ]; for each ROW in FROM-resultaat doif conditie=waar thenWHERE-resultaat :+ ROW; endfor;

21. SELECT instructie:WHERE (operatoren etc) • vergelijking: = < > <= >= <> • AND, OR, NOT • BETWEEN, IN, LIKE, NULL • IN met subquery • vergelijkingsoperator met subquery • ANY, ALL • EXISTS

22. SELECT instructie:WHERE (voorbeelden) • WHEREGEB_DATUM > ‘1970-12-31’ ANDNOT NAAM = ‘Haas’; • WHERE GEB_DATUM BETWEEN ‘1960-1-1’ AND ‘1970-1-1’; • WHERE NAAM IN (‘Smit’,’Jansen’,’Zwart’);

23. SELECT instructie:WHERE (IN met subquery) • SELECT naam AS aanvoerderFROM spelersWHERE spelersnr IN (SELECT spelersnr FROM teams);

24. SELECT instructie:WHERE (EXISTS) • SELECT naam AS aanvoerderFROM spelersWHERE EXISTS(SELECT * FROM teams WHERE spelersnr=spelers.spelersnr);

25. SELECT instructie:WHERE (ALL) • SELECT naam+’ is het oudst’ AS oudste, geb_datumFROM spelersWHERE geb_datum <= ALL(SELECT geb_datum FROM spelers);

26. SELECT instructie:GROUP BY, HAVING • volgend hoorcollege: • SET functies (COUNT, SUM, MIN, MAX, AVG, STDEV) • GROUP BY, HAVING

27. Referentiele Integriteit • spelernsnr in beide tabellen moeten gesynchroniseerd blijven foreign key primary key

28. Referentiele Integriteit :refs aangeven bij CREATE • CREATE TABLE teams(teamnr SMALLINT NOT NULL,spelersnr SMALLINT NOT NULL,divisie CHAR(6) NOT NULL,PRIMARY KEY (teamnr),FOREIGN KEY (spelersnr) REFERENCES spelers (spelersnr));

29. Referentiele Integriteitrefererende acties • default (SOLID): • ON UPDATE RESTRICT • ON DELETE RESTRICT(dwz wijzigen/verwijderen van een spelersnr in de SPELERS tabel wordt tegengehouden als spelersnr in TEAMS voorkomt)

30. Referentiele Integriteitrefererende acties • alternatief 1: • ON UPDATE CASCADE • ON DELETE CASCADE(dwz update/delete van een spelersnr in de SPELERS tabel triggert automatischeen update/delete in TEAMS) • alternatief 2: • ON UPDATE SET NULL • ON DELETE SET NULL

31. Referentiele Integriteitvragen • wat is de beste oplossing voor SPELERS en TEAMS? • ON UPDATE [restrict, cascade, set null] • ON DELETE [restrict, cascade, set null] • wat is de beste oplossing voor SPELERS en BOETES? • ON UPDATE [restrict, cascade, set null] • ON DELETE [restrict, cascade, set null]

32. Database Ontwerp • Wat is het? • Hoe doe je het? • Voorbeeld • Normaliseren (1NF, 2NF, 3NF, BCNF) • Opdracht

33. Database ontwerp :Wat is het ? • het bepalen van de tabellen en hun kolommen die nodig zijn om bepaalde gegevens op te slaan (structuur) • logisch ontwerp (niet fysiek) • C. J. Date : ‘database design is still very much of an art, not a science’ • gegevens integriteit

34. Database ontwerp : Hoe doe je het? • ontwerpmethoden (zie [Kroenke]): • E/R (entity-relationship) • semantisch object model • controle: • normaliseren

35. Database ontwerp voorbeeld: administratie van uitgeleende boeken (1) • voor wie : • de eigenaar van de boeken • functie : • het geven van een actueel overzicht van alle uitgeleende boeken; bovendien per boek: • aan wie (het boek is uitgeleend) • sinds wanneer (het boek is uitgeleend)

36. Database ontwerp voorbeeld: administratie van uitgeleende boeken (2) • bedenk eerst hoe je het zonder ge-automatiseerd systeem zou doen! ?

37. Database ontwerp voorbeeld: administratie van uitgeleende boeken (3) • bedenk eerst hoe je het zonder ge-automatiseerd systeem zou doen! • schrift met 1 regel per uitgeleend boek(auteur, titel, lener_naam, lener_telnr, sinds) • vgl. database met 1 tabel:‘uitgeleende boeken’

38. Database ontwerp voorbeeld: administratie van uitgeleende boeken (4) • problemen: • wijzigen van telnr op meerdere plaatsen • bij terugbrengen boek ook telnr weg • hoe komt dit ? • afhankelijkheid : lener_naam -> lener_telnr • lener_naam is een determinant van lener_telnr

39. Database ontwerp voorbeeld: administratie van uitgeleende boeken (5) • oplossing: 2 tabellen • schrift met uitgeleende boeken • adresboekje (of GSM telefoon): naam + telnr

40. Normalisatie: 1NF (first normal form) • Definitie 1NF:Een tabel is in 1NF als voor elke waarde van die tabel elke rij precies 1 waarde voor elke attribuut heeftvoorbeeld: in de tabel leners heeft elke rij 1 naam en 1 telnr

41. Normalisatie: 2NF • Definitie 2NF:(aanname: er is slechts 1 kandidaat sleutel die de primaire sleutel is)Een tabel is in 2NF als deze in 1NF is, en elk niet-sleutel attribuut (op de een of andere manier) afhankelijk is van de primaire sleutel

42. Normalisatie: 2NF PK

43. Normalisatie: 2NF PK PK

44. Normalisatie: 3NF • Definitie 3NF:(aanname: er is slechts 1 kandidaat sleutel die de primaire sleutel is)Een tabel is in 3NF als deze in 2NF is, en elk niet-sleutel attribuut niet-transitief afhankelijk is van de primaire sleutel

45. Normalisatie: 3NF PK 2NF 3NF

46. Normalisatie: BCNF (Boyce/Codd Normal Form) • (informele) definitie:een tabel is in BCNF als de enige determinanten kandidaat-sleutels zijn(dwz alle afhankelijkheidspijlen beginnen bij kandidaat-sleutels)

47. Normalisatie: BCNF (Boyce/Codd Normal Form)

48. Normalisatie: BCNF (Boyce/Codd Normal Form)

49. Normalisatie:conclusie • Wat zijn we eigenlijk aan het doen? • Normalisatie is een kwestie van gezond verstand! • De zojuist besproken methoden zijn ‘geformaliseerd’ gezond verstand! • Met wat ervaring weet je intuïtief wanneer tabellen BCNF zijn

50. Opdracht (5 minuten) • Breng in BCNF: