Field Independence in Normalised Tables

[samah]

Access beginner.

In my database, I have an Employees Tables with the following structure:

tblEmployees:
EmployeeID ) - Autonumber (pk)
EmployeeName - (text)
Terminated? - (Yes/No)
DateTerminated - (Date)
ReasonForTermination - Text
<other fields>

I have been reading about normalization. Under field independence the
rule states that "the data in any field (other than a primary key field)
in a table should be editable without affecting the data in any other
field". Well, in the above table data in the fields "DateTerminated" and
"ReasonForTermination" are totally dependent on the value in the field
"Terminated?". Although the table looks normal, just wondering whether
it breaks the rules of normalization?

Will appreciate if anybody can shed a little light on it.

Thanks in advance.

- samah.

 

[Klatuu]

Excellent answer, Chris.
I would like to add one other piece of advice.
The field [Terminated?] is poorly named. It contains a special character -
the ?

I would advice the OP to use good naming conventions. Names should contain
only letters, numbers, and the underscore character. This excludes any
special or punctuation characters. Also, no Access reserved word should be
used as a name. For example - Name, Date, Type, Count, etc.

--
Dave Hargis, Microsoft Access MVP

Access beginner.

In my database, I have an Employees Tables with the following structure:

tblEmployees:
EmployeeID ) - Autonumber (pk)
EmployeeName - (text)
Terminated? - (Yes/No)
DateTerminated - (Date)
ReasonForTermination - Text
<other fields>

I have been reading about normalization. Under field independence the
rule states that "the data in any field (other than a primary key field)
in a table should be editable without affecting the data in any other
field". Well, in the above table data in the fields "DateTerminated" and
"ReasonForTermination" are totally dependent on the value in the field
"Terminated?". Although the table looks normal, just wondering whether
it breaks the rules of normalization?

Will appreciate if anybody can shed a little light on it.

Thanks in advance.

samah,

The above table is not normalized, it violates 1st Normal Form.

[Terminate?] is a redundant column, and should be removed. The existence of a date in
DateTerminated is the indicator that an employee has been terminated.

Also, after [Terminated?] has been removed, tblEmployees still violates 2nd Normal form.

Employees hire and fire dates are a separate subset of data. Employees can be hired and
fired multiple times in the vast majority of businesses, and so that information must be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)


Sincerely,

Chris O.

 

[Chris2]

Access beginner.

In my database, I have an Employees Tables with the following structure:

tblEmployees:
EmployeeID ) - Autonumber (pk)
EmployeeName - (text)
Terminated? - (Yes/No)
DateTerminated - (Date)
ReasonForTermination - Text
<other fields>

I have been reading about normalization. Under field independence the
rule states that "the data in any field (other than a primary key field)
in a table should be editable without affecting the data in any other
field". Well, in the above table data in the fields "DateTerminated" and
"ReasonForTermination" are totally dependent on the value in the field
"Terminated?". Although the table looks normal, just wondering whether
it breaks the rules of normalization?

Will appreciate if anybody can shed a little light on it.

Thanks in advance.

samah,

The above table is not normalized, it violates 1st Normal Form.

[Terminate?] is a redundant column, and should be removed. The existence of a date in
DateTerminated is the indicator that an employee has been terminated.

Also, after [Terminated?] has been removed, tblEmployees still violates 2nd Normal form.

Employees hire and fire dates are a separate subset of data. Employees can be hired and
fired multiple times in the vast majority of businesses, and so that information must be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)


Sincerely,

Chris O.

 

[samah]

Access beginner.

In my database, I have an Employees Tables with the following structure:

tblEmployees:
EmployeeID ) - Autonumber (pk)
EmployeeName - (text)
Terminated? - (Yes/No)
DateTerminated - (Date)
ReasonForTermination - Text
<other fields>

I have been reading about normalization. Under field independence the
rule states that "the data in any field (other than a primary key field)
in a table should be editable without affecting the data in any other
field". Well, in the above table data in the fields "DateTerminated" and
"ReasonForTermination" are totally dependent on the value in the field
"Terminated?". Although the table looks normal, just wondering whether
it breaks the rules of normalization?

Will appreciate if anybody can shed a little light on it.

Thanks in advance.

samah,

The above table is not normalized, it violates 1st Normal Form.

[Terminate?] is a redundant column, and should be removed. The existence of a date in
DateTerminated is the indicator that an employee has been terminated.

Also, after [Terminated?] has been removed, tblEmployees still violates 2nd Normal form.

Employees hire and fire dates are a separate subset of data. Employees can be hired and
fired multiple times in the vast majority of businesses, and so that information must be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)


Sincerely,

Chris O.

Chris, thank you for your reply.

Here is the Products table from LawTrack Contacts sample database from
Access MVP John L. Viescas' Access 2003 Inside Out.

Products (Software Products)
ProductID AUTONUMBER -- Primary Key
ProductName TEXT
CategoryDescription TEXT
UnitPrice CURRENCY
TrialVersion YES/NO (Is this a trial version?)
TrialExpire NUMBER (If trial version, number of days before
expiration)

In this table, the value in TrialExpire Field is dependent on the value
in the field TrialVersion . Also these two fields are used in a table
validation rule for the products table in this sample database.

Table validation rule talks about the relationship between two or more
fields in a table, isn't it? So can we assume that some kind of
relationship or dependency should exist between fields in a table to
enable us to apply a validation rule for that table? And doesn't it
break the rules of normalization?

Here is a quote from the same book - Page 118- under Defining a Table
Validation Rule. "Table validation rules are handy when the values in
one field are dependent on what's stored in another field".

May be I am so confused that I fail to understand some very important
concept here. Sorry for my ignorance.

Thanks.

- samah

 

[samah]

Excellent answer, Chris.
I would like to add one other piece of advice.
The field [Terminated?] is poorly named. It contains a special character -
the ?

I would advice the OP to use good naming conventions. Names should contain
only letters, numbers, and the underscore character. This excludes any
special or punctuation characters. Also, no Access reserved word should be
used as a name. For example - Name, Date, Type, Count, etc.

Thank you for your advice Klatuu, I appreciate it.

- samah

 

[Chris2]

Excellent answer, Chris.

Dave Hargis,

Thank you!


Sincerely,

Chris O.

 

[Chris2]

samah,

My comments are found in-line below.

Chris, thank you for your reply.

Here is the Products table from LawTrack Contacts sample database from
Access MVP John L. Viescas' Access 2003 Inside Out.

Products (Software Products)
ProductID AUTONUMBER -- Primary Key
ProductName TEXT
CategoryDescription TEXT
UnitPrice CURRENCY
TrialVersion YES/NO (Is this a trial version?)
TrialExpire NUMBER (If trial version, number of days before
expiration)

In this table, the value in TrialExpire Field is dependent on the value
in the field TrialVersion . Also these two fields are used in a table
validation rule for the products table in this sample database.

This Produts table does not appear to be fully normalized based on the above description.

However, I do not have the book and cannot fully analyze it or the database.

If the book does not explain why things were done the way they were done, it is very
difficult for me to comment otherwise.

I hesitate to do so because John Viescas has a lot more experience than I do.

Table validation rule talks about the relationship between two or more
fields in a table, isn't it?

"Relationship" means many things.

In the MS Access world, it means Foreign Key references between tables to assure
Referential Integrity.

