Normalisointi, SQL-kieli - Luento4
- Luentotaltiointi
- Normalisointi
- Open Database Connectivity (ODBC) ja Java Database Connectivity (JDBC)
- Structured Query Language (SQL)
- Data Definition Language (DDL)
- Lisäharjoitus
Tällä luennollä käydään läpi normalisoinnin idea sekä SQL-kielen perusteita.
Luentotaltiointi
Ongelmia videon katselussa?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
Erkka-ohjelma luo tietokannan ODBC- tai JDBC-yhteyden avulla.
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;
Lisäharjoitus
Jos jää aikaa niin kerrataan ja harjoitellaan vielä tietokannan suunnittelua. Miettikää millaisen ER-kaavion piirtäisitte seuraavien vaatimusten pohjalta:
24 tunnin viestihiihdon tuloslaskenta
- 24 tunnin viestihiihdossa on joukkueita joissa on yksi tai useampia hiihtäjiä, jotka hiihtävät joukkueen itse määräämässä järjestyksessä yhden tai useampia hiihtovuoroja. Yhdellä hiihtovuorolla täytyy hiihtää vähintään yksi kierros.
- Tuloslaskennassa halutaan laskea montako kierrosta kukin joukkue on hiihtänyt, kauanko jokaisen hiihtäjän kuhunkin kierrokseen on mennyt aikaa ja mihin kellonaikaan hiihtäjä on saanut kierroksensa loppuun, montako kierrosta ja kilometriä kukin hiihtäjä on hiihtänyt, montako kilometriä kukin joukkue on hiihtänyt
- Viesti hiihdetään kiertäen jotain ennaltasovittua tietynmittaista reittiä
- Viestin vaihdot tehdään aina samassa pisteessä eli tulosseurannan edessä. Hiihtäjää ei siis voi vaihtaa kesken kierrosta vaan aina täytyy kierros hiihtää loppuun
- Jokaisella hiihtäjällä on oma numerolappu josta hiihtäjä tunnistetaan.
Käyttäjien kommentit