DBMS

Hsignment No 1 2 25
Unit
s! Explain the charactesistia f DBMS
--) Doto redundany incondistangy
iS Redundoancy isthe concept g repi tation data
that i each data ma have mOre than than aa
single cop
)h fle systems, data might bebe syed in severa
3ame
Cle o forat ,sting spa
)Ih consistany aries
axies ohén here is cantHi cHng data in
diffexent placei.
places
Data sharing
Hr:le system. daes oot allow sharing G data cor sharig
is too complex
ii)DB MS alloDS ensusenee controlled shared coith pemissios
3) Data concunen
i)Concuxsent acceis to dat meani moTe tanone
s accelsing sam e data at same time
18) kWithout concuIen conthol , data my get lost or
OVexoriten
)Datasearchina
i) fox evey search perahon pextom on fle ytemn
a diffexent appicaion pngram has to be witten
Sndexingoptimizahon techniqua foy fast
databases
searchig
sData itgity
even
lage
yThere maybe cases wbeo some constraints need
oe appied on the data befoxe insexing it
to be io
databale
)Data întegity meansaccuras ý consi s tany
data a
Qve ts e o e
 Page No.
Datg
9e Expkin levels abstraction DXMS
i) Ih DBMS , level o abstrachion hep hide the
+h
complexiby
i)IA ensuxes tht
how data is stosed & maioBanel
users can interact Dth dota
necding ho know withou
evezyìhing obout its pysicad struch
i)The 3 leveli abstrachioo
a) Physica level
b) Logical level
c) ViÇw level
iv) Physical leve!
- This jss lowest level _abstrachon in
-It describes houo the data is physically DâMS.
stoTage devicea 1:ke had disk
stored
Tt
It define the data format
data path in
,ecoxd placement &
v) Logical ievel memoy
-This level describes ohat data is
database & hoo the data is elatedstored inin the
It hides the details phy sical stoxage &sho
the strucre the dotabale
-Delcribel the schemas, tables, columns ,Yelatioahip s,
constraint s
vi)Vieo level &dota ype
-This is
bighet leve! absha chon
d.£fexernt only pa
used based o
on
gthe entine datobse to
theiy needs acceas rihts
T1liple vieus Can be created fox dP Fes ent ges
-Used to hide sensitive dat,
&inaeage seunity simpl: fy omplexiy
 q3 Explain diffevene beoer file yetem databse
Munagenent Sylem ?
Cile Syslem btabase 1Aanngerent Systern
System ostore manage Software to nanage daabaie
les manualy |eficinuy
), High sedundangy Low redundany
3Data sharing is difffaalt Data shazing eay
4) Low seunity |High seu
5)No conuysency control Supports ou hple wsers with.
e) Ne adstracion levet concurreny technigel
e)No abstrachion leve Has 3 abstrachion level
8)
ndexig not Qvailable Supposts îndexing
Highex cost
Lo cost
3) Ex aindows Fle explare Ex MysQL Orade,Mngoba
Complex maintainance Eay maintainance
Q4 Explain wiew &bow to create it Can e update
it ? Explain it in detail?
’i) A view io SL is a yitua table based on the mesult
SQLstatement
set
i Create yiew
-Views are Created the CREATE VIEW statemen
b e basic is ag to llos
CREATE VIE W view-oame AS
SELECI column i , columo2,
EROM_ ablename
WHERE conditioo i
 Vie)Dame The dosised name foy the virua table
GIECT olamn 1, cohumn, The coluons to be included
-IROM tuble Dame i The base kable (s) from ohich the dota is dea
WHERE conditioo:A conditon to hler he To0s induded
in the view
Ex A5sume we have a table STUD
STUD (rollno , namne ,age , mavks, dept)
Now,create a view to show only students the
CsD depax +ment
CREATE VIEW cSDStudents As
SELECT ollno
FRaM STUD
LWHERE dept ='csD'
b) Can De update t
-Yes update it,butoith Some ConditioDs.
-Vieuos that are based on simple SELECT stakomen
frorm a table meet certain criteria often
updatable
-IEa view updaable you Can USe standard INSERT
UPDATE DEÁETÆ statements on the view
-Ex updatingavieo
UPDATE CSD Sudents
SET Mazks =90
Tbiswill uodate the base table STUD if the
iew is updatable
 Fagc Ne.
Dale
g5 Exphin 3-Schema
3-Sh Axchitectuse DBMS
->)The 3Schema Architechure is a frameootk proposed
by ANSI]SPARC to se paxade the wser View the
database from the
phynicad
)Tt inthoduces hree evels
storge
databaje abstraction
Extey nal Schema
)Conceptua Schema
3) Physical Schema
User
External Externa External view
Schema [chem a schemq level
TabBes Conceptual schema logica
relatives tevet
Physica.schema }Physica
|evel
Databaye Hard disk
disk Data
it) Extexnal Schema
-T5is schena describea thepart the database rele
to a paxicula group us
UserS
-View level
- Can have different vieds fox diffexenA
 )
-Definei Lohal dato a speil ic uaer or applicadio
Can see
data
-Hides conple xity unneeksoy
i) Concepua schema
The schema descat be the ovexal! logical struce
the entixe database
-Defna the struchure orsganization 4 the erntie
databale
-Repxont al enties ,zelaionship , atlributes , cortraicte
) Iotend leve
-The schonma daaiba she phyicalstoroge sucse g
daabase
- Defines hoo data is actua lly stoxed on disk
g6. Daua the ER diagram for Banking Sytem
)Bank bave Custome.
F) Bank are idenhAed by anamecode,address main othc
Si) Bank have hzanches
i) Branchel Gdent ied by a bxanch_no,branch-nane,
address
) Customers are identifed
îdeoified by name ,custid, phone numbee,
addregs.
vi) Custome can have. accoust
vi) Accounts Sdentified by accountno. ace-type
balance
víi) Custormes Can avail loans
ix) loans are identtied by loanid, l-án tpe amcunt
X>Account Loans Qxe gelated to bank's bxanch.
 Preonhi
( Name,
Nnc
Address
Sode Bonk
offer Maintasn,
AcCount-No
Loan id
N N
Loantype Loan Account Acc Type
Amount Balance
Avaiked by Mold
Custome
Custid Address
Name Phone
3t £oplain the funcion follouaing component
9Quezy poceasor 2) Transachon Mansgement
-’) Query prDcasar
iThe queny pocelsor fs Taponsible fox întenpreting
exeautig queriea
betoeen wserS the databaie
)Tt acts bridge
H) Key taks aue PaoceasoT
a) Parsing- checks gL syntax
Checks agL yntax & coover ts t Soto an
Sotexna format
b)Quey Optimization- Cho0se themost efAient
 Page No.
Date
OExecukon- Runs Ihe oplrmized quesy rerives
ffom he doBnbase
&neporks Comman
d)Eo Che king - Dckecls
i) be queny prvceAsY
DParses the ommand
Optimizes it
Exeautes it & retun 4he reBult
c) "Fansacdion Management
STbe ttansachon manage ensurel that muliple cpeahe
unit
database are treated as a single , invisible
) key tasks
a) start l Cormmit Rollback Tsansacioo
)Maiotains logi fox Tecovey
e) Detect & revent conflet
i) ensures ACID popextie tansaion
- Atomicity -All o nane the operations happen.
Consistany-Database rermgins in avaid state
-ITolaio-Tonsa cions do not fotez fexe oih each olhez
even atle
Duzab:l:y -Changes are
saved
permanenty
craih
Construct an ER diagram tos car -însuxance
obose customers oDn one og mnoTe caxs eacb. Each
car ba associated ith it ze to any numbe
reorded acident Constuct appoprke table
Poy the Same
’)IniBy scds
)Custome
3)Aciden
:) Aibutes
Customes C Custid, cul-name, Address)
Cay Cmoded, engine -no,ea)
Aecident (aeport-no ,localion , Acc- dato)
3) ER Jiagzum
oodolnane
nme
Cuutid Age eng_no
Jear
Customer colors
Addses s Cars
Tepovt,n
cty stoeef Phone-no
Participale Accident
Acci- date
Darage-Amount
an ER diagsan fox a bray yskem that mange
book, bowers theix laan traosachion The system
peeds to track book details (IS BN,itle , authox, pubi'sher
year publicaion), bomrW Pnformaion(bomwex FD,
addressy phane nun bes)& loan details (loan ID,
due date , rehurn dae ,late fees).The yatem should
cord sbich books axe cunently bomooed by
obich boowess
 Prrotlo.
|Datz
Passwd
Reports
(ginid
Athenlicai
IssueRottn Sytern
Publisherid
Manages
Login
name Patsbert
staf
sta.d abishes
maintaio
keeps
track RegerveDake
price
Addvess
Books categ
Email Teserve/
Readexs £diion
dat Authdo
Titte
p h o n t n o
User_ID
retuv dake
name
first name lagt name
Explatn the concept o candidate ky prinay keg
oxeign hey Thenkiy above ]isted fos the foloag
Person Sdriver id,oameaddres, Contactno) Car(Iicence ,
model
Heas) Owns (dziver-id, licence)
) Candidate key
)A Candi date key i aminimal sel attributes hat can
uniquely identify a tuple snatable
) A table can have
mulliple candidate keys but
 only One is choseo as ihe pimay hey
i)The mirimd sel atthibules that uni quely identify
a tuple is koown as a candidate key
NNSE In a Tesson table ,dive-id a possibly contactn
could be ankcdate keys iff both are nique
) Primay hey
Hpimazy key is the main unique îdertifier tor cach
necod in a table
idI4 i chosen from the candidade key
:DIt annot be NULL mut be unique
iimay key is a unique key, meaning tcao
it uniquely
identiy each re coxd in q table
)Example daivex-id can be chosen al the pámang
fox Person
3) foreign Key
DA foxcigo key is a eftrene to the primay ky
anothey table
ii)Ft create relationhip betoeen too tables
)Eosue refesestial integit
ne table that
iv) A foéígo key is an attribue in one
vexs to the ey n anothex table
Je.nthe Oons table, driver_id is a
to the Pexson table
fegn.
keg efteing