Normalization and ERD You are to design a database for the following scenario. Create your solution in Microsoft Visio complete with all correct entities, attributes, keys, and relationships (minimum and maximum cardinality). Use verbs to describe the relationship between tables. Save your Visio document as a PDF. Turn it in to the Assignment 2

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, RAFT 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.