Create a logical ERD for the problems below using the crowsfoot format discussed in class. Be sure that each entity has the entity name at the top of the box, the primary key attribute or attributes in the middle of the box, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box. Follow these instructions about each ERD: The ERD should not have any M:N relationships. All attributes should be placed within an entity. Each entity must have a primary key defined. A primary key may consist of one or more attributes. Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD. Each relationship must include both a maximum and minimum cardinality for both sides of the relationship. Each relationship must have a verb phrase. Do not differentiate between an identifying or non-identifying relationship (dotted line vs. solid line). Make all your relationship lines solid. All attributes provided in the sample data or data forms must be included in the database.

Question 3. The Los Angeles County, California, court system believes that jail populations can be reduced, re-incarceration rates lowered, and court system costs lessened and, most important, that better outcomes can occur for people in the court system if there is a way to better coordinate data about people at risk. They want to create a database to collect data about activities for county jails, mental health facilities, homeless shelters, community health care alliances, pharmacies, and hospitals. Based on the contents of this database, algorithms can be used to predict what kind of help a person might need to reduce his or her involvement in the justice system. Eventually, such a database could be extensive (involving many agencies and lots of personal history and demographic data) once privacy issues are resolved. However, for now, the desire is to create a prototype database with the following data. Each participating agency is of a certain type (e.g. criminal justice, homeless shelter, community health care alliance) and has an agencyID, name and address. There are potentially many agencies that will participate in this database. The agency types could be standardized for consistency. Data about persons will be stored in the database, including professionals who work for the various participating agencies as well as those who have contact with an agency (e.g., someone who is a client of a mental health facility, who is incarcerated, or both). Data about people include personID, name, date of birth, telephone number, and gender. A person could have many relationships with an agency, and an agency could have many people that have relationships with the agency. For example, a person could be an employee of an agency. If a person is an employee of an agency, we keep track of the persons job title with that agency. As another example, a person could reside in a shelter. We keep track of the beginning and ending dates that a person is related to an agency as well as the role (i.e. employee, resident, incarcerated, patient) that the person has with that agency. A person in the system could potentially meet with many other people who are in the system. For example, a person who is in a mental health facility could meet with a person who works at that mental health facility. Or that same person could meet with a doctor at a local health care clinic that participates in this database as a participating agency. We want to have a way to record the meetings between people in the system. These meetings are referred to as a contact between people. For each contact, the database needs to record the contact date and the reason for the contact, as well as the people who participated in the contact. A contact could have multiple people participating in the contact and the database needs to store data about each person who participates in each contact. The data that needs to be stored for a particular contact with a particular person includes an attribute for a diagnosis, an attribute for a family history code, and an attribute to store unstructured notes. Some people in the system are prescribed certain drugs while in the care of county hospitals, clinics and mental health facilities. A drug has an identifier (NDI or National Drug Identifier) and a name. Each prescription (Rx) is for a particular person and drug. Each prescription includes the date it was prescribed and a dosage. A prescription is due to a diagnosis, which was the result of a contact with a person. When a contact by a person results in a prescription, the system stores the diagnosis and notes about the contact. The person who makes the diagnosis is the same person who writes the prescription (Rx) during the contact with that person.