Meaning of "first normal form."

Meaning of "first normal form."

Post by Darryl J. Darwen » Wed, 13 Aug 1997 04:00:00



Hi there:

I am in the process of independently learning database theory. My
particuliar interest is in relational databases, with a set goal of
understanding nested databases.

Your help would be appreciated in confirming my understanding of the
meaning of "first normal form." Is it true that the basis for having a
first normal form is to establish a relation containing a tuple, a
domain, and a primary key joined to another relation by way of a foreign
key?

Either post here or email me. TIA.

Sincerely,

Darryl Darwent
Information Resources Specialist
Petroleum Recovery Institute

 
 
 

Meaning of "first normal form."

Post by Torsten Grus » Thu, 14 Aug 1997 04:00:00


Hi Darryl,


Quote:> [...]
> Your help would be appreciated in confirming my understanding of the
> meaning of "first normal form." Is it true that the basis for having a
> first normal form is to establish a relation containing a tuple, a
> domain, and a primary key joined to another relation by way of a foreign
> key?

It's more simple than that: a relation is in first normal form, if all
its attributes have an atomic type (e.g., int, char, string, ...)
only.  The first normal form disallows relations with relation-valued
-- or, to be more general: non-atomic -- attributes.

However, such non-first normal form (NFNF, or NF^2) models exists (as
a starter, see the work of Schek and Scholl) and their ideas persist
in today's (object) data models and query languages.

HTH,
        --Torsten
--

  |                            http://www.informatik.uni-konstanz.de/~grust/ |
  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

 
 
 

Meaning of "first normal form."

Post by Olivier DAHA » Thu, 14 Aug 1997 04:00:00


The first nomal form states that the fields of each table must
consist of _atomic_ values only and the tables must contain no
repeating fields. So: a table having a field "Name" containing
"john Smith" or "Susan Jones"... is not of 1st form cause values
are not atomic. Two fields (columns) are needed here: "First
name" and 'Last name". Another sample: a table of people who rent
some video tapes can look like (fields): "name", "tape1",
"tape2", "tape3". This table is not in the 1st form cause
"tape.." field is repeated. 1st form is that, just that...

--
Don't worry, be happy ...

 
 
 

Meaning of "first normal form."

Post by MarkP286 » Thu, 14 Aug 1997 04:00:00


Quote:>> However, such non-first normal form (NFNF, or NF^2) models exists (as a

starter, see the work of Schek and Scholl)  <<

If it is not too much trouble could you post the titles of one or two of
their books.

Mark Powell  -- The only advise that counts is the advise that you follow
so follow your own advise

 
 
 

Meaning of "first normal form."

Post by Torsten Grus » Fri, 15 Aug 1997 04:00:00


Hi Mark,


> >> However, such non-first normal form (NFNF, or NF^2) models exists (as a
> starter, see the work of Schek and Scholl)  <<

> If it is not too much trouble could you post the titles of one or two of
> their books.

