Normalization

Question

Post reply

Normalization

Anjan@Sql

SSCrazy

Points: 2316
More actions
April 19, 2013 at 8:44 pm

#273183

Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

m.rajesh.uk Hall of Fame Points: 3653 More actions · Answer 1

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 SSC-Dedicated Points: 33109 More actions · Answer 2

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 SSCrazy Points: 2316 More actions · Answer 3

Anjan@Sql

SSCrazy

Points: 2316

April 20, 2013 at 3:48 am

#1608461

Thank You IgorMi,

Expecting More Answers

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 4

Anjan@Sql

SSCrazy

Points: 2316

April 20, 2013 at 3:49 am

#1608462

Thank You Rajesh

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

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

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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[/url]) to help you avoid making mistaken overbroad generalisations like that one.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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