QuestionQuestion

Transcribed TextTranscribed Text

1. Consider a relational database capable of hosting information about family operations, one of the subsystems in The Halifax Health Clinic (HHC). The clinic has a number of volunteer family practitioners, nurses and office workers who are on rotation during the week. Each NEW patient is admitted by an office worker who enters his or her personal information. The office worker then assigns a specific family practitioner for the patient. A family practitioner cannot be assigned more than 20 patients. A nurse schedules an appointment, which involves the assigned family practitioner and a patient. An appointment can be rescheduled or cancelled by a physician or a nurse. Before the appointment starts, the nurse on duty records the patient's vital signs. In the first appointment, the physician records the patient's medical history. During an appointment the physician records his comments and recommendations. For all operations involving adding and updating records, the identity of the user requesting the change should be recorded. Design an Entity-Relationship diagram for this database. Draw the design using SQL Workbench. Submit your diagram as a PNG file (Use File->Export menu item). 2. Provide one example of each kind of integrity constraint in the above database. Clearly explain your example in connection with the integrity constraint it illustrates. Use an ER diagram to complement your explanation, if necessary. 3. This question is based on the database introduced in the tutorial. You are required to write SQL statements to extract the information described. ###### Part: 1 #################################### #################################################### # # Find all patients having the first 3 digits of their # Home phone number and Office phone number equal. # # Show only the Patient's identification number, first name, # last name, home and office phone numbers # #################################################### +------+-------------+------------+------------+--------------+ | P_id | P_FirstName | P_LastName | Phone_Home | Phone_Office | +------+-------------+------------+------------+--------------+ | 1 | Peter | Johnsons | 3245686 | 3241212 | | 3 | Sara | Henson | 6048978 | 6048793 | | 6 | William | Jordan | 3123333 | 3123432 | | 10 | John | McDonnald | 8724957 | 8724957 | | 17 | Fery | Sea | 4321354 | 4321521 | | 30 | Romeo | Khoseo | 5423455 | 5425443 | | 36 | Steve | Wanderhal | 2343455 | 2343454 | | 39 | Oliseagan | Dysee | 3565663 | 35653666 | | 45 | Zinc | Goldman | 6145747 | 614678 | | 46 | Uve | Evalinson | 4578458 | 4578488 | +------+-------------+------------+------------+--------------+ ###### Part: 2 #################################### #################################################### # # Find the Office phone number of all patients even if they # do not have one. # # Show only the Patient's identification number, first name, # last name and office phone numbers # #################################################### +------+-------------+--------------+--------------+ | P_id | P_FirstName | P_LastName | Phone_Office | +------+-------------+--------------+--------------+ | 1 | Peter | Johnsons | 3241212 | | 2 | Mike | Jackson | 4563981 | | 3 | Sara | Henson | 6048793 | | 4 | John | McDonnald | | | 5 | Michael | Robinson | 4563981 | | 6 | William | Jordan | 3123432 | | 7 | Susan | McKinsy | 3258976 | | 8 | Mehdi | Kharrazi | 4443412 | | 9 | John | McKinsy | | | 10 | John | McDonnald | 8724957 | | 11 | Pat | Bentatar | | | 12 | Abraham | Lincoln | 3453455 | | 13 | Brian | Adam | NULL | | 14 | Catherin | Catholicy | 3656789 | | 15 | Demi | Moore | 3452345 | | 16 | Ebi | Farahanzadeh | 5346373 | | 17 | Fery | Sea | 4321521 | | 18 | Graham | Bell | 6734788 | | 19 | Hamilton | Green | 7634377 | | 20 | Isaac | Killiam | 7257422 | | 21 | Josef | Koopy | | | 22 | Karim | Abdoljabar | 8754848 | | 23 | Liliam | Toram | 5435666 | | 24 | Mandy | Moore | 6234566 | | 25 | Nancy | McLachlan | 4566566 | | 26 | Keyty | Orr | 2564356 | | 27 | Castadilla | Almondo | 6743774 | | 28 | Sam | Hill | 6666236 | | 29 | Robin | Carrier | 1264527 | | 30 | Romeo | Khoseo | 5425443 | | 31 | Diana | Fedrensinco | 2346523 | | 32 | Lama | Buda | 4089699 | | 33 | Cristina | Augelira | 7636777 | | 34 | Faith | Hill | 4056785 | | 35 | Micheal | Jackson | 2072666 | | 36 | Steve | Wanderhal | 2343454 | | 37 | Mina | Namiri | | | 38 | Withney | Houston | | | 39 | Oliseagan | Dysee | 35653666 | | 40 | Sampali | Sirini | 8547845 | | 41 | Steve | Waterson | 6467777 | | 42 | Nisan | Yagamachi | 3453566 | | 43 | Yjie | Lee | 3563566 | | 44 | Tuet | Iaswithin | 4532635 | | 45 | Zinc | Goldman | 614678 | | 46 | Uve | Evalinson | 4578488 | | 47 | Peter | Yu | 5097668 | | 48 | Bill | Clincton | 5235623 | | 49 | Woo | Xingho | 5234234 | | 50 | Qu | Minxoush | 7654788 | +------+-------------+--------------+--------------+ ###### Part: 3 #################################### #################################################### # # Find the patient identification number and the # rounded average of RBC, WBC, PLT (by 1 decimal) # which have been entered in the first half of 2005 # # Show only the Patient's identification number, RBC, # WBC, PLT and the Date of the lab result # #################################################### +------+------+------+----------+------------+ | P_id | RBC | WBC | PLT | Date | +------+------+------+----------+------------+ | 1 | 4.5 | 9.2 | 275250.0 | 2005-05-09 | | 2 | 5.5 | 12.6 | 498750.0 | 2005-04-10 | | 5 | 5.9 | 9.4 | 281000.0 | 2005-03-09 | | 6 | 6.8 | 6.1 | 339250.0 | 2005-02-14 | | 7 | 3.5 | 5.4 | 223000.0 | 2005-04-17 | +------+------+------+----------+------------+ ###### Part: 4 #################################### #################################################### # # Find all of patients whose phone number (home or # office) contains the digit 0 # # Show only the Patient's first name, last name, # home and office phone number # #################################################### +-------------+------------+------------+--------------+ | P_FirstName | P_LastName | Phone_Home | Phone_Office | +-------------+------------+------------+--------------+ | Sara | Henson | 6048978 | 6048793 | | Lama | Buda | 4968445 | 4089699 | | Faith | Hill | 4045676 | 4056785 | | Micheal | Jackson | 2024566 | 2072666 | | Mina | Namiri | 4056788 | | | Peter | Yu | 5987607 | 5097668 | +-------------+------------+------------+--------------+ ###### Part: 5 #################################### #################################################### # # Find all patients who live in Halifax and show their maximum RBC and WBC # # # Show only the Patient's identification number, # first name, last name, city name, max RBC and WBC # rounded by 0 decimals # #################################################### +------+-------------+------------+-----------+---------+---------+ | P_id | P_FirstName | P_LastName | City_Name | MAX_RBC | MAX_WBC | +------+-------------+------------+-----------+---------+---------+ | 1 | Peter | Johnsons | Halifax | 7 | 11 | | 2 | Mike | Jackson | Halifax | 7 | 14 | | 5 | Michael | Robinson | Halifax | 6 | 10 | | 7 | Susan | McKinsy | Halifax | 4 | 5 | | 9 | John | McKinsy | Halifax | 5 | 9 | +------+-------------+------------+-----------+---------+---------+

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Clinics Database Development Problems
    $50.00 for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Database Development Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats