Normalisointi, SQL ja ODBC - Luento 7

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

Luentotaltiointi

Ongelmia videon katselussa?

Harjoitustyö

Luentoesimerkki

ER-kaavio
Relaatiot

videovuokraus.erkka

Normalisointi

Ei normaalimuotoinen tietokanta

Myynti
Piiri Myyja Tuote
P1 Länsi M1 Pirkko Puoti T1 23500, T2 17200, T4 12300
P1 Länsi M2 Heikki Helppo T1 26800, T4 19300
P2 Itä M3 Kaija Kauppa T1 16200, T3 39200
P2 Itä M4 Rauno Ratsu T3 16500, T4 17200, T5 19100

Virheet

Myynti
PiiriID Piiri MyyjaID Myyja Tuote1ID Tuote1myynti Tuote2ID Tuote2myynti Tuote3ID Tuote3myynti
P1 Länsi M1 Pirkko Puoti T1 23500 T2 17200 T4 12300
P1 Länsi M2 Heikki Helppo T1 26800 T4 19300
P2 Itä M3 Kaija Kauppa T1 16200 T3 39200
P2 Itä M4 Rauno Ratsu T3 16500 T4 17200 T5 19100

Virheet

Myynti
PiiriID Piiri MyyjaID Myyja TuoteID Myynti
P1 Länsi M1 Pirkko Puoti T1 23500
P1 Länsi M1 Pirkko Puoti T2 17200
P1 Länsi M1 Pirkko Puoti T4 12300
P1 Länsi M2 Heikki Helppo T1 26800
P1 Länsi M2 Heikki Helppo T4 19300
P2 Itä M3 Kaija Kauppa T1 16200
P2 Itä M3 Kaija Kauppa T3 39200
P2 Itä M4 Rauno Ratsu T3 16500
P2 Itä M4 Rauno Ratsu T4 17200
P2 Itä M4 Rauno Ratsu T5 19100

Virheet

Myynti
MyyjaID Myyja TuoteID Myynti
M1 Pirkko Puoti T1 23500
M1 Pirkko Puoti T2 17200
M1 Pirkko Puoti T4 12300
M2 Heikko Helppo T2 26800
M2 Heikko Helppo T4 19300
M3 Kaija Kauppa T1 16200
M3 Kaija Kauppa T3 39200
M4 Rauno Ratsu T3 16500
M4 Rauno Ratsu T4 17200
M4 Rauno Ratsu T5 19100
Piiri
PiiriID Piiri MyyjaID Myyja
P1 Länsi M1 Pirkko Puoti
P1 Länsi M2 Heikki Helppo
P2 Itä M3 Kaija Kauppa
P2 Itä M4 Rauno Ratsu

Virheet

Myynti
MyyjaID TuoteID Myynti
M1 T1 23500
M1 T2 17200
M1 T4 12300
M2 T2 26800
M2 T4 19300
M3 T1 16200
M3 T3 39200
M4 T3 16500
M4 T4 17200
M4 T5 19100
Piiri
PiiriID Piiri
P1 Länsi
P2 Itä
Myyja
MyyjaID Myyja PiiriID
M1 Pirkko Puoti P1
M2 Heikki Helppo P1
M3 Kaija Kauppa P2
M4 Rauno Ratsu P2

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

Myynti
MyyjaID TuoteID TuoteNimi Myynti
M1 T1 Pölynimuri 23500
M1 T2 Linkkuveitsi 17200
M1 T4 Tietokone 12300
M2 T2 Linkkuveitsi 26800
M2 T4 Tietokone 19300
M3 T1 Pölynimuri 16200
M3 T3 Kirjahylly 39200
M4 T3 Kirjahylly 16500
M4 T4 Tietokone 17200
M4 T5 Mopo 19100

Virheet

Myynti
MyyjaID TuoteID Myynti
M1 T1 23500
M1 T2 17200
M1 T4 12300
M2 T2 26800
M2 T4 19300
M3 T1 16200
M3 T3 39200
M4 T3 16500
M4 T4 17200
M4 T5 19100
Tuote
TuoteID TuoteNimi
T1 Pölynimuri
T2 Linkkuveitsi
T3 Kirjahylly
T4 Tietokone
T5 Mopo

Open Database Connectivity (ODBC) ja Java Database Connectivity (JDBC)

ODBC ja JDBC ovat rajapintoja SQL-sovellusten ohjelmointiin.

ODBC Query Tool

Structured Query Language (SQL)

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', ...);

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.

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.

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

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/tiedonhallinta/luennot/luento7/
© Antti Ekonoja (anjoekon@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Kimmo Aittokallio (kimaitt@jyu.fi) <http://www.cc.jyu.fi/~kimaitt/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
2007-12-05 10:50:48