Richmond Alternative Family Therapy is a small family counseling practice. It needs an information system to schedule appointments. There are currently five counselors working in the practice. Their facility has four rooms that can be used for scheduling counseling sessions. Each appointment is scheduled for a particular date and has a start time and a duration (in minutes). Each appointment is for exactly one counselor and at least one client, but may be for multiple clients. Each counselor may have many appointments and each client can have many appointments. Each appointment is scheduled by an employee. Each appointment is in a particular room and each room can have many appointments. Additionally, appointments have a free-text field named “notes” where the counselor to keep notes about the counseling session. These notes may be as long as 3,000 characters. Each client who is scheduled for an appointment may have an assignment to complete in preparation for the counseling session. The description of the assignment may be as long as 2,000 characters. The system must also be able to record which clients actually came to the appointments for which they were scheduled. Appointments must be made in such a way that rooms, clients, and counselors do not have time conflicts. Of course, when a new employee, counselor, client, or room is added to the system, it will not have had any appointments, so it is possible for each to exist without being associated with a particular appointment.
Employees, counselors, and clients are all people, so they will need standard contact information: first name, last name, address, city, state, zip code, phone number, email address. In addition, Counselors have a degree suffix (such as MD or PhD). Clients have “FoundOut” attribute that allows for a free text description of how the client became aware of the practice. In addition, New Dawn needs to be able to record which clients were referred by which other clients. Each client may be referred by a single client and each client can be recorded as the referring client of many other clients. Clients also have a “reminder method” attribute that indicates how they prefer to be reminded about appointments (e.g. email, phone, text message, postcard). In addition to standard contact information, employees have a “date hired” attribute and a “wage” attribute. Also, each employee is supervised by exactly one other employee; an employee who is a supervisor may supervise more than one employee. The supervisory role is hierarchal and it is said that the top most employee in the hierarchy supervises herself. Rooms have a room number and a room name.
Counselors in the frim are partners, not employee; however, it is conceivable that a counselor could be hired in an employee role. For example, one of the counselors could be hired as a marketing agent to promote the firm’s counseling services. It is also possible for a counselor to receive counseling from another RAFT counselor. The only kinds of people that the practice keeps information about are clients, employees and counselors.
Design your solution such that it represents all basic contact information in a single entity and use inheritance to combine the contact information with other attributes of employees, clients, and counselors. Indicate which attributes serve as identifiers by underlining the attribute name. Do not include attributes to serve as foreign keys as that information is captured by relationships
Develop an EER diagram for the Richmond Alternative Family Therapy.
Express as many of the constraints in this scenario directly in the EER diagram as possible. If there is a constraint that you are unable to show using the diagram, you may express in a textual note. Show both minimum and maximum cardinality constraints.
