none
Normalization

    Question

  • What are transitive dependencies in 3rd normal form? Please give me an easy example for this and BCNF
    asharafmail@gmail.com
    Friday, December 18, 2009 6:18 AM

Answers

  • I belive this is a good start:
    http://www.databasedev.co.uk/3norm_form.html
    Jasmin
    • Proposed as answer by Doug_YMVP Friday, December 18, 2009 4:16 PM
    • Marked as answer by Ashru Monday, December 28, 2009 4:42 AM
    Friday, December 18, 2009 8:04 AM
  • I'm not sure I understand the question. The simplified definition of thrid normal form is "second normal form AND no transitive dependencies". So following from this definition, there can never be any transitive definitions.

    I can give you an example of a transitive dependency in 2nd normal form. Consider this table:
        BankAccounts (AccountNumber , AccountType, InterestRate, ...)
    AccountNumber is a candidate key, and both AccountType and InterestRate are functionally dependent on AccountNumber, so the relation is in 2NF. But InterestRate is also dependent on AccountType, since all SuperSavings accounts have a 3% interest, all NormalSavings account give 2%, and all InternetSavings accounts have 3.5%. The depenndency of InterestRate on AccountNumber is transitive, because it follows automatically from the other two dependencies. The 3rd normal form of this design would then be:
        BankAccounts (AccountNumber , AccountType, ...)
        AccountTypes (AccountType , InterestRate, ...)


    BCNF is a lot harder. The rules for BCNF are quite complex, and the examples are necessarly comples as well. Maybe you can start with the explanation and examples provided in the Wikipedia article at http://en.wikipedia.org/wiki/BCNF . If you still have questions after reading that, feel free to return here and ask them, and I'll try to answer them.
    -- Hugo Kornelis, SQL Server MVP
    • Proposed as answer by Doug_YMVP Friday, December 18, 2009 4:17 PM
    • Marked as answer by Ashru Monday, December 28, 2009 4:42 AM
    Friday, December 18, 2009 10:38 AM

