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


Normalization


Normalization

Author
Message
Anjan@Sql
Anjan@Sql
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 1285
Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?
m.rajesh.uk
m.rajesh.uk
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 598
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5990 Visits: 5084
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
Anjan@Sql
Anjan@Sql
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 1285
Thank You IgorMi,
Expecting More Answers
Anjan@Sql
Anjan@Sql
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 1285
Thank You Rajesh
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5990 Visits: 5084
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
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
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 598
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