Not sure where to post this but here goes anyway. I'm currently studying database at university and i'm still quite new to it. Here is a case study:

 
A dental surgery wishes to set up a database to record the details of its
patients, their appointments with dentists working at the surgery and 
treatments at these appointments.
A number of dentists work at the surgery. The information recorded for 
each dentist includes - employee code, name, address and phone number. 
Each patient has a - patient number, name, address and phone number 
to be stored. Patients make appointments. The time and date of the 
appointment are recorded along with a unique appointment number. 
Each appointment is conducted by one  dentist. A patient may attend 
a number of appointments during their time with the the dental surgery 
and each dentist can conduct many appointments. One treatment may 
be performed at each appointment. The information recorded for a 
treatment includes - a treatment number and the type of treatment. 
Sometimes a treatment may result in the recommendation of a particular 

drugs. The drug code, description and recommended dose of the medication 
are recorded.

The only bit that i am stuck on is the foreign keys(in bold). Do i have them within the correct entities?

ENTITY TYPES

Patient(PatientNo,Name,Address,PhoneNo)
Treatment(TreatmentNo,Type,DrugCode)
Drug(DrugCode,Description,Dose)

Appointment(AppointmentNo,Time,Date,PatientNo,EmployeeCode,TreatmentNo)

Dentist(EmployeeCode,Name,Address,PhoneNo)

Looks OK to me

I wasn't sure if the PatientNo,EmployeeCode,TreatmentNo foreign keys should all be in the "Appointment" entity.
I need to know if this is definately correct because all of the other questions are based around this answer.

Thanks

I wasn't sure if the PatientNo,EmployeeCode,TreatmentNo foreign keys should all be in the "Appointment" entity

Yep, because you need to pull the related data from the other tables, it certainly looks the best structure to minimise redundancy.

The only thing that seems to be missing is an appointment duration field which would possibly belong in the treatment structure if treatment types had specific durations or in the appointment if durations are variable.

thank you very much for advising me chrishirst.

Hi, I've got another question. When i'm typing this into MYSQL how would i create a table to hold a field that only has three specific types??

I mean the field TreatmentType either has a filling,extraction or whitening

CREATE TABLE Treatment (
TreatmentNumber VARCHAR(4),
TreatmentType ....... ???????

What would i type into MYSQL to get this correct??

Thanks

I think MySQL has an enum data type? Meaning a list, like you just described.

You could use a constraint, or validation rule, to make sure people only type one of the three values you're interested in. Or, you could create a new table with an ID and a value, then set up a relationship.