Duomenų bazių naudojimas, SQL PDF

Duomenų bazių naudojimas, SQL Įvadas į projektavimą Lentelių kūrimas Lentelės kuriamos CREATE TABLE sakiniu: CREATE TABLE lentelės_pavadinimas (stulpelis1 tipas1, stulpelis2 tipas2,..) Duombazių priimamos duomenų rūšys:  Simboliniai duomenys;  Dvejetainiai duomenys;  Skaičiai;...

Duomenų bazių naudojimas, SQL Įvadas į projektavimą Lentelių kūrimas Lentelės kuriamos CREATE TABLE sakiniu: CREATE TABLE lentelės_pavadinimas (stulpelis1 tipas1, stulpelis2 tipas2,..) Duombazių priimamos duomenų rūšys:  Simboliniai duomenys;  Dvejetainiai duomenys;  Skaičiai;  Datos;  Laikas. Simboliniai duomenys (character datatypes) Simbolių eilutė gali priimti tam tikrą kiekį simbolių. Fiksuoto ilgio simbolių eilutės:  CHAR(n) - n iki 254 (2 147 483 647); Kintamo ilgio:  VARCHAR(n) (CHARACTER VARYING(n)) - n iki 2 147 483 647 ilgio;  CLOB(n[K|M|G]) - iki 2 GB. Kai n > 254, užklausose nenaudotina: DISTINCT, GROUP BY, ORDER BY Skaitiniai duomenys (number datatypes)  SMALLINT - sveikieji skaičiai [-32 768; 32 767];  INTEGER - „dideli“ sveikieji skaičiai (4 baitai) [-2 147 483 648; 2 147 483 647];  BIGINT - „ypač dideli“ sveikieji skaičiai (8 b);  REAL - slankiojo kablelio skaičiai, 32 baitai, pvz., -2E5, 5.555E-18, -.655645e8;  FLOAT (DOUBLE) - (dvigubi) skaičiai po kablelio, 32 b (64 b);  DECIMAL (p, s) (NUMERIC) - dešimtainiai skaičiai - iki 100000 dešimtainių skaitmenų. Dvejetainiai duomenų tipai (binary datatypes) Fiksuoto ilgio:  BINARY(n) (iki 1E9 baitų). Kintamojo ilgio:  VARBINARY(n) (iki 1E9 baitų);  BLOB(n[K|M|G]). Datos ir laiko duomenys  DATE - (atmintyje 4 baitai);  TIME - (3 baitai);  TIMESTAMP - (10 baitų). DATE ir TIME vaizdavimas priklauso nuo terpės (locale): 2005.01.01, 12:00:00, 2005-01-01-12.15.55.330000. DBVS visada „supranta“ ISO datą ir laiką: 2005-01-01, 12:00:00. Lentelių kūrimas Pvz. darbuotojų lentelei: CREATE TABLE DARBUOTOJAI (ASMENS_KODAS BIGINT NOT NULL, VARDAS VARCHAR(20), PAVARDE VARCHAR(30), DIRBA_NUO DATE, ATLYGINIMAS DECIMAL (7,2) DEFAULT 500, PARDUOTUVES_ID SMALLINT) NOT NULL - stulpelis negali įgyti NULL reikšmių. DEFAULT - reikšmė pagal nutylėjimą (kokią įgaus, jei duomenys nebus įterpiami į stulpelį) Lentelių kūrimas Susikurkime lentelę parduotuvėms: CREATE TABLE PARDUOTUVES (ID INT AUTO_INCREMENT, MIESTAS VARCHAR(20), ADRESAS VARCHAR(40) UNIQUE) Galimos parinktys stulpeliams: AUTO_INCREMENT - automatinis didėjimas (nuo 1); UNIQUE - stulpelio reikšmės būtinai skirsis viena nuo kitos, nebus pasikartojimų. Lentelių kūrimas Daugumą lentelės savybių galima nurodyti vėliau naudojant ALTER sakinį (lentelės struktūros keitimas): ALTER TABLE DARBUOTOJAI ADD Gimtadienis DATE ALTER TABLE DARBUOTOJAI DROP Gimtadienis ALTER TABLE PARDUOTUVES ALTER MIESTAS SET DEFAULT ‘Vilnius’ ALTER TABLE PARDUOTUVES ALTER Svarba DROP DEFAULT Lentelių jungimas (WHERE(nebenaudotinas!)) Reliacinis modelis pagrįstas duomenų skaidymu į lenteles, jas susiejant per bendrą lauką. Norint rasti duomenis apie objektą, kai tie duomenys yra išskaidyti per kelias lenteles, naudojamas lentelių jungimas. Sintaksė: SELECT lentelė1.stulpelis1, lentelė2.stulpelis2 FROM lentelė1, lentelė2 WHERE jungimo_sąlyga Jungimo sąlyga - tai stulpelis, laikantis bendrus duomenis ir pirmoje, ir antroje lentelėse. Lentelių jungimas (JOIN) Kitas būdas jungti lenteles: SELECT stulpelis1, stulpelis2 FROM lentelė1 JOIN lentelė2 ON jungimo_sąlyga Naudojant JOIN lengviau atskirti jungimą nuo sąlygų. JOIN turi ir kitų galimybių. Galima jungti ir daugiau lentelių: SELECT stulpelis1, stulpelis2, stulpelis3 FROM lentelė1, lentelė2, lentelė3 WHERE jungimo_1_ir_2_sąlyga AND jungimo_2_ir_3_sąlyga(!) SELECT stulpelis1, stulpelis2, stulpelis3 FROM lentelė1 JOIN lentelė2 ON jungimo_1_ir_2_sąlyga JOIN lentelė3 ON jungimo_2_ir_3_sąlyga Lentelių jungimas (JOIN) Visų projektų vykdytojų pavardės, statusai ir valandos: SELECT Pavarde, Statusas, Valandos FROM Vykdytojai JOIN Vykdymas ON Vykdytojai.Nr=Vykdymas.Vykdytojas Užklausos rezultate 5-ojo vykdytojo nebus, jis nedalyvauja jokiame projekte, jo nėra lentelėje vykdymas. Lentelių jungimas (JOIN) Visi vykdytojai gali būti pateikti taikant išorinį jungimą. LEFT OUTER JOIN - rezultatas papildomas kairiosios lentelės nesujungiamomis eilutėmis. RIGHT OUTER JOIN - rezultatas papildomas dešiniosios lentelės nesujungiamomis eilutėmis. FULL OUTER JOIN - kai kuriose DBVS veikiantis jungimas, rezultatas papildomas iš abiejų pusių lentelių nesujungiamomis eilutėmis. JOIN atitinka INNER JOIN jungimą. Lentelių jungimas (JOIN) Darbuotojai ir jų dalyvavimas projekte Nr 1.: SELECT Pavarde, Statusas, Valandos FROM Vykdytojai LEFT OUTER JOIN Vykdymas ON Nr=Vykdytojas WHERE Projektas = 1 Arba: SELECT Pavarde, Statusas, Valandos FROM Vykdymas RIGHT OUTER JOIN Vykdytojai ON Nr=Vykdytojas WHERE Projektas = 1 Rezultatas tas pats. Lentelių jungimas (JOIN) Lenteles galima jungti su jomis pačiomis. Pavardės vykdytojų, turinčių vienodą kvalifikaciją: SELECT A.Pavarde, B.Pavarde FROM Vykdytojai A JOIN Vykdytojai B ON A.Kvalifikacija=B.Kvalifikacija WHERE A.Nr < B.Nr A.Nr < B.Nr reikalinga išmesti visus duplikatus. Struktūrinės užklausos Vienoje užklausoje gali būti ir kita užklausa. Pavardės vykdytojų, dalyvaujančių projekte Nr. 1: SELECT Pavarde FROM Vykdytojai WHERE Nr IN (SELECT Vykdytojas FROM Vykdymas WHERE Projektas = 1). Vykdytojai, dalyvaujantys bent viename ypatingos svarbos projekte: SELECT Pavarde FROM Vykdytojai WHERE Nr IN (SELECT Vykdytojas FROM Vykdymas WHERE Projektas IN (SELECT Nr FROM Projektai WHERE Svarba = ‘Ypatinga’)) Pirminis raktas (Primary Key) Pirminis raktas - tai stulpelio ar stulpelių apribojimas, neleidžiantis jam ar jiems įgyti pasikartojančių reikšmių ir reikšmės NULL. Pvz.: CREATE TABLE TEST(ID INT PRIMARY KEY) - ID neįgis NULL, nebus pasikartojantis. ALTER TABLE PARDUOTUVES ADD PRIMARY KEY (P_Id) ALTER TABLE PARDUOTUVES ADD CONSTRAINT pk_Miest_Adres PRIMARY KEY (MIESTAS, ADRESAS) ALTER TABLE PARDUOTUVES DROP CONSTRAINT pk_Miest_Adres Įprastai negalima apriboti stulpelio pirminiu raktu, jei jis priima NULL reikšmes. Antrinis raktas (Foreign Key) Antrinis raktas - tai stulpelio apribojimas, neleidžiantis stulpelio reikšmėms išeiti iš reikšmių rėžių, kurios yra kitoje lentelėje pirminio rakto stulpelyje. ALTER TABLE DARBUOTOJAI ADD FOREIGN KEY (PARDUOTUVES_ID) REFERENCES PARDUOTUVES(ID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE DARBUOTOJAI ADD CONSTRAINT fk_DarPard FOREIGN KEY (PARDUOTUVES_ID) REFERENCES PARDUOTUVES(ID) ALTER TABLE DARBUOTOJAI DROP CONSTRAINT fk_DarPard Indeksai Skirti paspartinti paieškos darbą duomenų bazėse. Atitikmuo rodyklėms mokslinėse knygose. Duomenų bazė, jei naudojamas suindeksuotas stulpelis paieškoje, nenaudos visos eilutės. Bus naudojamas tik tas stulpelis. Automatiškai suindeksuojami pirminiai ir antriniai raktai. Nepatartina indeksuoti dažnai besikeičiančių stulpelių (indeksas yra atskira lentelė). Priskyrimo sintaksė: CREATE INDEX IDX_NAME ON TABLE_NAME(COLUMN_NAME)  Pavyzdys: CREATE INDEX PAVARDZIU_IDX ON VYKDYTOJAI(PAVARDE)  TRYNIMAS: DROP INDEX IDX_NAME DB projektavimas Atliekamas prieš pradedant DB formavimo procesą. Svarbus norint sukurti efektyvią, tikslią, poreikius atitinkančią sistemą. Patartina planavimą pradėti raštu ant balto popieriaus lapo (MS Access, dbdesigner.net, PlantUML). Nors atrodo, kad toks projektavimas yra laiko gaišimas pradžioje, tačiau iš tiesų daugiau laiko sugaištama vėliau, jei prieš tai neplanuojama. DB projektavimo procesas 1. Numatyti duomenų bazės paskirtį; 2. Apibrėžti, kokios lentelės bus reikalingos; 3. Apibrėžti, kokie laukai turės būti DB lentelėse; 4. Nurodyti laukus su unikaliomis reikšmėmis; 5. Nustatyti, kaip lentelės siesis tarpusavyje; 6. Tobulinti pradinį projektą. DB paskirtis Pradedant kurti duomenų bazę reikia atsakyti į tris esminius klausimus: 1. Kokius duomenis turėtų kaupti duomenų bazė? 2. Kokią informaciją apie šiuos duomenis norės žinoti DB vartotojas? 3. Kokius veiksmus vartotojas norės atlikti su duomenimis? DB paskirtis Pavyzdys: duomenų bazė bibliotekai. 1. Duomenų bazėje turėtų būti duomenys apie skaitytojus ir knygas. 2. DB vartotojas norės gauti informaciją apie tai, kokias knygas kas skaito, kada turi grąžinti, kiek kokio autoriaus knygų yra bibliotekos fonde ir pan. 3. Vartotojas norės iš DB gauti informaciją, taip pat ją pakeisti, pasikeitus situacijai – grąžinamos ar paskolinamos knygos, nauji skaitytojai, nauji leidiniai, ištrinti įrašus apie nurašytas knygas ir pan. Lentelių apibrėžimas Apibrėžus paskirtį, apibrėžiamos lentelės:  Apie kokius objektus bus kaupiami duomenys?  Siektina, kad visa informacija apie objektą būtų toje pačioje lentelėje, o kiekviena lentelė būtų skirta tik vienam objektui - ne visada tai pavyksta. Lentelių apibrėžimas Bibliotekos pavyzdys:  Informacija bus kaupiama apie skaitytojus, autorius, knygas bei apie tai, kas kokią knygą yra pasiskolinęs;  4 lentelės: Skaitytojai Autoriai Knygos “Nuomos sutartys” Duomenų laukai lentelėse Nusprendus, kokiose lentelėse bus kaupiami duomenys, reikia nuspręsti, kokia informacija bus kaupiama apie kiekvieną objektą. Jos turi būti būtent tiek, kiek reikalinga, nei per daug, nei per mažai. Duomenų laukai lentelėse Skaitytojai: Autoriai: Duomenų laukai lentelėse Knygos: Nuomos sutartys: Duomenų laukai lentelėse Formuojant DB lenteles ir laukus reikėtų prisilaikyti šių principų:  Kiekviena informacijos detalė saugoma tik vienoje lentelėje;  Lentelėje neturėtų būti pasikartojančios informacijos;  Kiekvienoje lentelėje informacija turėtų būti tik apie vieną objektą. Pirminiai raktai Kiekviena lentelė turėtų turėti savo pirminį raktą – lauką, kurio reikšmė kiekvienam įrašui yra unikali. Pirminis raktas gali būti: Vieno lauko - automatiškai didėjantis (1,2,3... a,b,c) arba vartotojo nustatomas (asmens kodas, ISBN, studento pažymėjimo nr ir pan) Daugelio laukų (multi-field) - unikali ne konkretaus lauko reikšmė, bet keleto laukų reikšmių rinkinys (vardas IR pavardė, skaitytojo nr. IR egzemplioriaus nr.) Išoriniai raktai Pirminiai raktai taip pat atlieka vaidmenį kuriant ryšius tarp lentelių. Vienos lentelės pirminis laukas įterpiamas į kitą lentelę ir tampa ten išoriniu raktu. DB normalizavimas DB normalizavimas - tai patikrinimas, kad: 1. Lentelėje yra tik su pirminiu raktu susiję duomenys; 2. Kiekviename lauke yra tik vienas duomenų blokas; 3. Pasikartojantys duomenys yra eliminuoti. Normalizuojant duomenys yra priskiriami ten, kur yra jų logiška ir unikali vieta. DB normalizavimas Normalizavimo tikslai: 1. Pasikartojančių duomenų kiekio duomenų bazėje minimizavimas; 2. Efektyviai ir greitai prieinamos ir valdomos duomenų bazės sukūrimas nepakenkiant duomenų vientisumui; 3. Sukūrimas tokios struktūros, kad keičiant duomenis, juos reikėtų taisyti tik vienoje vietoje. DB normalizavimas Normalinės formos:  Nenormalizuoti duomenys - pasikartojančios grupės, netolygūs duomenys, įterpimo ir trynimo anomalijos.  1 normalinė forma - kiekvienas DB laukas saugo tik vieną reikšmę ir lentelėje nėra pasikartojančių grupių.  2 normalinė forma - atitinka 1 formos reikalavimus ir tiesiogiai nuo pirminio rakto nepriklausoma informacija turi būti perkelta į kitą lentelę.  3 normalinė forma - atitinka 2 formos reikalavimus ir visi laukai, kurių reikšmės gali būti išgautos iš kitų lentelės laukų duomenų, turi būti pašalinti. 1 normalinė forma (blogas variantas) 1 normalinė forma (tenkinantis variantas) 2 normalinė forma (blogas variantas) 2 normalinė forma (tenkinantis variantas) 3 normalinė forma (blogas variantas) 3 normalinė forma (tenkinantis variantas) Ryšiai tarp lentelių Ryšys – sąsaja tarp bendrų laukų dvejose duomenų bazės lentelėse. Trys tipai:  1:1 - naudojamas retai, paprastai dėl saugumo, arba kai dalis lentelės laukų dažniausiai būna tušti (pvz. Mokinių lankomumas ir anketų pildymas);  1:∞ - dažniausiai naudojamas ryšys. Pirminis raktas su išorinio rakto lauku kitoje lentelėje;  ∞:∞ - reikia skaidyti į du 1:∞ ryšius per tarpinę lentelę (netraktuojamas kaip tikras ryšys). Tobulinimas Sudėliojus pirminį DB projektą (lentelės, laukai ir ryšiai) reikia atidžiai jį išnagrinėti, ieškant galimų trūkumų. Patikrinkite, ar tokia duomenų bazės struktūra leis jums gauti tuos atsakymus, kurių norite. Paskutinė galimybė lengvai ištaisyti DB struktūros klaidas:  Per mažai dėmesio planavimui;  Normalizavimo ignoravimas;  Prastai parinkti laukų/lentelių pavadinimai;  Dokumentacijos trūkumas.