0 ratings0% found this document useful (0 votes) 85 views26 pagesDBMS Notes
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content,
claim it here.
Available Formats
Download as PDF or read online on Scribd
2.\| », Wa (.
3!) Ramis ae
Ml cut Date
| Struchmed Data, .
1
i Doty ino Ponticul format + fs
} Database. Manesment Sytem
i
“Hains
tt Ih diserpant ion 4h
TNo- Source Destinatiay Weebw.
Zedundaney {mulliple copies lo Nome. ce nation © 2
of data)” |
inconsi: ig ftesotine |
: Te Strucure Train |
> fo inconsistency +
Wing TainName >
4. Tafetmatin might snot be available + a.
Data _abstxaction aff) cade,
View Level Je ‘Different access
| ; pormissions
How the database is Z—[Concephual Covel f
0
~achaalg —tesarect = ;
: Oot ~ | Physical Level [deals with how date
| Yreachers Stgnature g.—
ahored+ Database stw
file System
Hardware}
which is fetched.
Vvimately dato is. styyed on handwan
by file system + file System interacts with DB sofhwaxe-
© [Vex Level ovis, views Jpg) -
triggers, |
[view af U procedures
[eencapheat teed]
¢ theo Classes
L Physic Le ceuel J J
Entity’ Relalicnship Model
(ER Model)
Ly Used fox designing, oF database
7d d
Entity > ang disting ishable object 7
£4— individual _student-
focally member.
Teacher's Signature © eve: : set of _alttib uty os)
Even enlity does not nece, y have existen Ce «
Fy cesar physical.
i t lish 6)
+ Regihs Name Pla (Counce Ses
individual student=< 20214225, x Lucknow , B-Tech+ 5>
Collection of similan entilies is called Eotiy sok +
| ke: — Er i. ie Hnout obi
: Primary kew
Candidate. Ko
—Sup: aa Key
Foxeign key ~
Cosh ey. ( Primed , Candidate. peed
Came Goch by fey ;
(eg tat 9 = rey
Addition
cactertbach
SS
Pi Key 2 Candiclate key ane same »
7 oe
{ fem ving any of the atfaibubes will lead +p
aot being a Key)
___Staper key, > (ey puopaity is the va)
| l fedundat( Additicena)) atti butes
Teacher's Signdture en) NULL vale, |
\ 7 Undefined i
(Ges 92" yy Ug dad th dned th
be:
Aime) (nator) ("°F ) — \pems Sei. difficult to
a opply —egpregede
H 45,000—books Yooo uses functions-
Relationship (-mapping or association) bfus 2_enbity sede
Relationship
4)
Book - borrow > — [tier ]
sa Ce) GD ED Gee a=
Dek
we can define a relationship without
“aHributes also
LE CNN | Vid | Dex | Dek
Teacher's Signature.
. Ss Mapai i \
j ; Tas Porno loan
One +o -0ne
One te many. 4
{ “one to One [ Customer fe Borred>—s)loan
One to Many. aatmene Borrow. Jtoan ]
Many to One Customen } —— >] Loan
Hany fo Many licustmey me Berveal Eben
“Teacher's Signature Pat
$,000 _
Existential Dependence
Level of
a _xelationship bf 2. entity. sets =
[eastornesch iad fAccount ij
This_entity set_is
—S+- 4
dependent on
‘Cartomer’ entity sete 4
2) Weak Entity Set
> Cannot exist on its —
own
Teacher's Signature _—______——" | for
4 ‘Total partie gation From
t 2 “Tean’ Entity Sek in |
Tdentifying > Relionshipe |
(( Relalionship _) _‘Tadteated By TER diagram
ie Loan : oo. ai |
—T + |
| | Los | fayment: Amount | Ale
| Date No |
|
Primary. Key for weak Enlity set will be combination of
painary Kew, of strong entity Set + any atti ef
weak enh eee
Customex : —Daiy loanNor cannot be prim any Hog
dete + Loan Noy beaue tt is a weak entity cof
A dependen dent on. sheng
ae
| Seok Borrow [bse
when beth ave strong entily sobs there mau be parbial
os well ay total participation»
Teacher's Signature a gs .
Employee u Works :fer, - =: Department]
as a
4
q
a
ciel
2 2 Strong Entity eds ; : >
\ Partial Parletpation —»—\""”_ Depariment “9 DME
Tot every Empleyee hay a. department» a
Shot every Department has Employees Wwerking tn ifs
DQ Meany Smployees can work. in
nae : 4
Employ
[emp | Name | Ade_| ” DNo
‘DNe & foregn Key of saplaqee Tater
which 1 Teferencing th privy rape
| _Ferelgn Key _ Dept:-tnble*
Defined bee _2._enity ack (may be sams _or_different)
+ ov tables. .
Constraints
u.
H(2) There should be Same domain _{ Integer)
of foreign Hea Sptimery. tage
Teacher's Signature —____— 20 x Lko +
oy yang 5
pz ee
ay We Al tte i
v
Either this value can be NULL
a.
| constratot i
(—
ov this should exist in Dum as wells
tis fi
We cannot have Nott value -for primary Key but we
|| Setishying OD 2 @, wesay thet Refenential
|| Satisfying O 4 @, we. say that Refenential Integaify. —___—
ran have NULL value for foreign Key»
Teacher's Signature Retatonak- Mosel —{CBarrealiy-conmts of —Retatians) — a
VY
Tables
Relations
Diffevent-
Relationship \
: Dean Acade
1 (student, Course, facut
: ‘Student (Batch, Basch Sem, Ye
Relatien >
Subset of the cantesian product of 2 sels constitutes
a velahion =
Az 41, 2,33
B= fabs
AxB= £ Ciralh, a), (3,4), (1/0), (2, 9, BOS
Rf treater}
= Order is important = (ai) # (tea)
ahs 1 table called os gq >telation 2 |
‘Teacher's Signature DNo
x,
2 & s&
22 c 6
L.
<20,A,5>
—£mpIid x Name x DNo ~
This a subset of the CHoss—product of
underlying attributes e
0
TH isan ordered tupple in. each vow >
2 ‘pple ea
Book L “Berson
I /\
ee Sod (Bee) ~ Cfleme Cura 1
Re
lational_Mode}
4 Relations
Book (_ZS8N. + Name, Authox)
Borrow (Ud. 15GN., Do, Dek)
i Usen(VId » Name) _
ape ISN, Name, Author, Old, Dax, CK}
Fall participation from Book Side +
Teacher's Signature Tig company —i organized. into__depantments + Each
depantment- eae mame, 70+ and an emple yee
who ne el ay tee
Stont date of hie cept: managen- 9 ee may have.
everal Locations «
Ench__depasttment:. nis damien af payed ach
Prinj ect has unique name./numbex and is ———___
Docated. sini ocation » i
We store each employee.’ SSN. address, gender and
birth date » Each_emplayes. ‘works: fer one. _depasrtment —_
but may. work om __sevenal projects +
we keep track ef the. number of hours pex week that
cop ee_sumnely tanta pees
b
we also Keep track of the dived supewiser gf _each __
_empleyee> -
fach_employee may have. a numben of‘ dependents —
fox each depandant , we Keep track of their mame
MWe
gender} ‘DOB _& _2elationship i» the employee =
weate the ER model «
ER Model
ba ranjar components
=> Enfi
Relation b/w _ entities
Teacher's Signature. Date
Page :___—
Teacher's Signature. This_ adie Pe >
: cannot—have—
. . @ Value which
doesn’ & exist
H 7 Fa BNom- oy
CRefevential Integrity 3
?- Constraint )
jag Cardinals
aren aX Jpn £8 :
sHcipahien Je od :
Relational Model
! | sets.
=) Relationship is “associatim b/w" 2 er move entity
ee
L> Relation -- is table, in simple words~ (Employee )
“Sheno i :
PFA Primary Key ¥o Yelahenal Model
i ¢ es
We tan _hevecaly | afivibuite Value ina given cell.
ef a table
v
Employee °
‘| SSM [items ees boa | : _ ‘
ept
DNum DName {Dr0e 3
Not —torvec
a
{Hyd Ban, Lucknow $
Teacher's Signature. Dept-
of strong entity of =
| \ Lug Num | _DNam marsn ster | ae
oe
Pak ; Dependent :
Lo pram | pwame | Poe [ovum] | S6N | Name Cenden [ Retahensip |
| DNum | |
: “Ty
: Sined i s cannot exi:
= Et will have the primary key.
|
Discriminant attribute f weak Entity Set is like
primary Key of weak Entity seb
5 | wonton Works For
\ - Pro | n_| 4s | ssw] Nem |
Hiso Corre
is Qenevall a Acpayate Yelation
Teacher's Signature High Lah F ale
Mathe matical
[tonsa Sval Sal Sanession
—irmestin |
Quay
Opa ‘Database Execadien
z Plan | Relational Algebra.
Relational Calculus.
fundomental_O, perations
Select
Project
Castesian product
Rename.
Set Difference,
ee Unien
le “oo |p te
J] Select
Hee
oo (Relation) = where
Select & fiom Employee: where income > 25,000
| all the attributes
Hori: tio lation into two +
iz Employee,
Towane gp ——Lelation)
ar, (Employee)
- (Relation)
“enpTa
tonpit, Name.
TT
: Contesian Product
| Ree
oo Pe
EmpId "<< income >30000 >
clect EmpId from Employee, where income > 30,000.
(Employee)
Rix Ro
——
RA | RE l kc &D
| a b 1 2
| a b 3 4
ej 4 ! e
i A a 3 4
| 7
7
> Cartesian product
v
Natural Join
Left Outet
Right Oudere
Teacher's Signature ———— a
Ro Ja lo jale|y
>| Union
book (AccNer, YPub, Title)
Usex ( CordNer, Name, Aad)
Supplier (Sname, Sadde)
borrew/ AccNor, CundNer, Dol, Do)
Ace
ak
Aece}lsien Now
Supphy ( AcaNa, Sname , ty .
Q-=9 find out all_the
books that one cithen
issued, or have been
Supplied fay | Supplier +
Teacher's Signature. 7; To onion set ci ference
aes oie
cl xelations, should have the cam no: of.
attributes 2 conrespon cing domain should’ also be
Same-
beok UO sex X
Find cut all the books thet ane available in the tibrary «
Taye (Peek) = Tres’ becrsy)
“| fects _| [Aes / [reste
I Zz 3
= a :
3 s
5 i
5
ce
Teacher's Signature Page fl
ES feat) 9 Em
Tapp losarew)) Tag lt I) enpry
Rename.
Pp (ald eet)
few Rel *
7. ( Book)
T Book New
fatal, Tei
fo isy
Derived Operator
=> Derived from 2 fundamental operators
Ss
C D
3 Io
6 22
Teacher's Signature Natural Join 4 RE Ss
: R BIS
~ a
: A B ci| Dp
t Ee 3 v2
4 _[s c RR
2 TT o (axs)
CRAFERE SD) = RO = SES
ok
+ :
I = (Rx Ss)
Ros = TT RA, RB, 6-C,5:D
Teerron etree ss
7 =. im
ipa cf 8: [ Cc D if
1 > 3 & 3 12
| 4 s 6 s| 6 22
| t a 2 2;
} l a) ; —l
a s All the attyibutes
I A 8 e| D> which ave _common
they values must be
‘ 2 { 3] fa Some in the
: [ a Takara! Join ©
Teacher's Signature Gin
TRL (wer ba lborba)
Berew-Ale No
Ge Neos)
vatlge Worname Vijay” >
y :
Ke (usex Da borrow) ) Ba beck )
Heitde \S Name ="vijog!
ey it ik
saa Name = ipaq” User DA borrow “SA book ==. / 4] 20x3I| =
wi Csi stqute. safe ating )
Boat (bid .-hna
find the names of sail have. steserved boat id 103 +
we A phy syoys Bette
(TF we (Sailox Da Reserve }
Sailorsname Reseweebid = 103
OR
Bettey|
a. (/ (Resenve)) Da Sailor _)
lore Sname \\ Rsewebid=103
7 >
find the mames of sailors who have steservedl. atleast
one boat +
Zz Tr (Sailoy bat Reserve)
Salers Bname
Wyname ("sailor -aid = Salley x Resewe)
Resewe: sid
"W = -AND operator
Peerve toy =r
Teacher's Signature _ : : : q
“ar é (Reserve ba Boat) J Pd Sailor =
i cael { | Beat-color = Red" 3
+ son US >
xX. Baatcoloy = “Green” 5
I 24) Union operator is used bj 2 otelations calwasys,
ae | [el Pesewe be boat )JU (e,
| naire \ ON _
|
1 oR
T , (Book) }
rp (Ca PY a 2) Rae
S| find the names“of sailors who have, mesered a >
ped boat and a geen boat~
tl TT ual re (Bot) Ba Reeve )
Teacher's Signature. a ay eat LBoat) bt Resene})
ira (ROR) PA Sailor
Ahame
