Sel 3

(0 ohat is tog rotda that trock,

Am
t0 a kqunte b
madt to the databas.
at! change
us
Vanious pusposts.

mode
wohat t th
locks n mllipla
mode
Ars thhee typs of intnbion
totku n ndlyle granulaihy
Tntenioh shaud (s)
T.1 -5 Dntwion taclusive Cix)
shaud & Inttion eaclusive (sx)
hat i ee-do and Un-do opuratig ian

Un-do openabioMs a
-do openahi cnucial to
Re-do
daBa nteaniny ard nuovey
in
enusin
made by
"Redo : st involves noplying the change
Commi Hed transackion to tu databa

udo : gt invotver neverting th chorqs mad

uncommited branaehion
wohat au He Acid moperhes f a tansachon
Ary -tto ’ Abicihy
5Consi's eny
 (mauk

4th answey
Auy
Ashat is ISAM indencd Suquenhia Aceess
que
tde onganira h'on techni
tSAM
sards

woays to sequentidy
Metand both
eicivnt
that povde eced.
access

Pegormana
shat is pepet tarning?
it prous of ophmizin a
Pertormante tuning
system to
vesponsiveness
tnden 1
wohat Secondaay
an tdeud l that
tu
tontadn
data
an
addihional indey
tndea:
Catonday non - primay key atthibnte
meated
 Nested Quey? nai has another
Nestcd Sueg: "he mested Qe is a queqna
enlbedded hin it
s caled a ubqer
enbedded qe
t Constsis f t,nOTIN, ex\6T, noTExIST operntorS .
A tigger spd hype sored
Prpcuduu automakealay eneutey
° sL Statenent in gesponAt
event
peeiie eve database
 Expla'n thethe Timc
Time Stomp
stomp- Bosrd Concuvwency Conlvol
Pyotocol. tHow is it vscd to entuvc selialiabl

Cmeuey contvol Can be implement cd in

d4xent ways. Dne way to inplemend it is by
Vsing tocks. Now, let os discoss Tine stomp
Dvdeg proloco l.
Tinestaup is a oniquc identttiu (yeatcad
by he DBMs to identifqia! trons achion. They
Usyally axsigned in Hhe Drde in which hey
Submi ttcd to the System. Rete to the timestamp
tYansacHon T sTSCT)Fo

Timestamp odledng pyotoal -"

?

The' matn idec tor this pvobocol is to ovden

4lé vons actio bused on heiv Timestamps.
the
A schedute in which the transactfom paxticipate
t Hen souali 9able und the only quivadent
Sewel schedute pemitted has the transoction
in the ydcy b4 hei'y Time slamp Values,
statig singy the chedale sik eyuivalent
to the paut'eulay Seval oYdon Corespondivg
to the tYde b-f the TranSaction timestamps.
 2

-An alnoi hm musl

ilem acteacd by (unf licling Ihat oy coch
bpeelioN
Schedulr, the oYden in uohich in
the ite
acceyed violate the ordeig
4his values
rel* W.TS
atig (x) is thedafabcuse
to cach

lazget
item X:

ronsoclion that exeçúled. timestamp

orite Cx).
bt
RTsCx) is the
transachon' thot
laxqeittimestamp
ecud Yeud Cx)..
ot
Babit Time: tamp ordeving
Evey tyansacti ons issued a times tunp
bas ed bn ohen it entou the system. Suppose,
ik an old vansaction T has timestamp:
Ts (T:) ) a new tydnsacti Dn T is Csiqne d
timestamp TSCT;) Svch thad Ts (Ti)<TscT;).
ConuYYent eeLLtion
The:. pYolotol mancig es the
Such that the timestam s detexnine
ovde. The timeckam*
Se'atiabiity
Protocol' ensuY C% Fhad any Contlieting eud
wri te ereuted
4ime stamp oYdon.
 issue
hlheneveH Sne oosnction T tyie, to
Basic TO
a Rtcmx) a w item (x), the
D-f T with
algonithm Compates the timtslamp Timestanp
to ensuye Hat Hhe
R- TS Cx) & N-Ts Cx) descr'be the basi'e
d. Tht
Ovd en is Dot viotate
pyotocol in te tollouc'ng two tases.
To Wtemx)
Wheneve a TYansacHon T Is sues a
L) Cndití onb
opeahion, check. thetollooia g Cuditi
TS (x) >TS CT) and i W-is (a)>TSCT))
4I+ R-
reject the
then aboyt cnd vollback T and
opexation ., else,
t Enecute W-ikemCx) opeation o4 T, Ond
set w.TS CX) t Ts CT).

2) .ihevey 'a TYcnsac Hon T issues a R iFemCx)'

opeakon, chech the tolouoing tondi'-ons
It TS (x) >TS CT), then abovt and
Yeject T and Yeject the opoation , ele.
I4 W_sx)c TS CT), Hhen exceutc the
R. itcm Cx) opelion o T and set R.1sCx)
to thc D4 1S CT )and CvYve nt RTS (%).
 Trimedaap cvdering potecel ensuvts senialiabi liy
since the piec.dence gaph will bc ot the teim.

Tansacion Tianscicton
oith Snalle with lag1
Ts TS

* Tincstanp potecol ensus recdom From

dead lock s.to tansaction eveh wu ts.
* But the Schedule may not NOt be cascade fyec,
and not even be Yetoveable.

Shict TimesBamp brdexing

-A Vaniation o Bosic. To is called strict
To Sthat the schedutes axe both s t
and contlict seialisable. In thie Voation,
a Thansoction T that issuer a R item Cx)'y
W-item (*) Such thet Ts CT)> WTS (x) haa i
Yead os wite opeation de laycd 'untiln he
Tyonseclion T that uYDle the Values ot X ha
CommiftCd or aboy ted

Advantag1agey
|. Hiqh ConcUYCnCY
Tinestam based
 3

Contyol allows. foy a high. degrtc o intertene:

tyans achons ' do not
by ensirig
with tucb othe.
E4ticien t The tcchni uc is etieient
reqive' locking and :
Stalable as it does hot
can Fand le a lagg
N6 Dead lock
Since thene te no . lockg involved ) there

is poscibi lity ot dead loc ke

2. Explain mutiple ot
04 tockinq protoco l
 Comparison of Various File Organization Methods
File organization methods are crucial for
efficient data storage and retrieval in databases: Let's
compare some of the most common methods:

1. Sequential File Organization

"Storage: Records are stored in a
specific order, usually sorted by a key field. v
"Access: Records are accessed
sequentially, starting from the beginning of the file. v
"Pros: Simple to implement, efficient for
sequential processing like batch processing,
" Cons: Inefficient for random access,
requires sequential scanning to find a specific record.
2. Random (Direct) File Organization
"Storage: Records are stored in locations
determined by a hashíng function applied to the
record's key.
" Access: Records are accessed directly using the hash
function to calculate the record's
address.
" Pros: Fast access to individual records, efficient for random
access patterns.

" Cons: Inefficient for range queries, requires careful hash function design to avoid
collisions.

3. Indexed Sequential Access Method (ISAM)

" Storage: Records are stored sequentially, but an index is maintained to provide direct access to
specific records.

" Access: Records can be accessed sequentially or directly using the index.

" Pros:Combines advantages of sequentialand direct access, eficient for both sequential and
random access.
" Cons: Index maintenance overhead, requires additional storage space for the index.

4. B-Tree File Organization

" Storage:Records are stored in nodes of a B-tree, a self-balancing tree structure.

" Access: Records are accessed efficiently using the B-tree's search algorithm.

" Pros: Efficient for both sequential and random access, supports dynamic insertion and deletion
of records. v

" Cons: More complex to implement than simpler methods.

Ask Gemini

Gemini can make mistakes, So double-check it

 "Access: Records are accessed efficiently using the B-tree's search algorithm.

" Pros: Efficient for both sequentialand random access, Supports dynamíc insertion and deletion
of records.

" Cons: More complex to implement than simpler methods.

5. Hashed File Organization
Storage: Records are stored in buckets, determined bya hash function applied to the record's.
key.
Access: Records are accessed directly using the hash function to locate the bucket.

Pros: Very fast access to individual records, efficient for equality searches.
"Cons: Inefficient for range queries, reqires careful hash function design to avoid colisions.
Comparison Table:

Random ISAM B-Tree Hashed

Feature Sequential
Sequential with Buckets
Sequential Direct Tree-based
Storage Index

Sequential or Tree-based Direct

Access Sequential Direct
Direct

Simple, Combines
Fast Efficient for Both Very Fast
Efficient for Sequential and Random
Pros Random Random
Access Types,
Sequential Dynamic Access
Access
Access Access

Inefficient Inefficient
Inefficient for Index
for Range Complex for Range
Cons Random Maintenance
Queries, Implementation Queries,
Access Overhead Collisions
Collisions

i Export to Sheets

The choice of file organization method depends on the specific application requirements,such as
the frequency of access patterns, the need for fast random access, the importance of efficient
range queries,and the desired level of storage utilization. By carefully considering these factors,
you can select the most suitable file organization method for your database system.

Ask Gemini

Gemini can make mistakes, so double-check it

) 00:27
 ctosureVV
-then e closye
3 erampe.
opevatovs aotih
)
Axplain Aggregat
Value

alculaeions on multfple valúes and vetun ostnqe

Value6
a
".
6ohen 'it esfevooe
peiform,
An Aggegate epevators "gnees
count functfon avg
Calculatfons except fo the
funct?ons thalfnelucto
aggeqate
SQ Provides many MAXC)etc
Count(, Sum(), MNC),
Table
CX: ¬MP. DATA
Name Dept
mancetog
(salany
80pD0
Ram
producEion e,oDO
Henry
3 Disha RD 46,00o
Helen RD 84,b00
Meera
Afsha
mantding
Productfo 64,000
BhuNan Produc(?on 6o,000
8 Hari 60)000O
Peeth
Manteling. NuL
Mank production 6êiDCO
n) o database
CoUNT(): coUNT() 3etunns the total no.of0bws
+able hat matches the defDect citeria Sn the sa Que
COUNT (*) oR
CoUnNT (coLUMN. NAMc)
 CX SLCCT coUNT (salay) From EMp- DATA3

SUM():he SuMefunclioo akes -the mame b H6e colmo as an

aqument and nelens -the sm of -the a non- nULL Nalues in
n mcemeric lelds
only
ex: Selecl soM (5alar)From eMPbAT A;

AvG(The AVGC) -function us es t he Iame of the column as on

ent hd etams he aver aqe alues 0f | the mon nULL
in the colemnn H eoorks onl for mumeic tie lds
only
¬X? select AvG (6ala)From Eme DATA,
6lp: 444.414
MINC): MuNcfuncion takes the mame of the clumo as Qn
|agument and retums the inemum Wale Present inthe column
Ex: select MIN (salar) Fom P DATA;

MAxC): MAX)funcio takes the name ofthe colum as Qn

agement and eturns the marimum Value presentn the column.
Ex: sele ct MAx (6ala)ron eMPDATE
ATA,

G1ROUP BY: The GIROuP BY 6tatements io SQL is Ised to aNaAe the

identRcal data înto goups fththe help of some tunetfons
Sytax :: select aMe, SUM(soLuMN-NAme) fRO MTABLE-AME
GIROUP 8y coLUMNAe3
HAVINE :The HAVNG clouse s use d
Bms to-feteh the data/
qen coneliion
Nalues from the qoups accondiog to the