Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Normalization Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 8:44 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:19 AM
Points: 536, Visits: 648
Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?
Post #1444666
Posted Saturday, April 20, 2013 1:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 10:00 AM
Points: 190, Visits: 475
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.
Post #1444689
Posted Saturday, April 20, 2013 2:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 3,014, Visits: 3,095
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
Post #1444691
Posted Saturday, April 20, 2013 3:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:19 AM
Points: 536, Visits: 648
Thank You IgorMi,
Expecting More Answers
Post #1444696
Posted Saturday, April 20, 2013 3:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:19 AM
Points: 536, Visits: 648
Thank You Rajesh
Post #1444697
Posted Saturday, April 20, 2013 7:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 3,014, Visits: 3,095
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
Post #1444709
Posted Saturday, April 20, 2013 6:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #1444769
Posted Saturday, April 20, 2013 6:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #1444770
Posted Saturday, April 20, 2013 6:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #1444772
Posted Monday, April 22, 2013 2:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 10:00 AM
Points: 190, Visits: 475
Thanks for your information.
Post #1444893
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse