Transcript Plani dhe programi i bazave te te dhenave

Plani dhe programi i
bazave te te dhenave me
zbatim ne MYSQL
Bazat e punes ne access (ketu
permenden elementet themelore
dhe puna me to )
Urdherat themelore ne
SQL(structural query
language)(ketu mesohen urdherat
themelore dhe manipulimet me to
ne SQL( te pergjithshem) ,ndersa
ushtrimet organizohen ne MYSQL)
nbraha-bazat e te dhenave
1
Menyra e vleresimit
10% nga vijueshmeria .
25% nga seminari i cili punohet
kryesisht ne ushtrime laboratorike
(d.m.th ne laborator ), punohet ne
access dhe dorezohet me se largu nje
jave pa perfundu ligjeratat e
semestrit III .
65 % nga provimi perfundimtare (i cili
mbahet ne fund te semestrit ).
nbraha-bazat e te dhenave
2
Literatura
Literatura :
1.Mastering access , Autore:Alan
Simpson,Celeste Robinson ,Botimi
1999
2.Database system concepts
Autore :Abraham Silberschatz,Henry
F.Korth,S.Sudarshan,Botimi 2001
3.Microsoft server 2000
4.Database modeling and design
autore:Toby .J.Teorey,botimi 1999
(gjindet ne biblioteke)
nbraha-bazat e te dhenave
3
Literatura
5. Mannual-sql (manual I mysqlse ne web ne adresen
www.mysql.com)
6.Database process,autore :David
Kroenke(gjindet ne biblioteke)
7.Access bible 2002 ,autore
Michael Irwin ,Cary Prague
8.Teach yourself sql (botim nga
SAMS)
nbraha-bazat e te dhenave
4
Bazat e te dhenave
Ç’eshte baza e te
dhenave
Per cfare sherben
Si ndertohet
nbraha-bazat e te dhenave
5
Funksionet e bazave te te
dhenave
Vendosja e te dhenave
Pershtatja
Modifikimi
Kerkimi
Printimi
Qasja e me teper se nje shfrytezuesi
Qasja nga intraneti dhe interneti etj
nbraha-bazat e te dhenave
6
Kriteret qe merren parasysh
ne nje baze
“Data Redudancy“(perseritja e te
dhenave )Gjate ndertimit te bazes
se te dhenave ,nga disa persona
mundet qe nje fajlle (tani e
tutje tabela) i njejte te
perseritet p.sh nje e dhene
“emri”.Keshtu ne bazen e te
dhenave fitojme perseritje te te
dhenave ,kjo njihet me emrin
“redudancy”.
nbraha-bazat e te dhenave
7
Data redudancy
Nje redudance e te dhenave eshte
bere ne shembullin qe do te
shohim ne vijim ,ku e dhena e
emrit ne nje lidhmeri te
entiteteve eshte perseritur dhe
si e tille eshte e palejueshme.
Kjo shihet ne diagramin e
paraqitur me poshte dhe kjo
perseritje eshte bere ne tere
tabelat e shqyrtuara (atributi
emri) .
nbraha-bazat e te dhenave
8
Data redudancy(vazh.)
nbraha-bazat e te dhenave
9
“Veshtirsia ne qasje”
Nese merret nje shembull I ndertimit
te nje programi per pune bankare ,sic
dihet ne te duhet te kemi nje aplikim
per vendosje te klienteve ,per
balance te te ardhurave etj dhe
kerkohet nje e dhene qe te kihet
regjistri I te dhenave te klienteve qe
jane nga “tirana” .Nese ne ate program
nuk eshte aplikacioni i kerkesave nje
gje te tille nuk mundemi me e pase
d.m.th kemi nje veshtiresi ne qasje te
te dhenave .Kete e ilustron ky diagram
i meposhtem.
nbraha-bazat e te dhenave
10
Veshtirsia ne qasje(vazh.)
nbraha-bazat e te dhenave
11
Veshtirsia ne qasje(vazh.)
Siq shihet me siper mundesia e
kerkimit te te dhenave nuk eshte
e mundur pasi nje gje e tille nuk
eshte parapare ne kuader te asaj
forme kerkuese,qe d.m.th se kemi
veshtiresi ne qasje
nbraha-bazat e te dhenave
12
“Data isolation”
Nese kemi te dhenat nga disa
fajlla me forma te ndryshme
ateher nje qasje e te dhenave ne
ate baze do te ishte e veshtire
pasi kemi informata te izoluara
nbraha-bazat e te dhenave
13
“integrity problem”
Nese kemi te bejme me nje kliente
te bankes dhe per ate dhe klientet
tjere kemi vendose qe ne llogarine e
tije te kete me se paku 30 euro
gjate terheqjes se parave nga
llogaria ateher kemi te bejme me nje
pjese te programit e cila mundeson
nje te dhene te tille ,tani venja e
largimi i nje kushti te tille na paraqet
problem ,keto paraqesin nje integritet
referencial te te dhenave
nbraha-bazat e te dhenave
14
“Atomicity of data”
Problemi I tille ne kuader te nje baze te
te dhenave paraqitet nese kemi te bejme
me dergim te nje llogaria nga klienti 1 ne
ate te klientit 2 dhe nese gjate ketij
transferi na largohet shuma nga klienti1
por nuk paraqitet te klienti 2,d.m.th
humben te dhenat .Gjate bartjes se te
dhenave duhet ajo te jete “atomike “d.m.th
qe bartja te behet brenda atij entiteti.
nbraha-bazat e te dhenave
15
“concurrent access anomalies”
Nese ne kuader te nje banke kemi
shumen prej 80 eurove ne nje llogari
dhe njekohesisht nga e njejta llogari
dy persona (klient) terheqin ne te
njejten kohe shumat p.sh 50 dhe 40
nga po e njejta shume ,ateher llogaria
e tille e larte shenuare mbetet ne nje
pozite inekzistente ,per kete sistemi
duhet te kete nje supervision i cili
bene nje kontrolle te tille .Kjo na
paraqet nje anomali gjate qasje
paralele te te dhenave
nbraha-bazat e te dhenave
16
“security problem”
Nese meret shembulli I bankes ateher
programi te cilin e kemi duhet qe ti
mundesoje klienteve vetem te kene
qasje ne gjendjen e llogarise se tyre
dhe parashtrimit te kerkesave ne
lidhje me te por jo edhe te
nderhyrjes se tyre ne vet gjendje ,kjo
arrihet me ane te sigurimit te te
dhenave ne kuader te asaj baze te te
dhenave
nbraha-bazat e te dhenave
17
Siguria e te dhenave(vazh.)
Nje gje e tille do te ishte e
realizueshme nese ne shembullin
e shikimit te te dhenave nga
forma e paraqitur me pare te mos
kishim mundesi te nderhyrjes ne
vet programin e ndertimit te
bazes .
nbraha-bazat e te dhenave
18
“shikimi i te dhenave ”
Abstrakcioni i te dhenave
Qasja dhe skema e te dhenave
Pavaresia e te dhenave
nbraha-bazat e te dhenave
19
Abstrakcioni i te dhenave
Shfrytezuesit e bazes se te dhenave
duhet ti paraqitet baza ne ate menyre
qe ai ta kete me te lehte per me
perdore kjo arrihet permes disa
niveleve te organizimit te te dhenave
Niveli fizike I te dhenave –ky eshte
niveli I pare I shikim te te dhenave
dhe ketu jane te dhenat e pershkuara
ne detale se si jane te vendosura
Niveli logjike-ketu pershkruhen
lidhmerite dhe rrolet e te dhenave ,ne
veqanti nga administratori I bazes se
te dhenave
nbraha-bazat e te dhenave
20
Abstakcioni i te dhenave (1)
Niveli i shikimit –ky ne radhe te pare na
mundeson nje shikim te nje aplikacioni te
programit por jo edhe detalet e tipeve
te te dhenave dhe te dhenave ne
pergjithesi .P.sh nje puntore banke i cili
punon ne kabine per dhenie dhe marrjen
e te hollave ka mundesin e shikimit te te
dhenave por jo edhe te te dhenave detale
,perkatesisht per modifikim te vet
bazes se te dhenave .Nje ilustrim I
kesaj do te ishte
nbraha-bazat e te dhenave
21
Abstrakcioni i te dhenave(2)
nbraha-bazat e te dhenave
22
Qasja dhe skema e te
dhenave
Skema e te dhenave-eshte realizimi
logjike I bazes se te dhenave ,p.sh si
ne gjuhe programuese qe behet
definimi dhe tipi i variblave etj.
Qasja e te dhenave –eshte nje
permbajtje e momentit e bazes se te
dhenave p.sh ne shembullin e bankes ne
nje moment kemi 300 kliente dhe me
shume te teresishme 300000 euro
,kjo do te ishte nje qasje
nbraha-bazat e te dhenave
23
Pavaresia e te dhenave
Aftesia e ndrrimit te skemes se
te dhenave nga nje nivel dhe I cili
nuk ndikon ne skemen e te dhenave
ne nivelin tjeter ,paraqet pavaresin
e te dhenave .Jane dy nivele te
pavaresise se te dhenave
1.niveli fizike dhe
2.niveli logjike
nbraha-bazat e te dhenave
24
Modelet e bazave te te
dhenave
Me model te te dhenave
nenkuptojme teresine e veglave
per pershkrim te :
1. Te te dhenave
2. Te lidhjeve te tyre
3. Semantiken e te dhenes
4. kushtezimet
Ne varesi nga pershkrimi i
mesiperm kemi keto lloje te
bazave te te dhenave
nbraha-bazat e te dhenave
25
“entity relation model”
Nje modele I tille do te pershkruhej
me pershkrim reale te te dhenave i cili
pershkruhet me ,objekte bazike te
quajtura entitete , lidhmerise se tyre
si dhe atributet p.sh nje entitet mund
te jete personi qe punon ne banke dhe
keto jane te pershkruara me atribute
p.sh ai te punoje ne kabine,ndersa
lidhmeria e tyre do te ishte mes disa
entiteteve .Keto modele shkurt do ti
shenojme me E-R.Pershkrimi i te
dhenave ne kuader te nje E-R do te
paraqitej ne keto pika kryesore
nbraha-bazat e te dhenave
26
E-R modeli
Bashkesia e entiteve
Lidhmeria e tyre
Paraqitja e Dizajnit
Kufizimi i te dhenave
Qelesat
E-R Diagrami
Veglerit e zgjeruara te E-R
Dizajni i nje E-R baze
Kthimi i nje E-R modeli ne forme
tabele
nbraha-bazat e te dhenave
27
Objektet (entitetet)
Nje baze mund te modelohet si :
Bashkesi e entiteteve ,
Relacionet ne mes tyre.
Nje objekt eshte nje objekt ekzistues dhe i
cili dallon nga nje tjeter.
Psh nje person i caktuare ,fabrika etj
Keto objekte kane vetite e tyre (atributet)
Psh :njerzit kane emer dhe mbiemer etj
Nje bashkesi e entiteteve eshte ajo
bashkesi e cila ka tipe te njejte dhe qe
ndajne veti (atribute) te njejta ne mes
vete.
Psh : bashkesia e te gjithe njerezve (kane te
gjithe emra,mbiemra etj),bashkesia e te gjitha
fabrikave etj
nbraha-bazat e te dhenave
28
Bashkesia e entitetit”loan”
customer-id customer- customer- customername street
city
nbraha-bazat e te dhenave
loan- amount
number
29
Atributet
Pershkrimi i nje objekti mundesohet permes
shume vetive (atributeve)
Psh :
bleresi (customer) = (customer-id, customer-name,
customer-street, customer-city)
huaja (loan) = (loan-number, amount)
Domeni i atributit – bashkesia e vlerave te
mundeshme te atributeve
Tipet e atributeve :
Atributet e drejtperdrejta (thjeshta) dhe ato te
derivueme (ato qe rrjedhin nga ato te parat).
Ato te parat jane te njevlershme dhe te
shumevlershme
Psh shumevlershme :numrat e kartelave te bankes etj
Ato te derivueshme
Merren nga ato te parat
Psh mosha e marre nga data e lindjes
nbraha-bazat e te dhenave
30
Atributet e derivueme
nbraha-bazat e te dhenave
31
Lidhmeria e te dhenave
Lidhmeria e te dhenave realizohet per disa
objekte
Psh :
Hayes depositor A-102
me kete behet lidhmeria e bleresit
(customer) dhe llogarise se tije .
Lidhmeria ndermjet e objekteve eshte nje
relacion matematike i me teper se 2
objekteve ,i marre si nenbashkesi e
{(e1, e2, … en) | e1  E1, e2  E2, …, en  En}
ku (e1, e2, …, en) eshte nje qifte i atributeve
psh :
(Hayes, A-102)  depositor
nbraha-bazat e te dhenave
32
Nje lidhje ne mes te marresit
dhe huadhenesit
nbraha-bazat e te dhenave
33
Lidhmeria e objekteve
Nje paraqitje e nje lidhje ne mes te
objekteve eshte paraqitur edhe ne kete
skeme ,dhe objekti depozitor mund te ket
edhe atributin “data-access”
nbraha-bazat e te dhenave
34
Shkalla e lidhjes se objekteve
Kjo paraqet numrin e entiteteve qe
marrim pjese ne nje lidhje.
Relacioni ne te cilin marrin pjese dy
entitete quhet binar ,tri ternar etj.
Ne nje lidhje mund te futen me teper
se dy entitete .
Psh nje i punesuar ne banke mund te kete pune
ne disa filialla, atehere kemi nje lidhje ternare ne
mes te entiteteve te punesuar ,punet dhe fillialle
Lidhjet ne mes te me shume se 2
entiteteve jane te rralla
nbraha-bazat e te dhenave
35
Tipet e lidhjeve ne mes te
entiteteve
Meqe shumica e lidhjeve ne mes te
entiteteve eshte lidhje binare ateher
me poshte do te pershkojme tipet e
lidhjes ne mes tyre.
Tipet themelore te lidhjes ne mes te
entiteteve jane keto :
Nje me nje (One to one)
Nje me shume (One to many)
Shume me nje (Many to one)
Dhe shume me shume (Many to many )
nbraha-bazat e te dhenave
36
Tipet e lidhjes- nje me nje dhe
nje me shume
Lidhja nje me nje
Lidhja nje me shume
shenim:disa nga elementet ne A dhe B mund te mos jene te perfshira ne
Lidhje
nbraha-bazat e te dhenave
37
Tipet e lidhjes–shume me nje
dhe shume me shume
Shume me nje
Shume me shume
nbraha-bazat e te dhenave
38
Diagrami i nje E-R-je
 Drejtekendeshi –paraqet nje entitet .
 Paralelogrami –nje lidhje ne mes te entiteteve .
 Vizat –lidhjet ne mese te entitetit dhe atributit si dhe ne
mes te entiteteve permes lidhjes .
 Elipsat -paraqesin atribute
 Elipsat e dyfishta-paraqesin atribute te shumfishta .
 Elipsat e nderprera –atributet e derivuara .
 Te nenvizuara –atributet kryesore (qelesat )
nbraha-bazat e te dhenave
39
Pershkrimi i elementeve te
nje ER-je
nbraha-bazat e te dhenave
40
Nje E-R me i komlikuar
nbraha-bazat e te dhenave
41
Lidhmerite me atribute
nbraha-bazat e te dhenave
42
Rrolet e objekteve
Entitetet e nje lidhje nuk duhet te jen
me veti te ndryshme
Ne vizat e meposhtme “manager” dhe
“worker” paraqesin rrolet;ata specifikojne se
si entiteti “employee” komunikon me “ worksfor” ne ate lidhje.
Rrolet ne nje E-R shenohen me shkronja ne
vizat qe i lidhin entitetet dhe kushtit te
lidhjes .
Rrolet me labela zgjedhen sipas deshires dhe
merren vetem per kjartesi te semantikes
nbraha-bazat e te dhenave
43
Menyrat e shenimit te lidhjes
Lidhja nje me shume do te shenohet me (),
ku njeshin e paraqet fillimi i vizes ndersa
shumicen mbarimi ,viza (—) e pa orientuare
paraqet lidhjen shume me shume e orientuare
nga kushti i lidhjes ne drejtim te entitetit.
Psh : nje me nje :
Bleresi eshte i shoqeruare me se paku nje hua
(loan) permes huadhenesin (borrower)
Njesoj huaja eshte e lidhur me te pakten nje
bleres permes te huadhenesit (borrower)
nbraha-bazat e te dhenave
44
Nje me shume
Ne lidhjen nje me shume huaja( loan)
eshte lidhur me te pakten nje bleres
permes “borrower”,ndersa bleresi
eshte i lidhur me disa huaja permes te
“borrower”
nbraha-bazat e te dhenave
45
Shume me nje
Ne kete raste kemi mundesin e
kundert me ate te mare ne rastin e
mesiperm.
nbraha-bazat e te dhenave
46
Shume me shume
Ne kete raste ana e bleresit eshte e
lidhur me shume hua permes te
“borrower” dhe anasjelltas huaja ka
shume bleres
nbraha-bazat e te dhenave
47
Realizimet e lidhjeve
Ne lidhjet e mesiperme duhet
cekur se kusht themelore eshte
qe ato te dhena te cilat
deshirojme ti lidhim te kene tipe
te njejte te te dhenave (ku tipi i
te dhenave do te permendet
edhe ne vazhdim )
nbraha-bazat e te dhenave
48
Pjesemarrja e nje entiteti ne
nje lidhje
 Pjesemarrja totale ne nje lidhje (e shenuar e me viza te dyfishta ): kjo merret
ne ate raste kur secili entitet merr pjese ne te pakten nje lidhje ne ate lidhmeri
te entiteteve
 Psh pjesemarrja e huase (loan) ne “borrower” eshte totale
 secila hua duhet te kete nje bleres(costumer) permes te “borrower”
 Pjesemarrja e pjesshme : disa entitete mund te mos marrin pjese ne te gjitha
lidhjet ne ate baze
 Psh pjesemarrja e “customer” ne “ borrower” eshte e pjesshme
nbraha-bazat e te dhenave
49
Qelesat (Keys)
Le te jete K  R
K eshte nje “superkey” i relacionit R nese
vlerat e K jane te mjaftueshme per
identifikim te nje qifti te mundshem nga
relacioni r(R)
Ku me relacion te mundshme nenkuptojme nje qift i
cili mund te krijohet ne ate model.
Psh : {customer-name, customer-street} dhe
{customer-name}
jane “superkeys” te “Customer”,nese dy
“customer” nuk kane emra te njejte.
K eshte “candidate key “ nese K eshte
minimal
psh : {customer-name} eshte “candidate key”
per “Customer”,perderisa ai do te jete
“superkey” (nen supozimin se dy “customers”
nuk kane emer te njejte ) dhe nese
nbraha-bazat e te dhenave
50
nenbashkesit e tije
nuk jane “superkey”.
Fushat kryesore dhe rroli i tyre
ne lidhjen e entiteteve
Kombinimi i qelesave kryesore te entiteteve me
pjesemarrje te pjesshme ne nje lidhje formojne
nje “super key”.
Psh (customer-id, account-number) eshte nje “super
key” per tabelen “depositor”
Shenim: kjo nenkupton se qiftet e entiteteve kane
te pakten nje lidhje ne bashkesine epjesshme te
lidhjes.
Psh nese percjellim te gjitha qasjet e te dhenave ne
secilin llogari (account) nga secili “customer”, ne nuk mund
te supozojme lidhje per secilen qasje.Edhe pse ne mund
te marrim atribute me shumevlera
Gjithashtu gjate percaktimit te “candidate key”-ve duhet
te kihet parasysh edhe lidhmeria e te dhenave ne ato
entitete
Gjate percaktimit te “primary key ” ne
bashkesine e lidhjeve me me shume se nje
“candidate key ” ne kuader te nje pyetsore kjo
duhet edhe te ceketnbraha-bazat
ne semantiken
e tij.
e te dhenave
51
Realizimi i lidhjeve ne access
Me nje shembull shohim ne
vazhdim lidhjet e tipeve te
permendura
1.nje me nje
Kjo realizohet ndermjete fushave
kryesore te atyre dy entiteteve
nbraha-bazat e te dhenave
52
Lidhja nje me nje
nbraha-bazat e te dhenave
53
Lidhja nje me nje (vazh.)
nbraha-bazat e te dhenave
54
Lidhja nje me nje(vazh)
nbraha-bazat e te dhenave
55
Lidhja nje me nje (vazh.)
nbraha-bazat e te dhenave
56
Lidhja nje me nje (vazh.)
nbraha-bazat e te dhenave
57
Lidhja nje me shume
Kjo lidhje ne access realizohet
per dy entitete ashtu qe ne
tabelen e pare ku deshirojme te
kemi lidhjen nje duhet te kemi
fushen kryesore ,ndersa ne te
dyten ku deshirojme te kemi
lidhjen shume duhet te jete fushe
jokryesore,kjo shihet me kete
shembull,ndermjet tabelave 1 dhe
tabeles 2. gjithashtu duhet te
ceket se tipet e te dhenave duhet
te jen te njejta
nbraha-bazat e te dhenave
58
Lidhja nje me shume
nbraha-bazat e te dhenave
59
Lidhja shume me shume
Kjo lidhje ne access realizohet ne
mes te dy atributeve jo kryesore
duke ndertuare nje tabele ndihmese
e cila perbehet nga atributet
kryesore te dy tabelave ne lidhje
dhe i ka qe te dy ato si atribute
kryesore,sic shihet ne shembullin e
meposhtem ,ku behet realizimi i
lidhjes ne mes te tab1 dhe tab2.
nbraha-bazat e te dhenave
60
Lidhja shume me shume(vazh.)
nbraha-bazat e te dhenave
61
Lidhjet jo binare
Me siper u pa se si behet lidhja e
entiteve dhe realizimi i tyre ne
Access per ato entitete te cilat
jane ne lidhmeri binare ne mes
veti .Ne vazhdim shohim se cka
ndodhe me ato entitete te cilat
kane lidhje te rendeve tjera .
nbraha-bazat e te dhenave
62
Shembull i diagramit E-R me
nje lidhje ternare
nbraha-bazat e te dhenave
63
Shembull i diagramit E-R me
nje lidhje ternare
Sic shihet nga shembulli i mesiperm
lidhja ternare eshte realizuare ne
mes te entiteteve “job”,”employ” dhe
“branch”.E gjithe kjo eshte realizuare
permes “works on”.
nbraha-bazat e te dhenave
64
Lidhmeria ne nje relacion
ternare
Ne nje relacion ternare (ose n-are) se
paku nje lidhje ne mes te tyre lihet
anash per te percaktuare lidhjen ne
mes te dy entiteteve qe marrin pjese
ne ate lidhje
Psh nje lidhje ne mes te “works-on “
dhe “job” paraqet faktin se secili
“employee” punon se paku ne nje “job”
ne cilendo “branch”
Nese aty merren me shume se nje
lidhje ateherë kemi dy menyra te
percaktimit te kuptimit ne mes tyre.
nbraha-bazat e te dhenave
65
Lidhmeria ne nje relacion
ternare(vazh)
Psh nje relacion ternare R ne mes te entiteteve
lidhese A, B dhe C me lidhje ne B dhe C mund
te kuptohet si
1.secili entitet ne kete rast A eshte i
percaktuare ne menyre te vetme me entitet B
dhe C ose
2.secilit qift te entiteteve nga (A, B) i
pergjigjet nje i vetem C dhe secilit qift nga (A,
C) nje i vetem B
Secila alternative eshte marre ne menyra te
ndryshme
Per kete arsye ne largojme nje ose me shume
lidhje ne relacionet ternare (n-are) gjate
realizimit te lidhjes .
nbraha-bazat e te dhenave
66
Paraqitja binare e lidhjeve qe
nuk jane binare
Ato relacione qe nuk jane binare eshte mire
me i paraqite ne trajte binare
Psh nje lidhje familjare ne mes te prinderve
dhe femijes paraqet nje lidhje ternare ,si
relacion ternare me mire eshte me e paraqite
me dy relacione binare permes femijes dhe
nenes si dhe permes te babait dhe femijes.
Duke mare dy relacione binare ne lidhje lejohen
informata te pjesshme ne ate lidhje (psh vetem baba
dihet )
Mirepo ka disa relacione te cilat per nga natyra e
tyre jane jo binare
Psh “works-on”
nbraha-bazat e te dhenave
67
Paraqitja binare e lidhjeve qe
nuk jane binare
Ne pergjithesi secili relacion jo binare mund te
shprehet permes atyre binare duke krijuare
nje entitet artificial.Kjo behet keshtu
Zevendesohet R ne mes te entiteteve A, B dhe C
me nje entitet E, ateher merren keto lidhje te reja:
1. RA, per E dhe A 2.RB, per E dhe B
3. RC, per E dhe C
Duhet krijuar edhe disa atribute lidhese ne E
Se pari duhet te shtohen ter atributet e R ne E
Per cdo relacion lidhje (ai , bi , ci) ne R, krijohet
1.Nje entitet i ri ei ne bashkesine e entiteteve E
2.Shtohet (ei , ai ) ne RA 3.Shtohet (ei , bi ) ne
RB
4.shtohet (ei , ci ) ne RC
nbraha-bazat e te dhenave
68
Paraqitja binare e lidhjeve qe
nuk jane binare me nje ‘E-R’
diagram
nbraha-bazat e te dhenave
69
Paraqitja binare e lidhjeve
qe nuk jane binare
Gjithashtu duhet te pershtaten
kufizimet ne lidhjen e re
Pershtatja e te gjitha kufizimeve mund
te jete e pamundur
Ne paraqitjen e re mund te kemi asi raste
kur ne skemen e vjeter mos ti pergjigjet
asnje,mirepo per kete duhet te veprohet
keshtu:
Psh : me i shtue kushtet e reja ne RA, RB dhe
RC qe te sigurohemi se entiteti i ri ti pergjigjet
njerit nga entitetet ne A, B dhe C
nbraha-bazat e te dhenave
70
Modelet relacionale(3)
Struktura e nje baze relacionale
Algjebra relacionale
Domeni i njehsimeve Relacionale
Veprimet e zgjeruara me Algebrarelacionale
Modifikimi i bazes
Shikimet (Views)
nbraha-bazat e te dhenave
71
Shembulli i nje
relacioni,tabela “account”
nbraha-bazat e te dhenave
72
Struktura baze
Per bashkesite e dhena D1, D2, …. Dn nje
relacion r eshte nenbashkesi e
D1 x D2 x … x Dn
d.m.th relacioni i gjatesise n eshte nje qift i
renditur (a1, a2, …, an) ku secila ai  Di
Psh :nese customer-name = {Jones, Smith, Curry,
Lindsay}
customer-street = {Main, North, Park}
customer-city = {Harrison, Rye, Pittsfield}
ateher r = { (Jones, Main, Harrison),
(Smith, North, Rye),
(Curry, North, Rye),
(Lindsay, Park, Pittsfield)}
eshte relacion i gjatesise 3 me atributet
customer-name x customer-street x customercity
nbraha-bazat e te dhenave
73
Tipet e atributeve
Secili atribute eshte i emeruare
Bashkesia e vlerave te lejuara te atributit
paraqet domenine e atributit
Vlerat e atributeve kerkohet te jene
atomike, kjo do te thote te pandashme
Psh atribute me shume vlera nuk jane
atomike
Psh vlerat e atributeve te kompozuara nuk
jane atomike
Ndersa vlera zerro eshte pjese e secilit
domene
Vlera zerro sjell komplikime ne definimin e
shume operatoreve
ne vijim do te injorojme vlerat zerro dhe do ti
shohim me vone . nbraha-bazat e te dhenave
74
Skema Relacionale
Nese A1, A2, …, An jane atribute
,ateher
R = (A1, A2, …, An ) paraqet nje skeme
relacionale
psh Customer-schema =
(customer-name,
customer-street, customer-city)
Me r(R) do te shenojme nje relacion
ne skemen relacionale
psh customer (Customer-schema)
nbraha-bazat e te dhenave
75
Shembull i nje relacioni
Vlerat e nje relacioni jane te
specifikuara permes te tabelave
Ku nje element (t) nga r eshte nje
cifte i paraqitu me nje
rresht(record) ne kuader te tabeles
atributet
(ne kolona)
customer-name customer-street customer-city
Jones
Smith
Curry
Lindsay
Main
North
North
Park
Harrison
Rye
Rye
Pittsfield
Ciftet
ne rreshta
customer
nbraha-bazat e te dhenave
76
Relacionet jane jo te renditura
 renditja e cifteve ne kuader te nje tabele eshte e
parendesishme dhe per kete mund te jene ne nje renditje te
cfardoshme
 psh relacioni ne account i paraqitur ne cifte te cfardoshme
nbraha-bazat e te dhenave
77
Baza e te dhenave
Bdh perbehet prej disa relacioneve
Informatat per nje fabrike ndahen ne pjese
ne disa relacione ku secili nga ta paraqet nje
informate
Psh : account :paraqet informata ne lidhje
me llogarite ne ate firme
depositor : paraqet te dhenat ne
lidhje me depozituesit
customer : paraqet informatat ne
lidhje me klientet
Paraqitja e te dhenave ne nje relacion si ne
bank(account-number, balance, customername, ..)
rezulton ne
Perseritjen e te dhenave (psh dy kliente me nje
llogari)
Paraqitja e vlerave zerro (psh paraqet klientin qe
nuk ka llogari)
nbraha-bazat e te dhenave
78
Pershtatja e bdh
Ne gjithe problemet e paraqitura
me larte ,zgjidhjen e tyre do ta
marrim me normalizimin e bdh qe
do te shihet me vone .
nbraha-bazat e te dhenave
79
Relacioni “customer”
nbraha-bazat e te dhenave
80
Relacioni “depositor”
nbraha-bazat e te dhenave
81
E-R Diagrami per nje banke
nbraha-bazat e te dhenave
82
Skema e Diagramit per banke
nbraha-bazat e te dhenave
83
Gjuha e pyetsoreve (Query
Languages)
Kjo eshte gjuha me te cilen ipen dhe
merren te dhenat nga bdh.
Kategorite e gjuheve
procedurale
Dhe jo-procedurale
Gjuhet e pastra (“Pure” languages):
Algjebra relacionale
Njehsimet me ciftet relacionale
Domeni i njehsimeve relacionale
nbraha-bazat e te dhenave
84
Algjebra Relacionale
Eshte gjuhe procedurale
Keto jane operacionet themelore
Select
project
union
set difference
Cartesian product
rename
Veqori e ketyre operacioneve eshte
se marrin si te dhena hyrese disa
relacione dhe japim vetem nje relacion
ne dalje si rezultate.
nbraha-bazat e te dhenave
85
Shembull i operacionit Select
•
Relacioni r
A
B
C
D


1
7


5
7

 12 3

 23 10
A
B
C
D


1
7

 23 10
• A=B ^ D > 5 (r)
nbraha-bazat e te dhenave
86
Shembull i operacionit
Select(vazh.)
• A=B ^ D > 5 (r) me ane te relacionit te mesiperm
eshte mundesue te behet zgjedhja e te
dhenave nga relacioni r me kushte qe ato te
dhena te plotesojne keto kushte:
• 1.qe A te jete sikurse B
• 2.dhe qe ne ate relacion r kushti D te jete me i
madhe se sa numri 5 dhe sic u pa me larte
nje gje te tille e plotesojne rreshti i pare dhe i
fundit ne relacionin r.
nbraha-bazat e te dhenave
87
Operacioni “Select”
Shenimi :  p(r) do te thote :
p paraqet predikatin zgjedhes ne ate relacion
Definohet si me poshte :
p(r) = {t | t  r dhe p(t)}
ku p eshte nje formule me kushtet e
zgjedhjes qe permbane ne vete shprehjet e
lidhura me relacionet :  (dhe (and)),  (ose
(or)),  (mohimin (not))
secili nga termet eshte njeri nga :
<attribute> dicka <attribute> ose <constant>
ku dicka paraqet : =, , >, . <. 
Shembull i operacionit “select” eshte ky :
 branch-name=“Perryridge”(account)
nbraha-bazat e te dhenave
88
Operacioni “select”
Ne shembullin e fundit
mundesohet shikimi i te dhenave
te llogarive(account) nga filialla
me emer (branch) “Perryridge”
nbraha-bazat e te dhenave
89
Operacioni “Project”
A
Relacioni r :
B
C
 10 1
 20 1
 30 1
 40 2
A,C (r)
A
C
A
C

1

1

1

1

1

2

2
=
nbraha-bazat e te dhenave
90
Operacioni “Project”
shenim:
me relacioni A1, A2, …, Ak (r)
ku A1, A2 jane emrat e atributeve jane marre
atributet e cekura ne relacionin r .
Veprimi qe kryhet eshte ky merren k kolona
te cekura duke i perjashtuare te tjerat
Vlerat e njejta (te shumfishta ) ne kuader te
rreshtave gjithashtu perjashtohen
Psh per te eliminuare emrin e filialles (branch-
name) nga llogaria (account) marrim si me
poshte
account-number, balance (account)
nbraha-bazat e te dhenave
91
Operacioni “Union”
Relacionetr, s:
A
B
A
B

1

2

2

3

1
s
r
r  s:
A
B

1

2

1

3
nbraha-bazat e te dhenave
92
Operacioni “Union”
Shenimi : r  s
Definohet sikurse :
r  s = {t | t  r ose t  s}
r  s eshte i vertete (i realizueshem) kur
1. r, s kane numer te njejte te atributeve
2. Domenet e atributeve duhet te jene
kompatibile ne mes veti (psh , kolona e 2-te
nga relacioni ka tipe te njejte te vlerave me
kolonen 2 nga s)
Psh . Gjeni te gjithe “customers” me llogari
(account) ose hua (loan)
customer-name (depositor)  customer-name
(borrower)
nbraha-bazat e te dhenave
93
Ndryshimi ne mes te
bashkesive
Relacionet r, s:
A
B
A
B

1

2

2

3

1
s
r
r – s:
A
B

1

1
nbraha-bazat e te dhenave
94
Ndryshimi ne mes te
bashkesive
Shenimi r – s
Definohet si :
r – s = {t | t  r dhe t  s}
Ndryshimi ne mes te bashkesive
duhet te merret ne mes te relacioneve
kompatibile dhe pershkrimi i
shembullit larte eshte ky:jane marre
ato cifte te relacionit r dhe jane
larguare nga r ato qe jane edhe ne s.
r dhe s duhet te kene te njejten gjatesi
Domenet e atributeve duhet te jene
kompatibile
nbraha-bazat e te dhenave
95
Produlti kartezian
Relacionet r, s:
A
B
C
D
E

1

2




10
10
20
10
a
a
b
b
r
s
r x s:
A
B
C
D
E








1
1
1
1
2
2
2
2








10
10
20
10
10
10
20
10
a
a
b
b
a
a
b
b
nbraha-bazat e te dhenave
96
Produkti kartezian
Shenimi r x s
Definimi eshte :
r x s = {t q | t  r dhe q  s}
Supozohte se atributet nga r(R) dhe
s(S) jane disjunkte. (d.m.th
R  S = ).
Nese atributet e r(R) dhe s(S) nuk jane
disjunkte ,ateher ato qe mbesin duhet te
merren.Pershkrimi i shmbullit te
mesiperm eshte ky:secili cifte i anes se
majte ne relacioni r eshte marre me
secilin cifte te anes se djathte nga
relacioni s
nbraha-bazat e te dhenave
97
Kompozimi i operacioneve
Me kombinimin e operacioneve te mesiperme
mund te behet krijimi i shprehjeve
operacionale,psh : A=C(r x s)
rxs
A=C(r x s)
A
B
C
D
E








1
1
1
1
2
2
2
2








10
10
20
10
10
10
20
10
a
a
b
b
a
a
b
b
A
B
C
D
E



1
2
2
 10 a
 20 a
 20 b
nbraha-bazat e te dhenave
98
Kompozimi i relacioneve
Me shembullin e mesiperm shihet
se eshte marre fillimisht produkti
kartezian i bashkesive dhe ne
kuader te tije kushti qe
bashkesite A dhe C me qene te
barabarta.
nbraha-bazat e te dhenave
99
Riemrimi i Operacioneve
Lejon emerim dhe me kete mund ti
qasemi nje rezultati te shprehjes
relacionale algjebrike.
Lejon referim te nje relacioni me me
shume se nje emer.psh : x (E)
Kjo na mundeson shikim e te dhenes nga
shprehja E me emrin X.
Nese shprehja algjebrike E ka nje gjatesi
n,atehere
x (A1, A2, …, An) (E)
Na jepe rezultatin e shprehjes E me
emer X dhe me atribute e riemruara
A1, A2, …., An.
nbraha-bazat e te dhenave
100
Shembuj te relacioneve
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street,
customer-only)
account (account-number, branch-name,
balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
nbraha-bazat e te dhenave
101
Shembull i pyetsorit
Gjeni te gjitha huate me te medha se
sa $1200.kjo pershkohet me kete
relacion
amount > 1200 (loan)
Gjeni numrin e huase per secilen hua me sasi me te
madhe se sa
$1200
loan-number (amount > 1200 (loan))
nbraha-bazat e te dhenave
102
Shembuj te metejme te
pyetsoreve
Gjeni emrat e te gjithe klienteve
(“customer”)te cilet kane hua, nje
llogari ose qe te dyja nga banka
customer-name (borrower)  customer-name (depositor)
Gjeni emrat e te gjithe klienteve te cilet kane hua dhe
nje llogari bankare.
customer-name (borrower)  customer-name (depositor)
nbraha-bazat e te dhenave
103
Shembuj pyetsoresh (vazh)
Gjeni emrat e te gjithe klienteve te cilet
kane hua ne filialllen “Perryridge”.
customer-name (branch-name=“Perryridge”
(borrower.loan-number = loan.loan-number(borrower x loan)))
 Gjeni emrat e te gjithe klienteve te cilet kane hua ne filiallen
“ Perryridge” dhe te cilet nuk kane asnje llogari ne ndonje
filialle te bankes.
customer-name (branch-name = “Perryridge”
(borrower.loan-number = loan.loan-number(borrower
x loan)))
–
customer-name(depositor)
nbraha-bazat e te dhenave
104
Shembuj pyetsoresh (vazh)
Gjeni emrat e te gjithe klienteve te cilet
kane hua ne filiallen “Perryridge”.
Pyetsori 1

customer-name(branch-name = “Perryridge” (
borrower.loan-number = loan.loan-number(borrower x
loan)))
 pyetsori 2
customer-name(loan.loan-number = borrower.loan-number(
(branch-name = “Perryridge”(loan)) x borrower))
nbraha-bazat e te dhenave
105
Shembuj pyetsoresh (vazh)
Gjeni llogarine e ballancuare me te
madhe (account balance)
Riemroje relacionine ellogarise si d
Zgjidhja :
balance(account) - account.balance
(account.balance < d.balance (account x d
(account)))
nbraha-bazat e te dhenave
106
Definimi formal i nje shprehje
Nje shprehje relacionale ne algjeber
relacionale perbehet nga keto dy elemente
themelore :
Relacioni eshte ne BDH(baze te te dhenave)
Dhe nga nje relacion konstant
Le te jene E1 dhe E2 dy shprehje algjebrike
relacionale ; keto jane te gjitha shprehjet
relacionale algjebrike per to :
E1  E2
E1 - E2
E1 x E2
p (E1), P eshte nj epredikat i atributeve ne E1
s(E1), S eshte nje liste e cila permbane disa nga
atributet e E1
 x (E1), x eshte nje emer i ri per rezultatin e E1se
nbraha-bazat e te dhenave
107
Operacionet shtese
Ne mund te definojme operacione
shtese te cilet nuk e bejne fuqizimin
e algjebres relacionale por bejne
thjeshtesin e pyetsoreve.
Prerja e bashkesive(Set intersection)
Lidhja e bashkesive (Natural join)
Pjestimi (Division)
Ndarja (Assignment)
nbraha-bazat e te dhenave
108
Prerja e bashkesive
Shenimi : r  s
Definohet me ane te relacionit:
r  s ={ t | t  r dhe t  s }
Supozojme se :
r, s kane te njejten gjatesi
atributet e r dhe s jane kompatibile
Ateher dihet matematikisht se
A  B =A\(A\B),keshtu mund te
marrim se: r  s = r - (r - s)
nbraha-bazat e te dhenave
109
Prerja e bashkesive-shembull
Le te jene dhene relacionet r, s:
A
B



1
2
1
A
B


r
2
3
s
Ateher me prerje r  s marrim:
A
B

2
nbraha-bazat e te dhenave
110
Operacioni i lidhjes natyrale

shenimi i ketij operacioni do te jete : r
s
Le te jene r dhe s relacione ne skemat R dhe
S perkatesisht.
Ateher r s eshte relacion nga skema R  S
e marre si me poshte:
Merret ne konsiderate secili cift tr nga r dhe ts
nga s.
Nese tr dhe ts kane te njejten vlere ne secilin nga
atributet ne R  S, shtohet cifti t ne rezultat,ku
t ka vlere te njejte sikurse tr ne r
t ka vlere te njejte sikurse ts ne s Psh :
R = (A, B, C, D)
S = (E, B, D)
Skema e rezulatit = (A, B, C, D, E)
r
s eshte definuare sikurse :
r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))
nbraha-bazat e te dhenave
111
Operacioni i lidhjes natyrore
Me perkufizim te operacionit te
lidhjes natyrore shihet se kjo
realizohet per dy relacionet e
shqyrtuara R dhe S te marra nga
unioni i tyre ashtu qe rekordet e
atributeve te cilat jane ne te dy
shprehjet merren sipas produktit
kartezian te tyre.kjo shihet me
kete shembull
nbraha-bazat e te dhenave
112
Shembull i lidhje natyrore
Jane dhene relacionet r, s:
A
B
C
D
B
D
E





1
2
4
1
2





a
a
b
a
b
1
3
1
2
3
a
a
a
b
b





r
r
s
s
A
B
C
D
E





1
1
1
1
2





a
a
a
a
b





nbraha-bazat e te dhenave
113
Operacioni i
pjestimit(division)
rs
Le te jene r dhe s relacione ne skemat
R dhe S te dhena me
R = (A1, …, Am, B1, …, Bn)
S = (B1, …, Bn)
Rezultati i shprehjes r  s eshte nje relacion
ne skemen
R – S = (A1, …, Am)
Te definuare me shprehjen
r  s = { t | t   R-S(r)   u  s ( tu 
r)}
nbraha-bazat e te dhenave
114
Shembull i operacionit te
pjestimit
A
B











1
2
3
1
1
1
3
4
6
1
2
B
Relacionet r, s:
r  s:
A
1
2
s
r


nbraha-bazat e te dhenave
115
Another Division Example
Relations r, s:
A
B
C
D
E








a
a
a
a
a
a
a
a








a
a
b
a
b
a
b
b
1
1
1
1
3
1
1
1
D
E
a
b
1
1
s
r
r  s:
A
B
C


a
a


nbraha-bazat e te dhenave
116
Division Operation (Cont.)
Property
Let q – r  s
Then q is the largest relation satisfying
qxsr
Definition in terms of the basic
algebra operation
Let r(R) and s(S) be relations, and
let S  R
r  s = R-S (r) –R-S ( (R-S (r) x s)
– R-S,S(r))
To see why
R-S,S(r) simply
reorders
attributes of r
nbraha-bazat
e te dhenave
117
Assignment Operation
The assignment operation ()
provides a convenient way to
express complex queries.
Write query as a sequential program
consisting of
a series of assignments
followed by an expression whose value is
displayed as a result of the query.
Assignment must always be made to a
temporary relation variable.
Example: Write r  s as
temp1  R-S (r)
temp2  R-S ((temp1 x s)
– R-S,S (r))
resultnbraha-bazat
= tempe 1te dhenave
– temp2
118
Example Queries
Query 1
Find all customers who have an account
CN(BN=“Downtown”(depositor account)) 
from at least the “Downtown” and the
CN(branches.
BN=“Uptown”(depositor account))
Uptown”
where CN denotes customer-name and BN denotes
branch-name.
Query 2
customer-name, branch-name (depositor account)
 temp(branch-name) ({(“Downtown”), (“Uptown”)})
nbraha-bazat e te dhenave
119
Example Queries
Find all customers who have an account
at all branches located in Brooklyn
city.customer-name, branch-name (depositor
account)
 branch-name (branch-city = “Brooklyn” (branch))
nbraha-bazat e te dhenave
120
Extended RelationalAlgebra-Operations
Generalized Projection
Outer Join
Aggregate Functions
nbraha-bazat e te dhenave
121
Generalized Projection
Extends the projection operation by
allowing arithmetic functions to be
used in the projection list.
 F1, F2, …, Fn(E)
E is any relational-algebra expression
Each of F1, F2, …, Fn are are
arithmetic expressions involving
constants and attributes in the
schema of E.
Given relation credit-info(customername, limit, credit-balance), find how
nbraha-bazat e te dhenave
much more each
person can spend: 122
Aggregate Functions and
Operations
Aggregation function takes a
collection of values and returns a
single value as a result.
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
Aggregate operation in relational
algebra
( E)
G1, G2, …, Gn
g F1( A1), F2( A2),…, Fn( An)
nbraha-bazat e te dhenave
123
Aggregate Operation –
Example
Relati
on r:
g sum(c) (r)
A
B
C




 7
 7
 3
 10
sum-C
27
nbraha-bazat e te dhenave
124
Aggregate Operation –
Example
branch-name account-number
balance
Relation account grouped by
Perryridge
A-102
400
branch-name
: A-201
Perryridge
900
Brighton
Brighton
Redwood
branch-name
A-217
A-215
A-222
750
750
700
g sum(balance) (account)
branch-name
Perryridge
Brighton
Redwood
balance
1300
1500
700
nbraha-bazat e te dhenave
125
Aggregate Functions (Cont.)
Result of aggregation does not
have a name
g
branch-name
sum(balance) as sum-balance
Can use rename operation to give it a
(account)
name
For convenience, we permit renaming
as part of aggregate operation
nbraha-bazat e te dhenave
126
Outer Join
An extension of the join operation
that avoids loss of information.
Computes the join and then adds
tuples form one relation that does not
match tuples in the other relation to
the result of the join.
Uses null values:
null signifies that the value is unknown or
does not exist
All comparisons involving null are (roughly
speaking) false by definition.
Will study precise meaning of comparisons
with nulls later
nbraha-bazat e te dhenave
127
Outer Join – Example
loan-number branch-name amount
Relation
loan
Downtown
L-170
L-230
L-260
Redwood
Perryridge
3000
4000
1700
 Relation borrower
customer-name loan-number
Jones
Smith
Hayes
L-170
L-230
L-155
nbraha-bazat e te dhenave
128
Outer Join – Example
Inner Join
loan-number branch-name amount customer-name
L-170
L-230
loan
Downtown
Redwood
Borrower
3000
4000
Jones
Smith
 Left Outer Join
loan
Borrower
loan-number branch-name amount customer-name
L-170
L-230
L-260
Downtown
Redwood
Perryridge
3000
4000
1700
Jones
Smith
null
nbraha-bazat e te dhenave
129
Outer Join – Example
Right Outer Join
loan-number branch-name amount customer-name
L-170
L-230
L-155
loan
borrower
Downtown
Redwood
null
3000
4000
null
Jones
Smith
Hayes
 Full Outer Join
loan
borrower
loan-number branch-name amount customer-name
L-170
L-230
L-260
L-155
Downtown
Redwood
Perryridge
null
3000
4000
1700
null
Jones
Smith
null
Hayes
nbraha-bazat e te dhenave
130
Null Values
It is possible for tuples to have a null
value, denoted by null, for some of
their attributes
null signifies an unknown value or that
a value does not exist.
The result of any arithmetic
expression involving null is null.
Aggregate functions simply ignore null
values
Is an arbitrary decision. Could have
returned null as result instead.
We follow the semantics of SQL in its
e te dhenave
handling ofnbraha-bazat
null values
131
Null Values
Comparisons with null values return
the special truth value unknown
If false was used instead of unknown, then
not (A < 5)
A >= 5
would not be equivalent to
Three-valued logic using the truth
value unknown:
OR: (unknown or true)
= true,
(unknown or false)
= unknown
(unknown or unknown) = unknown
AND: (true and unknown)
= unknown,
(false and unknown)
= false,
(unknown and unknown) = unknown
NOT: (not unknown) = unknown
In SQL “P is unknown” evaluates to true if
nbraha-bazat to
e te unknown
dhenave
132
predicate P evaluates
Modification of the Database
The content of the database may
be modified using the following
operations:
Deletion
Insertion
Updating
All these operations are
expressed using the assignment
operator.
nbraha-bazat e te dhenave
133
Deletion
A delete request is expressed
similarly to a query, except instead of
displaying tuples to the user, the
selected tuples are removed from the
database.
Can delete only whole tuples; cannot
delete values on only particular
attributes
A deletion is expressed in relational
algebra by:
rr–E
where r is a relation and E is a
nbraha-bazat e te dhenave
134
Deletion Examples
Delete all account records in the
Perryridge
branch.
account  account
– 
branch-name = “Perryridge” (account)
Delete all loan records with amount in the range of 0 to 50
loan  loan –  amount 0 and amount  50 (loan)
Delete all accounts at branches located in Needham.
r1   branch-city = “Needham” (account
branch)
r2  branch-name, account-number, balance (r1)
r3   customer-name, account-number (r2
depositor)
account  account – r2
depositor  depositor – r3
nbraha-bazat e te dhenave
135
Insertion
To insert data into a relation, we
either:
specify a tuple to be inserted
write a query whose result is a set of
tuples to be inserted
in relational algebra, an insertion is
expressed by:
r r  E
where r is a relation and E is a
relational algebra expression.
The insertion of a single tuple is
expressed by letting E be a constant
nbraha-bazat e te dhenave
136
relation containing one tuple.
Insertion Examples
account
 information
account  {(“Perryridge”,
1200)}
Insert
in theA-973,
database
depositor
 depositor
{(“Smith”,
A-973)}
specifying
that Smith
has
$1200
account A-973 at the Perryridge
branch.
in
 Provide as a gift for all loan customers in the
Perryridge
branch, a $200 savings account. Let the loan number
serve
ras
(branch-name
loan)) account.
the
account number
for(borrower
the new savings
1 
= “Perryridge”
account  account  branch-name, account-number,200 (r1)
depositor  depositor  customer-name, loan-number(r1)
nbraha-bazat e te dhenave
137
Updating
A mechanism to change a value in a
tuple without charging all values in the
tuple
Use the generalized projection
operator to do this task
r   F1, F2, …, FI, (r)
Each Fi is either
the ith attribute of r, if the ith attribute
is not updated, or,
if the attribute is to be updated Fi is an
expression, involving only constants and the
attributes of r, which gives the new value
for the attribute
nbraha-bazat e te dhenave
138
Update
Examples
Make interest payments by increasing all
balances
by
5
percent.
account  
(account)
AN, BN, BAL * 1.05
where AN, BN and BAL stand for account-number, branchname and balance, respectively.
 Pay all accounts with balances over $10,000 6 percent
interest
and pay all others 5 percent
account 
(account))
 AN, BN, BAL * 1.06 ( BAL  10000
 AN, BN, BAL * 1.05 (BAL  10000
(account))
nbraha-bazat e te dhenave
139
Views
In some cases, it is not desirable for
all users to see the entire logical model
(i.e., all the actual relations stored in
the database.)
Consider a person who needs to know a
customer’s loan number but has no
need to see the loan amount. This
person should see a relation described,
in the relational algebra, by
customer-name, loan-number (borrower
loan)
Any relation that is not of the
conceptual model but is made visible to
a user as a “virtual relation” is called a
view.
nbraha-bazat e te dhenave
140
View Definition
A view is defined using the create
view statement which has the form
create view v as <query expression
where <query expression> is any legal
relational algebra query expression.
The view name is represented by v.
Once a view is defined, the view name
can be used to refer to the virtual
relation that the view generates.
View definition is not the same as
creating a new relation by evaluating
nbraha-bazat e te dhenave
141
the query expression
View Examples
Consider the view (named allbranch-name, customer-name (depositor account)
customer) consisting of branches and
 branch-name, customer-name (borrower loan)
their
customers.
create view all-customer as
 We can find all customers of the Perryridge branch by writing:
branch-name
(branch-name = “Perryridge” (allcustomer))
nbraha-bazat e te dhenave
142
Updates Through View
Database modifications expressed as
views must be translated to
modifications of the actual relations in
the database.
Consider the person who needs to see
all loan data in the loan relation except
amount. The view given to the person,
branch-loan, is defined as:
create view branch-loan as
branch-name, loan-number (loan)
Since we allow a view name to appear
wherever a relation name is allowed,
nbraha-bazat e te dhenave
143
Updates Through Views
(Cont.)
The previous insertion must be
represented by an insertion into the
actual relation loan from which the view
branch-loan is constructed.
An insertion into loan requires a value
for amount. The insertion can be dealt
with by either.
rejecting the insertion and returning an
error message to the user.
inserting a tuple (“L-37”, “Perryridge”, null)
into the loan relation
Some updates through views are
impossible to translate into database
relation updates
create view v as branch-name = “Perryridge”
(account))
nbraha-bazat e te dhenave
144
Views Defined Using Other
Views
One view may be used in the
expression defining another view
A view relation v1 is said to depend
directly on a view relation v2 if v2 is
used in the expression defining v1
A view relation v1 is said to depend on
view relation v2 if either v1 depends
directly to v2 or there is a path of
dependencies from v1 to v2
A view relation v is said to be
recursive if it depends on itself.
nbraha-bazat e te dhenave
145
View Expansion
A way to define the meaning of views
defined in terms of other views.
Let view v1 be defined by an
expression e1 that may itself contain
uses of view relations.
View expansion of an expression
repeats the following replacement
step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the
expression defining vi
nbraha-bazat e te dhenave
146
until no more view relations are
Tuple Relational Calculus
A nonprocedural query language, where
each query is of the form
{t | P (t) }
It is the set of all tuples t such that
predicate P is true for t
t is a tuple variable, t[A] denotes the
value of tuple t on attribute A
t  r denotes that tuple t is in relation r
P is a formula similar to that of the
predicate calculus
nbraha-bazat e te dhenave
147
Predicate Calculus Formula
1. Set of attributes and constants
2.Set of comparison operators: (e.g., ,
, , , , )
3.Set of connectives: and (), or (v)‚
not ()
4.Implication (): x  y, if x if true,
then y is true
x  y x v y
5.Set of quantifiers:
  t  r (Q(t))  ”there exists” a tuple in t
