1. Clinic Appointment System

You are asked to design a database system for a clinic appointment system. The requirements for the appointment system are summarized as the follows.

• There are three types of persons in the system: staff, physicians, and patients. Any data about a person in the system must be stored in one of these three categories (and cannot be simply stored as person). In addition, a person cannot be two categories at the same time (e.g., a person cannot be a staff and a patient simultaneously).
  • The system has data about the staff ID, first name, last name, phone, and address.
  • The system should contain data about physicians, including their ID, first name, last name, phone, address, certificate, and specialty.
  • The system has to track information about patients. A patient, identified by its ID, has information including first name, last name, phone, and address.
• A patient can make appointments with a physician through the system. A patient may make several appointments in the system. A physician may have several appointments in the system. For each appointment, only one (and at least one) patient is involved. In some occasions, there may exist an appointment without a physician on it. Data about an appointment includes appointment ID, time, date, reason, and the patient and the physician for the appointment.
• One appointment may have one or more diagnoses. Each diagnosis has its own diagnosis ID, description, and treatment.

* Use Microsoft Visio or Draw.io to draw the diagram.

* Use IE (crows foot) notation.

* The diagram should specify entities, keys, attributes, relationship types (identifying or non-identifying), and cardinalities (MAX and min on each side). Divide an attribute further into simple attributes when appropriate. All many-to-many relationships, if any, should be converted into associative entities (the answer should not have many to many relationship). Relationship labels are not needed. Use a specialization hierarchy when appropriate.