Results 1 to 9 of 9

Thread: Database query

  1. #1
    Join Date
    Dec 2007
    Posts
    5

    Database query

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Ok, here are my suggestions...

    First thing that caught me when reading the question was this
    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!
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    5
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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)
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Posts
    5
    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)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2007
    Posts
    5
    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
              ...

  8. #8
    Join Date
    Dec 2007
    Posts
    5
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •