##########################################
#
# MySQL (3) : In class exercise
#
# Use the sample database (sample_data.sql)
# if you are required to use a database
#
##########################################


####################################################
#
# A small walk-in clinic has approached you for a database to  
# handle their patients, doctors, visits and lab results.
#
# The clinic informs you about the following business workflow:
# Each patient can be visited by various doctors.
# Of course, doctors can visit different patients too.
# The information of each visit should be recorded
# Each visit will generate a list of medical signs and symptoms, and prescribed medications.
# Each doctor can prescribe multiple medications for the same patient.
# Lab results should also be stored for patients.
# Each lab test can be ordered only by one doctor
# and each lab test belongs only to one patient.
#
# The following data should be recorded:
#	
# Patient Information: FirstName, LastName, DOB, Street Address, City, Country, ZipCode, PhoneNumber, Email, Height, Weight, Sex
# Doctor Information: FirstName, LastName, OfficeNo, Street Address, City, Country, ZipCode, PhoneNumber, PagerNumber, Email
# Visit Information: Date, Signs, Symptoms, Diagnosis, PrescribedMedication, DoctorComment, PatientComment
# Lab Information: Date, RBC, WBC, PLT, Hgb, MCV, TimeStamp
# (You may need more tables and columns...) 
#
# Please complete the following steps:
# 1. Analyze the business requirements
# 2. Draw the ER (Entity Relationship) diagram
# 3. Create the tables in MySQL (you can prefix them with a constant such as sample_xxxx)
# 4. Write SQL commands that can: 
#    - Find complete contact information for a specific patient
#    - Find complete list of doctors (including their contact info) for a specific patient
#    - Find complete lab result history for a specific patient
#    - Find complete lab orders for a specific doctor
#    - Find complete visit history for a specific patient
#    - Find complete visit history for a specific doctor
#    - Find complete list of medication prescribed for a patient
#
####################################################