Podatkovne baze in osnove programa Microsoft Access 2007
Podatkovne baze in osnove programa Microsoft Access 2007 2. del: Access – kreiranje tabel Bor Plestenjak Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport.
Glavno kazalo Microsoft Access – priprava baze Access – povezovanje tabel Access – iskanje podatkov in filtri Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 2
Microsoft Access 2007 n n n Kot zgled programa za delo z podatkovnimi bazami si bomo pogledali program Microsoft Access 2007. Ker so programi za delo z bazami dokaj podobni, ni težko začeti uporabljati drug program, ko poznamo enega. Glavne značilnosti programa Microsoft Access 2007 so: n n V samem programu lahko definiramo tabele, povezave in vnašamo podatke. Definiramo lahko poizvedbe s katerimi pridemo do raznih podatkov ali pa spreminjamo njihovo vrednost. S pomočjo vgrajenih čarovnikov lahko naredimo preproste obrazce in poročila (znanje programiranja ni potrebno). Zapletenejše programe lahko sestavimo s programiranjem v jeziku Visual Basic for Applications, ki je skupni za celotni Microsoft Office. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 3
Zagon programa Ob zagonu programa se pojavi okno z izbiro baze podatkov: n n New Blank Database: začnemo z prazno bazo. Določiti moramo ime datoteke in jo shraniti na disk. Templates: Iz seznama predlogov izberemo bazo, ki ustreza našim potrebam. Poleg lokalnih je na voljo tudi mnogo dodatnih predlogov iz medmrežja. Open recent database: Odpremo eno izmed baz, ki smo jih nazadnje uporabljali. Access bazo podatkov (tako podatke, kot tudi pripadajoče poizvedbe, obrazce in poročila) shrani v eni datoteki s končnico accdb. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 4
Glavni meni n Ko odpremo bazo podatkov oz. določimo datoteko za novo bazo, se v vmesniku Office Fluent pojavijo naslednji zavihki za delo z bazami podatkov: n n Home: tu poleg orodij za urejanje videza besedila najdemo še orodja za iskanja po tabelah (filtri) Create: tu so orodja za kreiranje glavnih sestavnih delov baze podatkov: tabel, obrazcev (forms), poročil (reports) in poizvedb (queries). External Data: orodja za uvoz in izvoz podatkov iz drugih baz podatkov oz. datotek Database Tools: dodatna orodja za delo z bazo podatkov, kot so makroji, prikaz in urejanje relacij, kreiranje samostojne aplikacije in ostalo. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 5
Podokno za krmarjenje n n Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Za lažji dostop do gradnikov baze podatkov imamo na levi strani zaslona podokno za krmarjenje, kjer vidimo seznam tabel, poizvedb, obrazcev in poročil, ki sestavljajo bazo podatkov. Gradnik se odpre na desni strani, če dvakrat kliknemo na njegovo ime v podoknu za krmarjenje. Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 6
Kazalo Microsoft Access – priprava baze Access – povezovanje tabel Access – iskanje podatkov in filtri Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 7
Nova tabela Za novo tabelo na zavihku Create v skupini Tables izberemo enega izmed naslednjih načinov: n n n Table. Odpre novo tabelo v obliki preglednice, kamor vnašamo podatke. Posamezna polja lahko preimenujemo z dvojnim klikom na njegovo ime. Odvečne prazne stolpce in vrstice bo program na koncu odstranil, iz tipov vnešenih podatkov pa bo poskusil uganiti tipe posameznih polj. Že obstoječo tabelo odpremo v tem načinu tako, da dvakrat kliknemo na njeno ime v podoknu za krmarjenje. Table Design. V tem načinu določimo imena in lastnosti posameznih polj tabele, podatke pa vstavljamo kasneje v načinu Table. Lastnosti polj lahko nastavimo veliko bolj natančno, kot bi jih program sam določil iz vnešenih podatkov. Table Templates. Tabelo izberemo iz seznama predlogov, ki so na voljo. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 8
Design View za urejanje tabele n V načinu design za vsako polje določimo njegovo ime, tip podatka in njegove lastnosti. Nastavimo lahko tudi osnovne vrednosti, izmed katerih lahko izbiramo vrednost polja, če gre za takšno polje. Če pa gre za povezavo z drugo tabelo, lahko določimo, kaj naj se prikazuje na mestu reference. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 9
Glavne lastnosti polja n n Ime polja (Field Name) ima lahko maksimalno 64 znakov. Tip polja (Data Type) je lahko: n n n Text: kratko besedilo (do 255 znakov) Memo: daljše besedilo Number: število Date/Time: čas in datum Currency: denarni znesek Auto. Number: avtomatično enolično določeno število Yes/No: logična vrednost OLE object: predmet iz drugega programa, npr. slika, preglednica, . . . Hyperlink: spletna povezava Lookup. Wizard: to je čarovnik za določanje vnosa podatkov v izbrano polje, kadar ima polje le končno mnogo vrednosti, ki jih določimo ali sami ali pa so to vse vrednosti izbranega stolpca v izbrani tabeli. V opis (Description) lahko shranimo opis polja, ki se potem izpisuje v obrazcih za vnos podatkov. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 10
Velikost polja Spodaj lahko nastavimo še dodatne možnosti. V General imamo: Field Size: velikost polja. Pride v poštev pri poljih tipa Text (število znakov), Number in Auto. Number, kjer so možnosti: n n n n Byte: 8 -bitno celo število Integer: 16 -bitno celo število Long Integer: 32 -bitno celo število Single: realno število v enojni natančnosti. Double: realno število v dvojni natančnosti. Decimal: Z dodatnima parametroma Precision in Scale določimo število vseh cifer in število decimalk. Replication ID: 128 -bitno celo število. To pride v poštev pri polju Auto. Number (ki je lahko tipa Long Integer ali Replication ID) oz. polju, ki je referenca na polje tipa Auto. Number. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 11
Dodatne lastnosti polja n n n New Values: način določanja novih vrednosti (za polje Auto. Number) Format: način izpisa podatkov Decimal Places: število decimalnih mest pri izpisu števil Input Mask: vnosna maska Caption: naslov polja (za prikaz v obrazcu) Default Value: privzeta vrednost Validation Rule: pravilo, ki opisuje dovoljene vrednosti Validation Text: besedilo, ki se pokaže v primeru nedovoljene vrednosti Required: ali je polje obvezno ali ga lahko pustimo praznega Allow Zero Length: ali lahko vnesemo prazno besedilo Indexed: ali naj se indeksira po tem polju Dodatne lastnosti, ki so na voljo, so odvisne od tipa polja, zato vse niso na voljo za vse tipe. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 12
Primarni ključ n n n Primarni ključ (primary key) je polje ali več polj, katerega podatki enolično določajo vsak zapis v tabeli. Primarni ključ ni obvezen, ga pa potrebujemo pri povezovanju tabel. Definiramo ga tako, da označimo eno ali več polj in v meniju izberemo Primary Key. Pri zapiranju nove tabele program pogleda, če imamo primarni ključ in če ga ni, predlaga, da doda novo polje tipa Auto Number za primarni ključ. Polja, ki sestavljajo primarni ključ, so označena na levi strani z znakom Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 13
Vnos podatkov n n n Osnovni način vnosa podatkov v tabelo je v obliki preglednice (Datasheet View). Če v podoknu za krmarjenje v skupini Tables dvakrat kliknemo na ustrezno tabelo, se ta prikaže v obliki preglednice, kjer vidimo podatke in lahko dodajamo nove. Med zapisi se lahko sprehajamo tudi v statusni vrstici, kjer se lahko premaknemo na izbrani zapis ali uporabimo gumbe za premik na začetek, konec seznama, predhodnji zapis, naslednji zapis ali na naslednji novi zapis. Zapis na katerem trenutno smo, je na levi označen s pobarvanim kvadratom. Če smo na koncu tabele, lahko vnesemo nov zapis. Znaki + se pojavijo, kadar smo v glavni tabeli, ki je povezana z drugo tabelo. Če pritisnemo na +, se pojavijo povezani zapisi iz druge tabele. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport.
Vnos podatkov n n n Zapis označimo tako, da kliknemo na kvadratek pred zapisom. Polja zapisa popravljamo tako, da kliknemo vanje in popravimo vrednost. Ko imamo zapis označen, ga lahko zbrišemo (Edit/Delete) ali pa kopiramo (Edit/Copy, Insert/New Record, Edit/Paste) v nov zapis (to je koristno, če je veliko podatkov enakih, saj jih tako ni potrebno dvakrat vnašati). Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 15
Kazalo Microsoft Access – priprava baze Access – povezovanje tabel Access – iskanje podatkov in filtri Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 16
Vrsta vnosnega polja Pri nekaterih tipih podatkov lahko na kartici Lookup izberemo vrsto vnosnega polja (Display Control). Na vrsto imamo: n Check Box: potrditveni gumb za logične vrednosti n Text Box: polje za vnos besedila n List Box: seznam za izbiranje, kjer moramo nujno izbrati iz seznama n Combo Box: seznam za izbiranje, kjer lahko vnesemo tudi vrednost, ki je ni na seznamu Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 17
Nastavitve seznama za izbiranje n Row Source Type: izvor vrednosti, med katerimi lahko izbiramo: n n n Row Source: določimo poizvedbo (v obliki stavka SQL ali preko čarovnika) ali naštejemo možne vrednosti Seznam za izbiranje je lahko sestavljen iz več stolpcev: n n n Table/Query: iz druge tabele ali poizvedbe Value List: predpišemo jih sami Field List: imena polj Bound Column: indeks stolpca iz katerega vzamemo vrednost za polje Column Count: število vseh stolpcev Column Heads: imena stolpcev (da ali ne) Column Widths: širine stolpcev Za Combo Box imamo še: n n n List Rows: koliko vrednosti iz seznama se naenkrat pojavi na zaslonu List Width: širina seznama Limit To List: ali mora vrednost biti nujno iz seznama (da ali ne) Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 18
SQL nastavitev seznama za izbiranje n n V primeru, ko vrednosti poiščemo iz druge tabele oziroma poizvedbe, v polje Row Source vpišemo polja, ki naj se prikažejo kot vrednosti polja pri izpisu oziroma vnosu. Denimo, da imamo v tabeli stolpec ID Dijak tipa Number, ki je povezava s tabelo Dijaki s stolpci ID Dijak, Ime, Priimek Row Source: SELECT [Dijaki]. [ID Dijak], [Dijaki]. [Ime]&” “&[Dijaki]. [Priimek] FROM [Dijaki]; Bound Column: 1 Column Count: 2 Column Widths: 0 cm; 5, 50 cm List Rows: 8 List Width: 6 cm n n Zgornje nastavitve pomenijo, da se v stolpcu kaže ime in priimek dijaka, podatek pa se vsakič pobere iz tabele Dijaki. V bazi je v tabeli na mestu stolpca ID Dijak številka, na zaslonu pa se izpiše ime in priimek, saj je tako nastavljeno na kartici Lookup. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 19
Povezovanje tabel Povezave med tabelami urejamo v posebnem oknu, ki ga odpremo v menuju Database Tools/Relationships. Ko okno prvič odpremo, moramo izbrati tabele, med katerimi bodo povezave, pozneje pa jih lahko dodamo z Relationships/Show Table. n n Vsaka tabela se v oknu pokaže v obliki manjšega okna, v katerem je seznam njenih polj, primarni ključ pa je izpisan krepko. Povezavo vrste ena proti več naredimo tako, da polje tabele na strani ena z miško povlečemo na ustrezno polje tabele na strani več. Pojavi se novo okno, v katerem lahko določimo še ostale lastnosti povezave in jo nato z ukazom Create tudi dokončno naredimo. Povezava se prikaže kot tanka črta med poljema, ki ju povezuje. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 20
Referenčna integriteta n n V oknu za lastnosti povezave lahko izberemo vrsto povezave (Join Type). Če izberemo Enforce Referential Integrity, bo program skrbel za referenčno integriteto povezanih podatkov. V tem primeru ni možno vstaviti zapisa z referenco, ki je ni v glavi tabeli in ni možno odstraniti zapisa iz glavne tabele, če ne odstranimo prej tudi vse povezane zapise v drugih tabelah, razen pri izbiri: n n Cascade Update Related Fields: ob spremembi primarnega ključa program sam popravi vse reference na ta zapis v povezanih tabelah. Cascade Delete Related Records: pri brisanju zapisa iz glavne tabele program odstrani vse zapise v povezanih tabelah, ki se nanašajo nanj. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 21
Kako deluje referenčna integriteta ID n n n Dijak Letnik ID Razrednik 084 Janez Novak 1990 1 1 2. a Polona Jan 117 Miha Mavec 1990 1 2 2. b Jasna Cevc 123 Žan Klopec 1991 3 3 1. a Zvone Hiti 167 Janez Novak 1990 2 4 1. b Karel Nagode V primeru referenčne integritete iz desne tabele ni moč brisati zapisa z ID 2, v levo tabelo pa ne moremo vstaviti ID Razred različen od 1, 2, 3, 4. Če imamo Cascade Update Related Fields in spremenimo v desni tabeli ID iz 2 v 5, se to spremeni tudi v levi tabeli. Če imamo Cascade Delete Related Records in v desni tabeli zbrišemo zapis z ID 2, se v levi tabeli zbrišejo vsi zapisi z ID Razred 2. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 22
Kazalo Microsoft Access – priprava baze Access – povezovanje tabel Access – iskanje podatkov in filtri Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 23
Urejanje podatkov n n Zapise v tabeli lahko uredimo po poljubnem stolpcu. Najprej označimo stolpec, po katerem želimo urediti podatke (kliknem na ime stolpca) in nato izberemo v Sort & Filter naraščajoč (A->Z) ali padajoč (Z->A) vrstni red. Sama ureditev zapisov na zaslonu ne vpliva na ureditev v tabeli v bazi. Pri večjih tabelah bo urejanje hitrejše, če je stolpec, po katerem urejamo, indeksiran. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 24
Iskanje podatkov n n Ko je tabela odprta v obliki preglednice, lahko z izbiro Edit/Find iščemo zapise podobno kot pri ostalih Okenskih programih. Find What: besedilo, ki ga iščemo. Look In: ali iščemo po trenutno označenem polju ali pa po vseh poljih. Match: Možna ujemanja so: n n n Any Part of Field: iskano besedilo je lahko kjerkoli v polju, Whole Field: iskano besedilo se mora točno ujemati z vsebino polja, Start of Field: vsebina polja se začne z iskanim besedilom. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 25
Zamenjava podatkov n n Podobno kot iskanje podatkov poteka tudi zamenjava besedila z novim besedilom. Replace With: besedilo, ki zamenja iskano besedilo. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 26
Filtri n n n Filtre uporabljamo, kadar želimo na zaslonu v preglednici videti le tiste zapise, ki ustrezajo danemu kriteriju. Za zapletenejše kriterije uporabljamo poizvedbe, pri enostavnejših pa si lahko pomagamo s filtri. Najenostavnejši filter je po označenem polju (Filter By Selection), kjer pridejo v poštev tisti zapisi, ki imajo vrednost polja. Izberemo ga bodisi v Sort & Filter/Selection ali pa z klikom na desni gumb miške). Možnosti so: n n n enaka vrednost kot označeno polje (Equals. . . ), različno od označenega polja (Does Not Equal. . . ) (za števila) večje (manjše) od označenega polja (Less Than or Equal to. . . , Greather Than ot Equal to. . . ) (za tekstovna polja) vsebujejo oz. ne vsebujejo danega niza (Contains. . . , Does Not Contain. . . ) Če želimo narediti filter po več kot enem polju, uporabimo filter preko obrazca (Advanced/Filter By Form). Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 27
Zapisi z enako vrednostjo v označenem polju Označimo polje in izberemo Sort & Filter/Selection/Equals. Pokaže se samo tisti del tabele, kjer imajo zapisi enako vrednost označenega polja. V statusni vrstici je sedaj tabela označena z Filtered. n n n Na koncu filter izklopimo z Sort & Filter/Clear Filter From. . Druga možnost je, da kliknemo na napis Filtered v statusni vrstici. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 28
Zapisi z drugo vrednostjo v označenem polju n n Označimo polje in izberemo Sort & Filter/Selection/Does Not Equal to. . Pokaže se samo tisti del tabele, kjer imajo zapisi drugačno vrednost označenega polja. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 29
Zapletenejši filtri Z izbiro Sort & Filter/Advanced/Filter By Form lahko naredimo filter po več kot enem polju. n n n V vsako polje vpišemo iskano vrednost ali pa ga pustimo praznega. Uporabljamo lahko tudi operatorje za primerjavo >, <, =, <=, >= in <>. Več različnih kriterijev lahko združimo v kriterij, ki je izpolnjen, kadar zapis ustreza vsaj enemu izmed kriterijev. Ni možno poiskati zapisa, ki bi ustrezal več kriterijem hkrati. Če želimo npr. poiskati vse zapise, ki imajo vrednost polja med dvema danima vrednostima, moramo uporabiti poizvedbo. Fakulteta za matematiko in fiziko http: //up. fmf. uni-lj. si Izvedbo projekta je omogočilo sofinanciranje Evropskega socialnega sklada Evropske unije in Ministrstva za šolstvo in šport. 30
- Slides: 30