Using ER approach design a hospital database with a set of patients (including information of patient name, gender, DOB, primary insurance company) and a set of medical doctors (including doctor name, specialty). [To simplify the design, we will use a single character string to denote any persons full name.] Information on each visit (including date) by a patient to a doctor is recorded. Information recorded also includes a log of the various tests of patients conducted on various dates (ordered by doctors), as well as the costs. Note that although in some cases a visit by a patient to a doctors office may not necessarily result in tests, in some other cases there could be multiple tests. Design a database containing all the basic information stated above, along with any other necessary information so that the two queries in part (d) can be answered.

a) Draw the ERD (entity relational diagram) using standard notation (also indicate primary keys for entity sets).

b) Convert (or reduce) the ERD into a set of tables using the method introduced in the class (always remember to indicate primary keys for all tables).

c) Redraw the design diagram using UML notation.

d) Write RA expressions for the following two queries for the RDB you have just designed (i.:e., using the tables obtained in part (b)):

(i) Find insurance information for patients who have visited both Cardiologist Dr. Jill Stein and orthopedics specialist

Dr. Ben Carson in 2015.

(ii) Find out during 2014 how many patients who visited a doctor but did not take any test on the same day.