3

As part of a homework assignment I have been asked to create tables based on a case study, and all tables must be in 3NF. However, I've tried and tried to understand 3NF but I'm just not getting the hang of it and would appreciate some help.

The requirements of the case study are for a vet clinic:

  1. Allow pets to be booked in for appointments
  2. Record pet treatments
  3. Record which vet performed the treatment
  4. Record items sold by the business to provide info that allows the business to produce sstock lists for purchase from the suppliers

DOESNT NEED TO: RECORD ALL SALES

I have the following tables so far:

staff:

| staff_ID | firstName | lastName | gender | address_ID | contactNumber | partTimeOrFullTime | salary |

A staff address table:

| address_ID | staff_ID | number | street | city | county | postalCode |

The vet table:

| staff_ID | appointment_ID |

vet_nurse: 

| staff_ID | appointment_ID |

An appointment table:

| appointment_ID | customer_ID | staff_ID | patient_ID | date | time |

initial_appointment table:

| appointment_ID | customer_ID | patient_ID | diagnosis | treatment |

followUp_appointment:

| appointment_ID | customer_ID | patient_ID | diagnosis | treatment |

patient:

| patient_ID | customer_ID | animal_type | gender | weight | height | previous_Appointments | previous_Treatment |

product:

| product_ID | name | product_Category | animal | price | quantity_Available | reOrder_Level |

product_sold:

| sale_ID | product_ID | sale_Date |

supplier:

| supplier_ID | product_ID | contactNumber | email |

suppliers_address:

| supplierAddress_ID | supplier_ID | doorNumber | street | city | town | postalCode |

inventory:

| name | product_ID | quantity_Available | price |

Thanks!

h21
  • 37
  • 5
  • 1
    Just a tip: I would try and format this better. This is a really long question and hard to filter through. – AdamMc331 Nov 06 '14 at 14:37
  • 1
    For someone who claims not to understand normalisation, this is a remarkably good effort! However, storing customer_id anywhere other than the customer table and the patient table is redundant. And date and time should be stored as a single entity. Also, do not have separate tables for initial and follow-up. If it's not simply the case that a patient's first visit is their initial visit then store a flag (0/1) in a separate column indicating 'initial' visits. – Strawberry Nov 06 '14 at 14:45
  • in the EERD i made for this, we were required to model generalisation/specialization and do i did that with an appointment being either an initial appointment or followup appointment. shouldnt i do seperate tables for them? – h21 Nov 06 '14 at 15:00
  • @h21 They are still just appointments though, aren't they? – AdamMc331 Nov 06 '14 at 17:38
  • All the addresses can go into one table. Follow up and initial appointment look like the same thing so can go into one table. Just add another column to say what type it is. – Abu Nooh Nov 06 '14 at 17:47
  • Have you got a link to the eerd? That may be a better way to view it. – Abu Nooh Nov 06 '14 at 17:49
  • yes but if in the ERD i have shown them as subclasses etc.shouldnt i model them with 2 seperate tables in the database? – h21 Nov 06 '14 at 18:00
  • In 3nf you'll take out all the redundancy and duplicate tables. Are you doing an erd or eerd? – Abu Nooh Nov 06 '14 at 18:11
  • eerd http://i1371.photobucket.com/albums/ag296/Henley_Osei/erd_zps41c04737.jpg – h21 Nov 06 '14 at 18:20
  • Having looked at it briefly I think you can keep the staff as disjoint but merge the others that are the same. As long as the columns are the same you won't need a separate table. You can achieve that with a flag in the same table ie address type: staff; patient. Appt type: initial; follow up. Even if your staff have same columns then use a flag there too. – Abu Nooh Nov 07 '14 at 02:00
  • so instead of a seperate table for vet and vet_nurse i should put an attribute "staff type" in the staff table? and appointment type in the appointment table? @AbuNooh – h21 Nov 08 '14 at 14:49
  • Yes as long as those tables that are to be merged have the same columns you can add that field 'type' or whatever you want to call it and then make it one table. – Abu Nooh Nov 08 '14 at 16:32

1 Answers1

1

I will not give you an exact answer for 2 reasons: 1) I'm too lazy to filter through all that text. There, I said it. 2) You wouldn't learn anything.

Third normal form is about having no transitive functional dependencies. In other words, if A determines B, where B does not determine A, and B determines C, you have a transitive dependency, so B and C could be put into their own table.

An example from your set could be the tables with city, state, and post code. In real world situations, zipcode can be used to determine city and state. Perhaps you could have a separate table with zipcode as the key, and city and state are the other two attributes. This can be a transitive dependency because address ID -> zip code and zip -> city, state as I said.

Another important thing to remember: if any fact appears twice, you can normalize even more. For example, [city A, State B, ZipCode C] likely appears multiple times, because I'm sure you have multiple people from the same area.

EDIT After looking this over and editing it I have found a lot more things to comment on, but as this is an assignment I will give you time to think about it and come back in a few days or longer to go over it again, if you're still curious.

EDIT 2

I will give you suggestions table by table, but will try to limit these to just pushes in the right direction.

staff - There are no transitive dependencies that jump out to me, everything in there is determined by the primary key, which is good.

staff_address - Why do you have a staff_id column? This is not a good idea. In addition - you have a transitive dependency with the address as I mentioned already.

vet and vet_nurse - These tables have the exact same columns, so why are there two tables? Certainly there's a way you can use one.

appointment tables - Initial appointment and follow up have the same columns. Again, there should be a way you can make them one. I will give you one direct suggestion for the appointment table: put date and time as one column, aptDate, and give it the DATETIME value type.

patient - The customer_ID value is in the patient table, so why should it be in the others? Also, previous appointments and previous treatments are going to be very difficult to track inside the database. You should see that as soon as you start inputting data.

product - As it is now, it doesn't seem too bad, but there are issues I will discuss later.

product_sold - Is sale_ID unique? If it is, how many products can be sold in one sale? This table is definitely not normalized well.

supplier - How many products can a supplier have? This is your hint at how to change the product table.

suppliers_address - Same issue with postalCode here. Also, why does suppliers_address point to supplier?

inventory - Aren't you already tracking all of these fields in the product table (except for price)?

These are potential problems I see, but I can't in good conscience give you solutions to your assignment. However, if this is one of your first attempts at a normalized database it's not bad at all.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133