Hans-Joerg Schek (now ETH Zuerich) and Marc H. Scholl (now at U
Konstanz, and my supervisor for what it's worth :-) published a series
of articles on the NFNF model in the lates 80s.  They also implemented
their ideas on top of the flat relational (i.e. 1NF) DASDBS system.

Here are some references and I hope you might find them useful:

        H.J. Schek and M.H. Scholl
        ``The relational model with relation-valued attributes''
        Information systems, volume 11, no. 2, pages 137-147,
        June 1986

        H.J. Schek, H.B. Paul, M.H. Scholl, and G. Weikum
        ``The DASDBS project: objectives, experiences, and future
          prospects''
        IEEE Transactions on Knowledge and Data Engineering,
        volume 2, no. 1, pages 25-43, 1990

        H.J. Schek and M.H. Scholl
        ``The two roles of nested relations in the DASDBS project''
        in
        S. Abiteboul, P.C. Fischer, and H.J. Schek (editors)
        ``Nested Relations and Complex Objects in Databases''
        Lecture Notes in Computer Science, vol. 361, Berlin, 1989

        M.H. Scholl and H.J. Schek
        ``A relational object model''
        Proceedings of the 3rd Intl. Conference on Database Theory (ICDT)
        Lecture Notes in Computer Science, vol. 470, Paris,
        pages 89-105, 1990

Cheers,
        --Torsten
--

  |                            http://www.informatik.uni-konstanz.de/~grust/ |
  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

 
 
 

Meaning of "first normal form."

Post by Richard Mitchel » Fri, 15 Aug 1997 04:00:00





>> Hi Darryl,


>> > [...]
>> > Your help would be appreciated in confirming my understanding of the
>> > meaning of "first normal form." Is it true that the basis for having a
>> > first normal form is to establish a relation containing a tuple, a
>> > domain, and a primary key joined to another relation by way of a foreign
>> > key?

>> It's more simple than that: a relation is in first normal form, if all
>> its attributes have an atomic type (e.g., int, char, string, ...)
>> only.  

>In what sense is string `atomic'?  In all relational database systems that I
>have
>used, the query language allows you to find sub-strings of a string and to use
>those sub-strings as join conditions in queries, view definitions, etc.  So
>apparently ALL such systems are non-1NF.

>> The first normal form disallows relations with relation-valued
>> -- or, to be more general: non-atomic -- attributes.

>So SQL3 will finally put an end to arguments about what is and what is
>not first normal form, as it will make the distinction VERY blurred!

>Regards

>Peter Thanisch

Atomic values cannot be further reduced without loss of meaning.  

The fact that a tool allows you to extract a subset of, say, last name does not
mean that last name is non-atomic.

Richard Mitchell

 
 
 

Meaning of "first normal form."

Post by Torsten Grus » Fri, 15 Aug 1997 04:00:00



> In what sense is string `atomic'?  In all relational database
> systems that I have  used, the query language allows you to find
> sub-strings of a string and to use those sub-strings as join
> conditions in queries, view definitions, etc.  So  apparently ALL
> such systems are non-1NF.

With respect to the relational model, a string is atomic since it is
not built using the type constructors the relational model provides
you with: `set of' and `tuple of'.

Cheers,
        --Torsten
--

  |                            http://www.informatik.uni-konstanz.de/~grust/ |
  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

 
 
 

Meaning of "first normal form."

Post by Peter Thanis » Fri, 15 Aug 1997 04:00:00



> Hi Darryl,


> > [...]
> > Your help would be appreciated in confirming my understanding of the
> > meaning of "first normal form." Is it true that the basis for having a
> > first normal form is to establish a relation containing a tuple, a
> > domain, and a primary key joined to another relation by way of a foreign
> > key?

> It's more simple than that: a relation is in first normal form, if all
> its attributes have an atomic type (e.g., int, char, string, ...)
> only.  

In what sense is string `atomic'?  In all relational database systems that I have
used, the query language allows you to find sub-strings of a string and to use
those sub-strings as join conditions in queries, view definitions, etc.  So
apparently ALL such systems are non-1NF.

Quote:> The first normal form disallows relations with relation-valued
> -- or, to be more general: non-atomic -- attributes.

So SQL3 will finally put an end to arguments about what is and what is
not first normal form, as it will make the distinction VERY blurred!

Regards

Peter Thanisch

 
 
 

Meaning of "first normal form."

Post by Eric Boh » Fri, 15 Aug 1997 04:00:00




>> In what sense is string `atomic'?  In all relational database
>> systems that I have  used, the query language allows you to find
>> sub-strings of a string and to use those sub-strings as join
>> conditions in queries, view definitions, etc.  So  apparently ALL
>> such systems are non-1NF.

>With respect to the relational model, a string is atomic since it is
>not built using the type constructors the relational model provides
>you with: `set of' and `tuple of'.

>Cheers,
>    --Torsten
>--

>  |                            http://www.informatik.uni-konstanz.de/~grust/ |
>  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

Maybe I'm misunderstanding what you are saying but 1NF has nothing to do with
data types.  In fact, it does not even require your attributes to be atomic,
only that they be non-repeating within an instance of the relation.

Eric Bohn

 
 
 

Meaning of "first normal form."

Post by Jens Lufte » Sat, 16 Aug 1997 04:00:00


There still seems to be some misunderstandings regarding 1NF.

'atomic' or 'exactly one value at each intersection of column and row'
or 'no repeating groups' has to be seen with respect to the
DATABASE SYSTEM and its query language.
That is, the query language itself cannot 'see into' an atomic value.
There are no relational operators like join or projection using
only parts of such a value.

BUT there is no prohibition in 1NF that such values cannot be structured
internally. They can be values of any datatype - primitives like
integer, fields like strings or of any complex user-defined class or
structure.
You can construct datatypes including methods accessing these types.
You can use them in, say, SQL as easy as you use addition with
integers.
All this is conform to 1NF.
Much irritation rises from the fact that SQL and most relational
database systems only support rather primitive types.
This ist not the last word, however.

The main point is, as I said, that the values has to be atomic
wrt. the relational operators.

See C.J. Date, Relational Database Writings 1991-1994, Addison Wesley
or something like this for further reference.

Jens
---

 
 
 

Meaning of "first normal form."

Post by Jason Mirsk » Sat, 16 Aug 1997 04:00:00


Date, CJ (1995) An Introduction to Database Systems (6th edition)
p296 "A relation is in 1NF if and only if all underlying domains contain
scalar values only."

Data in 1NF relations can be at risk to update anomalies and the like.
Hence the higher forms...

-Jason

 
 
 

Meaning of "first normal form."

Post by KE Fe » Sat, 16 Aug 1997 04:00:00



Quote:

>The first nomal form states that the fields of each table must
>consist of _atomic_ values only and the tables must contain no
>repeating fields. So: a table having a field "Name" containing
>"john Smith" or "Susan Jones"... is not of 1st form cause values
>are not atomic. Two fields (columns) are needed here: "First
>name" and 'Last name". Another sample: a table of people who rent
>some video tapes can look like (fields): "name", "tape1",
>"tape2", "tape3". This table is not in the 1st form cause
>"tape.." field is repeated. 1st form is that, just that...

>--
>Don't worry, be happy ...

_Very_ _Unhappy_...
1NF states no such thing. There is no use of the words _table_ or _fields_ in
the definition of 1NF. I recommend that you review E. F. Codd or C. J. Date.
Their treatment of these subjects, esp. Date's, is exceedingly rigorous. BTW,
1NF hinges on _domains_ and their respective _values_. As far as I can tell,
your examples above are both 1NF.(based on functional dependence and domain
values).

--
                      \\///
                      (0-0)
           +----oOO----(_)-----------+
           |       K. E. Fein        |
           |    Database Analyst     |

           | www.primenet.com\~kfein |
           +------------------oOO----+
                     |  |  |
                     |__|__|
                      || ||
                     ooO Ooo

 
 
 

Meaning of "first normal form."

Post by Richard Mitchel » Sat, 16 Aug 1997 04:00:00


In article , "Darryl says...


>> Atomic values cannot be further reduced without loss of meaning.

>>The fact that a tool allows you to extract a subset of, say, last name does not
>> mean that last name is non-atomic.

>> Richard Mitchell

>Whoa, posse time, guys. You know the posse goes only as fast as the
>slowest horse.

>I have seen references to atomic in the literature and now here. Torsten
>referred to it as int, char, and string in offline comm. By virtue of
>the name and these examples I get the impression that they are very
>small units of code. Correct?? What are their importance?

>Darryl

Atomic in this context is not referring to code at all.  Atomic is referring to
values. This discussion thread centers around the exact definitions of 1st
Normal Form and atomic values.  As I mentioned, atomic values cannot be reduced
without loss of meaning.

Why do we care? It is generally conceded (Pascal, et.al.) that relational
databases require normalization; that is, a minimum requirement is 1st normal
form.  Thus, the importance of defining 1st normal form and how and whether it,
by definition,  embraces atomic values is at the heart of relational database
theory.

Here are some examples of what we're trying to avoid in our relational table's
attributes:

1)  Comma delimited strings:
   Mary,John,Frank,Bill,Sally

2) Values that can be further reduced, such as full name:
   John Smith

3) Multiple attributes that repeat, such as is common in COBOL and is used
elsewhere:
   ADDRESS1    
   ADDRESS2
   ADDRESS3

4) Encoded strings that are comprised of contatenated substrings, often used for
part numbers:
   A12-00089-4218-9999

I suppose you got the basic idea at some previous paragraph. :)

We need to be able to identify the smallest possible meaningful value and use
that to utilize elements in a set or to identify corresponding subsets of other
sets. If we're not at the smallest meaningful value, then we have ambiguity.
For instance, in item 1 above, the comma delimited example, "Sally" is not a
value until we parse it out and therefore we can reasonably say that it just
simply is not a distinct value and is virtually useless because it is mired in
the comma delimited context.

There's a lot of internet sites and plenty of good books that can give you a
more complete explanation.

HTH,
Richard Mitchell

 
 
 

Meaning of "first normal form."

Post by Eric Boh » Sat, 16 Aug 1997 04:00:00



>Hi Eric,

>Eric Bohn writes:

>> >With respect to the relational model, a string is atomic since it is
>> >not built using the type constructors the relational model provides
>> >you with: `set of' and `tuple of'.

>> [...]

>> Maybe I'm misunderstanding what you are saying but 1NF has nothing to do with
>> data types.  In fact, it does not even require your attributes to be atomic,
>> only that they be non-repeating within an instance of the relation.

