Demo 4
ER-kaavion muuntaminen relaatiotietokannaksi SQL:llä + SQL-kyselyjä

1. Vertaa edellisissä demoissa piirtämiäsi ER-kaavioita seuraaviin malliversioihin. Korjaa mahdolliset virheet omasta kaaviostasi. Pyydä ohjaajaa selittämään epäselvät kohdat.

   
   Yritys

   
   Yritys hieman optimoituna versiona

   
   Jalkapalloturnaus

2. Käynnistä Access ja luo uusi tietokanta nimeltään Yritys omaan hakemistoosi d:\omat\ alle.
3. Luo tietokantaan Yritys-kaavion mukaiset taulut ja niiden väliset viite-eheydet SQL-kieltä käyttäen. Käytä monistetta apunasi.
4. Pääset syöttämään SQL-komentoja valitsemalla Queries-välilehden ja sieltä valinnan New. New Query-ikkunasta valitse Design View. Sulje näytölle aukeava Show Table-ikkuna. Valitse View|SQL View. Nyt voit kirjoittaa SQL-lauseita.
5. ER-kaavion muuntaminen tietokannaksi on selitetty monisteen sivuilla 16-18. Jokainen kohde eli suorakulmio muutetaan suoraan tauluksi eli joudut luomaan seuraavat taulut (vältä taulujen nimissä skandinaavisia merkkejä):
   • Henkilot
   • Nimikkeet
   • Projektit
   • Tehtavat

   Jokaiseen näistä tauluista luot kentiksi siihen yhdistyvät ominaisuudet eli ellipsien sisältä löytyvät asiat.

   Taulujen luominen SQL:llä selitetään monisteen sivuilla 47-49. Yritä luoda taulut ensimmäiseksi aivan yksinkertaisimmassa muodossaan (s. 47). Taulujen luominen täytyy aloittaa aina sellaisista kohteista, jotka eivät viittaa enää edelleen muihin kohteisiin. Aloita luomalla Nimikkeet-taulu.

6. Kokeile kirjoititko SQL-lauseen oikein valitsemalla Query|Run tai painamalla työkalupalkissa olevaa huutomerkkikuvaketta. Jos et saa mitään valituksia, niin teit todennäköisesti oikein. Varmista taulun syntyminen katsomalla mitä on ilmestynyt Tables-välilehdelle.
7. Talleta SQL-lauseesi selkeästi nimettynä. Älä kuitenkaan käytä samaa nimeä kuin on luomallasi taululla.
8. Luo myös taulut Henkilot, Projektit ja Tehtavat vastaavalla tavalla. Muista tallettaa jokaista taulua vastaava SQL-lause.
9. Poista luomasi taulut niin voit edelleen parannella SQL-lauseita ja luoda taulut uudelleen.
10. Keksi jokaiselle taululle jokin perusavain. Henkilot-taulussa voit käyttää avaimena email-kenttää. Korjaa edellä kirjoittamiasi SQL-lauseita niin, että nyt ne luovat tauluun myös perusavaimen (s. 48). Muista tallettaa.
11. Poista taas kaikki taulut.
12. Parantele SQL-lauseitasi nyt niin, että luot tauluihin myös M-to-1-suhteiden vaatimat kentät ja niitä vastaavat viite-eheydet (s. 48).
13. Kirjoita kaksi uutta SQL-lausetta, joilla luot M-to-M-suhteiden vaatimat uudet taulut viite-eheyksineen. Joudut siis luomaan Tekijat-taulun ja siitä viittaukset Henkilot-, Tehtavat- ja Projektit-tauluihin.
14. Access ei valitettavasti tue SQL92:sta aivan kokonaisuudeessaan. Viite-eheyksien tarkemmat toiminnalliset määrittelyt joudutaan tekemään Accessin Relationships-ikkunan kautta. Käy määrittelemässä jokaiseen viite-eheyteen päivitystoiminnot vyöryviksi ja kiellä poisto-operaatioiden vyöryttäminen.
15. Tarkistuta ohjaajalla käyttämiesi SQL-lauseiden oikeellisuus. Sulje luomasi Yritys-tietokanta.
16. Luo uusi tietokanta nimeltään Futis omaan hakemistoosi d:\omat\ alle.
17. Luo tietokantaan Jalkapallo-kaavion mukaiset taulut ja niiden väliset viite-eheydet SQL-kieltä käyttäen aivan vastaavalla tavalla kuin teit Yritys-tietokannan yhteydessä.
18. Kun saat tietokannan kokonaisuudessaan luotua, niin tarkistuta SQL-lauseesi demo-ohjaajalla.
19. Sulje luomasi tietokanta. Hae osoitteesta http://www.jyu.fi/tt-appro/2000/tietokannat/demot/demo4/demo4.mdb mallitietokanta. Talleta se omaan hakemistoosi. Aukaise demo4.mdb Accessilla.
20. SQL-kyselyjen tekeminen on selitetty monisteen sivulta 29 eteenpäin. Kirjoita seuraavat SQL-kyselyt ja talleta jokainen tehtävän numeron mukaan nimettynä:
    
    Videovuokraamo
    1. Hae Supplier-taulusta kaikki supplierit, ja heidän tietonsa.
    2. Hae Member-taulusta memberien nimet ja osoitteet.
    3. Hae Member-taulusta kaikkien niiden nimet, jotka ovat liittyneet ennen 1.5.1998.
    4. Hae Member-taulusta kaikkien niiden nimet, joiden nimestä löytyy M-kirjain.
    5. Hae Tape-taulusta kaikki ne nauhat (kaikki kentät), jotka ovat supplier 3:en toimittamia ja jotka löytyvät catalogista nro 3.
    6. Hae kaikki ne vuokraustapahtumat, joissa on käytetty rahaa yli 15 mk.
    7. Hae kaikkien niiden elokuvien nimet catalog-taulusta, joiden vuokraus maksaa vähintään 10mk, mutta enintään 13mk.
    8. Hae kaikkien niiden nauhojen hankintapäivämäärät, jotka ovat maksaneet vähintään 100mk mutta enintään 110mk. Käytä BETWEEN-lausetta.
    9. Hae kaikkien niiden elokuvien nimet, jotka maksavat (rentalcharge) 5mk, 10mk tai 15mk.
    10. Hae Catalog-taulusta kaikkien niiden elokuvien nimet, joiden title alkaa The-sanalla ja joiden rating on suurempi kuin 6.
    11. Hae kaikki ne memberit joiden nimessä esiintyy M-kirjain tai jotka ovat liittyneet ennen 1.1.1995.
    12. Hae kaikki ne memberit, joiden nimessä esiintyy o-kirjain, mutta ei V-kirjainta tai jotka ovat liittyneet 1.1.1990.
    13. Hae kaikki memberit, joiden nimi alkaa M-kirjaimella ja jotka asuvat Meikämannentie 12:ssa tai jotka asuvat jonkin kadun varrella (eivät tiellä tai kujalla tms) ja joiden nimi loppuu n-kirjaimeen.
21. Sulje Access ja kopioi kaikki luomasi tietokannat talteen seuraavien demokertojen varalle.