can you help me with final entity list and thier relations
base on these instruction a SQL database
Problem (requirements): Wannacureu Medical Clinic has a need for a database to store data on their patients, doctors, and nurses. Wannacureu clinic has many doctors and nurses that work for them. And at any specific time of the visit, a patient can be seen by a physician and be cared for by nurses that may have been different from their previous visit. Meaning the first time they visit, Doctor A may take care of them. The second time, Doctor B. And this is the same for nurses. However, for any particular appointment, there can be one or more nurses that actually provided care for them and they all should be captured. For each patient, all common personal and demographic information should be collected including age, SSN, and insurance company. In addition, contact information must be collected that includes phone number and email address. Also, emergency contact information would be useful for each patient. For each doctor and nurse, personal information and address would need to be stored. In addition, their hire date, termination date (if no longer with the clinic), and also the date they passed their boards needs to be collected. Every time a sick person walks into the clinic, the date of the visit and the reason for the visit must be collected. A MD and the nurse(s) are then assigned to the case. Upon completion of the appointment, the diagnosis and follow-up date must be added to the data for the visit. D. Focus The following problem: 1. Write the purpose/mission statement for the database. Though this should not be long, it should be concise and complete as to the purpose of the database. (5 Points) 2. Identify and list the initial entities from the provided requirements. (5 Points) 3. Pair down the entity list based on duplication and other factors resulting in the final entity list that the database will be built. (5 Points) 4. Create the ERD for this solution using VISIO. (5 Points) 5. Define the relationships between the entities. (5 Points) 6. Add the appropriate attributes for each entity in VISIO. (5 Points) 7. Identify the correct datatype for each attribute and note them by the attribute name. (5 Points) 8. Apply the Primary key to each entity by identifying the key in VISIO for each entity. (5 Points) 9. Create the physical database in SQL Server by writing the drop tables and creates. (10 Points) 10. Build the physical relationships between all of the tables based on design via the use of foreign keys. (5 Points) 11. Create referential integrity by altering the tables to add a foreign key constraint. (5 Points) 12. Insert 5 or more rows into each table created. (5 points) 13. Write the SQL Statements to provide the following: List all columns and all rows from the patient table. (5 Points) b. List all of the patients that are from a certain zip code. (5 Points) Provide the First Name and Last Name of the patients where their last visit was before a specific date. (5 Points) d. List all the patients (First Name and Last Name) of a specific nurse. (5 Points) e. List the patients (First Name and Last Name) who has seen a specific doctor. (5 Points) 14. Write the following Update Statements. Before and after each Update, do a simple select of the table to show the before and after effect of the update. a. Update all the emergency contact columns for a specific patient. (5 Points) 15. Write the following Delete Statements. Before and after the Delete, do a simple select of the table to show the before and after effect of the delete. Delete a specific visit of a specific patient. (5 points) a. C. a. Problem (requirements): Wannacureu Medical Clinic has a need for a database to store data on their patients, doctors, and nurses. Wannacureu clinic has many doctors and nurses that work for them. And at any specific time of the visit, a patient can be seen by a physician and be cared for by nurses that may have been different from their previous visit. Meaning the first time they visit, Doctor A may take care of them. The second time, Doctor B. And this is the same for nurses. However, for any particular appointment, there can be one or more nurses that actually provided care for them and they all should be captured. For each patient, all common personal and demographic information should be collected including age, SSN, and insurance company. In addition, contact information must be collected that includes phone number and email address. Also, emergency contact information would be useful for each patient. For each doctor and nurse, personal information and address would need to be stored. In addition, their hire date, termination date (if no longer with the clinic), and also the date they passed their boards needs to be collected. Every time a sick person walks into the clinic, the date of the visit and the reason for the visit must be collected. A MD and the nurse(s) are then assigned to the case. Upon completion of the appointment, the diagnosis and follow-up date must be added to the data for the visit. D. Focus The following problem: 1. Write the purpose/mission statement for the database. Though this should not be long, it should be concise and complete as to the purpose of the database. (5 Points) 2. Identify and list the initial entities from the provided requirements. (5 Points) 3. Pair down the entity list based on duplication and other factors resulting in the final entity list that the database will be built. (5 Points) 4. Create the ERD for this solution using VISIO. (5 Points) 5. Define the relationships between the entities. (5 Points) 6. Add the appropriate attributes for each entity in VISIO. (5 Points) 7. Identify the correct datatype for each attribute and note them by the attribute name. (5 Points) 8. Apply the Primary key to each entity by identifying the key in VISIO for each entity. (5 Points) 9. Create the physical database in SQL Server by writing the drop tables and creates. (10 Points) 10. Build the physical relationships between all of the tables based on design via the use of foreign keys. (5 Points) 11. Create referential integrity by altering the tables to add a foreign key constraint. (5 Points) 12. Insert 5 or more rows into each table created. (5 points) 13. Write the SQL Statements to provide the following: List all columns and all rows from the patient table. (5 Points) b. List all of the patients that are from a certain zip code. (5 Points) Provide the First Name and Last Name of the patients where their last visit was before a specific date. (5 Points) d. List all the patients (First Name and Last Name) of a specific nurse. (5 Points) e. List the patients (First Name and Last Name) who has seen a specific doctor. (5 Points) 14. Write the following Update Statements. Before and after each Update, do a simple select of the table to show the before and after effect of the update. a. Update all the emergency contact columns for a specific patient. (5 Points) 15. Write the following Delete Statements. Before and after the Delete, do a simple select of the table to show the before and after effect of the delete. Delete a specific visit of a specific patient. (5 points) a. C. a
