Domeinmodellering/2010-11/Assignments/Opdracht01-B01/2 Opdrachtbeschrijving

Studietaak 01: over SQL: eenvoudige queries en complexere queries op één of méér tabellen met joins, subqueries e.d.

Achtergrond

Een belangrijke basis voor informatiesystemen is het onderliggende database-(management)systeem (DBS). De tegenwoordig gebruikte database-systemen zijn bijna steeds van het zogenaamde relationele soort. De bij die relationele database-managementsystemen RDBMS behorende standaardtaal voor gegevensmanipulatie is SQL. Als je later met het bouwen van informatiesystemen te maken krijgt, zul je daarbij vrijwel altijd een stevige kennis van de mogelijkheden en het gebruik van SQL moeten hebben.

Via deze studietaak maak je een aanzet voor het opbouwen van die SQL-kennis.

Leerdoelen

Na afloop van deze studietaak kun je:

• aangeven waaraan een database moet voldoen om van het ‘relationele soort’ te zijn;
• SQL-queries opstellen voor het opvragen van al dan niet afgeleide en/of gegroepeerde gegevens uit een enkele gegevenstabel van een RDB of uit een koppeling van meerdere tabellen via een join- (samenvoeging) en/of een subquery-wijze;
• aangeven waarom je hierbij in sommige situaties alléén een join-constructie kunt gebruiken, terwijl dat in andere situaties per se een subquery-constructie moet zijn.

Niveau: we passen 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: geen.

Voorbereiding:

A) het elektronisch ter beschikking gestelde collegedictaatdeel over SQL tref je in Blackboard aan (zie Course Materials/Documents/Block B/Lecture Notes Block B).

B) installeer op je eigen pc het Firebird-RDBMS, een ermee samenwerkende client en een er door benaderbare versie van de presidentiële database (zie de informatie hierover op Blackboard; eventueel ook voor andere versies van de presidentiële database, zoals voor MSAccess, e.d.).

C) volg in de DM-werkplaats de 'Werkplaats instructies' om (als je dat nog niet eerder gedaan hebt) allereerst een 'jaarpagina' aan te maken. Vervolgens zul je voor deze eerste opdracht twéé bijdragen moeten aanmaken: eentje waar je je uitwerking van deze eerste opdracht inzet (in Speciaal:Mypage/2010-11/Domeinmodellering/Opdracht01-B01/Opdracht ) en eentje waarin je je reflectie erop plaatst (in Speciaal:Mypage/2010-11/Domeinmodellering/Opdracht01-B01/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! Plaats -zoals aangegeven in de 'Werkplaats instructies'- de daar gegeven exacte code aan het begin van je 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.

Overzicht van de uit te voeren taken:

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

a) Bestudeer uit het elektronisch ter beschikking gestelde collegedictaat over SQL de hoofdstukken 1 t/m 7 (dus tot en met blz. 23). (2u)

b) Bestudeer ‘gelijktijdig’ je aantekeningen v/h college van maandag 13 september (p.m.)

c) Stel telkens een SQL-query op die antwoord geeft op de volgende vragen en test de querie uit: (¼ u)

I) Op welke leeftijd overleed de langstlevende president van de USA?

II) Hoeveel presidenten werden in totaal geboren in de staten California, Texas, Illinois of Georgia?

e) Probeer al een eerste deel van de verderop bij ‘In te leveren uitwerkingen’ gevraagde (in te leveren) SQL-queries op te stellen en uit te testen. ( ½ u)

f) Stel, mede naar aanleiding van je ervaringen/problemen/invallen bij het bedenken en uittesten van de hiervoor gestelde queries en de bestudeerde stof, een lijstje op van punten die je op het college van donderdag 16 september ter sprake wilt brengen.

Taak 2. Voer ná het college van donderdag 16 september 2010 de volgende deeltaken uit:

g) Bestudeer uit het elektronisch collegedictaat over SQL de hoofdstukken 8 t/m 10.1.8 (dus tot en met blz. 47). (2u)

h) Maak vóór uiterlijk maandag 20/09/2010 vóór 10:00u (inlever-deadline!) de resterende van de hieronder bij ‘In te leveren uitwerkingen’ aangeduide te maken vragen + queries en test ze uit. (2 u)

i) Stel, mede naar aanleiding van je ervaringen/problemen/invallen bij het bedenken en uittesten van deze queries, een lijstje op van punten die je op het werkgroepcollege van maandag 20/09/2010 wilt inbrengen.

