RĪGAS TEHNISKĀ UNIVERSITĀTEDatorzinātnes un informācijas tehnoloģijas fakultāte

Lietišķās datorzinātnes institūts

4. praktiskais darbsmācību priekšmetā

“Multibāzes”

Globālās shēmas realizācija

Izstrādāja: Kaspars Graudiņš; Kristaps Vilcāns<I DB3 1, 121RDB514>

2015./16. māc. gads

SatursDatu avotu izveide..........................................................................................................3

Pirmā datubāze (IT struktūrvienība)..........................................................................3

Otrā datubāze (Grāmatvedības struktūrvienība)........................................................5

Trešā datubāze (Biznesa struktūrvienība)..................................................................8

Globālās Shēmas kā ceturtās datubāzes izveide...........................................................11

Datubāžu saišu izveide.............................................................................................11

Pirmajā praktiskajā darbā definēto skatu izveide.....................................................12

EMP_INFO..........................................................................................................12

WO_INFO............................................................................................................14

CONTRACTS......................................................................................................15

REPORT_ORDERS.............................................................................................16

TRANSACTIONS...............................................................................................17

SELECT, UPDATE, DELETE, INSERT atbalstošu skatu izveide.....................18

Tiesību piešķiršana...................................................................................................19

Secinājumi....................................................................................................................23

Datu avotu izveideKā jau šī priekšmeta pirmajā praktiskajā darbā tika definēts, tad šī darba ietvaros tiks

izveidotas 3 atsevišķas datubāzes, kuras saturēs informāciju par organizācijas 3

filiālēm. Katrai no tām ir sava datubāze ar saviem datiem. Lai varētu tos apvienot,

globālā shēma būs ceturtā datubāze, kura saturēs lietotāju ar kuru jebkurš lietojums

var pieslēgties un izmantot datus no visām 3 filiālēm.

Darbs tiek realizēts izmantojot Oracle Database versiju 12c. Visas trīs datubāzes būs

izveidotas uz viena datora, lai saglabātu darba vienkāršību un pārskatāmību, bet

risinājums jebkurā gadījumā būtu izmantojam kamēr Globālā Shēmas datubāze “redz”

pārējās trīs.

Jau pirmajā darbā tika aplūkota kāda būs datu struktūra katrā no trīs datubāzē, bet

vienkāršības dēļ atkārtosim.

Pirmā datubāze (IT struktūrvienība)Šo datubāzi izmanto organizācijas IT struktūrvienība.

Tabulu diagramma:

Tabulu diagramma

Jaunizveidoto struktūru uzreiz jāaizpilda ar datiem, lai pēc visu DB izveidošanas var

uzreiz ķerties klāt globālajai shēmai. Tā kā tabulas ir salīdzinoši daudz un kopējais

datu ievades daudzums liels, tad katra tabulā tiks ievietoti tikai tik daudz dati, lai gūtu

priekšstatu par multibāzes darbību.

Dati:

Tabulas: EMPLOYEES; WORKSTATIONS; EMP_TO_WO

Tabulas: ORDERS; TRANSACTIONS; CONTRACTS

Tabulā Employees ir informācija par darbinieku. Tabula Workstation satur

informāciju par filiālē esošajām darbstacijām. Sasaiste starp šīm tabulām notiek ar

EMP_TO_WO tabulas palīdzību, kura satur lietotāja ID un darbstacijas ID pāri.

IT filiāles pakļautībā ir savi konti no kuriem var norēķināties par noslēgtiem

kontraktiem vai pasūtījumiem. Tabula Transactions satur informāciju par

pārskaitījumu, kā arī tā tipu. Tips var norādīt vai nu, ka maksājums ir par pasūtījumu

(tabula Orders) vai noslēgtu līgumu/vienošanos (tabula Contracts).

Otrā datubāze (Grāmatvedības struktūrvienība)Tabulu diagramma:

Tabulu diagramma

Dati:

Tabulas: DARBSTACIJAS; DARBINIEKI; KONTAKTU_KARTE; ATSKAITES

Tabulas: KONTU_BILANCE; PARSKAITIJUMI; PARSKAITIJUMI_ARHIVS

Tabula Darbinieki satur informāciju par filiālē strādājošajiem darbiniekiem, savukārt

Darbstacijas-informāciju par filiālē esošajām darbstacijām. Sasaiste starp darbstacijām

un darbiniekiem tiek īstenota Darbstacijas tabulā ar kolonnas darbinieka_id palīdzību.

Ja datori neviens neizmanto, tad šis lauks var būt null. Papildus informācija par

darbinieku tiek glabāta kontaktu_kartes tabulā. Tajā var atrast piemēram telefona

numuru un citu līdzīga tipa informāciju.

Grāmatvedības pārvaldība ir dažādi banku konti no kuriem var veikt maksājumus gan

par uzņēmuma saistībām, pasūtījumiem, kā arī izmaksāt algas utt. Kontu_bilances

glabās informāciju par kontu, tā bilanci un valūtu. Tālāk no šiem kontiem varēs

apmaksāt Parskaitijumi tabulā esošās saistības. Kad maksājums veiksmīgi izpildīts un

apstiprināts, tas nonāk Parskaitijumi_arhivs, lai lieki nepiepildītu galveno tabulu.

Grāmatvedības uzdevums ir ģenerēt dažādas atskaites. Tabula atskaites glabā

informāciju par tām. Papildus tās vēlāk varēs sasaistīt ar IT dienesta filiāle, jo tās

katram pasūtījumam nepieciešama sava atskaite no grāmatvedības.

Trešā datubāze (Biznesa struktūrvienība)Tabulu diagramma:

Dati:

Tabulas: DARBINIEKI; DATORI; VIZITKARTE; PILSETA; VALSTS

Tabulas: LIGUMI; MAKSAJUMI

Informācija par darbinieku izvietota kopā 4 tabulās, kuras sasaistot kopā, var iegūt

pilno informāciju. Tabula darbinieki satur pamatinformāciju un norādi uz tabulas

VIZITKARTE ierakstu. Tajā savukārt informācija par darbinieka adreses pilsētu tiek

izgūta no tabulas pilseta, kura valsti, savukārt izgūst no tabulas valsts. Šādā situācijā

pilsētu un valstu sarakstu ir viegli papildināt, kā arī izmantot priekš šajā piemērā

neapskatītas funkcionalitātes.

Papildus filiāle satur tabulu datori, kura glabā informāciju par darbstacijām. Šajā

filiālē sasaiste ar darbstaciju notiek tabulā darbinieki, kur tiek norādīts tās numurs. Šī

pieeja atšķiras no iepriekšējām divām. Biznesa galvenais uzdevums ir organizācijai

meklēt jaunus partnerus un ar tiem slēgt izdevīgus līgumus un vienošanās. Tas tiek

darīts papīra formātā, bet datubāzē tabulā LIGUMI tiek glabāta minimāla informācija,

lai varētu noteikt meklējamā līguma informāciju. Papildus bizness definē

nepieciešamos maksājumus, kuri jāveic. Tie atrodas tabulā MAKSAJUMI. Ja tie tiek

apstiprināti augstākajā līmenī, tad grāmatvedība var veikt pārskaitījumu.

Globālās Shēmas kā ceturtās datubāzes izveideKā jau nodaļas virsrakstā minēts, tad globālā shēma tiks īstenota ar ceturtās Oracle

datubāzes palīdzību, kura saturēs sasaisti ar pārējām trīs. Atslēgas elements

realizācijai būs datubāzes saite (database link) ar kuru tiks izveidoti nepieciešamie

skati, lai lietojums var caur vienu datubāzes lietotāju varētu piekļūt visiem datiem.

Visas sasaistes un skati tiks veidoti ar priviliģētu jeb administratora līmeņa lietotāju.

Šajā gadījumā tas būs System lietotājs. Autors nekādā gadījumā neatbalsta šī lietotāja

izmantošanu, bet, lai samazinātu sarežģītību un demonstrētu darbību, ar to pietiks.

Izmantošanai gatavajiem skatiem tiks piešķirtas tiesības uz lasīšanu lietotājam

SCOTT, kurš būs piekļuves punkts ārējiem lietotājiem globālajai shēmai. Bez pirmajā

praktiskajā darbā definētajiem skatiem, papildus katrai datu tabulai kādam no trīs

struktūrvienībā būs skats, kas to pilnībā reprezentēs. Šiem skatiem var piešķirt

speciālas tiesības, lai tikai augstāka līmeņa lietotājs spēj ar tiem darboties, jo tie atļaus

veikt UPDATE, INSERT, DELETE operācijas. Tas nodrošinās pilnīgu saišu

nerādīšanu gala lietotājam un tajā pašā laikā pilnu kontroli pār visām trīs datubāzēm.

Datubāžu saišu izveidePiemēra vajadzībām visām trīs struktūrvienību datubāzēm ir vienāds lietotājvārds, bet

parole ir vienāda ar DB nosaukumu. Datubāzes saite tiks veidota ar CREATE

DATABASE LINK komandu norādot ārējās datubāzes nosaukumu, lietotāju un

paroli. Publiskā saite netiks izmantota, jo lietojums tiks veidots šajā pašā lietotājā,

nevis kādā citā.

Datubāzes saites izveide ar IT struktūrvienību:

GS_DB2 ir saites nosaukums, SCOTT-lietotājvārds, db2-parole un DB2-datubāzes

nosaukums. Identiski izveido vēl divus savienojumus.

Datubāzes saites izveide ar Grāmatvedības struktūrvienību:

Datubāzes saites izveide ar Biznesa struktūrvienību:

Šajā momentā pats svarīgais ar tīri tehnisku sasaisti ir paveikt. Lai izgūtu , piemēram,

lietotāja datus no IT struktūrvienības ar globālās shēmas System lietotāju, SELECT

raksta šādi:

Vienīgā norāde, ka vaicājumā ietvertā tabula neatrodas ne esošā lietotāja ietvaros, ne

visas datubāzes, ir @ simbols aiz tabulas nosaukuma. Tas norāda, ka šī tabula jāmeklē

datu avotā, kuru definē GS_DB2, kas ir IT struktūrvienības datubāze.

Pirmajā praktiskajā darbā definēto skatu izveideEMP_INFOŠis skats satur informāciju par visiem darbiniekiem, kas strādā organizācijā. Datu

apvienošana no katras struktūrvienības tiek veikta ar UNION ALL, bet tāpat

nepieciešama pareiza vaicājuma izveide, jo katrā no struktūrvienībām nepieciešamā

informācija ar atainot citā struktūrā. Tāpat arī ir informācijas fragmenti, kas nav

vienā, bet ir otrā datubāzē. Tad jāsaprot, ka nav nepieciešamā informācija un ir

vajadzīgs atstāt tukšu lauku.

Skatā esošās kolonnas:

EPASTS

ADRESE

TELEFONA_NR

DZIMSANAS_DATUMS

DARBINIEKA_UZVARDS

DARBINIEKA_VARDS

DARBINIEKA_ID

STRUKTURVIENIBA

Vaicājums, lai šo informāciju izgūtu no visām struktūrvienībām:

Skata izveide:

Ir iegūts skats, kurš pilda savu funkcionalitāti un to izmantojot var aizmirst par

datubāžu saitēm. Piemērs:

WO_INFOLai gan iesākumā bija plāns iepriekšējo skatu papildināt arī ar darbstaciju informāciju,

to atcēla, jo tādā gadījumā izveidotos pārāk milzīga informācijas kvantitāte un būtu

grūtāk orientēties datos. Tādēļ tika nolemts informāciju par darbstacijām izcelt ārā

atsevišķā skatā ar sekojošajām kolonnām:

DARBINIEKA_ID

TIPS

PROCESORS

OS

MODELIS

RAZOTAJS

PIRKSANAS_DAT

DATORA_ID

STRUKTURVIENIBA

Vaicājums, lai šo informāciju izgūtu no visām struktūrvienībām un ievietotu

skatā:

Skata pārbaude:

CONTRACTSŠeit tiktu apkopota informācija par organizācijā slēgtajiem līgumiem. Tā kā

grāmatvedība šādu lietu nedara, tad jāapkopo informācija no IT un Biznesa

struktūrvienībām. IT pusē līgumam norāda termiņu no kura līdz kuram tas ir spēkā,

bet Bizness puse proceduārā veidā norāda uzreiz vai līgums ir Aktīvs vai nē, tad skatā

jāieliek loģika, ka, ja šodienas datums neietilpst IT līguma norādītajā periodā, tad tas

tiek uzskatīts par slēgtu. Skatā ir sekojošas kolonnas:

LIGUMA_STATUSS

LIGUMS_NOSLEGTS_AR

STRUKTURVIENIBA

Vaicājums, lai šo informāciju izgūtu no visām struktūrvienībām un ievietotu

skatā:

Skata pārbaude:

REPORT_ORDERSGrāmatvedības nodaļai ir aktīvi jāveido atskaites pēc pieprasījumiem. Tabulā

ATSKAITES tiek glabāti visi izpildītie un vēl pildāmie atskaišu pieprasījumi.

Atskaites statusu nosaka izpildes un nosūtīšanas datums. Ja datumi ir nākotnē, tad

atskaites statuss ir GAIDA. Papildus jāpiesaista IT nodaļas ORDERS tabula, kur par

katru pasūtījumu grāmatvedībai jāgatavo jauna atskaite. Tādēļ šīs abas tabulas var

