A database for Doctors Office.

The following Business Rules are followed

1. One patient can see many doctors in the same office on the same day.

2. One doctor can also see many patients. On the same day.

3. If one doctor sees the same patient multiple times in one day, then its only recorded once in the database.

4. For each patient we are recording primary and secondary phone numbers.

5. A doctor can also be a patient.

6. A doctor cannot be his/her own patient.

7. Each patient will get 0 to 10 prescriptions(medicine).

8. Each patient can be given 0-5 tests (medical test).

9. A patient cannot be given a new prescription, test without first visiting the doctor.

10. Make sure that the Doctor ID is first 2 letter of his first name followed by number. Eg. If the Doctors name is Ron Gates, then his DoctorID can be RO3283.

11. Patient ID is a system generated number.

12. Data retrieval speed is a priority. Use techniques to make it faster.

Also Things to do;;

1) One SCRIPT to create this database (call it DocOffice) in SQL SERVER

2) /*

Doc Rob Belkin is retiring. We need to inform all his patients, and ask them to select a new doctor.

For this purpose, Create a VIEW that finds the names and Phone numbers of all of Rob's patients.

*/

3)/*

Create a view which has First Names, Last Names of all doctors who gave out prescription for Panadol

*/

4)/*

Create a view which Shows the First Name and Last name of all doctors and their specialtys

*/

5).

/*Modify the view created in Q4 to show the First Name and Last name of all doctors and their specialties ALSO include doctors who DO NOT have any specialty*/

Person 8 PersonID 8 SpecialityD StreetAddress City State Zip Doctor Doctor D Doctor D Speciality D SSN 8 PVisitP 8 Visit D PatientVisit Patient 8 Visit D 8 PatientD Patient D Doctor D DOB Person D Prescription DocNote Test PVisitTest 8 Vsit D 8 Test D 8 Test D TestName