Demo 6 ja 7
Sekalaista SQL:ää

Ota yhteys ODBC Query Toolilla omaan tietokantaasi.

Ristikkäiset viite-eheysmääritykset

Yritä luoda seuraavien määritysten mukainen tietokanta. Tämä ei onnistu. Muokkaa SQL-koodia siten, että tietokannan luominen on mahdollista.

CREATE TABLE Hlo (
HID	INTEGER NOT NULL,
Nimi VARCHAR(50) NOT NULL,
PID INTEGER NOT NULL,
CONSTRAINT hlo_PK 
	PRIMARY KEY (HID),
CONSTRAINT Hlo_FK
	FOREIGN KEY (PID)
		REFERENCES Projekti (PID)
)
;

CREATE TABLE Projekti (
PID	INTEGER NOT NULL,
Nimi VARCHAR(50) NOT NULL,
HID INTEGER NOT NULL,
CONSTRAINT projekti_PK
	 PRIMARY KEY (PID),
CONSTRAINT Projekti_FK
	FOREIGN KEY (HID)
		REFERENCES Hlo (HID)
)
;

Normalisointi

Olet saanut korjattavaksesi seuraavanlaisen huonon tietokantaratkaisun. Normalisoi tietokanta vähintään kolmanteen normaalimuotoon ja kirjoita korjatun tietokannan luovat SQL-lauseet. Joudut siis jakamaan tietokannan tiedot useampaan tauluun. Varmista, että korjatulla tietokannalla pystyy esittämään suunnistustapahtuman väliajat riippumatta rastien lukumäärästä. Korjaa myös testidatan lisäämiseen tarvittavat lauseet uuteen rakenteeseen sopiviksi.

CREATE TABLE Suunnistus (
ID INTEGER NOT NULL PRIMARY KEY,
Nimi VARCHAR(128) NOT NULL,
Osoite VARCHAR(128) NOT NULL,
Seura VARCHAR(64),
Ika INTEGER NOT NULL,
Kisa VARCHAR(64) NOT NULL,
Pvm DATE NOT NULL,
Lahtonro INTEGER,
Lahtoaika TIME,
Rasti1 TIME,
Rasti2 TIME,
Rasti3 TIME,
Rasti4 TIME,
Rasti5 TIME,
Rasti6 TIME,
Rasti7 TIME,
Rasti8 TIME,
Rasti9 TIME,
Rasti10 TIME,
Loppuaika TIME,
Sarja VARCHAR(64)
)
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(1, 'Tommi Lahtonen', 'PL 35, 40351 Jyväskylä', 
     'Lounais-Hämeen rasti', 29, 'Kuntorastit Laajavuori', 
     '2002-05-02', 'Miehet')
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(6, 'Tommi Lahtonen', 'PL 35, 40351 Jyväskylä', 
     'Lounais-Hämeen rasti', 29, 'Kuntorastit Touruvuori', 
     '2002-05-16', 'Miehet')
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(9, 'Tommi Lahtonen', 'PL 35, 40351 Jyväskylä', 
     'Lounais-Hämeen rasti', 29, 'Kuntorastit Väärämäki', 
     '2002-05-09', 'Miehet')
;
INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(11, 'Petri Heinonen', 'PL 35, 40351 Jyväskylä', 
     'Jyväskylän yliopisto', 28, 'Kuntorastit Laajavuori', 
     '2002-05-02', 'Miehet')
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(12, 'Petri Hienonen', 'PL 35, 40351 Jyväskylä', 
     'Jyväskylän yliopisto', 28, 'Kuntorastit Väärämäki', 
     '2002-05-02', 'Miehet')
;
INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(13, 'Petri heinonen', 'PL 35, 40351 Jyväskylä', 
     'Jyväskylän yliopisto', 28, 'Kuntorastit Touruvuori', 
     '2002-05-02', 'Miehet')
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(15, 'Maija Meikäläinen', 'TNT 9 ö 100, 40740 Jyväskylä', 
     'Kortepohjan suunnistajat', 31, 'Kuntorastit Laajavuori', 
     '2002-05-02', 'Naiset')
;

INSERT INTO Suunnistus 
	(ID, Nimi, Osoite, Seura, Ika, Kisa, Pvm, Sarja)
VALUES
	(10, 'Maija Meikäläinen', 'TNT 9 ö 100, 40740 Jyväskylä', 
     'Kortepohjan suunnistajat', 30, 'Kuntorastit Touruvuori', 
     '2002-05-16', 'Naisset')
;

Viite-eheyden toiminta

Luo seuraava tietokanta:

