here i have attached my assessment 2 for an ER diagram
FACULTY OF LAW AND BUSINESS Peter Faber Business School, North Sydney SEMESTER 1, 2019 DATA 200: Data and Information Management Assignment Three (Individual): e-Portfolio (45%) Due: before 11.55pm 26/05/19 The main objective of assignment task 3 is to convert the logical-level design to a physical database using Microsoft Access software. You may change your logical design submitted in assignment task 2, depending on the feedback and/or due to some implementation issues. During the physical design stage, it is quite common to iterate between design and implementation stages. Your e-portfolio should contain two elements. 1. ACCESS database as a file 2. Technical report presented as a journal entry (Not a WORD document) (A) Access database: Specifications: 1) No of minimum records of each entity: Patients 15 Receptionists 05 Nurses 10 Doctors 10 Medication 10 You may increase above (not decrease). You may also have additional tables. Assign any number of records as you wish for such tables. 2) Perform normalisation 3) Create relevant relationships 4) Create 5 queries (One query using 1 table, 4 queries using 2 or more tables) 5) Make any business rules or assumptions that are necessary. (B) Technical report: as a journal entry of e-Portfolio (900 – 1000 words). This technical report should contain the following elements. a) Reflection – (700 words) You need to reflect on the major learnings of this subject and how you have linked them to your assignments. It may be quite possible that you may have a new design. This is quite normal in design and implementation processes. You need to explain the reasons behind the new change. You also need to explain the additional requirements that you would like to add in future designs, how would your database be used by multi-users and any other relevant features. b) E-R diagram (old) and E-R diagram (New) It is quite possible that the ER diagram you produced in assignment 2 may have changed. Therefore, include both old and new ER diagrams. Note that new changes are a result of students making changes in their old ER diagram, individually. Therefore, include both old and new ER diagrams. c) Queries: List and describe your queries in a table as shown below. Note: if you list name of query as qry_doctor, in your Access database, there should be a query called qry_doctor. This consistency must be maintained. Name of query implemented at A3 Query Description Example: qry_doctor List first name, last name, email address and mobile number of all doctors. d) Business rules and/or assumptions e) References – Links to your discussion forum posts in weeks 7-11, and any other sources f) Note: The length of (a), (b), (c) and (d) should be 900 – 1000 words (max) Submission of DATA200 Assessment task 3: Submit your assignment via the Submission link available in Assessment section of Leo. When clicked on the submission link, the system will display all the e-portfolios that you have. Select the correct one. Case Study – Emergency Room In our Emergency Room (ER), we have three distinct types of workers: receptionists, nurses, and doctors. Any of the workers can in fact be a patient. Each person in the proposed system, be it a patient or a worker has a last, a first, possibly a middle name, and one or more addresses. An address consists of a country, province, city, street and street number. Each person can have none or more email addresses, none or more telephone numbers. The workers work in ER in shifts. A shift consists of start and end time. The shifts do not overlap, but they are consecutive, i.e. there is a shift on at any given time and day. We are assuming that the model we are creating (and eventually the database we will design) covers some extended period of time. Each worker will thus be assigned to many shifts in that period. Exactly two receptionists are assigned to each shift, a group of two or more nurses is assigned to each shift, a group of two or more doctors is assigned to each shift, and one of the doctors assigned to a shift is the shift’s triage doctor. When a patient comes to ER, it happens during a particular shift. The patient is admitted by a particular receptionist, is seen by the triage doctor of the shift. The patient may be send home, prescribed some medication by the triage doctor and send home, or is staying in ER – in which case the patient is assigned a bed and case doctors (one of the doctors on each shift best qualified for the particular problem of the patient). Each bed is supervised by a single nurse during a shift, but a nurse may supervise many beds, or none at all. The case doctor(s) may prescribe a medication that is administered to the patient by a single nurse in each shift for the duration of the patient taking the medicine. Each medication has a name, and for each patient there may be a different dosage and different number of times a day to take it. Rubric C – Assessment Task 3 (e-Portfolio) (marks out of 45) ILO s Criteria NN (points) PA (points) CR (points) DI (points) HD (points) LO 4, 5, 6 Reflection report capturing critical learning points of data and information management throughout the semester Reflection report largely repeats the textbook or do not show the ability for reflective learning, do not capture critical learning points, not properly referenced (4.9) Reflection report shows an average level of reflective learning, capture some critical learning points, may be some errors in references (6.4) Reflection report shows a fair level of reflective learning, capture most of critical learning points, properly referenced (7.4) Reflection report shows a good level of reflective learning, capture all critical learning points, properly referenced (8.4) Reflection report shows that the learning materials are well analysed and deeply reflected on, all critical learning points are comprehensively captured, properly referenced (10) LO 4, 5, 6 Implementation of all the entities, keys, field names and properties in the database Database contains less than half of correctly identified entities and keys of the case study (4.9) Database contains about half of correctly identified entities and keys of the case study (6.4) Database contains more than half correctly identified entities and keys of the case study (7.4) Database contains most of the correctly identified entities and keys of the case study (8.4) Database contains all the correctly identified entities, keys, field names and properties of the case study (10) LO 4, 5, 6 Implementation of relationships in the database Database contains less than half relevant and correctly interpreted relationships. (2.9) Database contains about half of relevant and correctly interpreted relationships. (3.4) Database contains more than half relevant and correctly interpreted relationships. (4.4) Database contains most of the relevant and correctly interpreted relationships. (5.4) Database contains all the relevant and correctly interpreted relationships. (7) LO 4, 5, 6 Implementation of Normalisation (1st Norm, 2nd Norm, 3rd Norm) Normalisation is not applied. (0) Only 1st norm is applied correctly (4.4). Only 1st norm and 2 norm is applied correctly (5.4). Most of the three norms are applied correctly (6.4). All three normalisations are applied correctly (8). LO 4, 5, 6 Query Design Queries are not implemented. (0) Some queries are implemented using one table (6.4). Some relevant and meaningful queries are implemented using one table (7.4). Many relevant and meaningful queries are implemented using many tables (8.4). Several relevant and meaningful queries are implemented using many tables and AND, OR, NOT (10). DATA200: Data and Information Management Group Assessment Submitted by: Roja Khadka (S00217058) Sujan Ghimire (S00255046) Submitted to: Mr. Shakir Karim Part A:- Figure 1: Entity relationship diagram of the emergency room Figure 1 above depicts the entity relationship diagram (ERD) of the emergency room. The ERD has multiple entities with their attributes listed under them and illustrates the relationship between each entities using the crows feet notation. Below we have explained the kind of relationship between the entities. Entities Relationship Doctor Personal Details One to one relationship Doctor, patient, receptionist or nurse can have only one personal details. Patient Receptionist Nurse Personal Details Doctor One tomany relationship Personal details table can have details of one or many doctors, patient, receptionist or nurse. Patient Receptionist Nurse Doctor Patient Zero to many relationship A doctor diagnosis zero or many patients in a shift. A nurse can supervise zero to many beds that have patients assigned to them in a shift. A receptionist can admit zero to many patients in a shift. Nurse Receptionist Patient Doctor One to one relationship A patient is looked by only one case and triage doctor. A patient is admitted by only one receptionist. Receptionist Patient Nurse One to many relationship A patient can be supervised by one or many nurses since shift changes in a day which means different nurse has to be assigned to a bed/patient each time shift of a nurse ends. Shift Doctor One to many relationship A shift has one or many doctor, nurse or receptionist since the case study states