SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Normalization


Normalization

Author
Message
Anjan@Sql
Anjan@Sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1572
Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?
m.rajesh.uk
m.rajesh.uk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 602
Yes you can get a idea by seeing the table.
First of all know the normalization forms and see whether they are implemented in table or not.
example: In your table there is calculated column .This means this is violating 3rd norm of normalization. So you can say it is not properly normalized. Like wise check other norms as-well.
Igor Micev
Igor Micev
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19844 Visits: 5191
Hi

Interesting questions.
For example the following simple code would help you find the computed columns (violation of 3th normal form) for a table:

select t.name [TableName], c.name [ColumnName]
from sys.tables t
join sys.computed_columns c on c.object_id = t.object_id
where t.name = 'Table_name'



You can do something similar for the other normal forms.

Regards
IgorMi

Igor Micev,
My blog: www.igormicev.com
Anjan@Sql
Anjan@Sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1572
Thank You IgorMi,
Expecting More Answers
Anjan@Sql
Anjan@Sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1572
Thank You Rajesh
Igor Micev
Igor Micev
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19844 Visits: 5191
Hi
You can use this code to determine if a table has primary key (a condition for 2NF)

declare @2NF_flag bit
set @2NF_flag=convert(bit,0)
if objectproperty(object_id('[schema].[Table]'),'IsUserTable') = 1
if (objectproperty(object_id('[schema].[Table]'),'TableHasPrimaryKey')=1
/*or objectproperty(object_id('[schema].[Table]'),'TableHasClustIndex')=1*/)
set @2NF_flag=convert(bit,1)



Regards
IgorMi

Igor Micev,
My blog: www.igormicev.com
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51030 Visits: 13159
m.rajesh.uk (4/20/2013)
Yes you can get a idea by seeing the table.
First of all know the normalization forms and see whether they are implemented in table or not.
example: In your table there is calculated column .This means this is violating 3rd norm of normalization. So you can say it is not properly normalized. Like wise check other norms as-well.

That's not always true. Here's an example of a table which is in 3rd normal form and has a computed column.
CREATE TABLE example (A int PRIMARY KEY, B as A/3417)


Since A is the whole primary key, and everything in the table is determined by A and by nothing else, there are no non-atomic columns/repeating groups, and there is a primary key, the table is in all of 1NF, 2NF and 3NF. In fact the table is also in EKNF, BCNF, 4NF, and 5NF as is immediately obvious from inspection - you will never see a more thorougly normalised table than that.

You might want to read my article on 3NF (here) to help you avoid making mistaken overbroad generalisations like that one.

Tom

Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51030 Visits: 13159
Ananth@Sql (4/19/2013)
Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?

In general no. Usually you have to know all the business rules that can be expressed in terms of functional dependencies before you can be sure whether a table violates one of 2NF, 3NF, EKNF, or BCNF. If you are worried about 4NF you also need to know all the business rules that can be expressed as multi-valued dependencies, and for 5NF you need to know all the business rules that could be expressed in terms of lossless join decompositions. Since the business rules may not be obvious from looking at the table, you may not be able to tell whether the table definition would allow any to be violated. There are some cases however where it is obvious that a table violates one of the normal forms - for example a computed column which is computed from a set of columns that doesn't include every column of some candidate key violates 3NF and every higher normal form).

Tom

Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51030 Visits: 13159
IgorMi (4/20/2013)
Hi
You can use this code to determine if a table has primary key (a condition for 2NF)

Actually having a primary key is a condition for 1NF. Of course since being in 1NF is a condition for 2NF, it's a condition for 2NF too.

Tom

m.rajesh.uk
m.rajesh.uk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 602
Thanks for your information.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search