Domeinmodellering/2010-11/Assignments/Opdracht02-B02/2 Opdrachtbeschrijving

Studietaak 02: over SQL: complexere queries waarbij ook correlated subqueries, Union, View e.d. en een aantal 'pittige queries' die een terdege probleem-analyse vereisen

Achtergrond

Je hebt reeds gezien, dat de 'relationele' mogelijkheden van SQL vooral tot uiting komen bij het koppelen van gegevens, die in verschillende gegevenstabellen van een (relationele) database aanwezig zijn. Je hebt daarbij reeds gewerkt met de 'normale' samenvoeging (join) en subqueries.
In deze studietaak komen daar nog bij de 'wat moeilijkere query-mogelijkheden' van SQL, zoals het combineren van gegevens uit een tabel met (andere) gegevens uit diezelfde tabel, het koppelen van een conditie in een ('correlated') subquery met (gegevens uit de tabel van) de bijbehorende hoofdquery en tot slot: het werken met 'views'. Ook SQL92 komt ter sprake.
Naast een aantal oefenqueries over de nieuwe onderwerpen geven we je ook een aantal pittige gegevensvragen, waar je je hele SQL-kennis voor uit de kast zult moeten halen.

Leerdoelen

Na afloop van deze studietaak:

• kun je voor flink ingewikkelde gegevensvragen op de gegevens in een relationele database een SQL-query opstellen, die het gewenste resultaat oplevert;
• ben je na een probleem-analyse in staat op een correcte wijze de diverse bestaande SQL- combinatie-, selectie- en afleidingsmogelijkheden te gebruiken (zoals met groeperen, statistische functies, selectie van gewenste afzonderlijke tabelregels of groeperingsresultaten, joins, subqueries [al dan niet 'correlated'], unions en views toe te passen (en extra opvraagmogelijkheden die SQL92 biedt);
• moet je dus zèlf de wijze kunnen bepalen waarop je een antwoord kunt krijgen op 'complexe' gegevensvragen, waarvan van tevoren niet is aangegeven van welk soort ze zijn; je kunt er daarbij zelfs niet zeker van zijn (alhoewel ….) of er wel SQL-queries bestaan om correcte antwoorden te krijgen op alle gegevensvragen.

Niveau: je past deze constructies weliswaar toe op de gegevens van de 'presidentiële database', maar in principe moet je in staat zijn ze toe te passen op een willekeurige relationele database met een 'overzichtelijk aantal' (tot enkele tientallen) gegevenstabellen in die database.

Wat moet je doen?

Vereiste voorkennis: de materie van studietaak 01.

Voorbereiding:

A) Als je dat nog niet eerder gedaan hebt, volg dan in de DM-werkplaats de 'Werkplaats instructies' om allereerst een 'jaarpagina' aan te maken.

B) Vervolgens zul je voor deze eerste opdracht weer twéé bijdragen moeten aanmaken: eentje waar je je uitwerking van deze eerste opdracht inzet (in Speciaal:Mypage/2010-11/Domeinmodellering/Opdracht02-B02/Opdracht ) en eentje waarin je je reflectie erop plaatst (in Speciaal:Mypage/2010-11/Domeinmodellering/Opdracht02-B02/Reflectie ). Let er speciaal op, dat je helemaal op het einde van deze 'URL's' (dus achter enerzijds 'Opdracht' en anderzijds 'Reflectie' géén '/' backslash plaatst!

C) Plaats -zoals aangegeven in de 'Werkplaats instructies'- de daar gegeven exacte code aan het begin van de bijdrages.

Hint: het kan handig zijn om gelijktijdig meerdere tabbladen in je webbrowser te openen, waarbij je in het ene tabblad de opgaven hebt, in een ander je uitwerkingen intikt en eventueel een derde tabblad met daarin bijvoorbeeld de SQL-reader.

Hint: een gemakkelijke manier om zowel je queries als hun uitvoer op een 'normale', goed leesbare manier op het scherm te krijgen is, om ze binnen een werkplaatspagina tussen de (HTML-tag) '< pre >' en '< /pre >' te plaatsen (steeds zonder spaties achter/voor resp. '<' en '>' teken). Een andere manier om die afzonderlijke regels van SQL-queries en resultaat in de werkplaats als aparte regels te laten verschijnen is, door alle regels met een spatie te laten beginnen. Een mooie manier om je queries (met syntax-high-lighting) te laten weergeven is: plaats je gehele query tussen de tags '< source lang='sql' >' en '< /source >'.

Overzicht van de uit te voeren taken:

Taak 1. Voer vóór het college van a.s. donderdag 23 september 2010 (13:45u) de volgende deeltaken uit:

a) Als je fouten of missers had in je uitwerking van assignment 01, oefen die onderdelen dan nog eens goed, zodat je evt. toetsvragen over die materie wèl correct kun beantwoorden.

b) Bestudeer van het SQL collegedictaat blz. 48 t/m 56.

c) Stel een SQL-query op die antwoord geeft op de volgende vraag en test hem uit:

Toon de door presidenten meest beoefende hobby, samen met de namen van de presidenten die die hobby hadden.

d) Stel, mede naar aanleiding van je ervaringen/problemen/invallen bij het bedenken en uittesten van de hiervoor gestelde query, een lijstje op van punten die je op het college van donderdag 23 september 2010 ter sprake wilt brengen (ter verduidelijking ...).

Taak 2. Voer ná het college van a.s. donderdag 23 september 2010 de volgende studieactiviteiten uit:

g) bestudeer uit het SQL-college-dictaat blz. 57 t/m 80 en 85 t/m 88

h) formuleer SQL-opdrachten voor het vinden van correcte antwoorden op de verderop gestelde gegevensvragen (vermeld steeds eventuele realistische(!) aannames).

Waarschuwing: onderschat deze studietaak niet en wacht dus niet tot de laatste dag om eraan te beginnen!

==> Weer geldt: test geformuleerde SQL-queries steeds goed uit, vóórdat je ze inlevert.

Algemene hint:
Je mag aannemen, dat een gekozen president in het jaar[tal] volgend op het jaar[tal] van zijn verkiezing wordt geïnstalleerd (presidentsverkiezingen zijn [meestal?] in november en inauguraties in januari/februari). Een uitzondering hierop is de allereerste administratie geweest; waarbij 'George Washington' in 1789 bij de eerste presidentsverkiezingen werd gekozen en in datzelfde jaar werd geïnaugureerd.

Let op: er is op maandag 27 september 2010 maar één werk(groep)college: van 13:45-14:30u (zowel voor de Informatica- als voor de IK-studenten. Na afloop van dit werkcollege volgt een uur college over de stof van blok A (Modeling and Reasoning).
Weer geldt: je bent op het werk(groep)college alléén welkom als je serieus aan deze studietaak hebt gewerkt en je uitwerking (ook al heb je maar een deel van de opdracht succesvol kunnen oplossen) hebt ingeleverd en een print ervan meeneemt naar het werkcollege (op die afdruk hoeven geen resultaattabellen te staan).

In te leveren uitwerkingen

Formuleer nu steeds SQL-opdrachten voor het laten bepalen van correcte antwoorden op de volgende gegevensvragen (vermeld steeds eventuele realistische(!) aannames).
Neem in je antwoord behalve die query óók de resultaat-tabel mee (bewaar alles in ASCII-formaat, dus bijvoorbeeld niet opgemaakt als Word-document of Html-document, en plaats het geheel in het 'Opdracht' werkplaats-document).

N.B. we formuleren hieronder slechts gegevensvragen en geven géén garantie dat ze ook werkelijk via SQL-queries op te lossen zijn. Misschien zul je wel allereerst ‘trucjes’ moeten verzinnen (b.v. via het definiëren van een view) om tóch voor die gegevensvraag een oplossing te kunnen genereren.
Indien voor een correcte oplossing de keuze bestaat uit wèl of nìet gebruiken van een view-definitie, dan gaat onze voorkeur beslist uit naar de oplossing zònder view!

Establish SQL-queries to give correct answers to the next information demands on the presidential database (and test your queries...!):

1. Give for the presidents that had more than three hobbies, their name, their native state, their birth year and the number of their hobbies.
2. More than a dozen of states founded the USA when there still was no first administration. Give for the presidents, born in one of those founding states, their name, their native state and their death age. Order the results by state name.
3. Give for all presidents that still do not have a death age filled in, their names, their native state and -if they have:- their hobbies.
4. Give for those presidents that participated in elections between 1900 and 1950, their names, their number of hobbies and their number of participations in elections.
5. Give the names of those presidents and their spouse, that married in the same year as at least one other president married.
   a) with [as much as possible] join-constructions: ?
   b) with [as much as possible] subquery-constructions: ?
6. Give for every state the name and birth year of the first-born president out of that state. (So show: state name, president name and birth year.)
7. Give for every state which president had the highest death age of all the presidents that came out of that state. (So show president name, state name and that highest age at dying.)
8. Give a list with the names of all the states of the USA and the number of presidents born in that state. States where no presidents at all were born, must appear with ‘0’ in the list. Order by number of births, descending.
9. Give a result table for all presidents (each time their two names) who have exactly the same hobbies (so e.g. the ones who both like fishing, riding and swimming) and the number of their mutual hobbies.
10. Give a result table with a ranking of the presidents, according to the number of hobbies of each president, where the president with ranking 1 has the highest number of hobbies. Equal numbers result in equal ranking (see table below).

 Nr	pres_name	NumHob
 1	Roosevelt T	7
 2	Coolidge C	5
 2	Eisenhower D D	5
 4	Kennedy J F	3
 4	Truman H S	3

Resultaat:
Uitgeteste SQL-queries, die een bij de betreffende vraagstelling correct resultaat opleveren, inclusief de resultaat-tabel (suggestie: gebruik de Windows-klembord copy/paste-mogelijkheden!). Als beoordelingscriterium geldt, dat de SQL-query-commando's het gewenste resultaat opleveren en dat ze daarbij nìet reuze ìnefficiënt zijn.

==> Geef in het gekopieerde -bij deze opdracht behorende- werkplaatssjabloon bij de parameter 'Percentage' aan hoeveel procent -naar jouw eigen inschatting- van deze opdracht je correct gemaakt hebt. (dus bijvoorbeeld [tussen dubbele vierkante haken..] Percentage::80 ).
En geef bij de parameter 'Status' een van de volgende waarden aan: 'bezig', 'klaar', 'opgegeven', 'deels geslaagd'. (Dus bijvoorbeeld de default-waarde Status::bezig geeft aan dat je op dat moment nog bezig bent met het werken aan deze opdracht. Als je later er verder aan gewerkt hebt, pas dan die Status- en Percentage-waarde aan.) Je doet ons een groot plezier als je tijdens het werken de hier ingevulde waarden regelmatig ververst.
Vul voor de Studie-parameter een van de volgende waarden in: IC, IK, hbo-IC of hbo-IK (die laatste 2 mogelijkheden voor 'hbo-doorstromers'.

Reflectie / discussie

In dit deel (dat je bent begonnen via het tweede te kopiëren werkplaatssjabloon) geef je enerzijds kort aan hoe het er bij deze opdracht vanaf hebt gebracht en geef je anderzijds aan andere studenten een mogelijkheid om op een vraag van jou te reageren. Dit 'reflectie/discussie-onderdeel' is voor iedereen zichtbaar en kan door iedereen die een verbetering of aanvulling wil doorvoeren, aangepast worden. Dus als je twijfels hebt bij je eigen beheersing van de stof, vraag dan expliciet om reacties van anderen.

a) je reflectie
Beantwoord hier (kort) de volgende vragen:

• Wat heb je deze week geleerd in relatie tot de vooraan in deze opdracht geformuleerde leerdoelen? Heb je die bereikt?

• Hoe moeilijk vond je deze te bestuderen/verwerken stof?

• Heb je tips voor verbetering?

Uiteraard ben je van tevoren al vragen nagegaan als:

• Zijn de verkregen resultaattabellen wel correcte antwoorden op de informatievragen?

• Kunnen de opgestelde SQL-queries niet efficiënter geformuleerd worden?

b) je discussie-bijdrage/verzoek
Bij dit tweede deel (waar anderen op kunnen reageren) kan je bijdrage heel divers zijn. Het kan zijn dat je hier aan anderen vraagt je te helpen om een bepaalde 'theoretische' vraag te beantwoorden. Of het kan bijvoorbeeld zijn dat je zelf een in jouw ogen hééél moeilijke [of heel geniepige] gegevensvraag op de presidentiële database formuleert -die je gezien de leerdoelen van deze opdracht nu op zou moeten kunnen lossen- en dat je daarna een SQL-query formuleert [al dan niet bewust fout opgesteld] en dan aan anderen vraagt of je uitwerking correct is en zo niet om er commentaar op te leveren.

Het is hierbij uitdrukkelijk niet de bedoeling, dat je anderen vraagt hier een SQL-uitwerking van een oefenopgave uit het eerste deel te geven!

Vergeet niet om bij de uit het sjabloon gekopieerde 'variabelen' waarden in te vullen voor zowel 'Studie' als voor 'Remark' (de inhoud van die laatste parameter verschijnt in het Reflectie-overzicht' onder het kopje 'Korte opmerking van auteur zelf').

Kijk ook bij de 'reflectie/discussie-onderdelen' van anderen wat zij daar als vraag hebben gesteld of moeilijke gegevensvraag hebben bedacht en of je correcties in of aanvullingen bij hun uitwerking kunt aanbrengen.
We verwachten dat je bij de 'reflecties/discussies' van zeker 2 andere studenten een reactie/commentaar/suggestie geeft. Als je zo'n reactie hebt gegeven, dan wordt je naam toegevoegd in de kolom 'Met commentaar van..'.

Breng je commentaar.. als volgt aan: Bewerk de reflectie-pagina van je mede-student. Kopieer en plak onderstaande regel onder zijn/haar reflectie en vervang "Mijn commentaar" door je eigen commentaar en/of vragen.

 {{!|~~~|| Mijn commentaar }} 

En kijk uiteraard regelmatig bij je eigen 'reflectie/discussie' of je door anderen aangebrachte veranderingen begrijpt en/of ze correct zijn en reageer er desnoods op!

Op het werk(groep)college van maandag 27 september 2010 worden de ingeleverde opdrachten en eventueel gerezen vragen&problemen nabesproken. Zorg er daarom voor dat je eventuele problemen/onduidelijkheden, die je tegen gekomen bent bij het maken van het inleverdeel van deze opdracht, op een rijtje hebt staan, zodat als die knelpunten in de nabespreking niet al eerder ter tafel zijn komen, jij ze zèlf kunt aankaarten.
==> Een uitdraai van je (ingeleverde) uitwerkingen + refelectie geldt als entreebewijs bij dit werkcollege!

N.B. Indien je hebt gemerkt dat je toch wel grote problemen hebt met het opstellen van de hier gevraagde SQL-queries, help dan mee [eventueel voor een later tijdstip] met het maken van de Extra oefening zoals die bij deze opdracht is gegeven; je kunt daarbij dan ook je vragen en aarzelingen aangeven, waarop door anderen dan gereageerd kan worden. Het is de bedoeling dat degenen die 'goed zijn' in het opstellen van deze queries zich beperken tot het geven van suggesties/hulp/e.d. om je te helpen bij het maken van deze opdracht.

Deadline inleveren

Deadline voor het inleveren: maandag 27/09/2010 vóór 10:00u (zowel "queries + resultaat-tabellen" in de 'Uitwerkingen'-bijdrage als je reflectie/discussie in de 'Reflectie'-bijdrage) binnen de DM-werkplaats; vergeet niet om binnen je uitwerking zowel je naam alsook je studie en je studentnummer te vermelden.
Breng een uitdraai van zowel de queries uit je uitwerking-deel als van je reflectie-deel als entreebewijs mee naar het werkcollege.

N.B. Op maandag 27 september 2010 is er ‘s middags voor zowel de IC- als de IK-studenten slecht één gemeenschappelijk werk/responsiecollege (van 13:45-14:30u) ter nabespreking van deze opgaven. In het erop volgende uur (van 14:45-15:30u) wordt 'hoorcollege' gegeven over 'blok A'.

Op donderdag 30 september 2010 is er éérst een toets over het behandelde SQL-deel van blok B. Daarna is er weer ‘hoorcollege’ over blok A.