Normalisointi, SQL ja ODBC

• Luentotaltiointi
• Normalisointi
  • Ei normaalimuotoinen tietokanta
• Open Database Connectivity (ODBC)
• Structured Query Language (SQL)
• Data Definition Language (DDL)
  • Kenttien pakollisuus
  • Perusavaimen määrittely
  • Viiteavaimien määrittely
  • Viiteavaimien toiminta
  • Oletusarvot
  • Tarkistukset
  • Koko tietokannan luominen
• Tietojen lisääminen tietokantaan
  • Esimerkkejä tietojen lisäämisestä tauluun
• Taulun tietojen muuttaminen
• Taulun tietojen poistaminen

Tällä luennolla käydään läpi normalisoinnin idea, tietokannan rakenteen määritteleminen SQL-kielellä sekä tietokantayhteyden luominen ODBC:lla.

Normalisointi

• Pyritään vähentämään tietojen ylimäärää ja tästä aiheutuvia ongelmia tietojen lisäämisessä, poistamisessa ja päivityksessä.
• Lisätään rakenteiden selkeyttä, yhtenäisyyttä ja laajennettavuutta
• Normalisointi suosii ympäristöjä, joissa tietoja päivitetään usein.
  • Hakeminen vaatii enemmän tehoa kuin normalisoimattomassa ratkaisussa koska tiedot joudutaan hakemaan useammasta relaatiosta
• Normalisointi tapahtuu suht automaattisesti jos noudatetaan seuraavia sääntöjä:
  • Kohteen yhteyteen tallennetaan vain kohteeseen välittömästi liittyviä tietoja
  • Kunkin tiedon päivitys tapahtuu vain yhteen paikkaan

Ei normaalimuotoinen tietokanta

Virheet

• Kenttien arvot eivät ole jakamattomia

Virheet

• Riveillä on eri määrä tietoa. Ei voida tietää etukäteen montaako tuotetta kukin myyjä tulee myymään.

Virheet

• Taulun perusavaimeksi muodostuu yhdistetty avain PiiriID, MyyjaID ja TuoteID -kentistä. Kaikkien muiden kenttien pitäisi riippua taulun avaimesta. Piiri-kenttä riippuu kuitenkin vain PiiriID-kentästä eikä koko perusavaimesta. Myyja-kenttä riippuu vain MyyjaID-kentästä eikä koko perusavaimesta.
• Taulussa on myös havaittavissa aivan turhaa toistoa josta pitää päästä eroon.
• Mitä jos tulee uusi myyjä, joka ei ole vielä myynyt mitään? Miten hänen tietonsa tallennetaan järjestelmään? (insertion anomaly)
• Mitä jos Pirkko Puoti ja Heikki Helppo saavat potkut? Poistamalla heidän tietonsa järjestelmästä menetetään myös Länsi-piiri. (deletion anomaly)
• Mitä jos Pirkko Puoti siirretään uuteen piiriin? Täytyy muistaa päivittää useiden rivien tiedot samalla kertaa. (modification anomaly)

Virheet

• Edelleen jää epäselväksi, että minne pitäisi uuden myyjän tiedot lisätä. Edelleen ongelmia myös päivittämisessä ja poistamisessa.
• Edelleen turhaa toistoa

• Nyt jokaisen asian päivittäminen, poistaminen tai lisääminen tapahtuu vain yhteen paikkaan.
• Jokaisessa taulussa (kohteessa) on vain siihen liittyviä tietoja.

Mitä jos jokaisesta tuotteesta olisi tallennettuna myös tuotteen nimi?

Virheet

• Taas on havaittavissa toistoa.
• Mitä jos tuotetta ei ole myyty ollenkaan? Missä sen tiedot ovat silloin? Vastaavanlaiset ongelmat ilmenevät myös poistamisen ja päivittämisen kanssa.

Open Database Connectivity (ODBC)

ODBC on rajapinta SQL-sovellusten ohjelmointiin Windows-ympäristöissä

• Sovellus käyttää ODBC:n standardoituja funktioita, jotka tulkitsevat SQL-komennot tietokannan ymmärtämään muotoon.
• Sovelluksen ei tarvitse tukea erikseen mitään tiettyä tietokantapalvelinta

