Scribd Logo
Upload

Welcome to Scribd!

  • 427 views

    1NF, 2NF, 3NF and BCNF in Database Normalization

    Uploaded by

    Adil Farooq
    A database management system file to solve the issue of the normalization . first , 2nd and third normalization form of the database system. normalization is used to sort out and save data without going to any anomaly .

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    1NF, 2NF, 3NF and BCNF in Database Normalization

    Uploaded by

    Adil Farooq
    427 views3 pages
    A database management system file to solve the issue of the normalization . first , 2nd and third normalization form of the database system. normalization is used to sort out and save data without going to any anomaly .

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    A database management system file to solve the issue of the normalization . first , 2nd and third normalization form of the database system. normalization is used to sort out and save data without going to any anomaly .

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    427 views3 pages

    1NF, 2NF, 3NF and BCNF in Database Normalization

    Uploaded by

    Adil Farooq
    A database management system file to solve the issue of the normalization . first , 2nd and third normalization form of the database system. normalization is used to sort out and save data without going to any anomaly .

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    DATABASEMANAGEMENTSYSTEM

    Share
    Share

    "WithoutSQL,websiteswillneverhaveanywaytohandleDatabasesfordata."

    Share
    (http://www.addthis.com/bookmark.php?

    US&s=linkedin&url=http%3A%2F%2Fwww

    normalization.php&title=1NF%2C%202NF%

    /54c1d0ae75cebcfc/2&frommenu=1&uid=5
    Share

    DatabaseExamples

    DatabaseDesign

    StudentDatabase

    CreateDatabase

    NormalizationofDatabase

    1.9K

    DatabaseNormalisationisatechniqueoforganizingthedatainthedatabase.Normalizationisasystematic
    DatabaseConcept

    approachofdecomposingtablestoeliminatedataredundancyandundesirablecharacteristicslikeInsertion,

    OverviewofDBMS

    UpdateandDeletionAnamolies.Itisamultistepprocessthatputsdataintotabularformbyremoving

    (overviewofdbms)

    duplicateddatafromtherelationtables.

    DatabaseArchitecture

    Normalizationisusedformainlytwopurpose,

    (architectureofdatabase)
    DatabaseModel(database

    Eliminatingreduntant(useless)data.

    model)

    Ensuringdatadependenciesmakesensei.edataislogicallystored.

    Codd'srule(coddrule)
    RDBMSConcept(rdbms
    concept)

    ProblemWithoutNormalization

    Databasekey(database
    WithoutNormalization,itbecomesdifficulttohandleandupdatethedatabase,withoutfacingdataloss.

    key)
    Normalization(database

    Insertion,UpdationandDeletionAnamoliesareveryfrequentifDatabaseisnotNormalized.Tounderstand
    theseanomaliesletustakeanexampleofStudenttable.

    normalization)
    ERDiagrams(erdiagram)
    Generalizationand
    Specialization
    (generalizationand
    specialization)
    SQLConcept
    SQLIntroduction

    S_id

    S_Name

    S_Address

    Subject_opted

    401

    Adam

    Noida

    Bio

    402

    Alex

    Panipat

    Maths

    403

    Stuart

    Jammu

    Maths

    404

    Adam

    Noida

    Physics

    (introductiontosql)
    DDLCommand
    Createquery(createquery)
    Alterquery(alterquery)
    Truncate,Dropand

    UpdationAnamoly:Toupdateaddressofastudentwhooccurstwiceormorethantwiceinatable,we
    willhavetoupdateS_Addresscolumninalltherows,elsedatawillbecomeinconsistent.

    Renamequery(truncate

    InsertionAnamoly:Supposeforanewadmission,wehaveaStudentid(S_id),nameandaddressofa

    droprenamequery)

    studentbutifstudenthasnotoptedforanysubjectsyetthenwehavetoinsertNULLthere,leadingto

    DMLCommand

    InsertionAnamoly.

    AllDMLcommand(dml
    command)

    DeletionAnamoly:If(S_id)401hasonlyonesubjectandtemporarilyhedropsit,whenwedeletethat
    row,entirestudentrecordwillbedeletedalongwithit.

    TCLCommand
    AllTCLCommand(tcl
    command)

    NormalizationRule

    DCLCommand
    Normalizationrulearedividedintofollowingnormalform.
    AllDCLCommand(dcl
    (http://www.studytonight.com/)
    command)

    1. FirstNormalForm

    WHEREclause(where
    ONLINECOURSES(http://studytonight.com/onlinecourses/)
    clause)

    2. SecondNormalForm
    3. ThirdNormalForm

    SELECTquery(select
    LIBRARY(http://studytonight.com/library/)
    STUDYROOM(http://studytonight.com/studyroom/)
    query)

    4. BCNF

    LIKEclause(likeclause)
    FLASHCARDS NEW(http://studytonight.com/flashcards/)
    ORDERBYclause(orderby

    FirstNormalForm(1NF)

    clause)
    GroupBYclause(groupby
    clause)

    LogIn(http://www.studytonight.com/login)

    WRITE
    FORUS&
    GETPAID!
    (/collaborate)

    Search

    Havingclause(having

    AsperFirstNormalForm,notwoRowsofdatamustcontainrepeatinggroupofinformationi.eeachsetof

    clause)

    columnmusthaveauniquevalue,suchthatmultiplecolumnscannotbeusedtofetchthesamerow.Each

    SignUp(http://www.studytonight.com/register)

    DISTINCTkeyword(distinct
    Suggest(http://www.studytonight.com/suggest)
    keyword)
    AND&ORoperator(sql
    andoroperator)
    AdvanceSQL
    SQLConstraints(sql

    tableshouldbeorganizedintorows,andeachrowshouldhaveaprimarykeythatdistinguishesitasunique.
    ThePrimarykeyisusuallyasinglecolumn,butsometimesmorethanonecolumncanbecombinedtocreate
    asingleprimarykey.ForexampleconsideratablewhichisnotinFirstnormalform
    StudentTable:
    Student

    Age

    Subject

    Adam

    15

    Biology,Maths

    Alex

    14

    Maths

    Stuart

    17

    Maths

    constraints)
    SQLfunction(sqlfunction)
    SQLJoin(joininginsql)
    SQLAlias(sqlalias)
    SQLSEToperation(set
    operationinsql)
    SQLSequences(sql

    InFirstNormalForm,anyrowmustnothaveacolumninwhichmorethanonevalueissaved,likeseparated

    sequences)

    withcommas.Ratherthanthat,wemustseparatesuchdataintomultiplerows.

    SQLViews(sqlviews)

    TestYourself!
    Ifyouhavestudiedallthelessons
    ofDBMS,thenevaluateyourselfby
    takingthesetests.

    StudentTablefollowing1NFwillbe:
    Student

    Age

    Subject

    Adam

    15

    Biology

    Adam

    15

    Maths

    Alex

    14

    Maths

    17

    Maths

    DBMSTopicalTest(http://www.studytonight.com/dbms/tests/)
    Stuart

    UsingtheFirstNormalForm,dataredundancyincreases,astherewillbemanycolumnswithsamedatain
    multiplerowsbuteachrowasawholewillbeunique.

    SecondNormalForm(2NF)
    AspertheSecondNormalFormtheremustnotbeanypartialdependencyofanycolumnonprimarykey.It
    meansthatforatablethathasconcatenatedprimarykey,eachcolumninthetablethatisnotpartofthe
    primarykeymustdependupontheentireconcatenatedkeyforitsexistence.Ifanycolumndependsonlyon
    onepartoftheconcatenatedkey,thenthetablefailsSecondnormalform.
    InexampleofFirstNormalFormtherearetworowsforAdam,toincludemultiplesubjectsthathehasopted
    for.Whilethisissearchable,andfollowsFirstnormalform,itisaninefficientuseofspace.Alsointheabove
    TableinFirstNormalForm,whilethecandidatekeyis{Student,Subject},AgeofStudentonlydependson
    Studentcolumn,whichisincorrectasperSecondNormalForm.Toachievesecondnormalform,itwouldbe
    helpfultosplitoutthesubjectsintoanindependenttable,andmatchthemupusingthestudentnamesas
    foreignkeys.
    NewStudentTablefollowing2NFwillbe:
    Student

    Age

    Adam

    15

    Alex

    14

    Stuart

    17

    InStudentTablethecandidatekeywillbeStudentcolumn,becauseallothercolumni.eAgeisdependenton
    it.
    NewSubjectTableintroducedfor2NFwillbe:
    Student

    Subject

    Adam

    Biology

    Adam

    Maths

    Alex

    Maths

    Stuart

    Maths

    InSubjectTablethecandidatekeywillbe{Student,Subject}column.Now,boththeabovetablesqualifiesfor
    SecondNormalFormandwillneversufferfromUpdateAnomalies.Althoughthereareafewcomplexcasesin
    whichtableinSecondNormalFormsuffersUpdateAnomalies,andtohandlethosescenariosThirdNormal
    Formisthere.

    ThirdNormalForm(3NF)
    ThirdNormalformappliesthateverynonprimeattributeoftablemustbedependentonprimarykey.The
    transitivefunctionaldependencyshouldberemovedfromthetable.ThetablemustbeinSecondNormal
    form.Forexample,consideratablewithfollowingfields.
    Student_DetailTable:
    Student_id

    Student_name

    DOB

    Street

    city

    State

    Zip

    InthistableStudent_idisPrimarykey,butstreet,cityandstatedependsuponZip.Thedependencybetween
    zipandotherfieldsiscalledtransitivedependency.Hencetoapply3NF,weneedtomovethestreet,city
    andstatetonewtable,withZipasprimarykey.
    NewStudent_DetailTable:
    Student_id

    Student_name

    DOB

    Zip

    AddressTable:
    Zip

    Street

    city

    state

    Theadvantageofremovingtranstivedependencyis,
    Amountofdataduplicationisreduced.
    Dataintegrityachieved.

    BoyceandCoddNormalForm(BCNF)
    BoyceandCoddNormalFormisahigherversionoftheThirdNormalform.Thisformdealswithcertaintype
    ofanamolythatisnothandledby3NF.A3NFtablewhichdoesnothavemultipleoverlappingcandidatekeys
    issaidtobeinBCNF.

    Prev(databasekey.php)

    Next(erdiagram.php)

    CheckOut: Library (http://www.studytonight.com/library) VideoCourses (http://www.studytonight.com/onlinecourses) Studyroom


    (http://www.studytonight.com/studyroom) Flashcards (http://www.studytonight.com/flashcards)
    Studytonight2014

    HandcraftedwithLove

    AboutUs(http://www.studytonight.com/about)Suggest(http://www.studytonight.com/suggest)Terms(http://www.studytonight.com/terms)ContactUs
    (http://www.studytonight.com/contact)Collaborate(http://www.studytonight.com/collaborate/)Authors(http://www.studytonight.com/authors/)Blog
    (http://studytonight.tumblr.com/)

    You might also like