This case study is two parts - as shown & marked in the table-

I have completed part one, the other attachment. However, i need help with part two

- Scemarav smpliverinent at the MrS Moywat. at the Rrer Nirgnal? are 20 mponde of in-purimb ituar wpe atrintod bo a wald Camerwerk Detalh: fie the RPY Mecpitil Mantastime Sysain. Syedent 7. Mewify the istal namber of narwet and the sane of theit ablarin radirat. telepboser number, dase of hirth, serider, and the dete ad tine at the anked the out-pution dific: M 1113 tare that of ase 20 niconde of in-patiant that woy adminted to a wad Caervetwer Drtaila: Stelnt. 1. Liat the detule af manes athocatod at entopole wast. puliest's anory Stedrst 1 Steleat 4 the copectod decative of ateje at the want Qhallent2 batiqut's narre 3 of 4 3etelent 4 1. Wiat the name of cach f lead Wurne at each waed. ordlorod hy ward marne the expociod darinion ef abgh in the wand. Alvanedilatest Toteque Civertal Reymiermets? In then matporsen yue are roymerd ar: - Werk a guest of a menhers. Advanced Database Concepts MISY 3331 Page I of 4 Learning Outcomes: - Apply redundancy control in designing a database. - Demonstrate a database solution using an appropriate tool based on a case study. Case Study: KPJ Hospital Management System - KPJ Hospital is planning to upgrade and expand several facilities in its hospital. To keep up with the expansion and new services that came with it, KPJ Hospiral has decided to modernize its health care management system. Listed below is a description of how the data are recorded and maintained by the hospital staff. Your team is assigned the project to design a database system to support the management and day-to-day operation of the KPJ Hospital. - Scenario: - The KPJ Hospital has 17 wards with a total of 240 beds available and an out-patient clinic. Each ward is uniquely identified by a number (for example, ward 11) and also a ward name (for example, Orthopedic), the total number of beds (the out-patient elinic has no beds). whether it is for females or males, and telephone extension number (for example, Extn 7711). - Every ward has a head nurse and two general nurses. The information stored for each nurse includes a staff number, name (first and last), full address, telephone number, date of birth, gender, the position held, and current salary. - The details of each of the doctors working at KPJ Hospital are recorded. They include the doctor's full name, staff number, the doctor's specialty or specialties, and the doctor's telephone extension. The appointment schedule is recorded for each doctor, including time, date, location, and patient name. Currently, there are 15 records of doctors that are under employment at the KPJ Hospital. - When a patient is first registered at the hospital, he or she is allocated a unique patient number. At this time, additional details of the patient are also recorded including the name (first and last name), address, telephone number, date of birth, gender, marital status, date registered with the hospital, and the details of the patient's next of kin (full name, relationship to the patient, address, and telephone number). Currently, there are 20 records of patients that are registered at the KP J Hospital. - When a patient makes an appointment either through phone or by visiting the hospital, he or she is given an appointment for an examination by a hospital doctor. Each appointment is given a unique appointment number. The details of each patient's appointment are recorded and include the name and staff number of the doctor performing the examination, the date and time of the appointment, and the examination room (for example, Room E252). As a result of the examination, the patient is either recommended to attend the out-patient clinic or is assigned to a bed in an appropriate ward. (Assume that there is always such a bed available.). Currently. there are 35 records of patients' appointments made at the KPJ Hospital. - The details of out-patients are stored and include the patient number, name (first and last name), address, telephone number, date of birth, gender, and the date and time of the appointment at the out-patient clinic. Currently, there are 15 records of out-patients that were recommended to attend the out-patient clinic. Advanced Database Concepts MisY 3331 Page 2 of 4 - The details of patients who are admitted to a ward are recorded. These details include the patient number, name (first and last name), address, telephone number, date of birth, gender, marital status, the ward assigned, the expected duration of stay (in days), date stay began, date expected to leave the ward, and the actual date the patient left the ward when known. When a patient enters the ward, he or she is allocated a bed with a unique bed number. Currently, there are 20 records of in-patients that were admitted to a ward. Coursework Details: 1. In this assignment, you are required to design, implement, and document a database system for the KPJ Hospiral Management System. 2. Create the following queries using Data Manipulation Language (DML.) - Each student must be able to explain the queries and justify the approach taken. Student 1 1. List the details of nurses allocated at orthoprdic ward. 2. Identify the total number of nurses and the sum of their salaries. 3. List the details of patients whose stays at the hospital has excecded the expected duration of stays at the ward, ordered by patient's name. Student 2 1. List the details of patients that were referred to the out-patient clinic. 2. List the number of currently available beds that are allocated in the meurology ward. 3. List the next-of-kin's details of all patients whose marital status is single, ordered by paticnt's name Student 3 1. List the details of patients that were allocated at the cardiology ward. 2. Identify the total number patients allocated at each ward, categorised by gender. 3. List the total number of patients who had an appointment with Dr Helen Cho. Sfudent 4 1. List the name of each Head Nurse at each ward, ordered by ward name. 2. List the details of patients currently located in the respiratory ward. 3. List the details of patients in the dialysis ward whose stays at the hospital has not exceeded the expected duration of stays at the ward. 3. Deliverables: \begin{tabular}{|c|l|} \hline Part & Component \\ \hline 1 & a) Database and Database Management System \end{tabular} - Disadvantages of file-based system - Advantages of Database and DBMS - Relate your discussion to the case study 1 b) Business Rules \& Normalization - Generate a list of business rules - Provide an example of UNF and perform normalization up to 3NF clearly showing all the steps with explanation - 1 c) Entity Relationship Diagram - Design the database using Chen's or Crow's foot notation - Draw the ERD with any suitable tools such as Visio - All cntities, attributes, relationship and constraints should be clearly shown 1 d) Workload Matrix of each team member for Part 1 of the assignment ( a copy of this will also need to be submitted via MS Teams) 2 e) Database Schema - Finalized ERD - 7 ENTITY HERE. - Map the ERD to its corresponding relational schema and normalise all relations up to 3NF - 7 RELATION ONLY - Identify the attributes, data types and constraints of each tables and document in data dictionary. 2 f) SQL-Data Definition Language (DDL) - Create all tables with suitable data types - Insert 5-10 rows of data into cach table - Sereen shot all query statements - Screen shot all tables with its data 2 g) SQL-Data Manipulation Language (DML.) - Write SQL statements to answer question (2) above - Sereen shot all query statements together with its executed result General Requirements: In this assignment you are required to: - Work a group of 3-4 members. - Design and implement a solution to a business problem. - Implement the solution using any Enterprise DBMS. - Document the solution as set out in the assignment requirements. - Submit the document online according to the date and time given below. - Submit a Workload Matrix given by lecturer through blackboard. - Each group member is required to participate in all tasks / discussions together. Note: It is acceptable for discrete activities of this assignment to be undertaken by individual group members. However, it is essential that all group members understand the presentation in its entirety. At the end of the demonstration your group will be asked a series of questions to explore your understanding and analysis of the given problem. Responses to these questions such as "I don't know because I didn't work on that part of the assignment" are not acceptable and will result in a penalty for cither the entire group or specific individual(s). - Scemarav smpliverinent at the MrS Moywat. at the Rrer Nirgnal? are 20 mponde of in-purimb ituar wpe atrintod bo a wald Camerwerk Detalh: fie the RPY Mecpitil Mantastime Sysain. Syedent 7. Mewify the istal namber of narwet and the sane of theit ablarin radirat. telepboser number, dase of hirth, serider, and the dete ad tine at the anked the out-pution dific: M 1113 tare that of ase 20 niconde of in-patiant that woy adminted to a wad Caervetwer Drtaila: Stelnt. 1. Liat the detule af manes athocatod at entopole wast. puliest's anory Stedrst 1 Steleat 4 the copectod decative of ateje at the want Qhallent2 batiqut's narre 3 of 4 3etelent 4 1. Wiat the name of cach f lead Wurne at each waed. ordlorod hy ward marne the expociod darinion ef abgh in the wand. Alvanedilatest Toteque Civertal Reymiermets? In then matporsen yue are roymerd ar: - Werk a guest of a menhers. Advanced Database Concepts MISY 3331 Page I of 4 Learning Outcomes: - Apply redundancy control in designing a database. - Demonstrate a database solution using an appropriate tool based on a case study. Case Study: KPJ Hospital Management System - KPJ Hospital is planning to upgrade and expand several facilities in its hospital. To keep up with the expansion and new services that came with it, KPJ Hospiral has decided to modernize its health care management system. Listed below is a description of how the data are recorded and maintained by the hospital staff. Your team is assigned the project to design a database system to support the management and day-to-day operation of the KPJ Hospital. - Scenario: - The KPJ Hospital has 17 wards with a total of 240 beds available and an out-patient clinic. Each ward is uniquely identified by a number (for example, ward 11) and also a ward name (for example, Orthopedic), the total number of beds (the out-patient elinic has no beds). whether it is for females or males, and telephone extension number (for example, Extn 7711). - Every ward has a head nurse and two general nurses. The information stored for each nurse includes a staff number, name (first and last), full address, telephone number, date of birth, gender, the position held, and current salary. - The details of each of the doctors working at KPJ Hospital are recorded. They include the doctor's full name, staff number, the doctor's specialty or specialties, and the doctor's telephone extension. The appointment schedule is recorded for each doctor, including time, date, location, and patient name. Currently, there are 15 records of doctors that are under employment at the KPJ Hospital. - When a patient is first registered at the hospital, he or she is allocated a unique patient number. At this time, additional details of the patient are also recorded including the name (first and last name), address, telephone number, date of birth, gender, marital status, date registered with the hospital, and the details of the patient's next of kin (full name, relationship to the patient, address, and telephone number). Currently, there are 20 records of patients that are registered at the KP J Hospital. - When a patient makes an appointment either through phone or by visiting the hospital, he or she is given an appointment for an examination by a hospital doctor. Each appointment is given a unique appointment number. The details of each patient's appointment are recorded and include the name and staff number of the doctor performing the examination, the date and time of the appointment, and the examination room (for example, Room E252). As a result of the examination, the patient is either recommended to attend the out-patient clinic or is assigned to a bed in an appropriate ward. (Assume that there is always such a bed available.). Currently. there are 35 records of patients' appointments made at the KPJ Hospital. - The details of out-patients are stored and include the patient number, name (first and last name), address, telephone number, date of birth, gender, and the date and time of the appointment at the out-patient clinic. Currently, there are 15 records of out-patients that were recommended to attend the out-patient clinic. Advanced Database Concepts MisY 3331 Page 2 of 4 - The details of patients who are admitted to a ward are recorded. These details include the patient number, name (first and last name), address, telephone number, date of birth, gender, marital status, the ward assigned, the expected duration of stay (in days), date stay began, date expected to leave the ward, and the actual date the patient left the ward when known. When a patient enters the ward, he or she is allocated a bed with a unique bed number. Currently, there are 20 records of in-patients that were admitted to a ward. Coursework Details: 1. In this assignment, you are required to design, implement, and document a database system for the KPJ Hospiral Management System. 2. Create the following queries using Data Manipulation Language (DML.) - Each student must be able to explain the queries and justify the approach taken. Student 1 1. List the details of nurses allocated at orthoprdic ward. 2. Identify the total number of nurses and the sum of their salaries. 3. List the details of patients whose stays at the hospital has excecded the expected duration of stays at the ward, ordered by patient's name. Student 2 1. List the details of patients that were referred to the out-patient clinic. 2. List the number of currently available beds that are allocated in the meurology ward. 3. List the next-of-kin's details of all patients whose marital status is single, ordered by paticnt's name Student 3 1. List the details of patients that were allocated at the cardiology ward. 2. Identify the total number patients allocated at each ward, categorised by gender. 3. List the total number of patients who had an appointment with Dr Helen Cho. Sfudent 4 1. List the name of each Head Nurse at each ward, ordered by ward name. 2. List the details of patients currently located in the respiratory ward. 3. List the details of patients in the dialysis ward whose stays at the hospital has not exceeded the expected duration of stays at the ward. 3. Deliverables: \begin{tabular}{|c|l|} \hline Part & Component \\ \hline 1 & a) Database and Database Management System \end{tabular} - Disadvantages of file-based system - Advantages of Database and DBMS - Relate your discussion to the case study 1 b) Business Rules \& Normalization - Generate a list of business rules - Provide an example of UNF and perform normalization up to 3NF clearly showing all the steps with explanation - 1 c) Entity Relationship Diagram - Design the database using Chen's or Crow's foot notation - Draw the ERD with any suitable tools such as Visio - All cntities, attributes, relationship and constraints should be clearly shown 1 d) Workload Matrix of each team member for Part 1 of the assignment ( a copy of this will also need to be submitted via MS Teams) 2 e) Database Schema - Finalized ERD - 7 ENTITY HERE. - Map the ERD to its corresponding relational schema and normalise all relations up to 3NF - 7 RELATION ONLY - Identify the attributes, data types and constraints of each tables and document in data dictionary. 2 f) SQL-Data Definition Language (DDL) - Create all tables with suitable data types - Insert 5-10 rows of data into cach table - Sereen shot all query statements - Screen shot all tables with its data 2 g) SQL-Data Manipulation Language (DML.) - Write SQL statements to answer question (2) above - Sereen shot all query statements together with its executed result General Requirements: In this assignment you are required to: - Work a group of 3-4 members. - Design and implement a solution to a business problem. - Implement the solution using any Enterprise DBMS. - Document the solution as set out in the assignment requirements. - Submit the document online according to the date and time given below. - Submit a Workload Matrix given by lecturer through blackboard. - Each group member is required to participate in all tasks / discussions together. Note: It is acceptable for discrete activities of this assignment to be undertaken by individual group members. However, it is essential that all group members understand the presentation in its entirety. At the end of the demonstration your group will be asked a series of questions to explore your understanding and analysis of the given problem. Responses to these questions such as "I don't know because I didn't work on that part of the assignment" are not acceptable and will result in a penalty for cither the entire group or specific individual(s)