ODBC Query Tool

Structured Query Language (SQL)

• standardoitu ja laajimmin käytetty kieli relaatiotietokantojen yhteydessä.
• SQL jaetaan kahteen eri osaan:
  • Data Definition Language (DDL) - Määritellään tietokannan rakenne
  • Data Manipulation Language (DML) - Käsitellään tietokannan sisältöä
• SQL:ää käytetään monin eri tavoin ja eri yhteyksissä:
  • Vuorovaikutteinen SQL

    käytetään antamalla SQL-käskyjä omassa ikkunassaan ja saadaan vastaukset suoraan omaan ikkunaansa

  • Upotettu SQL

    SQL-käskyt upotetaan ohjelmointikieleen tai sovellus/raporttikehittimeen. Vastaukset suoraan ohjelmointikielen muuttujiin

  • Dynaaminen SQL

    SQL-käskyjä luodaan dynaamisesti ohjelmakoodissa ja lähetetään generoitu SQL-käsky tietokantajärjestelmälle käännettäväksi ja suoritettavaksi

Data Definition Language (DDL)

Taulut luodaan CREATE TABLE komennolla.

CREATE TABLE Tyontekija (
	TyontekijaID  INTEGER,
	Etunimi       VARCHAR(32),
	Sukunimi      VARCHAR(64),
	Palkka        NUMERIC(7,2),
	Syntymaaika   DATE,
	Osasto        INTEGER
);

Tauluja poistetaan DROP TABLE komennolla.

DROP TABLE Tyontekija;

Kenttien pakollisuus

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       NUMERIC(7,2) NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL

);

Perusavaimen määrittely

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       NUMERIC(7,2) NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY  (TyontekijaID)
);

Viiteavaimien määrittely

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       NUMERIC(7,2) NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
);

Taulut, joihin halutaan viitata, täytyy luoda ennen viittauksia:

CREATE TABLE Osasto (
OsastoID 	  INTEGER NOT NULL,
OsastoNimi  VARCHAR(32) NOT NULL,
CONSTRAINT  Osasto_PK
            PRIMARY KEY (OsastoID)
);

Viiteavaimien toiminta

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       NUMERIC(7,2) NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
             ON DELETE NO ACTION
             ON UPDATE CASCADE
)

Oletusarvot

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       NUMERIC(7,2) DEFAULT 10000.00,
Syntymaaika  DATE DEFAULT '1970-1-23',
Osasto       INTEGER DEFAULT 1,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
             ON DELETE NO ACTION
             ON UPDATE CASCADE
)

Tarkistukset

CREATE TABLE Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		    VARCHAR(32) NOT NULL,
Sukunimi	    VARCHAR(64) NOT NULL,
Palkka        NUMERIC(7,2) DEFAULT 10000.00 CHECK ( palkka > 0 ),
Syntymaaika	  DATE DEFAULT '1970-1-23' CHECK ( syntymaaika > '1900-1-1' ),
Osasto		    INTEGER DEFAULT 1,
CONSTRAINT Tyontekija_PK
	            PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_FK_Osasto
	            FOREIGN KEY (Osasto)
	            REFERENCES Osasto (OsastoID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE
);

CREATE TABLE Puhelinnumero (
Tyontekija	  INTEGER NOT NULL,
Puhelinnumero	VARCHAR(32) NOT NULL UNIQUE,
CONSTRAINT Puhelinnumero_PK
	            PRIMARY KEY (Tyontekija,Puhelinnumero),
CONSTRAINT Puh_FK_Tyontekija
	            FOREIGN KEY (Tyontekija)
	            REFERENCES Tyontekija (TyontekijaID)
		          ON DELETE CASCADE
		          ON UPDATE CASCADE
);

Koko tietokannan luominen

CREATE TABLE Osasto (
OsastoID	  INTEGER NOT NULL,
OsastoNimi	VARCHAR(32) NOT NULL,
CONSTRAINT Osasto_PK
	          PRIMARY KEY (OsastoID)
);


