Aman Yadav

elAsSMAte
ITNS Date
Page
DatabaseMauagemnt dykfem
2ha Uui4
Relatfon model -
Teresu kouo
clata u ttovea 'n Relotioal databaes
Relabional datalsase stores olata. in thu f o e Tabl
RnMs laguage Ora t , &QL,MySQL et
Conuptuol Modl Relation modtl
ER ouaoram

RDBMS A a datab a4e mauagwut syseu litrodauusl by

E.F Codd .dBMs)

Popuar RDeMS
Orau &Rdb-from Ora
8QL sewer & Mi aecus om micnoioft;

Table Row Tuple /vetord

Column atri bute 4feld.

Relation sthema A relotion schma represeutu ttuu haw

6arelatiow iHa uh ati bult

Degre Total uo. e Couun altikoui in Tab.

Cayoliuality-Total wo o w in t Talu
Stueart 1aafRelatioAtfbut l l
PrimaTy kuy RoL o Nal Mob. No ublu vanabu

Tubu/Rous AAma)99xx Cardinaliky:3

2 Cuiraa No.o ow
Dev
domain
inbeger10-9 Degrel 31
No of oumn

Relatfon schuma, Stuoleuta ( RolNo, Nawe, Mob No.

 seuma Logical nupraeutation of data
lassM
Dhta
Page

Oralt of coumn/rouo i ineavau in deMS.

eacuL e Relation: tontains only ou valu i-e. alor
akomit.
oloweol

9wteg coshaiutu lGed agaiuut acti dtudal danaets

tegity
Aeo +o euaue acuray & consicleinuy o dta fn
relational databae.
ae uet ef n s Bhat Hu dlatabase u not berwi
ted
volate.
SAeuru hat chanqes updai, delitfon, iwertion) mao to del
by authorized uU olont result f doss ef conuisten
llconatrait may apply to eah a rfbulz 01 eotio ip
u tablu

Twkeqity couaiut
Domain courait EvtihyJuanty Ryunlal Ky
lapplies ovn alhibutz CohsraiitJ Ategny Co traiut
alti but Relatioiuhip altn
bite
bfus tabt
Doaiw tonstrasna
dins Hu olsuOu or valid uat evauu for an
dato type odomai atbrbul
t,kime,dsti et
ehuk, uot null Ca be uedt
euity iigiy cenehraia
ataat primoy vky eant be wull
uigu
Key Cestraiuá
Au vallu primary ky shoulol
A va
be uuique
epmmany k CAnt be NOLL.
 Dafo
Pagd

Relerewha dlkgily, couaiaiut

pecupieol b / w a g u

ewttd wh a foaeig key 9/erenua pnmarky o tabl.

Ruls

t dult a eord Prom a p n a n Jab if matehing

m elatiol tahle
e orel exat
valuu ii n pnmany table + teot
Cant changi' pnnmany Kay
reDTels
LL LOre has relotol
.

cant iusesty i n v a l u in foriegn ukey fielel

of retol
a l 4 t dont eut iw pmary ky of pmmary ablz.
Can utte a NULL vau ort9n.y foi
Primary tabu Foreigntallk

Dep-No Location mp-IdNam Age Dep-No

A n m a y

Mumba Aman 20

Deli 2 Yash
Noicl JI3 Dev 18 3
|3 Chivag
Chira 20
5
Not allowes
asin pimay teble
Dep No-5 wot prant
Keyin DBMS
tihat CO
A hiy u altibi oi set of atbibuü Cau

wfqully idewtijies ayre oroleYom tabu

L/o tallu.
eolAol to estakluah R ioliutify relatioakips

uper ky
Auper key is a tombinatiou a all_possTbu atbibuh Hut
LA Uigeky dentipy Hu tub
Superet ocanoiolalë kuy
MAy have adolitHonal ournbut tat ae nst requireol por

wque ioleutity
 Dote
POg

A dkaar No Emarl-1D Do
EmId Nam Dep
Amav 14S+54810 a@gmal tom
02 Chura YE6492183 6A1O c@gmail.Com 2
896-132143642 ehegmain, tom
O3 Chrag4 2
gmasl com
Dev 116Su 638tO0 da

Super ky
emp. Idy EnbIol, Acl haar Ns.} mpd, Email dsa-
Acdhaar Na- 3 Aadhar No.Eail-1dy_|Emp-ok, Nane -
Emal-IlY_Iemp Iol, Email--1dy Emp-Iol, Nau,Enal
etc
Couoioali ky
ouatbibldz srhsiho cau uuguly iouictity.ate
miwal usupr kuy.
collid mumal wuber keuy dsecause se elut a
|Caudlidatu ky -om a set of super kuy euh that
selecteo Canoliolatu kip us wit miL abbibuls
Not alloiweslthave NULL valuei.

caucuolai kuy Emp-Il 3, pEmal-sd } Adhar. No.3

