RE: Normalization Tables Examples (Cont)
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Nov 2006 11:19:02 -0800
Firstly I'd recommend you follow the convention of using plural or collective
nouns for table names (e.g. Projects not Project, a table representing a set
which by definition has plurality), singular nouns for column names (which
you've done) and not to use upper case for table or field names. This
becomes important when writing queries, as the SQL reads more easily and the
table and column names are distinguished from the keywords. You might say
that queries can de designed visually im design view, but its when you come
to write the more advanced queries which can only be written in SQL that the
fluency of the SQL becomes most important.
If each project has relates to only one customer (which is likely), and each
project can relate to only one employee (which may or may not be the case)
then you simply add a Cust_Comp foreign key column to the Project table. No
redundancy is involved here as all the non-key columns of the Project table
are functionally determined solely by the key (as it’s a single column key it
follows that they are determined by the whole of the key of course, so all
the 3NF rules are satisfied).
If more than one employee is involved in each project then this is not
satisfactory of course and it would be necessary to introduce another table
to model the many-to-many relationship between projects and employees. If
this were the case then this table would have two foreign key columns
referencing the primary keys of Project and Employee. The two columns form
the composite primary key of the table. There may also be other columns in
this table which represent attributes of the entity type (while the table
models a relationship type, this is also an entity type as relationship types
are really just a special king of entity type, so all tables model entity
types and can have attributes, which are what columns represent). In this
case the non-key columns would have to be functionally dependent on both the
key columns (the whole of the key) and solely on the key (a non-key column
which is functionally dependent on another non-key column is transitively
functionally dependent on the key, and thus introduces redundancy).
As regards the Shift table the question arises as to whether this needs to
reference the Project table, i.e. is a shift worked by an employee in
relation to a particular project. If this is the case then a Project_Num
foreign key column needs to be added to Shift. Otherwise the data does not
tell us which of the many projects an employee might have been involved in
the shift relates to. It looks to me, however, as if this table actually
represents a many-to-many relationship between employees and shifts so should
be called something like Employee_Shifts and have a primary key made up of
Shift_Num and Emp_Num referencing the keys of Employee and a new Shifts table.
Ken Sheridan
Stafford,
"MichiganMan" wrote:
I am trying to make up a Database and it has to be 3NF. Is the example
at the bottom correct?
Ive added EMP_NUM to the Project table so that
projects may have an employee assigned to them. But how would I go
about linking the CUSTOMER Table to the project? The reason I ask is I
need to build a relationship diagram between these 4 tables.
I could add CUST_COMP to the Project Table, but then I would have two
links in the that table to other tables which would be redundant
data...
PROJECT (PROJECT_NUM, PROJECT_NAME, EMP_NUM)
EMPLOYEE (EMP_NUM, EMP_NAME, EMP_PAY, EMP_TYPE, EMP_PHONE)
SHIFT (EMP_SHIFT, EMP_NUM)
CUSTOMER (CUST_COMP, CUST_PHONE)
Any help would be appreciated!!!
.
- References:
- Normalization Tables Examples (Cont)
- From: MichiganMan
- Normalization Tables Examples (Cont)
- Prev by Date: Re: Tables
- Next by Date: RE: Auto Number does not match number of records
- Previous by thread: Re: Normalization Tables Examples (Cont)
- Next by thread: office xp maximize
- Index(es):
Relevant Pages
|
Loading
