Oefeningen ERD – normalisatie Oefening: De filmadministratie Je wenst een eigen administratie op te stellen voor het verwerken van filmgegevens. Bij elke film hoort de lijst met acteurs en het personage dat zij in de film speelden. Bedoeling is ook de gegevens over de volledige filmcrew te verwerken. De filmcrew bestaat uit de regisseur, cameramensen, ... Uiteindelijk wil je volgende overzicht kunnen afdrukken: FilmID: Titel: Productiehuis: Release Date: Korte inhoud: Main Cast:
MPAA rating: CrewID
1999/57 Bicentennial Man Touchstone Pictures 17/12/99 In the first decade of the new millenium, .... Acteur Robin Williams Sam Neill Ioliver Platt ... PG
Personage Andrew Martin Sir Rupert Burns
Description Name 1 Director: Chris Columbus 2 Screen writer Nick Kazan 3 Special Effects Steve Johnson 4 Producer Mark Radcliffe 5 Costume Designer Joseph G. Aulisi
Teken het ERD Begrippen: Entiteit: ■ Crew ■ Film ■ Auteur ■ Productiehuis Attribuut: ■ Inhoud ■ Titel ■ Datum
1
Productiehuis
Film
Personage
Cast
Crew details
Acteur
Crew
Functie
Normaliseer het rapport 0NF Film = (FilmID, Titel, ProductiehuisID, Productiehuis, ReleaseDate, KorteInhoud, {ActeurID,Acteurvoornaam, Acteurfamilienaam, Personnage}, Rating, {CrewID, CrewVoornaam, Crewfamilienaam, Functie}) 1NF Film =
(FilmID, Titel, ProductiehuisID, Productiehuis, ReleaseDate, KorteInhoud, Rating) Cast = (FilmID, ActeurID, ActeurVoornaam, ActeurFamilienaam, Personage) CrewDetails = (FilmID, CrewID, Crewvoornaam, CrewFamilienaam, Fucntie) 2NF → Elke afhankelijkheid afsplitsen: enkel kijken naar tabellen met samengestelde sleutel Film = (FilmID, Titel, ProductiehuisID, Productiehuis, ReleaseDate, KorteInhoud, Rating) Cast = (FilmID, ActeurID, Personage) Acteur = (ActeurID, ActeurVoornaam, ActeurFamilienaam) CrewDetails = (FilmID, CrewID, functie) Crew = (CrewID, CrewVoornaam, CrewAchternaam) 3NF Fim = (FilmID, Titel, ProductiehuisID, ReleaseDate, KorteInhoud, Rating) Productiehuis = (ProductiehuisID, Productiehuis) Cast = (FilmID, ActeurID, Personage) Acteur= (ActeurID, ActeurVoornaam, ActeurFamilienaam) CrewDetails = (FilmID, CrewID, functie) Crew = (CrewID, CrewVoornaam, CrewAchternaam) EXTRA VRAAG:
2
Wij zijn er bij opgave van uitgegaan dat iemand van de technische ploeg slechts 1 functie kan uitoefenen. Stel welke gevolgen heeft het voor organisatie en voor het ERD als iemand van de technische ploeg meerdere functies zou hebben.
Oefening: het Songfestival Stel dat je al jaren fan bent van het songfestival en dat je alle gegevens hierover bijhoudt in een database. Van elk festival noteer je telkens de plaats (land en stad) waar het wordt georganiseerd en de datum wanneer het plaatsvindt. Je houdt ook in detail bij welke scores de deelnemende artiesten verkregen van de verschillende landen. Je kan dan ook het volgende overzicht afdrukken voor elk songfestival ! Je moet dus uit je databank informatie kunnen halen zoals hoeveel keer een land heeft meegedaan, de totaalscore van land in een bepaald jaar, … Normaliseer het onderstaande rapport: Songfestival: 2003 Plaats: Riga, Letland Datum: 24/mei Land: Artiest: Lied:
Turkije Hannelore Casteels Ik ben vandaag zo vrolijk Score overzicht punten voorronde Land: Punten België 12 Nederland 10 Rusland 10 … Totaal 167 Score finale Land: Frankrijk Duitsland Rusland … Totaal
Land: Artiest: Lied:
Punten 12 10 9 134
België Hannelore Casteels Senior Sanomi memeee amimameeemamooeeeeee Score overzicht punten voorronde Land: Punten Turkije 8 Nederland 5 Rusland 12 … Totaal 165 Score finale 3
Land: Duitsland Rusland Nederland … Totaal
Punten 10 9 12 109
Mogelijke entiteiten: Land, Songfestival, Artiest Attributen: Datum, Totaal, Plaats, Titelsong, Score
Teken het ERD Land Scores voorronde
Songfestival
Score land
Song
Artiest
Score finale Deelnemend land
Herhalende groepen: Song (?), ScoresVoorronde, ScoreFinale
Normaliseer het rapport ???
Oefening: Het sponsorbureau Je hebt een bureau dat sponsoring zoekt voor projecten van klanten. Je ontwerpt zelf een database voor het verwerken van je klantengegevens met hun projecten en zorgt ervoor dat je een overzicht kan drukken van alle sponsorgelden die je hebt verzameld. Alle anonieme giften worden onder de persoon anoniem ondergebracht. Hierdoor kan een persoon (bv “anoniem”) meerdere keren sponsorgeld storten. Ook personen waarvan de naam is gekend, kunnen meerdere keren sponsorgeld storten. Van elke storting aan sponsorgeld moet steeds het bedrag en de datum van de storting worden bijgehouden. Je organiseert in overleg met de klant ook evenementen om aan sponsoring te komen. Er zijn wel kosten verbonden aan dergelijke evenementen. Dus per evenement moet je een overzicht kunnen geven van de gemaakte kosten.
4
Teken het ERD Netto-opbrengst en tekort op de winst gaan we niet opnemen in de normalisatie
5
Klant
Project
Omschrijving Vereist bedrag
Bedrag
Evenement
Sponsoring
Datum
Inkomsten
UitgavePost Sponsor Omschrijving
Bedrag
Sponsoring
Normaliseer het rapport 0NF Project = (ProjectID, Project, KlantID, Vereniging, Adres, Postcode, Gemeente, VereistBedrag, {SponsorID, Voornaam, Familienaam, SponsorAdres, SponsorPostcode, SponsorGemeente, Bedrag, Datum}, {EvenementID, Evenement, EvenementDatum, {Uitgave, UitgaveBedrag}, Inkomsten}) 1NF Project =
(ProjectID, Project, KlantID, Vereniging, Adres, Postcode, Gemeente, VereistBedrag) Sponsoring = (ProjectID, SponsorID, Voornaam, Familienaam, SponsorAdres, SponsorPostcode, SponsorGemeente, Bedrag, Datum) Evenement = (ProjectID, EvenementID, Evenement, EvenementDatum, Inkomsten) → ProjectID hier geen sleutel omdat je anders niet weet bij welk project het evenement hoort EvenementID = enkelvoudige sleutel Uitgave = (EvenementID, UitgaveID, Uitgave, UitgaveBedrag) → EvenementID is geen sleutel UitgaveID = enkelvoudige sleutel ProjectID bij de voorlaatste en EvenementID bij de laatste zijn geen sleutels omdat we er vanuit gaan dat een evenement / uitgave sowieso bij 1 project / evenement hoort → dus moet je niet meer expliciet vermelden, maar moet wel opgenomen worden in de tabel om de relatie met de vorige tabel aan te duiden 2NF Project =
(ProjectID, Project, KlantID, Vereniging, Adres, Postcode, Gemeente, VereistBedrag) Sponsoring = (ProjectID, SponsorID, Bedrag, Datum) Sponsor = (SponsorID, Voornaam, Familienaam, SponsorAdres, SponsorPostcode, SponsorGemeente) 6
Evenement = (ProjectID, EvenementID, Evenement, EvenementDatum, Inkomsten) Uitgave = (EvenementID, UitgaveID, Uitgave, UitgaveBedrag) 3NF Project = (ProjectID, Project, KlantID, VereistBedrag) Klant = (KlantID, Vereniging, Adres, Postcode) Gemeente = (Postcode, Gemeente) Sponsoring = (ProjectID, SponsorID, Bedrag, Datum) Sponsor = (SponsorID, Voornaam, Familienaam, SponsorAdres, SponsorPostcode) SponsorGemeente = (SponsorPostcode, SponsorGemeente) → Want wordt al geheel vermeld in entiteit Gemeente Evenement = (ProjectID, EvenementID, Evenement, EvenementDatum, Inkomsten) Uitgave = (EvenementID, UitgaveID, Uitgave, UitgaveBedrag)
Oefening: De elektronische boekhandel Een grote boekhandel beslist om een electronic bookshop te creëren via hun website. Bezoekers van de website krijgen een elektronische caddy. Telkens zij een boek wensen te kopen, nemen zij dit boek op in hun caddy. Bij het afsluiten van de aankopen, moet de klant zijn persoonlijke gegevens opgeven. Bestaande klanten vullen hiervoor hun klantnummer in waarna zijn persoonlijke gegevens worden opgehaald. Nieuwe klanten moeten in een apart formulier hun gegevens invullen, waarna een klantnummer wordt aangemaakt. Elke klant moet het nummer van zijn kredietkaart ingeven vooraleer de aankoop kan worden bevestigd. Een klant kan in principe bij elke aankoop een verschillende kredietkaart opgeven. Na het bevestigen van de caddy krijgt de klant volgende overzicht van zijn aankopen:
Teken het ERD
7
De nummer van het kredietkaart moet elke keer opnieuw opgegeven worden, dus dit nummer wordt niet verbonden met de klantgegevens Begrippen: - Caddy - Customer - Book Klant
Caddy
Creditcard Number
Caddy Content
Book Totaal boek → kan je opnemen in je ERD, maar dit is niet noodzakelijk. Het is een berekend attribuut In cirkel met stippellijnen aan 'CaddyContent'
Normaliseer het rapport 0NF Caddy =
(CaddyNumber, Date, CustomerNumber, Voornaam, Familienaam, Address, Postalcode, Gemeente, CreditcardNumber, {ISBN, Title, {AuteurID, AuteurVoornaam, AuteurFamilienaam}, Quantity, Price})
1NF Voorbereidende stap: Caddy = (CaddyNumber, Date, CustomerNumber, Voornaam, Familienaam, Address, Postalcode, Gemeente, CreditcardNumber) CaddyContent = (CaddyNumber, ISBN, Title, {AuteurID, AuteurVoornaam, AuteurFamilienaam}, Quantity, Price) Volledig uitgewerkt: Caddy = (CaddyNumber, Date, CustomerNumber, Voornaam, Familienaam, Address, Postalcode, Gemeente, CreditcardNumber) CaddyContent = (CaddyNumber, ISBN, Title, Quantity, Price) → samengestelde sleutel Auteursteam = (ISBN, AuteurID, AuteurVoornaam, AuteurFamilienaam) → samengestelde sleutel 2NF Caddy =
(CaddyNumber, Date, CustomerNumber, Voornaam, Familienaam, Address, Postalcode, Gemeente, CreditcardNumber) CaddyContent = (CaddyNumber, ISBN, Quantity)
8
Book = (ISBN, Title, Price) Auteursteam = (ISBN, AuteurID) Auteur = (AuteurID, AuteurVoornaam, AuteurFamilienaam) 3NF Caddy = (CaddyNumber, Date, CustomerNumber, CreditcardNumber) Customer = (CustomerNumber, Voornaam, Familienaam, Address, Postalcode, Gemeente) CaddyContent = (CaddyNumber, ISBN, Quantity) Book = (ISBN, Title, Price) Auteursteam = (ISBN, AuteurID) Auteur = (AuteurID, AuteurVoornaam, AuteurFamilienaam) Gemeente = (Postalcode, Gemeente)
Oefening: De souvenierverkoop Een bedrijfje dat souveniers verkoopt, heeft niet alleen een winkel maar heeft ook een aantal verkopers in dienst die met hun souvenierkarretje erop uittrekken om souveniers te verkopen. Elke verkoper kiest elke week (van maandag tot zondag) een locatie uit waar hij de hele week blijft staan. De prijs van een souvenier kan verschillend zijn, afhankelijk van de plaats waar het souvenierkarretje is opgesteld (of met andere woorden: afhankelijk van de plaats waar het wordt verkocht). Op eenzelfde plaats geldt echter wel steeds dezelfde prijs voor dat souvenier, ongeacht de verkoper. Op het einde van elke dag brengt elke verkoper een overzicht binnen wat die dag precies werd verkocht. Normaliseer het volgende overzicht (voorzie extra velden voor de sleutels):
Aard van het rapport gaat een goede hint geven van de herhalende groepen
Teken het ERD Begrippen: - Verkoper - Plaats - Weeknummer - Artikel - Plaats en artikel bepalen prijs 9
Datum
PlaatsID
Verkoper
Plaats Verkoop Locatie
PlaatsID
VerkoperID
VerkoperID Verkoper DetailID
Aanbod
PlaatsID ArtikelID
Prijs
Verkoop Detail
ArtikelID
Artikel
Datum
1°Als men een veel-op-veelrelatie wil oplossen in ERD → altijd samengestelde sleutel in uw normalisatie 2°Als er meer dan 3 attributen zijn, zoals bij VerkoopDetail dan kan je 1 uniek attribuut maken, bv VerkoopDetailID. De 3 of meer andere attributen worden dan niet gebruikt in de normalisatie → vereenvoudiging Hier: VerkoperID, PlaatsID, ArtikelID en Datum worden vervangen door VerkoopDetailID
Normaliseer het rapport 0NF Verkoper = (VerkoperID, Voornaam, Familienaam, {PlaatsID, Plaats, Datum, {ArtikelID, Artikel, Prijs, Aantal}}) 1NF → Zorgen dat er geen herhalende groepen meer zijn Verkoper = (VerkoperID, Voornaam, Familienaam) VerkoopLocatie = (VerkoperID, PlaatsID, Plaats, Datum) → Datum onderstreept: 1 verkoper op 1 datum op 1 plaats VerkoperDetail = (VerkoperID, PlaatsID, Datum, ArtikelID, Artikel, Prijs, Aantal) 2NF → Niet-sleutelvelden onafhankelijk maken Verkoper = (VerkoperID, Voornaam, Familienaam) VerkoopLocatie = (VerkoperID, PlaatsID, Datum) Plaats = (PlaatsID, Plaats) VerkoperDetail = (VerkoperID, PlaatsID, Datum, ArtikelID, Aantal) Artikel = (ArtikelID, Artikel) PrijsLocatie = (ArtikelID, PlaatsID, Prijs)
1
3NF = 2NF
Oefening: Het facturatiesysteem Sitautieschets: Een bedrijf verkoopt goederen aan haar klanten. Per order kan een klant meerdere producten bestellen. De prijzen van de goederen kunnen doorheen het jaar variëren, maar natuurlijk wil het bedrijf op het einde van het jaar de juiste omzet kunnen berekenen. De orders van de klanten worden niet in één keer naar de klant verzonden. Als een klant bv. 5 stuks van product X besteld en 7 stuks van product Y, dan is het mogelijk dat er eerst een verzending en facturatie gebeurt van 3 stuks van product X en 4 stuks van product Y en nadien een tweede verzending en facturatie van de overblijvende goederen. Bij één order kunnen dus meerdere facturen/verzendingen horen. Een factuur/verzending is natuurlijk gekoppeld aan één order. Op elke factuur wordt steeds nauwkeurig afgedrukt welke en hoeveel producten er worden gefactureerd. Het is ook mogelijk dat bij een order nog geen enkele verzending en facturatie is gebeurd.
Teken het ERD voor deze situatie. Oefening: De consultants De firma ABC Consultants verhuurt haar werknemers aan klanten waar zij meewerken aan projecten. Elk project krijgt een unieke identificatiecode. Een medewerker kan in dezelfde periode tegelijkertijd aan meerdere projecten bij verschillende klanten meewerken. Per project wordt een kilometervergoeding vastgelegd (aantal km + bedrag per km) dat geldig is voor alle werknemers die aan het betreffende project meewerken. Indien een werknemer niet bij de klant aan het project werkt, maar bij de firma ABC Consultants, worden 0 km opgegeven. Om de facturen te kunnen maken, moet elke medewerker een time sheet bijhouden. Hierbij noteert hij per dag aan welke projecten hij heeft gewerkt. De medewerkers noteren dit telkens per dagdeel (DAG = volledige dag, VM = voormiddag, NM= namiddag). Teken het ERD Normaliseer het rapport
1
Oefening: oogartsenpraktijk Een privépraktijk van oogartsen heeft verschillende mensen in dienst: oogartsen, medische assistenten, administratief personeel. Het administratief personeel ontvangt de patiënten, noteert afspraken, noteert de gegevens van nieuwe patiënten en wijst ze toe aan een oogarts. De medische assistenten voeren een aantal onderzoeken uit (bv. oogdrukmeting, meting gezichtsveld, …). Van elk personeelslid worden naast naam en adresgegevens ook de behaalde diploma’s genoteerd. Een personeelslid kan meerdere diploma’s hebben behaald. Van elke patiënt worden de persoonlijke gegevens genoteerd (naam, adresgegevens, geboortedatum). Elke patiënt krijgt ook een oogarts toegewezen. Patiënten komen op consultatie na vooraf gemaakte afspraken. Van elke afspraak wordt datum en uur genoteerd. Tevens wordt op dat moment meteen aangeduid welke onderzoeken er moeten worden uitgevoerd. Bij sommige patiënten is dit alleen ‘consultatie oogarts’, maar bij andere patiënten kan men ook aanduiden dat er tevens een oogdrukmeting en een onderzoek gezichtsveld moeten worden uitgevoerd. Het informatiesysteem bevat dus een lijst van alle mogelijke types onderzoeken. Niet alle medische assistenten kunnen alle onderzoeken uitvoeren. Het informatiesysteem moet kunnen bijhouden wie welke onderzoeken mag uitvoeren. Oogartsen kunnen uiteraard alle onderzoeken uitvoeren. Het administratief personeel mag geen enkel onderzoek uitvoeren. Zodra een patiënt zich voor een afspraak heeft aangeboden, duidt men aan dat de patiënt gekomen is. Door dit in apart aan te duiden, kan men later nagaan welke patiënten zich niet aan de afspraken houden. Men kan ze dan nadien eventueel contacteren. Telkens een patiënt zich aanbiedt op een afspraak ziet men ook meteen welke onderzoeken er moeten worden uitgevoerd. Indien er naast het standaard ‘consultatie oogarts’ nog andere onderzoeken moeten worden uitgevoerd, dan zullen de medische assistenten de patiënt ophalen uit de wachtkamer naargelang de onderzoeken die zij mogen uitvoeren. Elke medische assistent noteert steeds de uitslag van het onderzoek. Het informatiesysteem registreert automatisch welk
1
personeelslid het onderzoek heeft uitgevoerd. Ook bij het standaard ‘consultatie oogarts’ wordt de oogarts automatisch genoteerd. De reden hiervoor is dat men op die manier kan nagaan welke patiënten niet behandeld zijn geweest door zijn normale oogarts. Bij ziekte van een oogarts probeert men immers zijn patiënten te verwittigen en een nieuwe afspraak te maken. Bij dringende dossiers zal men trachten de patiënt door een andere oogarts te laten behandelen. Zodra alle voorbereidende onderzoeken zijn uitgevoerd, zal de patiënt door zijn oogarts in de wachtkamer worden opgehaald voor de consultatie. Ook de oogarts noteert de uitslag van zijn onderzoek. Teken het ERD voor dit systeem en neem de belangrijkste attributen erin op zoals je ze uit deze beschrijving kunt afleiden.
Oefening: De masterproeven Je bent aangesteld als coördinator van de meesterproeven. Je wilt hiervoor een database ontwerpen waarmee je de voorstellen en de verdedigingen van de meesterproeven kunt verwerken. Elke meesterproef heeft een promotor. Voor de verdediging moet je twee assessoren aanduiden en een datum, uur en lokaal vastleggen. Een personeelslid kan promotor zijn voor de ene meesterproef en assessor voor een andere meesterproef. Elk lokaal krijgt een lokaalNr of naam en is op een bepaalde campus gelegen. Het is de bedoeling om in je database ook de specificaties (aanwezig materiaal, aantal zitplaatsen, …) te kunnen verwerken. Van deze campus is het adres volledig gekend. Het is zeker de bedoeling om lijsten zoals het onderstaande rapport te kunnen afdrukken per academiejaar. Dus ook gegevens van voorgaande academiejaren moeten kunnen worden afgedrukt. Je wenst de nodige rapporten te kunnen afdrukken: overzichten verdedigingen per dag, per docent, per lokaal, ….
1
Normaliseer het bovenstaande rapport
Oefening: De wijnhandelaar Je wordt in België wijnverdeler van Sloveense wijn. Je wenst hiervoor een database te ontwerpen waarin je alle gegevens van de sloveense wijnen en hun wijnbouwers kunt verwerken. Normaliseer de gegevens vanhet onderstaande rapport.Let op: een wijnsoort kan natuurlijk door meerdere wijnhuizen worden verkocht. Voorzie de nodige sleutels.
De kamporganisatie Deel 1: ERD Een jeugdorganisatie organiseert elk jaar een zomerkamp. De organisatie heeft de beschikking over een terrein met verschillende blokhutten waarin telkens een 15-tal
1
deelnemers kunnen overnachten. Elke deelnemer wordt aan een blokhut toegewezen. Binnen de groep 'kampeerders' van een blokhut is er steeds een 'blokhutleider' die toezicht houdt. Er worden verschillende wedstrijden en activiteiten georganiseerd en alle leden van eenzelfde blokhut nemen telkens allemaal deel van een activiteit. Aan het begin van het zomerkamp beslissen alle deelnemers van een blokhut aan welke zij deelnemen. Het is niet de bedoeling dat de jeugdorganisatie ba het zomerkamp de gegevens (wie in welke blokhut zat of de activiteiten waaraan een blokhut deelnam) bijhoudt. Teken het ERD en geef de belangrijkste attributen. - Blokhut - Deelname - Leider: ja/nee veld - Activiteiten Als je 1 deelnemer mist, klopt je databank niet meer → veel-op-veel relatie tussen deelnemer en activiteit deze relatie dus niet tekenen
Blokhut
Deelname
Activiteit
Deelnemer
Leider
Herhalende groep samenstelling
Herhalende groep van deelnemende activiteiten
Deel 2: Normalisatie Normaliseer het volgende rapport:
1
0NV Blokhut =
(BlokhutID, Omschrijving, {DeelnemerID, Naam, Voornaam, Geboortedatum, Leider}, {Datum, ActiviteitID, OmschrijvingActiviteit, Bedrag, Score}) → 1ste herhalende groep: Deelnemers → 2de herhalende groep: DeelnemersActiviteit
1NV Blokhut = (BlokhutID, Omschrijving) Deelnemers = (BlokhutID, DeelnemerID, Naam, Voornaam, Geboortedatum, Leider) DeelnameActiviteit = (BlokhutID, Datum, ActiviteitID, OmschrijvingActiviteit, Bedrag, Score) 2NV Blokhut = (BlokhutID, Omschrijving) Deelnemers = (BlokhutID, DeelnemerID, Naam, Voornaam, Geboortedatum, Leider) DeelnameActiviteit = (BlokhutID, ActiviteitID, Score) Activiteiten = (ActiviteitID, Datum, OmschrijvingActiviteit, Bedrag) 3NV = 2NV Deel 3: SQL De volgende database bevat de gegevens van de organisatoren van een zomerkamp. De deelnemers zijn ondergebracht in blokhutten, die aan activiteiten deelnemen. Alle deelnemers van een blokhut nemen steeds deel aan dezelfde activiteiten. Per blokhut is er een blokhutleider. De databank bevat gegevens van 1 zomerkamp.
1
Betekenis van de tabellen: - tblDeelnemers deelnemers aan het zomerkamp - tblBlokhutten de verschillende blokhutten die tijdens het zomerkamp gebruikt kunnen worden - tblBlokhutSamenstelling geeft aan welke deelnemer in welke blokhut logeert. Het veldje leider is een Yes/No veld dat aangeeft of die persoon de blokhutleider is - tblActiviteiten de verschillende activiteiten die worden georganiseerd - tblDeelname geeft per blokhut weer aan welke activiteiten de blokhut deelneemt Geef de SQL statements om volgende informatie uit de database te halen; benoem steeds eventueel berekende gegevens:
Blokhut
Deelname BlokhutID
Blokhut samenstelling
BlokhutID
Activiteit
DeelnemerI D
Deelnemer
Jaar DeelnemerI D
1
1. Welke blokhutten zijn tijdens het zomerkamp niet in gebruik? Geef enkel de omschrijving van deze blokhutten. Eerste stap: welke blokhutten zijn wel in gebruik? SELECT BlokhutID FROM tblBlokhutten INNER JOIN tblBlokhutSamenstelling ON tblBlokhutten.BlokhutID = tblBlokhutSamenstelling.BlokhutID → Subquery Tweede stap: selecteer alle blokhutten die niet in de voorgaande lijst voorkomen SELECT Omschrijving FROM tblBlokhutten WHERE BlokhutID NOT IN (SELECT BlokhutID FROM tblBlokhutten INNER JOIN tblBlokhutSamenstelling ON tblBlokhutten.BlokhutID = tblBlokhutSamenstelling.BlokhutID) 2. Voor sommige activiteiten moeten de deelnemers een extra bijdrage betalen (veldje Bedrag). Wat is de totale som die de kamporganisator hiervoor intvangt? (Tip: maakt eerst een query die het aantal deelnemers levert per activiteit en het te betalen bedrag voor die activiteit) Query van de tip SELECT tblActiviteit.ActiviteitID, Count(DeelnemerID) As AantalDeelnemers, Bedrag FROM (((tblActiviteiten INNER JOIN tblDeelname ON tblActiviteiten.ActiviteitenID = tblDeelname.ActiviteitID) INNER JOIN tblBlokhutten ON tblBlokhutten.BlokhutID = tblDeelname.BlokhutID) INNER JOIN tblBlokhutSamenstelling ON tblBlokhutten.BlokhutID = tblBlokhutSamenstelling.BlokhutID) GROUP BY tblActiviteit.ActiviteitID, Bedrag Oplossing: SELECT AantalDeelnemers * Bedrag AS TotaalBedrag FROM (SELECT tblActiviteit.ActiviteitID, Count(DeelnemerID) As AantalDeelnemers, Bedrag FROM (((tblActiviteiten INNER JOIN tblDeelname ON tblActiviteiten.ActiviteitenID = tblDeelname.ActiviteitID) INNER JOIN tblBlokhutten ON tblBlokhutten.BlokhutID = tblDeelname.BlokhutID) INNER JOIN tblBlokhutSamenstelling ON tblBlokhutten.BlokhutID = tblBlokhutSamenstelling.BlokhutID) GROUP BY tblActiviteit.ActiviteitID, Bedrag) 3. Wie is de jongste leider? Geef de naam als een lange string, en zijn geboortedatum Eerste stap: alle geboortedata verzamelen SELECT Geboortedatum FROM tblDeelnemers Jongste persoon = persoon met geboortedatum <= aan alle andere geboortedata
1
Oplossing: SELECT Naam & “ “ & Voornaam AS Deelnemer, Geboortedatum FROM tblDeelnemers WHERE Geboortedatum <= ALL (SELECT Geboortedatum FROM tblDeelnemers) OF: SELECT Naam & “ “ & Voornaam AS Deelnemer, Geboortedatum FROM tblDeelnemers WHERE Geboortedatum = (SELECT max(geboortedatum) FROM tblDeelnemers) 4. Aan welke activiteiten nemen meer dan 3 blokhutten deel? Geef de omschrijving van de blokhut en het aantal activiteiten Selecteren op een groepstotaal → HAVING-component SELECT Omschrijving, Count(BlokhutID) AS Aantal FROM tblActiviteiten INNER JOIN tblDeelname ON tblActiviteiten.ActiviteitID = tblDeelname.ActiviteitID GROUP BY Omschrijving HAVING Count(BlokhutID) > 3 5. Geef per blokhut een overzicht van de deelnemers (naam als een lange string), per blokhut in dalende volgorde van geboortedatum. De output toont dus deelnemer, de blokhut en de geboortedatum SELECT Omschrijving, Naam & “ “ & Voornaam AS Deelnemer, Geboortedatum FROM (tblBlokhutten INNER JOIN tblBlokhutSamenstelling ON tblBlokhut.BlokhutID = tblBlokhutSamenstelling.BlokhutID) INNER JOIN tblDeelnemers ON tblDeelnemers.DeelnemerID = tblBlokhutSamenstelling.DeelnemerID ORDER BY Omschrijving, Geboortedatum DESC → DESC = descending >< voor ascending moet je er niets bijzetten
De filmindustrie
1
FilmID: Titel: Productiehuis: Release Date: Korte inhoud:
1999/ 57 Bicentennial Man Touchstone Pictures 17/ 12/ 99 In the first decade of the new millenium, .... ... Acteur Personage Main Cast: Robin Williams Andrew Martin SamNeill Sir Ioliver Platt Rupert Burns ... MPAA rating: PG CrewID
Description Name 1 Director: Chris Columbus 2 Screen writer Nick Kazan 3 Special Effects Steve Johnson 4 Producer Mark Radcliffe 5 Costume Designer Joseph G. Aulisi ... ...
Geef de SQL-statements voor volgende vragen: 1. In hoeveel films heeft elke acteur meegespeeld? SELECT ActeurVoornaam & “ “ & ActeurFamilienaam AS Acteur, Count(FilmID) AS Aantal FROM Acteur INNER JOIN FilmPersonnages ON Acteur.ActeurID = Filmpersonnage.ActeurID GROUP BY ActeurVoornaam & “ “ & ActeurFamilienaam
2
2. Hebben we productiehuizen in onze databank waarvan we geen films hebben geregistreerd? SELECT Productiehuis → Veldnaam FROM Productiehuis → Tabelnaam WHERE ProductiehuisID NOT IN (SELECT ProductiehuisID FROM Film) 3. Hoeveel filmen hebben we met rating 'PG'? SELECT Count(FilmID) AS Aantal FROM Film WHERE Rating = “PG” 4. Welke films hebben meer dan 10 acteurs? SELECT Titel, Count(ActeurID) AS Aantal FROM Film INNER JOIN FilmPersonnages ON Film.FilmID = FilmPersonnages.FilmID GROUP BY Titel HAVING Count(ActeurID) >= 10 ORDER BY → sorteren en alle informatie tonen GROUP BY → sorteren en alles herleiden tot 1 lijntje per groep hij toont 5. Geef een volledig overzicht van de acteurs per film SELECT Titel, ActeurVoornaam & “ “ & ActeurFamilienaam AS Acteur FROM Film INNER JOIN FilmPersonnagse ON Film.FilmID = FilmPersonnages.FilmID) INNER JOIN Acteurs ON Acteurs.ActeurID = FilmPersonnages.ActeurID ORDER BY Titel, ActeurFamilienaam, ActeurVoornaam 6. Welke acteurs hebben slechts voor 1 regisseur (= functie binnen technische ploeg) gewerkt? SELECT ActeurVoornaam & “ “ & ActeurFamilienaam AS Acteur, Count(FunctieID) AS AantalRegisseurs FROM ((Acteur INNER JOIN Filmpersonnage ON FilmPersonnage.ActeurID = Acteur.ActeurID) INNER JOIN Film ON Film.FilmID = FilmPersonnages.FilmID) INNER JOIN TechnPloeg ON Film.FilmID = TechnPloeg.FilmID WHERE FunctieID = 4 GROUP BY ActeurVoornaam & “ “ & ActeurFamilienaam HAVING Count(FunctieID) = 1 7. Welke acteur heeft in 2009 in geen enkele film gespeeld?
2
SELECT ActeurVoornaam & “ “ & Familienaam AS Acteur FROM Acteur WHERE ActeurID NOT IN (SELECT ActeurID FROM FilmPersonnage INNER JOIN Film ON FilmPersonnage.FilmID = Film.FilmID WHERE Year(ReleaseDATe) = 2009)) 8. Welke functies komen meer dan 2 keer voor in eenzelfde film (geef ook de naam van deze film)? SELECT Functie, Titel, Count(FilmID) AS Aantal FROM (Film INNER JOIN TechnPloeg ON Film.FilmID = TechnPloeg.FilmID) INNER JOIN Functies ON TechnPloeg.FunctieID = Functies.FunctieID GROUP BY Functie, Titel HAVING Count(FilmID) >2 9. Welke acteurs beelden in welke films meer dan 1 personage uit? SELECT ActeurVoornaam & “ “ & Familienaam AS Acteur, Titel, Count(Personnage) AS Aantal FROM (Acteur INNER JOIN FilmPersonnage ON Acteur.ActeurID = FilmPersonnage.ActeurID) INNER JOIN Film ON FilmPersonnage.FilmID = Film.FilmID GROUP BY ActeurVoornaam & “ “ & Familienaam, Titel HAVING Count(Personnage) >1 10. Welke film werd het eerst uitgebracht? SELECT Titel FROM Film WHERE ReleaseDate <= ALL (SELECT ReleaseDate FROM Film)
2