All replies

  • I belive this is a good start:
    http://www.databasedev.co.uk/3norm_form.html
    Jasmin
    • Proposed as answer by Doug_YMVP Friday, December 18, 2009 4:16 PM
    • Marked as answer by Ashru Monday, December 28, 2009 4:42 AM
    Friday, December 18, 2009 8:04 AM
  • I'm not sure I understand the question. The simplified definition of thrid normal form is "second normal form AND no transitive dependencies". So following from this definition, there can never be any transitive definitions.

    I can give you an example of a transitive dependency in 2nd normal form. Consider this table:
        BankAccounts (AccountNumber , AccountType, InterestRate, ...)
    AccountNumber is a candidate key, and both AccountType and InterestRate are functionally dependent on AccountNumber, so the relation is in 2NF. But InterestRate is also dependent on AccountType, since all SuperSavings accounts have a 3% interest, all NormalSavings account give 2%, and all InternetSavings accounts have 3.5%. The depenndency of InterestRate on AccountNumber is transitive, because it follows automatically from the other two dependencies. The 3rd normal form of this design would then be:
        BankAccounts (AccountNumber , AccountType, ...)
        AccountTypes (AccountType , InterestRate, ...)


    BCNF is a lot harder. The rules for BCNF are quite complex, and the examples are necessarly comples as well. Maybe you can start with the explanation and examples provided in the Wikipedia article at http://en.wikipedia.org/wiki/BCNF . If you still have questions after reading that, feel free to return here and ask them, and I'll try to answer them.
    -- Hugo Kornelis, SQL Server MVP
    • Proposed as answer by Doug_YMVP Friday, December 18, 2009 4:17 PM
    • Marked as answer by Ashru Monday, December 28, 2009 4:42 AM
    Friday, December 18, 2009 10:38 AM
  • Thanks a lot
    Hugo Kornelis and Jasmin Azemovic. I am going through these..

    Asharaf

    asharafmail@gmail.com
    Friday, December 18, 2009 11:52 AM
  • Hugo,

    Ah, yes, but...

    InterestRate 'may' be transitive and dependent only on the AccountType.

    And InterestRate may vary within the AccountType and NOT be transitive at all.

    Consider a 'teaser rate' for new accounts. Perhaps 3.5% for the first n months, then reverting to the current AccountType rate. Perhaps some accounts have been 'grandfathered' in with a different static rate due to mergers, etc.

    Of course, all of those variations may be accomodated by adding additional 'AccountTypes' -or even sub-typing. One would have to carefully consider the model. Would AccountTypes/SubTypes more adequately reflect the business requirements of the data model, or would accepting that InterestRate is NON-transitive more properly reflect the model.

    I think that, as always, it depends...


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Monday, December 21, 2009 1:40 PM
  • Hi Arnie,

    Of course. And in the real world, the interest rate will probably vary over time, to follow market trends.

    I intended my sample asa a simplified one, not a good model for a real banking database. And I intended to include that in my text, but upon rereading I now see that I forgot to do so.

    So please read my earlier post as if it includes the words "this is simplified from the real words", and "supposing that for this bank, the interest rate depends on the account type only".
    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 22, 2009 11:58 AM
  • Create table Mainaccount(Id numeric,AccountNum varchar(10),Name varchar(30) )

    Create table debit(accountno varchar(10), date1 datetime, debit numeric)

    Create table credit(accountno varchar(10), date1 datetime, credit numeric)

    Insert into mainaccount values(1,'0001','Murtaza')

    Insert into debit values('0001',getdate(),100)

    Insert into credit values('0001',getdate(),0)

    Insert into debit values('0001',getdate(),0)

    Insert into credit values('0001',getdate(),200)

    Select ma.id, ma.accountNum, dt.debit,cr.credit from mainaccount ma inner join debit dt

    on dt.accountno=ma.accountnum

    inner join credit cr

    on cr.accountno=ma.accountnum group by ma.id, ma.accountNum, dt.debit,cr.credit

    order by 1

    result I am getting is :

    id accnum debit credit

    1 0001         0    0
    1 0001         0    200
    1 0001        100 0
    1 0001        100 200

    which is wrong

    result shoul be like as following:

    id accnum debit credit
    1 0001         0    200
    1 0001        100 0


    Murtaza Abbasi Sr. Software Engineer

    Friday, July 27, 2012 6:18 AM
  • thnaks buddy,

    Got solution myself by addinf id as numeric in debit and credit tables : and have some midicication in query as follow:

    select  ma.accountNum,dt.debit,cr.credit  from mainaccount ma

       inner join debit dt

          on dt.accountno=ma.accountnum

     inner join credit cr

          on cr.accountno=ma.accountnum and cr.id=dt.id

    group by  ma.accountNum, dt.debit,cr.credit

    order by 1

    result:

    0001 0 200
    0001 100 0

    0002 0 150
    0002 150 0

    no can you help me generatin balance ?



    Murtaza Abbasi Sr. Software Engineer

    Friday, July 27, 2012 7:44 AM
  • thnaks buddy,

    Got solution myself by addinf id as numeric in debit and credit tables : and have some midicication in query as follow:

    select  ma.accountNum,dt.debit,cr.credit  from mainaccount ma

       inner join debit dt

          on dt.accountno=ma.accountnum

     inner join credit cr

          on cr.accountno=ma.accountnum and cr.id=dt.id

    group by  ma.accountNum, dt.debit,cr.credit

    order by 1

    result:

    0001 0 200
    0001 100 0

    0002 0 150
    0002 150 0

    no can you help me generatin balance ?



    Murtaza Abbasi Sr. Software Engineer

    Friday, July 27, 2012 7:44 AM
  • thnaks buddy,

    Got solution myself by addinf id as numeric in debit and credit tables : and have some midicication in query as follow:

    select  ma.accountNum,dt.debit,cr.credit  from mainaccount ma

       inner join debit dt

          on dt.accountno=ma.accountnum

     inner join credit cr

          on cr.accountno=ma.accountnum and cr.id=dt.id

    group by  ma.accountNum, dt.debit,cr.credit

    order by 1

    result:

    0001 0 200
    0001 100 0

    0002 0 150
    0002 150 0

    now can you help me generatin balance ?


    Murtaza Abbasi Sr. Software Engineer


    Friday, July 27, 2012 7:48 AM
  • my query:

    select  ma.accountNum,dt.debit,cr.credit , sum(dt.debit-cr.credit) balance from mainaccount ma

       inner join debit dt

          on dt.accountno=ma.accountnum

     inner join credit cr

          on cr.accountno=ma.accountnum and cr.id=dt.id

    group by  ma.accountNum, dt.debit,cr.credit

    order by 1

    my result come like:

    0001 0 200 -200
    0001 100 0 100
    0002 0 150 -150
    0002 150 0 150


    Murtaza Abbasi Sr. Software Engineer

    Friday, July 27, 2012 7:50 AM