Tabele i ograničenja | Baze podataka
Tabele ili entiteti u bazama podataka su zapravo kontejneri u kojima se naši podaci čuvaju. Podaci u njima, klasifikovani su po vrlo jednostavnom sistemu kolona i redova (vrsta).
Kada definišemo strukturu neke tabele, mi, zapravo, određujemo broj i nazive njenih kolona i tipove podataka. Ako bismo, na primer, želeli da naša tabela skladišti podatke o korisnicima, i da ti podaci budu ime, prezime i matični broj, mogli bismo napraviti tabelu od tri kolone. U jednoj bi bilo ime, a njen tip bi mogao biti char, varchar ili text, jer je u pitanju tekstualna vrednost. Druga kolona bi bilo prezime, koje može biti istog tipa kao i ime. I treća kolona bi bio matični broj. Ali, pošto je taj broj prilično veliki, optimalnije bi bilo i tu kolonu deklarisati kao tekstualnu. To znači da će naša tabela imati sledeću strukturu:
ime varchar(40)
prezime varchar(40)
maticniBroj varchar(13)
Postavili smo veličine tipa varchar na 40, pod pretpostavkom da, ni ime, ni prezime, neće biti duže od 40 karaktera.
Kada definišemo tabelu sa ovakvom strukturom, ona će biti validna, ali i prilično slabe upotrebljivosti. Njen osnovni problem biće to što će moći da postoje dva ili više korisnika sa istim imenom, prezimenom i matičnim brojem. Što bi značilo, nepotrebno gomilanje podataka. Takođe, može se desiti da postoje i dve različite osobe, sa istim matičnim brojem. U tom slučaju, ne bismo mogli da pronađemo željene podatke o osobi sa određenim matičnim brojem, pa tako i sama tabela gubi svrhu, osim da skladišti potpuno neorganizovanu grupu podataka.
Dakle, ovoj tabeli potreban je neki mehanizam koji će da zabrani unos duplikata matičnog broja. Taj mehanizam može biti ograničavajući faktor. Na primer, primarni ključ.
Primarni ključ je osnovni ograničavajući faktor u jednoj tabeli. To je svojstvo kolone, koje ne omogućava da se vrednosti u njoj ponavljaju. Na ovaj način, lako je identifikovati bilo koji red kolone, jer se u svakom od njih nalazi unikatan primarni ključ. Primarni ključ je karakteristika skoro svake tabele, iako postoji mogućnost da se izbegne njegovo postavljanje.
Ponekad, tabela može imati i nekoliko kolona, koje zajedno čine primarni ključ te tabele. Na primer, ako bismo hteli da se ni jedno ime i prezime ne ponove, mogli bismo definisati primarni ključ na kolonama ime i prezime.
Ovo nam je dovoljno informacija za pravljenje jedne jednostavne tabele. Tabelu možemo napraviti iz GUI aplikacije ili putem SQL upita.
Obe aplikacije, i MySQL Query Browser i MySQL Administrator, imaju opcije za pravljenje tabele. Ali da bismo mogli da napravimo tabelu, potrebno je prvo da napravimo bazu. Ukoliko već imate napravljenu bazu, selektujte je i aktivirajte naredbu Create Table.
Pred Vama će se naći sledeći ekran:
Ovaj prozor predstavlja vašu tabelu. Analizirajmo ga od početka.
Prva opcija je Table Name. Ova opcija, naravno, predstavlja naziv vaše tabele. Ovaj naziv je logički i veoma je bitan, jer je baš to ono kako ćete se obraćati svojoj tabeli. Zato obratite pažnju. Pre svega, nemojte davati naziv razdvojen praznim prostorom („Moja tabela” ili „Tabela korisnici”) – ne zbog MySQL servera, već zbog sebe. Davanje naziva tabela razdvojenih praznim prostorom znatno otežava stvari jer ćete, svakog puta kada budete hteli da se obratite tabeli, morati to da činite putem Backtick navodnika (select * from `moja tabela`).
Izbegavajte i razlikovanje naziva tabela po velikim i malim slovima. Ovo ne radi na svim plaformama isto, pa zato ne očekujte da tabele mojatabela i mojaTabela budu u svakom slučaju iste ili u svakom slučaju različite.
Dvoklikom na polje Column Name možete uneti naziv kolone, a zatim dodati i druge podatke.
Započnite unos podataka matičnim brojem (iz praktičnog razloga jer prvoj dodatoj koloni MySQL Query Browser automatski dodeljuje primarni ključ), a zatim unesite i ostale kolone.
Column Name i Datatype polja su jasna – naziv i tip kolone. Postavili smo tipove baš onako kako smo na početku lekcije i planirali. Nakon toga sledi nekoliko polja koja zahtevaju objašnjenje.
NOT NULL
Ako je ovo polje uključeno i pokušate da unesete podatke u tabelu, a da pri tom, ne unesete vrednost za polje koje ima ovu opciju uključenu, nećete uspeti. MySQL će prijaviti grešku, što znači da se na ovaj način osiguravate da će polje imati vrednost.
AUTO INC (Auto Increment)
U slučaju ove tabele, identifikovaćemo redove putem matičnog broja, ali često, nećemo imati kolonu po kojoj ćemo moći da identifikujemo određene redove (na primer, šta ako se u tabelu unose samo imena i prezimena). Zato se, po pravilu, u svaku tabelu dodaje jedna kontrolna kolona, čija je uloga isključivo obezbeđivanje integriteta redova, odnosno, njihove identifikacije u tabeli. Ova kolona, najčešće se naziva što intuitivnije, jer se obično kroz nju vrši povezivanje i sa drugim tabelama (korisnikid, racunid, fakturaid…). Ali, ovakva kolona ne bi imala svrhu, ako bi, prilikom svakog unosa podataka, trebalo da mi unosimo i redni broj podatka.
Zato se koristi opcija Auto Increment. Ona automatski unosi brojeve u ovakvu kolonu. Ukoliko je ostavimo na podrazumevanim vrednostima, brojanje će početi od 1 i svaki novi red imaće vrednost za jedan veću od prethodnog. Ali, ova podešavanja se mogu i izmeniti i brojanje može početi od bilo kog (celog) broja i imati neograničen inkrement. Ovi parametri nazivaju se Seed (broj od koga počinje brojanje) i increment (broj za koji će svaka nova kolona biti povećana). U našem primeru, nije nam potrebna kolona za identifikaciju, pa smo isključili i ovu opciju.
BINARY
Aktivacija ove opcije pretvara kolonu u binarni tip. Identično kao da smo upotrebili varbinary tip. Nakon prihvatanja ovakve strukture tabele, pritiskom na taster Accept Changes, pojavljuje se sledeći prozor:
U ovom prozoru MySQL Query Browser nam prikazuje upit koji je generisao i pita nas da li da ga izvrši. Ako je upit isti kao ovaj na slici, pritisnite taster Execute. Primećujete da je na kraju dodata opcija ENGINE = InnoDB. To je zato što se mehanizam skladištenja može definisati na nivou tabele.
Kada jednom pritisnete taster Execute, tabela će biti kreirana. I pojaviće se u desnom polju ispod baze u kojoj je napravljena (ukoliko se ne pojavi odmah, pritisnite desni taster miša i opciju refresh):
Ukoliko sada izvršite dvoklik levim tasterom miša na novokreiranu tabelu, ili bilo koje od njenih polja, MySQL Query Browser će generisati upit za izlistavanje selektovanog objekta i smestiti ga u polje za upit. Na primer, dvoklik na New Table iz primera, kreiraće sledeći upit:
Ako sada aktivirate ovaj upit pritiskom na taster Execute, biće izlistano samo nekoliko praznih redova, jer je tabela prazna. Takođe, pogledajte na koji način je MySQL Query Browser imenovao našu tabelu u generisanom upitu. Morao je da je stavi pod navodnike, pošto se sastoji od dve reči.
Da bismo ovo ispravili, možemo izmeniti strukturu tabele, pa i njeno ime. Odaberite tabelu i pritisnite desno dugme miša, a zatim odaberite opciju Edit Table:
Tabela će se ponovo otvoriti i sada možete urediti sve što ste mogli i prilikom kreiranja. Promenite ime (table name polje) u, na primer, mojatabela i pritisnite Apply Changes, kako biste potvrdili izmene.
Ovog puta, MySQL Query Browser nam generiše sledeći, prilično jednostavan upit:
ALTER TABLE `mojabaza`.`new table` RENAME TO `mojabaza`.`mojatabela`;
Podsetićemo da naredba ALTER služi da izmeni strukturu već postojećeg objekta.
Već smo videli da je aplikacija za nas generisala upit za kreiranje tabele. Taj upit smo mogli napisati i sami i aktivirati ga kroz polje za upit. Rezultat bi bio isti. Osim toga, jednom kreirana tabela, može se menjati, na koji god način bila kreirana (ako kreiramo grafički, možemo menjati putem SQL-a i obrnuto).
Da bi istu tabelu kreirali u programu MySql Workbench ispratite sledeću proceduru:
U Object Browser panelu, otvorite bazu mydatabase (kreiranu u prethodnom primeru), a zatim, iznad opcije Tables, desnim tasterom miša aktivirajte kontekstni meni. Iz menija, odaberite stavku Create Table…
Otvoriće se panel za kreiranje nove tabele, sa predefinisanim nazivom (new_table). Možete za početak ostaviti taj naziv. U odeljku Columns, unesite kolone: personalid, firstname, lastname, kao na sledećoj slici:
Program će prikazati generisani upit i dati mogućnost njegovog izvršavanja. Da bi upit bio izvršen aktivirajte taster Apply.
Da bi promenili ime kreirane tabele, otvorite je uz pomoć kontekstnog menija iznad njenog naziva, a zatim odaberite opciju Alter Table. U polju za naziv tabele, postavite novo ime.
Recimo da u našoj bazi želimo da pamtimo i grad u kome stanuje svaki od radnika. Rekli smo da bi, ako bismo želeli da poštujemo relacioni integritet, ovakav podatak trebalo smestiti u zasebnu tabelu kako ne bi dolazilo do ponavljanja.
Kreaciju ove tabele ćemo, za razliku od prethodne, izvršiti ručno, putem upita. Osnovna karakteristika svakog grada je svakako njegovo ime. U našem slučaju, to je i jedino što želimo da upamtimo. To znači da naša tabela treba da ima samo jednu kolonu.
nazivgrada
Beograd
Niš
Pirot
Valjevo
Čačak
Pored ove kolone, kao što smo rekli, potrebna nam je još jedna kolona, koja će da obezbeđuje integritet na nivou redova. Ova kolona može se zvati gradid.
| gradid | nazivgrada |
| 1 | Beograd |
| 2 | Niš |
| 3 | Pirot |
| 4 | Valjevo |
| 5 | Čačak |
Takođe, za našu tabelu važiće jedno pravilo, a to je da se vrednost u koloni gradid nikada ne ponovi.
create table gradovi
(
gradid int primary key auto_increment,
nazivgrada varchar(50)
)
Upit za kreiranje tabele počinje naredbom Create Table, kojoj sledi naziv Tabele. U našem slučaju, Gradovi.
Zatim sledi struktura tabele. Ona počinje i završava se malim zagradama, unutar kojih se nalaze opisi kolona, odvojeni zarezima. Svaka kolona počinje nazivom, kojoj sleduje tip i opcioni parametri.
Naša tabela ima dve kolone. Prva je gradid, koja je tipa int i za nju važe sledeće karakteristike:
-
primary key (vrednosti se ne mogu ponavljati na nivou kolona)
-
auto_increment (vrednost kolone se automatski povećava)
Sledeća kolona je nazivgrada. Ona je tipa varchar 50 i nema dodatne parametre.
Ako aktiviramo ovaj upit, ova tabela će biti kreirana. Primećujete da nismo u upit stavili naziv mehanizma skladištenja (iako je postojao u upitu koji je generisala aplikacija). Ukoliko se mehanizam skladištenja ne postavi, tabela će funkcionisati na podrazumevanom mehanizmu skladištenja.
Integritet podataka
Sada imamo dve tabele i u te dve tabele, imamo dva identična ograničenja, bazirana na primarnom ključu. Ovo ograničenje dešava se na nivou kolone i zbog toga se naziva domensko ograničenje. Isto tako, mogli bismo izvršiti ograničenje na nivou reda (ne dozvoliti da se vrednost ponavlja u okviru jednog reda, što bi bilo entitetsko ograničenje, ili ograničenje kroz druge tabele, relaciono ograničenje.
Relacije između tabela
S obzirom na to da imamo sve potrebne podatke (gradove i radnike), ostalo je još da izvršimo povezivanje tih podataka. U ovom trenutku se ne zna koji korisnik živi u kom gradu. Da bismo uskladištili i taj podatak, potrebna nam je relacija između tabela. Pri tom, relaciju ne morate shvatati kao fizički pojam, jer ona ne mora stvarno, fizički, da postoji u strukturi.
Na primeru naše dve tabele, relacija (dodeljivanje gradova korisnicima) može da bude napravaljena na dva načina. n:1 i n:n. Ali, s obzirom na to da nije realno da jedan korisnik živi u više gradova, dovoljna će biti relacija n:1.
Šta zapravo znači n:1? Znači da jedno polje, jedne tabele, može biti upotrebljeno na n (više) polja druge tabele, ali ne i obrnuto. U primeru, znači da više korisnika može biti iz istog grada, ali jedan korisnik može biti samo iz jednog grada.
Pošto smo se odlučili na relaciju n:1, potrebno nam je da u tabeli sa korisnicima (mojatabela) postavimo još jednu kolonu, koja će nositi broj grada.
Ovu kolonu možete dodati kroz SQL upit ili aplikaciju:
ALTER TABLE mojatabela ADD COLUMN gradid INTEGER UNSIGNED NOT NULL
Sada bismo mogli da ispunimo tabelu gradovi, a zatim, prilikom unosa korisnika, ukazujemo na određeni grad brojevima. Na primer:
insert into mojatabela values (‘1234567890123′,’petar’,’petrovic’,1)
Ukoliko u tabeli gradovi, postoji polje gradid sa vrednošću 1, mi ćemo, na osnovu tog polja, znati koji je naziv grada u kome živi korisnik (jer i korisnik ima vrednost jedan, kao oznaku grada).
Na taj način, obezbedili ste relaciju. Ali, ova relacija, kao što smo napomenuli, još uvek nema fizička svojstva. Ona, u stvari, ne postoji u bazi. Zato se može dogoditi sledeće:
insert into mojatabela values (‘1234567890123′,’petar’,’petrovic’,15)
Ako pogledate listu gradova iz prethodnih pasusa, videćete da gradid sa vrednošću 15 ne postoji u listi. Dakle, korisnik koga smo upravo uneli, nema grad u kome stanuje. Pri tom, ukoliko izvršimo ovakav unos, greška ne mora ni biti prijavljena, jer ne postoji sistem koji će nam reći da grad pod brojem 15 ne postoji. Takve greške (logičke greške), veoma su opasne, jer ne ometaju izvršavanje programa, ali program ne radi dobro.
Sa druge strane, rešenje za ovaj „problem“ veoma je jednostavno.
Strani ključ (Foreign Key)
Strani ključ je simbol relacione baze podataka. To je Ograničavač koji održava fizičku relaciju između dve tabele. Jednostavnije rečeno, ne dozvoljava da u jednu tabelu bude unešena vrednost, ukoliko takva vrednost ne postoji u drugoj tabeli na povezanoj koloni.
Strani ključ možete uneti putem GUI-ja ili ručno, unosom upita:
ALTER TABLE mojatabela ADD CONSTRAINT fk_gradid FOREIGN KEY fk_gradid(gradid) REFERENCES gradovi (gradid)
Ne zaboravite samo da strani ključevi ne funkcionišu na MyISAM mehanizmu skladištenja.
Pošto se radi o stranom ključu na tabeli mojatabela, nju ćemo i modifikovati naredbom alter. Nakon naziva tabele, ide naredba add constraint. Pošto je strani ključ, zapravo, relacioni ograničavač (constraint), ova naredba je prilično jasna.
Sledi naziv stranog ključa (fk_gradid). Ovaj naziv definišemo sami i nije zgoreg da bude intiutivan. Obično, ograničavači imaju ispred naziva skraćenicu tipa ograničavača (fk – strani ključ, pk – primarni ključ…). Zatim, ključna reč (reči) foreign key, identifikuje tip ograničavača. Konačno, obzirom da je u pitanju strani ključ, njegov identifikator podrazumeva dva podatka: kolonu na koju će biti primenjen strani ključ i kolonu koja je referenca za taj strani ključ. U ovom slučaju, kolona na koju primenjujemo ključ je kolona tabele mojatabela.gradid. Pošto se ključ postavlja na ovoj tabeli, stavljamo je u sledeću sintaksu fk_gradid(gradid). Zatim, nakon ključne reči references, imenujemo i tabelu i kolonu na koju će se strani ključ oslanjati. gradovi(gradid).
Na kraju, možemo (a ne moramo) postaviti i dodatne parametre foreign key ograničavaču. To su: on delete i on update osobine.
Ukoliko dođe do intervencije na redu na koji referencira neki strani ključ, moguće je izazvati i reakciju svih redova svih tabela koje su referencirane ovim redom. Ove reakcije mogu biti kaskadne, isključene ili podešene na null. Ukoliko su kaskadne, dogodiće se upravo ono što smo opisali u ovom pasusu: sva deca će dobiti svojstvo roditelja. Ako su isključene, neće se dogoditi ništa, što može dovesti i do kasnije povrede relacionog integriteta. Ako su podešene na null, onda će svaki red dete, koje više nema roditeljski red (ili je roditeljskom redu izmenjena vrednost), dobiti vrednost null:
ON DELETE NO ACTION
ON UPDATE NO ACTION
Ako napravite ovaj strani ključ i pokušate ponovo da izvršite malopređašnji upit:
insert into mojatabela values (‘1234567890123′,’petar’,’petrovic’,15)
dobićete sledeću poruku:
Cannot add or update a child row: a foreign key constraint fails (`mojabaza`.`mojatabela`, CONSTRAINT `fk_gradid` FOREIGN KEY (`gradid`) REFERENCES `gradovi` (`gradid`))
Odnosno, neće Vam biti dozvoljeno da unesete red, zbog toga što taj unos krši referencijalni integritet utvrđen stranim ključem.
Strani ključ možete definisati i putem GUI-ja.
Aktivirajte tabelu mojatabela (edit table) i odaberite opciju foreign keys (u donjem delu prozora). Pritisnite oznaku + nakon čega ćete uneti naziv ograničavača (na primer fk_gradid):
Nakon toga, ukoliko već ne postoji istoimeni ključ, ovaj ključ će biti aktiviran.
Ostaje samo da podesite tabelu na koju će biti referenciran (padajući meni ref. table u desnom delu polja), kolone koje će biti referencirane i dodatne opcije (on delete i on update).
U programu MySql Workbench, strani ključ možete kreirati na sledeći način:
Aktivirajte opciju Alter Table za tabelu mojatabela, a zatim odaberite panel Foreign Keys.
U listu sa leve strane, unesite novi nazi ključa: fk_gradid i odaberite referenciranu tabelu (gradovi). Zatim, u listi sa leve strane, odaberite kolonu koja će biti referencirana, i kolonu na koju će se referencirati.
Ne zaboravite da obe kolone u obe tabele moraju imati iste karakteristike kako bi referenciranje bilo uspešno izvršeno.
Relacija »više na više« (n:n)
Relacija više na više (many to many), poznata je još i kao relacija n:n. Ova relacija ne predstavlja ništa što već nismo pomenuli. Jedino što ide korak dalje, koristeći logiku koju smo upravo naučili.
Problematiku ćete, jednostavno, shvatiti ako pokušata da naredne dve tabele povežete tako da jedan korisnik može živeti u više gradova i jedan grad može imati više korisnika. Ako pokušate to da uradite putem referencijalnog integriteta, videćete da je nemoguće.
Zato se, kako bi se ostvarila n:n relacija, u relacionim bazama podataka najčešće uvodi još jedna tabela, koja će služiti samo tome da poveže jednu tabelu sa drugom.
Ova tabela se, obično, naziva posledična tabela, jer je posledica relacije između dve tabele. Ali, često se događa da ovakva tabela sadrži i neke dodatne podatke, osim same relacije.
Na primer (ovaj primer je veoma čest u praksi), postoji baza prodavnice. U prodavnici postoje proizvodi koji se, naravno, prodaju. Takođe, razumljivo, ova prodavnica izdaje račune, na kojima se nalaze proizvodi koje je neko kupio. Očigledno je da bi baza te prodavnice sadržala sledeće tabele:
-
proizvodi
-
računi
Međutim, jedan račun može sadržati više proizvoda, a jedan proizvod može se, takođe, naći na više računa. Kako ih povezati? Jednostavno: uvođenjem još jedne tabele, koja će slulžiti samo za povezivanje. Ta tabela se, na primer, može zvati: detaljikupovine.
Kolone koje bi ova tabela morala da sadrži su:
-
detaljikupovineid
-
sifraproizvoda
-
brojracuna
-
kolicina
Na ovaj način, nigde ne dolazi do ponavljanja (redundantnosti) podataka i kada to uradimo, možemo reći da smo normalizovali podatke (tabele).
Pojam normalizacije podrazumeva malo šire objašnjenje (postoje različite forme normalizacije), ali za nas je, u ovom trenutku, dovoljno da znamo na koji način da se oslobodimo nepotrebnog gomilanja podataka. To je upravo ono sto normalizacija jeste.
Ali, nije ni normalizacija uvek dobro rešenje. Više spojenih tabela daju nešto slabije performanse prilikom pretrage, pa je ponekad bolje spojiti više podataka u jednu tabelu (denormalizacija). Ipak najčešće ćete ipak rešavati skladištenje normalizacijom, a optimizaciju dobrim indeksima, upitima i funkcionalnošću.
Tabelu možete obrisati iz GUI-ja ili naredbom drop table imetabele
