Google Answers Logo
View Question
 
Q: Database Management Systems ( Answered,   0 Comments )
Question  
Subject: Database Management Systems
Category: Computers > Software
Asked by: skokie-ga
List Price: $25.00
Posted: 04 May 2003 01:38 PDT
Expires: 03 Jun 2003 01:38 PDT
Question ID: 199149
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.
Answer  
Subject: Re: Database Management Systems
Answered By: dogbite-ga on 05 May 2003 15:01 PDT
 
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
Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy