Hey there! Sign in to join this conversationNew here? Join for free
    • Thread Starter
    Offline

    0
    ReputationRep:
    hi all, just wanted to know if anyone could help me with this question (see attachment)

    the whole relational model seems so simple yet so confusing. the answer i wrote for this question is:

    test(test_id, date, name, time, result)
    patient(name, date_of_birth, contact_number, SIN, date_admitted, date_checked_out, test_id)
    doctor(specialization, name, doctor_id)

    but i know it is the wrong answer (since the question is worth 15 marks). can anyone point out where i've gone wrong and what other schemas i need to write for this particular question?

    would really appreciate it thanks in advance!
    Attached Images
     
    • Thread Starter
    Offline

    0
    ReputationRep:
    bump
    Offline

    0
    ReputationRep:
    (Original post by thesdasdsadas)
    hi all, just wanted to know if anyone could help me with this question (see attachment)

    the whole relational model seems so simple yet so confusing. the answer i wrote for this question is:

    test(test_id, date, name, time, result)
    patient(name, date_of_birth, contact_number, SIN, date_admitted, date_checked_out, test_id)
    doctor(specialization, name, doctor_id)

    but i know it is the wrong answer (since the question is worth 15 marks). can anyone point out where i've gone wrong and what other schemas i need to write for this particular question?

    would really appreciate it thanks in advance!
    First up lose the test_id in the patient table. Next you need to create 3 tables to join things up ie a test patient table that has a compound key made of patient_id and test_id etc... This gives you 6 tables in all.

    Have to say on a personal note not convinced by the result field being in the test table. Seems to breach basic normalisation rules to me. After all what if 2 people have the same test done to them. You then get redundancy in the name field of your test table....
    • Thread Starter
    Offline

    0
    ReputationRep:
    thank you very much for the reply

    taking everything you said into consideration, this is the answer i got:

    test(test_id, date, name, time, result)
    doctor(specialisation, doctor_id, name)
    performed_by(doctor_id, test_id)
    test_log(SIN, test_id)
    patient(name, date_of_birth, contact_number, SIN, date_admitted, date_checked_out)
    examined_by(SIN, doctor_id)

    have i made any mistakes this time round?
 
 
 
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • Poll
    Would you like to hibernate through the winter months?
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

    Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

    Quick reply
    Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.