Click here to monitor SSC
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: 1057 Visits: 1194
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 (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 589
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
www.seavus.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: 1057 Visits: 1194
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: 1057 Visits: 1194
Thank You Rajesh
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
www.seavus.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
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 (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 589
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