imay ky
Primary ky s ot
otu caudidatt ky cheosen by
0wwquuy udtulyy thu tuble
valu ePinuary kiy canbe NuLL , aluoays wuul, tan
Uwer be changeo,
Rlation alleweot to kav owly 1 pimaxy: ky
Attmati Key Canolidali kuy - Pnmay Keu
 Dota
Page

ForuG ku
olink 2 4abls togen
ls
table t a t
attibui or i t oabibutr in One
e e s oh pnmay ky n anothey table
f o r e i g u hut o eusw uferenbial
purpode
Hedata
wig ity ef

e fau huy may have anamu oftw uan prmary kuy

may o t be wniquL
Core tgn kay

Cembosile kuy / Compoo key

than an bu kuowe
kw hat has
moe on us
A
a t&mosite kyy.

Relation Alqebra
Proceolural Qu Laguage &©rD JomaQuuy longag
wave to mtan wkat to do
Sn PQL usen

now to do
Operatort
bafc rator divedoperatoy
proechon (n)
elut Join ()D
o n lr)
cos produst (x)Fnkret Cn)
uni on (u) (x n Y=X - (x-v)
RenamL (e) oluivition - (,+)
tet diperene l-)
 Date
Page
Tab
opert'dn

rechion op.crator () Rol ND Naml Age

GwOrksw coum A 20
B 2)
A
19
Glen Rehoeve e mll wo fronn table (shuoluut)
p d u t oR o T s h u s l n t )

preuopereter lablu WA
kya pete kas 4.
RoLNo Nam (Studout)
2iection operafor g i v e owly P output, h o duplica

JLNa &tuout)
NamL
Output. A
B
H opeeration ued in hi dast.
seleubio operator
works ontup
U Rereve name of studect whne Rol No-=2

Coss poouut
B
3 DE
2
:2
R
 co mn i n table I + Coumn in f a u J = coluw
Cods pmoluf
Jalu
R R2
3 +3 = 6

Rou in tabla1 Ro w in Jabll 2 CrDS poduut

R R Jabu
2 2=y

A B C D
2 4 5
2 2 RXP2
2 2
La
be common
2 O Joiw atlkast 1 Cokumw should
- e t digerenu 2

A-B =
A nB'
To appy&et dig
No- e coumw shouko be 8a
coman e evey colum shoulo be game

RoNO| Nam Emp-No Alam

8 1 A
3 C
(Employe b
albibe is
o w

(3tudunt)
buduuct)- employec) R o l NJo NamL
2 B
3 C
 Date
Page

pes o n ouo u 8tuolunt but nst e

uuy oName e gtuoleuct Cwploye
TNae tuduut) -TNam (Epoyse)
Nam
Gulput B

MLO bperati on

a L Uull a det du[ercu

(8tudlent) U (Employea)
solum e RolNo Na
1sat-
1 A
2 B

livieio opevation
Couse
Cl eid
C
2
Se

ACxY)/BlY) vuAult valus or thot

the se siouk
be tupl (4, Y? Por every value ovelatonMB
 Phga
Al ver evey Or a
wOTd A UAed we ,
peraio

eobneve Sld o udsuaa who eno leo tn every eoute

E (sId/6clod)/c(cia) = SI

i d (Enrolad)-(TISid/ 1 S1a(ewoleod) XT eidlcoune))-(6rllad

SI Cd Sl Cl
S C Si C
S C2 S1 C2
S S2 C
S2 CJoutput
oup

S3 C
S3 C2

JoINS Oucter doi

Cro Ja Naturalo Equi Sey
Condikional Joih LeyE
Jom
Cowdidieu, FRight
8nLr Joiy Ful.
Natural Toin o
- Cross Proolut +ConoiHon.

atibull comon. in botth tha tablu.

selut E_ nam from emp,dtpt shu emp.6.No= Oept E

seluut E nae tw Emp Naturaljoiw dupt

dupli'cacy ef atri b ui kot pound.

 classmAt
pate
Page-

8tudy
AelJon S-Id C-Id Sfuu
S C 2016
C2 2016
S C2 O4f
KeletfTom Sudy aa
Ti,&tady as T2 ohuu
T . Cld<>T2. Cl
and
TSLd = T2$1d
nof equal to

Equf Join o Dept

6- No 6-Name A dolrt4s/| OeptNo Loa EN
Ama Del Deu
2
eira Cu O2 Pu 2
O3 Patna
aibutt veus 2 +imu 3 dev
OT repeatiob i Eaujo Yos
k A atepaasoi-
Nausal jo
eln 6.Na o m eup, olubt whui emp.6.No -
Depl6. N
a u o emp. Adeas - Dept Add
Locaio

olup enols on t u ON'clauur U toaiduU

Lept Outvr Join.

t gives thu matching houg and Hu uews which Be

lablldable eut dst u tie igt talt.
A t &p.no,ename d-ua,Loc por eup left outer joln de
** emp.dpt. ns rdipt deptas)