in relation r
true
such that predicate Q(t) is
nbraha-bazat e te dhenave
148
Banking Example
branch (branch-name, branch-city,
assets)
customer (customer-name, customerstreet, customer-city)
account (account-number, branchname, balance)
loan (loan-number, branch-name,
amount)
depositor (customer-name, accountnumber)
borrower (customer-name, loannumber)
nbraha-bazat e te dhenave
149
Example Queries
{t | t  loan  t [amount]  1200}
Find the loan-number, branch-name,
and amount for loans of over $1200
Find the loan number for each loan of an amount greater than
$1200
{t |  s loan (t[loan-number] = s[loan-number]  s [amount] 
1200)}
Notice that a relation on schema [loan-number] is implicitly
defined by the query
nbraha-bazat e te dhenave
150
Example Queries
{t
| s the
borrower(
t[customer-name]
= s[customer-name])
Find
names
of all customers
having
 u  depositor( t[customer-name] = u[customer-name])
a loan, an account, or both at the bank
 Find the names of all customers who have a loan and an
account
at the bank
{t | s  borrower( t[customer-name] = s[customername])
 u  depositor( t[customer-name] = u[customername])
nbraha-bazat e te dhenave
151
Example Queries
{t Find
| s  borrower(t[customer-name]
= s[customer-name]
the names of all customers
having
 u  loan(u[branch-name] = “Perryridge”
a loan at the
Perryridge
branch
 u[loan-number]
= s[loan-number]))}
 Find the names of all customers who have a loan at the
Perryridge branch, but no account at any branch of the bank
{t | s  borrower( t[customer-name] = s[customername]
 u  loan(u[branch-name] = “Perryridge”
 u[loan-number] = s[loan-number]))
 not v  depositor (v[customer-name] =
t[customer-name]) }
nbraha-bazat e te dhenave
152
Example Queries
{t | s
 loan(s[branch-name]
Find
the names of= “Perryridge”
all customers having
u  borrower
= s[loan-number]
a loan
from (u[loan-number]
the Perryridge
branch,
 t [customer-name] = u[customer-name])
and
cities (u[customer-name]
they live in = v[customer-name]
 the
v  customer
 t[customer-city] = v[customer-city])
nbraha-bazat e te dhenave
153
Example Queries
{t | Find
c  customer
(t[customer.name]
= c[customer-name])
the names
of all customers
who 
 s account
 branch(s[branch-city]
= “Brooklyn” 
have an
at all branches
 u  account
( s[branch-name] = u[branch-name]
located
in Brooklyn:
  s  depositor ( t[customer-name] = s[customer-name]
 s[account-number] = u[account-number] )) )}
nbraha-bazat e te dhenave
154
Safety of Expressions
It is possible to write tuple calculus
expressions that generate infinite
relations.
For example, {t |  t r} results in an
infinite relation if the domain of any
attribute of relation r is infinite
To guard against the problem, we
restrict the set of allowable
expressions to safe expressions.
An expression {t | P(t)} in the tuple
relational calculus is safe if every
component of t appears in one of the
nbraha-bazat e te dhenave
relations, tuples,
or constants that 155
Domain Relational Calculus
A nonprocedural query language
equivalent in power to the tuple
relational calculus
Each query is an expression of the
form:
xn)}
{  x1, x2, …, xn  | P(x1, x2, …,
x1, x2, …, xn represent domain variables
P represents a formula similar to that of
the predicate calculus
nbraha-bazat e te dhenave
156
Example Queries
Find the loan-number, branch-name, and
amount for loans of over $1200
{ l, b, a  |  l, b, a   loan  a > 1200}
 Find the names of all customers who have a loan of over $1200
{ c  |  l, b, a ( c, l   borrower   l, b, a   loan  a > 120
 Find the names of all customers who have a loan from the
Perryridge branch and the loan amount:
{ c, a  |  l ( c, l   borrower  b( l, b, a   loan 
b = “Perryridge”))}
or { c, a  |  l ( c, l   borrower   l, “Perryridge”, a   loan)}
nbraha-bazat e te dhenave
157
Example Queries
{ Find
c  |  lthe
({ c,names
l   borrower
of
all
customers
having
  b,a( l, b, a   loan  b = “Perryridge”))
a loan,
ana account,
 a( c,
 depositor or both at the
Perryridge
  b,n(branch:
a, b, n   account  b = “Perryridge”))}
 Find the names of all customers who have an account at
all
branches located in Brooklyn:
{ c  |  s, n ( c, s, n   customer) 
 x,y,z( x, y, z   branch  y = “Brooklyn”) 
 a,b( x, y, z   account   c,a   depositor)}
nbraha-bazat e te dhenave
158
Safety of Expressions
{  x1, x2, …, xn  | P(x1, x2, …, xn)}
is safe if all of the following hold:
1.All values that appear in tuples of
the expression are values
from
dom(P) (that is, the values appear
either in P or in a tuple
of a relation
mentioned in P).
2.For every “there exists” subformula
of the form  x (P1(x)), the
subformula is true if an only if P1(x) is
true for all values x from
dom(P1).
nbraha-bazat e te dhenave
159
End of Chapter 3
nbraha-bazat e te dhenave
160
Result of  branch-name =
“Perryridge” (loan)
nbraha-bazat e te dhenave
161
Loan Number and the Amount
of the Loan
nbraha-bazat e te dhenave
162
Names of All Customers
Who Have Either a Loan or
an Account
nbraha-bazat e te dhenave
163
Customers With An Account
But No Loan
nbraha-bazat e te dhenave
164
Result of borrower  loan
nbraha-bazat e te dhenave
165
Result of  branch-name = “Perryridge”
(borrower  loan)
nbraha-bazat e te dhenave
166
Result of customer-name
nbraha-bazat e te dhenave
167
Result of the Subexpression
nbraha-bazat e te dhenave
168
Largest Account Balance in
the Bank
nbraha-bazat e te dhenave
169
Customers Who Live on the Same
Street and In the Same City as
Smith
nbraha-bazat e te dhenave
170
Customers With Both an
Account and a Loan at the
Bank
nbraha-bazat e te dhenave
171
Result of customer-name, loanloan)
number, amount (borrower
nbraha-bazat e te dhenave
172
Result of branchname(customer-city =
account
“Harrison”(customer
depositor))
nbraha-bazat e te dhenave
173
Result of branch-name(branch-city =
(branch))
“Brooklyn”
nbraha-bazat e te dhenave
174
Result of customer-name, branchaccount)
name(depositor
nbraha-bazat e te dhenave
175
The credit-info Relation
nbraha-bazat e te dhenave
176
Result of customer-name, (limit –
credit-balance) as credit-
(credit-info).
available
nbraha-bazat e te dhenave
177
The pt-works Relation
nbraha-bazat e te dhenave
178
The pt-works Relation
After Grouping
nbraha-bazat e te dhenave
179
Result of branch-name  sum(salary)
(pt-works)
nbraha-bazat e te dhenave
180
Result of branch-name  sum salary,
max(salary) as max-salary (pt-works)
nbraha-bazat e te dhenave
181
The employee and ft-works
Relations
nbraha-bazat e te dhenave
182
The Result of employee
works
nbraha-bazat e te dhenave
ft-
183
The Result of employee
ft-works
nbraha-bazat e te dhenave
184
Result of employee
works
nbraha-bazat e te dhenave
ft-
185
Result of employee
works
nbraha-bazat e te dhenave
ft-
186
Tuples Inserted Into loan and
borrower
nbraha-bazat e te dhenave
187
Names of All Customers
Who Have a Loan at the
Perryridge Branch
nbraha-bazat e te dhenave
188
E-R Diagram
nbraha-bazat e te dhenave
189
The branch Relation
nbraha-bazat e te dhenave
190
The loan Relation
nbraha-bazat e te dhenave
191
The borrower Relation
nbraha-bazat e te dhenave
192
Chapter 4: SQL
Basic Structure
Set Operations
Aggregate Functions
Null Values
Nested Subqueries
Derived Relations
Views
Modification of the Database
Joined Relations
Data Definition Language
nbraha-bazat e te dhenave
Embedded
SQL, ODBC and JDBC193
Schema Used in Examples
nbraha-bazat e te dhenave
194
Basic Structure
SQL is based on set and relational
operations with certain modifications
and enhancements
A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Ais represent attributes
ris represent relations
P is a predicate.
This query is equivalent to the
relational algebra expression.
x rm))
A1, A2, ..., An(P (r1 x r2 x ...
nbraha-bazat e te dhenave
195
The select Clause
The select clause list the attributes
desired in the result of a query
corresponds to the projection operation of
the relational algebra
E.g. find the names of all branches in
the loan relation
select branch-name
from loan
In the “pure” relational algebra syntax,
the query would be:
branch-name(loan)
NOTE: SQL does not permit the ‘-’
character in names,
Use, e.g., branch_name instead of branchname in a real implementation.
nbraha-bazat e te dhenave
196
The select Clause (Cont.)
SQL allows duplicates in relations as
well as in query results.
To force the elimination of duplicates,
insert the keyword distinct after
select.
Find the names of all branches in the
loan relations, and remove duplicates
select distinct branch-name
from loan
The keyword all specifies that
duplicates not be removed.
select
all branch-name
nbraha-bazat e te dhenave
197
The select Clause (Cont.)
An asterisk in the select clause
denotes “all attributes”
select *
from loan
The select clause can contain
arithmetic expressions involving the
operation, +, –, , and /, and operating
on constants or attributes of tuples.
The query:
select loan-number, branchname, amount  100
from loan
nbraha-bazat e te dhenave
198
The where Clause
The where clause specifies conditions
that the result must satisfy
corresponds to the selection predicate of
the relational algebra.
To find all loan number for loans made
at the Perryridge branch with loan
amounts greater than $1200.
select loan-number
from loan
where branch-name = ‘Perryridge’
and amount > 1200
Comparison results can be combined
using the logical
connectives
and, or,199
nbraha-bazat
e te dhenave
The where Clause (Cont.)
SQL includes a between comparison
operator
E.g. Find the loan number of those
loans with loan amounts between
select loan-number
$90,000
and $100,000 (that is,
from loan
$90,000
and
$100,000)
where amount between 90000 and 100000
nbraha-bazat e te dhenave
200
The from Clause
The from clause lists the relations involved
in the query
corresponds to the Cartesian product operation
of the relational algebra.
Find the Cartesian product borrower x loan
select 
borrower,
loan of all
 Find the name,from
loan number
and loan amount
customers
having a loan at the Perryridge branch.
select customer-name, borrower.loan-number, amount
from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = ‘Perryridge’
nbraha-bazat e te dhenave
201
The Rename Operation
The SQL allows renaming relations and
attributes using the as clause:
old-name as new-name
Find the name, loan number and loan
amount of all customers; rename the
select customer-name, borrower.loan-number as loan-id,
column
amount name loan-number as loan-id.
from borrower, loan
where borrower.loan-number = loan.loan-number
nbraha-bazat e te dhenave
202
Tuple Variables
Tuple variables are defined in the
from
clause via the use of the as
select customer-name, T.loan-number, S.amount
clause.
from borrower as T, loan as S
where T.loan-number = S.loan-number
Find the customer names and their
loan
numbers
for allthat
customers
 Find
the names
of all branches
have greater having
assets a
than
loan at some branch.
some branch located in Brooklyn.
select distinct T.branch-name
from branch as T, branch as S
where T.assets > S.assets and S.branch-city = ‘Brooklyn’
nbraha-bazat e te dhenave
203
String Operations
SQL includes a string-matching
operator for comparisons on character
strings. Patterns are described using
two special characters:
percent (%). The % character matches any
substring.
underscore (_). The _ character matches
any character.
Find the names of all customers whose
street includes the substring “Main”.
select customer-name
from customer
where customer-street like
‘%Main%’
Match the name “Main%”
nbraha-bazat e te dhenave
204
Ordering the Display of
Tuples
List in alphabetic order the names of
all customers having a loan in
Perryridge branch
select distinct customer-name
from
borrower, loan
where borrower loan-number loan.loan-number and
branch-name = ‘Perryridge’
order by customer-name
We may specify desc for descending
order or asc for ascending order, for
each attribute; ascending order is the
nbraha-bazat e te dhenave
205
default.
Duplicates
In relations with duplicates, SQL can
define how many copies of tuples
appear in the result.
Multiset versions of some of the
relational algebra operators – given
multiset relations r1 and r2:
1.
 (r1): If there are c1 copies of tuple t1
in r1, and t1 satisfies selections ,, then
there are c1 copies of t1 in  (r1).
2. A(r): For each copy of tuple t1 in r1,
there is a copy of tuple A(t1) in A(r1)
where A(t1) denotes the projection of
the single tuple
t1. e te dhenave
nbraha-bazat
206
Duplicates (Cont.)
Example: Suppose multiset
relations r1 (A, B) and r2 (C) are as
follows:
r1 = {(1, a) (2,a)} r2 = {(2),
(3), (3)}
Then B(r1) would be {(a), (a)},
while B(r1) x r2 would be
{(a,2), (a,2), (a,3), (a,3), (a,3),
(a,3)}
SQL duplicate semantics:
select A1,, A2, ..., An
from r1, r2, ..., rm
where P
is equivalent to the multiset
nbraha-bazat e te dhenave
version of the expression:
207
Set Operations
The set operations union, intersect,
and except operate on relations and
correspond to the relational algebra
operations 
Each of the above operations
automatically eliminates duplicates; to
retain all duplicates use the
corresponding multiset versions union
all, intersect all and except all.
Suppose a tuple occurs m times in r
and n times in s, then, it occurs:
m + n timesnbraha-bazat
in r union
all s
e te dhenave
208
Set Operations
(select customer-name from depositor)
union
Find all(select
customers
whofrom
have
a loan,
customer-name
borrower)

account, or both:
an
Find all customers who have both a loan and an account.
(select customer-name from depositor)
intersect
(select customer-name from borrower)
 Find all customers who have an account but no loan.
(select customer-name from depositor)
except
(select customer-name from borrower)
nbraha-bazat e te dhenave
209
Aggregate Functions
These functions operate on the
multiset of values of a column of a
relation, and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
nbraha-bazat e te dhenave
210
Aggregate Functions (Cont.)
select avg (balance)
from account
Find thewhere
average
account
balance
branch-name
= ‘Perryridge’
the Perryridge branch.
at
 Find the number of tuples in the customer relation.
select count (*)
from customer
 Find the number of depositors in the bank.
select count (distinct customer-name)
from depositor
nbraha-bazat e te dhenave
211
Aggregate Functions – Group
By
Find the number of depositors for each
branch.
select branch-name, count (distinct customer-name)
from depositor, account
where depositor.account-number = account.account-num
group by branch-name
Note: Attributes in select clause outside of aggregate
functions must
appear in group by list
nbraha-bazat e te dhenave
212
Aggregate Functions –
Having Clause
selectthe
branch-name,
Find
names avg
of (balance)
all branches where
from account
the average
balance is more
group byaccount
branch-name
than $1,200.
having avg (balance) > 1200
Note: predicates in the having clause are applied after the
formation of groups whereas predicates in the
where
clause are applied before forming groups
nbraha-bazat e te dhenave
213
Null Values
It is possible for tuples to have a null
value, denoted by null, for some of
their attributes
null signifies an unknown value or that
a value does not exist.
The predicate is null can be used to
check for null values.
E.g. Find all loan number which appear in
the loan relation with null values for
amount.
select loan-number
from loan
where amount is null
The result of any arithmetic
expression involving null is null
nbraha-bazat e te dhenave
214
Null Values and Three
Valued Logic
Any comparison with null returns
unknown
E.g. 5 < null or null <> null
null
or
null =
Three-valued logic using the truth
value unknown:
OR: (unknown or true) = true, (unknown or
false) = unknown
(unknown or unknown) = unknown
AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
NOT: (not unknown) = unknown
nbraha-bazat e te dhenave
215
“P is unknown” evaluates to true if
Null Values and Aggregates
Total all loan amounts
select sum (amount)
from loan
Above statement ignores null amounts
result is null if there is no non-null amount,
that is the
All aggregate operations except
count(*) ignore tuples with null values
on the aggregated attributes.
nbraha-bazat e te dhenave
216
Nested Subqueries
SQL provides a mechanism for
the nesting of subqueries.
A subquery is a select-fromwhere expression that is nested
within another query.
A common use of subqueries is to
perform tests for set
membership, set comparisons, and
set cardinality.
nbraha-bazat e te dhenave
217
Example Query
select distinct customer-name
Find allfrom
customers
who have both an
borrower
where
in (select
customer-name
account
andcustomer-name
a loan at the
bank.
from depositor)
 Find all customers who have a loan at the bank but do
not have
an account at the bank
select distinct customer-name
from borrower
where customer-name not in (select customer-name
from depositor)
nbraha-bazat e te dhenave
218
Example Query
select
customer-name
Finddistinct
all customers
who have both an
from borrower, loan
account
and a loan at the Perryridge
where borrower.loan-number = loan.loan-number and
branch
branch-name = “Perryridge” and
(branch-name, customer-name) in
(select branch-name, customer-name
from depositor, account
where depositor.account-number =
account.account-number)
 Note: Above query can be written in a much simpler
manner. The
formulation above is simply to illustrate SQL features.
(Schema used in this example)
nbraha-bazat e te dhenave
219
Set Comparison
select distinct T.branch-name
Find all from
branches
have
branch asthat
T, branch
as Sgreater
assets than
in
where some
T.assetsbranch
> S.assetslocated
and
S.branch-city =
Brooklyn.
‘Brooklyn’
 Same query using > some clause
select branch-name
from branch
where assets > some
(select assets
from branch
where branch-city = ‘Brooklyn’)
nbraha-bazat e te dhenave
220
Definition of Some Clause
0
F
<comp>
some
r

t

r
s.t.
(5<
5 )=
(F <comp>
) 5 < some tuple in the relation)
(read:
some
true t
6
Where <comp> can be:

0
5
) = false
(5 = some
0
5
) = true
(5  some
0
5
) = true (since 0  5)
(5<
some
(= some)  in
However, ( some)  not in
nbraha-bazat e te dhenave
221
Definition of all Clause
0
(5< all 5 all
) = false
F <comp>
r t  r (F
<comp> 6t)
6
(5< all 10
) = true
(5 = all
4
5
) = false
(5  all
4
6
) = true (since 5  4 and 5  6)
( all)  not in
However, (= all)  in
nbraha-bazat e te dhenave
222
Example Query
select
branch-name
Find
the
names of all branches that
from branch
have greater
assets
where assets
> all than all branches
located in Brooklyn.
(select assets
from branch
where branch-city = ‘Brooklyn’)
nbraha-bazat e te dhenave
223
Test for Empty Relations
The exists construct returns the
value true if the argument
subquery is nonempty.
exists r  r  Ø
not exists r  r = Ø
nbraha-bazat e te dhenave
224
Example Query
Find all customers who have an account
at all branches located in Brooklyn.
select distinct S.customer-name
from depositor as S
where not exists (
(select branch-name
from branch
where branch-city = ‘Brooklyn’)
except
(select R.branch-name
from depositor as T, account as R
where T.account-number = R.account-number and
S.customer-name = T.customer-name))
 (Schema used in this example)
 Note that X – Y = Ø  X Y
 Note: Cannot write this query using = all and its variants
nbraha-bazat e te dhenave
225
Test for Absence of
Duplicate Tuples
The unique construct tests whether a
subquery has any duplicate tuples in its
result.
Find all customers who have at most
one account at the Perryridge branch.
select T.customer-name
from depositor as T
where unique (
select R.customer-name
from account, depositor as R
where T.customer-name =
R.customer-name and
R.account-number =
account.account-number and
account.branch-name
=
nbraha-bazat e te dhenave
226
Example Query
select
T.customer-name
Find distinct
all customers
who have at least
from depositor T
two accounts
where
not unique ( at the Perryridge
branch.
select R.customer-name
from account, depositor as R
where T.customer-name = R.customer-name
and
R.account-number = account.account-number
and
account.branch-name = ‘Perryridge’)
(Schema used in this example)
nbraha-bazat e te dhenave
227
Views
Provide a mechanism to hide certain
data from the view of certain users.
where:
To
create
a view
the command:
<query
expression>
is any we
legal use
expression
create view v as <query expression>
The view name is represented by v
nbraha-bazat e te dhenave
228
Example Queries
A view consisting of branches and their
customers
create view all-customer as
(select branch-name, customer-name
from depositor, account
where depositor.account-number = account.account-number)
union
(select branch-name, customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number)
 Find all customers of the Perryridge branch
select customer-name
from all-customer
where branch-name = ‘Perryridge’
nbraha-bazat e te dhenave
229
Derived Relations
Find the average account balance of
those branches where the average
account balance is greater than $1200.
select branch-name, avg-balance
from (select branch-name, avg
(balance)
from account
group by branch-name)
as result (branch-name, avg-
balance)
where avg-balance > 1200
Note that we do not need to use the
nbraha-bazat
dhenave
having clause,
sincee tewe
compute the
230
With Clause
With clause allows views to be defined
locally to a query, rather than globally.
Analogous to procedures in a
programming language.
Find all accounts with the maximum
balance
with max-balance(value) as
select max (balance)
from account
select account-number
from account, max-balance
where account.balance
nbraha-bazat e te dhenave = max-
231
Complex Query using With
Clause
Find
all branches
where
with
branch-total
(branch-name,
value)the
as total
select branch-name,
sum
(balance) than the
account
deposit is
greater
from account
average
of the total account deposits
group by branch-name
atbranch-total-avg(value)
all branches. as
with
select avg (value)
from branch-total
select branch-name
from branch-total, branch-total-avg
where branch-total.value >= branch-total-avg.value
nbraha-bazat e te dhenave
232
Modification of the
Database – Deletion
Delete all account records at the
Perryridge branch
delete from account
where branch-name =
‘Perryridge’
Delete all accounts at every branch
located in Needham city.
delete from account
where branch-name in (select branch-
name
‘Needham’)
from branch
where branch-city =
delete from depositor
where account-number in
(select account-
nbraha-bazat e te dhenave
233
Example Query
Delete
record of all accounts with
delete
from the
account
where balance
< (select
(balance) at the
balances
below
theavg
average
from account)
bank.
 Problem: as we delete tuples from deposit, the average balance
changes
 Solution used in SQL:
1.
First, compute avg balance and find all tuples to delete
2.
Next, delete all tuples found above (without recomputing avg or
retesting the tuples)
nbraha-bazat e te dhenave
234
Modification of the
Database – Insertion
Add a new tuple to account
insert into account
values (‘A-9732’,
‘Perryridge’,1200)
or equivalently
insert into account (branch-name,
balance, account-number)
values (‘Perryridge’, 1200, ‘A9732’)
Add a new tuple to account with
balance set to null
nbraha-bazat e te dhenave
insert into
account
235
Modification of the
Database – Insertion
Provide as a gift for all loan customers
of the Perryridge branch, a $200
savings account. Let the loan number
serve as the account number for the
new savings account
insert into account
select loan-number, branch-name,
200
from loan
where branch-name = ‘Perryridge’
insert into depositor
select customer-name, loan-number
from loan, borrower
where branch-name = ‘Perryridge’
and loan.account-number =
nbraha-bazat e te dhenave
borrower.account-number
236
Modification of the
Database – Updates
Increase all accounts with balances
over $10,000 by 6%, all other
accounts receive 5%.
Write two update statements:
update account
set balance = balance  1.06
where balance > 10000
update account
set balance = balance  1.05
where balance  10000
The order is important
Can be done better using the case
nbraha-bazat
e te dhenave
statement (next
slide)
237
Case Statement for
Conditional Updates
Same query as before: Increase all
accounts with balances over $10,000
by 6%, all other accounts receive 5%.
update account
set balance = case
when balance <=
10000 then balance *1.05
else balance *
1.06
end
nbraha-bazat e te dhenave
238
Update
of
a
View
Create a view of all loan data in loan
relation, hiding the amount attribute
create view branch-loan as
select branch-name, loan-
number
from loan
Add a new tuple to branch-loan
insert into branch-loan
values (‘Perryridge’, ‘L-307’)
This insertion must be represented by
the insertion of the tuple
(‘L-307’, ‘Perryridge’, null)
into the loan relation
Updates on more complex views are
nbraha-bazat e teto
dhenave
239
difficult or impossible
translate,
Transactions
A transaction is a sequence of queries
and update statements executed as a
single unit
Transactions are started implicitly and
terminated by one of
commit work: makes all updates of the
transaction permanent in the database
rollback work: undoes all updates performed by
the transaction.
Motivating example
Transfer of money from one account to
another involves two steps:
deduct from one account and credit to another
If one steps succeeds and the other fails,
database is in an inconsistent state
Therefore, either both steps should succeed
or neither should
nbraha-bazat e te dhenave
240
Transactions (Cont.)
In most database systems, each SQL
statement that executes successfully
is automatically committed.
Each transaction would then consist of
only a single statement
Automatic commit can usually be turned
off, allowing multi-statement
transactions, but how to do so depends on
the database system
Another option in SQL:1999: enclose
statements within
begin atomic
…
end
nbraha-bazat e te dhenave
241
Joined Relations
Join operations take two relations and
return as a result another relation.
These additional operations are
typically used as subquery expressions
in the from clause
Join condition – defines which tuples
in the
match, and what
Join
Types two relations
Join Conditions
attributes are natural
present in the result of
inner join
the
join.
left
outer
join
on <predicate>
right outer join
using (A1, A2, ..., An)
Join
full
outertype
join – defines how tuples in each
relation that do not match any tuple in
the other relation (based on the join
nbraha-bazat e te dhenave
242
condition) are
treated.
Joined Relations – Datasets
for Examples
Relation loan
loan-number
branch-name
amount
L-170
Downtown
3000
L-230
Redwood
4000
L-260
Perryridge
1700
 Relation borrower
customer-name loan-number
Jones
L-170
Smith
L-230
Hayes
L-155
 Note: borrower information missing for L-260 and
loan information missing for L-155
nbraha-bazat e te dhenave
243
Joined Relations – Examples
loan-number
L-170
L-230
loan inner join borrower on
3000 = Jones
Downtown
loan.loan-number
Redwood
Smith
4000
borrower.loan-number
branch-name
amount
customer-name loan-number
L-170
L-230
 loan left outer join borrower on
loan.loan-number = borrower.loan-number
loan-number
branch-name
amount
customer-name loan-number
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
L-260
Perryridge
1700
null
nbraha-bazat e te dhenave
null
244
Joined Relations – Examples
loan-number
branch-name
amount
customer-name
loan
natural
inner
join
borrower
L-170
3000
Downtown
Jones
L-230
Redwood
4000
Smith
 loan natural right outer join borrower
loan-number
branch-name
amount
customer-name
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
L-155
null
null
Hayes
nbraha-bazat e te dhenave
245
Joined Relations – Examples
loan full outer join borrower
using (loan-number)
loan-number
branch-name
amount
customer-name
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
L-260
Perryridge
1700
null
L-155
null
null
Hayes
 Find all customers who have either an account or a loan
(but not both) at the bank.
select customer-name
from (depositor natural full outer join borrower)
where account-number is null or loan-number is null
nbraha-bazat e te dhenave
246
Data Definition Language
(DDL)
Allows the specification of not only a set of relations but
also information about each relation, including:
The schema for each relation.
The domain of values associated
with each attribute.
Integrity constraints
The set of indices to be
maintained for each relations.
Security and authorization
information for each relation.
The physical storage structure
nbraha-bazat e te dhenave
247
Domain Types in SQL
char(n). Fixed length character string,
with user-specified length n.
varchar(n). Variable length character
strings, with user-specified maximum
length n.
int. Integer (a finite subset of the
integers that is machine-dependent).
smallint. Small integer (a machinedependent subset of the integer domain
type).
numeric(p,d). Fixed point number, with
user-specified precision of p digits, with
n digits to the right of decimal point.
real, double precision. Floating point
nbraha-bazatfloating
e te dhenave point
248
and double-precision
Date/Time Types in SQL
(Cont.)
date. Dates, containing a (4 digit)
year, month and date
E.g. date ‘2001-7-27’
time. Time of day, in hours, minutes
and seconds.
E.g. time ’09:00:30’
’09:00:30.75’
time
timestamp: date plus time of day
E.g. timestamp ‘2001-7-27 09:00:30.75’
Interval: period of time
E.g. Interval ‘1’ day
Subtracting a date/time/timestamp value
nbraha-bazat
e te interval
dhenave
249
from another
gives an
value
Create Table Construct
An SQL relation is defined using
the create table command:
create table r (A1 D1, A2
D2, ..., An Dn,
(integrityconstraint1),
...,
(integrityconstraintk))
r is the name of the relation
each Ai is an attribute name in the
schema of relation r
e teof
dhenave
Di is the nbraha-bazat
data type
values in the
250
Integrity Constraints in
not nullTable
Create
primary key (A1, ..., An)
check (P), where P is a predicate
Example: Declare branch-name as the primary key
for branch and ensure that the values of assets are
non-negative.
create table branch
(branch-namechar(15),
branch-city char(30)
assets
integer,
primary key (branch-name),
check (assets >= 0))
primary key declaration on an attribute
automatically ensures not null in SQL-92
onwards, needs to be explicitly stated in SQL-89
nbraha-bazat e te dhenave
251
Drop and Alter Table
Constructs
The drop table command deletes all
information about the dropped
relation from the database.
The alter table command is used to
add attributes to an existing
relation.
alter table r add A D
where A is the name of the
attribute to be added to relation r
and D is the domain of A.
All tuples in the relation are assigned
null as the value for the new attribute.
The alter table command can also be
used to drop attributes of a relation
alter
table
r drop A
nbraha-bazat
e te dhenave
252
Embedded SQL
The SQL standard defines
embeddings of SQL in a variety of
programming languages such as Pascal,
PL/I, Fortran, C, and Cobol.
A language to which SQL queries are
embedded is referred to as a host
language, and the SQL structures
permitted in the host language
comprise embedded SQL.
The basic form of these languages
follows that of the System R
embedding of SQL into PL/I.
nbraha-bazat e te dhenave
253
EXEC SQL statement
is used to
Example Query
From within a host language, find the names and cities
of customers with more than the variable amount dollars
in some account.
Specify the query in SQL and declare a
cursor for it
EXEC SQL
declare c cursor for
select customer-name, customer-city
from depositor, customer, account
where depositor.customer-name =
customer.customer-name
and depositor account-number =
account.account-number
and account.balance > :amount
nbraha-bazat e te dhenave
254
Embedded SQL (Cont.)
The open statement causes the query
to be evaluated
EXEC SQL open c END-EXEC
The fetch statement causes the
values of one tuple in the query result
to be placed on host language
variables.
EXEC SQL fetch c into :cn, :cc ENDEXEC
Repeated calls to fetch get successive
tuples in the query result
A variable called SQLSTATE in the
nbraha-bazat e te dhenave
255
Updates Through Cursors
 Can update tuples fetched by cursor by declaring that the
cursor is for update
declare c cursor for
select *
from account
where branch-name = ‘Perryridge’
for update
 To update tuple at the current location of cursor
update account
set balance = balance + 100
where current of c
nbraha-bazat e te dhenave
256
Dynamic SQL
Allows programs to construct and
submit SQL queries at run time.
Example of the use of dynamic SQL
from within a C program.
char * sqlprog = “update account
set balance =
balance * 1.05
where account-number =
?”
EXEC SQL prepare dynprog from
:sqlprog;
char account [10] = “A-101”;
EXEC SQL execute dynprog using
:account;
nbraha-bazat e te dhenave
257
ODBC
Open DataBase Connectivity(ODBC)
standard
standard for application program to
communicate with a database server.
application program interface (API) to
open a connection with a database,
send queries and updates,
get back results.
Applications such as GUI,
spreadsheets, etc. can use ODBC
nbraha-bazat e te dhenave
258
ODBC
(Cont.)
Each database system supporting
ODBC provides a "driver" library that
must be linked with the client program.
When client program makes an ODBC
API call, the code in the library
communicates with the server to carry
out the requested action, and fetch
results.
ODBC program first allocates an SQL
environment, then a database
connection handle.
Opens database connection using
SQLConnect(). Parameters for
SQLConnect:
connection handle,
nbraha-bazat e te dhenave
the server to which to connect
259
ODBC Code
int ODBCexample()
{
RETCODE error;
HENV env; /* environment */
HDBC conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, "aura.bell-labs.com",
SQL_NTS, "avi", SQL_NTS, "avipasswd",
SQL_NTS);
{ …. Do actual work … }
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env);
}
nbraha-bazat e te dhenave
260
ODBC Code (Cont.)
Program sends SQL commands to the
database by using SQLExecDirect
Result tuples are fetched using
SQLFetch()
SQLBindCol() binds C language variables
to attributes of the query result
When a tuple is fetched, its attribute values are
automatically stored in corresponding C variables.
Arguments to SQLBindCol()
ODBC stmt variable, attribute position in query result
The type conversion from SQL to C.
The address of the variable.
For variable-length types like character arrays,
The maximum length of the variable
Location to store actual length when a tuple is fetched.
Note: A negative value returned for the length field
indicates null value
Good programming
requires checking
nbraha-bazat e te dhenave
261
ODBC
Code
(Cont.)
Main body of program
char branchname[80];
float balance;
int lenOut1, lenOut2;
HSTMT stmt;
SQLAllocStmt(conn, &stmt);
char * sqlquery = "select branch_name, sum
(balance)
from account
group by branch_name";
error = SQLExecDirect(stmt, sqlquery,
SQL_NTS);
if (error == SQL_SUCCESS) {
SQLBindCol(stmt, 1, SQL_C_CHAR,
branchname , 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT,
&balance,
0 , &lenOut2);
while (SQLFetch(stmt)
>=
nbraha-bazat e te dhenave
262
More ODBC Features
Prepared Statement
SQL statement prepared: compiled at the
database
Can have placeholders: E.g. insert into
account values(?,?,?)
Repeatedly executed with actual values for the
placeholders
Metadata features
finding all the relations in the database and
finding the names and types of columns of a
query result or a relation in the database.
By default, each SQL statement is
treated as a separate transaction
is committed automatically.
that
Can turn off automatic commit on a connection
SQLSetConnectOption(conn,
nbraha-bazat
e te dhenave
SQL_AUTOCOMMIT,
0)}
263
ODBC Conformance Levels
Conformance levels specify subsets of
the functionality defined by the
standard.
Core
Level 1 requires support for metadata
querying
Level 2 requires ability to send and
retrieve arrays of parameter values and
more detailed catalog information.
SQL Call Level Interface (CLI)
standard similar to ODBC interface,
but with some minor differences.
nbraha-bazat e te dhenave
264
JDBC
JDBC is a Java API for communicating
with database systems supporting
SQL
JDBC supports a variety of features
for querying and updating data, and
for retrieving query results
JDBC also supports metadata
retrieval, such as querying about
relations present in the database and
the names and types of relation
attributes
Model for communicating with the
265
database: nbraha-bazat e te dhenave
JDBC
Code
public static
void JDBCexample(String dbid,
String userid, String passwd)
{
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@aura.bell-labs.com:2000:bankdb",
userid, passwd);
Statement stmt = conn.createStatement();
… Do Actual Work ….
stmt.close();
conn.close();
}
catch (SQLException sqle) {
System.out.println("SQLException : " +
sqle);
}
nbraha-bazat e te dhenave
266
JDBC Code (Cont.)
Update to database
try {
stmt.executeUpdate( "insert into account
values
('A-9732',
'Perryridge', 1200)");
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. "
+ sqle);
}
Execute query and fetch and print
results
ResultSet rset = stmt.executeQuery( "select
branch_name, avg(balance)
from
account
nbraha-bazat e te dhenave
group by267
JDBC Code Details
Getting result fields:
rs.getString(“branchname”) and
rs.getString(1) equivalent if
branchname is the first argument of
select result.
Dealing with Null values
int a = rs.getInt(“a”);
if (rs.wasNull())
Systems.out.println(“Got null value”);
nbraha-bazat e te dhenave
268
Prepared Statement
Prepared statement allows queries to be
compiled and executed multiple times
with different arguments
PreparedStatement pStmt =
conn.prepareStatement(
“insert into
account values(?,?,?)”); pStmt.setString(1,
"A-9732");
pStmt.setString(2, "Perryridge");
pStmt.setInt(3, 1200);
pStmt.executeUpdate();
pStmt.setString(1, "A-9733");
pStmt.executeUpdate();
Beware: If value to be stored in
nbraha-bazat
e te dhenave
database contains
a single
quote or
269
Other SQL Features
SQL sessions
client connects to an SQL server,
establishing a session
executes a series of statements
disconnects the session
can commit or rollback the work
carried out in the session
An SQL environment contains
several components, including a
user identifier, and a schema,
which identifies which of several
schemas a session is using.
nbraha-bazat e te dhenave
270
Schemas, Catalogs, and
Environments
Three-level hierarchy for naming
relations.
Database contains multiple catalogs
each catalog can contain multiple schemas
SQL objects such as relations and views
are contained within a schema
e.g. catalog5.bank-schema.account
Each user has a default catalog and
schema, and the combination is unique
to the user.
Default catalog and schema are set up
for a connection
nbraha-bazat e te dhenave
271
Procedural Extensions and
Stored Procedures
SQL provides a module language
permits definition of procedures in SQL,
with if-then-else statements, for and while
loops, etc.
more in Chapter 9
Stored Procedures
Can store procedures in the database
then execute them using the call
statement
permit external applications to operate on
the database without knowing about
internal details
These features are covered in Chapter
9 (Object Relational Databases)
nbraha-bazat e te dhenave
272
Extra Material on JDBC
and Application
Architectures
nbraha-bazat e te dhenave
273
Transactions in JDBC
As with ODBC, each statement gets
committed automatically in JDBC
To turn off auto commit use
conn.setAutoCommit(false);
To commit or abort transactions use
conn.commit() or
conn.rollback()
To turn auto commit on again, use
conn.setAutoCommit(true);
nbraha-bazat e te dhenave
274
Procedure and Function Calls
in JDBC
JDBC provides a class
CallableStatement which allows SQL
stored procedures/functions to be
invoked.
CallableStatement cs1 =
conn.prepareCall( “{call proc (?,?)}” ) ;
CallableStatement cs2 =
conn.prepareCall( “{? = call func
(?,?)}” );
nbraha-bazat e te dhenave
275
Result Set MetaData
The class ResultSetMetaData
provides information about all the
columns of the ResultSet.
Instance of this class is obtained by
getMetaData( ) function of ResultSet.
Provides Functions for getting number
of columns, column name, type,
precision, scale, table from which the
column is derived etc.
ResultSetMetaData rsmd =
rs.getMetaData ( );
for ( int i = 1; i <=
nbraha-bazat e te dhenave
276
Database Meta Data
The class DatabaseMetaData provides
information about database relations
Has functions for getting all tables, all
columns of the table, primary keys etc.
E.g. to print column names and types of a
relation
DatabaseMetaData dbmd =
conn.getMetaData( );
ResultSet rs = dbmd.getColumns( null,
“BANK-DB”, “account”, “%” );
//Arguments: catalog, schemapattern, table-pattern, column-pattern
// Returns: 1 row for each
column, with several attributes such as
//
nbraha-bazat e COLUMN_NAME,
te dhenave
277
Application Architectures
Applications can be built using one
of two architectures
Two tier model
Application program running at user
site directly uses JDBC/ODBC to
communicate with the database
Three tier model
Users/programs running at user sites
communicate with an application server.
The application server in turn
communicates with the database
nbraha-bazat e te dhenave
278
Two-tier Model
E.g. Java code runs at client site and
uses JDBC to communicate with the
backend server
Benefits:
flexible, need not be restricted to
predefined queries
Problems:
Security: passwords available at client
site, all database operation possible
More code shipped to client
Not appropriate across organizations, or
in large ones like universities
nbraha-bazat e te dhenave
279
Three Tier Model
CGI Program
Application/HTTP
Server
Servlets
JDBC
Database
Server
HTTP/Application Specific Protocol
Network
Client
Client
Client
nbraha-bazat e te dhenave
280
Three-tier Model (Cont.)
E.g. Web client + Java Servlet using
JDBC to talk with database server
Client sends request over http or
application-specific protocol
Application or Web server receives
request
Request handled by CGI program or
servlets
Security handled by application at
server
Better security
Fine granularity
security
nbraha-bazat e te dhenave
281
End of Chapter
nbraha-bazat e te dhenave
282
The loan and borrower
Relations
nbraha-bazat e te dhenave
283
The Result of loan inner
join borrower on loan.loan-
number = borrower.loannumber
nbraha-bazat e te dhenave
284
The Result of loan left
outer join borrower on
loan-number
nbraha-bazat e te dhenave
285
The Result of loan natural
inner join borrower
nbraha-bazat e te dhenave
286
Join Types and Join
Conditions
nbraha-bazat e te dhenave
287
The Result of loan natural
right outer join borrower
nbraha-bazat e te dhenave
288
The Result of loan full
outer join borrower
using(loan-number)
nbraha-bazat e te dhenave
289
SQL Data Definition for Part of
the Bank Database
nbraha-bazat e te dhenave
290
“record based logical model”
1.
2.
3.
4.
Ky model mundeson shikim dhe
pershkrimin e te dhenave ne nivelin
logjike dhe shikues.Quhet keshtu se
te dhenat na ipen ne forme te
fiksuare .Dallojme disa modele te
kesaj trajte si
Modeli racional(rational model)
Modeli I rrjetes(network model)
Modeli hirearkik(hierarchical model)
Ndryshimet ne mes te
modeleve(differences among the
models)
nbraha-bazat e te dhenave
291
Nje shembull tjeter I
modelit relacional
Atributet
Customer-id
customername
192-83-7465
Johnson
019-28-3746
Smith
192-83-7465
Johnson
321-12-3123
Jones
019-28-3746
Smith
customerstreet
customercity
accountnumber
Alma
Palo Alto
A-101
North
Rye
A-215
Alma
Palo Alto
A-201
Main
Harrison
A-217
North
Rye
A-201
nbraha-bazat e te dhenave
292
Shembull i nje baze relacionale
nbraha-bazat e te dhenave
293
“network model”
Modeli I rrjetes eshte I paraqitur si nje
teresi e recordeve dhe lidhmerite e te
dhenave jane te paraqitura permes te
“linkeve” te cilat mund te shihen si
“pointer”.Nje paraqitje e tille e te
dhenave te tabeles b nga rasti i kaluare
do te dukej keshtu
A101
Johnson 192-83- Alma Palo
7465
alto
Smith
019-283746
North
Rye
nbraha-bazat e te dhenave
A201
500
900
294
“modeli hierarchical”
Ky model eshte i ngjashem sikurse ai i
rrjetes me dallim te vetem ku organizimi i
te dhenave behet ne forme te rrenjes
d.m.th fillohet nga nje trung dhe
vazhdohet me tej me te ,si ne figuren e
paraqitur me poshte
RRENJA
Jonhson
A-101
Smith
Hayes
A-201
nbraha-bazat e te dhenave
295
“Differenca ne mes te
modeleve”
Modeli relacional dallon nga ai i
rrjetes dhe hirearkik ne ate pasi
nuk perdore “linke ” dhe
“pointera”,perderisa modeli
relational i lidhe te dhenat ne baze
te vlerave te tyre qe i permbajne.
nbraha-bazat e te dhenave
296
Transformimi i modeleve
relacionale ne SQL
Ne vazhdim do te shohim se si
nje model e ne veqanti modeli E-R
shprehet ne gjuhen e SQL-se.Para
se gjithash le te marrim nje model
logjike te bazes se te dhenave te
paraqitur si me pare (te modeli
fizike dhe logjike ).
nbraha-bazat e te dhenave
297
Paraqitja e nje modeli logjike
nbraha-bazat e te dhenave
298
Paraqitja e ER-se ne SQL
Nje paraqitje e tille e modelit logjike te
bazes relacionale do te kishte kete
paraqitje
Per entitetin e pare behet pershkrimi i
atributeve te tije qe e pershkojne e per
kete behet ndertimi I nje tabele
Create table studentat
(emrist,
Mbiemrist
Fotografiast
adresast);
Me pas ndertohet paraqitja e tabeles se
kurseve
nbraha-bazat e te dhenave
299
Paraqitja e ER-se ne SQL(1)
Pershkrimi I atributeve tjera per
entitetin e kurseve do te ishte disi
keshtu :
Create table kurset
(emrikursit
Kohakursit
Nivelikursit
Klasakursit
);
nbraha-bazat e te dhenave
300
Paraqitja e ER-se ne SQL
Ne paraqitjen e diagramit te mesem
eshte bere lidhmeria e te dhenave
nga ato dy entitete te shqyrtura e
te cilat do te jen te disa llojta edhe
do te ceken me vone.
nbraha-bazat e te dhenave
301
Modeli relacional i te dhenave
Per ilustrim te ketij modeli le te marrim dy
bashkesi te te dhenave (qe paraqesin
entitete)te shprehura me A={1,2,3} dhe
B={a,b,c,d}.Ku elementet e bashkesive
paraqesin atribute.Nje raport i tyre mund te
jete edhe produkti kartezian (matematike ) i
ketyre dy bashkesive:
AxB={
(1,a),(1,b),(1,c),(1,d),
(2,a),(2,b)(2,c)(2,d),
(3,a),(3,b),(3,c),(3,d)}
Qdo nenbashkesi e AxB paraqet nje relacion
matematike te atyre bashkesive .Ne baze te
kesaj modelet e marra ne kete menyre per
atributet e entiteve quhen modele
relacionale te bazave
te te dhenave
nbraha-bazat e te dhenave
302
Modeli relacional
Siq shihet nga larte modeli i tille
dallohet per nga paraqitja e formes
se tije ,sipas trajtes horizontale dhe
vertikale te te dhenave .Ku ne secilin
rresht paraqitet vetem nje atribut I
entitetit te pare ,ndersa ne secilen
shtylle vetem nje atribute I
entitetit te dyte .
nbraha-bazat e te dhenave
303
“relation model”
Praktikisht modeli I dhene mund te
paraqitet ne forme te tabelave te te
dhenave dhe lidhjet e tyre jane te
organizuara ne ate menyre qe te dhenat
jane te paraqitura ne kuader te nje
tabele permes te kolonave dhe
rreshtave ,nje paraqitje e tille do te
dukej keshtu
ATRIBUTET
Emri
Mbiemri gjinia id
mosha
emri1
mbiemri1 gjinia1 id1 mosha1
nbraha-bazat e te dhenave
304
“Database language”
Sistemet e bazave te te dhenave
pershkruhen me ane te dy tipeve
te gjuheve dhe njera per
specifikim te skemave te te
dhenave dhe tjetra per ndertim
dhe ndryshim te pyetsoreve,keto
ndryshe njihen me emrin
“Data definition language(DDL)”
“Data manipulation language(DML)”
nbraha-bazat e te dhenave
305
“data definition language(DDL)”
Me keta urdhera mundesohet definimi i
te dhenave themelore ne forme te
tabelave ,keto te dhena vendosen ne
fajlle te quajtura “data dictionary”,ku
keto te fundit permbajne “metadata”(te
dhena mbi te dhenat ne ate baze),nje
shembull i tille do te ishte ky ne vazhdim:
Create table studenti(
Emri char(30),
Mbiemri char(20),
Mosha integer)
nbraha-bazat e te dhenave
306
“Data manipulation
language(DML)”
Me ane te ketyre urdherave na mundesohet
qe ne kuader te te dhenave te krijuara me
pare te bejme Venjen e vlerave te reja
,Fshirjen e tyre ,Ndryshimin etj.
Na mundesohet edhe kerkimi i te dhenave dhe
plotesimi I kushteve te ndryshme qe arrihet
me ane te pyetsoreve.Dallojme dy tipe te
“DML” urdherave ,ato procedurale dhe jo
procedurale.Keto te parat kerkojne te dine
se cfare te dhena duhen dhe si te merren ato
,ndersa te dytat kerkojn te dine se cfare te
dhena te duhen pa e specifikue se si me I
marre ato.
nbraha-bazat e te dhenave
307
Disa te dhena te entiteteve
Ne vazhdim per ilustrim te
manipulimit dhe punes me urdherat
SQL marrim disa entitete dhe
atribute te tyre te dhena permes
tabelave te meposhtme .
nbraha-bazat e te dhenave
308
Tabela “account”
nbraha-bazat e te dhenave
309
Tabela “deposite”
nbraha-bazat e te dhenave
310
Tabela “loan”
nbraha-bazat e te dhenave
311
Tabela “borrower”
nbraha-bazat e te dhenave
312
Tabela “branch”
nbraha-bazat e te dhenave
313
Tabela “payment”
nbraha-bazat e te dhenave
314
DML-SQL
Nje shembull se si te merren te dhenat nga
baza eshte ky qe rekordi “customer-id”
kerkohet te jet ”192-83-7465”
select customer.customer-name
from customer
where customer.customer-id = ‘192-837465’
Sqarimet ne lidhje me te dhenat e mesiperme
te cilat jane marre :
Me ane te urdherit “select” behet zgjedhja e
te dhenave qe deshirojme me I pa me emer te
fushes dhe tabeles,me urdherin “from ”
tregohet vendi se ku ato duhet te shihen(pra
tabela),me urdherin “where ” ipet kushti qe
ato duhet te plotesojne.
nbraha-bazat e te dhenave
315
DML-SQL
Shembull tjeter i veprimeve me urdherat
SQL eshte dhene si me poshte :
Te gjindet “account balances”nga te gjithe
“ customer” me “customer-id=192-83-7465”
select account.balance
from depositor, account
where depositor.customer-id = ‘192-837465’
and
depositor.account-number =
account.account-number
Urdherat e ketij pyetsori jane te njejte me
ate te meparshmin ,dallimi qendron ne
faktin se ketu jane perdorur dy kushte te
kerkimit te te dhenave dhe ate permes te
operatorit “and”(dhe ) I cili do te
permendet me vone .
nbraha-bazat e te dhenave
316
Shfrytezuesit e bazave te
te dhenave
Shfrytezuesit jane me menyra te
ndryshme te shikimit te te dhenave p.sh
Aplikuesit e programit – I shohin te
dhenat permes urdherave DML
Shfrytezuesit e avancuare – punojne me
pyetsore e keshtu me radhe ,varesisht
nga niveli I njohurive kemi edhe menyra
te ndryshme te qasjes se te dhenave ne
nje baze te te dhenave
nbraha-bazat e te dhenave
317
Elementet themelore ne SQL
Struktura bazike
Operatoret e bashkesive
Funksionet Aggregate
Vlerat zerro
Nenpyetsoret (nested subquerys)
Relacionet e perfituara
Shikimet (Views)
Modifikimi I bazes
Relacionet e lidhjes (Joined Relations)
Data Definition Language
Embedded SQL, ODBC and JDBC
nbraha-bazat e te dhenave
318
Struktura baze
Le te marrim nje shembull te te
dhenave per nje baze relacionale me
ane te tabelave te meposhtme dhe
shqyrtojme lidhjen e te dhenave me
relacionet algjebrike (matematike).
nbraha-bazat e te dhenave
319
Skema e marre ne shembull
nbraha-bazat e te dhenave
320
Structura baze
SQL eshte e bazuare ne bashkesi dhe relacione operacionale me modifikime dhe
trashegime
Skema e pergjithshme do te kishte nje
pamje te ketille sipas shembullit te
dhene.Nje shembull I tille do te kishte kete
forme:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Ai paraqesin atributet
ri paraqesin relacionet
P eshte predikate
Ky pyetsore eshte ekuivalent me kete shprehje
relacionale algjebrike.
A1, A2, ..., An(P (r1 x r2 x ... x rm))
Rezultati i nje pyetsori eshte nje relacione.
nbraha-bazat e te dhenave
321
Urdheri “select”
Urdheri select paraqet atributet e nje (apo
disa ) entiteti ne kuader te nje pyetsori
P.sh te gjinden emrat e te gjitha fillialave
(branches) ne tabelen huaja (loan)
select branch-name
from loan
Kjo do te kishte nje paraqitje ne algjebren
relacionale si vijon:
branch-name(loan)
shenim: SQL nuk e dallon simbolin ‘-’ ne
mes te karaktereve
p,.sh ne vend te branch_name merret branchname
Ne vijim do te marrim ‘-’ .
Shenim : ne SQL shenimi I komandave nuk
ka rrole nga forma e shenimit te shkronjave
te vogla apo te medha por I te dhenave po.
nbraha-bazat e te dhenave
322
Urdheri “select” (vazh.)
SQL lejon vlerat e shumefishta ne kuader te
nje pyetsori.
Per te bere eliminimin e tille urdheri
“select”perdoret ne kombinim me urdherin
distinct dhe ate pas urdherit select.
Te gjinden emrat ne te gjitha filliallat e huase
(loan) dhe eliminoj te shumefishtat
select distinct branch-name
from loan
Ndersa per shikim te te dhenave te
teresishme pa perjashtim edhe atyre te
shumefishta perdoret urdheri “select” ne
kombinim me all .
select all branch-name
from loan
nbraha-bazat e te dhenave
323
Urdheri “ select” (vazhd.)
Zgjedhja e pernjehershme e te gjitha
atributeve ne nje tabele behet me
perdorim te urdherit select si ne vijim
select *from loan
Urdheri select mund te permban ne
vete operator aritmetike, +, –, , dhe /.
P.sh nese merret pyetsori :
select loan-number,branch-name,
amount  100
from loan
Ateher ky do te na jep rezultatin I cili
eshte I njejte me ate te huase (loan )
por te shumezuare me 100
nbraha-bazat e te dhenave
324
Urdheri “ where “
Urdheri where na specifikon kushtet te
cilat duhet ti plotesojne atributet e
entiteteve te cilat shqyrtohen ne ate raste
Funksion ne lidhmeri me urdherin select.
P.sh me gjete te gjithe numrat e huase nga
huaja (loan) dhe filiala Perryridge me numer
te huase me te madhe se sa $1200.
select loan-number
from loan
where branch-name = ‘Perryridge’ and
amount > 1200
Te dhenat ne kuader te nje pyetsori mund te
kerkohen edhe me ane te operatoreve logjike
and, or dhe not.
Ne te dhena mund te aplikohen edhe
operatoret aritmetike.
nbraha-bazat e te dhenave
325
Pershkrimi i rrolit te
pyetsoreve
Pyetsoret formohen me ane te ketyre
kritereve te pershkruara me poshte:
Emrin e tabeles (table)
Radhitjen e informates (sortim)
Kushtezimin e marrjes ne konsiderim
te informates
Kriterin e zgjedhjes sipas operatorit
logjike ,aritmetike,te bashkesive.
Edhe ndonje elemente tjeter shtese
nbraha-bazat e te dhenave
326
Rolet e operatoreve ne
organizimin e pyetsoreve
Ne funksion te pyetsoreve dallojme disa
tipe te operatoreve te cilet perdoren
per ndertim te pyetsoreve :
1.Operatoret logjike :“and”(operatori dhe),
“or”(ose),”eqv”(ekuivalent),”imp”(implikacion
),”xor”(ekslusiv ose),”not”(jo).
2.Operatoret artimetike:+(mbl.),(zbr.),*(shum.),/(pjes.),\(pjes.i
plote),^(fuqi),mod(modulo).
3.Operatoret krahasues(racional):=(bara.)
<(vog.),>(madh.),<>(!=)(ndrysh.),<=(!<)(vog.ose
bara.),>=(!>)(madhe ose baraz.).
nbraha-bazat e te dhenave
327
Operatoret
4.Operatoret tekstual(string):
&(operatori i bashkimit),”like”(operatori
sikurse),”not like”(operatori i cili
perjashton vlerat sikurse ato te
zgjedhurat).
5.Operatoret e perzier jane:
“between”(ndermjet),”in”(ne),”is”(nga).
Ne vijim shohim format e paraqitjes se
operatoreve te tille veq e veq.
nbraha-bazat e te dhenave
328
Perdorimi i operatoreve
logjike
Perdorimi i operatorit logjike “and” ne
pyetsore (ne trajten konstruktive )
realizohet sipas funksionit te tij qe
paraqitet me tabelen vijuese
p
q
pq
E vertet
E vertet
E vertet
E vertet
E pavertet
E pavertet
E pavertet
E vertet
E pavertet
E pavertet
E pavertet
E pavertet
nbraha-bazat e te dhenave
329
Perdorimi i operatoreve
logjike(v-1)
Rroli i operatorit logjike “or ”
realizohet sipas vetive te tij te
paraqitur ne trajten e meposhtme
p
q
pq
T
T
T
T

T

T
T
nbraha-bazat e te dhenave
330
Vecorite e operatoreve
logjike
Operatoret logjike funksionojne per
veqim ,zgjedhje , perjashtim te
informatave etj
Operatori “and” funksionon ne ato
raste kur te dy (apo me teper ) te
dhenat te cilat shqyrtohen
plotesohen.
Operatori “or” funksionon ne ato raste
kur te pakten njera nga te dhenat e
marra ne shqyrtim plotesohet.
nbraha-bazat e te dhenave
331
Forma paraqitese e
operatoreve logjike
Operatoret logjike ,perveq operatorit
“not”, jane operator binar(dyshe) ,ndersa
“not” eshte unar(njesh). forma paraqitese
e tyre eshte e njejte ,ndersa e operatorit
“not” dallon.Kete do ta japim me posht.
Operatori “and”-paraqitet si me poshte
[shprehja1] and [shprehja2]
Ngjashem paraqitet “or”.
Operatori “not” ka kete forme te
paraqitjes :p.sh
Not [shprehja e dhene ]<188
nbraha-bazat e te dhenave
332
Forma paraqitese e
operatoreve aritmetike
Operatori “+” ka kete trajte
[shprehja1] + [shprehja2]
Ne menyre analoge behet edhe
paraqitja e operatoreve te tjere p.sh
[shprehja1] - ([shprehja2] *
[shprehja3])
Me cka se pari eshte kryer shumzimi i
shprehjes2 me ate 3 dhe me pas jane
zbritur nga shprehja1
nbraha-bazat e te dhenave
333
Vecorite e operatoreve
Operatoret aritmetike ne radhe te
pare sherbejne per llogaritje te te
dhenave (nese tipi i te dhenave na
lejon nje gje te tille)
Sipas tipeve ata punojne si ne
modelin matematike dhe ate si vijon
: operatorin + (-) realizon
mbledhjen(zbritjen ) e te dhenave
,operatori <(>) sherben per kufizim
te informates nga lart (posht),<=(>=)
sherben per kufizim te informates nga
posht (lart) duke e marre parasysh
edhe vete ate informate
nbraha-bazat e te dhenave
334
Vecorite e operatoreve (v-1)
Operatori <> ka funksionin e
kerkimit te informates te
ndryshme nga ajo ne shqyrtim ,
“between” na mundeson kerkimin
e informatave ndermjet vlerave
te cekura ,”like” na mundeson
kerkimin e informatave te
ngjashme me ate te shqyrtuar .
nbraha-bazat e te dhenave
335
Forma paraqitese e
operatoreve krahasues
Operatori “=” ka kete paraqitje te
meposhtme:
[shprehja] =“dicka”.
Ngjashem paraqiten edhe operatoret e
tjere te krahasimit e nje shembull i
tille eshte edhe ky:
[shprehja1] !< “e dhena”.
nbraha-bazat e te dhenave
336
Forma paraqitese e
operatoreve tekstual
Nje trajte e paraqitjes se operatorit “&” do
te ishte
[emri] & [mbiemri] , si rezultat do te kishim :
Emrimbiemri.
Ndersa forme tjeter e ndarjes se te dhenave
eshte kur operatori perdoret ne kete menyre:
[numri i shtepise] & “” &[emri i rruges]
Nese numri do te ishte 6 dhe emri i rruges
A.Jashari ,ateher do te kishim rezultatin
6 A.Jashari,d.m.th me zbraztesi ne mes te
tyre.
Operatori “like”-perdoret ne formen e
meposhtme
[shprehja] Like “A[bc]”
Operatori tjeter perdoret njejte.
nbraha-bazat e te dhenave
337
Forma paraqitese e
operatoreve te perzier
Paraqitja e operatorit “between” eshte
si me poshte
[shprehja] between A and B
Operatori “in” ka paraqitje te
meposhtme
[shprehja] in (vlera1,vlera2)
[shprehja] is Null
nbraha-bazat e te dhenave
338
Perdorimi i “wildcards”-ve
nbraha-bazat e te dhenave
339
Urdheri where (vazh.)
Ne vazhdim shohim nje shembull te
perdorimit te operatorit te krahasimit
between me urdherin “where”
P.sh te gjindet numri I huase nga
huaja me vlere ne mes te $90,000 dhe
$100,000 (kjo eshte , $90,000 dhe
$100,000)
Nje gje e tille ipet permes pyetsorit te
meposhtem
select loan-number
from loan
where amount between 90000 and 100000
nbraha-bazat e te dhenave
340
Urdheri “from”
Urdheri from ben radhitjen e relacioneve
te perfshira ne nje pyetsore
Dhe I korespondon produktit kartezian
P.sh te gjindet produkti kartezian ne mes
te tabeles borrower dhe loan
select 
from borrower, loan
 Shembull: te gjindet emri ,numri I huase dhe sasia e huase
per te gjithe klientet te cilet kane hua ne filiallen Perryridge .
select customer-name, borrower.loan-number, amount
from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = ‘Perryridge’
nbraha-bazat e te dhenave
341
Riemrimi i operacionit
Permes urdherave te SQL lejohet
riemrimi I relacioneve dhe atributeve
permes urdherit as :
emri I vjeter as emri I ri
Te gjindet emri ,numri I huase dhe
sasia e huase e te gjithe klienteve; te
riemrohet emri I kolones loan-
number(numri I huase ) si loan-id.
select customer-name, borrower.loan-number as
loan-id, amount
from borrower, loan
where borrower.loan-number = loan.loan-number
nbraha-bazat e te dhenave
342
Qiftet e variablave (nga ketu
permireso)
Keto tipe te variablave jane te
definuara ne urdherin from permes
te urdherit as .
Gjeni emrat e klienetve dhe numrat e
tyre te huase per te gjithe kliente qe
kane hua ne ndonje filialle.
select customer-name, T.loan-number, S.amount
from borrower as T, loan as S
where T.loan-number = S.loan-number
gjeni emrat e te gjitha filiallave te cilat kane
gjendje(assets) me te mire se sa nje filialle ne
Brooklyn.

select distinct T.branch-name
from branch as T, branch as S
where T.assets > S.assets and S.branch-city = ‘Brooklyn’
nbraha-bazat e te dhenave
343
Operatoret e karaktereve
SQL lejon edhe perdorim te operatoreve te
karaktereve.keto pershkohen me ane te
ketyre dy operatoreve : Peqindja (%),E cila
zevendeson qdo nenvarge te karaktereve
underscore (_). Perdoret per zevendesim te nje
karakteri opa edhe te disa nese merret disa here.
Gjeni emrat e te gjithe klienteve qe permbajne
ne vete emrin e rruges “Main”.
Select customer-name
from customer
where customer-street like ‘%Main%’
SQL mbeshtet edhe operatore tjere siq jane
concatenation (bashkimi I stringjeve “||”)
kthimi nga shkronjat e medha ne te vogla dhe
anasjelltas gjetjen e gjatesise se stringut,
extraktimin e nenstringut etj.
nbraha-bazat e te dhenave
344
Radhitja e te dhenave
Te radhiten ne menyre alfabetike emrat e
klienteve (me emra te ndryshem) qe kane
hua ne filiallen Perryridge
select distinct customer-name
from
borrower, loan
where borrower.loan-number = loan.loan-
number
and
branch-name = ‘Perryridge’
order by customer-name
Ne mund te specifikojme desc (z-a) per
radhe nga e madhja kah e vogla dhe me
asc (a-z) per radhitje te kundert, per
secilin atribute; radha asc eshte e
nenkuptuare.
P.sh te radhiten emrat e klienteve me radhen desc
nbraha-bazat e te dhenave
345
Vlerat e shumefishta
Ne raste te perseritjes se te dhenave ,
SQL mund te definon se sa kopje jane ne
rezultate.
Format shumevepruese te operatoreve
algjebrike – jane dhene relacionet qe
veprojne ne shumebashkesi r1 dhe r2:
1.  (r1): nese jane c1 kopje te qiftit t1 ne
r1, dhe t1 e ploteson zgjedhjen ,, ateher
jane c1 kopje nga t1 ne  (r1).
2. A(r): per qdo kopje te qiftit t1 ne r1, ekziston
kopja e qiftit A(t1) ne A(r1) ateher A(t1)
shenon projeksionin e qiftit te vetem t1.
3. r1 x r2 : nese jane c1 kopje te qiftit t1 ne r1
dhe c2 kopje te qiftit t2 ne r2, ateher jane c1
x c2 kopje te qiftit t1. t2 ne r1 x r2
nbraha-bazat e te dhenave
346
Vlerat e shumefishta (vazh.)
P.sh : Supozojme nje relacion ne mes te
dy bashkesive r1 (A, B) dhe r2 (C) si me
poshte :
r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)}
Ateher B(r1) do te jet {(a), (a)}, ku
B(r1) x r2 do te jet
{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}
SQL semantika per vlera te
shumefishta eshte :
select A1,, A2, ..., An
from r1, r2, ..., rm
where P
ekuivalente me kete shprehje
relacionale algjebrike:
 A1,, A2, ..., An(P (r1 x r2 x ... x rm))
nbraha-bazat e te dhenave
347
Operatoret e bashkesive
Operacionet e bashkesive union, intersect
dhe except operojne ne relacione dhe I
pergjigjen operacioneve algjebrike si 
Secili nga operatoret e mesiperm
automatikisht eliminon vlerat e
shumefishta; per shikim te te gjitha te
dhenave edhe qe perseriten perdoren
operatoret union all, intersect all dhe
except all.
Supposojme se nje qifte perseritet m here
ne r dhe n here ne s, ateher , do te kishim :
m + n here ne r union all s
min(m,n) here ne r intersect all s
max(0, m – n) here ne r except all s
nbraha-bazat e te dhenave
348
Operatoret e bashkesive
(vazh.)
Gjeni te gjithe klientet te cilet kane
hua ,nje llogari apo edhe qe te dyja :
(select customer-name from depositor)
union
(select customer-name from borrower)

gjeni te gjithe klientet te cilet kane edhe hua dhe llogari
(select customer-name from depositor)
intersect
(select customer-name from borrower)

gjeni te gjithe klientet qe kane llogari por jo edhe hua
(select customer-name from depositor)
except
(select customer-name from
borrower)
nbraha-bazat e te dhenave
349
Funksionet Agregate
Keto funksione veprojne ne kuader te
nje bashkesie te vlerave ne kuader te
nje kolone dhe japim nje vlere te
caktuare
avg: vleren mesatare
min: vleren minimale
max: vleren maximale
sum: shumen e vlerave
count: numrin e vlerave
nbraha-bazat e te dhenave
350
Funksionet Agregate(vazh.)
Te gjindet mesatarja e ballances se
llogarive ne filiallen Perryridge .
select avg (balance)
from account
where branch-name = ‘Perryridge’

gjeni numrin e qifteve ne relacionin e klienteve
select count (*)
from customer

gjeni numrin e depozitoreve me emra te ndryshem ne banke
select count (distinct customer-name)
from depositor
nbraha-bazat e te dhenave
351
Funksionet Agregate– Group By
Gjeni numrin e depozitoreve per
secilen filialle.
Select branch-name, count (distinct customer-name)
from depositor, account
where depositor.account-number = account.accountnumber
group by branch-name
shenim: Attributet
ne urdherin select jashte
funksioneve agregate
duhet te hyne ne urdherin
group by
nbraha-bazat e te dhenave
352
Funksionet Agregate – Having
Clause
Gjeni emrat e te gjitha filiallave ne
te cilat e mesmja e llogarise se
ballancuare eshte me shume se
$1,200.
select branch-name, avg (balance)
from account
group by branch-name
having avg (balance) > 1200
shenim: predikatet ne urdherin having jane aplikuar pase formimit
te grupit perderisa predikatet ne urdherin where jane aplikuare para
formimit te grupit
nbraha-bazat e te dhenave
353
Vlerat
zerro
Ka mundesi qe nje atribute ne nje qifte
relacionale te mos kete vlere ,kjo njihet si
vlere zerro
null –shpreh nje vlere te panjohur apo edhe
vleren e cila nuk ekziston.
P.sh gjeni te gjithe numrat e huase te cilet
jane ne relacionin e huase me vlere zerro per
llogari.
select loan-number
from loan
where amount is null
Secili rezultat I ndonje veprimi aritmetike
me zerron na kthen vleren zerro
P.sh 5 + null kthene null
Funksionet agregate e injorojne vleren
zerro
nbraha-bazat e te dhenave
354
Vlerat zerro shprehjet logjike
me te
Cdo krahasim me zerro eshte vlere e
panjohur
P.sh 5 < null ose null <> null ose null = null
Tri vlerat logjike me zerro :
OR: (e panjohur or e vertet ) = e vertete , (e
panjohur or e pavertete ) = e panjohur
(e panjohur or e panjohur ) = e panjohur
AND: (e vertete and e panjohur ) =e panjohur ,
(e pavertete and e panjohur ) = e pavertete ,
(e panjohur and e panjohur ) = e panjohur
NOT: (not e panjohur ) = e njohur
“P eshte e panjohur ” eshte e vertete nese
prediakati P eshte I panjohur
Rezultati I urdherit where ne predikate
eshte I pavertete nese njihet si I panjohur
nbraha-bazat e te dhenave
355
Vlerat zerro dhe funksionet
agregate
Njehso gjithe sasine e huase
select sum (amount)
from loan
Urdherat e mesiperm I injorojne llogarite
zerro
Rezultati eshte zerro nese nuk ka llogari te
ndryshem nga zerro,kjo d.m.th
Te gjitha funksionet agregate perveq
count(*) I injorojne qiftete me vlere
zerro ne atributet ne funksionin
agregate.
nbraha-bazat e te dhenave
356
Nested Subqueries
Nje nenpyetsore eshte I marre
me urdherat select-from-where
si shprehje e permbajtur ne nje
pyetsore tjeter.
Nje pune me e shpeshte e
nenpyetsorit eshte te
performoje testimin e bashkesise
se antareve ,bashkesise se
krahasimeve dhe numrit kardinal
te tyre.
nbraha-bazat e te dhenave
357
Shembulli I pyetsorit
Gjeni te gjithe klientet qe kane
llogari dhe hua ne banke.
select distinct customer-name
from borrower
where customer-name in (select customer-name
from depositor)
 gjeni te gjithe klientet qe kane hua ne banke por nuk kane llogari
ne te
select distinct customer-name
from borrower
where customer-name not in (select customer-name
from depositor)
nbraha-bazat e te dhenave
358
Shembull pyetsori
Gjeni te gjithe klientet te cilet kane
llogari dhe hua ne filiallen Perryridge
select distinct customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = “Perryridge” and
(branch-name, customer-name) in
(select branch-name, customer-name
from depositor, account
where depositor.account-number =
account.account-number)

Note:ky pyetsore mund te rishkruhet edhe ne nje forme me te thjeshte.
nbraha-bazat e te dhenave
359
Krahasimet e bashkesive
Gjeni te gjitha filiallat te cilat kane
te ardhura(gjendje (assest)) me te
mire se sa nje filialle ne Brooklyn.
select distinct T.branch-name
from branch as T, branch as S
where T.assets > S.assets and
S.branch-city = ‘Brooklyn’
 I njejti pyetsore kur merret urdheri some
select branch-name
from branch
where assets > some
(select assets
from branch
where branch-city = ‘Brooklyn’)
nbraha-bazat e te dhenave
360
Perkufizimi I shprehjeve
Formula F <e ploteson nje kusht te dhene
> per ndonje (some )r t  r e tille qe
(F < e ploteson nje kushte te dhene > per
ate t)
ku shprehja <e ploteson nje kushte te
dhene > eshte njera nga : 
(5< per
0
ndonje
5
) =vertete
(5< per
ndonje
0
) = e pavertete
5
6
0
(5 =per
ndonje
5
) =e vertete (5  per
ndonje
0
5
) =e vertete (pasi 0 
5)
(= per ndonje )  ne
Dhe , ( per ndonje )  nuk eshte ne te
nbraha-bazat e te dhenave
361
Definimi i urdherave
Formula F <e ploteson kushtin >
all r t  r (F <e ploteson
kushtin> t)
0
(5< per te gjitha
5
) =e pavertete
6
(5< per te gjitha
6
) =e vertete
10
4
(5 = per te gjitha
(5  per te gjitja
5
4
) =e pavertete
) = e vertete (pa rastet 5  4 dh e 5  6)
6
( te gjitha )  nuk eshte ne
(= te gjitha )  ne
nbraha-bazat e te dhenave
362
shembull i dhene
Gjeni emrat e te gjitha filiallave te
cila kane gjendje (asset) me te mire
se sa tere ato ne Brooklyn.
select branch-name
from branch
where assets > all
(select assets
from branch
where branch-city = ‘Brooklyn’)
nbraha-bazat e te dhenave
363
Verifikimi I relacioneve te
zbrazeta
Kushti exists na kthene vleren e
sakte nese argumenti I
nenepyetsorit eshte jo I
zbrazet.
exists r  r  Ø
not exists r  r = Ø
nbraha-bazat e te dhenave
364
Shembull
Gjeni te gjithe klientet te cilet
kane llogari ne te gjitha filiallat
qe gjenden ne Brooklyn.
select distinct S.customer-name
from depositor as S
where not exists (
(select branch-name
from branch
where branch-city = ‘Brooklyn’)
except
(select R.branch-name
from depositor as T, account as R
where T.account-number = R.account-number and
S.customer-name = T.customer-name))

 shenojme se X – Y = Ø  X Y

nbraha-bazat e te dhenave
365
Testimi i vlerave te shumefishta
Kjo arrrihet permeste kushtit unique I
cili kthene vleren e sakte kur nuk kemi
vlera te shumefishta ,p.sh te gjenden
te gjithe klientet te cilet kane vetem
nje llogari ne filiallen perryridge.
select T.customer-name
from depositor as T
where unique (
select R.customer-name
from account, depositor as R
where T.customer-name =
R.customer-name and R.accountnumber = account.account-number and
account.branch-name = ‘Perryridge’)
nbraha-bazat e te dhenave
366
Shembull I nenpyetsorit
Ne kuader te nje nenpyetsori
kerkimi I vlerave te shumfishta
behet me ane te kushtit not unique
p.sh gjeni te gjithe klienete te cilet
kane se paku dy llogari ne filiallen
Perryridge.
select distinct T.customer-name
from depositor T
where not unique (
select R.customer-name
from account, depositor as R
where T.customer-name = R.customer-name
and
R.account-number = account.account-number
and
account.branch-name = ‘Perryridge’)
nbraha-bazat e te dhenave
367
Views(shikimet )
Shikimet na sherbejne per shikim
te te dhenave por jo edhe per
modifikim te tyre sikurse ne
nivelin logjike te tyre ,per krijim
te tyre perdoret urdheri :
create view emri I shikimit as <shprehja e pyetsorit >
Veqori themelore e tyre eshte se
behet ruajtja e pyetsoreve te
caktuare ne kuader te asaj baze
te te dhenave
nbraha-bazat e te dhenave
368
Shembull
Te krijohet nje shikim I cili ka ne
vete filiallat dhe kliente
create view all-customer as
(select branch-name, customer-name
from depositor, account
where depositor.account-number = account.accountnumber)
union
(select branch-name, customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number)
 gjeni te gjithe klientet nga filialla Perryridge
select customer-name
from all-customer
where branch-name = ‘Perryridge’
nbraha-bazat e te dhenave
369
Relacionet e perftuara
SQL lejon perdorim te shprehjeve ne
nenpyetsore dhe kjo behet te urdheri where
mirepo rezultati i fituare duhet te
riemrohet p.sh te gjindet mesatarja e
llogarive ne balance te atyre filiallave me
mesatare te tille me te madhe se sa $1200.
select branch-name, avg-balance
from (select branch-name, avg
(balance)
balance)
from account
group by branch-name)
as result (branch-name, avg-
where avg-balance > 1200
nbraha-bazat e te dhenave
370
Urdheri me (with)
Urdheri me(with ) ju lejon
shikimeve(views) te definohen lokalisht
ne nje pyetsore e jo ne forme globale.
Gjeni te gjitha llogarite me balance
maksimale
with max-balance(value) as
select max (balance)
from account
select account-number
from account, max-balance
where account.balance = max-
balance.value
nbraha-bazat e te dhenave
371
Shembull i nje pyetsori me te
nderlikuare
Gjeni te gjitha filiallat ku llogaria
totale e depositit eshte me e madhe
se sa mesatarja e llgarise deponuese
ne te gjitha filiallat
with branch-total (branch-name, value) as
select branch-name, sum (balance)
from account
group by branch-name
with branch-total-avg(value) as
select avg (value)
from branch-total
select branch-name
from branch-total, branch-total-avg
where branch-total.value >= branch-total-avg.value
nbraha-bazat e te dhenave
372
Modifikimi i bazes se te
dhenave
Me urdherin Delete mundesohet fshirja e te
dhenave te nje rekordi por jo edhe te tabeles
p.sh te fshihen te dhenat e huase nga
perryridge :
delete from account
where branch-name = ‘Perryridge’
Te fshihen te gjithe klientet ne secilen filialle
ne Needham city.
delete from account
where branch-name in (select branch-name
from branch
here branch-city = ‘Needham’)
delete from depositor
where account-number in
(select account-number
from branch, account
where branch-city = ‘Needham’
and branch.branch-name =
account.branch-name)
nbraha-bazat e te dhenave
373
Shembull
Te fshihen te gjitha rekordet ne te
gjitha llogarite me ballanca me te
vogel se sa mesatarja
delete from account
where balance < (select avg (balance)
from account)
nbraha-bazat e te dhenave
374
Modifikimi I bazes – Insertimi
(vendosja e te dhenave )
Te shtohet nje cifte ne account
insert into account
values (‘A-9732’, ‘Perryridge’,1200)
ose ne forme ekuivalente
insert into account (branch-name,
balance, account-number)
values (‘Perryridge’, 1200, ‘A-9732’)
Te shtohet nje cifte I te dhenave ne
account me balance zerro
insert into account
values (‘A-777’,‘Perryridge’, null)
nbraha-bazat e te dhenave
375
Modifikimi I bazes se te
dhenave Insertimi
Nese I japim te gjithe klienteve si
dhurate nga 200 $ ne filiallen
Perryridge . Nese numri I huase merret
sikurse numer I llogarise per llogarine
re
insert into account
select loan-number, branch-name, 200
from loan
where branch-name = ‘Perryridge’
insert into depositor
select customer-name, loan-
number
from loan, borrower
where branch-name = ‘Perryridge’
and loan.account-number
= borrower.account-number
nbraha-bazat e te dhenave
376
Modifikimi I bazes - Update
Me kete urdher mundesohet
ndryshimi I vlerave ne nje kolone te
tere .p.sh te rriten vlerat e te
gjitha llogarive me balance me te
madhe se sa $10,000 me 6%, te
gjitha llogarite tjera me 5%.
update account
set balance = balance  1.06
where balance > 10000
update account
set balance = balance  1.05
where balance  10000
Kjo u mundesua me dy update ne radhen
si me larte mund te behet edhe me
urdherin case si me poshte
nbraha-bazat e te dhenave
377
Urdheri Case per update te
kushtezuare
P.sh ne pyetsorin e mesiperm mund te
marrim
update account
set balance = case
when balance <= 10000
then balance *1.05
else balance * 1.06
end
nbraha-bazat e te dhenave
378
Urdheri Update ne shikime
Te krijohet nje view per te gjitha huate ne
relacionin e huase , duke mshefur atributin
e llogarise
create view branch-loan as
select branch-name, loan-
number
from loan
Shtoje nje rekord ne branch-loan
insert into branch-loan
values (‘Perryridge’, ‘L-307’)
ky shtim duhet te paraqitet me shtim te
ciftit
(‘L-307’, ‘Perryridge’, null)
ne relacionin e huase
Urdheri Updates ne shume shikime komplekse
eshte I veshtire dhe I pamundur te
pershtatet dhe per kete nuk lejohet.
nbraha-bazat e te dhenave
379
Transactions
Transakcioni eshte nje varge
I pyetsoreve dhe
urdherave update te ekzekutuara si nje teresi
Transakcionet fillojne ne menyre te shprehur
dhe t epercaktuara me njeren nga paraqitjet
commit work: ku kryen te gjitha update ne ate
transakcione te asaj baze
rollback work: I cbene te gjitha update te
kryera me ate transakcion
Shembull:Bartja e parave nga nje llogari ne
tjetren behet ne dy hapa:merr nga nje llogari
dhe I ven ne tjetren
Nese njeri hape kryhet por tjetri jo ateher
baza eshte ne nje gjendje jo te pershtatshme
Per kete duhet te plortesohen qe te dy keto
kushte ose asnjeri
Nese nje hape ne nje transakcion deshton
ateher ata kthehen me rollback work.
Rollback I nje transakcioni jo te suksesshem
behet automatikisht,
ne rastin e prishjes se380
nbraha-bazat e te dhenave
Transakcioni (vazh.)
Ne shume sisteme te baza te te dhenave,
nje urdheri I SQL ekzekutohet
suksesshem ne menyre automatike
Secili transakcion do te perbehetnga nje
urdher i vetem
Veprimi automatike zakonisht lihet anash
duke lejuare veprimin e transakcioneve te
tjera ne ate baze dhe kjo mvaret se cili
do te veproje ne ate sistem
Opcion tjeter ne SQL 1999: e permbyll
urdherin me begin atomic
…
end
nbraha-bazat e te dhenave
381
Relacionet e lidhjes
Relacionet e lidhjes marrim dy relacione
dhe si rezulta na kthejne nje relacion
tjeter.
Keto operacione merren si nenpyetsore ne
kuader te urdherit from
Kushtet e lidhjes – duhet te percaktohen
cilat cifte hyne ne lidhje dhe cfare
rezultati dote fitohet.
Tipet e lidhjes – duhet percaktuare ciftet
te cilat I pergjigjen apo jo cifteve tjera
ne ate lidhje varesisht nga zgjedhja e tipit
te lidhjes.
Kushtet e lidhjes
Tipet e lidhjes
Lidhja e brendshme
Lidhja e majte e jashtme
Lidhja e djathte e jashtme
Lidhja totale
Natyrale
ne <predicate>
Duke marre (A1, A2, ..., An)
nbraha-bazat e te dhenave
382
Relacionet e lidhjes – shembull
Relacioni loan
loan-number
branch-name
amount
L-170
Downtown
3000
L-230
Redwood
4000
L-260
Perryridge
1700
Relacioni borrower
customer-name
loan-number
Jones
L-170
Smith
L-230
Hayes
L-155
nbraha-bazat e te dhenave
383
shembull i lidhjes se brendshme
dhe te jashtme te majte
loan inner join borrower on
loan.loan-number =
borrower.loan-number
loan-number
branch-name
amount
customer-name
loan-number
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
loan left outer join borrower on
loan.loan-number = borrower.loan-number
loan-number
branch-name
amount
customer-name
loan-number
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
L-260
Perryridge
1700
null
nbraha-bazat e te dhenave
null
384
Pershkrimi i lidhjes inner
Nga shembulli i mesiperm
loan inner join borrower on
loan.loan-number = borrower.loan-number
shihet se jane marre te gjitha fushat e
anes se majte dhe ato te djathte per
te cilat ekzistojne fushat koresponduese
te atributeve dhe nuk jane zerro
fusha, ne te dy tabelat e dhena ,me
inicim nga tabela e majte dhe fushat
lidhese te marra ne ate lidhje paraqiten
dy here ne dalje
nbraha-bazat e te dhenave
385
Pershkrimi i lidhjes outer left
Ne shembullin e marre me larte
 loan left outer join borrower on
loan.loan-number = borrower.loannumber
Shihet se kjo lidhje realizohet me
ane te marrjes se te dhenave nga
ana e majte dhe elementet
koresponduese te asaj te djathte
edhe ne rastin kur nuk kemi vlera te
atributit ne te djathte(d.m.th kur
kemi vlera zerro) dhe gjate
paraqitjes behet edhe paraqitja e
vlerave(fushave ) te lidhjes dhe ate
dy here
nbraha-bazat e te dhenave
386
Pershkrimi i lidhjes outer te
djathte
Kjo eshte e ngjashme me ate te
realizimit te majte vetem tane
ne shikim merret tabela e pare si
tabele e djathte dhe vlerat
lidhese te atyre tabelave merren
edhe per atributet te cilat kane
vlerat zerro.
nbraha-bazat e te dhenave
387
Shembull I lidhjes
loan natural inner join
borrower
loan-number
branch-name
amount
customer-name
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
loan natural right outer join borrower
loan-number
branch-name
amount
customer-name
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
L-155
null
null
Hayes
nbraha-bazat e te dhenave
388
Pershkrimi i lidhjes natural
Lidhja natural eshte nje lidhje e
tipit te njejte sikurse inner
perveq te dallimit se gjate asaj
lidhje fushat lidhese ne mes te
dy tabelave nuk na paraqiten dy
here ne dalje te te dhenave qe
shihet edhe nga shembulli I marre
me larte
nbraha-bazat e te dhenave
389
Pershkrimi i lidhjes naturale
,outer
Edhe ketu vlene te ceket se lidhja
behet njesoj si te cilado lidhej e
jashtme per dallim se ketu fushat
lidhese te tabelave te cilat na
paraqiteshin dy here me heret tani
na paraqiten vetem nje here dhe e
tere paraqitja tjeter eshte e njejte
,e ate te marre te lidhjes se
jashtme
nbraha-bazat e te dhenave
390
Shembull I lidhjes
loan full outer join borrower
using (loan-number)
loan-number
branch-name
amount
customer-name
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
L-260
Perryridge
1700
null
L-155
null
null
Hayes
Gjeni te gjithe klientet kane nje llogari apo hua (por jo qe te dyja ) ne banke.
select customer-name
from (depositor natural full outer join borrower)
where account-number is null or loan-number is null
nbraha-bazat e te dhenave
391
Pershkrimi i lidhjes full
Kjo realizohet me marrje te te
gjitha fushave nga te dy tabelat
duke perfshire fushat me atribute
zerro dhe ato pa atribute zerro
si dhe paraqitjen e te dhenave ne
dalje vetem nje paraqitje te
fushes lidhese te tyre
nbraha-bazat e te dhenave
392
Data Definition Language
(DDL)
Skema e cdo relacioni
Domena e vlerave te shoqeruara per
secilin atribute.
Integrity I kushteve
Bashkesia e indekseve te ruhet per
secilin relacion.
Sigurimi dhe autorizimi per secilin
relacion.
Struktura fizike e te dhenave per
secilin relacion ne disk.
nbraha-bazat e te dhenave
393
Tipet e te dhenave ne SQL
char(n). Per karakter me gjatesi n.
varchar(n). Variable e karaktereve me
gjatesi n.
int. Integer (numer I plote ).
smallint. Integer I vogel.
numeric(p,d). Numer me decimale me p
shifra nga n te tere ne te djathet te pikes .
real, double precision. Floating point dhe
double.
float(n). Floating point number,me gjatesi
n shifrash .
Null lejohet ne tere tipet e te dhenave .
Deklarimi I atributi not null ndalon qe ajo
vlere te jet null per ate attribute.
create domain kushti I tille ne SQL-92
krijon per shfrytezuesin tipin e te dhenave
create domain person-name char(20) not null
nbraha-bazat e te dhenave
394
Date/Time tipet ne SQL (vazh.)
date. Datat me (4 shenja ) te vitit , muajin
dhe daten p.sh date ‘2001-7-27’
time.kohen e dites , ne ore, minuta dhe
seconda.p.sh. time ’09:00:30’
time
’09:00:30.75’
timestamp: date plus koha e dites
p.sh. timestamp ‘2001-7-27 09:00:30.75’
Interval: periode te kohes
p.sh. Intervali ‘1’ dite
Ndarja e vleres se date/time/timestamp na jep
nje interval
Vlerat e intervalev mund te shtohen ne vlerat e
date/time/timestamp
Mund te extraktohen vlerat e fushave
individuale nga date/time/timestamp
p.sh. extract (year from r.starttime)
Gjithashtu mund edhe te largohen vlerat nga
tipet e te dhenave date/time/timestamp
nbraha-bazat e te dhenave
p.sh. cast <string-valued-expression>
as date395
Krijimi I Tabelave
Nje relacion I realizohet me komanden
create table:
create table r (A1 D1, A2 D2, ..., An Dn,
integrity-constraint1),
...,
(integrity-constraintk))
r eshte emri I relacionit
Secila Ai eshte nje emer I atributit nga
relacioni r
Di eshte tipi I te dhenave per atributet Ai
P.sh :create table branch
(branch-name char(15) not null,
branch-city char(30),
assets
integer)
nbraha-bazat e te dhenave
396
Integriteti i kushteve ne
Create Table
not null
primary key (A1, ..., An)
check (P), ku P eshte
predicate
p.sh : te deklarohet branch-name si primary key per
branch dhe siguro qe vlerat e assets te jene jonegative.
create table branch
(branch-name char(15),
branch-citychar(30)
assets integer,
primary key (branch-name),
check (assets >= 0))
primary key ne nje deklarim automatikisht nenkuptohet jo zerro vlere
nbraha-bazat e te dhenave
397
Urdherat Drop dhe Alter Table
Urdheri drop table fshine nje tabele ne
teresi kjo d.m.th edhe gjithe relacionet me
te .
Urdheri alter table mundeson shtimin e
atributeve ne kuader te nje relacioni
ekzistues .
alter table r add A D
ku A eshte emri I attribute qe shtohet ne
relacionin r dhe D eshte domena e A.
krejt ciftet ne relacion merren si null per
vlerat e atributeve te reja .
alter table mund te meret edhe per fshirje
te attributeve ne nje relacion
alter table r drop A
ku A eshte emri i attributit ne relacionin r
nbraha-bazat e te dhenave
398
Baza te tjera relacionale
QBE(query by example-access)
Nje baze relacionale e cila do te
shqyrtohet eshte edhe MSAccess.
Ne vazhdimesi do te dallojme
elementet themelore ne lidhje me
pershkrimin e te dhenave permes
trajtes QBE .Kjo realizohet ne
panelin “disajn” te pyetsoreve te
pershkruare si me poshte .Kjo na
realizohet permes disa tipeve te
pyetsoreve .
nbraha-bazat e te dhenave
399
Pyetsoret e trajtes “crosstab
query wizard”
Ndertimi i ketyre pyetsoreve
realizohet me zgjedhjen e opcionit
“crosstab query wizard” ,edhe keta
sherbejne per veqim te te dhenave ,e
ne radhe te pare per krahasim te te
dhenave ,por vetem per ato te nje
tabele.Per ndertim te tyre duhet te
zgjedhim te dhenat te cilat
deshirojme ti kemi ne forme shtylle ,
ato qe deshirojme ti kemi ne forme
rreshti dhe ato te shumueshme
nbraha-bazat e te dhenave
400
Pyetsoret e tipit “Find
duplicates query wizard”
Keta formohen me ane te
zgjedhjes se opcionit “find
duplicates query wizard” dhe na
sherbejne per gjetjen e te
dhenave te njejta (te shumfishta)
te nje tabele apo edhe te nje
pyetsori .
nbraha-bazat e te dhenave
401
Pyetsoret e trajtes “find
unmatched query wizard”
Keta pyetsore ndertohen me
zgjedhjen e opcionit “find
unmatched query wizard”,na
sherben per gjetjen e te
dhenave nga nje tabele e cila
nuk eshte e lidhur me te tjerat
nbraha-bazat e te dhenave
402
Njehsimet me ane te
pyetsoreve
Dallojme njehsimet sipas te dhenave
horizontale dhe te dhenave vertikale .
Realizimi i njehsimit te te dhenave ne
trajten horizontale(nese te dhenat jane
per njehsim ) arrihet ne trajten
“disajn”(konstruktive) te pyetsorit dhe ate
duke marre operatoret aritmetike (ne ate
menyre qe ne mes te fushave te zgjedhura
dhe operatorit te kemi se paku nga nje
zbraztesi) ne mes te dhenave te dy
fushave te ndryshme(apo edhe me shume )
, te njehsuara ne nje fushe te trete dhe
ne opcionin “Field ” te formes “disajn” te
pyetsorit ku me pas shtypet “enter” me cka
emerohet fusha e re me “exp1”e cila me
vone mund te riemrohet.
nbraha-bazat e te dhenave
403
Paraqitja e hapesires
njehsuese
nbraha-bazat e te dhenave
404
Njehsimet me ane te
pyetsoreve(v-1)
Njehsimi ne forme vertikale te
dhenave (per ato te dhena per te
cilat lejohet njehsimi ) te tabeles
,realizohet me ane te pyetsoreve te
trajtes “simple query wizard”dhe
permes tipit “disajn”
Ne tipin “simple query wizard” te
dhenat e tabeles njehsohen sipas
ketyre mundesive
1. Shuma e teresishme e te
dhenave(sum)
nbraha-bazat e te dhenave
405
Njehsimet me ane te
pyetsoreve (v-2)
2.Njehsimi i vleres mesatare te
tyre (avg)
3. Njehsimi i vleres minimale te
dhenave te asaj kolone (min)
4. Njehsimi i vleres me te madhe
te te dhenave te asaj kolone
(max)
nbraha-bazat e te dhenave
406
Njehsimet me ane te
pyetsoreve(v-3)
Njehsimet sipas kolonave mund
te behen edhe ne paraqitjen
“disajn” te pyetsorit dhe ajo
paraqitje per dallim nga
njehsimi ne tipin “simple query
wizard ” na lejon nje mundesi me
te madhe te njehsimeve ne
elementet e shtylles dhe ate
sipas ketyre operacioneve :
nbraha-bazat e te dhenave
407
Njehsimet me ane te
pyetsoreve(v-4)
Group by(ne grupe)
Sum(shumen e te dhenave )
Avg(vleren mesatare)
Min(vleren minimale)
Max(vleren maksimale)
Count(numeron te dhenat pa fushat e
zbrazeta)
Stdev(derivimi standard)
Var(menyrat e paraqitjes se te
dhenes)
nbraha-bazat e te dhenave
408
Njehsimet me ane te
pyetsoreve (v-5)
First(vlera e te dhenes se pare ne
radhe)
Last(vlera e te dhenes se fundit
ne radhe)
Expression(sherben per ndertim
te fushes per njehsim)
Where(jepet kriteri per te
dhenat e shqyrtuara)
nbraha-bazat e te dhenave
409
Dallimet ne mes te
llogaritjeve ne pyetsore
Dallimet themelore ne mes menyrave
te llogaritjeve ne pyetsore per nga
menyra e njehsimit te te dhenave
sipas shtyllave dhe rreshtave
qendrojn ne faktin se njehsimet e te
dhenave sipas shtyllave jane te
kufizuara dhe ate vetem ne kater
llojet e permendura te njehsimeve
(sum,avg,min dhe max) per tipin e
pyetsorit “simple query wizard”
nbraha-bazat e te dhenave
410
Dallimet ne mes te
llogaritjeve ne pyetsore(v-1)
Ndersa ne formen “disajn ” me
zgjedhje te opcionit “total” ne menyne
“insert” shtohen mundesit e njehsimeve
te cekura me pare
Njehsimet e te dhenave sipas
rreshtave behen pa kufizime te tilla
dhe realizohen per gjitha mundesit te
cilat na lejojne operatoret aritmetike
te theksuare, d.m.th se njehsimet
sipas rreshtave jane njehsime me te
“mira” te te dhenave se sa ato sipas
shtyllave
nbraha-bazat e te dhenave
411
Llogaritjet me me shume
funksione
Keto llogaritje realizohen me ane te
“expresion builder”-it dhe kjo arrihet
kur ne fushen e caktuar te formes
konstruktive te pyetsorit zgjedhim me
tastin e djathet te miut opcionin
“built”.Ne kete rast kemi mundesi te
manipulimit me ane te
funksioneve,konstanteve,operatoreve
(logjike,aritmetike,krahasues) si dhe te
shprehjeve te paraqitura.Aktivizimi i
“expresion builder”-it realizohet per
tipet e te dhenat qe jane per njehsim ne
trajten “disajn” te pyetsorit dhe ka nje
paraqitje te meposhtme:
nbraha-bazat e te dhenave
412
“Expresion builder”-i
nbraha-bazat e te dhenave
413
Funksionet
Perveq operatoreve perdorim kane edhe
funksionet ,access-i ofron nje mori
funksionesh qe ne vijim do ti peshkojme
sipas kategorive si vijon:
Conversion(per ndryshim te formes se
dates,e ne te hyjne Str(kthen numrin si
string),Val(e kthen vetem vleren numerike
nga nje string p.sh nga [shprehja 1 ] kthen
1),Format(pershtat menyren e paraqitjes
p.sh nga 02\03\2002 ne 2-3-2002))
Date/time(punon me kohedhe date,ka keto
funksione:now,time,date,datediff)
Financial (SQL)(ofron vleren mesatare dhe
shumen e shprehjeve ne grupe)
nbraha-bazat e te dhenave
414
Funksionet (v-1)
Financial (monetary)(ofron funksionet
:NPV(serine e pageses dhe
sasine),DDB(cmimin
fillestar,cmimin,koheqendrushmerin))
Mathematical (int(pjesa e plote e numrit
reale),fix(pjesa e plote e numrit
negative ),sqr(rrenjezimi))
String manipulation(right(kthen numrin e
paracaktuar te karaktereve ne te
djathte),len(gjatesin e stringut
),lcase(kthen shprehjen ne shkronja te
vogla)) etj.
nbraha-bazat e te dhenave
415
Shprehjet (expression)
Pjeset e nje shprehje jane :operatoret(+,,etj),emrat e
objekteve([adresa]),funksionet(Date(),etj),
literal
values(ehene,1600,etj),constans(yes,no,etj)
.Nje shembull i ndertimit te nje shprehje
eshte dhene me poshte:
[data e fillimit te punes]=Date
(02.03.2002)+27
Shprehja e pare eshte nje rekord,e dyta
eshte nje operator(=),tjetra nje
funksion(Date()),tjetra nje operator(+) dhe
e fundit nje “literal”.
nbraha-bazat e te dhenave
416
Pyetsoret kushtezues
(parametrike)
Roli themelore i ketyre pyetsoreve
eshte qe permes venjes se kushteve
kushtezuese te marrim nje pasqyre
me fleksibile te te dhenave ne ate
(ato) tabela ,ndertimi i ketyre
pyetsoreve behet ne trajten
“disajn” dhe realizohet me venjen e
kushteve ne fushen “criteria” ne
trajten e shenimit te tekstit te
mberthyer ne kllapa te mesme , si
vijon [teksti] .Roli themelore i
ketyre pyetsoreve eshte qe te
kerkohen te dhenat ne ate tabele .
nbraha-bazat e te dhenave
417
Sortimi i te dhenave me ane
te pyetsoreve
Sortimi i te dhenave te tabeles
mund te behet edhe permes
pyetsoreve dhe kjo realizohet
permes zgjidhjes se fushes“sort”
ne paraqitjen “konstruktive ”te
pyetsorit dhe me ate rast kemi si
zakonisht dy mundesi te
sortimit ate nga A-Z dhe te
anasjelltin me te Z-A
nbraha-bazat e te dhenave
418
Modifikimi i te dhenave ne
pyetsore
Modifikimi i te dhenave ne
pyetsore mund te behet per te
gjithe te dhenat e fushave te
shqyrtuara apo edhe te vetem
nje fushe ,ndryshimi i te dhenave
te te gjitha fushave ne pyetsore
behet me zgjedhje te opcionit
“properties” nga ana e djathte e
miut e shtypur kudo ne nje
hapesire te zbrazet te pyetsorit
nbraha-bazat e te dhenave
419
Modifikimi i te dhenave ne
pyetsore(v-1)
Ndersa modifikimi i te dhenave
sipas nje fushe behet ne menyre
te njejte si me siper duke
zgjedhur “properties” nga ana e
djathte e mausit te vendosur
mbi fushen te cilen deshirojme
te ndryshojme
nbraha-bazat e te dhenave
420
Pyetsoret Aktive
Pyetsoret aktive na mundesojne
manipulimin e te dhenave ne
pyetsoret e krijuar si dhe ne tabela
.Kjo nenkupton se pyetsoret e tille
jane ne funksion pasi te jene krijuar
tabelat dhe pyetsoret e permendur
me pare dhe si te tille keta nuk mund
te krijohen ne trajten themelore te
tyre por vetem pasi te hapet nje
pyetsore tjeter ekzistues apo edhe
ndonje i ri ne trajten “disajn”
.Dallojme disa tipe te pyetsoreve
aktive
nbraha-bazat e te dhenave
421
Tipet e pyetsoreve aktive
Append query
Update query
Make table query
Delete query
nbraha-bazat e te dhenave
422
“Append query”
Qellimi themelore i krijimit te
ketyre pyetsoreve eshte bartja e
nje grumbulli te te dhenave nga nje
tabele ne nje tjeter .Krijimi i tyre
realizohet me zgjedhjen e trajtes
konstruktive (disajn) te pyetsorit
dhe me pas zgjedhjen e tabeles nga
e cila deshirojme te bejme bartjen
e te dhenave ,hapi i metejme eshte
zgjedhja e opcionit “query append ”
nga menyja “query” dhe ne dialogun e
paraqitur te zgjedhet baza ne te
cilen deshirojme te bartim
e te dhenaveperkatese
423
informatennbraha-bazat
dhe tabela
Disa veti te “Append query”
Realizimi i tille i te dhenave do
te realizohet vetem ne ato
tabela ku te dhenat jane te nje
natyre
Nese numri i fushave te
zhvendosura eshte me i madh se
ai ne tabelen e zhvendosur
ateher ato te cilat teprojne nuk
do te barten etj
nbraha-bazat e te dhenave
424
“Update queries”
Roli themelor eshte ndryshimi i te
dhenave nga nje apo edhe me shume
tabela .Realizohen ne trajten “disajn”
te pyetsoreve me opcionin
“query”dhe me pas “update query”.Per
kete se pari merret pyetsori ne
trajten “disajn” me pas zgjedhen
tabelat dhe te dhenat te cilat
deshirojme ti ndryshojme, me ate
rast ne fushen e re “update to”
plotesohen te dhenat qe ndrrohen
nbraha-bazat e te dhenave
425
Krijimi i tabelave permes
pyetsoreve (make table
query)
Permes ketyre pyetsoreve
mundesohet krijimi i tabeles me
te dhenat e tabelave e
pyetsoreve te asaj baze .Per
kete merret forma konstruktive
e pyetsorit dhe zgjedhim “querymake table query”,me pas
zgjedhet baza ku deshirojme te
vejme tabelen dhe emerohet
tabela .
nbraha-bazat e te dhenave
426
“Delete query”
Keta pyetsore na sherbejne per
fshirjen e grupeve te te dhenave
nga nje apo me shume tabela .Kjo
realizohet me zgjedhjen “disajn” te
pyetsorit dhe me pas zgjedhet
tabela(tabelat) te cilen deshirojme
me e shqyrtue me vendosje te te
dhenave dhe nga menyja “query”
zgjedhim “delete query” .Veqori e
tyre eshte se nuk kane fushen e
sortimit .
nbraha-bazat e te dhenave
427
Pyetsoret “autolookup “
Keta pyetsore krijohen me zgjedhjen e
opcionit “disajn query ” dhe sherbejne
vetem ne ate rast kur deshirojme te
veqojme te dhena nga dy tabela te lidhura
ne formen nje me shume , dhe ate ne
fushen “field ” te pyetsorit vehet fusha e
tabeles shume e cila e realizon lidhjen me
tabelen ame dhe me pas edhe fushat e
tabeles ame qe deshirojme me i pare.Me
plotesim te fushes nga tabela shume e cila
e realizon lidhjen plotesohen fushat tjera te
pyetsorit.(me tastin tab)
nbraha-bazat e te dhenave
428
Aktivizimi i pyetsoreve
Pyetsoret aktivizohen me ane te
komandes “run” , ne menyne query
te paraqitjes “disajn” te
pyetsorit .
nbraha-bazat e te dhenave
429
Integriteti i te dhenave dhe
sigurimi
nbraha-bazat e te dhenave
430
Varesite funksionale
nbraha-bazat e te dhenave
431
Format normalet
nbraha-bazat e te dhenave
432
Bazat e orientuara kah
objektet
nbraha-bazat e te dhenave
433
Bazat themelore ne mysql
Mysql eshte nje software i bazave
te te dhenave me qasje te lire
(“open source”) dhe mund te
merret ne faqen :www.mysql.com
Per kete softwari ofrohet per
kliente dhe serwer ,me cka edhe
ne secilin baze te te dhenave
behet qasja ne kete forme .
nbraha-bazat e te dhenave
434
Qasja ne mysql
Pas qasjes se bazes nga ana e klientit
ne serwer kalojme ne direktoriumin
aktive te mysql-se ,per shikim te
bazave te te dhenave ne ate serwer
marrim urdherin
Show databases ;
Ku siq shihet secili urdher ne mysql
eshte aktive kur te perfundoje me ;
Me pas fitohen te dhenat mbi ato baza
dhe per kalim ne nje baze te caktuare
atehet marrim urdherin “use
emrinebazes”
Dhe me pas po ne ate menyre shihen
edhe tabelat dhe ta dhenat tjera te cilat
ndoshen ne ate baze
. e te dhenave
nbraha-bazat
435
Krijimi i bazes se te dhenave
Pas kalimit ne dritaren aktive te
mysql-se ateher vazhdojme me
urdherin :”create database
emriisaj;”
Me pas ne kuader te saj edhe
krijimin e tabelave ne te dhe te
elemnteve te tjera .
nbraha-bazat e te dhenave
436
Krijimi i tabelave
Krijimi I tabeles behet me urdherat
e pershkruare si ne cdo gjuhe te
SQL-se .
“Create tab1 (fusha1 tipiitedhenave,
Fusha2 tipitedhenave,
Fusha3 tipiitedhenave);”
Kjo do te ishte nje trajte themelore
e krijimit te nje tabele .
nbraha-bazat e te dhenave
437