DBMS unit-3.pdf

Database Normalization

1. Database Normalization
Prof. K ADISESHA
(Ph. D)

2. Introduction
Normalization
Database Anomaly
Functional Dependency
Normal Forms
2
Database Normalization
Prof. K. Adisesha

3. Introduction
Prof. K. Adisesha (Ph. D)
3
DatabaseDesignStrategies:
Databasedesigncanbedefinedasacollectionoftasksorprocessesthat
enhancethedesigning,development,implementation,andmaintenanceof
enterprisedatamanagementsystem.
➢Designingaproperdatabasereducesthemaintenancecosttherebyimprovingdata
consistencyandthecost-effectivemeasuresaregreatlyinfluencedintermsofdisk
storagespace.
➢Therearetwoapproachesfordevelopinganydatabase:
❖Thetop-downmethod
❖Thebottom-upmethod

4. Introduction
Prof. K. Adisesha (Ph. D)
4
Top–downdesignmethod:
Thetop-downdesignmethodstartsfromthegeneralandmovestothe
specific.
➢Itstartwithageneralideaofwhatisneededforthesystemandthenworkyourway
downtothemorespecificdetailsofhowthesystemwillinteract.
➢Thisprocessinvolvestheidentificationofdifferent
entitytypesandthedefinitionofeachentity’s
attributes.

5. Introduction
Prof. K. Adisesha (Ph. D)
5
Bottom–updesignmethod:
Thebottom-upapproachbeginswiththespecificdetailsandmovesupto
thegeneral.
➢Thisisdonebyfirstidentifyingthedataelements(items)andthengroupingthem
togetherindatasets.
➢Inotherwords,thismethodfirstidentifiestheattributes,andthengroupsthemtoform
entities.

6. Introduction
Prof. K. Adisesha (Ph. D)
6
Normalizationistheprocessoforganizingthedataandtheattributesofa
database.
➢Itisperformedtoreducethedataredundancyinadatabaseandtoensure
thatdataisstoredlogically.
➢Dataredundancymeanshavingthesamedatabutatmultipleplaces.
➢Itisnecessarytoremovedataredundancybecauseitcausesanomaliesin
adatabasewhichmakesitveryhardforadatabaseadministratorto
maintainit.

7. Introduction
Prof. K. Adisesha (Ph. D)
7
Normalization:
Normalizationisastepbystepprocessofremovingthedifferentkindsofredundancy
andanomalyonestepatatimefromthedatabase.
➢E.FCodddevelopedfortherelationdatamodelin1970.
➢Normalizationrulesaredividedintofollowingnormalform:

8. Normalization
Prof. K. Adisesha (Ph. D)
8
Normalizationistheprocessoforganizingthedataandtheattributesofa
database.Themainreasonfornormalizingtherelationsisremovingthese
anomalies.
➢Normalizationistheprocessoforganizingthedatainthedatabase.
➢Normalizationisusedtominimizetheredundancyfromarelationorsetofrelations.It
isalsousedtoeliminateundesirablecharacteristicslikeInsertion,Update,and
DeletionAnomalies.
➢Normalizationdividesthelargertableintosmallerandlinksthemusingrelationships.
➢Thenormalformisusedtoreduceredundancyfromthedatabasetable.

9. Normalization
Prof. K. Adisesha (Ph. D)
9
Themainreasonfornormalizingtherelationsisremovingthese
anomalies.
➢AdvantagesofNormalization
❖Normalizationhelpstominimizedataredundancy.
❖Greateroveralldatabaseorganization.
❖Dataconsistencywithinthedatabase.
❖Muchmoreflexibledatabasedesign.
❖Enforcestheconceptofrelationalintegrity.
➢DisadvantagesofNormalization
❖Itisverytime-consuminganddifficulttonormalizerelationsofahigherdegree.
❖Carelessdecompositionmayleadtoabaddatabasedesign,leadingtoseriousproblems.

10. Normalization
Prof. K. Adisesha (Ph. D)
10
Need for Normalization:
Normalizationisusedtoreducedataredundancy.Itprovidesamethodtoremovethe
followinganomaliesfromthedatabaseandbringittoamoreconsistentstate:
➢Adatabaseanomalyisaflawinthedatabasethatoccursbecauseofpoorplanningand
redundancy.
❖Insertionanomalies:Thisoccurswhenwearenotabletoinsertdataintoadatabase
becausesomeattributesmaybemissingatthetimeofinsertion.
❖Updationanomalies:Thisoccurswhenthesamedataitemsarerepeatedwiththe
samevaluesandarenotlinkedtoeachother.
❖Deletionanomalies:Thisoccurswhendeletingonepartofthedatadeletestheother
necessaryinformationfromthedatabase.

11. Normalization
Prof. K. Adisesha (Ph. D)
11
DatabaseNormalForms.
TheTheoryofDataNormalizationinMySQLserverisstillbeingdevelopedfurther,
therearediscussionsevenon6thNormalForm.
➢However,inmostpracticalapplications,normalizationachievesitsbestin3rdNormal
Form.TheevolutionofNormalizationinSQLtheoriesisillustratedbelow-
❖1NF(FirstNormalForm)
❖2NF(SecondNormalForm)
❖3NF(ThirdNormalForm)
❖BCNF(Boyce-CoddNormalForm)
❖4NF(FourthNormalForm)
❖5NF(FifthNormalForm)
❖6NF(SixthNormalForm)

