fofHkUu izdkj ds Entities o muds Attributes dks dbZ rjhdksa ls fdlh Relation ds :i esa
Represent fd;k tk ldrk gSA bl v/;k; esa ge Normalization ds Process dks le>saxsA tc ge bl Process ds vk/kkj ij fofHkUu Relations Create djrs gSa] rc ,d [kjkc
Database Design ls iSnk gksus okyh fofHkUu izdkj dh leL;k,a Avoid gks tkrh gSaA
Database ds Normalization ds nks rjhds izpfyr gSaA igys rjhds esa ,d ER Diagram ds vk/kkj ij Normalization fd;k tkrk gSA bl rjhds esa ;fn ER Diagram dks Correctly Draw
fd;k x;k gS] rks ge dqN Simple Rules dks Follow djrs gq, ml ER Diagram dks ,sls
Relations esa Translate dj ldrs gSa] tks T;knkrj Relational Design Problems dks Avoid
gks tkrk gSA
bl Normalization Process dh leL;k ;s gS fd bl rjhds ds vk/kkj ij tks Database
Design curk gS] og Design lgh gS ;k ugha] bl ckr dks fuf”pr djus dk dksbZ rjhdk ugha gksrk gSA nwljs rjhds esa ge fofHkUu Relations Create djus ds fy, Theoretical Concept dks
Use djrs gSaA ;s rjhdk igys rjhds dh rqyuk esa FkksMk vf/kd tfVy gS] ysfdu blls cuus okyk
Design ,d Better Design gksrk gSA
Practically bu nksuksa rjhdksa ds Combination dks Use djds] T;knk vklkuh ls ,d vPNk
Design Create dj ldrs gSaA lcls igys ge ER Diagram Create djrs gSa vkSj bldk iz;ksx djds Relations Create djrs gSaA mlds ckn nwljs rjhds ds Theoretical Rules dks mu
Relations ij Apply djds Design dks Check djrs gSaA
Translating an ER Diagram into Relations
,d ,slk ER Diagram, ftlds lHkh Many To Many Relationships dks Composite
Entities dk iz;ksx djds One To Many Relationships esa Convert dj fy;k x;k gks] rks
Directly Database Relations esa Translate dj ldrs gSaA ,slk djus ds fy, gesa fuEu Steps
dks Follow djus gksrs gSa%
1 gj Entity ds fy, ,d Table Create djrs gSaA
2 gj og Entity tks fdlh ,d ;k ,d ls T;knk Relationships ds dsoy “One” End dh rjQ gks vkSj “Many” End dh rjQ uk gks] ,sls Entity dh Table esa dsoy ,d
Single-Column Primary Key dks Define djuk gksrk gSA
3 gj og Entity tks fdlh ,d ;k ,d ls vf/kd Relationship ds “Many” End dh rjQ gks] ,sls Entity dh Table esa mlds Parent Table, tks fd “One” End dh rjQ gksrk gS] dh Primary Key dks viuh Table esa Foreign Key dh rjg Use djuk pkfg,A
DBMS-RDBMS in HinDi
4 ;fn ,d Entity tks fdlh ,d ;k ,d ls T;knk Relationships ds “Many” End dh rjQ gks vkSj mlesa dksbZ Natural Primary Key gks] tSls fd Invoice Number ;k
Order Number, rks bl Single-Column Primary Key dks Use djuk pkfg,A ysfdu
;fn ,slk uk gks] rks bl Table dh Parent Table ds Primary Key dks fdlh vU;
Column ;k Columns ds Group ds lkFk Composite Primary Key ds :i esa Use djuk pkfg,A
bu Guidelines dks Follow djds ge “Music Store” Database ds Design dks fuEukuqlkj
Theoretically Represent dj ldrs gSa%
Customer (CustID, FName, LName, Street, City, State, Pincode, Telephone, CreditCardNo, CardExpiryDate)
Item (ItemID, Title, DistID, RetailPrice, ReleaseDate, Genre) Order (OrderID, CustID, OrderDate, OrderFilled)
OrderLines (OrderID, ItemID, Quantity, DiscountApplied, SellingPrice, LineCost, Shipped)
Distributor (DistID, Name, Street, City, State, Pincode, Telephone, ContactPerson, ContactPersonExt)
Actor (ActorID, Name)
Performance (ActorID, ItemID, Role) Producer (ProducerID, Studio) Production (ProductionID, ItemID)
bu Relations dks FkksMk lk Modify fd;k x;k gS] ysfdu bu Modifications dk ER Diagram
;k Database ds dke djus ds rjhds ij dksbZ vUrj ugha iMk gSA
Normal Forms
os Theoretical Rules ftudk fdlh Hkh Relational Database Design ds Compatible gksuk t:jh gksrk gS] Normal Forms dgykrs gSaA gj Normal Forms dqN Strict Rules dk lewg gksrk gSA Theoretically Database ftrus Higher Form esa gksrk gS] Relations ds chp dk
Design mruk gh vPNk gksrk gSA
DBMS-RDBMS in HinDi
tSlkfd ge fiNys fp= esa ns[kdj le> ldrs gSa] fdlh Hkh Design dh 6 Normal Forms gks ldrh gSaA ;s fp= bl ckr dks n”kkZrk gS fd ;fn dksbZ Design fdlh Higher Form esa gS rks og
Design mldh lHkh Inner Normal Form esa t:j gksrk gSA ;kuh ;fn dksbZ Design Inner Normal Form esa ugha gks rks mls Higher Normal Form esa Define ugha fd;k tk ldrk gSA
T;knkrj Cases esa ;fn ge gekjs Relations ;k Tables Third Normal Form (3NF) rd Hkh
Normalize dj ysrs gSa] rks ge [kjkc Database Design ds T;knkrj Problems dks Solve
dj pqds gksrs gSaA ;kuh fdlh Database dh lHkh Tables dks 3rd Normal Form rd
Normalize dj ysus ij ml Database dh Design lEcaf/kr T;knkrj ijs”kkfu;ka lekIr gks tkrh gSaA
Boyce-Codd (BCNF) o 4th Normal Form dks fo”ks’k Situations dks Handle djus ds fy, dHkh&dHkkj gh Database ij Apply fd;k tkrk gSA gkykafd bu nksuksa Normalization Processes dks Hkh Conceptually le>uk ljy gksrk gS vkSj t:jr iMus ij bUgsa Practice esa Hkh fy;k tk ldrk gSA
Fifth Normal Form ds fu;e cgqr gh Complex gksrs gSa vkSj bUgsa Practically Use djuk dkQh eqf”dy gksrk gSA dksbZ Relation 5th Normal Form esa gS ;k ugha] bl ckr dks Verify
djuk dkQh eqf”dy gksrk gSA T;knkrj Database Designers 5th Normal Form rd fdlh Hkh
Relation dks Normalize ugha djrs gSaA ;fn mUgsa yxrk gS fd mudk Relation 3rd Normal
Form ;k t:jr ds vk/kkj ij 4th Normal Form esa gS] rks os ;s eku ysrs gSa fd mudk Design Generally Problem Free gSA
DBMS-RDBMS in HinDi
fp= esa n”kkZ, x, 6 Normal Forms ds vykok ,d vkSj Normal Form gksrk gS] ftls
Domain/Key Normal Form dgk tkrk gSA ;s ,d iwjh rjg ls Theoretical Concept gksrk gS vkSj vkt ds le; esa bl Normal Form dks fdlh Hkh Practical Design esa Use ugha fd;k tkrk gSA
First Normal Form
;fn fdlh Table ds lkjs Data ,d 2-Dimensional Table ds :i esa gSa vkSj muesa ls fdlh Hkh
Data ds Group dk Repetition ugha gks jgk gS] rks ml Table dks 1st Normal Form esa ekuk ldrs gSA
1st Normal Form dks le>us dk eq[; vk/kkj ;s gS fd ge Data ds Repeating Group dh izd`fr dks le>saA fdlh Relation dk ,d ,slk Attribute, ftlesa ,d ls T;knk Data Store gks ldrs gksa] dks Repeating Group Data dgk tkrk gSA tc ge fdlh Repeating Group Data dks ER Diagram esa Represent djuk pkgrs gSa] rc ge ml Repeating Group Data
dks Multi-Valued Attribute ds :i esa Represent djrs gSaA
mnkgj.k ds fy, ekuyks fd ge fdlh Employee ds Relation ds lkFk izfØ;k dj jgs gSa vkSj gesa fdlh Employee ds cPpksa ds uke o Birth Date dks Employee ds Relation esa Data ds :i esa Store djuk gSA
vc pwafd ,d Employee ds ,d ls T;knk cPps Hkh gks ldrs gSa] blfy, ,d gh Employee dks
Represent djus okys ,d gh Row ds Child Name Filed o Child Birth Date Field esa ,d ls T;knk ekuksa dks Store djus dh t:jr iM ldrh gS] tcfd fdlh Hkh Relational Database esa fdlh ,d Row ds ,d Field esa dsoy ,d gh eku dks Store fd;k tk ldrk gSA
bl fLFkfr esa Child dk uke o Birth Date Employee ds Record esa ,d ls T;knk ckj
Repeat gks ldrs gSa] blfy, bu nksuksa Data dks Employee ds Relation ds fy, Repeating
Group of Data dgk tk ldrk gSA bl ckr dks ge ,d lkj.kh }kjk fuEukuqlkj n”kkZ ldrs gSa%
EmpID FName LName DOB ChildName ChildBirthDate 001 Rahul Sharma 10/12/1982 Rohit Sharma 15/12/2006
Mohit Sharma 20/10/2008
bl Table esa ge ns[k ldrs gSa fd Rahul uke ds ,d Employee ds nks cPps Rohit o Mohit
gSa] ysfdu ge nksuksa cPpksa ds Data dks Employee ds Relation esa Store ugha dj ldrs gSa]
D;ksafd ;fn ge ,slk djus dh dksf”k”k djrs gSa] rks gesa ,d gh Attribute Column esa ,d ls
DBMS-RDBMS in HinDi
T;knk ukeksa o Birthdates dks Store djuk gksxk] tks fd ,d Relational Database esa lEHko ugha gSA
fdlh Relational Database esa Repeating Groups dks Allow uk djus dk ,d vPNk dkj.k Hkh gSA bl dkj.k dks le>us ds fy, ge ,d mnkgj.k ns[krs gSa] ftlesa ,d Table esa fuEukuqlkj dqN Employees o muds cPpksa ds uke Stored gSa%
bl Table esa ge ns[k ldrs gSa fd ,d gh Single Row ds Children Name Field o
Children Birthdates Field nksuksa gh Fields esa ,d ls T;knk eku Stored gSaA ;s O;oLFkk nks cMh Problems Create djrh gS%
1 bl Table esa ,slk dksbZ rjhdk ugha gS] ftlls ;s tkuk tk lds fd fdl Child dk
Birth Date D;k gSA bl O;oLFkk dks Use djus ij gesa gesa”kk Children ds uke o
Birth Date nksuksa dks leku Øe esa Store djuk t:jh gksrk gSA fdlh Table esa
Children Name Field esa ftl cPps dk uke igys fy[kk x;k gS] gesa mlh cPps dk
Birth Date igys fy[kuk gksrk gS vkSj ,slk dksbZ fuf”pr rjhdk ugha gksrk gS] ftlls ;s
Verify gks lds fd geus ftl cPps dk uke igys fy[kk gS] ge mlh cPps dh Date Of
Birth Hkh igys gh fy[k jgs gSa ;k ughaA
2 tc ge fdlh Table esa Multi-Valued Data Store djrs gSa] rc DBMS fdlh Data
dks Search djus ds fy, ml Multi-Valued Column dks Extract djrk gS vkSj ml
Column ij Sequential Processing dks Apply djds Required Data dks izkIr djrk gS] tks fd lcls /kheh Searching Process gksrh gSA
bu nksuksa leL;kvksa dk ,d gh Solution gS fd fdlh Hkh Single Column esa Multiple Values
dks Store uk fd;k tk, ;kuh Repeating Group of Data dks fdlh Hkh Relation esa Avoid fd;k tk,A Repeating Groups dh leL;k ls cpus vkSj Relation dks First Normal Form esa ykus ds nks rjhds gSaA igyk rjhdk ,d lgh rjhdk gS tcfd nwljk rjhdk ,d xyr rjhdk gSA ge igys xyr rjhds dks ns[ksaxs rkfd ge ;s le> ldsa fd gesa ,d Database esa D;k ugha djuk pkfg,A bl xyr rjhds dks ge fuEu lkj.kh }kjk le> ldrs gSa] ftlesa fdlh
DBMS-RDBMS in HinDi
Repeating Group ds Data dh fofHkUu Values dks Handle djus ds fy, Relation esa
Columns dh la[;k dks Increase dj fy;k tkrk gS%
bl Example esa fdlh Employee ds rhu Childs ds uke o Birth Dates dks Store fd;k tk ldrk gSA ;s Table First Normal Form ds Criteria ls esy ugha dj jgk gSA gkykafd bl
Table esa Data ds Repeating Groups ugha gSa vkSj gj Child dh Birth Date dks vyx
Store fd;k x;k gSA fQj Hkh bl Design esa dbZ Problems gSa] tks fuEukuqlkj gSa%
1 bl Relation esa ge flQZ rhu Child ds gh Name o Birth Date dks Store dj ldrs gSaA blfy, ;fn ge Jane Smith ds pkSFks cPps dk uke o Date Of Birth Store
djuk pkgsa] rks bl Relation esa ml Child ds fy, dksbZ txg ugha gSA ;fn ge pkSFks cPps dk Hkh Data blh Relation esa Store djuk pkgsa] rks ;k rks gesa ,d vkSj Field Pair Create djuk gksxk ;k fQj ,d vkSj Row esa bl Data dks Store djuk gksxkA
;fn ge pkSFks cPps ds Data dks ,d vkSj Row esa Store djrs gSa] rks fQj ml cPps dks mlds Father ds EmpID ls Relate djus ds fy, gesa EmpID ds lkFk de ls de ,d Child ds uke ds Column dks Hkh feykuk gksxkA ;kuh gesa Composite Key dk iz;ksx djuk gksxkA
2 ;fn ge bl rjhds dks Use djrs gSa] rks ftu Employees ds ,d ;k nks gh Child gSa]
muds Row esa rhljs Child ds Data dh txg Empty jgsxh] ftlls Employee ds
Record }kjk Reserved Space Waste gksxkA
3 bl rjhds dks Use djus ij fdlh Particular Child dh Searching djuk dkQh eqf”dy gks tkrk gSA bl Design esa ;fn DBMS ls ;s iwNk tk,] fd “D;k bl
Relation esa Lee uke dk dksbZ Child gS ;k ugha\” rks DBMS dks ,d Query Construct djuk iMrk gS] ftlesa rhuksa Childs ds uke Included gksrs gSa] D;ksafd
DBMS ds ikl ,slk dksbZ rjhdk ugha gksrk gS] ftlls og Particular “Lee” ds gh
Column dks Identify dj ldsA
Repeating Group dks Handle djus dk lgh rjhdk ;s gS fd Repeating Group ds Data
dks Store djus ds fy, ,d vkSj Entity ;kuh Table Create fd;k tk,] tks Repeating
DBMS-RDBMS in HinDi
Group ds Multiple Records ;k Instances dks Handle dj ldsA ;fn ge gekjs bl mnkgj.k ds lUnHkZ esa ns[ksa] rks gesa Children uke dh ,d vkSj Table dks Create djuk gksxk]
ftlesa fuEukuqlkj Data Store fd, tk ldrs gSa%
Employees blfy, bl Design ls igys crkbZ xbZ lHkh izdkj dh leL;k,a lekIr gks tkrh gSaA ;kuh bl
Design ls gesa mijksDr lHkh Problems dk fuEukuqlkj Solution izkIr gks tkrk gS%
• bl Design esa ge fdlh Employee ds lHkh Childs ds Name o Birth Dates dks
DBMS-RDBMS in HinDi
Hkh Child dk uke Search djus ds fy, dsoy ,d gh Column dks /;ku esa j[kuk gksrk gSA
gkykafd First Normal Form ds Relations esa Data ds Repeating Groups ugha gksrs gSa]
ysfdu muesa vU; izdkj dh dbZ vkSj Problems gksrh gSaA First Normal Form ds Relation dh leL;kvksa dks le>us ds fy, ge Music Store Database ds Data Entry Form ls
Connected Back-End Table dks gh mnkgj.k ds :i esa ys jgs gSa] ftlesa Data Entry Form
ls Enter fd;k x;k Data tkdj Store gksrk gSA Music Store Data Entry Form ls
Connected Back-End Relation ds Notation dks ge fuEukuqlkj Represent dj ldrs gSa%
Orders (CustID, FName, LName, Street, City, State, Pincode, Telephone, OrderDate, ItemID, Title, Price, HasShipped)
bl Table esa gesa tks lcls igys dke ds :i esa Primary Key dks Determine djuk gksrk gSA bl Table esa dsoy Customer Number ls fdlh Order dks iwjh rjg ls Uniquely Identify ugha fd;k tk ldrk gS] D;ksafd gj Item ds Order ds lkFk gh CustID Repeat gksrk gSA
blh rjg ls dsoy ItemID dks Primary Key ds :i esa Use ugha fd;k tk ldrk gS] D;ksafd gj Order ds lkFk ;s Hkh Repeat gksrk gSA bl Relation esa Primary Key dk dsoy ,d gh mfpr rjhdk gks ldrk gS vkSj og rjhdk Composite Key dk gSA
bl mnkgj.k esa ge OrderID o ItemID nksuksa ds Combination dks Primary Key ds :i esa
Use dj ldrs gSaA ;fn ge OrderID o ItemID ds Combination dks Primary Key ds :i esa Use djrs gSa] rks bl Relation esa ge nks cgqr gh egRoiw.kZ dkeksa dks iwjk ugha dj ldrs gSa]
ftUgsa fuEukuqlkj crk;k x;k gS%
1 ge rc rd fdlh Customer ds Data dks Relation esa Store ugha dj ldrs gSa] tc rd fd og Customer de ls de ,d Order Place uk djs] D;ksafd fcuk ,d Order vkSj ml Order ij fLFkr ,d Item ds] gekjs ikl ,d Complete Primary Key ugha gksrh gSA
2 blh rjg ls ge rc rd fdlh Item dh Information dks Hkh Relation esa Store ugha dj ldrs gSa] tc rd fd dksbZ Customer fdlh Order }kjk ml Item dks Order uk djs] D;ksafd fcuk OrderID ds Hkh ,d Complete Primary Key ugha cu ldrkA
;s nksuksa dkj.k Insertion Anomalies gSa] tks ,d ,slh Situation dks Represent dj jgs gSa]
ftlesa ge fdlh Relation esa blfy, fdlh Data dks Insert ugha dj ldrs gSa] D;ksafd gekjs ikl ,d Complete Primary Key ugha gSA
DBMS-RDBMS in HinDi
dksbZ Hkh Relation, tks fd First Normal Form esa gksrk gS] mlesa Insertion Anomalies dh fLFkfr Common :i ls gksrh gh gSA Insertion Anomalies dh fLFkfr rc iSnk gksrh gS] tc fdlh ,d Relation esa ,d ls T;knk Entities ds Data dks Store djus dh dksf”k”k dh tkrh gSA bl Situation esa Anomaly gesa ml le; ,d Unrelated Entity tSls fd Item ds Data
dks Insert djus ds fy, etcwj djrk gS] tc ge fdlh nwljs Entity tSls fd Customer ds
Data dks Insert dj jgs gksrs gSaA
First Normal Form ds Relations ml le; Hkh leL;k,a iSnk djrs gSa] tc ge ml Relation
ls fdlh Data dks Delete djus dh dksf”k”k djrs gSaA mnkgj.k ds fy, ekuyks fd ge ml
Permanently Link VwV tk,xk] tc rd fd og nqckjk dksbZ Order ugha nsrk gSA
2 Customer us igyh gh ckj ml Item dk Order fn;k gks vkSj ml Customer ls
DBMS-RDBMS in HinDi
Orders ds Relation esa ,d vfUre Anomaly Update/Modification Anomaly Hkh gSA
Orders Relation esa gj Order ds lkFk gj Customer dh Information dks ckj&ckj Store
djus dh otg ls Music Store Database esa ,d gh Customer ds cgqr lkjs Unnecessary Duplicated Data Store gks tkrs gSaA
Information dks Represent djus okys ftu lHkh Data dks gesa”kk leku gksuk pkfg,] os leku ugha jg tkrs gSaA Data dh bl Inconsistency dh lEHkkouk ds dkj.k Modification Anomaly dh Situation iSnk gksrh gSA
Music Store Organization dk ;s Relation Second Normal Form esa vk tkrk gSA
Theoretical “kCnksa esa Second Normal Form dks fuEukuqlkj ifjHkkf’kr fd;k tk ldrk gS%
tc Relation First Normal Form esa gks vkSj lHkh Non-Key Attributes, Functionally flQZ
Primary Key ij Dependent gksA ;fn dksbZ Non-Key Attribute Functionally dsoy
Primary Key ij Depend uk gksdj fdlh Non-Key Attribute ij Depend gks] rks ml
Non-Key Attribute vkSj ml ij Depend lHkh vU; Non-Key Attributes dks ml Relation
ls gVkdj ,d u, Relation esa Define djuk pkfg, vkSj bl u, Relation esa ml Key dks
Primary Key cuk nsuk pkfg,] ftl ij vU; Attributes Depend gksaA
Functional Dependency nks Attributes ds chp dh ,d One-Way Relationship gksrh gSA tSls fdlh Relation esa fdlh Hkh le; ,d Attribute A ls fdlh nwljs Attribute B dh dsoy ,d gh Value Associated gksuh pkfg,A
mnkgj.k ds fy, ekuyks fd Orders Relation esa A ,d Customer dk Customer Number
;k CustID gSA vc gj Customer dk Customer Number ,d First Name, ,d Last
DBMS-RDBMS in HinDi
Name, ,d Street Address, ,d City, ,d State, ,d Pincode o ,d Telephone Number ls Associated gksrk gSA
gkykafd bu Attributes dh Values dks fdlh Hkh le; Change fd;k tk ldrk gS] ysfdu fdlh Hkh le; gj Attribute esa dsoy ,d gh eku gksrk gSA bl fLFkfr esa ge dg ldrs gSa fd
First Name, Last Name, Street Address, City State, Pincode o Telephone Numbers
;s lHkh Functionally Customer Number ij Dependent gSaA Attributes ds chp dh bl
Relationship dks vDlj fuEukuqlkj Represent fd;k tkrk gS%
CustID -> FName, LName, Street Address, City, State, Pincode, Telephone
vkSj bls bl rjg Read fd;k tkrk gS fd “Customer Number Determines First Name, Last Name, Street Address, City State, Pincode and Telephone Numbers”. bl
Relationship esa Customer Number ;kuh CustID dks Determinant ds :i ds tkuk tkrk gS] tks fd ,d ,slk Attribute gksrk gS] tks vU; Attributes dh Values dks Determine djrk gSA
/;ku j[ksa fd Functional Dependency dks Reverse Direction esa Represent ugha fd;k tk ldrk gSA mnkgj.k ds fy, fdlh Hkh First Name ;k Last Name dks ,d ls T;knk
Customer Numbers ds lkFk Associate fd;k tk ldrk gSA Orders Table esa fuEu
Functional Dependencies gSa%
CustID -> FName, LName, Street Address, City, State, Pincode, Telephone ItemID -> Title, Price
OrderID-> CustID, OrderDate ItemID + OrderID -> HasShipped
/;ku nsa fd Relation esa gj Entity ds fy, ,d Determinant gS vkSj Determinant ogh gS]
ftls geus Entity Identifier ds :i esa Choose fd;k gSA tc fdlh Entity esa Composite
ftls geus Entity Identifier ds :i esa Choose fd;k gSA tc fdlh Entity esa Composite