Dat

a
Thedi
scr
eteel
ementst
hatcanbest
oredorr
ecor
dedandpr
ocessedandt
hat
hav
ecl
earmeani
ng.

Dat
abase
Thecollecti
onofdatausuall
yref
erredtoasthedatabase.Adatabasesy stem i
s
basi
call
yj ustacomput er
izedrecord-
keepi
ngsystem i.e.i
tisar eposi
toryor
cont
ainerforacoll
ecti
onofcomput eri
zeddataf
il
es.Userscanperform av ar
iet
y
ofoperati
onsonsuchf i
l
es.

1.Addi
ngnew,
empt
yfi
l
est
othedat
abase.

2.I
nser
ti
ngdat
aint
oexi
sti
ngf
il
es.

3.Ret
ri
evi
ngdat
afr
om exi
sti
ngf
il
es.

4.Changi
ngdat
ainexi
sti
ngf
il
es.

5.Del
eti
ngdat
afr
om exi
sti
ngf
il
es.

6.Remov
ingexi
sti
ngf
rom t
hedat
abase.

Dat
abasesar
ewi
del
yusede.
g.

1.Banki
ng-Forcust
omeri
nfor
mat
ion,account
s and l
oans and banki
ng
tr
ansact
ion.

2.Uni
ver
sit
ies-
Forst
udenti
nfor
mat
ion,
cour
ser
egi
str
ati
onsandgr
ades.

3.Sal
es-Forcust
omer
,pr
oductandpur
chasei
nfor
mat
ion.

Pur
poseofDat
abaseSy
stem
I
nanyor gani
sationcol
lect
ionofdatamustbeerrorf
ree,i
ndi
vi
dualandsecur
e.
Fort
his,
thearrangementofdatabasemustber
eli
abl
eandsecure,
sothat

1.I
mpor
tanti
nfor
mat
ioncanr
etr
iev
eint
imeandeasi
l
y.

2.Thewor
kcanbedeci
dedont
heaccountofar
rangeddat
abase.

3.Theinf
ormat
ionofempl
oyees–name,address,desi
gnat
ionet
c.canget
easi
l
yandesti
mati
onsoft
herewor
kingef
fi
ciency.

1
 4.Fi
nanci
alst
atusofanyor
gani
sat
ion.

5.To securethedat
abase f
rom unaut
hor
ized used t
hatt
heycoul
d not
accessthem.

6.Tor
educet
hewor
kingt
imewi
tht
hedat
abase.

7.Toi
ncr
easet
heper
for
manceoft
heor
gani
sat
ion.

El
ement
sofDat
abase

Ther
ear
ethr
eemai
nel
ement
s–

1.Fi
eld–thi
selementi
susedt ocol
l
ecttheinf
ormat
ioni
nadef
ini
tesize
ar
ea.Forexampl
e– col
lect
ionofstudent
’snamei naNAMEfiel
dof
TABLE.

2.Recor
d– t
ocollectt
heinfor
mationinaf iel
d(s)iscal
l
edrecor
ds.For
exampl
e–col
lecti
onofst
udent’
si nf
ormat
ioninf i
eld–NAME,COURSE,
DATEOFBI
RTH,ADDRESS,andPHONE_ NO.

3.FI
LE–Gr oupofr
ecor
dsinfiel
d(s)i
scal
l
eddat
abasef
il
eandt
hisf
il
eis
st
oredi
nastor
agedev
iceofcomputer
.

Fi
elds

Name Address Cit

y Phone
Ram Ol
endgunj Jaunpur 222504
Mohan umarpur Jaunpur 395005

Recor
ds

DBMS
DBMSi sasetofpr ogr
amst hatfacil
i
tatest
heprocessofdefini
ngconst
ruct
ing,
accessi
ngandmani pul
atingdat abaseforvari
ousappl
icat
ions.I
notherwaywe
cansayt hati
tisacoll
ectionofdat abaseandthoseprogr
amsbywhi chwecan
accessthedatafr
om database.

2
 Sof
twar
etoaccessandmani
pul
ate
dat
abase

Dat
abase

Vi
ewsofdat
a
Adat abasesy
stem i
sacoll
ecti
onofint
errel
atedfi
lesandasetofpr ogramsthat
al
low userstoaccessandmodi fythesef i
les.A majorpurposeofdat abase
system ist
oprovi
deuser
swi hanabst
t ractviewofthedatai.
e.thehidescert
ain
detail
sofhowthedataar
estoredandmai nt
ained.

Dat
aAbst
ract
ion
Sincemanydat abasesystemsusersarenotcomputertrai
ned,dev
elopershi
de
thecompl exi
tyfrom userthr
oughsever
all
evel
sofabstracti
on,tosi
mplif
yuser’
s
i
nt er
act
ionswi t
ht hesyst
em hasthefol
l
owinglev
els.

1.Phy
sical
lev
el(
Int
ernal
lev
el)

2.Logi
cal
lev
el(
Concept
ual
lev
el)

3.Vi
ewl
evel
(Ext
ernal
lev
el)

Vi
ewlev
el
User1 User2User3 ………………………Usern

Mappi
ngsuppl
i
edbyDBMS

Logi
cal
lev
el

Mappi
ngsuppl
i
edbyDBMS/
OS

Phy
sical
lev
el

Atthephysi
callev
el,
accountoremployeerecor
dcanbedescribedasablockin
st
orage l
ocati
on.The language compli
erhides t
hese l
evel
s ofdetai
lf r
om

3
progr
ammer s.Atthelogicall
evel
,eachsuchr ecordisdescri
bedbyat
ype
defi
nit
ionandthei
nter
rel
ati
onshi
poftheser
ecor
dst ypedef
inedaswel
l
.

Final
l
y,thev iewlevel
,computeruser
sseeasetofappli
cati
onprogr
amsthathide
detai
lsoft hedatat y
pesthatisav i
ewercanseeonlythatpar
tofthedatabase
thathasi nformati
ononcust omeraccountsbuthecannotaccessinf
ormation
aboutsalariesofemployees.

Dat
aModel
Datamodelisacol l
ecti
onoft heconceptt hatcanbeusedtodefinethestruct
ure
(meandatatypes,relati
onships,constr
aintsforcontr
oll
ingdat
a)ofadat abase
aswellastoachieveabstracti
on( hi
dingthedetail
sofdatastor
age).Thev ari
ous
data model
s hav e been proposed with diff
erentconcept
s ofst ruct
ure and
abstr
acti
onfal
li
nt othethreediff
erentcategori
es.

1.Obj
ect
-baseddat
amodel
(concept
ual
orhi
ghl
evel
)

2.Recor
d-baseddat
amodel
(Repr
esent
ati
onorI
mpl
ement
ati
on)

3.Phy
sical
dat
amodel
(Lowl
evel
)

1.Objectbaseddatamodels–Thi smodelisusedtodealdataatl ogi

caland
vi
ew level
s.Thei
rspeci
alfeatur
esincl
udefl
exi
blestr
ucturecapabil
it
iesand
specif
icdat
aconstr
aint
s.Theycanalsodi
vi
dedint
ofoll
owingtypes:

1.Ent
it
y–Rel
ati
onshi
pModel
(E-
RModel
)

2.Obj
ectOr
ient
edModel

3.Semant
icModel

4.Funct
ional
Dat
aModel

2.Record–BasedLogi calModels–Thismodeli susedtodealdat aatlogical

andview level
s.I
nwhichdatabaseisstr
ucturedofseveralt
y pesofrecords
wit
hst andardfor
mat.Thei
rspeci
alf
eaturesincl
ude:overal
llogicalst
ruct
ure
ofdatabaseaswel lasexecut
iondetai
l
sathi gher–leveltheyal sodivi
ded
i
ntothreetypes-

1.Rel
ati
onal
Dat
aModel

2.Net
wor
kDat
aModel

3.Hi
erar
chi
cal
Dat
aModel

3.Phy
sicalDat
aModels–Thismodeli
susedt odealdat
aatt
helowestl
eveli
.e.
i
tprovidesconceptt
hatdescr
ibesthe detai
l
s ofhow dat
ai sstored i
n

4
 comput
er.Theycanbedi
vi
dedi
ntof
oll
owi
ngt
ypes-

1.Uni
fyi
ngModel

2.Fr
ameMemor
yModel
.

SchemasandI
nst
ances
Thecoll
ecti
onofi nf
ormati
onst oredi
nthedat abaseataparti
cul
armomentis
call
edaninstanceofthedatabase.Theover
alldesignofthedat
abasei
scal
led
thedat
abaseschema.Schemasar echangedinfrequent
ly.

Schemas

Name Address Cit

y Phone
Ram Ol
endgunj Jaunpur 222504
Mohan umarpur Jaunpur 395005

I
nst
ances

St
ruct
ureofDBMS
DBMScanbest
ruct
uredwi
tht
hef
oll
owi
ngcomponent
s.

1.Stor
ageManager–St oragemanagerisapr ogram modulethatpr
ovi
des
theint
erfacebetweent helow-l
eveldatastoredinthedatabaseandthe
appl
icati
onpr ogr
amsandquer i
essubmi t
tedt othesy st
em.Thust he
stor
agemanageri sresponsibl
eforstor
ing,
retri
evi
ngandupdat i
ngdat
ain
thedatabase.

Thest
oragemanagercomponent
sincl
ude:

 Authori
zati
on and int
egri
ty manager – Which test f
or the
sati
sfact
ionsofint
egri
tyconst
rai
ntsandcheckstheauthori
tyof
userstoaccessdat
a.

 Transact
ionManager–Whi chensuresthatthedatabaser emains
i
naconsi st
ent(corr
ect
)st at
edespi t
esy st
em f ai
l
uresandt hat
concurr
enttr
ansacti
onexecuti
onsproceedwithoutconfl
ict
ing.

 Fi
leManager– Whi chmanagest heall
ocat
ionofspaceondisk
st
orage and t
he data st
ruct
uresused t
orepresenti
nfor
mat
ion
st
oredondisk.

5
  Buff
erManager–Whi
chi
sresponsi
bleforf
etchi
ngdat
afrom di
sk
st
oragei
ntomainmemor
yanddecidingwhatdatacacheinmain
memory.

Thestor
agemanagerimpl
ement
ssev
eraldat
ast
ruct
uresaspar
toft
hephy
sical
syst
em i
mplement
ati
on

 Dat
afi
l
es.

 Dat
adi
cti
onar
y.

 I
ndi
ces.

2.TheQuer yProcessor( Dat

aManager )–Itiscentr
alsoftwarecomponents
ofDBMSwhi chisr esponsi
bleforint
erf
acingwiththesitesystem andto
maintai
ntheconsist encyi
ntegri
tyandsecuri
tyofdata.Datamanageralso
convert
st heoper ationsin user’
squeriesfrom user’slogicalvi
ew to
physi
calfi
lesystem.

 DDLinter
pret
er–Whi chi
nter
pretsDDLst
atement
sandr
ecor
dst
he
def
ini
ti
onsinthedat
adict
ionary
.

 DML Compil
er– Whi ch t
ransl
ates DML st
atementsin query
l
anguages i
nto an eval
uati
on plan consi
sti
ng of low-l
evel
i
nstr
ucti
onst
hatt
hequeryeval
uat
ionengi
neunderst
ands.

 Queryeval
uat
ionengi
ne– Whichexecut
esl
ow-
lev
eli
nst
ruct
ions
gener
atedbyt
heDMLcompil
ers.

6
TheEnt
it
y-Rel
ati
onshi
pModel

Anent i
tyisa“ t
hing”or‘object’thatisdistingui
shabl
efrom otherobject s,e.g.
each person is an entit
yand bankaccount sisanotherentity
.Ent it
ies ar e
descri
bedi nadat abasebyasetofat tr
ibutes,e.
g.account
_noandbal ancear e
att
ri
butesoft heaccountent i
tyset .Si
milarl
ycustomer_name,customer_ cityand
customer_stateareal soat t
ribut
esofacust omerentit
y.A relati
onshipi san
associ
ationamongsev eralentit
ies,e.
g.adeposi t
orrel
ati
onshipassoci atesa
customerwi theachaccountt hathehas.

Theover
alll
ogical
str
uct
ure(
schema)ofadat
abasecanbeexpr
essedgr
aphi
cal
l
y
byanE-Rdiagr
am.

7
Rel
ati
onalModel–Rel
ati
onal
model
hast
hef
oll
owi
ngf
eat
ures-

1.Al
lthedat
aisr
epr
esent
edi
nthef
orm oft
abl
e.

2.Rel
ati
onshi
pamongt
hedat
aisr
epr
esent
edbyt
hecol
umnv
alues.

3.I
tel
imi
nat
estheneedtochangeappl
i
cat
ionpr
ogr
amswhenachangei
s
modet
othedat
abase.

4.Userneednotknowt
heexactphy
sical
str
uct
urest
ouset
hedat
abase.

5.User
sar
epr
otect
edf
rom anychangesmodet
hesest
ruct
ures.

6.Eachat
tri
but
esofar
elat
ionhasdi
sti
nctname.

Net
wor
kModel
-Net
wor
kmodel
hast
hef
oll
owi
ngf
eat
ures-

1.Dat
aisr
epr
esent
edbyr
ecor
dsandl
i
nks.

2.Rel
ati
onshi
pamongt
hedat
aisr
epr
esent
edbyl
i
nksorpoi
nter
s.

3.Al
lther
ecor
dsar
eor
gani
sedi
nar
bit
rar
yfor
m.

4.Aseti
susedtorepr
esentadi
rect
edr el
ati
onshi
pbet
weentworecor
dsand
t
heserecor
dsar
eknownasownerr ecordsandmemberr
ecor
ds.

5.Thesethasonet
o many(
1:M)r
elat
ionshi
p bet
ween t
heownerand
memberr
ecor
d.

6.Thesetcanhav
emor
ethanonememberr
ecor
dbutonl
yoneowner
recor
d.

Hi
erar
chi
calModel–Hi
erar
chi
cal
model
hasf
oll
owi
ngf
eat
ure-

1.I
tisv
erysi
mil
art
onet
wor
kmodelexceptt
her
ecor
dsar
eor
gani
zedi
ntr
ee
f
orm.

2.Hi
erar
chyi
sanor
der
edt
reeandi
seasyt
ounder
stand.

3.Agai
ndat
aisr
epr
esent
edi
nthef
orm ofr
ecor
ds.

4.Si
mil
arl
yrel
ati
onshi
pamongt
hedat
aisr
epr
esent
edbyr
ecor
dsorl
i
nks.

5.At
reemaybedef
inedasasetofnodes.

6.Attherootofthetr
eei
sthesi
ngl
epar
ent
;thepar
entcanhav
enone,one
ormorechil
dren.

8
Ther
efor
e,E-
Rmodel
usesf
oll
owi
ngt
hreebasi
cthi
ngs-

1.Ent
it
y/Ent
it
yset

2.At
tri
but
es

3.Rel
ati
onshi
p

Ty
pesofat
tri
but
es

1.Si
ngl
eatt
ributes-Anat
tri
but
eswhi
chcannotbef
urt
herusedi
scal
l
ed
si
ngl
eat
tri
butes.

2.Composi
te-Anat
tr
ibut
ewhi
chi
sdi
vi
dedi
ntoi
tssubat
tri
but
es.

3.Singl
evalueatt
ri
but
e-Anattr
ibut
ehasonl
yonev
aluef
oranent
it
yis
call
edsi
nglev
alueat
tri
but
e.

4.Mul
tiv
alueat
tri
but
e-Anat
tri
but
ehasmor
ethanonev
aluef
oranent
it
y.

5.Nullat
tri
bute–Anat t
ri
but
ehasnotanyv
alueorv
aluei
smi
spl
acedor
doubtf
ulforanent
it
y.

6.Deri
vedat
tri
butes–Thev al
ueofat
tri
but
ecanbeder
ivedwi
tht
hehel
pof
theval
ueofotherat
tri
but
e.

Key
s

Keymeanst hespeci
alat
tri
but
esbywhichwecani
dent
if
yorr
elat
etheot
her
at
tri
but
es.Keyscanbecat
egori
zedasf
oll
ows-

1.Pr
imar
ykey

2.For
eignkey

3.Composi
tekey

4.Superkey

5.Candi
dat
ekey

1. Pr
imarykey– Anatt
ri
but
e( orcombinat
ionofat
tri
but
es)thatuni
quel
y
i
dent
if
ieseachr
owinarel
ati
on.I
tfol
l
owst het
woproper
ti
es.

9
 a.Uni
que

b.Notnul
l

I
nstudentt
abl
eev
eryst
udenthasauni
queRNt
hat
’swhyRNi
sconsi
der
edasa
pr
imarykey
.

2. Foreignkey–Anat t
ri
butesinar elat
ionofadat abaset hatser vesast he
primarykeyofanot herrelat
ionint hesamedat abase.I tisal socal l
ed
refer
encekey( foreignkey )becausei test
abli
shest her elati
onbet ween
thetables.Repet i
tionandnul lvaluebothareper missibleinf oreignkey .
RN at t
ri
but e(fi
eld)i spresentinbot hstudentandcl asst able.Her ein
studentt ableiti sconsideredaspr imarykeybuti ncl asst ableitis
consideredasaf orei
gnkey .Itshouldbenot edt hatthiscommonf iel
d
should be same dat atype and par amet
ers.Byf oreign keywe can
establi
sht herel
ationshipbetweent hesetwotables.

3. Composi
tekey–Apri
mer
ykeyt
hatconsi
stsofmor
ethanoneat
tri
but
eis
cal
l
edcomposit
ekey
.

4. Candidat
ekey-Ev er
yattr
ibut
esorev er
ycombi
nati
onofattr
ibut
esthat
uni
quelyident
if
iesarow i
nar el
ati
on.Amongal
lthecandi
datekeyswe
chooseanyoneasapr i
marykey.

Ty
pesofEnt
it
yset
s–Ther
ear
etwot
ypesofent
it
iesset
s

1.Str
ongenti
tyset–Anenti
tysethassuchfiel
d(at
tri
but
e)whichcanbe
consi
der
edasapri
marykeyt
henthi
senti
tyseti
scal
ledstr
ongenti
tyset
.

2.Weakenti
tyset–Anent
it
ysethasnotsuchfi
eld(
attr
ibut
e)whi
chcanbe
consi
der
edasapri
marykeyt
henthi
senti
tyseti
scall
edweakenti
tyset
.

Int
egrit
yconst r
aintsensurethatchangesmadet othedatabasebyauthor
ised
usersdonotr esultinalossofdat
aconsi st
ency
.Thusi
ntegr
it
yconst
rai
ntsguard
againstacci
dental damagetothedatabase.

Theyhav
etwobasi
crul
es-

1.Keydecl
arat
ions

2.For
m ofar
elat
ionshi
p.

1.Keydeclarat
ions:A keyal l
owsust oident
if
yasetofatt
ri
but
est hat
suf
fi
cetodi sti
nguishent
it
iesfr
om eachother.Key
sal
sohel
puniquely
i
denti
fyr
elati
onshipsfr
om eachot
her
.

10
2.For
m ofarel
ati
onshi
ps:Forabinar
yrel
ati
onshipsetRbet
weenent
it
y
set
sAandB,t
hemappingmustbeoneoft
hefoll
owing.

a.OnetoOne:Anent
it
yin‘
A’isassoci
atedwi
thatmostoneenti
tyi
n
‘
B’,
andanent
it
yin‘
B’i
sassoci
atedwit
hatmostoneenti
tyi
n‘A’
.

A1 B1
A2 B2
A3 B3
A4 B4

b.OnetoMany:Anent i
tyin‘
A’isassoci
atedwit
hanynumber(zero
ormore)ofent
it
yin‘
B’.Anenti
tyi
n‘B’,
however
,canbeassoci
ated
wit
hatmostoneenti
tyin‘
A’.

A1 B1
A2 B2
A3 B3
A4 B4

c.Manyt oOne:Anenti
tyin‘
A’isassoci
atedwit
hatmostoneent
it
y
i
n‘B’.Anent
it
yin‘B’
,however
,canbeassociat
edwit
hanynumber
(
zeroormore)ofent
it
iesi
n‘A’
.

A1 B1
A2 B2
A3 B3
A4 B4

d.ManytoMany:Anent i
tyin‘A’
; i
sassoci
atedwit
hanynumber(zer
o
ormore)ofent
it
iesi
n‘B’,andanent i
tyi
n‘B’i
sassoci
atedwi
thany
number(zer
oormore)ofentit
iesin‘
A’.

A1 B1
A2 B2
A3 B3 11
A4 B4
Referent
ialI
ntegri
ty:Often,wewi shtoensur
ethatav aluethatappear
si none
rel
ati
onforagi vensetofattri
butesal
soappear
sforacer tai
nsetofattr
ibut
esin
anotherr
elat
ion.Thiscondit
ioniscall
edr
efer
enti
ali
ntegri
ty.

