| Groups | Blog | Home |
sql server new users : Table integrity
table will maintain its integrity ....by this I mean if somebody other than the database owner or someone who has permission to write data to the table. Thanks for any help if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SkinnerSaw_Thickness]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SkinnerSaw_Thickness] GO CREATE TABLE [dbo].[SkinnerSaw_Thickness] ( [Counter] [bigint] IDENTITY (1, 1) NOT NULL , [Date] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Time] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [BlankID] [int] NOT NULL , [TargetThickness] [real] NOT NULL , [MaxThickness] [real] NOT NULL , [MinThickness] [real] NOT NULL , [ActualPanelThickness] [real] NOT NULL , [TopReading1] [real] NOT NULL , [TopReading2] [real] NOT NULL , [TopReading3] [real] NOT NULL , [TopReading4] [real] NOT NULL , [TopReading5] [real] NOT NULL , [TopReading6] [real] NOT NULL , [TopReading7] [real] NOT NULL , [TopReading8] [real] NOT NULL , [TopReading9] [real] NOT NULL , [TopReading10] [real] NOT NULL , [BottomReading1] [real] NOT NULL , [BottomReading2] [real] NOT NULL , [BottomReading3] [real] NOT NULL , [BottomReading4] [real] NOT NULL , [BottomReading5] [real] NOT NULL , [BottomReading6] [real] NOT NULL , [BottomReading7] [real] NOT NULL , [BottomReading8] [real] NOT NULL , [BottomReading9] [real] NOT NULL , [BottomReading10] [real] NOT NULL , [Blow] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [BlowDetectorOn] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
[quoted text, click to view]
"ktm400" <ktm400@discussions.microsoft.com> wrote in message news:0BB41251-B5C7-4725-B0EB-2E5CC1DFDA18@microsoft.com... >I am very new to sql and would like somebody to tell me if the following > table will maintain its integrity ....by this I mean if somebody other > than > the database owner or someone who has permission to write data to the > table. > Thanks for any help > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[SkinnerSaw_Thickness]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > drop table [dbo].[SkinnerSaw_Thickness] > GO > > CREATE TABLE [dbo].[SkinnerSaw_Thickness] ( > [Counter] [bigint] IDENTITY (1, 1) NOT NULL , > [Date] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [Time] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [BlankID] [int] NOT NULL , > [TargetThickness] [real] NOT NULL , > [MaxThickness] [real] NOT NULL , > [MinThickness] [real] NOT NULL , > [ActualPanelThickness] [real] NOT NULL , > [TopReading1] [real] NOT NULL , > [TopReading2] [real] NOT NULL , > [TopReading3] [real] NOT NULL , > [TopReading4] [real] NOT NULL , > [TopReading5] [real] NOT NULL , > [TopReading6] [real] NOT NULL , > [TopReading7] [real] NOT NULL , > [TopReading8] [real] NOT NULL , > [TopReading9] [real] NOT NULL , > [TopReading10] [real] NOT NULL , > [BottomReading1] [real] NOT NULL , > [BottomReading2] [real] NOT NULL , > [BottomReading3] [real] NOT NULL , > [BottomReading4] [real] NOT NULL , > [BottomReading5] [real] NOT NULL , > [BottomReading6] [real] NOT NULL , > [BottomReading7] [real] NOT NULL , > [BottomReading8] [real] NOT NULL , > [BottomReading9] [real] NOT NULL , > [BottomReading10] [real] NOT NULL , > [Blow] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [BlowDetectorOn] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > GO > > No keys = no integrity. Since you didn't post any I have to assume that the answer is No. There are plenty of other problems here too: The numbered columns 1-10 look like what's usually called a Repeating Group. Repeating groups are a fundamentally bad design for lots of important reasons. "Counter", "Date" and "Time" are pretty useless column names. They tell us virtually nothing about the attributes in question. Names are important, don't neglect them. Why two CHAR columns for date and time? Why not DATETIME? Do you know the difference between exact and inexact numeric datatypes and are you sure you want to use inexact numerics for all the Readings? Why BIGINT for the IDENTITY column? Do you expect 10^19 rows! Chances are INTEGER will be more than sufficient. Hopefully this has given you some things to think about and lookup in the documentation. Unfortunately newsgroups aren't the place for design tutorials. Logical design requires more knowledge of the concepts you are modelling than can be adequately conveyed in an online discussion. -- David Portas SQL Server MVP --
I forgot to check the keys and constraints box in enterprise manager
"generate sql script".....this is what it looks like now: Iam assuming that the rows will retain integrity....is this a correct assumption? Thank you if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SkinnerSaw_Thickness]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SkinnerSaw_Thickness] GO CREATE TABLE [dbo].[SkinnerSaw_Thickness] ( [Counter] [bigint] IDENTITY (1, 1) NOT NULL , [Date] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Time] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [BlankID] [int] NOT NULL , [TargetThickness] [real] NOT NULL , [MaxThickness] [real] NOT NULL , [MinThickness] [real] NOT NULL , [ActualPanelThickness] [real] NOT NULL , [TopReading1] [real] NOT NULL , [TopReading2] [real] NOT NULL , [TopReading3] [real] NOT NULL , [TopReading4] [real] NOT NULL , [TopReading5] [real] NOT NULL , [TopReading6] [real] NOT NULL , [TopReading7] [real] NOT NULL , [TopReading8] [real] NOT NULL , [TopReading9] [real] NOT NULL , [TopReading10] [real] NOT NULL , [BottomReading1] [real] NOT NULL , [BottomReading2] [real] NOT NULL , [BottomReading3] [real] NOT NULL , [BottomReading4] [real] NOT NULL , [BottomReading5] [real] NOT NULL , [BottomReading6] [real] NOT NULL , [BottomReading7] [real] NOT NULL , [BottomReading8] [real] NOT NULL , [BottomReading9] [real] NOT NULL , [BottomReading10] [real] NOT NULL , [Blow] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [BlowDetectorOn] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[SkinnerSaw_Thickness] WITH NOCHECK ADD CONSTRAINT [PK_SkinnerSaw_Thickness] PRIMARY KEY CLUSTERED ( [Counter] ) ON [PRIMARY] GO [quoted text, click to view] "David Portas" wrote:
> "ktm400" <ktm400@discussions.microsoft.com> wrote in message > news:0BB41251-B5C7-4725-B0EB-2E5CC1DFDA18@microsoft.com... > >I am very new to sql and would like somebody to tell me if the following > > table will maintain its integrity ....by this I mean if somebody other > > than > > the database owner or someone who has permission to write data to the > > table. > > Thanks for any help > > > > if exists (select * from dbo.sysobjects where id = > > object_id(N'[dbo].[SkinnerSaw_Thickness]') and OBJECTPROPERTY(id, > > N'IsUserTable') = 1) > > drop table [dbo].[SkinnerSaw_Thickness] > > GO > > > > CREATE TABLE [dbo].[SkinnerSaw_Thickness] ( > > [Counter] [bigint] IDENTITY (1, 1) NOT NULL , > > [Date] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [Time] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [BlankID] [int] NOT NULL , > > [TargetThickness] [real] NOT NULL , > > [MaxThickness] [real] NOT NULL , > > [MinThickness] [real] NOT NULL , > > [ActualPanelThickness] [real] NOT NULL , > > [TopReading1] [real] NOT NULL , > > [TopReading2] [real] NOT NULL , > > [TopReading3] [real] NOT NULL , > > [TopReading4] [real] NOT NULL , > > [TopReading5] [real] NOT NULL , > > [TopReading6] [real] NOT NULL , > > [TopReading7] [real] NOT NULL , > > [TopReading8] [real] NOT NULL , > > [TopReading9] [real] NOT NULL , > > [TopReading10] [real] NOT NULL , > > [BottomReading1] [real] NOT NULL , > > [BottomReading2] [real] NOT NULL , > > [BottomReading3] [real] NOT NULL , > > [BottomReading4] [real] NOT NULL , > > [BottomReading5] [real] NOT NULL , > > [BottomReading6] [real] NOT NULL , > > [BottomReading7] [real] NOT NULL , > > [BottomReading8] [real] NOT NULL , > > [BottomReading9] [real] NOT NULL , > > [BottomReading10] [real] NOT NULL , > > [Blow] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [BlowDetectorOn] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > > ) ON [PRIMARY] > > GO > > > > > > No keys = no integrity. Since you didn't post any I have to assume that the > answer is No. There are plenty of other problems here too: > > The numbered columns 1-10 look like what's usually called a Repeating Group. > Repeating groups are a fundamentally bad design for lots of important > reasons. > > "Counter", "Date" and "Time" are pretty useless column names. They tell us > virtually nothing about the attributes in question. Names are important, > don't neglect them. > > Why two CHAR columns for date and time? Why not DATETIME? > > Do you know the difference between exact and inexact numeric datatypes and > are you sure you want to use inexact numerics for all the Readings? > > Why BIGINT for the IDENTITY column? Do you expect 10^19 rows! Chances are > INTEGER will be more than sufficient. > > Hopefully this has given you some things to think about and lookup in the > documentation. Unfortunately newsgroups aren't the place for design > tutorials. Logical design requires more knowledge of the concepts you are > modelling than can be adequately conveyed in an online discussion. > > -- > David Portas > SQL Server MVP > -- > >
Could you please tell me how to prevent duplicate insertion of data?
Thanks [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 28 Oct 2005 07:28:13 -0700, ktm400 wrote: > > >I forgot to check the keys and constraints box in enterprise manager > >"generate sql script".....this is what it looks like now: > >Iam assuming that the rows will retain integrity....is this a correct > >assumption? > > Hi ktm400, > > No, it's not. The only change from the previous posting is that you > added a PRIMARY KEY constraint on the IDENTITY column. That means that > you still don't have any real keys: a key on an IDENTITY will never > prevent the accidental duplicate insertion of data. > > All other points mentioned yesterday by David also still apply. > > So we have (quoted almost verbatim from David's message): > > * No other keys than identity = no integrity > > * The numbered columns 1-10 look like what's usually called a Repeating > Group. Repeating groups are a fundamentally bad design for lots of > important reasons. > > * "Counter", "Date" and "Time" are pretty useless column names. They > tell us virtually nothing about the attributes in question. Names are > important, don't neglect them. > > * Why two CHAR columns for date and time? Why not DATETIME? > > * Do you know the difference between exact and inexact numeric datatypes > and are you sure you want to use inexact numerics for all the Readings? > > * Why BIGINT for the IDENTITY column? Do you expect 10^19 rows! Chances > are INTEGER will be more than sufficient. > > And to this list, I'd like to add: > > * No CHECK constraint for any of the columns. Though I know nothing > aboput the field of business you're in, I have a hunch that at least the > columns Blow and BlowDetectorOn should allow only a limited set of > values. Also, would you really want to accept data with MaxThickness > less than MinThickness? Or with TargetThickness not somewhere between > those two? > > * No FOREIGN KEY constraints. I think that BlankID is intended to refer > to data in another column, but without FOREIGN KEY constraint, it's very > unlikely that it will really do so. > > Please read some basic books on database design before proceeding. > Building a database is like building a home: you can attempt to do it > without ever learning how to lay bricks, and you might even build > something that looks like a house - but it'll probably all come tumbling > down the moment you slam a door. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
[quoted text, click to view]
On Fri, 28 Oct 2005 07:28:13 -0700, ktm400 wrote: >I forgot to check the keys and constraints box in enterprise manager >"generate sql script".....this is what it looks like now: >Iam assuming that the rows will retain integrity....is this a correct >assumption? Hi ktm400, No, it's not. The only change from the previous posting is that you added a PRIMARY KEY constraint on the IDENTITY column. That means that you still don't have any real keys: a key on an IDENTITY will never prevent the accidental duplicate insertion of data. All other points mentioned yesterday by David also still apply. So we have (quoted almost verbatim from David's message): * No other keys than identity = no integrity * The numbered columns 1-10 look like what's usually called a Repeating Group. Repeating groups are a fundamentally bad design for lots of important reasons. * "Counter", "Date" and "Time" are pretty useless column names. They tell us virtually nothing about the attributes in question. Names are important, don't neglect them. * Why two CHAR columns for date and time? Why not DATETIME? * Do you know the difference between exact and inexact numeric datatypes and are you sure you want to use inexact numerics for all the Readings? * Why BIGINT for the IDENTITY column? Do you expect 10^19 rows! Chances are INTEGER will be more than sufficient. And to this list, I'd like to add: * No CHECK constraint for any of the columns. Though I know nothing aboput the field of business you're in, I have a hunch that at least the columns Blow and BlowDetectorOn should allow only a limited set of values. Also, would you really want to accept data with MaxThickness less than MinThickness? Or with TargetThickness not somewhere between those two? * No FOREIGN KEY constraints. I think that BlankID is intended to refer to data in another column, but without FOREIGN KEY constraint, it's very unlikely that it will really do so. Please read some basic books on database design before proceeding. Building a database is like building a home: you can attempt to do it without ever learning how to lay bricks, and you might even build something that looks like a house - but it'll probably all come tumbling down the moment you slam a door. Best, Hugo --
[quoted text, click to view]
On Fri, 28 Oct 2005 13:37:42 -0700, ktm400 wrote: >Could you please tell me how to prevent duplicate insertion of data? >Thanks Hi ktm400, Step 1: Investigate the information needed by the business that's going to use the database. Make sure that you understand the business rules that apply to the information. Get to know how the different data elements interact, which facts are functionally dependent on which other facts, etc. Step 2: Use the information gathered in step 1 to normalize the table design to at least third, preferably fifth normal form. Step 3: Create PRIMARY KEY and/or UNIQUE constraints. The hardest part is step 1. Unfortunately, this is also the part that can't be easily explained in newsgroup postings. I've been teaching data modeling for some years: one three-hour session weekly, and enough homework to keep the students busy for another two to four hours. The first nine weeks were devoted to step 1. I only needed lesson #10 to explain step 2. The last four lessons were then reserved to prepare the students for the examination. Step 3 was not included in the course at all - once a complete and correct data model is known, translating it into a correct CREATE TABLE statement is elementary. Best, Hugo --
Thank you
[quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 28 Oct 2005 13:37:42 -0700, ktm400 wrote: > > >Could you please tell me how to prevent duplicate insertion of data? > >Thanks > > Hi ktm400, > > Step 1: Investigate the information needed by the business that's going > to use the database. Make sure that you understand the business rules > that apply to the information. Get to know how the different data > elements interact, which facts are functionally dependent on which other > facts, etc. > > Step 2: Use the information gathered in step 1 to normalize the table > design to at least third, preferably fifth normal form. > > Step 3: Create PRIMARY KEY and/or UNIQUE constraints. > > The hardest part is step 1. Unfortunately, this is also the part that > can't be easily explained in newsgroup postings. I've been teaching data > modeling for some years: one three-hour session weekly, and enough > homework to keep the students busy for another two to four hours. The > first nine weeks were devoted to step 1. I only needed lesson #10 to > explain step 2. The last four lessons were then reserved to prepare the > students for the examination. Step 3 was not included in the course at > all - once a complete and correct data model is known, translating it > into a correct CREATE TABLE statement is elementary. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |