Database normalization to 3rd Normal Form 3NF

[interlekt]

Background on the firm and requirements.

Hamiltons Vets is a medium sized veterinary surgeon based in north east London founded and run by Stephanie Hamilton. Stephanie wants a database system developed to handle the records of patients, prescriptions and referrals. Although Hamiltons had traditionally dealt with dogs and cats they also have to treat more exotic species especially as these become more popular as pets; to this end they have employed Dave Nellis a vet from Australia who specialises in snakes and lizards. Hamiltons requirements are for a system that keeps records of their patients, patient’s owners and the type of animal they are. For appointments they need to know with which vet the appointment is with as well as the time and date. The outcome of the appointment might be a prescription with one or more drugs on it. Repeat prescriptions are seen as being the outcome of a single appointment. An appointment might also lead to a referral or number of referrals for a particular treatment. Hamiltons referrals are for treatments carried out at only one particular treatment centre each.


Materials.

1. Interview with Stephanie Hamilton
‘Things have been expanding in recent years. That’s why we’ve taken on Dave. So now we have myself, Dave, Adrian Cooper, Suleman Faizi and Emma Goldman. The real substance of our work is the appointment where people bring in their pets. Owners who have more than one pet tend to use us for all of them. We charge for each appointment and for each prescription made out depending on the cost of the drug. Any treatment carried out by treatment centre is billed by them and has nothing to do with us.
‘One other thing. If you examine our manual records they might not seem very precise. We don’t always seem to keep the same information for example about the owners or the types of animal. Maybe you can help us to make this more efficient’



2. Manual patient record


Name Owner Type of Animal
Tiddles Mrs Jones of 12 Armpit Road Cat
Ralph Dave Green 0902 88881 Border Collie
Allan Mrs Jones 12 Armpit Road N15 Dog
Lucy Julie Smith 0789 00021 Siamese Cat
Leccy Beggsy (Friend of Dave Nellist) Monitor Lizard



3. Example of a manual appointment record


Hamiltons Vets
23 Mare Street
E5 2YT

Name: Tiddles
Owner: Mrs Jones
Appointment Date: 01/Jan/01 2.00pm

Details:
I examined tiddles (cat) and found he had a severe in-growing toenail. Prescribed some diazipan to get him through it. Mrs Jones is bearing up.

Stephanie.

Cost of appointment £10
Cost of drugs £5
Recommend also laser treatment at Harris’s Animal Hospital Middlesex. (3 sessions)
Recommend series of training sessions at Woodhouse.

Total Cost
£15



[u][u]4. Hospitals and Treatments[/u]
Treatment Type Hospital or Centre Name
Laser removal Harris’s Animal Hospital Middlesex
Extensive surgery Harris’s Animal Hospital Middlesex
Behaviour Assessment The Woodhouse Institute
Specialist Training The Woodhouse Institute

5. Example of a prescription sheet.
Prescription

PRESCRIPTION NUMBER: 0023
PATIENT NAME: Rolph
PATIENT TYPE: Alsatian Dog
OWNER: Dave Stewart
CUSTOMER ADDRESS: 11 Victoria Street, N1
PRESCRIPTION BY: Adrian Cooper (Vet)



Drug Dosage Period Length of Course Cost
Zoratin 1ml 4 hours 2 weeks 2.00
Pheno B 2 ml 6 hours 1 week 10.00
Aspirin 1ml 4 hours 2 days 0.50

I dunno what i keep doing wrong but i just cant work out how to do the normalization right and its an important part to building the database required for this.

[dinangkur]

Submit what you have done, then we will see.

-DK.
_________________
...we too are stardust...

[quantum]

This message and other too sound awfully like a school project. Now we don't mind help you do your school project, but you just cannot expect us to do all your works. If you show the efoorts you have originally made and then ask us specific questions, we will be more than glad to help you solve that.
_________________

Dust fills my eyes / Clouds roll by / and I roll with them / Centuries cry / Orders fly / and I fall again
Afford best design, implement best solution. Outsource your web design.

[interlekt]

here is what i have come out with, i need this done in 3rd normal form, and this is the best i can do:

3NF

Patient Number
Patient Name
Patient Type PATIENT
Owner First Name
Owner Last Name
Owner Address
Appointment Number

Appointment Number
Appointment Date APPOINTMENT
Appointment Cost

Patient Number
Vet Number
Vet Number
Vet First Name VET
Vet Last Name
Prescription Number

Prescription Number
Prescription By
Prescription Cost
Drug Code PRESCRIPTION
Drug Cost
Dosage
Period
Length

Patient Number
Treatment Code
Treatment Type TREATMENT
Treatment Sessions
Hospital Name
Hospital Sessions


Please can anyone help

[quantum]

Hi Interlect,

I understand your emergency. But please do not post duplicate topics. This does not help your cause and is not good for the forum. As you must understand, participation is voluntary here. If nobody posts a reply, that's probably because they are busy or don't want to. I have deleted your duplicate post and also changed your topic tilte, so that people can get the summary.

Now I would love to be of help, but I am rather too busy too to immerse myself into a full database designing with so many tables. But let us see. I can give you some general pointer at least. Do you fully understand what is normalization to the 3d form?

A table is in 1st normal form if:
there are no repeating groups
all the key attributes are defined
all attributes are dependent on the primary key

Do your tables satisfies these rules? Which are the primary keys here? And what are the data types? Make them clear and post.

After you do that...

A table is in 2nd normal form if:
it's in 1st normal form
it includes no partial dependencies (where an attribute is dependent on only a part of a primary key).

Define your primary keys and make seperate tables for each key. It will be just so that all fields in each table is totally dependent on the primary key of that table.

And lastly,

A table is in 3rd normal form if:
It's in 2nd normal form
It contains no transitive dependencies (where a non-key attribute is dependent on another non-key attribute).

It's very clear. For any field that is dependent on another field, which is not a primary key must be moved to a seperate table. I see from a glance that your current fields needs major restructuring in that sense.

I hope that gives you some pointer. Reply with the primary keys defined for each table or groups you made.
_________________

Dust fills my eyes / Clouds roll by / and I roll with them / Centuries cry / Orders fly / and I fall again
Afford best design, implement best solution. Outsource your web design.

[interlekt]

I still dont get it, i just wanna know wot i have done wrong in the 3rd normal form i provided, and what is the correct solution for it. I have tried and tried and i keep comin up with different solutions, and this one is the best one i cud come up with. Can anyone please help and give me the correct answer to this

[quantum]

As far as I can see, what you are doing wrong, is that you are not following the definitions of 3rd normal form. In order to call a database design 3rd normal form you have to follow certain rules, as mentioned above. One of the most important of them is to get the primary keys defined cearly and associate related fields with them. Do you know what is a database table's primary key? If you do not then tell us. If you do then tell us what are the primary keys you found in this solution of yours.
_________________

Dust fills my eyes / Clouds roll by / and I roll with them / Centuries cry / Orders fly / and I fall again
Afford best design, implement best solution. Outsource your web design.