Exampl
e:

a. Theattr
i est
but udent_nameinexam_
recor
disaf
orei
gnkeyr
efer
enci
ng
thepr
imarykeyofstudent_
recor
d.
b. Theat
tri est
but udent
_namei
nst
udent
_recor
disnotaf
orei
gnkey
.

I
tshoul
dbenot
edt
hatt
her
efer
ent
ial
int
egr
it
yconst
rai
ntsi
sweakent
it
yset
s.

Not
ati
on

Thebasicnotati
onissupert
ype/subty
per el
ationshi
p.Att
ributesthatar
eshar
ed
byallent
iti
esareassoci
atedwiththesupertype.Attr
ibut
est hatareuni
quetoa
par
ti
cularsubty
peareassociat
edwiththatsubtype.

Super
type

Super
type1 Super
type2 Super
type3

Att
ri
bute Att
ri
bute Att
ri
bute
uni
queto uni
queto uni
queto
subt
ype1 subt
ype2 subt
ype3

Gener
ali
zat
ion

Thegener
ali
zat
ioni
sthenumberofent
it
yset
shasmostcommonat
tri
but
es.

12
Gener
ali
zat
ioni
sthebot
tom-
uppr
ocess.

Speci
ali
zat
ion

Special
izati
oni
sthetop-downprocess.Eachsubt
ypeisformedbasedonsome
dist
inguishi
ngchar
acter
isti
csuchasat tri
but
eorr el
ati
onshi
psspecif
ict
othe
subtype.

Aggr
egat
ion

Thebestwayt omodelr elat

ionshipsamongrelati
onshipstouseaggr
egati
on.
Aggregat
ionisanabst r
acti
ont hroughwhichr
elati
onshi
psaretreat
edashi
gher-
l
evelenti
ti
es.Higherlevelenti
tycont ai
nssomeentit
iesandrel
ati
onamongthem
whicharewil
li
ngt orelatetoanyent i
tybysamerel
ation.

Exampl
e:

13
Customeri sassoci at
edwi thloanandforms,acust omer -
loanpairv i
aborr
ower
rel
ation.Eachcust omer -
loanpairmayhav eaempl oyeewhoi stheloan-
off
icer
forthatpar t
icularpair.Socust omerandl oanent it
iesar ewi l
lingtoform a
customer-empl oyeeandl oanemployeepairvialoan-offi
cerrelati
onrespect
ivel
y.
Whileonepai rcust omerl oanisassociat
edt oloanof f
icerrelat
ion,sowecan
aggregatetheirpairtorel
atetoloanoff
icerforremov i
ngt hecompl exit
y.

UNI
T-I
I

SetTheor
yConcept

Anenti
tyhasasetofpr operti
es,andt heval
uesforsomesetofpropert
iesmay
uni
quel
yidenti
fyanentity.Forexampl e,aper
sonhasapersonalproper
tywhose
val
ueuniquel
yidenti
fi
es.Anent i
tyseti sasetofenti
ti
esofthesamet ypethat
shar
ethesamepr opert
iesi.e.att
ri
butes.

Pr
imar
yKey Domai
n

SN NAME STATUS CITY

1 MANOJ 67 JAUNPUR
Tupl
es
2 RAHUL 58 VARANASI
Rel
ati
on 3 SURESH 43 KANPUR
4 SATI
SH 25 JAUNPUR
5 SATOSH 89 JABALPUR
6 ANSU 78 VARANASI
7 BRI
JESH 68 JAUNPUR

At
tri
but
es

Degr
ee

Foreachattr
ibut es,thereisasetofper
mitt
edvaluescal
ledthedomai
norv alue
setofthatatt
ribut e.e.g.
,thedomainofat
tri
but
eNamemi ghtbethesetoftext
str
ingsofacert ainlengthi.e.dat
aty
peint
,char
,etc.Rel
ati
onshi
psamongent it
y

14
set
sandat t
ri
buteshavi
ngmeani
ngf
ullr
elat
ioni
sknownasr
elat
ionalmodel
havi
ngt
hreepri
ncipal
–

1.Dat
ast
ruct
ure

2.Dat
aint
egr
it
y

3.Dat
amani
pul
ati
on.

Ent
it
yint
egr
it
y

Anentit
ysetmayhavesev
eralat
tri
but
esandeachent
it
ycanbedescr
ibedbya
setofat
tri
but
eanddat
aval
ue.

name

Fi
rstname mi
ddl
ename l
astname

Nor
mal
izat
ion

Theprocessofconvert
ingthecomplexdat
ast r
uct
ureint
osimpl
edat
ast
ruct
ure
i
scallednormali
zat
ion.Inthi
sprocess,r
elat
ionisdecomposedi
nsmal
l
erwell
def
inedstr
uctur
edrelat
ions.

Ex.
:

Un-
nor
mal
izedt
abl
e Nor
mal
izedt
abl
e

name Addr
ess Ci
ty Name Addr
ess Ci
ty
LIG Ram LIG I
ndor
e
Ram I
ndor
e
MIG
Ram MIG I
ndor
e
Palasi
Hari I
ndor
e Hari Palasia I
ndor
e
Saket
Hari Saket I
ndor
e

Nor
malFor
m

A nor malf ormisast ateofrelat

iont
hatresul
tsfr
om apply
ingsimpl
erules
regarding tor el
ati
onships bet
ween at
tri
but
es.These nor
malforms can be
categor i
zedint
ot hefol
l
owi ngways–

1.Fi
rstNor
mal
For
m

2.2ndNor
mal
For
m

3.3rdNor
mal
For
m

4.Boy
ce/
CoddNor
mal
For
m

5.4thNor
mal
For
m

15
 6.5thNor
mal
For
m

Di
ff
erentSt
agesofNor
mal
izat
ion

Un-
nor
mal
i
zedt
abl
ewi
thmul
tiv
aluedat
tri
but
es

Remov
emul
ti
-val
uedat
tri
but
es

Fi
rstNor
mal
For
m

Remov
ePar
ti
alDependenci
es

2ndNor
mal
For
m

Remov
eTr
ansi
ti
veDependenci
es

3rdNor
maFor
m

Removeremai
ninganomali
es
Resul
ti
ngfr
om f
uncti
onaldependenci
es

Boy
ce/
CoddNor
mal
For
m

Remov
emul
ti
-val
ueddependenci
es

4thNor
mal
For
m

Remov
eremai
ninganomal
i
es

16
 5thNor
mal
For
m

Fi
rstNor
malFor
m

RN SName SAdd SubCode SubName Facul

ty FAdd Grade
101 Ram LI
G CM-201 Networ
k S.K. MIG A
CM-202 DBMS M.K. HIG B
102 Har
i Pal
asi
a CM-201 Networ
k S.K. MIG B
CM-203 Maths N.K. LI
G A
Arelat
ionisi
nfi
rstnor
malf
ormi
fandonl
yif
,wheni
tcont
ainsnomul
ti
-val
ued
at
tri
butes.

RN SName SAdd SubCode SubName Facul

ty FAdd Grade
101 Ram LI
G CM-201 Networ
k S.K. MIG A
101 Ram LI
G CM-202 DBMS M.K. HIG B
102 Hari Pal
asi
a CM-201 Networ
k S.K. MIG B
102 Hari pal
asi
a CM-203 Marhs N.K. LI
G A

Par
ti
alFunct
ionDependency

Afunct
iondependency
,inwhichoneormor enonkeyatt
ribut
es(suchasName,
Addr
ess)ar
efuncti
onall
ydependentonpar
tbutnotal
loftheprimar
ykey.

2ndNor
malFor
m

Ar el
ati
onisi nsecondnormalfor
mifandonlyifwhent herel
ati
onisinfi
rst
normalform andhaseverynon-
keyat
tri
but
eful
l
yfunct
ionall
ydependentont
he
keyattr
ibut
e(pri
marykey)
.

St
udent Subj
ect
RN SName SAdd SubCode SubN Facul
ty FAdd
101 Ram LI
G CM-
201 Net
work S.
K. MIG
CM-
202 DBMS M.K. HI
G
102 Har
i Pal
asi
a CM-
203 Maths N.
K. LI
G

Gr
ade

RN SubCode Gr
ade
101 CM-
201 A
101 CM-
202 B

17
 102 CM-
201 B
HereRN ispri
mar ykeyforfi
eldsSName
andSAddandSubCodei spr
imarykeyfor 102 CM-
203 A
SubName,Facult
yandFAdd.ButGr adeis
funct
ional
l
ydependentonRNaswellasSubCode.

Tr
ansi
ti
veDependency

Af uncti
onaldependencybet
weent
wo(
ormor
e)non-
keyat
tri
but
es,i
scal
l
ed
t
ransiti
vedependency.

3rdNor
malFor
m

Ar el
ati
oni sinthir
dnor malf ormi fandonl yi fwhentherel
ationi sinsecond
normalf or
m andhasnot ransit
ivedependenci es.Int
heaboveexampl er
elati
on
studentandGr adebot hareal r
eadyi n3rd NormalForm butrelat
ionSubhasa
problem oftr
ansit
ivedependency .Becausef acult
yFAdddependsonFacul tyand
botharenon- keyattr
ibut
es.That ’
swhywehav etoconver
tthisrelati
oni o3rd
nt
Normal Form.

SubCode SubN Facul

ty Facul
ty FAdd
S.K. MIG
CM-
201 Net
work S.
K.
M.K. HIG
CM-
202 DBMS M.K.
N.K. LI
G
CM-
203 Maths N.
K.
Funct
ionalDependency

Aconstrai
ntbet
weent
woat
tri
but
esort
woset
sofat
tri
but
esi
scal
l
edf
unct
ional
dependency

RN Name Add Ci
ty

Her
eName,
Add,
Cit
yar
efunct
ional
l
ydependentonRNf
iel
d.

Det
ermi
nant

Theatt
ribut
eont
hel
efthandsi
deoft
hear
rowi
nfunct
iondependencyi
scal
l
ed
det
erminant

Candi
dat
eKey

Anat
tri
but
eorcombi
nat
ionofat
tri
but
es,t
hatuni
quel
yident
if
iesar
ow i
na

18
r
elat
ioni
scal
l
edcandi
dat
ekey
.

Boy
ce/
CoddNor
malFor
m(BCNF)

Boyce/Coddproposedastr angerdefi
nit
ionof3rd Nor
malForm whichi
sal so
call
ed3½ NormalForm.Ar elati
onisinBCNFi fandonlyi
fev
erydetermi
nantin
therel
ati
onisacandidat
ekey .BCNFisav er
yrarecase.

RN Subject
s Facul
ty Grade
101 Comput er Ram A
102 Maths Hari B
103 Accounts Sit
a C
104 El
ectronics Kamal B
105 Physics Git
a C

i
.e.
, RN,
Sub  Facul
ty,
Grade(
Exi
sti
ngcondi
ti
on)

Facul
ty  Subj
ect
s(possi
blecondi
ti
on)

Here,Facultyi
sadet er
minantbutnotcandi
dat
ekey
.Thereisnot r
ansi
ti
ve
r
d
dependency.That’
swhytherel
ati
oni
sin3 NFbutnotinBCNF.Ther
efor
ewe
areconvert
ingarel
ati
ont
oBCNF.

RN Subject
s Grade Facul
ty Subject
s
101 Comput er A Ram Comput er
102 Maths B Hari Maths
103 Accounts C Sit
a Accounts
104 El
ectronics B Kamal El
ectronics
105 Physics C Git
a Physics

Mul
ti
-val
uedDependenci
es

Whent her
eareatleastthr
eeattri
butesi
nar elat
ionR( l
ikeA,BandC)andf or
eachv alueofA,t
hereiswelldef
inedsetofval
uesofBandawel ldef
inedsetof
val
uesofC.Howev er,thesetofv al
uesofBi sindependentofsetC,andvice
versa.Thenthi
sphenomenoni scall
edmult
i-
valueddependenci
es.

A B C
B1 C1
A B2 C2
B3 C3

19
4thNor
malFor
m

Arel
ati
onisinfort
hnormalformi
fandonl
yifar
elat
ioni
sinBCNFandhasno
mult
i-
val
ueddependenci
es.Ex.
:

Sub TextBook Facul

ty
KORTH S.
K.
DBMS DATE R.
K.
DESAI N.
K.

Sub TextBook Sub Facul

ty
DBMS KORTH DBMS S.K.
DBMS DATE DBMS R.K.
DBMS DESAI DBMS N.K.
Supposethebookstableisconv
ert
edi
nt he1stNFt
ot hent
abl
ewi
l
lbel
i
kegi
ven
bel
owwi t
hlotofredundancy
.

Sub TextBook Facul

ty
DBMS KORTH S.
K.
DBMS DATE S.
K.
DBMS DESAI S.
K.

DBMS KORTH R.
K.
DBMS DATE R.
K.
DBMS DESAI R.
K.

DBMS KORTH N.
K.
DBMS DATE N.
K.
DBMS DESAI N.
K.

5thNor
malFor
m

Til
lnow wehav ef ocusedont hedecomposi t
ionofar elati
onschemewi t
h
undesir
ableproper
ti
esi nt
otworelat
ionschemes,Suchthatthedecompositi
onis
l
ossless.A joi
noft hesedecomposedr el
ati
onschemeswi l
lgivetheori
ginal
schemeand,hence,t hedata.Butsomet imesi tmaynotpossi bl
et ofi
nda
l
osslessdecompositionofarel
ati
onschemei ntotworelat
ionschemes.5thNFis
nothi
ng,iti
sabasi cconceptofJOINDependency( JD)i
.e.
,ar elat
ioncannotbe
decomposed.Ex.

SUB

SName Subj
ect Facul
ty
RAM COMPUTER R.K.

20
 RAM COMPUTER S.
K.
RAM MATHS N.
K.
HARI COMPUTER N.
K.
1.Abov
etabl
ei n4thNFbecausei
si thasnoMVD.

2.Butther
eis no way ofel
i
minat
ing t
hisr
edundancy wi
thoutl
osi
ng
i
nfor
mati
on.

3.Supposet
hatt
het
abl
eisdecomposedi
ntoi
tst
wopr
oject
ions.SUB1and
SUB2.

SUB1 SUB2

SName Subj
ect SName Facul
ty
RAM COMPUTER RAM R.K.
RAM MATHS RAM S.K.
HARI COMPUTER RAM N.K.
HARI N.K.
4.Her
ether
edundancyhasbeenel
i
minat
ed,
butt
hei
nfor
mat
ionabout–

a.Whi
chSUBi
sst
udi
edbywhom st
udent

b.Whi
chst
udent(
SName)i
staughtbywhom Facul
ty.

5.Thenat
uralj
oinoft
hesepr
oject
ionsshoul
dpr
ovi
det
heor
igi
nalt
abl
es
i
nfor
mati
onli
ke.

SUB3 SUB4

SName Subj
ect Facul
ty Subj
ect Facul
ty
RAM COMPUTER R.K. COMPUTER R.K.
RAM COMPUTER S.K. COMPUTER S.K.
RAM COMPUTER N.K.
* COMPUTER N.K.
RAM MATHS R.K.
* MATH N.K.
RAM MATHS S.K.
*
RAM MATHS N.K.
HARI COMPUTER N.K.
6.Thet
abl
eSUB3r
esul
ti
ngf
rom t
hisj
oini
sspur
ious(
Fal
se)
.

7.Si
ncet
hecr
osssi
gn(
*)r
owsoft
het
abl
econt
aini
ncor
recti
nfor
mat
ion.

8.Now supposet
hatt
heor
igi
nalt
abl
ewer
etobedecomposedi
ntot
hree
t
abl
es.

9.I
faj
ointi
stakenofal
lthr
eepr
oject
ions.

SUB5

21
 SName Subj
ect Facul
ty
RAM COMPUTER R.K.
RAM COMPUTER S.K.
RAM COMPUTER N.K.
*
RAM MATHS N.K.
HARI COMPUTER N.K.
Final
lySUB5t abl
eisal
sospuri
ous(f
alse)becauseithasonei
ncorrectr
ow.So
thatconclusi
onisthati
naboveexampl e,wecannotgetor i
ginali
nfor
mati
on
from r
ejoi
nprocessoft
hedi
ffer
entpr
ojecti
onsofrel
ati
on.

22
 T–3rd
UNI

Quer
yLanguage

Aquer ylanguageisal anguageinwhi chauserrequestsinf

ormati
onfr
om the
database.Theselanguagesareusuallyonalevelhi
gherthanthatofast
andard
programminglanguage.Querylanguagecanbecategori
zedas–

1.Pr
ocedur
alLanguage

2.Nonpr
ocedur
alLanguage

1.ProceduralLanguage–Int hepr
ocedurallanguaget
heuserinst
ruct
sthe
system t
oper for
m asequenceofoperati
onsont hedat
abasetocompute
thedesir
edr esul
t.

2.Non-ProceduralLanguage– Inthenon-pr
ocedur
allanguaget heuser
descr
ibest hedesir
edinf
ormat
ionwi
thoutgi
vingaspeci
fi
cpr ocedur
efor
obtai
ningthatinf
ormati
on.

Mostcommer ci
alrelat
ional
-database syst
ems of fera queryl anguage that
i
ncl
udesel
ement sofboththeproceduralandthenon- pr
ocedur
alapproaches.To
day
s,t
heverywidel
yusedquer yl
anguageSQL( Sequenti
alQueryLanguage).

Therelat
ionalalgebr
aisprocedur
al,wher
easthet
upl
erel
ati
onalcal
cul
usand
domainrelat
ionalcal
cul
usar
enonprocedur
al.

TheRel
ati
onalAl
gebr
a

Ther elati
onalalgebrai sapr oceduralqueryl anguage.Itconsist
sofasetof
operati
onst hattakeoneort wor elati
onsasi nputandpr oduceanewr el
ati
onas
thei
rr esult.Thef undament aloperati
onsi nt her el
ati
onalalgebr
aar eSelect
,
Project,Union,Setdiff
erence,Cartesi
anpr oductandRename.I naddi
ti
ontot he
fundament aloper at
ions,there ar e sev
eralot heroper ati
ons namely – Set
i
ntersection,Natur
al j
oin,Di
visi
on,andAssignment .

TheSelectOper
ati
on–Thesel ectoper
ati
onselect
stupl
est
hatsat
isf
yagi
ven
rel
ati
onofaspeci
fi
edcondi
ti
on.Table–Loan(Schema)
Loan_
no Branch_
name Amount
L-
11 VNS 900
L-
14 KNP 1500
L-
25 JNP 1500
L-
16 JNP 1300
L-
17 GKP 1000
L-
23 ALD 2000
L-
93 LKO 500

i
. Sy
ntax:

23
 SELECT(
col
1,col
2,……col
n)FROM t
abl
ename
WHEREsear
chcondit
ion;
Ex.
:
l
SELECT (oan_
no, br
anch_
name) FROM l
oan WHERE
l
oan_
no=‘
L-15’
;

i
i
. Synt
ax:
SELECT*FROM t abl
enameWHEREcol name
BETWEENv ol
1-colnameANDvol
2-col
name;
Ex.
:
SELECT*FROM l oan
WHEREbr anch_
name=“ JNP”ANDamount>1500;

Letr
elationRhaveattr
ibut
esXandYandanoperator(
“=”,
“>”
,“<”et
c.)bet
weenX
andY( ex.X>Y)ev
aluatest
oatr
uthval
ue(
trueorfal
se).

SELECT*FROM loan
WHEREbranch_
name=“JNP”^amount>1500;

Her
eAND(
^),
OR(
V)andNOT(
-)l
ogi
cal
oper
ator
.

ThePr
ojectOperat
ion–Retur
nsar el
ati
oncont
aini
ngall(
sub)t
uplest
hatr
emai
n
i
naspecifi
edrel
ati
onaft
erspeci
fi
edattr
ibut
eshavebeenremoved.

Supposewewanttoli
stal
ll
oannumber
sandtheamountoft
hel
oans,
butdonot
careaboutt
hebranchname.Theproj
ectoper
ati
onall
owsustoproducet
his
rel
ati
on.

PROJECTl
oan_
no,
amount

Loan_
no Amount
L-
11 900
L-
14 1500
L-
25 1500
L-
16 1300
L-
17 1000
L-
23 2000
L-
93 500

Letr
elati
onRhav eattr
ibut
esX, Y,..
..
.Zthent heprojecti
onofrelat
ionRonX, Y,..
..
.
Z.
R{X, Y,...
..
.Z}
i
sarelati
onindicat
es
Aheadingderivedfrom t heheadi ngofRbyr emov i
ngal latt
ri
but
esnot
ment
ionedintheset{ X,Y...
.Z}and
Abodyconsisti
ngofal ltuples{X:x,Y:y,
...
..
.Z:z}suchthatat upl
eappears
i
nRwi t
hXv aluex,Yv aluey ,.
..
..andZv aluez.

24
TheInter
sectOper
ati
on(
)–Tof
indal
lcust
omer
swhohav
ebot
hal
oanandan
accountatt
hebank.

Common
Recor
ds
I
nboth
Queri
es

INTERSECTrecords
(Sel
ectdi
stnctcl
i i
ent_
nofrom cli
ent _mast
er)
I
NTERSECT
(Sel
ectdi
stnctcl
i i
ent_
nofrom order _
no)
Tabl
ename: cl
i
ent_mast
er
cl
ient
_no Name Ci
ty
C001 Ashok Mumbai
C002 Ram Delhi
C003 Aj ay Mumbai
C004 Rohi t Calcut
ta
C005 Nal i
ni Delhi
C006 Pr em Mumbai
Tabl
ename: or
der_no
or
der_no Order _
dat e Cli
ent_no
O001 12-apr-97 C002
O002 25-dec- 97 C006
O003 03-dec97 C003
O004 18-jun-97 C002
O005 12-jan-97 C006

SELECTcl i
ent_
no, nameFROM cl ient _mast er
I
NTERSECT
SELECTcl i
ent_
mast er
.cli
ent_no,name
FROM cli
ent _mast er
,sales_or der
WHEREcl ient_mast er
.cli
ent_ no=sal es_order
.cl
i
ent_no;
Output:
Cli
ent _no Name
--
--
-----
--
--
--
---
-- -
----
---
--
---
----
-
C002 Ram
C006 Pr em
TheCar t
esianProductOper ati
on( X)–TheCar t
esi
anproductoperat
ion,denoted
byacr oss(
X),all
owsust ocombi nei nformat i
onf r
om anyt wor
elati
ons.Returns
arelat
ioncontainingal lpossiblet uplest hatar ecombinati
onoftwot uples,one
fr
om eachoft wospeci fiedrelati
ons.
product

25
 a a x
x a y
b
b x
c y b y

d c x
c y

TheRenameOperat
ion–
Amechanism f
orrenamingbot
hrel
ati
onsandat
tri
but
es.Taki
ngt
hef
oll
owi
ng
for
m–

Renameol
d_namet
onew_
name

TheUnionOper
ation(
U)–Retur
nsar el
ati
oncont
aini
ngal
ltupl
est
hatappeari
n
ei
therorbot
hoftwospeci
fi
edr
elat
ion.

Consideraquer
yt ofi
ndthenameofal lthecl
ientsandsal
esmanintheci
ty
‘
Mumbai ’f m cl
or i
ent
_masterand salesman_master
.Notethatthecust
omer
rel
ati
ondoesnotcont
aint
heinf
ormation.

cl
Name( i
ent
_mast
er sal
)UName( esman_
mast
er)

Tabl
ename: cl
i
ent_mast
er
Cl
ient_no Name Ci
ty
C001 Ashok Mumbai
C002 Ram Del
hi
C003 Ajay Mumbai
C004 Rohit Cal
cut
ta
C005 Nali
ni Del
hi
C006 Prem Mumbai

Tabl
ename: sal
esman_mast er
Salesman_
no Name Cit
y
S001 Mani sh Delhi
S002 Ki ran Mumbai
S003 Ni tesh Calcut
ta
S004 Mahesh Mumbai
S005 Rahul Lucknow
Thus,
Sel
ectNamefrom cl
ient _mast
er
UNION
Sel
ectNamefrom salesman_ mast
er

26
Sy
ntax:
SELECTsal
esman_ no“I
D”,
name
FROM sal
esman_master
WHEREcity=’Mumbai’

UNION
SELECTcli
ent_no“ID”,name
FROM client_
mast er
WHEREci t
y=’Mumbai’;
Out
put:
I
D Name
--
--
--
--
--
--
- --
--
---
--
--
--
--
--
-
C001 Ashok
C003 Ajay
C006 Prem
S002 Kiran
S004 Mahesh

TheSetDif
fer
enceOperat
ion–Thesetdi ff
erenceoperat
ion(-
)all
owsustofi
nd
tupl
esthatareinoner elat
ionbutarenoti n anot
her
,e.x.,wecan f
ind al
l
cust
omersofthebankwhohav eanaccountbutnotaloanatthebank.

Cust
omer
_name(
deposi
tor
)–Cust
oment
_name(
bor
rower
)

Recor
d
s
Onlyi
n
Query

MI
NUSr
ecor
ds
Tabl
ename: cl
i
ent_mast
er
Cl
ient_no Name Ci
ty
C001 Ashok Mumbai
C002 Ram Del
hi
C003 Ajay Mumbai

27
 C004 Rohi
t Cal
cut
ta
C005 Nali
ni Del
hi
C006 Prem Mumbai

Tabl
ename:order
_no
or
der_no Order_dat
e Cl
ient
_no
O001 12-apr-
97 C002
O002 25-dec-97 C006
O003 03-dec97 C003
O004 18-j
un-97 C002
O005 12-j
an-97 C006

Sy
ntax:
SELECTcli
ent
_no,name
FROM cl
ient
_master
MINUS
SELECTcli
ent
_no,cl
ient
_mast
er.
name
FROM order
_no;

Out
put:
Cl
ient_no Name
-
--
---
--
--
---
-- -
--
--
--
---
--
--
--
-
C001 Ashok
C004 Rohi
t
C005 Nali
ni

TheNatur
alJoi nOperati
on
Thenat
uraljoinisabinar
yoperat
iont
hatall
owsustocombi
necert
ainsel
ect
ions
andaCartesianproducti
ntooneoper
ati
on.Iti
sdenot
edbyt
he“Joi
n”symbol

a1 b1 b1 c1 a1 b1 c1

a2 b1 b2 c2 a2 b1 c1
=
a3 b2 b3 c3 a3 b2 c2

Ex.
:

Loan Bor
rower
Loan_
no Br
anch_
name amount
Cust
_Name Loan_
no
L-
170 JNP 3000 S.
K. L-
170
L-
230 VNS 4000 R.
K. L-
230
L-
260 KNP 1700 N.
K. L-
155

28
Nat
ural
joi
n
Loan Bor
rower

Loan_
no Br
anch_
name amount Cust
_name
L-
170 JNP 3000 S.
K.
L-
230 VNS 4000 R.
K.

TypesofJoinsandcondit
ions
Jointypes
1.Innerjoi
n(thet
a)
2.Leftouterj
oin
3.Rightouterjoi
n
4.Fullouterj
oin
Joincondit
ions

Nat
ural
Joi
non<pr
edi
cat
e>usi
ng(
A1,
A2,
..
..
..
..
..
..An)

Wher
eA1,
A2,
..
..
..
..
..
..Anar
eat
tri
but
es

1.I
nnerJoi
n

oani
l nnerjoinboroweron
r
loan.
loan_no=borr
ower
.l
oan_
no

Loan_
no Br
anch_
name amount Cust
_name Loan_
no
L-
170 JNP 3000 S.
K. L-
170
L-
230 VNS 4000 R.
K. L-
230

Theexpressioncomputesthet het
aj oi
noft hel oanandt hebor rower
rel
ati
onswiththej
oincondi
ti
onbeingloan.l
oan_no=borr
ower.loan_no.The
att
ri
butesoft he r
esul
tconsistoft heattri
butesoft hel eft
-hand-si
de
rel
ati
onfol
lowerbytheatt
ri
butesoftheri
ght-
hand-sider
elat
ion.

2.Lef
tout
erJoi
n

oanl
l eftout
erjoi
nbor oweron
r
loan.
loan_no=bor
rower
.l
oan_
no

Loan_
no Br
anch_
name amount Cust
_name Loan_
no
L-
170 JNP 3000 S.K. L-
170
L-
230 VNS 4000 R.K. L-
230
L-
260 KNP 1700 null nul
l

29
 Theexpr essioncomput est heleftout erj oinoperati
onlogicallyasf ollows.
First,comput et heresultoft heinnerj oinasbef ore.Then, forev erytupl ein
thel eft-
hand- siderelati
onl oant hatdoesnotmat chanyt uple(t)inther ight
-hand- sider elati
onofbor roweri nt hei nnerj oi
n,addat uple(r)tot her esult
oft hej oin.Theat tri
butesoft uple(r)t hatar ederi
v edfrom t hel eft
-hand-
sider elat i
onar ef i
ll
edinwi ththev al
uesf r
om tuple(t
),andt her emai ning
attri
but esoft uple(
r)arefil
ledwi thnul lv alues.

3.Ri
ghtOut
erJoi
n

oanr
l ightout
erjoi
nborroweron
loan.
loan_
no=borrower
.l
oan_
no

Loan_
no Br
anch_
name amount Cust
_name Loan_
no
L-
170 JNP 3000 S.
K. L-
170
L-
230 VNS 4000 R.
K. L-
230
nul
l Nul
l nul
l N.
K L-
155

4.Ful
lOut
erJoi
n

oanf
l ullout
erj
oinbor
rowerusi
ng(
loan_
no)

Loan_
no Br
anch_
name Amount Cust
_name
L-
170 JNP 3000 S.K.
L-
230 VNS 4000 R.K.
L-
260 KNP 1700 null
L-
155 Nul
l Null N.K

I
farel
ati
onfol
lowedwith“=”,
therel
ati
onjoi
niscal
l
edanequi
joi
n.
Ex.
:
loannatur
aljoi
nborroweron
l
oan.l
oan_no=borrower.
loan_
no
wherecit
y=’
JNP’

30
UNI T–IV
Fail
ureCl assi f
ication
Therear ev ari
oust ypesoff ail
uret hatmayoccuri nasy stem,eachofwhi ch
needst obedealwi thi ndi fferentmanner .Thesi mpl esttypeoff ai
luresisone
thatdoesnotr esul ti nt hel ossofi nf ormat i
oni nsy stem.Thef ail
urest hatare
mor ediffi
cul ttodealwi t
har et hoset hatr esultinl ossofi nformat i
on.Ther eare
foll
owingt ypesoff ailures–
1.Tr ansact ionf ail
ure– Ther ear et wot ypesofer rort hatmaycausea
tr
ansact i
onf ailure.
a.Logi caler ror– Thet ransact i
oncannol ongercont i
nuewi thi t
s
nor malexecut ionbecauseofsomei nternalcondi ti
on,suchasbad
input ,dat anotf ound, ov er f
low, orresour cel i
mitexceeded.
b.Sy stem er ror–Thesy st em hasent eredanundesi r
ablest at
e( ex.
Deadl ock)asar esultofwhi chat ransact ioncannotcont inuewi t
h
itsnor malexecut i
on.Thet r
ansact i
on,howev er,canber e-executed
atal at ert ime.
2.Sy st em Cr ash–Ther ei sahar dwar emal f
unct i
onorabugi nthedat abase
softwar eort heoper at i
ngsy st em.So,causest helossoft hecont entof
volatilestor age, andbr ingtransact i
onpr ocessi ngt oahal t
.Thecont entof
non- volatil
est orager emai nsi nt act,andi snotcor r
upted.
3.Di skFai l
ure–Adi skbl ockl ossesi t scont entasar esul tofeitherahead
crashorf ailur edur i
ngadat at ransferoper ation.Copi esoft hedat aon
otherdi skar eusedt or ecov erf r om fail
ures.

TheSt or
ageDev i
ceHierar
chy
Thev ari
ousstoragemediacanbeor gani
zedinahi erar
chyaccordi
ngt
othei
r
speedandt hei
rcost.Thehigherl
evel
sareexpensi
ve,butar
efast.Aswemove

31
down the hi
erar
chy
,the costperbi
tdecr
eases,wher
eas t
he access t
ime
i
ncreases.

CacheMemor
y

Mai
nMemor
y

Fl
ashMemor
y

Magnet
icDi
sk

Opt
ical
Disk

Magnet
icTape
TransactionModel
At r
ansact i
onisauni tofprogram executi
onthataccessesandpossi blyupdates
vari
ousdat aitems.Usually,atransacti
onisi
niti
atedbyauserpr ogram writt
enin
ahi gh-
leveldatamani pulati
onl anguageorprogrammi nglanguage( ex.SQLor
JAVA)wher eitisdel i
mitedbyst at
ements(orfunctioncell
)ofthef orm begin
tr
ansact i
on andendt ransaction.Thetransacti
onconsi stsofal loperati
ons
executedbet weenthebegi ntransacti
onandendt ransact
ion.Ther
ear efol
lowing
typesofpr opert
iesofthet r
ansaction.

LetTbeat r
ansact i
ont hattransfer
sRs.50f r
om accountAt oaccountB.This
tr
ansactioncanbedef inedas–
T: r ead(A);
A: =A–50;
wr i
te(B)
;
read(B);
B=B+50;
wr i
te(B)
;
Here,read(X)whi chtransfer
st hedatait
em Xf rom t
hedat abasetoalocalbuf
fer
oftransact
iont hatexecutesther eadoperati
on.And,writ
e(X)whichtransf
erst
he
i
tem Xf rom thelocalbuf f
eroft hetr
ansacti
onthatexecutedthewritebacktot
he
database.

1.At omici t
y–Ei theralloperati
onsoft het r
ansact ionarer eflectedproperl
y
i
nt hedat abaseornone.Supposet hat,
thev al
uesofaccount sAandBar e
1000and2000r espect
ivel
y.Now supposet hat,duri
ngt heexecut ionof
transact i
on– T,af ail
ureoccur st hatpr ev entsT f r
om compl et
ingits
execut ion successfull
y.Her ef ai
luresi ncludepowerf ailures,hardware
failur
esandsof twareerror.Fur t
her,supposet hatthef ai l
urehappened
afterthewr i
te(
A)oper ati
onbutbef orewr i
te(B)oper at
ion.I nt hi
scase,the
valuesofaccount sAandBr efl
ectedi nthedat abasear e950and2000.
Thesy stem destroyed50asar esultofthisf ail
ure.

32
 2.Consi stency–Ex ecut ionofat r ansactioni ni solat i
on( i.
e.wi thnoot her
transact i
on execut ing concur rent l
y)pr eser vet he consi stency oft he
database.
The consi st
ency r equirementher ei st hatt he sum ofA and B be
unchangedbyt heexecut ionoft het ransact ion.Wi t
houtt heconsistency
requirement ,moneycoul dbecr eat edordest roy edbyt hetransact i
on.
3.Isolati
on–Ev ent houghmul ti
plet r
ansact ionsmayexecut econcur r
ently
,
thesy stem guar ant eest hat,f orev erypai roft r
ansact ionsTiandTj,i t
appear st oTithatei therTjf i
nishedexecut i
onbef oreTist artedorTjstarted
execut ionaf t
erTif inished.Thus,eacht ransact i
oni sunawar eofot her
transact i
onsexecut ingconcur rent l
yint hesy stem.
4.Dur abili
ty–Af terat ransact ioncompl etessuccessf ull
y ,t
hechangesi thas
madet ot hedatabaseper sist,ev enift herear esy stem f ail
ur es–
Ex.:
Oncet heexecut ionoft het ransact ioncompl etessuccessf ull
y,andt he
userwhoi ni
tiatedt het ransact i
onhasbeennot i
fiedt hatt hetransf
erof
fundshasbeent akenpl ace, itmustbet hecaset hatnosy stem fai
lur
ewi l
l
resultinal ossofdat acor respondi ngt ot hist ransf eroff unds.

These properti
es are aft
ercal l
ed t
he ACI D pr operti
es.To gain a bett
er
underst
anding of ACID pr oper
ti
es,consider a si mplifi
ed banki
ng syst
em
consi
sti
ngofsev er
alaccountsandasetoft ransactionthataccessandupdate
thoseaccounts.Hereitshouldbenotedthatt hedat abasepermanentl
yresi
des
ondisk,butthatsomeporti
onofitist
emporarilyresidinginmainmemor y.

Stor ageandf i
lestructure
Thev ariousdat aitemsi nt hedat abasemaybest oredandaccessedi nanumber
ofdi f
ferentst oragemedi a.Thest oragemedi acanbedi stingui shedbyt hei r
relativ
e speed,capaci t
y,and r esi l
i
ence t of ail
ur e,and cl assi f
ied as v olatil
e
storageornon- volati
lestor age.
 Vol at i
lestor age–I nformat ionr esidingi nv olatil
est or agedoesnot
usual l
ysur vivesy stem cr ashes.Exampl esofsuchst or agearemai n
memor yandcachememor y
.Accesst ov olati
lest or agei sext r
emel y
fast.
 Non- volati
lest orage–I nformat ionr esidingi nnon- volatil
estor age
survivessy st em cr ashes.Exampl esofsuchst or agear ediskand
magnet i
ct apes.Bot h,howev er,ar esubj ectt of ai
lur e(ex.,head
crash),whi chmayr esul tinlossofi nfor mat i
on.
Thedat abaser esidesper manent lyonnon- volatil
est orage( usual l
ydi sks),andi s
par t
iti
onedi ntof i
xed-lengt hst orageuni tscal ledbl ocks.Bl ocksar et heunitsof
dat atransf ertoandf rom di sk,andmaycont ainsev eraldatai tems.Tr ansacti
ons
i
nputi nformat i
onf rom thedi skt omai nmemor y
, andt henout putt hei nfor
mat i
on
backont othedi sk.Thei nputandout putoper ationsar edonei nbl ockuni ts.The
blocksr esidingont hedi skr eferredt oasphy sicalbl ocks,t hebl ocksr esiding
tempor aril
yi n mai n memor yar er eferr
ed t o asbuf f
erbl ocks.The ar ea of

33
memory wher e bl ocks resi
de temporar
il
yis cal
led the disk buf
fer.Block
movement sbet weendi skandmai nmemor yar
einiti
atedthroughthefoll
owing
t
wooper ati
ons–
1.I nput(
A)t ransferst
hephysicalbl
ockAtomainmemor y
.
2.Out put(A)t ransfer
st he buff
erblockA tot he di
sk,and repl
acest he
appropriatephy si
calbl
ockthere.

I
nput
(A)
A
Out
put
(B)
B B

Eachrequestspeci f
iest headdr essesont hedi
skt oberefer
enced,t
hataddress
i
si nthef orm ofabl ocknumber .Ablockisacont i
guoussequenceofsectors
fr
om asi ngletrackofonepl atter.Bl
ocksizesrangefrom512by t
estosev eral
ki
lobytes.Datat r
ansf er
redbet weendi skandmai nmemor yinunit
sofblocks.
Thelower -l
evel
soft hef i
lesy stem managerconv ertbl
ockaddressesint
ot he
hardware-l
evelcyl
inder ,
surface,andsectornumber .

RAID
Thedatast or
agerequi
rementsofsomeappl i
cati
ons(i
nparti
cul
arweb, dat
abase,
andmul ti
mediadataappl
icati
ons)havebeengrowingsofastt
hatalargenumber
ofdisksareneededt ostoredataforsuchappli
cati
ons,eventhoughdiskdri
ve
capacit
ieshavebeengrowingveryfast
.

Hav i
ngal argenumberofdisksinasystem presentsoppor
tuni
tiesf ori
mprovi
ng
therateatwhi chdatacanbereadorwritt
en,ifthedisksar
eoper atedinparal
l
el.
Parall
eli
sm canal sobeusedtoper f
orm severali
ndependentreadsorwr it
esin
paral
lel
.Thi ssetup isforimprovi
ng thereliabi
li
tyofdatast or
age,because
redundantinformati
oncanbest or
edonmul tipledi
sks.Thus,f
ai l
ureofonedisk
doesnotl eadtolossofdat
a.

Avari
etyofdi
sk-
organi
sat
iont
echni
ques,
col
l
ect
ivel
ycal
l
edRedundantAr
ray
sof
I
ndependentDi
sks(RAID)
.

RAID canbeimplement edwit

hnochangeatt hehardwarelevel,usi
ngonly
soft
waremodif
icat
ion.SuchRAIDimpl
ement
ationsar
ecalledsoft
war eRAIDand
syst
emswithspeci
al har
dwaresuppor
tar
ecal
ledhardwareRAIDsy stem.

HardwareRAI Dimplementat
ioncanusenon- volati
l
eRAM t orecor
dwr itest
hat
needtobeexecut ed,i
ncaseofpowerf ai
lur
ebef or
eawr i
teiscompleted,when
thesystem comesbackup, i
tret
ri
evesi
nformationaboutincomplet
ewr i
tesfr
om
non-v
olati
leRAM andt hencomplet
ethewr i
tes.Wi t
houtsuchhardwaresupport
,

34
ext
raworkneedstobedonet
odet
ectbl
ockst
hatmayhav
ebeenpar
ti
all
ywr
it
ten
bef
orepowerfai
lur
e.

Somehar dwareRAIDimplementati
onspermithotswappi ng,thati
s,faul
tydi
sks
can beremov ed and r
eplaced bynew oneswi thoutturning poweroff.Hot
swappingreducesthemeant imetorepai
r,si
ncereplacementofadi skdoesnot
havetowaitunti
latimewhent hesyst
em canbeshutdown.

Thepowersuppl y,orthedi skcont r

oll
er,orevent hesy st
em interconnecti
onina
RAIDsy st
em coul dbecomeasi ngl
epoi ntoffail
ure,thatcouldst opfuncti
oning
oftheRAI Dsy stem.Toav oi
dt hispossibil
it
y,goodRAI Dimplement ati
onshav e
multi
pleredundantpowersuppl ies(withbat t
erybackupssot heycont i
nuet o
functi
onev enifpowerf ai
ls).SuchRAI Dsy stemshav emulti
pledi skcontrol
lers,
andmul ti
pleinterconnectionst oconnectt hem tothecomput ersy st
em (ortoa
networkofcomput ersystem) .Thus,fai
lureofanysi nglecomponentwi l
lnotstop
thefuncti
oningoft heRAI Dsy stem.

Stor
ageAccess
Adatabaseismappedi ntoanumberofdif
fer
entfi
les,whicharemaint
ainedby
theunderl
yi
ngoper at
ingsyst
em.Thesefil
esresi
deper manentl
yondisksand
eachfi
leispart
it
ionedint
ofix
ed-
lengt
hstor
ageunit
scal l
edblocks,abl
ockmay
cont
ainsever
aldatait
ems.

A maj orgoaloft hedat abasesy st

em i sto mini
mizet henumberofbl ock
transf
ersbetweenthediskandmemor y.Onewayt oreducethenumberofdi sk
accessesistokeepasmanybl ocksaspossi bl
einmai nmemor y.Sinceitisnot
possiblet
okeepallblocksinmai nmemor y
,weneedtomanaget heallocat
ionof
thespaceavail
ableinmai nmemor yfort hest
orageofblocks.Thebuf feri
sthat
partofmai nmemor yav ail
ableforstorageofcopiesofdi skbl ocks.Thereis
alwaysacopykeptondi skofev er
yblock.

I
ftheblocki salreadyi nthebuffer,t
hebuffermanagerpassest headdressoft he
bl
ockinmai nmemor ytotherequester
.Iftheblocki snotinthebuffer
,thebuffer
managerfi
r stallocatesspacei nthebufferfortheblock,thr
owingoutsomeot her
bl
ock,i
fnecessar y,tomakespacef ort
henewbl ock.
Fi
leOrganisat i
on
Afil
eisorgani sedl ogical
lyasasequenceofr ecords.Theserecordsaremapped
ont
odi skbl ocks.Fi l
esar eprovi
dedasabasi cconstructinoperat
ingsy st
ems.
Weneedt oconsi derway sofrepresent
inglogicaldatamodel si
ntermsoff il
es.

Althoughblocksareofaf i
xedsizedeter
mi nedbythephysi
calpr
opert
iesoft he
diskandbyt heoper at
ingsystem,wher easrecordsizevar
y.Inar elati
onal
dat abase,t
yples ofdisti
nctrelat
ions are gener
all
y ofdif
fer
entsi zes.An
alternati
vei
stostruct
ureourfi
l
essot hatwecanaccommodat emulti
plelengths
forr ecor
ds.

35
Fixed-
Lengt
hRecords
Letusconsiderafi
leofaccountr
ecor
dsf
orourbankdat
abase.Eachr
ecor
dof
thisfi
l
eisdefi
nedas–

t
ypedeposi
t=r
ecord
account-
numberchar(
10)
;
branch-
namechar(22)
;
balancereal
;
end

I
fweassumet hateachchar acteroccupi es1by teandt hatar ealoccupies8
byes,ouraccountr
t ecordi s40by t
eslong.Asi mpleappr oachi stouset hefir
st
40bytesfort hefirstrecord,thenext40by t
esf orthesecondr ecord,andsoon.
However,therearet wopr oblemswi t
ht hissimpl eappr oach–
1.I tisdiffi
culttodelet earecordfrom t hisstructure.Thespaceoccupi edby
ther ecordtobedel etedmustbef ill
edwi thsomeot herrecordofthef i
le,
orwemusthav eawayofmaki ngdel et
edr ecordssot hattheycanbe
ignored.
2.Unl esst hebl ocksi zehappenst obeamul t
ipl
eof40,somer ecor
dswi ll
crossbl ockboundar i
es.Thatis,par toft her ecordwi llbest oredinon
blockandpar tinanot her
.Itwouldt husr equir
et wobl ockaccessest oread
orwr it
esuchar ecor d.

Account
-no Name Balance
Recor
d0 1000 Ramesh 1000.00
Recor
d1 1001 Suresh 1500.00
Recor
d2 1003 Alok 2000.00
Recor
d3 1204 Santosh 5000.00
Recor
d4 1400 Ruchi 2500.00
Recor
d5 1475 Rahul 3500.00

Organi
zat i
onofRecor dsi nFiles
Therearethr eemaj ort ypest oor ganizerecor dsi naf i
l
e–
1.Heapf i
leor gani zati
on–Anyr ecor dcanbepl acedany wher eint hef i
l
e
wher et herei spl acef ort her ecord.Ther eisno or der ingofr ecor ds.
Ty pi
cal l
y ,t
her eisasi nglef i
leforeachr el
at i
on.
2.Sequent ialfileor ganizat i
on– Recor dsar est or edi nsequent ialor der,
accor dingt ot hev alueofa“ searchkey ”ofeachr ecor d, i
.e.,processingof
recordsi nsor tedor derbasedonsear ch- key.Asear ch-keyi sanyat tr
ibute
orsetofat tr
ibut es,itneednotbet hepr imar ykey, orev enasuperkey .
3.Hashi ngf i
leor gani zation–Fi leor ganisationsbasedont het echniqueof
hashi ngal lowust oav oi daccessi ngani ndexst ruct ure.I nt histechnique,
wehav etheaddr essoft hedi skblockcont aini
ngdesi redr ecor ddirectl
yby
comput ingaf unct i
onont hesear ch-keyv al
ueoft her ecor d.Fort hi
swe
shalluset het erm buckett odenot eauni tofstor aget hatcanst oreoneor

36
 morerecor
ds.Abucketi
stypi
cal
lyadi
skbl
ock,
butcoul
dbechosent
obe
smal
lerorl
argert
hanadiskbl
ock.

LetKdenotethesetofal
lsearch-
keyval
ues,
andletBdenot
ethesetofal
l
bucketaddr
esses.Ahashfunctonhi
i safuncti
onfom Kt
r oB.

Toinsertarecor
dwi thsearchkeyKi,wecomput eh(Ki)
,whichgivesthe
addressofthebucketforthatrecor
d.Nowassumet hatther
eisspacei n
thebuckett
ostoretherecord.Then,t
herecor
disstor
edinthatbucket.

Fordel
eti
on,ifthesearch-
keyv al
ueoft herecor
dtobedelet
edisKi,we
comput
eh( Ki)
,thensearchthecorrespondi
ngbucketf
ort
hatrecor
d,and
del
etet
herecordf or
mt hebucket.

Bucket0

Bucket1
A-217 Ramesh 750
A-305 Rahul 350

Bucket2

Bucket3
A-222 Al
ok 700
A-102 Amar 800

Bucket4

Bucket5
A-355 Sachin 1000
A-450 Sunil 580

Bucket6

Data-Di
ctionarySt or
age
Arelati
onal-databasesystem needstomaintai
ndataaboutt
herel
ati
ons,suchas
theschemaoft her el
ati
ons.Thisinfor
mationiscall
edthedatadicti
onaryor
system catalog.Amongt hety pesofi
nformati
onthatt
hesystem muststor
eare

37
t
hese–
Namesoft herelations
Namesoft heat t
r i
butesofeachr elati
on
Domai nsandl engt hsofattri
but es
Namesofv i
ewsdef inedont hedat abase,anddef init
ionsoft hose
v i
ews
 Integri
tyconst raints(forex.Keyconst r
aints)
Thedatadict
ionarymayal sonot ethest orageor ganization(sequent ial
,hashor
heap)ofr
elat
ions,andt helocat i
onwher eeachr el
ati
oni sstored–
 Ifr el
at i
onsar e st ored in oper ating system f il
es,t he dict
ionary
woul dnot ethenamesoft hef i
le(s)cont ainingeachr el
ation.
 Ifthedat abasest or
esal lrelationsi nasi nglef i
le,thedi ct
ionary
maynot ethebl ockscont aini
ngr ecordsofeachr el ati
oni nadat a
structuresuchasal i
nkedl i
st.

UNITV
Dat
abaseSy stem Ar chitectures
The ar
chi
tectur e ofa dat abase sy stem r uns i nthe comput ersy stems as
net
worki
ng,par all
eli
sm, anddi stri
bution–
 Net wor kingofcomput ersallowssomet askst obeexecut edona
serversy stem,andsomet askst obeexecut edoncl i
entsy stems.
Thisiscal ledclient-serverdatabasesy st
em.
 Par all
elpr ocessi ng within a comput ersy stem allows dat abase
system act i
viti
est obespeededup,al lowingf asterr esponset o
tr
ansact ions,aswel lasmor et ransactionspersecond.Thi sis
caledpar
l all
eldat abasesy stems.
 Keepi ngmul t
iplecopi esoft hedat abaseacr ossdi f
ferentsitesalso
all
owsl argeor ganizati
onst ocont inuet heirdatabaseoper ati
ons
arecalleddi stri
but eddat abasesy stem.

Central
izedSy stem
Central
izeddat abasesy stemsarethosethatrunonasi ngl
ecomput ersystem
andnoti nt
eractwi t
hothercomput ersy
stems.Suchdatabasesyst
emsar esingle
-userdatabasesy stemsr unni
ngonpersonalcomputer
s.Inthi
ssystem CPUand
anumberofdev icecont r
oll
erst
hatareconnectedt
hroughacommonbust hat
provi
desaccesst osharedmemor y.

di
s di
s pr
int
e Tapedr
ive
r
38
Di
sk Pri
nter Tapedri
ve
CPU Cont
rol
l
er Cont
roll
er Contr
oll
er
Cl
ient-
ServerSystems
Centr
ali
zedsy st
emsactasserversystemsthatsat
isf
yrequest
sgener
atedby
cl
ientsyst
ems, i
scaledcl
l i
ent
-ser
versystems.

