Convert the following table to an equivalent collection of tables that
is in third normal form. This table contains information about
patients of a dentist. Each patient belongs to a household.

Patient ( HouseholdNum, HouseholdName, Street, City, State, Zip,
Balance, PatientNum, PatientName, (ServiceCode, Description, Fee,
Date) )

The following dependencies exist in the Patient table:

PatientNum  „³  HouseholdNum, HouseholdName, Street, City, State, Zip,
Balance,
                                 PatientName

HouseholdNum „³  HouseholdName, Street, City, State, Zip, Balance
ServiceCode   „³    Description, Fee
PatientNum, ServiceCode   „³ Date

---

Request for Question Clarification by dogbite-ga on 05 May 2003 14:03 PDT

Hi skokie-ga,

  I am able to answer this question,
  but I would have to spend some time
  looking through some of my old course
  notes (or online material).

  If you want me to answer it, I will
  answer it for $25.  Alternatively,
  another researcher might answer for $10.

               dogbite-ga

---

Clarification of Question by skokie-ga on 05 May 2003 14:13 PDT

Hi, dogbite-ga
If you can answer the question before 10 P.M tonight, I will pay you $25.

Hi skokie-ga,

  Great.  Here is your answer.

  To satisfy 3NF you must first satisfy
  1NF and 2NF.

  1NF is simple -- you need a primary key (the
  PatientNum) and you cannot have duplicative
  columns (none here).

  2NF requires that you pull out subsets of data
  that apply to multiple rows into separate tables.
  Then use external keys to link into the new tables.

  Then, for 3NF, we remove columns that are not
  fully dependent on the primary key.


Table: Patient =
(PatientNum*, PatientName, HouseholdNum)

Table Household =
(HouseholdNum*, HouseholdName, AddressID)

Table Address =
(AddressID*, Street, Zip)

Table Zips =
(Zip*, City, State)

Table PatientServices =
(PatientNum, ServiceCode, Date)

Table Services =
(ServiceCode*, Description, Fee)


  The primary keys are all tagged with a '*'.

  I believe this is 3NF because:

    1) we have a primary key for each patient,
    2) there are no duplicative colums,
    3) No table can have subsets of data that
       are the same.  For example, we cannot
       have two patients with the same PatientName
       and HouseholdNum (assuming normal family
       naming).
    4) I removed the balance column because it
       can be computed by summing the Fees.

  To get this answer I mainly used my old course
  notes.  But I also found these pages helpful:

http://databases.about.com/library/weekly/aa081901a.htm
    - about.com description of 1NF
http://databases.about.com/library/weekly/aa090201a.htm
    - about.com description of 2NF
http://databases.about.com/library/weekly/aa091601a.htm
    - about.com description of 3NF


   I hope that helps you.

                 dogbite-ga