Let op: er zijn dan op die maandag twéé aparte werk(groep)colleges ; van 13:45-14:30u voor de Informatica-studenten en van 14:45-15:30u voor Informatiekunde-studenten. Je bent op deze werk(groep)colleges 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. (N.B. op die afdruk hoeven geen resultaattabellen te staan.)

In te leveren uitwerkingen

a) Geef allereerst antwoorden op de volgende vragen:

i) verzin zelf een vraag op de presidentiële database, waarbij een verschillend gebruik van haakjes (..) binnen het WHERE-deel van een query tot verschillende antwoorden leidt;

ii) verklaar waarom je wel/niet een verschil in uitvoerresultaat verwacht als je aan de ‘kale query’ SELECT * FROM PRESIDENT al dan niet de WHERE clause toevoegt:

   WHERE  death_age=90  OR  ( death_age <> 90  OR  death_age IS NULL )
			[geen verschil want .............. (en dus leveren beide 39 rows)]

b) Stel steeds een geschikte SQL-query op, die correct antwoord geeft op de hiernavolgende informatie/gegevensvragen en 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).

==> Allereerst: test geformuleerde SQL-queries steeds goed uit, vóórdat je ze inlevert!

Bij deze gegevensvragen kan het zijn dat er meerdere oplossingen zijn en dat je die moet geven. Indien je bijvoorbeeld als mogelijkheden vindt: ‘oplossen via een dubbele join (van 3 tabellen)’, ‘via een enkele join en een subquery’ en ‘via 2 subqueries’, dan valt de eerste mogelijkheid onder ‘zoveel mogelijk met een join-constructie’ en de laatste onder ‘zoveel mogelijk met een subquery-constructie’. Indien het nìet mogelijk is om zowel via een join- als via een subquery-constructie een oplossing te vinden, geef dan aan waarom dat nìet kan.

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 resultaattabellen door 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 >'.

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

1. Which presidents were born before 1800 in one of the states ‘New York’ or ‘Virginia’? Show their names and also their native state and birth year.

2. Which presidents got in at least one marriage (or in both marriages separate!) more than 5 children? Show their names, spouse-names, marriage-year and number of children of such a marriage.

3. Show the hobbies (with their names) of the presidents Eisenhower and Truman.

4. Give in one result table the overall number of presidents, the sum of their birth years, that sum divided by their number, and finally their average birth year. (Did you expect the relation between the results? Why?)

5. Show an ordered list with for each value of ‘admin_entered’ that ‘administration number’ and the number of states that entered the USA during that administration. The ordering must be with decreasing numbers of states. Explain the meaning of the first result row.

6. Give a list with for each hobby, practised by more than 3 presidents, that hobby and the numbers of presidents that practised that hobby. For this list ‘Poker’ and ‘Bridge’ may not be considered as accepted hobbies. Order the list according to a decreasing number of presidents.

7. Show the name, native state and hobbies of all ‘Republican’ presidents that are still alive.

8. Give for the presidents that married more than once a list with their name, native state and the total number of children out of their various marriages.

9. Give for the election year 1800 a list with for each participating candidate, his name, the number of his votes and his percentage related to the total number of votes in that year.

Try to establish SQL-queries as well (‘as far as possible’) with a ‘join’- as with a subquery-construction, to give correct answers to the next information demands on the presidential database (and test your queries...!).
If it is not possible to use one of these constructions, explain why you can not use it!

10. From which states came presidents that never participated as candidate in elections? Show the names of those states and also from those presidents.

a) with (as far as possible) a join-construction: ?

b) with (as far as possible) a subquery-construction: ?

11. Give for the president that was born in the same year as the state ‘Minnesota’ entered in the USA, his name, his native state, and the name of his spouse.

a) with (as far as possible) a join-construction: ?

b) with (as far as possible) a subquery-construction: ?

12. In which year did the state, where the presidential husband of Mrs. ´Smith M M´ was born, enter the USA?

a) with (as far as possible) a join-construction: ?

b) with (as far as possible) a subquery-construction: ?

13. Which vice-presidents that participated in elections to become a ‘real’ president have never been president? Take care that each name appears just once in the result table.

a) with (as far as possible) a join-construction: ?

b) with (as far as possible) a subquery-construction: ?

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 wordt gebruikt, 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 Refelectie-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 20 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!

Deadline inleveren

Deadline voor het inleveren: maandag 20/09/10 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 je uitwerking-deel als van je reflectie-deel als entreebewijs mee naar het werkcollege. (N.B. op die afdruk hoeven geen resultaattabellen te staan.)