apvienot vienā skatā ar šādām kolonnām:

STATUSS

NOSAUKUMS

ID

STRUKTURVIENIBA

Vaicājums, lai šo informāciju izgūtu no visām struktūrvienībām un ievietotu

skatā:

Skata pārbaude:

TRANSACTIONSKatra no struktūrvienībām var veikt savus darījumus ar sev piešķirtajiem līdzekļiem.

To arī viņas dara, bet nepieciešams vienots notikušo pārskaitījumu skats, kur būtu

apvienoti visi darījumi, lai organizācijas vadība labāk var saprast, kas notiek ar tās

finanšu līdzekļiem. Šai vajadzībai tika izveidots skats TRANSACTIONS ar šādām

kolonnām:

SUMMA

KONTS_UZ

KONTS_NO

TRANSAKCIJAS_INFO

ID

STRUKTURVIENIBA

Vaicājums, lai šo informāciju izgūtu no visām struktūrvienībām un ievietotu

skatā:

Skata pārbaude:

Līdz šim ir izveidoti nepieciešamie skati, kuri tika definēti pirmajā praktiskajā darbā.

Bet tagad tiks izveidoti dublējoši skati katrai struktūrvienības tabulai. Tas tiek darīts

ar mērķi izveidot objektu caur kuru, lietotājs var izpildīt UPDATE, DELETE,

INSERT operācijas reālajām tabulām kādā no struktūrvienībām. Pirms šī darba

izpildes tika plānots izmantot publisko sinonīmu, bet skatu izmantošana, kas piedāvā

visu nepieciešamo, likās interesantāka, jo datu manipulācijas caur viņiem vēl nekad

nav veiktas.

SELECT, UPDATE, DELETE, INSERT atbalstošu skatu izveideLai saglabātu pārskatāmību, tad pirms katra skata nosaukuma, priekšā pievienos

struktūrvienību. Piemēram, tabulas EMPLOYEES skata nosaukums būs

IT_EMPLOYEES. To veidošana tiks uzticēta SQL Developer Create New View

vednim, kas atvieglo skata veidošanu. Tā kā šis ir atkārtojošs darbs, tad demonstrēts

tiks tikai viens gadījums.

IT_WORKSTATIONSSQL Developer ar labo peles taustiņu klikšķina uz Skatu sadaļas un izvēlas New

View. Atveras veidne, kur jāaizpilda skata nosaukums un datu atlases vaicājums.

Pēc OK nospiešanas, automātiski tiek izveidots skats un darbs ir paveikts. Tālāk šis

pats jāatkārto visām pārējām tabulām. Uzdevums laikietilpīgs, bet rezultāts labs, jo

pie katra skata parādīsies opcijas, ka tas atbalsta UPDATE, DELETE un INSERT.

Nākamajā sadaļā piešķirsim globālās shēmas lietojuma apkalpojošajam klientam

tiesības uz jaunizveidotajiem skatiem.

Tiesību piešķiršanaGlobālās Shēmas datubāzes lietotājs SCOTT būs tas, kam būs nepieciešamās tiesības,

lai izmantotu izveidotos skatus. Tiesības var pārvaldīt administrators, kas ir ļoti

parocīgi, jo ar pāris komandu palīdzību lietotājam var piešķirt vai nepiešķirt piekļuvi

kādam no objektiem. Tiesību piešķiršana arī notiek caur SQL Developer veidnēm.

Jāatzīmē, ka skatiem šajā gadījumā jāpiešķir individuālas tiesības SCOTT lietotājam,

bet to var risināt ar lomu palīdzību. Visas tiesības apkopot dažādās lomās un ar vienu

vaicājumu piešķirt vai noņemt. Bet šajā praktiskajā darbā to darīs individuāli katram

skatam, lai demonstrētu lietojamību.

Piešķirsim skatam WORKSTATIONS SELECT tiesības lietotājam SCOTT. Lai to

izdarītu, atveram pašu skatu Developer un izvēlamies Grants sadaļu. Tālāk no

izvēlnes Actions ejam Privileges->Grant. No saraksta izvēlamies lietotāju SCOT un

piešķiram SELECT tiesības.

Kad tas izdarīts, tad jāpārbauda vai SCOTT var izmantot WORKSTATIONS skatu.

Jāatver savienojums ar lietotāju SCOTT un jāieraksta SELECT vaicājums uz minēto

skatu.

Kā redzams, tad vaicājumā joprojām jāizmanto pilnais ceļš līdz skatam, tajā ietverot

arī skata īpašnieku. No tā var atteikties izveidojot publisku sinonīmu ar System