12. Normalization
Prof. K. Adisesha (Ph. D)
12
DatabaseNormalForms.
Tounderstandtheabove-mentionednormalforms,wefirstneedtohavean
understandingofthefunctionaldependencies.
➢Therearefourtypesofnormalformsthatareusuallyusedinrelationaldatabases
❖1NF:Arelationisin1NFifallitsattributeshaveanatomicvalue.
❖2NF:Arelationisin2NFifitisin1NFandallnon-keyattributesarefully
functionaldependentonthecandidatekey.
❖3NF:Arelationisin3NFifitisin2NFandthereisnotransitivedependency.
❖BCNF:ArelationisinBCNFifitisin3NFandforeveryFunctionalDependency,
LHSisthesuperkey.

13. Functional dependency
Prof. K. Adisesha (Ph. D)
13
Functionaldependency.
Thefunctionaldependencyisarelationshipthatexistsbetweentwoattributes.It
typicallyexistsbetweentheprimarykeyandnon-keyattributewithinatable.
➢TheleftsideofFDisknownasadeterminant,therightsideoftheproductionisknown
asadependent.
➢ItisdenotedbyX->Y,whereXiscalledadeterminantandYiscalleddependent.
➢Example:
❖Assumewehaveanemployeetablewithattributes:Emp_Id,Emp_Name,Emp_Address.
❖Functionaldependencycanbewrittenas:Emp_Id→Emp_Name
❖WecansaythatEmp_NameisfunctionallydependentonEmp_Id.
❖HereEmp_IdattributecanuniquelyidentifytheEmp_Nameattributeofemployeetable
becauseifweknowtheEmp_Id,wecantellthatemployeenameassociatedwithit.

14. Functional dependency
Prof. K. Adisesha (Ph. D)
14
Functionaldependency.
Thefunctionaldependencyisarelationshipthatexistsbetweentwoattributes.It
typicallyexistsbetweentheprimarykeyandnon-keyattributewithinatable.
➢TypesofFunctionaldependency
❖Trivialfunctionaldependency
❖Non-Trivialfunctionaldependency
❖Multivaluedfunctionaldependency
❖Transitivefunctionaldependency.
roll_noname age
42 abc 17
43 pqr 18
44 xyz 18
➢Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant
set {roll_no, name}
➢roll_no→ name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no.
➢If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional
dependency.
➢If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive

15. Functional dependency
Prof. K. Adisesha (Ph. D)
15
Functionaldependency.
Thefunctionaldependencyisarelationshipthatexistsbetweentwoattributes.It
typicallyexistsbetweentheprimarykeyandnon-keyattributewithinatable.
➢TypesofFunctionaldependency
❖Trivialfunctionaldependency
❖A→BhastrivialfunctionaldependencyifBisasubsetofA.
❖Thefollowingdependenciesarealsotriviallike:A→A,B→B
❖Non-trivialfunctionaldependency
❖A→Bhasanon-trivialfunctionaldependencyifBisnotasubsetofA.
❖WhenAintersectionBisNULL,thenA→Biscalledascompletenon-trivial.
❖InMultivaluedfunctionaldependency:entitiesofthedependentsetarenotdependentoneach
other.i.e.Ifa→{b,c}andthereexistsnofunctionaldependencybetweenbandc,thenitis
calledamultivaluedfunctionaldependency.

16. Inference Rule (IR):
Prof. K. Adisesha (Ph. D)
16
InferenceRule(IR):.
TheFunctionaldependencyhas6typesofinferencerule:.
➢ReflexiveRule(IR1):Inthereflexiverule,ifYisasubsetofX,thenXdeterminesY.
IfX⊇YthenX→Y
➢AugmentationRule(IR2):Theaugmentationisalsocalledasapartialdependency.Inaugmentation,ifX
determinesY,thenXZdeterminesYZforanyZ.IfX→YthenXZ→YZ
➢TransitiveRule(IR3):Inthetransitiverule,ifXdeterminesYandYdetermineZ,thenXmustalsodetermineZ.
IfX→YandY→ZthenX→Z
➢UnionRule(IR4):Unionrulesays,ifXdeterminesYandXdeterminesZ,thenXmustalsodetermineYandZ.
IfX→YandX→ZthenX→YZ
➢DecompositionRule(IR5):Decompositionruleisalsoknownasprojectrule.ThisRulesays,ifXdeterminesYand
Z,thenXdeterminesYandXdeterminesZseparately.
IfX→YZthenX→YandX→Z
➢PseudotransitiveRule(IR6):InPseudotransitiveRule,ifXdeterminesYandYZdeterminesW,thenXZ
determinesW. IfX→YandYZ→WthenXZ→W

