Monday, 09.09.2013 – Zidar
Svi koji se bavimo bazam podataka znamo sta je kljuc – minimalan skup atributa koji jedinstveno odredjuje entorke (tuples) koji cine telo posmatrane relacije. Minimalan znaci da ako izbacimo neki element iz kljuca, preostali skup vise nije kljuc, jer se gubi jedinstvenost. Kljuc genralno ima vise atributa, a cesto tacno jedan. Sta je onda super-kljuc? Super kljuc je – prosireni kljuc, to jest kljuc kome smo dodali jedan ili vise atributa.
U teoriji, super kljucevi su veoma vazni. A sta nam u praksi znace superkljucevi? Zar nije dovoljno strasno sto ponekad moramo da koristimo kljuceve od sastavljene od vise atributa? Zasto bi dodavali kljucu jos atributa? Ispostavlja se das u superkljucevi veoma korisna stvar i u praksi.
Dodavanjem atributa kljucevima ne narusava se jedinstvenost. Ako nekakav ID jedinstveno odredjuje redove u nakoj tabeli, onda i kombinacija (ID, Ime) isto tako jedinstveno odredjuje redove u tabeli. Ovo pak znaci da super kljuc mozemo da upotrebimo umesto kljuca, ako nam je tako zgodno.
Pokazacemo dva primera gde superkljucevi znacajno olaksavaju odrzanje integriteta i kvaliteta podataka koje cuvamo u bazi.
Primer 1 – generalizacija.
Neka imamo relaciju koja sdrzi podatke o knjigama u biblioteci. Predikat P koji opisuje relaciju glasi:
P = “Knjiga sa identifikacioniom brojem [ISBN] ima naslov [Title] cuva se u formatu [Format]” . Kljuc relacije je [ISBN]. Format moze imati vrednosti iz skupa {‘mp3′,’printed’}
Iz ovog predikata se mogu izvesti sledece propozicije (iskazi) koji cine telo relacije:
Knjiga [ISBN=9000] ima naslov [Title=’An Introduction To Database Systems’] i cuva se u formatu [Format=’mp3′]
Knjiga [ISBN=75284] ima naslov [Title=’Database Design for Mere Mortals’] i cuva se u formatu [Format=’printed’]
Knjiga [ISBN=53495] ima naslov [Title=’SQL Antipatterns’] i cuva se u formatu [Format=’printed’]
Umesto da pisemo iskaze koji odgovaraju datom predikatu, naravno da je lakse prikazati sve u tabelarnoj formi:
RELATION Books:
| [ISBN] |
[Title] |
[Format] |
| 9000 |
An Introduction To Database Systems |
mp3 |
| 75284 |
Database Design for Mere Mortals |
printed |
| 53495 |
SQL Antipatterns |
printed |
Primary Key PK = {ISBN}
Naslovni red u tabeli predstavlja zaglavlje (heading) relacije, a redovi sa podacima predstavljaju telo relacije. Tablea koju smo nacrtali je slika relacije R koju smo definisali predikatom P.
Posto knjige dolaze u razlicitim formatima (tacno dva u nasem slucaju), mi zelimo da za razlicte formate cuvamo razlicte informacije. Za format ‘printed’ hocemo da cuvamo broj stranica i vrstu poveza. Za format ‘mp3’ hocemo da cuvamo velicnu u kilobajtima. Da to postignemo, mozemo da napravimo jos dve relacije, sa ovakvim predikatima:
Predikat PS: “Knjiga [ISBN] ima [Pages] stranica i ima povez [Binding]”
Predikat PMP3: “Knjiga [ISBN] zahteva [KB] kilobajta na elektronskim medijima”
Slike relacija koje definisanih predikatima PS i PS3 izgledale bi ovako:
RELATION PrintedBooks:
| [ISBN] |
[Pages] |
[Binding] |
| 75284 |
612 |
paper-back |
| 53495 |
334 |
hard cover |
Primary key PK = {ISBN}
Foreign Key FK = {ISBN} REFERENCES Books {ISBN}
RELATION BooksMP3:
Primary key PK = {ISBN}
Foreign Key FK = ISBN REFERENCES Books {ISBN}
Relacije su perfektno normalizovane i sve izgleda OK. Ipak, imamo jedan ozbiljan problem. Ja sam pazljivo uneo stampane knjige u relaciju PrintedBooks i knjigu ISBN = 9000 u relaciju BooksMP3. Da nisam bio pazljiv, mogao sam da unesem knjige u pogresne relacije. Kako da garantujem da ce u PrintedBooks biti unesene samo one knjige koje su u format ‘printed’, I das vi MP3 idu u relaciju BooksMP3? Odgovor je – super kljuc, uz jedan CHECK constraint.
Na relaciji Books, definisacemo super kljuc SK1 = {ISBN,Format}. Relacija sada izgleda ovako:
RELATION Books:
| [ISBN] |
[Title] |
[Format] |
| 9000 |
An Introduction To Database Systems |
mp3 |
| 75284 |
Database Design for Mere Mortals |
printed |
| 53495 |
SQL Antipatterns |
printed |
Primary Key PK = {ISBN}
Super key SK1: UNIQUE {ISBN,Format}
Relacije PrintedBooks I BooksMP3 prosiricemo zap o jedan novi atribut – [Format], ovako:
RELATION PrintedBooks:
| [ISBN] |
[Pages] |
[Binding] |
[Format] |
| 75284 |
612 |
paper-back |
printed |
| 53495 |
334 |
hard cover |
printed |
Primary key PK = {ISBN}
Foreign Key FK = {ISBN} REFERENCES Books {ISBN,Format}
CHECK [Format] = ‘printed’
RELATION BooksMP3:
| [SBN] |
[KB] |
Format |
| 9000 |
12350 |
mp3 |
Primary key PK = {ISBN}
Foreign Key FK = {ISBN} REFERENCES Books {ISBN,Format}
CHECK [Format] = ‘mp3’
Superkljuc SK1 na relaciji Books treba nam da bi mogli da uspostavimo Foreign Key u relacijama PrintedBooks i BooksMP3. CHECK constraints garantuju da samo knjige sa korektnim formatom mogu da idu relacije PrintedBooks i BooksMP3.
Da budemo inzenjerski precizni, trebalo bi da azuriramo i predikate koji idu uz tabele PrintedBooks i BooksMP3, ovako:
Predikat PS: “Knjiga [ISBN] je formata [Format=’printed’] , [Pages] stranica i ima povez [Binding]”
Predikat PMP3: “Knjiga [ISBN] cuva se u formatu [Format=’mp3′] i zahteva [KB] kilobajta na elektronskim medijima”
Slucaj koji smo opisali zove se generalizacija, preciznije “Relacija Books je generalizacija relacija PrintedBooks i BooksMP3 po atributu [Format]”
Primer 2: Dozvoljeni iznos za povlacenje na bankovnoj masini
Svaki vlasnik tekuceg racuna moze da na bankovnoj masini povuce odredjenu sumu novca. Iznos koji se moze povuci u jednoj transakciji odredjuje se za svaki racun posebno. Kako garantovati da niko ne moze u jednoj transakciji da povuce vise nego sto mu je dozvoljeno? Narvno – uz pomoc super kljcuceva.
Predikat R1: “Racun broj [AcctNo] ima ogranicenje za podizanje novca u jednoj transakciji of [MaxAmt] dinara”
RELATION Accounts:
| [AcctNo] |
MaxAmt |
| A1 |
100 |
| A2 |
150 |
| A3 |
50 |
Primary Key PK = {AcctNo}
Predikat R2: “U transakciji [TrasnsID] u datum i vreme [TransDate] sa racuna [AcctNo] podignuto je [Amt] dinara”
RELATION: Transactions
| [TransId] |
[TransDate] |
[AcctNo] |
[Amt] |
| T1 |
D1 |
A1 |
70 |
| T2 |
D2 |
A2 |
130 |
| T3 |
D1 |
A3 |
45 |
Primary key PK =:{ TransID}
Alternate Key AK1 = {[AcctNo],[Transdate]}
Foreign Key FK1 = {[AcctNo]} REFERENCES Accounts {[AcctNo]}
Ponovo je sve perfektno normalizovano. I ponovo imamo problem – kako garantovati da relacija nece prihvatiti vrednosti vece od dozvoljenih u atributu [Amt]?
Uradicemo istu stvar kao u slucaju sa generalizacijom:
- Uvodimo super kluc u relaciji Accounts, {AcctNo, MaxAmt}
- Dodajemo atribut [MaxAmt] u relaciju Transactions
- Azuriramo Foreign Key u relaciji Transaction
- Dodajemo CHECK constraint u relaciju Accounts koji poredi [Amt] se [MaxAmt]
Konacne relacije izgledace ovako:
RELATION Accounts:
| [AcctNo] |
[MaxAmt] |
| A1 |
100 |
| A2 |
150 |
| A3 |
50 |
Primary Key PK = {AcctNo}
Super Key SK1 = {AcctNo, MaxAmt}
RELATION: Transactions
| [TransId] |
[TransDate] |
[AcctNo] |
[Amt] |
[MaxAmt] |
| T1 |
D1 |
A1 |
70 |
100 |
| T2 |
D2 |
A2 |
130 |
150 |
| T3 |
D1 |
A3 |
45 |
50 |
Primary key PK =:{ TransID}
Alternate Key AK1 = {[AcctNo],[Transdate]}
Foreign Key FK1 = { [AcctNo],[MaxAmt]} REFERENCES Accounts ([AcctNo],[MaxAmt]}
CHECK [Amt]<=[MaxAmt]
Azurirani predikati izgledaju ovako:
Predikat R2: “U transakciji [TrasnsID] u datum i vreme [TransDate] sa racuna [AcctNo] podignuto je [Amt] dinara, sto je manje od dozvoljenih [MaxAmt] dinara”
U oba slucaja koja smo pokazali, konacne relacije su donekle denormalizovane. Uveli smo redundansu – [MaxAmt] se nalazi u relaciji [Transactions], gde mu nije mesto, ako cemo da doslovno ispostujemo Boyce-Codd normalnu formu. To je cena koju smo morali da platimo da bismo garantovali veoma vazna poslovna pravila. Ovo se moze nazvati i ‘kontrolisana denormalizacija’. Konacan rezultat je skup tabela koje bolje predstavljaju realnost nego formalno perfektno normalizovana shema.
Super kljucevi su ponekad zaista super stvar.
Objavljeno u: Best practice, Modelovanje podataka, MS Access, MS SQL Server, Problemi i rešenja, SQL | Nema komentara »
