[RESOLVED] Am I doing this 2NF in the right way?
Hello, I'm learning normalization and I wonder if I'm doing this right, and does this database need 3NF:
Un-normalized
Code:
DEPARTMENT
+-------------------------------------------------------------------------------+
| Dname | Dnumber | Dmgr_ssn | Dlocations |
|-------------------------------------------------------------------------------+
| Research | 5 | 333445555 | { Bellaire, Sugarland, Houston } |
|-------------------------------------------------------------------------------+
| Administration | 4 | 987654321 | { Stafford } |
|-------------------------------------------------------------------------------+
| Headquarters | 1 | 888665555 | { Houston } |
+-------------------------------------------------------------------------------+
1NF
Code:
DEPARTMENT
+--------------------------------------------------------+
| Dname | Dnumber | Dmgr_ssn | Dlocation |
|--------------------------------------------------------|
| Research | 5 | 333445555 | Bellaire |
|--------------------------------------------------------|
| Research | 5 | 333445555 | Sugarland |
|--------------------------------------------------------|
| Research | 5 | 333445555 | Houston |
|--------------------------------------------------------|
| Administration | 4 | 987654321 | Stafford |
|--------------------------------------------------------|
| Headquarters | 1 | 888665555 | Houston |
+--------------------------------------------------------+
2NF
Code:
DEPARTMENT
+------------------------------------------+
| Dname | Dnumber | Dmgr_ssn |
|------------------------------------------|
| Research | 5 | 333445555 |
|------------------------------------------|
| Administration | 4 | 987654321 |
|------------------------------------------|
| Headquarters | 1 | 888665555 |
+------------------------------------------+
LOCATION_MAP
+-----------------+
| Dnumber | LocID |
|-----------------|
| 5 | 1 |
|-----------------|
| 5 | 2 |
|-----------------|
| 5 | 3 |
|-----------------|
| 4 | 4 |
|-----------------|
| 1 | 3 |
+-----------------+
LOCATIONS
+----------------------------+
| LocID | Location |
|----------------------------|
| 1 | Bellaire |
|----------------------------|
| 2 | Sugarland |
|----------------------------|
| 3 | Houston |
|----------------------------|
| 4 | Stafford |
+----------------------------+
Re: Am I doing this 2NF in the right way?
at first sight one may conclude it is a well normalized system
on closer look, it is a very contrived example
why ?
because it leads to a paradox, or rather a contradiction in terms
for a table to be a relation, every possible value of a specific field has to be in the same domain
if Research and Headquarters are both in the Department domain
and
Research can be in multiple Locations
follows
there is a one to many relationship between Research and Location
follows
there is a many to many relationship between Department and Location
follows
there can be multiple Headquarters
and that is a contradiction in terms
of cource, in real live a business can have multiple regional Headquarters
and multiple regional Research units
and multiple different kinds of research
etc...
the big problem of the example is that the (implied definition) of the Department domain is to narrow (or to broad, depends how you look at it)
for example look at the 'Research' department
a business that has its 'Research' department spread out over different locations, is probably doing things very wrong
however there may be very good reasons to spread its different kinds of research over different locations
out of curiousity:
in your last tables:
in table DEPARTMENT
i can clearly see what the Dname field is for : to store the name of the department
but what is the Dnumber of a department ?
and what is the Dmgr_ssn of a department ?
what is the use of these fields ?, and are they really needed ? (do think very good about this)
before i forget:
what happens if i insert the following in the LOCATION_MAP table:
LOCATION_MAP
+-----------------+
| Dnumber | LocID |
|-----------------|
| 5 | 1 |
|-----------------|
| 5 | 2 |
|-----------------|
| 5 | 3 |
|-----------------|
| 4 | 4 |
|-----------------|
| 1 | 3 |
|-----------------|
| 1 | 3 |
|-----------------|
| 105 |3123 |
+-----------------+
as a word of encouragement
dont worry to much about the mechanics of normalisation
if you design a system that works as it should work
and has no duplicate data
and has no conflicting data
and has no redundent data
you are probably good
just remember the definitions of the different normal forms
a system is in 2NF if it is (fill in) AND it is in 1NF
a system is in 3NF if it is (fill in) AND it is in 2NF
a system is in XNF if it is (fill in) AND it is in X-1NF
you see: every NF is dependent on 1NF (directly or indirectly)
and 1NF is dependent on the definition of the atomisity of a fields domain
and ever since the bird of the relational system,the relational experts can not seem come up with a definition they all completely agree over
Re: Am I doing this 2NF in the right way?
Thank you very much for your detailed explanation. While reading, I was stressed a lot but the end of the explanation; the rest of this sentence made me happy: "if you design a system that works as it should work". Thanks again.