17. Normal Form :
Prof. K. Adisesha (Ph. D)
17
FirstNormalForm(1NF):
1NF:Arelationisin1NFifallitsattributeshaveanatomicvalue.
➢Arelationwillbe1NFifitcontainsanatomicvalue.
➢Itstatesthatanattributeofatablecannotholdmultiplevalues.Itmustholdonlysingle-
valuedattribute.
➢Firstnormalformdisallowsthemulti-valuedattribute,compositeattribute,andtheir
combinations.
S_IDS_NAME S_PHONE S_COURSE
101SUNNY 72XXXX9064
738XXXX238
BCA
102PRAJWAL 857XXXX832 BBA
S_ID S_NAME S_PHONE S_COURSE
101 SUNNY 72XXXX9064 BCA
101 SUNNY 738XXXX238 BCA
102 PRAJWAL 857XXXX832 BBA

18. Normal Form :
Prof. K. Adisesha (Ph. D)
18
SecondNormalForm(2NF):
2NF:ThefirstconditionforthetabletobeinSecondNormalFormisthatthetablehasto
beinFirstNormalForm.Thetableshouldnotpossesspartialdependency.
➢Inthe2NF,relationalmustbein1NF.
➢Inthesecondnormalform,allnon-keyattributesarefullyfunctionaldependentonthe
primarykey
S_IDS_NAME C_ID
101 SUNNY C01
102 SHAILU C01
103 PRAJWAL CO2
S_IDS_NAME S_COURSE
101 SUNNY BCA
102 SHAILU BCA
103 PRAJWAL BBA
C_IDS_COURSE
C01 BCA
C01 BCA
CO2 BBA

19. Normal Form :
Prof. K. Adisesha (Ph. D)
19
ThirdNormalForm(3NF):
3NF:ThethirdNormalFormensuresthereductionofdataduplication.Itisalsousedto
achievedataintegrity.
➢ThethirdNormalFormensuresthereductionofdataduplication.Itisalsousedto
achievedataintegrity.
➢Arelationwillbein3NFifitisin2NFandnotcontainanytransitivepartialdependency.
➢3NFisusedtoreducethedataduplication.Itisalsousedtoachievethedataintegrity.

20. Normal Form :
Prof. K. Adisesha (Ph. D)
20
ThirdNormalForm(3NF):
3NF:Arelationisin3NFifitisin2NFandthereisnotransitivedependency.
➢ThethirdNormalFormensuresthereductionofdataduplication.Itisalsousedto
achievedataintegrity.
➢Inthestudenttable,stu_iddeterminessubid,andsubiddeterminessub.Therefore,stu_id
determinessubviasubid.Thisimpliesthatthetablepossessesatransitivefunctional
dependency,anditdoesnotfulfillthethirdnormalformcriteria..

21. Normal Form
Prof. K. Adisesha (Ph. D)
21
BoyceCoddnormalform(BCNF):
BCNF:ArelationisinBCNFifitisin3NFandforeveryFunctionalDependency,LHSis
thesuperkey.
➢BCNFistheadvanceversionof3NF.Itisstricterthan3NF.
➢AtableisinBCNFifeveryfunctionaldependencyX→Y,Xisthesuperkeyofthetable.
➢ForBCNF,thetableshouldbein3NF,andforeveryFD,LHSissuperkey.
➢TotransformthetableintotheBCNF,youwilldividethetableintotwoparts.Onetablewillhold
stuidwhichalreadyexistsandthesecondtablewillholdanewlycreatedcolumnprofid.

22. STU_ID COURSE HOBBY
21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
Normal Form
Prof. K. Adisesha (Ph. D)
22
Fourthnormalform(4NF):
4NF:Arelationwillbein4NFifitisinBoyceCoddnormalformandhasnomulti-valued
dependency.
➢ForadependencyA→B,ifforasinglevalueofA,multiplevaluesofBexists,thentherelation
willbeamulti-valueddependency.
➢ThegivenSTUDENTtableisin3NF,buttheCOURSEandHOBBYaretwoindependententity.Hence,
thereisnorelationshipbetweenCOURSEandHOBBY.
➢SotomaketheStudenttableinto4NF,wecandecomposeitintotwotablesCourse&Hobbytables:
STU_ID COURSE
21 Computer
21 Math
34 Chemistry
74 Biology
STU_ID HOBBY
21 Dancing
21 Singing
34 Dancing
74 Cricket

23. Normal Form
Prof. K. Adisesha (Ph. D)
23
Fifthnormalform(5NF):
4NF:Arelationisin5NFifitisin4NFandnotcontainsanyjoindependencyandjoining
shouldbelossless.
➢5NFissatisfiedwhenallthetablesarebrokenintoasmanytablesaspossibleinordertoavoid
redundancy.
➢5NFisalsoknownasProject-joinnormalform(PJ/NF).
➢SotomaketheSPCtableinto4NF,wecandecomposeitintothreetablesSP,SC&PCtables:

24. Normal Form
Prof. K. Adisesha (Ph. D)
24
Normalization:
Normalizationisastepbystepprocessofremovingthedifferentkindsofredundancy
andanomalyonestepatatimefromthedatabase.

25. Discussion
Prof. K. Adisesha (Ph. D)
25
Queries ?
Prof. K. Adisesha
9449081542