CREATE TABLE Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		    VARCHAR(32) NOT NULL,
Sukunimi	    VARCHAR(64) NOT NULL,
Palkka        NUMERIC(7,2) DEFAULT 10000.00 CHECK ( palkka > 0 ),
Syntymaaika	  DATE DEFAULT '1970-1-23' CHECK ( syntymaaika > '1900-1-1' ),
Osasto		    INTEGER DEFAULT 1,
CONSTRAINT Tyontekija_PK
	            PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_FK_Osasto
	            FOREIGN KEY (Osasto)
	            REFERENCES Osasto (OsastoID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE
);



CREATE TABLE Puhelinnumero (
Tyontekija	  INTEGER NOT NULL,
Puhelinnumero	VARCHAR(32) NOT NULL UNIQUE,
CONSTRAINT Puhelinnumero_PK
	            PRIMARY KEY (Tyontekija,Puhelinnumero),
CONSTRAINT   Puh_FK_Tyontekija
	            FOREIGN KEY (Tyontekija)
	            REFERENCES Tyontekija (TyontekijaID)
		          ON DELETE CASCADE
		          ON UPDATE CASCADE
);


CREATE TABLE Lapsi (
Huoltaja	  INTEGER NOT NULL,
Syntymaaika	DATE NOT NULL,
CONSTRAINT Lapsi_PK
	          PRIMARY KEY (Huoltaja, Syntymaaika),
CONSTRAINT   Lapsi_FK_Huoltaja
	          FOREIGN KEY (Huoltaja)
	          REFERENCES Tyontekija (TyontekijaID)
		        ON DELETE CASCADE
		        ON UPDATE CASCADE
);


CREATE TABLE Toimittaja (
ToimittajaID	  INTEGER NOT NULL,
ToimittajaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Toimittaja_PK
	              PRIMARY KEY (ToimittajaID)
);


CREATE TABLE Osa (
OsaID	  INTEGER NOT NULL,
OsaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Osa_PK
	      PRIMARY KEY (OsaID)
);

CREATE TABLE Koostuu (
K_OsaID	  INTEGER NOT NULL,
OsaID		  INTEGER NOT NULL,
Lukumaara	INTEGER NOT NULL,
CONSTRAINT Koostuu_PK
 	        PRIMARY KEY (K_OsaID, OsaID),
CONSTRAINT   Koostuu_FK_1
	        FOREIGN KEY (K_OsaID)
	        REFERENCES Osa (OsaID)
		      ON DELETE CASCADE
		      ON UPDATE CASCADE,
CONSTRAINT   Koostuu_FK_2
	        FOREIGN KEY (OsaID)
	        REFERENCES Osa (OsaID)
		      ON DELETE CASCADE
		      ON UPDATE CASCADE
);


CREATE TABLE Projekti (
ProjektiID		    INTEGER NOT NULL,
ProjektiNimi		  VARCHAR(64) NOT NULL,
Projektipaallikko	INTEGER NOT NULL,
CONSTRAINT Projekti_PK
	                PRIMARY KEY (ProjektiID),
CONSTRAINT   Projekti_FK_P
	                FOREIGN KEY (Projektipaallikko)
	                REFERENCES Tyontekija (TyontekijaID)
		              ON DELETE NO ACTION
		              ON UPDATE CASCADE
);


