Database query

pansylea · #1 (**permalink**) 12-08-07, 17:42

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. I'm just stuck on something at the minute. I have gotten a practical as an exercise for the exam and i've got a question. Here is the case study:

Code:

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.

For the entity types i've got

Entity types

Patient(PatientNo,Name,Address,PhoneNo)
Treatment(TreatmentNo,Type,DrugCode)
Drug(DrugCode,Description,Dose)
Appointment(AppointmentNo,Time,Date,PatientNo,Empl oyeeCode,TreatmentNo)
Dentist(EmployeeCode,Name,Address,PhoneNo)

all i want to know is if i got this correct because the rest of the questions depend on this answer and if i get this wrong i will get the rest of the questions wrong.

thanks in advance

gvee · #2 (**permalink**) 12-08-07, 18:02

Ok, here are my suggestions...

First thing that caught me when reading the question was this

Quote:

The time and date of the appointment are recorded along with a unique
appointment number.

Why is an appointment given a unique number, when the patient and the datetime are unique? But if that's what the question wants...

The second thing I picked up on was that you've split the date and the time into two fields?
Why have you decided to do that? What datatype do you propose to use to hold these values?

Third point... Is the dose of a medication relative to the drug, or to the patient?

Fourth, and final one for now; Have you considered splitting peoples names into first and last? See this recent thread for a good reason why you should consider it

But pansylea... You should really chat with your tutors about all of this when you hand in the work (if not beforehand).

And well done for giving it such a good try; you'd get 3/4 marks for that answer!

pansylea · #3 (**permalink**) 12-08-07, 19:09

The case study that i was given wants a unique number for an appointment. It also wants the date and time seperately because i'm using SQL i'll just type in TIME and DATE and it'll work.

I just want to know if i have got the foreign keys correct and if the Entity type "Drug" should have been created.

gvee · #4 (**permalink**) 12-08-07, 19:29

I appreciate that is what you're assignment asks for - but it's worth realising why that is not considered the best way.

What datatype will you be using for your date and time columns?
Your justification for separating them doesn't cut it with me.

The drug entity should indeed have been created, however, I'm concerned (and torn) about wher the dose should sit.

Report your entities using the following notation

TableName (primaryKeys, foreignKeys, otherFields)

pansylea · #5 (**permalink**) 12-10-07, 06:10

I will be using DATE and TIME for the columns becuase that is what the assignment asks of me so if i dont provide them i will get the question wrong.

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)

gvee · #6 (**permalink**) 12-10-07, 08:23

Quote:

Originally Posted by Your assignment

The time and date of the appointment are recorded along with a unique appointment number.

It asks you to store the date and time - it does not express that they should be separated.

Moved thread to database concept and design topic

pansylea · #7 (**permalink**) 12-10-07, 11:18

It does ask me to seperate them as a question further on asks to complete a domains list and it provided me with an incomplete list which is:

Code:

model DentistSurgery
domains
          PatientNumbers = p001 .. p999
          PersonNames = string
          AddressLine = string
          PhoneNumbers = string
          Time = TIME
          Date = DATE
          ...

pansylea · #8 (**permalink**) 12-11-07, 17:00

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

r937 · #9 (**permalink**) 12-11-07, 17:12

Code:

TreatmentType varchar(27) not null references TreatmentTypes ( TreatmentType )

TreatmentTypes is a table with TreatmentType as the primary key, and other possible columns such as MinFee (the minimum fee charged for that particular treatment type), etc.

Internet Services

Web Development

Digital Marketing

Consumer Tech