Normalization Example Question

[MichiganMan]

I am trying to set up a database for a class, and it needs to be 3rd
Normal form. Here were my two examples:

BOOK (ISBN_NUM, BOOK_NAME)

AUTHOR (AUTH_NUM, AUTH_NAME, ISBN_NUM)

ADDRESS (AUTH_ADDRESS,AUTH_NAME)

CONTACT (AUTH_NAME, AUTH_PHONE, AUTH_EMAIL)

or

PROJECT (PROJECT_NUM, PROJECT_NAME, CUST_COMP)

EMPLOYEE (EMP_NUM, EMP_NAME, EMP_PAY, EMP_TYPE, EMP_PHONE)

SHIFT (EMP_SHIFT, EMP_NUM)

CUSTOMER (CUST_COMP, CUST_PHONE)

The first field after the parenthesis is the key. (it wouldn't let me
underline on here).

The teacher wrote back and stated "in the first example In the AUTHOR
table he didn't understand why do I have ISBN_NUM in that table? Is
that what book the Author wrote? If so what if the Author wrote two
books?"

"Also what is ADDRESS and CONTACT tables used for?"

I am thinking I am just going to go with the second example, my
question is, is this 3NF? Can anyone help me with what might be another
table I could use for the first example, or could provide me with other
examples that I might find the answer myself?

I appreciate any help!!

 

[Damian S]

Hi MichiganMan,

Your table structure is incorrect - the way you have it, you are saying that
an Author has an ISBN_Num... they don't... Books have an ISBN_Num... Books
also have an Author...

Check this out:
BOOK (ISBN_NUM, BOOK_NAME, AUTH_NUM)
AUTHOR (AUTH_NUM, AUTH_NAME, AUTH_ADDRESS, AUTH_PHONE, AUTH_EMAIL)

Generally you shouldnt' have data repeated across tables that is the same,
eg: AUTH_NAME in your example. Also, if a linked table will only have a
single record every time, it should be part of the table it is linked to, not
a seperate table. See above.


In your second example:

PROJECT (PROJECT_NUM, PROJECT_NAME, CUST_COMP)
EMPLOYEE (EMP_NUM, EMP_NAME, EMP_PAY, EMP_TYPE, EMP_PHONE)
SHIFT (EMP_SHIFT, EMP_NUM)
CUSTOMER (CUST_COMP, CUST_PHONE)

You don't have any links between the tables... eg: if Employees have
Projects, you might have a table
EMPLOYEEPROJECTS (EMP_NUM, PROJECT_NUM)
to link the two together.

Hope that helps...

Damian.

 

[David F Cox]

comments inline

I am trying to set up a database for a class, and it needs to be 3rd
Normal form. Here were my two examples:

BOOK (ISBN_NUM, BOOK_NAME)

This is fine, the ISBN number identifies the book under that name. (Books
get renamed and republished)

AUTHOR (AUTH_NUM, AUTH_NAME, ISBN_NUM)

This is not the author table. WHOBY might be a closer match. Book ISBN_NUM
can be by several co-authors, and you need a table to be able link the book
to several authors. This is called a one-to-many relationship.

It is a "relation". and it should only contain information relating the book
to the author. The author real name is in the author table, so it does not
need to be here. What might need to be here is the author alias, the name
author used on this book. One author can use many aliases.

ADDRESS (AUTH_ADDRESS,AUTH_NAME)
CONTACT (AUTH_NAME, AUTH_PHONE, AUTH_EMAIL)

These are useless. Several authors can have the same name. It should contain
the AUTH_NUM to identify which real author has this address. AUTH_NAME
should be AUTH_ALIAS, which is the name the author wants to be known by at
that address, if different from their real name. ( authors lead complicated
lives). It is all contact information, and should be in one table. None of
these fields are guaranteed unique, so you should have a contact key to
identify the record.

and you still need an author table, which has a key of AUTH_NUM and the real
information about the author. - real name, SSN, DOB, favourite colour,
whatever.

or

PROJECT (PROJECT_NUM, PROJECT_NAME, CUST_COMP)

EMPLOYEE (EMP_NUM, EMP_NAME, EMP_PAY, EMP_TYPE, EMP_PHONE)

SHIFT (EMP_SHIFT, EMP_NUM)

CUSTOMER (CUST_COMP, CUST_PHONE)

This is a lot better, except that projects do not have and staff working on
them, and staff are not working on any projects.

You need another table to show the relation between projects and employees,
(that is why they are called relational databases) and to hold information
about the relationships between the employees and the projects.

 

[MichiganMan]

I think I am starting to understand this a little better.
I appreciate all your help and time!

 

[MichiganMan]

Ok, how is this? 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)