TECHNISCHE UNIVERSITEIT EINDHOVEN Faculteit Wiskunde en Informatica Proeftentamen ISO (2R290), November 2005 Dit proeftentamen bestaat uit twee opgaven met een aantal deel-opgaven. Geef de oplossingen kort, bondig en duidelijk weer. Geef toelichting waar nodig, maar voeg geen onzin toe waar je denkt dat toelichting nodig is maar je niks zinnigs kunt bedenken. 1. (zou 4 punten opleveren bij het echte tentamen) VR (Verwegistan Railways) verzorgt het reizigersvervoer per spoor (en bij calamiteiten ook per bus, of helemaal niet). VR beschikt over een aantal treinen (in vaste samenstellingen) die een identificatienummer hebben (voor de trein als geheel dus). De treinen worden ingezet om trajecten te rijden op bepaalde tijdstippen. Een traject wordt gekenmerkt door een begin en eindstation en een aanduiding IC en ST (intercity of stoptrein), en bevat een aantal tussenstations (begin en eind staan daar ook bij) met per station de minimale aankomst- en vertrektijden in minuten na het vertrek aan het beginstation. Bij elk traject en vertrektijd (aan het beginstation) hoort precies één trein. Elke trein wordt bestuurd door een machinist. Er zijn ook altijd twee conducteurs op de trein. De machinist en conducteurs kunnen tijdens elke stop hun dienst verder zetten op een andere trein (die daar later stopt), maar hun plaats moet door een andere machinist dan wel conducteurs worden ingenomen. a) Ontwerp een ER-model (ER-diagram) voor het informatiesysteem VRIS dat de treinen, trajecten en toewijzing van machinisten en conducteurs administreert. Geef attributen en sleutels aan en cardinaliteits-beperkingen. b) Geef een vertaling van dit model naar een relationeel model (tabellen). Leg kort uit waarom entiteitverzamelingen en relatieverzamelingen wel of niet door een tabel worden voorgesteld, en waarop de keuze van attributen en sleutels van de tabellen is gebaseerd. We breiden VRIS uit om een VR reisplanner mogelijk te maken. Van elke stop die een trein maakt (in een station) leggen we vast op welk perron de trein aankomt (en weer vertrekt). We leggen ook vast welke de eerste toegestane overstap is, voor elk ander traject met een stop in hetzelfde station. (Zo kunnen we later de kortste reisweg berekenen tussen elk paar stations, gegeven een bepaald vertrek- of aankomst-tijdstip, waarbij we er rekening mee houden dat de tijd die nodig is om een overstap te maken verschillend kan zijn bij elke overstap.) We leggen bij elk traject ook de afstand vast van het beginstation tot elk ander station. (Dit laat ons later toe om de totale afstand van een reis te bepalen, en daarop de prijs van een ticket te baseren.) c) Geef een nieuw ER-model (ER-diagram) voor het uitgebreide VRIS. d) Geef een vertaling van het nieuwe VRIS naar tabellen.
2. (zou 6 punten opleveren bij het echte tentamen) De bier-associatie “Schol” verenigt enthousiaste bierdrinkers en cafés. Ze gebruikt het onderstraande (relationele) ScholIS systeem, waarbij onderstreepte attributen primaire sleutels zijn. (We gebruiken leeftijd als attribuut, maar dat is in het algemeen natuurlijk geen goed idee. We gebruiken ook namen als identificerende attributen, wat in het algemeen ook geen goed idee is. Maar deze vereenvoudigingen maken het schema eenvoudiger te begrijpen en gebruiken.) drinker(naam, geslacht, gemeente, leeftijd) café(cafénaam, gemeente, eigenaar) bier(biernaam, brouwerij, percentage) bezoek(drinkernaam, cafénaam) schenkt(cafénaam, biernaam) Beschrijf de volgende vragen in de relationele algebra: a) Geef de (namen van) cafés die alcoholvrij bier (0% alcohol) schenken. b) Geef de (namen van) cafés waar geen vrouwen komen. Beschrijf de volgende vragen in SQL: c) Geef een lijst van (alle) brouwerijen, met per brouwerij het gemiddelde alcoholpercentage van de bieren die door die brouwerij gebrouwen worden. d) Geef de (namen van) cafés waar geen vrouwen komen die jonger zijn dan 25 jaar. Beschrijf de volgende vragen in QBE: e) Geef een lijst van (namen van) drinkers die naar een café gaan waar ook een andere drinker komt die in dezelfde gemeente woont (als die eerste drinker). f) Geef een lijst van (namen van) drinkers die naar een café gaan in de gemeente waar ze wonen, en waar ook nog een andere drinker komt die in dezelfde gemente woont (als die eerste drinker).
Uitwerking vraag 1 Het zwarte gedeelte is het eenvoudige schema. Het rode deel is wat er bij komt in de uitbreiding. Het modelleren begint met trajecten. Een traject bevat 2 of meer stations van soort b(egin), t(ussen) of e(ind). Bij elk traject-station staat de relatieve aankomst- en vertrektijd. Bij een traject horen 0 of meer diensten; elke dienst heeft een absolute aanvangstijd (btijd), een trein en drie medewerkers (via md): 2 van soort c(onducteur) en 1 van soort m(achinist). De uitbreiding kent de entiteit "stop" tussen station en dienst met een perron. De relatie "ovmog" geeft aan tussen welke stops een overstap mogelijk is. Je kunt bijvoorbeeld afspreken dat dit alleen nodig is als er minder dan 5 minuten tussen aankomst van de "van" en vertrek van de "naar" trein ligt. De tijden kunnen berekend worden doordat bij elke stop een "bevat" relatie-elt hoort: die tussen het station en het traject bij de dienst. Bij het begintijdstip van de dienst moeten de relatieve aankomst- en vertrektijden van het bijbehorende "bevat" relatie-elt opgeteld worden om de werkelijke aankomst- en vertrektijden te berekenen. Opmerking: bij de uitbreiding is het zinnig om van de relatie "bevat" een entiteit te maken. Dan kan het bovenstaande verband rechtstreeks worden gemodelleerd. Bij de vertaling naar tabellen zien we dat elke entiteitverzameling en elke veel op veel relatie wordt omgezet naar een tabel. Voor de veel-op-een relaties “td”, “van”, “sd” en “ss” is er geen tabel nodig.
srt(m/c) trein
trnid
0..*
md
medewerker
mnaam idnr
0..*
srt(ic/st) trajid
3..3 1..1
td
dienst did btijd
1..1
0..*
van
traject
0..*
2..* srt(b/t/e) atijd vtijd
sd
bevat nkms
overstap mogelijk
naar 0..*
van 0..*
snaam
1..1 stop
1..1
ss
0..*
1..*
1..*
station
dist
1..*
stid perron
trein | trnid
station | snaam
dienst | did , trnid , trajid , btijd
bevat | snaam , trajid , srt(b/t/e) , atijd , vtijd
traject | trajid , srt(ic/st)
stop | stid , did , snaam
medewerker | idnr , naam
ovmog | stidvan , stidnaar
md | did , idnr , srt(m/c)
dist | snaam1 , snaam2 , nkms
Uitwerking vraag 2 a) Geef de (namen van) cafés die alcoholvrij bier (0% alcohol) schenken. Π schenkt.cafénaam ( σschenkt.biernaam=bier.biernaam ∧ bier.percentage=0( schenkt × bier ) ) b) Geef de (namen van) cafés waar geen vrouwen komen. Π café.cafénaam( café ) − Π bezoek.cafénaam( σbezoek.drinkernaam=drinker.drinkernaam ∧ drinker.geslacht=”V”( bezoek × drinker ) ) c) Geef een lijst van (alle) brouwerijen, met per brouwerij het gemiddelde alcoholpercentage van de bieren die door die brouwerij gebrouwen worden. select b.brouwerij, avg(b.percentage) from bier as b group by b.brouwerij
d) Geef de (namen van) cafés waar geen vrouwen komen die jonger zijn dan 25 jaar. ( select b.cafénaam from bezoek as b ) except ( select b.cafénaam from bezoek as b, drinker as d where b.drinkernaam = d.drinkernaam and d.geslacht = “V” and d.leeftijd < 25 ) e) Geef een lijst van (namen van) drinkers die naar een café gaan waar ook een andere drinker komt die in dezelfde gemeente woont (als die eerste drinker). drinker
naam _x _y
geslacht
bezoek
drinkernaam _x _y
cafénaam _c _c
gemeente _g _g
leeftijd
conditions _x ¬= _y
f) Geef een lijst van (namen van) drinkers die naar een café gaan in de gemeente waar ze wonen, en waar ook nog een andere drinker komt die in dezelfde gemente woont (als die eerste drinker). drinker
naam _x _y
geslacht
bezoek
drinkernaam _x _y
cafénaam _c _c
café
cafénaam _c
gemeente _g
conditions _x ¬= _y
gemeente _g _g
eigenaar
leeftijd