It is rare, but you can declare a Foreign Key from one column of a table to another column
(I've never done it myself).

On the other hand, when "relationship" is used more generically, it can be discussing any
form of association.

So, that was a long-winded way of saying that I don't know what your book is saying
because I don't have it.

So can we assume that some kind of
relationship or dependency should exist between fields in a table to
enable us to apply a validation rule for that table? And doesn't it
break the rules of normalization?

A "dependency" in normalization is one of factual correctness.

When you have products that have colors, then your Product entity will have an attribute
called Color, and that attribute that is dependent on the primary key of Product.

But there is no MS Access Relationship (Foreign Key) between whatever column or columns
make up the Primary Key and the the color column.

Part of the rules of normalization exist to make sure that all attributes of an entity
(i.e. columns of a table) are related (the generic use of the word).


One of my pet peeves about MS Access is that it "robbed" the use of "relationship" to
replace the term referential integrity constraints (which it should not have done, in my
opinion).

Here is a quote from the same book - Page 118- under Defining a Table
Validation Rule. "Table validation rules are handy when the values in
one field are dependent on what's stored in another field".

Yes, this is true. That can be very useful.

That subject is not "related" (I'm using the word generically right there) to MS Access
Relationships or database normalization.

May be I am so confused that I fail to understand some very important
concept here. Sorry for my ignorance.

Thanks.

- samah

Here's my big discussion on this:

Part of the trouble about understanding relationships could come from the fact that
Relation, Relations, and Relationship(s) have multiple meanings in regards to "Relational
Databases."

One meaning is that the underlying theory behind Relational Databases is a branch of math
called Relational Algebra (invented by Dr. Codd). This branch of math works on "sets" of
data. A "set" in Relational Algebra is called a "relation". Then, some people in various
companies (IBM, Oracle, etc.) took this branch of math and made it into the precursors of
the Relational Databases software we have today. They grabbed "relational" off of
Relational Algebra and stuck it in front of "database", and voila, we have "Relational
Databases".

Another meaning is that "Relation" is the technical name of what Relational Databases call
"Tables". (Just to be complete, the technical name of "Row" is "Tuple".)

Now, in order to run our databases, we have something that goes by the technical term,
Referential Integrity. This states that if we start with table called Items, that has a
Primary Key of UPC, and we also have table called Pricing, that has a primary key of UPC
and PricingDate, then whatever happens, we do not want to have a row in the Pricing table
that has an UPC value that doesn't appear in the Items table. Why? Because we would have
prices in Pricing for items no one in the company could identify (there being nothing in
Items to identify the item). Relational Databases use internal management, usually
indexes, to *enforce* a "rule" that automatically prevents users from deleting any row in
Items . . . *when there is still any row* . . . in Pricing with the UPC about to be
deleted from Items. This process, this "enforcement" of the "rules", is called
Referential Integrity. It is one of the most critical features of any Relational
Database.

Now, in MS Access, you open up the "Relationships" window, and make some table sub-windows
appear on it. Then you drag fields back and forth between the table sub-windows and
establish nice little lines that go from table to table, and MS Access calls these lines
"Relationships" (and a lot of other people do, too). In this case, an MS Access
"Relationship" is the method whereby "Referential Integrity" is set up. It should be
noted that MS Access uses the word "relationship" in completely different ways than other
database products, and differently from Data Modeling (more on that in a second). This
alternate usage is so pervasive on the PC desktop world that terminology creep has taken
place, causing the MS Access version of "relationship" to be thought of by many as "what
real relationships are in databases".

To further confuse the issue (yes, there's more), Data Modeling, the process where ideas
about things (entities) are organized into understandable formats, like big charts covered
by boxes with lines running between them (rather remarkably like the boxes and lines in
the MS Access Relationships window), also uses the concept of "relationships". Except in
Data Modeling, a "relationship" is an actual box on the chart (an entity), not the lines
running between them.

Data Models themselves are built to help database designers create good databases. When
all the aspects of the chart (there can be many versions and detail levels) are completed,
and I'm skipping over a lot of stuff here for simplicity, the chart is turned over to the
database designers, and they go to work.

The boxes on a Data Modeling chart represent "entities", or the people, places, things, or
knowledge that are being described on the chart. A "relationship", then, is a form of
knowledge because it *tells* us something. Therefore, it gets a box on the chart. And
here's another one, the lines on a Data Model chart? They represent something called
Cardinality. Cardinality is one to one, one to many, many to many, or recursive (Bill of
Materials).

To yet further confuse things, the word "relationship" *frequently* replaces "Cardinality"
even when experts are discussing the situation. It's important to understand all this,
because a table of a concrete entity (like an Book) can have a many to many "cardinality"
with an abstract piece of knowledge (a relationship). If a Book can be checked out of
library by many people, the book does not have a one to many cardinality with people,
instead there is a "relationship" between books and people, and books have a cardinality
with that relationship, and people have a cardinality with that relationship (we'll call
it a CheckedOut relationship).

Concrete Entity: Books
Concrete Entity: People

Abstract Entity: CheckedOut

Both Books and People can appear in CheckedOut over an over again, as the same person
checks out and reads the same book over and over again. The check-out date will be the
attribute ("CheckedOutDate") of CheckedOut that helps define each row. The primary key
would be, BookID, PeopleID, and CheckedOutDate. The "relationship" between books and
people is that people can check the books out of the library. The two cardinalities from
the two concrete entities to the one abstract relationship entity are the one-to-many
(etc.) part of it and together form a many-to-many cardinality (i.e. relationship in MS
Access terms).

Yes, cardinality is *rarely* discussed by MS Access users. But it is still important to
realize that it exists. It is further necessary to realize that what MS Access "calls" a
"relationship" is really called something else by a large fraction of Data Modelers and
Database Designers. It's important because you have to realize that a real-world entity,
a "bit of knowledge", may be a "relationship" between other entities and that the
"relationship" may be described in the database by a table (also known as a "relation").
And in MS Access, the cardinality between the tables is called a "relationship", as well.

Don't ask me why the MS Access designers decided to rob an already massively overused term
and apply it the way they did, but they did, and knowing about it can be quite helpful in
my opinion.


I can only hope this helped clear up all the different ways that the word relationship
gets used in the overall database world.


Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000 (not significantly
changed by Access 2007, see the article's own references at the end to material from the
early 1990s).
http://support.microsoft.com/support/kb/articles/q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right describing many important
aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html



Sincerely,

Chris O.

 

[John W. Vinson]

If the book does not explain why things were done the way they were done, it is very
difficult for me to comment otherwise.

I hesitate to do so because John Viescas has a lot more experience than I do.

I think it was from John Viescas that I got the phrase I now include in my
resume: "Judicious denormalization, only when needed".

Sometimes being a real expert involves knowing when to break the rules - and
bear in mind that two or three real experts could have two or three different
opinions on when to do so!

John W. Vinson [MVP]

 

[samah]

samah,

My comments are found in-line below.




This Produts table does not appear to be fully normalized based on the above description.

However, I do not have the book and cannot fully analyze it or the database.

If the book does not explain why things were done the way they were done, it is very
difficult for me to comment otherwise.

I hesitate to do so because John Viescas has a lot more experience than I do.



"Relationship" means many things.

In the MS Access world, it means Foreign Key references between tables to assure
Referential Integrity.

It is rare, but you can declare a Foreign Key from one column of a table to another column
(I've never done it myself).

On the other hand, when "relationship" is used more generically, it can be discussing any
form of association.

So, that was a long-winded way of saying that I don't know what your book is saying
because I don't have it.



A "dependency" in normalization is one of factual correctness.

When you have products that have colors, then your Product entity will have an attribute
called Color, and that attribute that is dependent on the primary key of Product.

But there is no MS Access Relationship (Foreign Key) between whatever column or columns
make up the Primary Key and the the color column.

Part of the rules of normalization exist to make sure that all attributes of an entity
(i.e. columns of a table) are related (the generic use of the word).


One of my pet peeves about MS Access is that it "robbed" the use of "relationship" to
replace the term referential integrity constraints (which it should not have done, in my
opinion).



Yes, this is true. That can be very useful.

That subject is not "related" (I'm using the word generically right there) to MS Access
Relationships or database normalization.





Here's my big discussion on this:

Part of the trouble about understanding relationships could come from the fact that
Relation, Relations, and Relationship(s) have multiple meanings in regards to "Relational
Databases."

One meaning is that the underlying theory behind Relational Databases is a branch of math
called Relational Algebra (invented by Dr. Codd). This branch of math works on "sets" of
data. A "set" in Relational Algebra is called a "relation". Then, some people in various
companies (IBM, Oracle, etc.) took this branch of math and made it into the precursors of
the Relational Databases software we have today. They grabbed "relational" off of
Relational Algebra and stuck it in front of "database", and voila, we have "Relational
Databases".

Another meaning is that "Relation" is the technical name of what Relational Databases call
"Tables". (Just to be complete, the technical name of "Row" is "Tuple".)

Now, in order to run our databases, we have something that goes by the technical term,
Referential Integrity. This states that if we start with table called Items, that has a
Primary Key of UPC, and we also have table called Pricing, that has a primary key of UPC
and PricingDate, then whatever happens, we do not want to have a row in the Pricing table
that has an UPC value that doesn't appear in the Items table. Why? Because we would have
prices in Pricing for items no one in the company could identify (there being nothing in
Items to identify the item). Relational Databases use internal management, usually
indexes, to *enforce* a "rule" that automatically prevents users from deleting any row in
Items . . . *when there is still any row* . . . in Pricing with the UPC about to be
deleted from Items. This process, this "enforcement" of the "rules", is called
Referential Integrity. It is one of the most critical features of any Relational
Database.

Now, in MS Access, you open up the "Relationships" window, and make some table sub-windows
appear on it. Then you drag fields back and forth between the table sub-windows and
establish nice little lines that go from table to table, and MS Access calls these lines
"Relationships" (and a lot of other people do, too). In this case, an MS Access
"Relationship" is the method whereby "Referential Integrity" is set up. It should be
noted that MS Access uses the word "relationship" in completely different ways than other
database products, and differently from Data Modeling (more on that in a second). This
alternate usage is so pervasive on the PC desktop world that terminology creep has taken
place, causing the MS Access version of "relationship" to be thought of by many as "what
real relationships are in databases".

To further confuse the issue (yes, there's more), Data Modeling, the process where ideas
about things (entities) are organized into understandable formats, like big charts covered
by boxes with lines running between them (rather remarkably like the boxes and lines in
the MS Access Relationships window), also uses the concept of "relationships". Except in
Data Modeling, a "relationship" is an actual box on the chart (an entity), not the lines
running between them.

Data Models themselves are built to help database designers create good databases. When
all the aspects of the chart (there can be many versions and detail levels) are completed,
and I'm skipping over a lot of stuff here for simplicity, the chart is turned over to the
database designers, and they go to work.

The boxes on a Data Modeling chart represent "entities", or the people, places, things, or
knowledge that are being described on the chart. A "relationship", then, is a form of
knowledge because it *tells* us something. Therefore, it gets a box on the chart. And
here's another one, the lines on a Data Model chart? They represent something called
Cardinality. Cardinality is one to one, one to many, many to many, or recursive (Bill of
Materials).

To yet further confuse things, the word "relationship" *frequently* replaces "Cardinality"
even when experts are discussing the situation. It's important to understand all this,
because a table of a concrete entity (like an Book) can have a many to many "cardinality"
with an abstract piece of knowledge (a relationship). If a Book can be checked out of
library by many people, the book does not have a one to many cardinality with people,
instead there is a "relationship" between books and people, and books have a cardinality
with that relationship, and people have a cardinality with that relationship (we'll call
it a CheckedOut relationship).

Concrete Entity: Books
Concrete Entity: People

Abstract Entity: CheckedOut

Both Books and People can appear in CheckedOut over an over again, as the same person
checks out and reads the same book over and over again. The check-out date will be the
attribute ("CheckedOutDate") of CheckedOut that helps define each row. The primary key
would be, BookID, PeopleID, and CheckedOutDate. The "relationship" between books and
people is that people can check the books out of the library. The two cardinalities from
the two concrete entities to the one abstract relationship entity are the one-to-many
(etc.) part of it and together form a many-to-many cardinality (i.e. relationship in MS
Access terms).

Yes, cardinality is *rarely* discussed by MS Access users. But it is still important to
realize that it exists. It is further necessary to realize that what MS Access "calls" a
"relationship" is really called something else by a large fraction of Data Modelers and
Database Designers. It's important because you have to realize that a real-world entity,
a "bit of knowledge", may be a "relationship" between other entities and that the
"relationship" may be described in the database by a table (also known as a "relation").
And in MS Access, the cardinality between the tables is called a "relationship", as well.

Don't ask me why the MS Access designers decided to rob an already massively overused term
and apply it the way they did, but they did, and knowing about it can be quite helpful in
my opinion.


I can only hope this helped clear up all the different ways that the word relationship
gets used in the overall database world.


Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000 (not significantly
changed by Access 2007, see the article's own references at the end to material from the
early 1990s).
http://support.microsoft.com/support/kb/articles/q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right describing many important
aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html



Sincerely,

Chris O.

Chris,

Thank you for your time and guidance and thanks again to Dave Hargis.

- samah

 

[samah]

I think it was from John Viescas that I got the phrase I now include in my
resume: "Judicious denormalization, only when needed".

Sometimes being a real expert involves knowing when to break the rules - and
bear in mind that two or three real experts could have two or three different
opinions on when to do so!

John W. Vinson [MVP]

Thank you, John. I have just started with the book and in the later
chapters I hope to find more on denormalization and when to break the
rules. Anyway it is an excellent book that I could find on ms access.
John Viescas is a great teacher!

- samah

 

[Jamie Collins]

"Relation" is the technical name of what Relational Databases call
"Tables".

One of my pet peeves about MS Access is that it "robbed" the use of "relationship" to
replace the term referential integrity constraints (which it should not have done, in my
opinion).

One of my pet peeves about MS Access is DAO (<g>), which has a collection
class consisting of such 'Relationships' named 'Relations'!!

Note that MS Access 'Relationships' is not the same as concept as an
engine-level FOREIGN KEY (DRI). I can create a MS Access 'Relationship' that
does not enforce referential integrity, one between columns of wildly
different data types, etc. Hence the need for a new term with a suitably
vague meaning.

Jamie.

--

 

[Jamie Collins]

Employees hire and fire dates are a separate subset of data. Employees can be hired and
fired multiple times in the vast majority of businesses, and so that information must be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)

"Must"? Temporal databases are hard work in SQL. For one reason or another,
many (most?) tables model the current (or most recent) state. I note your
proposed design lacks a sequenced primary key. Care to declare which Normal
Form it is in? Does "fully normalized" mean 6NF? Isn't 'ReasonForTermination'
an attribute of a 'termination', therefore does not belong in the
'employment' table?

Jamie.

--

 

[Chris2]

"Chris2" wrote:

Note that MS Access 'Relationships' is not the same as concept as an
engine-level FOREIGN KEY (DRI). I can create a MS Access 'Relationship' that
does not enforce referential integrity, one between columns of wildly
different data types, etc. Hence the need for a new term with a suitably
vague meaning.

Jamie,

When using MS Access Relationships, I always enforce RI, and never create one between
columns of different *attributes*, much less different data types, so I tend not to think
of that aspect.

The main reason for that is that I always create them via DDL, and so "Enforce RI" is
automatically set, which is what I want.

It is not immediately obvious to me what good they would be if not used to enforce RI.
I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.


Sincerely,

Chris O.

 

[Chris2]

Jamie,

Comments in-line.

"Must"?

"Must" from my point of view only.

Does "fully normalized" mean 6NF?

Yes it does.

I didn't say that I was shooting for full normalization when I gave the above. (When I
said "fully normalized" later, I was only commenting on the table design presented, which
I also further qualified by stating that I didn't have the full db available to comment
on.)

For the level of the discussion, shooting for the range of 1-3NF is often pretty good.

Isn't 'ReasonForTermination'
an attribute of a 'termination', therefore does not belong in the
'employment' table?

Jamie.

I was looking at that column, thinking it should be moved to a different table, even as I
wrote the table out. I even had a EmployeeTerminationHistory table started (which I
deleted).

To me, the normalize/denormalize decision would involve questions about the overall
structure, extent, and purpose of the rest of the db, something I didn't know and felt was
beyond the scope of the discussion.

I also skipped normalize/denormalize discussions in regard to it for the same reason.

(The OP discussed just getting into this.)


Sincerely,

Chris O.

 

[Jamie Collins]

When using MS Access Relationships, I always enforce RI, and never create one between
columns of different *attributes*, much less different data types, so I tend not to think
of that aspect.

It is not immediately obvious to me what good they would be if not used to enforce RI.

IIRC MS Access uses a 'Relationship' to automatically create a JOIN when
both tables are pulled into the query builder tool thing (another one of

I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.

Note that 'Relationships' are not a feature of the engine.

Jamie.

--

 

[Jamie Collins]

Jamie,

Comments in-line.

Interesting interpretation of 'in line': you snipped out

Your proposed schema offers no means of uniquely identifying data e.g. it
will allow this:

INSERT INTO Employment (EmployeeID,
DateHired, DateTerminated)
VALUES (1, #2007-01-01 00:00:00#, #2007-01-31 00:00:00#)
;
INSERT INTO Employment (EmployeeID,
DateHired, DateTerminated)
VALUES (1, #2007-10-01 00:00:00#, NULL)
;

not to mention other funky combinations e.g. termination reason with NULL
DateTerminated, DateTerminated before DateHired, etc. It doesn't matter what
Normal Form a table is in if it's not fit for purpose. Care to add the
missing constraints now?

For the level of the discussion, shooting for the range of 1-3NF is often pretty good.

You opinion. I'd be looking for 5NF, especially from someone critiquing in
the same thread another's design as regards Normal Form violations.

Jamie.

--

 

[John W. Vinson]

It is not immediately obvious to me what good they would be if not used to enforce RI.
I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.

About the ONLY benefit, and it's a very tiny one, is that if there is a
nonenforced relationship between two tables it will default to creating that
Join when you use the query builder and add those two tables.

I never use the feature either.

John W. Vinson [MVP]

 

[Chris2]

IIRC MS Access uses a 'Relationship' to automatically create a JOIN when
both tables are pulled into the query builder tool thing (another one of
those things us 'real' SQL coder don't use <g>). Not all tables joined in
queries will have RI e.g. auxiliary tables such as Calendar.

Jamie,

Thank you for the tip. I hadn't know that.


Sincerely,

Chris O.

 

[Chris2]

About the ONLY benefit, and it's a very tiny one, is that if there is a
nonenforced relationship between two tables it will default to creating that
Join when you use the query builder and add those two tables.

I never use the feature either.

John W. Vinson [MVP]

John Vinson,

Thank you!


Sincerely,

Chris O.

 

[Chris2]

Yes, you're correct. The PK selection was insufficient. It should have included the
first three columns.

You opinion. I'd be looking for 5NF, especially from someone critiquing in
the same thread another's design as regards Normal Form violations.

How do you rate the normalization of table that was presented from the book in question?


Sincerely,

Chris O.