>Well, if you don't believe me, then let me quote (I hope you recognize
>the authors):

>    Serge Abiteboul, Richard Hull, Victor Vianu
>    ``Foundations of Databases''
>    page 265: "... A relation is in first normal form (1NF) if
>                   each column contains atomic values."

>    Ab Silberschatz, Hank Korth, S. Sudarshan
>    ``Database System Concepts'' (3rd ed.)
>    page 247: "... We say that a relation schema R is in first
>               normal form (1NF) if the domains of all attributes
>               of R are atomic.  A domain is atomic if elements
>               of the domain are considered to be indivisible
>               units."

>1NF is a schema-related concept (concerning attributes and their flat
>domains) and is in no way instance-related, as you say.

>Cheers,
>    --Teggy
>--

>  |                            http://www.informatik.uni-konstanz.de/~grust/ |
>  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

Hi Teg.
I have seen that definition in other texts as well.  I believe that developing
atomic data elements is critcal but I'm not convinced that it is part of the
normalization rules originally proposed by Codd.  Let me quote the following
from Tom Bruce's book "Designing Quality Databases with IDEF1X Information
models" Page 212:

"An entity is in its first normal form if each of its attributes has exactly one
value in each instance."

He goes on to say (same page):

"Although not part of the precise definition of first normal form, it is often
usefull to add the following.

Every attribute must have a single meaning."

Here, I interpret single meaning as atomic domain and veiw an instance of the
entity as a table record where the values of the table non-key fields are
defined based on an instantiation of the value(s) of the table's key field(s).

I was taught a 1NF definition similar to Bruce's in training I had several years
ago.  Although I have not researched Codd's formal definitions, I consider Bruce
an authority.  In any case, can you see how you could have atomic data elements
and still have repeating values within an instance of the entity/relation?  This
problem is not fixed by applying the normalization rule identified by the
sources you identified.  Therefore I consider their 1NF definition to be
incomplete and possibly incorrect.

- Eric

 
 
 

Meaning of "first normal form."

Post by Torsten Grus » Sat, 16 Aug 1997 04:00:00


Hi Eric,

Quote:Eric Bohn writes:

> >With respect to the relational model, a string is atomic since it is
> >not built using the type constructors the relational model provides
> >you with: `set of' and `tuple of'.

> [...]

> Maybe I'm misunderstanding what you are saying but 1NF has nothing to do with
> data types.  In fact, it does not even require your attributes to be atomic,
> only that they be non-repeating within an instance of the relation.

Well, if you don't believe me, then let me quote (I hope you recognize
the authors):

        Serge Abiteboul, Richard Hull, Victor Vianu
        ``Foundations of Databases''
        page 265: "... A relation is in first normal form (1NF) if
                   each column contains atomic values."

        Ab Silberschatz, Hank Korth, S. Sudarshan
        ``Database System Concepts'' (3rd ed.)
        page 247: "... We say that a relation schema R is in first
                   normal form (1NF) if the domains of all attributes
                   of R are atomic.  A domain is atomic if elements
                   of the domain are considered to be indivisible
                   units."

1NF is a schema-related concept (concerning attributes and their flat
domains) and is in no way instance-related, as you say.

Cheers,
        --Teggy
--

  |                            http://www.informatik.uni-konstanz.de/~grust/ |
  | Database Research Group, University of Konstanz (Lake Constance/Germany) |

 
 
 

1. Meaning of message "First Chance Exception"

In an application which uses MFC CDatabase and CRecordset to access some
data files, I frequently get the following message in my output window:

"First Chance Exception in XXXX" where XXXX is the name of a DLL.

Does anyone know what causes this message.

2. Online database

3. How to use "unload form", "form.hide", "form.visible =false "?

4. Can I copy a script to the clipboar

5. max of ("...","...","..")

6. Stumped with execute

7. tnsping times, what's "normal"?

8. Converting int dates to datetime

9. Normal user to run "onmode -ky"

10. Wintegrate Import "Normal" mode

11. Meaning of "Reads" column in Profiler

12. Meaning of "Bad Request"?

13. Meaning of "GO" in Transact SQL