[SOLVED] For your answers to the following Problems

For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form:
a. Chapter 2, Problem and Exercise 2-39b
b. Chapter 2, Problem and Exercise 2-39g
c. Chapter 2, Problem and Exercise 2-39h
d. Chapter 2, Problem and Exercise 2-39i
e. Chapter 2, Problem and Exercise 2-42
f. Chapter 2, Problem and Exercise 2-46

Data from Problem and Exercise 2-39:
Draw an ERD for each of the following situations. (If you believe that you need to make additional assumptions, clearly state them for each situation.) Draw the same situation using the tool you have been told to use in the course.
a. A company has a number of employees. The attributes of EMPLOYEE include Employee ID (identifier), Name, Address, and Birthdate. The company also has several projects. Attributes of PROJECT include Project ID (identifier), Project Name, and Start Date. Each employee may be assigned to one or more projects or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (Billing Rate) for each employee when assigned to a particular project. Do the attribute names in this description follow the guidelines for naming attributes? If not, suggest better names. Do you have any associative entities on your ERD? If so, what are the identifiers for those associative entities? Does your ERD allow a project to be created before it has any employees assigned to it? Explain. How would you change your ERD if the Billing Rate could change in the middle of a project?
b. A laboratory has several chemists who work on one or more projects. Chemists also may use certain chemicals on each project. Attributes of CHEMIST include Employee ID (identifier), Name, and Phone No. Attributes of PROJECT include Project ID (identifier) and Start Date. Attributes of CHEMICAL include Compound No and Cost. The organization wishes to record Volume Used—that is, the amount of a given chemical used by a particular chemist working on a specified project. A chemist must be assigned to at least one project and one chemical on each project to which he or she is assigned. A given chemical need not be assigned to any project, and a given project need not be assigned to either a chemist or a chemical. Provide good definitions for all of the relationships in this situation.
c. A college course may have one or more scheduled sections or may not have a scheduled section. Attributes of COURSE include Course ID, Course Name, and Units. Attributes of SECTION include Section Number and Semester ID. Semester ID is composed of two parts: Semester and Year. Section Number is an integer (such as 1 or 2) that distinguishes one section from another for the same course but does not uniquely identify a section. How did you model SECTION? Why did you choose this way versus alternative ways to model SECTION?
d. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician ID (the identifier) and Specialty. Patients are admitted to the hospital by physicians. Attributes of PATIENT include Patient ID (the identifier) and Patient Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of the treatment (Treatment Detail). Components of Treatment Detail include Date, Time, and Results. Did you draw more than one relationship between physician and patient? Why or why not? Did you include hospital as an entity type? Why or why not? Does your ERD allow for the same patient to be admitted by different physicians over time? How would you include on the ERD the need to represent the date on which a patient is admitted for each time he or she is admitted?
e. The loan office in a bank receives from various parties requests to investigate the credit status of a customer. Each credit request is identified by a Request ID and is described by a Request Date and Requesting Party Name. The loan office also received results of credit checks. A credit check is identified by a Credit Check ID and is described by the Credit Check Date and the Credit Rating. The loan office matches credit requests with credit check results. A credit request may be recorded before its result arrives; a particular credit result may be used in support of several credit requests. Draw an ERD for this situation. Now, assume that credit results may not be reused for multiple credit requests. Redraw the ERD for this new situation using two entity types, and then redraw it again using one entity type. Which of these two versions do you prefer, and why?
f. Companies, identified by Company ID and described by Company Name and Industry Type, hire consultants, identified by Consultant ID and described by Consultant Name and Consultant Specialty, which is multivalued. Assume that a consultant can work for only one company at a time, and we need to track only current consulting engagements. Draw an ERD for this situation. Now, consider a new attribute, Hourly Rate, which is the rate a consultant charges a company for each hour of his or her services. Redraw the ERD to include this new attribute. Now, consider that each time a consultant works for a company, a contract is written describing the terms for this consulting engagement. Contract is identified by a composite identifier of Company ID, Consultant ID, and Contract Date. Assuming that a consultant can still work for only one company at a time, redraw the ERD for this new situation. Did you move any attributes to different entity types in this latest situation? As a final situation, now consider that although a consultant can work for only one company at a time, we now need to keep the complete history of all consulting engagements for each consultant and company. Draw an ERD for this final situation. Explain why these different changes to the situation led to different data models, if they did.
g. A parking garage in downtown Baltimore offers its services to both monthly customers, who pay a fixed fee every month, and to visitors, who pay an hourly fee (assume that the hourly fee is the same regardless of the day or time of day). Each monthly customer gets an ID assigned by the garage, and the garage wants to maintain the customer’s basic contact information. The monthly fee paid is negotiated separately for each customer, and it changes periodically; it is important for the garage to maintain a history of the fee details for each customer. The garage has more than 700 parking spots, each of which is equipped with a sensor that recognizes whether there is a car in the spot; in addition, the sensor can read each monthly customer’s customer ID card with an RFID reader and, thus, knows which monthly visitor has parked at which spot and when. Each spot is also equipped with a camera that is able to take a picture of each visitor’s license plate number; this information is stored to help locate any vehicle that an owner has misplaced. The system should keep track of each time a parking spot is used, including an image of the license plate, start and end times, and a link to the monthly customer, if appropriate.
h. Each case handled by the law firm of Dewey, Cheetim, and Howe has a unique case number; a date opened, date closed, and judgment description are also kept on each case. A case is brought by one or more plaintiffs, and the same plaintiff may be involved in many cases. Each plaintiff in each case has a requested judgment characteristic, such as a requested dollar award, possession of some asset, or other outcome. A case is against one or more defendants, and the same defendant may be involved in many cases. A plaintiff or defendant may be a person or an organization. Over time, the same person or organization may be a defendant or a plaintiff in cases. In either situation, such legal entities are identified by an entity number, and other attributes are name and net worth. As you develop the ERD for this problem, follow good data naming guidelines.
i. A professional society, CAM, organizes hundreds of meetings for its members every year, and it would like to develop an application to support the organizational processes needed to ensure the success of these meetings. Each of the meetings will have five to 500 participants (sometimes even more), and each meeting takes place in a specific hotel in a specific city in a specific state. The organizers need to know each participant’s full name, cell phone number, and e-mail address. CAM wants to maintain information regarding dietary restrictions for each participant at the general level, but the restrictions also need to be confirmed and recorded separately for each meeting. For each member’s participation for a specific meeting, the organizers need to know how he or she will travel to the meeting and when he or she is planning to arrive and leave. It is important that CAM can produce an accurate report on how many meetings have been organized in a specific city or a specific state.

Data from Problem and Exercise 2-42:
Each semester, each student must be assigned an adviser who counsels students about degree requirements and helps students register for classes. Each student must register for classes with the help of an adviser, but if the student’s assigned adviser is not available, the student may register with any adviser. We must keep track of students, the assigned adviser for each, and the name of the adviser with whom the student registered for the current term. Represent this situation of students and advisers with an E-R diagram. Also, draw a data model for this situation using the tool you have been told to use in your course.

Data from Problem and Exercise 2-46:
Draw an ERD for the following situation, which is based on Lapowsky (2016): The Miami-Dade County, Florida, court system believes that jail populations can be reduced, reincarceration rates lowered, and court system costs lessened and, most important, that better outcomes can occur for people in and potentially in the court system if there is a database that coordinates activities for county jails, metal health facilities, shelters, 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. 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 name, birth date, education level, job title (if the person is an employee of one of the participating agencies), and (permanent) address. Some people in the system will have been prescribed certain medicines while in the care of county hospitals and mental health facilities. A medicine has a name and a manufacturer. Each prescription is for a particular medicine and has a dosage. A prescription is due to some diagnosis, which was identified on a certain date, to treat some illness, was diagnosed by some facility professional, and has notes explaining family history at the time of the diagnosis. Each illness has a name and some medicines or other treatments commonly prescribed (e.g., certain type of counseling). Each participating agency is of a certain type (e.g., criminal justice, mental health) and has a name and a contact person. People visit or contact an agency (e.g., they are arrested by the justice system or stay at a shelter). For each contact a person has with an agency, the database needs to record the contact date, employment status at time of contact, address at time of contact, reason for visit/contact, and the name of the responsible agency employee.

Question: For your answers to the following Problems