Normalization Tables Examples (Cont)

[MichiganMan]

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!!!

 

[KARL DEWEY]

I did not see how you were going to use the shift table.
Links are not necessarily redundant data. If you only have one customer per
project then just add the customer to the project table. Therefore you would
not need the PROJECT_CUST table.

PROJECT ---
PROJECTID – Autonumber – Primary key
PROJECT_NUM -text
PROJECT_NAME – text
-- Alternate --
CUST ID – number – integer - foreign key
STATUS – text
REMARKS - memo

PROJECT_EMP ---
PROJECTID – integer - foreign key
EMPID – number – integer - foreign key
ASSIGNED - DateTime

EMPLOYEE ---
EMPID – Autonumber – Primary key
EMP_NUM - text
EMP_NAME -text
EMP_PAY – Number - Single
EMP_TYPE - text
EMP_PHONE - text

CUSTOMER ---
CUST ID - – Autonumber – Primary key
CUST_COMP - text
CUST_PHONE - text

PROJECT_CUST ---
PROJECTID – integer - foreign key
CUST ID – number – integer - foreign key
STATUS – text
REMARKS – memo

Join the primary key to the corresponding foreign fields.

Someone else may have a better idea.

 

[David F Cox]

You should try to think in terms of "Things" and how they are related to
other "things".

You are thinking of having an employee record with a project number in his
record. But an employee can work on more than one project, and the employee
can do more than one function in a project. Bill may be project leader for
project A and advisor for Project B and wiring a tender for project C. The
employee has relationships with projects. You need a Table to describe that
relationship. I would start from:

Table Emp_Project
id_EP autonumber
Proj_num FK (foreign key, points to project)
Emp_num FK
Notes Memo (I stick Notes fields everywhere when developing., it is
somewhere to put information thathas not got a home elsewhere. I often find
that some of such information has to be given a field of its own later.)
..... other fields that describe the relationship between the project and the
employee.

You will have to think about the Shift Table in the same way. It too will
relate and employee to a project, and may contain such information as a
record id, shift_id, Emp_id, Proj_id, hours worked, work done, pay rate (if
it varies), Notes, job function etc.
You should think about whether one record per shift will do the job. I would
believe not. Sooner or later one employee will split a shift between two or
more projects.

The real world is complex, you have to be ready for it.

I hope this helps more than it confuses.

 

[Ken Sheridan]

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,