Practicumtoets Databases 1 oktober 2003

Practicumtoets Databases 1 oktober 2003
versie A
Studentnummer
Naam
Cijfer
Op een hogeschool worden database ontwerpprojecten uitgevoerd voor externe opdrachtgevers. Vanwege het
brede karakter van de opdrachten nemen studenten van diverse opleidingen deel aan de projecten. Steeds heeft
echter één opleiding de eind verantwoordelijkheid voor het project. Natuurlijk worden ook de projectgegevens in
een database opgeslagen. Op blackboard vind je onder [Documenten]>[Practicumtoets] het conceptueel ontwerp
van deze database in Dezign formaat. Aan jou de taak om hierbij een relationele database te implementeren.
Van de entiteiten en relaties is bovendien het volgende bekend:
 Een student kan aan meerdere projecten deelnemen, een project kent meerdere studenten als leden.
 Bijgehouden dient te worden welke functie/taak een student in een bepaald project vervult (b.v.
projectleider, databaseadministrator, informatieanalist, etc.)
 Een project kan een vervolgproject zijn op een eerder project. Dit moet geïmplementeerd worden.
Opdrachten:
 Implementeer de database in SQL Server.
Gebruik hierbij als naam: <jouw inlognaam>_toets (b.v. rvwolffela_toets).
Hanteer hierbij de geldende regels voor relationele implementatie.
(30 punten)
In het ontwerp moeten ook de volgende zaken geregeld zijn:
 De opleidingscode bestaat uit drie tekens en moet één van de volgende codes zijn: HIO, TBK of KAM.
Definieer hiervoor een herbruikbaar datatype.
 Als een project afgebroken en dus verwijderd wordt, dan dienen ook alle functies/taken in dit project en
de opdrachtgever automatisch uit de database te worden verwijderd.
 Als de opdrachtgever verandert, b.v. vanwege overname bij een faillissement, dan dient dit ook
automatisch in de projectgegevens aangepast te worden.
 De in te voeren einddatum bij een project dient voorbij de begindatum te liggen.
(4 x 5 punten)
Vul de tabellen met enkele gegevens en ontwerp de volgende query’s (in de vorm van SQL-views)
 Een overzicht van de projecten(omschrijving) met per project de projectleden (studentnummers) met
hun functie en de opleiding(opleidingscode) waartoe ze behoren.
 Het totaal aantal studenten dat aan projecten deelneemt.
 Een overzicht van de projecten (omschrijving) met per project het aantal projectleden, gesorteerd van
hoog naar laag aantal deelnemers.
 Een overzicht van de opleidingen met de projecten waarvoor ze verantwoordelijk zijn.
Ook opleidingen die voor geen enkel project verantwoordelijk zijn dienen in het overzicht opgenomen
te worden.
 Een overzicht van de opleidingen die verantwoordelijk zijn voor meer dan twee projecten met een duur
van meer dan zes weken.
(5 x 10 punten)
Bijlage conceptueel model:
Oplossing relationeel model:
Opleiding
oplcode*
omschrijving
Project
projcode*
omschrijving
opdgevercode
Student
Functie
oplcode
Opdrachtgever
studnr*
functie
duur
naam
studnr*
begindatum
opdgevercode*
oplcode
projcode*
einddatum
opdrachtgever
vervolgopproject
Query 1
SELECT
FROM
Project.omschrijving AS Expr1, Functie.studnr, Functie.functie, Student.oplcode
Functie INNER JOIN Project
ON Functie.projcode = Project.projcode INNER JOIN Student
ON Functie.studnr = Student.studnr
Query 2
SELECT
FROM
COUNT(DISTINCT studnr) AS aantal_studenten
Functie
Query 3
SELECT
FROM
Project.omschrijving, COUNT(Functie.studnr) AS aantal_studenten
Project INNER JOIN Functie
ON Project.projcode = Functie.projcode
GROUP BY Project.omschrijving
ORDER BY COUNT(Functie.studnr) DESC
Query 4
SELECT
FROM
Opleiding.omschrijving, Project.omschrijving
Opleiding LEFT OUTER JOIN Project
ON Opleiding.oplcode= Project.oplcode
Query 5
SELECT
FROM
Opleiding.omschrijving
Opleiding INNER JOIN
Project ON Opleiding.oplcode = Project.oplcode
WHERE (Project.duur > 6)
GROUP BY Opleiding.omschrijving
HAVING
(COUNT(Project.projcode) > 2)