Normalization

Question

What are transitive dependencies in 3rd normal form? Please give me an easy example for this and BCNF

---

[e-mail]

Answer 1

I belive this is a good start:
http://www.databasedev.co.uk/3norm_form.html

---

Jasmin

Answer 2

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

Answer 3

Thanks a lot

Hugo Kornelis and Jasmin Azemovic. I am going through these..

Asharaf

---

[e-mail]

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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