CREATE TABLE Nimike (
NimikeID INTEGER     NOT NULL,
Nimike   VARCHAR(64) NOT NULL,
CONSTRAINT Nimike_PK
   PRIMARY KEY (NimikeID)
);
CREATE TABLE Tehtava (
TehtavaID   INTEGER     NOT NULL,
TehtavaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Tehtava_PK
   PRIMARY KEY (TehtavaID)
);
CREATE TABLE Projekti (
ProjektiID   INTEGER     NOT NULL,
ProjektiNimi VARCHAR(64) NOT NULL,
Alkamispvm   DATE        NOT NULL,
Paattymispvm DATE        NOT NULL,
CONSTRAINT Projekti_PK
   PRIMARY KEY (ProjektiID)
);
CREATE TABLE Henkilo (
Email     VARCHAR(64) NOT NULL, 
Etunimi   VARCHAR(32) NOT NULL,
Sukunimi  VARCHAR(32) NOT NULL, 
PuhNro    VARCHAR(32) DEFAULT '-',
Tyonimike INTEGER     NOT NULL, 
CONSTRAINT Henkilo_PK
   PRIMARY KEY (Email),
CONSTRAINT Henkilo_FK_T
   FOREIGN KEY (Tyonimike)
   REFERENCES Nimike (NimikeID)
      ON UPDATE NO ACTION
      ON DELETE NO ACTION
);
CREATE TABLE Tekee (
Henkilo  VARCHAR(64) NOT NULL, 
Tehtava  INTEGER     NOT NULL,
Projekti INTEGER     NOT NULL,
CONSTRAINT Tekee_PK
   PRIMARY KEY (Henkilo, Tehtava, Projekti),
CONSTRAINT Tekee_FK_H
   FOREIGN KEY (Henkilo)
   REFERENCES Henkilo (Email)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_T
   FOREIGN KEY (Tehtava)
   REFERENCES Tehtava (TehtavaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_P
   FOREIGN KEY (Projekti)
   REFERENCES Projekti (ProjektiID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

Ota yhteys kurssin palvelimella olevaan tietokantaasi. Luo itsellesi yllämainittu tietokanta suorittamalla SQL-lauseet ODBC Query Toolin avulla.

Kirjoita SQL-kielellä järjestyksessä seuraavat lisäysoperaatiot (INSERT-lauseet).

Kaikki lisäysoperaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Perusavain- ja viite-eheysmääritykset vaikuttavat siihen onnistuuko lisääminen vai ei. Kirjoita kommentteihin lisäyssoperaation jälkeen selitys siitä miten operaation saisi onnistumaan.

1. Lisää Nimike-tauluun

   NimikeID	Nimike
   1	Ohjelmoija

2. Lisää Nimike-tauluun

   NimikeID	Nimike
   2	Suunnittelija

3. Lisää Tehtava-tauluun

   TehtavaID	TehtavaNimi
   1	Ohjelmoi

4. Lisää Tehtava-tauluun

   TehtavaID	TehtavaNimi
   2	Projektipäällikkö

5. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   1	WWW	1.2.2000	1.6.2000

6. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   2	Makro	1.3.2000	1.5.2000

7. Lisää Projekti-tauluun

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   3	WWW	2.3.2000	2.6.2000

8. Lisää Henkilo-tauluun

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   tommi.j.lahtonen@jyu.fi	Tommi	Lahtonen	2602746	2

9. Lisää Henkilo-tauluun

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   peheinon@mit.jyu.fi	Petri	Heinonen	2602746	1

10. Lisää Henkilo-tauluun

    Email	Etunimi	Sukunimi	Puhnro	Tyonimike
    eskheis@mit.jyu.fi	Esko	Heiskanen	2600000	0

11. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	2

12. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	1	1

13. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    eskheis@mit.jyu.fi	1	1

14. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	1

15. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	2

16. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	1

17. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	3

18. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	3	3

19. Lisää Tekee-tauluun

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	3

Kirjoita SQL-kielellä järjestyksessä seuraavat päivitysoperaatiot (UPDATE-lauseet).

Kaikki päivitysoperaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Kirjoita kommentteihin päivitysoperaation jälkeen selitys jos operaatio ei onnistunut. Perusavain- ja viite-eheysmääritykset vaikuttavat siihen onnistuuko päivittyminen vai ei.

Osa päivityksistä voi vaikuttaa myös useampaan tauluun samanaikaisesti (viite-eheys). Tarkista jokaisen päivityksen jälkeen mihin kaikkiin tauluihin on tullut muutoksia. Varmista, että ymmärrät miksi päivitys on vaikuttanut useampaan tauluun.

1. Päivitä Projekti-taulun projekti 1:lle (projektiID = 1) nimeksi Webbiprojekti 1 ja uudeksi projektiID:ksi 4.
2. Päivitä Nimike-taulussa nimike 1:en (nimikeID = 1) nimeksi Koodinvääntäjä.
3. Päivitä Henkilo-taulussa kaikkien henkiloiden työnimikkeeksi 1.
4. Päivitä Tekee-taulussa tehtäväksi 2 kaikille niille jotka toimivat projektissa 2 ja joiden sähköpostiosoite on tommi.j.lahtonen@jyu.fi.
5. Päivitä Nimike-taulussa NimikeID 1:en NimikeID:ksi 3.
6. Päivitä Nimike-taulussa NimikeID 2:en NimikeID:ksi 4.

Kirjoita SQL-kielellä järjestyksessä seuraavat poisto-operaatiot. Suorita jokainen operaatio ja tallenna omaksi kyselykseen (Poisto1 ... Poisto3)

Kaikki poisto-operaatiot eivät välttämättä onnistu ilman virheitä. Jos saat virheilmoituksen niin varmista, että ymmärrät miksi operaatio ei onnistu. Viite-eheysmääritykset vaikuttavat siihen onnistuuko poistaminen vai ei.

Osa poistoista voi vaikuttaa myös useampaan tauluun samanaikaisesti (viite-eheys). Tarkista jokaisen poiston jälkeen mihin kaikkiin tauluihin on tullut muutoksia. Varmista, että ymmärrät miksi poisto on vaikuttanut useampaan tauluun.

1. Poista Projekti-taulusta kaikki Projekti joiden nimi on WWW.
2. Poista Tehtava-taulusta kaikki ne tehtävät joiden TehtavaID on 1.
3. Poista Nimike-taulusta kaikki ne Nimike joiden NimikeID on suurempi kuin 2.