lietotāju. Tā nepieciešamība jāizskata katrā sistēmā atsevišķi, bet pareiza piekļuves

tiesību politika neko nenodara, ja gala lietotājs arī zina priviliģētā lietotāja

nosaukumu.

Publisko sinonīmu izveido SQL Developer sadaļā klikšķinot New Public Synonym un

aizpilda veidni. Šajā gadījumā izveidosim publisko sinonīmu TRANSACTIONS, lai

nebūtu SCOTT lietotājam jāvada pilnais ceļš līdz skatam.

Pēc jaunā sinonīma izveides, lietotājam SCOTT vaicājumu izveide ir krietni

vienkāršāka, jo vairs nav jānorāda skata īpašnieks.

Kā attēlos redzams, tad kopumā viss strādā. Līdzīgi tiesības jāpiešķir arī pārējiem

skatiem, bet vēl būtu jāpamēģina SELECT, UPDATE, DELETE, INSERT skatu

piekļuves tiesību piešķiršana SCOTT. Tas notiek līdzīgi, kā ar iepriekšējo piemēru,

bet šoreiz bez SELECT vēl jāpiešķir arī DELETE, UPDATE un INSERT tiesības.

Šoreiz izvēlēsimies IT_WORKSTATIONS skatu.

Kad tiesības piešķirtas un sinonīms izveidots, tad jāpārbauda vai strādā ne tikai adatu

atlase, bet arī to maiņa. Lai to pārbaudītu, tiks izveidots vienkāršs UPDATE

vaicājums.

Pēc paziņojuma spriežot viss ir sanācis. Var reāli pārbaudīt pašu tabulu DB2

datubāzē.

Viss izdarītais ir nostrādājis. Tālāk atliek piešķirt nepieciešamās tiesības visiem

objektiem, lai lietotājs SCOTT var tiem piekļūt.

SecinājumiGrūtākā daļa darba izpildē bija pati plānošana un problēmsituācijas izveide.

Realizācija bija vienkārša un daudz problēmas nesagādāja pateicoties Oracle datubāžu

saitēm. Tādas izmantoju pirmo reizi, bet to izmantošana ir ļoti vienkārša un neliek

mainīt daudz vaicājumu izveidē. Papildus, saite ir gana universāla, jo ļauj sadarboties

ar Oracle heterogeneous services, kas attiecīgi dod iespēju to pielietot saziņai ar citām

datubāzēm, failiem. Tomēr jāuzsver, ka vislabāk tā darbojas homogēnā vidē, kā tas

tika īstenots šajā praktiskajā darbā.

Praktiskā darba daļa ļāva novērot “problēmu” ar Oracle datubāžu kopu uz datora, kas

nav serveris ar daudz resursiem. Kopumā problēmu nebija, bet bija momenti, kad

Oracle sāk pats veikt kādus uzturēšanas darbus fonā, kas pēkšņi datubāze kernel

procesa lietoto RAM apjomu no pāris simts megabaitiem uzsit uz 2,5 gigabaitiem. Ja

šādus uzkopes darbus sadomā izpildīt visas 4 datubāzes vienlaicīgi, tad dators kļūst

nelietojams, jo visa operatīvā atmiņa ir iztērēta un cietais disks pilda tās funkcijas.

Lai gan darbā darbojos ar Oracle datubāzēm un nekas daudz tajās nav atlicis

neizmantots, tad skati, kuri bez SELECT atbalsta arī UPDATE, DELETE un

INSERT, man ir kaut kas jauns. Līdz šim šķita, ka skats ir skats un paredzēts tikai

informācijas izguvei rafinētākā veidā no tabulas vai tabulām. Bet kā izrādās,

konkrētās situācijās, Oracle dod iespēju caur tiem veikt krietni vairāk operāciju.

Kopumā darbs bija interesants, bet laikietilpīgs. Vienkāršības ziņā tas ne tuvu nebija

tik sarežģīts, kā specializēto datubāžu mācību priekšmetā esošajos praktiskajos

darbos. Uzdevums būtu krietni plašāks, ja vēl būtu jāizstrādā kāda

lietojumprogramma, bet tas neko daudz nemainītu, jo tā slēgtos klāt izveidotajam

SCOTT lietotājam un izpildītu dažādus vaicājumus. Noteikti papildinājumus būtu, ka

nāktos veidot dažādas procedūras dažādām darbībām, lai klienta pusē nav jāveido

sarežģīti un vairāki vaicājumi, lai veiktu manipulācijas ar datiem vairākās tabulās

vienlaicīgi.

Patērētais stundu daudzums: 20