CREATE TABLE Tekee (
TyontekijaID	INTEGER NOT NULL,
ProjektiID	  INTEGER NOT NULL,
CONSTRAINT Tekee_PK
	            PRIMARY KEY (TyontekijaID, ProjektiID),
CONSTRAINT   Tekee_FK_1
	            FOREIGN KEY (TyontekijaID)
	            REFERENCES Tyontekija (TyontekijaID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	            FOREIGN KEY (ProjektiID)
	            REFERENCES Projekti (ProjektiID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE
);

CREATE TABLE Toimittaa (
ToimittajaID	INTEGER NOT NULL,
OsaID		      INTEGER NOT NULL,
CONSTRAINT Toimittaa_PK
	            PRIMARY KEY (ToimittajaID, OsaID),
CONSTRAINT   Toimittaa_FK_1
	            FOREIGN KEY (ToimittajaID)
	            REFERENCES Toimittaja (ToimittajaID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	            FOREIGN KEY (OsaID)
	            REFERENCES Osa (OsaID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE
);

CREATE TABLE Osan_toimitus (
ProjektiID	  INTEGER NOT NULL,
ToimittajaID	INTEGER NOT NULL,
OsaID		      INTEGER NOT NULL,
Lukumaara	    INTEGER NOT NULL,
CONSTRAINT OsaT_PK
	            PRIMARY KEY (ProjektiID, ToimittajaID, OsaID),
CONSTRAINT   OSaT_FK_1
	            FOREIGN KEY (ToimittajaID)
	            REFERENCES Toimittaja (ToimittajaID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_2
	            FOREIGN KEY (OsaID)
	            REFERENCES Osa (OsaID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_3
	            FOREIGN KEY (ProjektiID)
	            REFERENCES Projekti (ProjektiID)
		          ON DELETE NO ACTION
		          ON UPDATE CASCADE
); 

Varmistaaksesi ettei tietokannassa ole valmiiksi mitään tauluja niin kannattaa ennen CREATE TABLE -lauseita poistaa jo mahdollisesti olemassaolevat taulut.

DROP TABLE Osan_toimitus;
DROP TABLE Toimittaa;
DROP TABLE Tekee;
DROP TABLE Projekti;
DROP TABLE Koostuu;
DROP TABLE Osa;
DROP TABLE Toimittaja;
DROP TABLE Lapsi;
DROP TABLE Puhelinnumero;
DROP TABLE Tyontekija;
DROP TABLE Osasto;

Tietojen lisääminen tietokantaan

Tietueen tietojen eli tietorivin lisääminen tietokantaan tehdään INSERT-komennon avulla. Lauseen yleinen muoto on seuraava:

INSERT INTO Taulu (Kentta1, Kentta2, ...)
VALUES ('Arvo1', 'Arvo2', ...);

• Yhdellä INSERT-komennolla lisätään yksi rivi (tietue) kerrallaan.
• Tietueen lisäämisessä voidaan jättää määrittelemättä NULL-arvoja sallivat tai oletusarvoja käyttävät kentät. Kaikkiin muihin kenttiin on annettava jokin arvo!

Esimerkkejä tietojen lisäämisestä tauluun

Esimerkissä lisätään videotietokannan Elokuva-tauluun yksi elokuva. Huomaa, että elokuvaid-kentän on oltava yksilöllinen.

INSERT INTO Elokuva (elokuvaid,nimi, julkaisuvuosi, vuokrahinta, arvio)
VALUES (1,'What women want',2001, 3 , 5);

Esimerkissä lisätään Jasen-tauluun yksi elokuva. Huomaa, että jasenid-kentän on oltava yksilöllinen.

INSERT INTO jasen (jasenid,nimi,osoite,liittymispvm, syntymavuosi)
VALUES (8,'Leila Leffafani','Leffatie 1','1990-01-01', 1973);

Taulun tietojen muuttaminen

Tietojen muuttaminen tauluihin tehdään UPDATE-komennon avulla. Tietoja päivittäessä pitää muistaa seuraavat asiat.

• Muista päivitykseen WHERE-ehto!
• Jos UPDATE-lauseessa ei ole WHERE-ehtoa, niin päivitys tehdään kaikille riveille!

Seuraavassa esimerkissä muutetaan henkilön nimi toiseksi UPDATE-komennon avulla.

UPDATE Jasen
SET nimi = 'Leila Leffahani'
WHERE nimi = 'Leila Leffafani';

Taulun tietojen poistaminen

Taulun tietueiden poistaminen onnistuu DELETE-komennolla.

Seuraavassa esimerkissä tuhotaan Jasen-taulusta henkilöt.

• Muista lisätä poistoon WHERE-ehto ja käytä ehtona perusavainta jos haluat poistaa vain yhden rivin!
• Jos DELETE-lauseessa ei ole WHERE-ehtoa, niin koko taulu tyhjennetään!

DELETE FROM Jasen
WHERE osoite = 'Nörttikuja 3'