all huuqs sam au Lelt _uls joi aud xexu u

givo 'iqht lablu_ihuteal f t lable
Putk outi foin ytowiv joor URigkt eutu oe
 Dofe

SRL
slandard databaue lauguae
AhaureQuy laquage a a
wwch taa,waivolain aud eieve t
sed to
sqL sevver)
Lational datalsase MysQLDrale,
So.
DB, DynaMODB dont
u e

Moo

L dala 8tored as well as eieved iu he

RDB A QLus
fom e Relatiou

SQL QuL
Daladywiow Dala mavipulatiovm Data QuLy
Longitoge Languag
8tuture ned to manipula extratt Zata
Jsed fo dyie date i tlaho from t Relation
data bade.
Select
Ousert, Delete, pelat wiure
Tablk, Atd Column,
Gea
|DrDp olumn. +rcieval ef iotoA" G0up by, Ordar
juaertiom ,dulation, maelti cation
iomalion obus
Baaicuen

Rol No 22
Roll-No >2,
Selt fYom 8tuduntoke
Ascending Orour a9e

8elact from 8 Buotnt ORDGR BY Age A J

&a ORDER bs AGE ASC
aA

in ducmoiny droltr ORDER by AGE DESC

want o rehmeve
&tuouuls
d t value 8elect duaint aldreas tro
Stusltut,
AGGREGATE punt, Seleut CMavks) from
Xa be 8UM, AVG, N Ax, MIN
Stuolant Goup by (4ddras);
Selut Add a s Sum[Age) from
wot beolont oechre atmbuli sami adldrtss 's
&uw oil
tupl variabu ei be aolok ok.
 HAVING vs ulHERE Clause

having clause can be useo witth aggrg ati stu

Lokexclause cant be seol

Sele 3hudleut, MCoe) As ks

totel. 4roM maxks G0up by
8udenE having dal 240

Not a RULE but use WHERE PRIOR to GROUP 8y

&HAVIGNG ayter GROUP By

WHERE Clause HAVING CRause

Wwe lause u se to havinA caus e ud use
filer eeords from table Piler veords fro m JuP bad
based on specipic tondiHom.|| on Conalition
WhL Clauue lan be weL Cant be wed without GRoUP
wihout GRoUP By laue Clause
usedAed bejoie GROVP BY Clauwe used ater GRouP 8y clause
Whe clause cant condaiv | |Ca cotain aqgreqai func.
24ggatipun

INNER JOIN OUTERJOLN

etuns Hu tombineol tupl refum Htu combinntupluL fom a
b/uooT mort +tuplu spelfied tabli even join condition
fal
seol cKauwe INNERJOIN &JOINuaecd clauge lEFI OU TER JOIN , PGT

OUTER J0IN, FOLL oUTER JOIN

FASTER if tupll a mac 3LaoER
wo tommmon atibut NUL valur in Case of
hen uotuiy u refummesl Won- tommon attn buli
 EW e d to cyeali a sub lable from a table
0EATE VIEW amaM as 8
elet emp-icd, emp-numu-
Age om emplo/ uohae age >13

NDEX Jke a poinlw.

NEled Qu s uly wntten wwil Qeny

Ihdepeten -elatzzl
depandant.
doesn t delen or whidn us
Onovthtr Qut CuTent exL ted Ilouter QuLy
INN6R QVERY OUTER QUERy

ALTER UPDATE
DDL DML
usedto wpdal, Creato, eo action on data Leve
change e t utut [daBa typed
f Tablu
ADD, DELETE, MODIFV athibute||updol command us Usedl to updeh
relaton eusting Recorel
-J.

by dyault itralize updat command sei

value? o all tuple NUL 5Peuitied valuu4 in tommand to tp
ALTER TABLE (4abl Nam) Updai Hablu wume] get [olumn 1
DROP CoLuMN tolumn name;|| valu 1} l u e (condiH'o n)
 Data

DDL DML
Stands por Dala dayiniBion anquee Data nmanijpukat on Laxour
seol to ccatt_delakase sekuua||used fo add ,3etieve_or
Kax be uked to d/ Eu ome (onatautupdai thu data.
beially dlius tuatibut add or upolali tup
domtuse wHERE clauue
Cocatk Dvop,ALter - updata iert mergee
Proedusal languag Non-Proodunal longuage
ARK as to speciy ohattodo LAUL hos o dpeeuH Owy
|& alko " how to do what to do
Comuaud daiveu laxguage wfuukiou daven longuao
Look Hough Hu slat of markiu oorks timuglh malhomalal ni
Cny ugh Jeus
ikrativt2ecusive
¬al retutve calL
Ora PL/s L Eve sQL &iatumnt
for Pctioual olepeudsny tuple- tuplu x then guy=lpley