f
rontend
cl
i
ent cl
i
ent cl
i
ent cl
i
ent

network,
i
nterf
ace(SQL+API
)

backend ser
ver

Inthissystem dat abasefuncti

onali
tyisbroadlydi v
idedi ntotwoparts–t hef r
ont
end and t he back end.The back end manages access st ructures,quer y
evaluati
onandopt i
mizat
ion,concurrencycontrol,andr ecovery
.Thef r
ontendof
adat abasesy stem consist
soft oolssuchasf or ms,repor twri
tes,andgr aphi
cal
user-i
nterf
acef acili
ti
es.Theinterf
acebet weent hef r
ontendandt hebackendi s
thr
oughSQL, ort hroughanapplicati
onprogram.Thef ront -
enduserinterfaceand
theback-endser verhaveAppl i
cati
onDev elopmentTool sareusedt oconst r
uct
userinter
faces.

Ser
verSyst
em Archit
ectures
Ser
versy
stem canbecat egori
zedas–
 Transacti
on-ServerSy stems – Thisis also call
ed query-
server
systems,provi
deandi nter
facetowhichclientscansendr equests
toperfor
m anact ion,inresponsetowhicht heyexecutetheaction
andsendbackr esult
st othecli
ent
.Ther equestsmaybespeci fied
by using SQL,ort hrough a speci
ali
zed appl i
cati
on program

39
 i
nt erface.
 Dat a-ServerSy stems–Thi ssyst
em al
lowsclientstoi
nteractwith
theser ver
sbymaki ngrequeststoreadorupdat edat
a,i nunits
suchasf ilesorpages.Forexampl e,fi
leserversprovi
deaf il
e-
syst em interfacewhereclient
scancreate,update,r
eadanddel ete
fi
les.Dat a ser versfor database systems of f
er much mor e
funct i
onality,theysupportunit
sofdat a-
suchaspages,t uples,or
object s–t hatar esmal
lerthanafil
e.

Paral
lelSystems
Thesesy st
emsi mproveprocessingandI /Ospeedsbyusi ngmulti
pleCPUsand
di
sks in par allel
.In paral
lelpr ocessing,many oper ati
ons ar
e perf
ormed
si
multaneously ,asopposedt oserialprocessi
ng,inwhi chthecomputati
onal
st
epsar eperformedst epsareperformedsequent i
all
y.Thesear
etwot y
pes–
 Coar se-gai
npar all
el– Thispar al
l
elmachi neconsi
stsofasmal l
numberofpower fulprocessors.
 Massi v
ely par
allelorFi ne-gai
n parall
el– This machine uses
thousandsofsmal l
erprocessors.

Paral
lel
systemsconsistofasetofcomponent s(processors,memoryanddi sks)
thatcancommuni catewi t
heachot herv i
aani nt
erconnect i
onnetwork.Thr ee
commonl yusedtypesofinter
connectionnetworks–
 BUS–Al lt
hesy stem component scansenddat aonandr eceive
datafrom a single communi cat
ion bus.The bus could be an
Ether
netorapar al
lelinter
connect.Busar chi
tectur
esworkwel lfor
smallnumbersofpr ocessors.

 MESH–Thecomponent sarenodesi
nagr i
d,andeachcomponent
connect
st o al
litsadjacentcomponentsint hegr i
d.Inat wo-
di
mensionalmesheachnodeconnect stof ouradjacentnodes,
whil
ei nat hree-
dimensionalmesh each node connectsto si
x
adj
acentnodes.

40
  Hy percube – The component s are number ed i
n binary,and a
componenti sconnect edt oanot
heri ft hebi naryr
epresentati
onsof
theirnumber s di f
ferin exactl
y one bi t
.Thus,each oft he n
component si s connected tolog( n)ot hercomponent s.Ina
hypercube interconnect
ion,a message f rom a componentcan
reachanyot hercomponentbygoi ngt hr oughatmostl og(n)li
nks.I
t
shouldbenot edthatthecommuni cationdel aysi
nahy percubeare
signif
icant
lylowert hani
namash.

01 11
1 1

00 10
1 1

01 11
0 0

00 10
0 0

Paral
lel
DatabaseAr chitectures
Therearef
oll
owi ngar chitectur
almodel
sf orparall
elmachines.
 Shar edMemor y–Allt
hepr ocessor
sshar eacommonmemor y.
 Shar edDi sk–Al lt
hepr ocessorsshareacommonsetofdi sks.
Shared-disksy stemsaresomet imescall
edcluster
s.
 Shar ed Not hing – The pr ocessors shar
e neithera common
memor ynorcommondi sk.
 Hi erarchical– Thi s modeli s a hybri
d oft he precedi
ng t
hree
archit
ectur es.

Distri
butedSy st
ems
In a distr
ibuted system,t hedat abasei sst or ed on severalcomput er
s.The
comput ersi n a dist
ributed sy st
em communi cate with one anot herthrough
vari
ouscommuni cati
onmedi a,suchashi gh-speednet worksort elephonelines.
Theydonotshar emai nmemor yordisks.Thecomput ersinadist r
ibutedsystem
mayv aryi n si
zeand f unction,rangi
ng f r
om wor kstati
onsup t o mainframe
systems.
Thecomput ersinadi st
ributedsy st
em arer eferredtobyanumberofdi f
ferent
names, suchassi tesornodes.Ther earesev eralreasonsf orbuildi
ngdi st
ri
but ed
databasesy stems, i
ncludingshar i
ngofdat a,autonomy ,
andav ai
labili
ty.
 Shar i
ngofDat a– Themaj oradv ant
agei nbui l
dingadi st
ri
but ed
databasesy st
em i stheprovi
sionofanenv i
ronmentwher euser sat

41
 onesi temaybeabl et oaccesst hedat ar esidingatot hersi te.For
exampl e,i
nadi stri
but edbanki ngsyst em, wher eeachbr anchst or es
dat arelatedtot hatbr anch, iti
spossi blef orauseri nonebr ancht o
accessdat ai nanot herbr anch.Andal soausercant r
ansf erf unds
from onebr ancht oanot her.
 Aut onomy–Thepr imar yadv ant
ageofshar i
ngdat abymeansof
dat adi str
ibutioni st hateachsi tei sabl et or etainadegr eeof
cont roloverdat at hatar estoredlocally.Inacent ral
izedsy stem, the
dat abaseadmi nistratoroft hecent r
alsi tecont rol sthedat abase.I n
a di stri
buted sy stem,t herei s a gl obaldat abase admi nistrator
responsi blefort heent i
resy st
em.Apar toft heser esponsi bilit
iesi s
delegat edtot hel ocal databaseadmi nistr
at orforeachsi te.
 Av ail
abi l
it
y–I fonesi tefail
sinadi st ri
butedsy st em, ther emai ning
sitesmaybeabl et ocont i
nueoper ating.Inpar ticular,ifdat ai tems
arer epl i
catedi nsev eralsites,at ransact ionneedi ngapar ticular
dat aitem mayf indt hatitem inanyofsev er alsi
t es.Thus, thef ai l
ure
ofasi t
edoesnotnecessar i
lyimplytheshut downoft hesy stem.

Si
teA Si
teC

Net
wor
Communi
cati
on k
vi
anet
work

Si
teB

DatabaseofWeb
Awebser v
erisacomput erthatholdsinformati
onawebsi te–i tsHTMLpages,
i
magesandsoon.Thecl ientisthevisi
tortothewebsite(webbr owser).I
not her
words,t hei nter
networ kson t hecl i
ent/ser
vermodel .Two comput er
swor k
t
ogether, andcommuni catewi theachother.Thecli
entcomput ersendsar equest
fori
nformat ionofdatabaset otheser v
ercomput er
.Theser verthenrespondst o
t
he clientwi t
ht he i
nformation ofdat abase t
hatwas r equested ofi t.This

42
par
adi
gm i
sther
equest
/responsemodel
.

Generall
y,oncet hecli
enthasr ecei
vedtheinfor
mat i
onofdat
abase,theprocess
i
sf i
nished.Theser verhasnoi deawhat ’
shappeningonthecli
ent,becausethe
serv
erandcl i
entar etwosepar atecomputers?Theyonl
ycommuni catewithone
anotherduri
ngt herequestresponseprocess.Oncethepagehasbeendel i
vered,
theserverdoesn’tcarewhathappens.

Cl
ient Ser
ver
Yes
Request Fi
rstt
ime? Compi
l
e

No

Handl
eev
ent
s

Execut
ecode

Di
spl
ay Tr
ansl
atei
ntoHTML

i
.e.

WebBr
owser

Communi
cat
ion
Net
wor
k

WebSer
ver

Appl
i
cat
ionSer
ver

Dat
aSer
ver

ODBC(
OpenDat
aBaseConnect
iD
va
i
tt
ya
)base

43
TheODBCst andar
ddef i
nesawayf oranapplicati
onprogram tocommuni cate
withadat abaseserver
.ODBCdef inesanAppl icat
ionProgram Int
erface( API)
thatappli
cati
onscanuset oopenanconnect ionwi thadatabase,sendquer ies
andupdates,andgetbackresult
s.Appli
cat
ionssuchasgr aphical
userinter
faces,
stat
ist
icspackages,andspreadsheetscanmakeuseoft hesameODBCAPIt o
connecttoanydatabaseservert
hatsupport
sODBC.

Eachdatabasesy stem supporti

ngODBCprovi
desalibr
arythatmustbelinked
wit
ht hecli
entprogram.Whent hecl
i
entprogr
am makesanODBCAPIcal l,the
codei ntheli
brarycommuni cat
eswitht
heser v
ertocarryouttherequested
acti
on,andfet
chr esul
ts.

DSN( DataSourceName)
Windowsoperatingsystem hasthreetypesofDSN–
 UserDSN
 Sy stem DSN
 FileDSN
UserDSN andSy st
em DSN st oredataindatabaseprov
ider
,whereasFil
eDSN
connectswit
ht hedat abaseprovider
.UserDSNr epr
esentscur
rentsyst
em and
Syst
em DSNr epresentsalluser
s.

ODBCandDSNareconfi
gur edinfol
lowi
ngsteps–
 Open“Contr
ol Panel”
 Open“Perf
ormanceandManagement ”
 Open“Administr
ativ
eTools”t
hiswil
lshow

 Open“
ODBC”

44
 Nowwecanconf
igur
eanyoneDSN

45