ER-kaavion muuntaminen relaatioiksi, Normalisointi, SQL ja ODBC - Luento 3
- Luentotaltiointi
- Kertaus
- ER-kaavion muuntaminen relaatioiksi
- Normalisointi
- Open Database Connectivity (ODBC) ja Java Database Connectivity (JDBC)
- Structured Query Language (SQL)
- Data Definition Language (DDL)
- Tietojen lisääminen tietokantaan
- Taulun tietojen muuttaminen
- Taulun tietojen poistaminen
Tällä luennolla käydään läpi ER-kaavion muuntaminen relaatioiksi, normalisoinnin idea, tietokannan rakenteen määritteleminen SQL-kielellä sekä tietokantayhteyden luominen ODBC:lla.
Luentotaltiointi
- tiedonhallinta03.wmv 38M
- tiedonhallinta03.mp3 16M
- tiedonhallinta03.avi 177M
Kertaus
Kerrataan edellisluennon lopussa käsitelty viite-eheys ja sen toiminta esimerkin avulla.
ER-kaavion muuntaminen relaatioiksi
- Jokainen tavallinen kohde muutetaan relaatioksi (tauluksi)
- Jokainen ER-diagrammista löytyvä ominaisuus lisätään siihen liittyvään relaatioon
- Jokainen Many-to-Many -suhde muutetaan tavalliseksi
relaatioksi.
- Määritellään relaatioon yhtä monta viiteavainta (foreign key) kuin on suhteeseen liittyviä kohteita
- määritellään perusavaimeksi viiteavaimista tehty yhdistetty avain
- lisätään relaatioon siihen liittyvät muut kentät.
- Many-to-One -suhteet
- Ei luoda uusia relaatioita mutta lisätään ennestään olevaan relaatioon uusi viiteavain. Lisäys tapahtuu kohteeseen, jonka puolella on Many-merkintä.
- Suhteeseen liittyneet ominaisuudet lisätään ennestään olevaan relaatioon
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 suhteellisen automaattisesti jos
noudatetaan seuraavia kahta sääntöä:
- Kohteen yhteyteen tallennetaan vain kohteeseen välittömästi liittyviä tietoja
- Kunkin tiedon päivitys tapahtuu vain yhteen paikkaan
Ei normaalimuotoinen tietokanta
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
- Kenttien arvot eivät ole jakamattomia
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
- Riveillä on eri määrä tietoa. Ei voida tietää etukäteen montaako tuotetta kukin myyjä tulee myymään.
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
- 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)
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 |
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
- 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
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 |
PiiriID | Piiri |
---|---|
P1 | Länsi |
P2 | Itä |
MyyjaID | Myyja | PiiriID |
---|---|---|
M1 | Pirkko Puoti | P1 |
M2 | Heikki Helppo | P1 |
M3 | Kaija Kauppa | P2 |
M4 | Rauno Ratsu | P2 |
- 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?
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
- 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.
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 |
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.
- Sovellus käyttää rajapinnan standardoituja funktioita, jotka tulkitsevat SQL-komennot tietokannan ymmärtämään muotoon.
- Sovelluksen ei tarvitse tukea erikseen mitään tiettyä tietokantapalvelinta
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
- Vuorovaikutteinen 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', ...);
- 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'
Käyttäjien kommentit