Hello,
I guess this is a good brief definition for Normalization. I am
trying to understand what is the sentence "and are not and are not
dependent on any non-key ATTRIBUTEs." in the definition.
"Normalization is the process of designing database tables to ensure
that the fields in each table do not repeat, are fully identified by a
unique KEY, and are not dependent on any non-key ATTRIBUTEs."
For instance a table Employee has three columns
EmpID PK
EmpFname varchar(50)
EmpRole varchar(50)
...
are the non-key attributes mean the key that are not neither PK nor FK
in a table like EmpFname and EmpRole in the table Employee?
And also if you are not rush , please let me know how could we define
1st Normalization, II Normalization and III Normalization. And in most
projects to what normalization level we need to normalize the data.
Thanks,
-LFor the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on Course
Country - and we should therefore have a separate table for course
locations.
For the second part, I can't go into all the details about normalization
here - it's too lengthy. You'll need a decent design book for that (eg Pro
SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
Kline and Kurt Windisch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thanks for the quick reply. Sorry to bother you one more time but I
couldn't understand
From: Paul Ibison - view profile
Date: Fri, Aug 11 2006 9:44 am
Email: "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com>
Groups: microsoft.public.sqlserver.server
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
For the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on
Course
Country - and we should therefore have a separate table for course
locations.
your explanation on this. Could you please take couple of more minutes
to explain it?
Thanks
-L
Paul Ibison wrote:
> For the first part, consider a table such as this:
> CREATE TABLE dbo.Courses
> (
> Course_ID int NOT NULL,
> City varchar(50) NULL,
> Country varchar(50) NULL
> )
> go
> ALTER TABLE dbo.CoursesADD CONSTRAINT
> PK_Courses PRIMARY KEY CLUSTERED
> (
> Course_ID
> )
> go
> This voilates the condition you quote, as the Course City depends on Cours
e
> Country - and we should therefore have a separate table for course
> locations.
> For the second part, I can't go into all the details about normalization
> here - it's too lengthy. You'll need a decent design book for that (eg Pro
> SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
> Kline and Kurt Windisch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com|||Course Cities depend on Course Countries, but each attribute should just
relate to the Key, so Course Cities violates the rule. This example shows
the importance of reducing redundancy, so if we had a lot of courses in
London, England, the relationship between London and England would be
repeated unnecessarily for each course. Also, the dependancy between London
and England has been hidden as data in a table, so if it were to change,
this would be very bad news. Ridiculous you might think! But if it was city
and county, in the UK we have changed county boundaries so that this has
occured, and it will be a problem in many database tables designed this way.
This is a big subject so I had a trawl through a few websites and I quite
like this explanation of all the rules that I hope you'll find useful:
http://www.datamodel.org/NormalizationRules.html#four
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 11 Aug 2006 05:43:41 -0700, "Learner" <pradev@.gmail.com> wrote:
>And also if you are not rush , please let me know how could we define
>1st Normalization, II Normalization and III Normalization. And in most
>projects to what normalization level we need to normalize the data.
There's a saying that 1NF, 2NF, and 3NF amount to:
The key,
The whole key,
and nothing but the key.
Which is pretty close!
Congratulations, if you can recite this on demand, you will come off
as better educated than about half the "DBA's" out there.
1NF includes some other stuff, and I don't think 2NF makes any sense,
really. The fun starts after 3NF - it seldom matters, but when it
does, it makes you feel good to know about it!
The idea behind normalization is that there are algebraic and semantic
ideas that work if and only if the rules for normalization are
followed, but you don't have to recite those, just the mechanical
rules.
J.sql
Showing posts with label sentence. Show all posts
Showing posts with label sentence. Show all posts
Monday, March 26, 2012
Normalization - help me understand
Hello,
I guess this is a good brief definition for Normalization. I am
trying to understand what is the sentence "and are not and are not
dependent on any non-key ATTRIBUTEs." in the definition.
"Normalization is the process of designing database tables to ensure
that the fields in each table do not repeat, are fully identified by a
unique KEY, and are not dependent on any non-key ATTRIBUTEs."
For instance a table Employee has three columns
EmpID PK
EmpFname varchar(50)
EmpRole varchar(50)
...
are the non-key attributes mean the key that are not neither PK nor FK
in a table like EmpFname and EmpRole in the table Employee?
And also if you are not rush , please let me know how could we define
1st Normalization, II Normalization and III Normalization. And in most
projects to what normalization level we need to normalize the data.
Thanks,
-LFor the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on Course
Country - and we should therefore have a separate table for course
locations.
For the second part, I can't go into all the details about normalization
here - it's too lengthy. You'll need a decent design book for that (eg Pro
SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
Kline and Kurt Windisch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thanks for the quick reply. Sorry to bother you one more time but I
couldn't understand
From: Paul Ibison - view profile
Date: Fri, Aug 11 2006 9:44 am
Email: "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com>
Groups: microsoft.public.sqlserver.server
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
For the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on
Course
Country - and we should therefore have a separate table for course
locations.
your explanation on this. Could you please take couple of more minutes
to explain it?
Thanks
-L
Paul Ibison wrote:
> For the first part, consider a table such as this:
> CREATE TABLE dbo.Courses
> (
> Course_ID int NOT NULL,
> City varchar(50) NULL,
> Country varchar(50) NULL
> )
> go
> ALTER TABLE dbo.CoursesADD CONSTRAINT
> PK_Courses PRIMARY KEY CLUSTERED
> (
> Course_ID
> )
> go
> This voilates the condition you quote, as the Course City depends on Course
> Country - and we should therefore have a separate table for course
> locations.
> For the second part, I can't go into all the details about normalization
> here - it's too lengthy. You'll need a decent design book for that (eg Pro
> SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
> Kline and Kurt Windisch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com|||Course Cities depend on Course Countries, but each attribute should just
relate to the Key, so Course Cities violates the rule. This example shows
the importance of reducing redundancy, so if we had a lot of courses in
London, England, the relationship between London and England would be
repeated unnecessarily for each course. Also, the dependancy between London
and England has been hidden as data in a table, so if it were to change,
this would be very bad news. Ridiculous you might think! But if it was city
and county, in the UK we have changed county boundaries so that this has
occured, and it will be a problem in many database tables designed this way.
This is a big subject so I had a trawl through a few websites and I quite
like this explanation of all the rules that I hope you'll find useful:
http://www.datamodel.org/NormalizationRules.html#four
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 11 Aug 2006 05:43:41 -0700, "Learner" <pradev@.gmail.com> wrote:
>And also if you are not rush , please let me know how could we define
>1st Normalization, II Normalization and III Normalization. And in most
>projects to what normalization level we need to normalize the data.
There's a saying that 1NF, 2NF, and 3NF amount to:
The key,
The whole key,
and nothing but the key.
Which is pretty close!
Congratulations, if you can recite this on demand, you will come off
as better educated than about half the "DBA's" out there.
1NF includes some other stuff, and I don't think 2NF makes any sense,
really. The fun starts after 3NF - it seldom matters, but when it
does, it makes you feel good to know about it!
The idea behind normalization is that there are algebraic and semantic
ideas that work if and only if the rules for normalization are
followed, but you don't have to recite those, just the mechanical
rules.
J.
I guess this is a good brief definition for Normalization. I am
trying to understand what is the sentence "and are not and are not
dependent on any non-key ATTRIBUTEs." in the definition.
"Normalization is the process of designing database tables to ensure
that the fields in each table do not repeat, are fully identified by a
unique KEY, and are not dependent on any non-key ATTRIBUTEs."
For instance a table Employee has three columns
EmpID PK
EmpFname varchar(50)
EmpRole varchar(50)
...
are the non-key attributes mean the key that are not neither PK nor FK
in a table like EmpFname and EmpRole in the table Employee?
And also if you are not rush , please let me know how could we define
1st Normalization, II Normalization and III Normalization. And in most
projects to what normalization level we need to normalize the data.
Thanks,
-LFor the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on Course
Country - and we should therefore have a separate table for course
locations.
For the second part, I can't go into all the details about normalization
here - it's too lengthy. You'll need a decent design book for that (eg Pro
SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
Kline and Kurt Windisch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thanks for the quick reply. Sorry to bother you one more time but I
couldn't understand
From: Paul Ibison - view profile
Date: Fri, Aug 11 2006 9:44 am
Email: "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com>
Groups: microsoft.public.sqlserver.server
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
For the first part, consider a table such as this:
CREATE TABLE dbo.Courses
(
Course_ID int NOT NULL,
City varchar(50) NULL,
Country varchar(50) NULL
)
go
ALTER TABLE dbo.CoursesADD CONSTRAINT
PK_Courses PRIMARY KEY CLUSTERED
(
Course_ID
)
go
This voilates the condition you quote, as the Course City depends on
Course
Country - and we should therefore have a separate table for course
locations.
your explanation on this. Could you please take couple of more minutes
to explain it?
Thanks
-L
Paul Ibison wrote:
> For the first part, consider a table such as this:
> CREATE TABLE dbo.Courses
> (
> Course_ID int NOT NULL,
> City varchar(50) NULL,
> Country varchar(50) NULL
> )
> go
> ALTER TABLE dbo.CoursesADD CONSTRAINT
> PK_Courses PRIMARY KEY CLUSTERED
> (
> Course_ID
> )
> go
> This voilates the condition you quote, as the Course City depends on Course
> Country - and we should therefore have a separate table for course
> locations.
> For the second part, I can't go into all the details about normalization
> here - it's too lengthy. You'll need a decent design book for that (eg Pro
> SQL Server 2005 Database Design and Optimization by Louis Davidson, Kevin
> Kline and Kurt Windisch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com|||Course Cities depend on Course Countries, but each attribute should just
relate to the Key, so Course Cities violates the rule. This example shows
the importance of reducing redundancy, so if we had a lot of courses in
London, England, the relationship between London and England would be
repeated unnecessarily for each course. Also, the dependancy between London
and England has been hidden as data in a table, so if it were to change,
this would be very bad news. Ridiculous you might think! But if it was city
and county, in the UK we have changed county boundaries so that this has
occured, and it will be a problem in many database tables designed this way.
This is a big subject so I had a trawl through a few websites and I quite
like this explanation of all the rules that I hope you'll find useful:
http://www.datamodel.org/NormalizationRules.html#four
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 11 Aug 2006 05:43:41 -0700, "Learner" <pradev@.gmail.com> wrote:
>And also if you are not rush , please let me know how could we define
>1st Normalization, II Normalization and III Normalization. And in most
>projects to what normalization level we need to normalize the data.
There's a saying that 1NF, 2NF, and 3NF amount to:
The key,
The whole key,
and nothing but the key.
Which is pretty close!
Congratulations, if you can recite this on demand, you will come off
as better educated than about half the "DBA's" out there.
1NF includes some other stuff, and I don't think 2NF makes any sense,
really. The fun starts after 3NF - it seldom matters, but when it
does, it makes you feel good to know about it!
The idea behind normalization is that there are algebraic and semantic
ideas that work if and only if the rules for normalization are
followed, but you don't have to recite those, just the mechanical
rules.
J.
Wednesday, March 7, 2012
NOLOCK sentence
Hello !!
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ?? I mean, I don't like to lock any table for selects.
Is It possible ?? How to do It (step by step) ?
Thanks !!You can set transaction isolation level for your connection:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED|||Choosing to not use locking in inherantly dangerous. It means that you can have all kinds of strange problems due to interactions with other spids (users) that can be impossible to diagnose because they are impossible to recreate.
If you want to desend into the madness, all you need to do is:SET TRANSACTION LEVEL READ_UNCOMMITTED (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp) Be sure to read Customizing Locking (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_27cc.asp) AND all of the sections under it before you do this!
-PatP|||I wouldn't exaggerate the dangers of dirty reads. In a busy OLTP database there are hundreds of calls made to static tables and there is NO NEED to allow default READ COMMITTED behavior. Of course, when a DML is relying on a SELECT, then this should be seriously taken into account. Personally, I wouldn't use the SET statement to control transaction isolation level. It takes less than 10 characters to explicitly state how you want the data to be accessed by using table hints.|||From my perspective, I don't exaggerate the dangers of dirty reads. I see them as a serious problem that lots of people overlook because allowing dirty reads is easier than solving the underlying problems in their code.
While there are reasons that dirty reads are necessary, and many cases where they are convenient, I feel very strongly that dirty reads are both dangerous and overused.
Most of the time when I encounter dirty reads, it is because of financial statements that don't balance consistantly in production systems, but almost always balance in test. The test system may only have a simulated load of 50 or 100 users, which may not be enough to cause the report to be unbalanced. It will never cause it to be unbalanced the same way twice. This leads to lots and lots of hair loss if someone doesn't think to check the locking to see that the developer specified that the statements don't need balance!
-PatP|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.In the ideal world that is true, but I can't even get a vistor's pass for there anymore! I have to live and code in the real world.
You are correct that final statements need to be done after the period is closed, but working statements are generated from shortly after the period starts until sometime after the period ends. When the bean counters get a statement that doesn't balance, they don't think about why it might not balance, they just scream that it doesn't. You can explain to them, and they understand that "work papers" might not always balance, but those papers have the same format/appearance as final papers and that can make the users crazy.
In some ways, this is a training issue. The users need to realize that a statement for YE 12/31 isn't complete on 06/18, and if they stopped to think about it they'd know that it wasn't complete, but that still doesn't make them willing to excuse a statement that doesn't balance as of the time it was run.
I can control what the developers do (to some extent). I have little or no control over what the users do. I fix the problems where I can.
-PatP|||I wash windows...
And string up my dbas who use (NOLOCK)
EDIT: And if you want, why not pin the code tables?
EDIT2: And if you notice they want to do that for the ENTIRE db...
WOW...holy corruption bartman!|||I think I must continue using NOLOCK clause !|||Well, I think it's more an app design issue rather than users training or NOLOCK. App design will be reconciled with db design which should have the same set of business rules as the foundation, just like the app design must. And all this results from a sound system analysis where each data/info flow is accounted for and projected onto app/db design...But, as you said, - that's an ideal world, and "they" don't pay us enough to dedicate several years of our lives to creating one :(|||The application development finished, and they are having locking problems, and I can't change it !|||Doesn't sound finished to me...
If they have that many problems...you can bet the wheels are gonna fly right off when you change the ISOLATION LEVEL
They may be complaining now...soon they will be blaimng you and the database for screwing up the data
Oh
MOO|||The application development finished, and they are having locking problems, and I can't change it !Have they finished, or are they having locking problems? The two are mutually exclusive, they can't have both.
Changing the locking level would be what we call a "Class 2 CLM (Career Limiting Move)". It might not get you fired, but whether it does or not you'll wish that it had!
I think that life is too short to volunteer to sign up for that kind of problems.
-PatP|||Thanks everybody !
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ?? I mean, I don't like to lock any table for selects.
Is It possible ?? How to do It (step by step) ?
Thanks !!You can set transaction isolation level for your connection:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED|||Choosing to not use locking in inherantly dangerous. It means that you can have all kinds of strange problems due to interactions with other spids (users) that can be impossible to diagnose because they are impossible to recreate.
If you want to desend into the madness, all you need to do is:SET TRANSACTION LEVEL READ_UNCOMMITTED (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp) Be sure to read Customizing Locking (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_27cc.asp) AND all of the sections under it before you do this!
-PatP|||I wouldn't exaggerate the dangers of dirty reads. In a busy OLTP database there are hundreds of calls made to static tables and there is NO NEED to allow default READ COMMITTED behavior. Of course, when a DML is relying on a SELECT, then this should be seriously taken into account. Personally, I wouldn't use the SET statement to control transaction isolation level. It takes less than 10 characters to explicitly state how you want the data to be accessed by using table hints.|||From my perspective, I don't exaggerate the dangers of dirty reads. I see them as a serious problem that lots of people overlook because allowing dirty reads is easier than solving the underlying problems in their code.
While there are reasons that dirty reads are necessary, and many cases where they are convenient, I feel very strongly that dirty reads are both dangerous and overused.
Most of the time when I encounter dirty reads, it is because of financial statements that don't balance consistantly in production systems, but almost always balance in test. The test system may only have a simulated load of 50 or 100 users, which may not be enough to cause the report to be unbalanced. It will never cause it to be unbalanced the same way twice. This leads to lots and lots of hair loss if someone doesn't think to check the locking to see that the developer specified that the statements don't need balance!
-PatP|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.In the ideal world that is true, but I can't even get a vistor's pass for there anymore! I have to live and code in the real world.
You are correct that final statements need to be done after the period is closed, but working statements are generated from shortly after the period starts until sometime after the period ends. When the bean counters get a statement that doesn't balance, they don't think about why it might not balance, they just scream that it doesn't. You can explain to them, and they understand that "work papers" might not always balance, but those papers have the same format/appearance as final papers and that can make the users crazy.
In some ways, this is a training issue. The users need to realize that a statement for YE 12/31 isn't complete on 06/18, and if they stopped to think about it they'd know that it wasn't complete, but that still doesn't make them willing to excuse a statement that doesn't balance as of the time it was run.
I can control what the developers do (to some extent). I have little or no control over what the users do. I fix the problems where I can.
-PatP|||I wash windows...
And string up my dbas who use (NOLOCK)
EDIT: And if you want, why not pin the code tables?
EDIT2: And if you notice they want to do that for the ENTIRE db...
WOW...holy corruption bartman!|||I think I must continue using NOLOCK clause !|||Well, I think it's more an app design issue rather than users training or NOLOCK. App design will be reconciled with db design which should have the same set of business rules as the foundation, just like the app design must. And all this results from a sound system analysis where each data/info flow is accounted for and projected onto app/db design...But, as you said, - that's an ideal world, and "they" don't pay us enough to dedicate several years of our lives to creating one :(|||The application development finished, and they are having locking problems, and I can't change it !|||Doesn't sound finished to me...
If they have that many problems...you can bet the wheels are gonna fly right off when you change the ISOLATION LEVEL
They may be complaining now...soon they will be blaimng you and the database for screwing up the data
Oh
MOO|||The application development finished, and they are having locking problems, and I can't change it !Have they finished, or are they having locking problems? The two are mutually exclusive, they can't have both.
Changing the locking level would be what we call a "Class 2 CLM (Career Limiting Move)". It might not get you fired, but whether it does or not you'll wish that it had!
I think that life is too short to volunteer to sign up for that kind of problems.
-PatP|||Thanks everybody !
Subscribe to:
Posts (Atom)