database design

ERD Design Issues to be Addressed Assumptions Assumptions need to be e.g., it is not mentioned whet particular attribute is unique and always defined (to make it the identifier). The main issues with the assumptions were: to make a design decision, her a relationship is mandatory or optional, or it is not explicitly said that a made in cases when you lack information (or clarity) .Presenting your design decisions as assumptions, e-g., creating an entity to represent some data. Repeating the requirements. Contradicting the requirements . Design General mistakes Identifier not shown . Several entities or relationships with the same name Design mistakes: ta duplication. In most cases you did not analyze the requirements but simply applied them to your design and ended with the entity Bed that has the CareCenterlD as an attribute. This is the attribute of the entity CareCenter, and you needed to realize that there is a relationship between Bed and CareCenter (see the design guidelines in the handout on design exercises). More representing diagnosing and treating Nurse and Nurse-in-Charge. In some cases you created the entity Nurse and did not provide any attributes (because no attributes of Nurse were mentioned). We cannot have the entity with no attributes. It is mentioned that one of the Employees is assigned as a Nurse-in-Charge of a Department. It should have prompted you to think of nurses as employees. This allows to Dat rare cases of duplication included attributes like PhysicianlD or PatientiD on relationships present nurses' data properly and also have all needed relationships involving nurses. In-and out-patients. There is no need to add an attribute that defines is - it can be seem from the relationship between Patient and Bed. In one case there were two entities InPatient and OutPatient, which is incorrect Not representing some requirements as attributes where you needed attributes representing Diagnosis ID/Code and Diagnosis between Patient and Physician. This will lead to inconsistencies in the database (see the design guidelines in the handout on design exercises). what kind of Patient this Name as attributes of relationship Diagnose In order to know which Patient was diagnosed by which Physician and what Diagnosis it was you need to have a ternary relationship. You have the similar situation with Patient, Physician, and Treatment. Different binary relationships between these entities do not give you this (see the analysis of the exercise about Chemists, Projects and Equipment in the handout on design exercises). ERD Design Issues to be Addressed Assumptions Project 2. Building the Logical Model Correct the problems of the conceptual model; submit the initial and corrected designs. The grade for the ERD will be computed as 45% of the first submission plus 55% of the second submission. Build the relational model of the database according to the corrected conceptual model (80 points). Item Number Name ID DOB tem Number Name Patient MRN MRN Unit Price Consumed items tems Treatment each Care Center has only one nurse in Assigned Bed Number patient can be treated and diognosis by Room Number ID Bed Room ID Refer Nurse can be male or female ID Name Name New Label has Diagnosis Employee ID Employee Care Center Name Center Name Employee ID Center ID Center ID assigned works on Employee Relationship Record ID ERD Design Issues to be Addressed Assumptions Assumptions need to be e.g., it is not mentioned whet particular attribute is unique and always defined (to make it the identifier). The main issues with the assumptions were: to make a design decision, her a relationship is mandatory or optional, or it is not explicitly said that a made in cases when you lack information (or clarity) .Presenting your design decisions as assumptions, e-g., creating an entity to represent some data. Repeating the requirements. Contradicting the requirements . Design General mistakes Identifier not shown . Several entities or relationships with the same name Design mistakes: ta duplication. In most cases you did not analyze the requirements but simply applied them to your design and ended with the entity Bed that has the CareCenterlD as an attribute. This is the attribute of the entity CareCenter, and you needed to realize that there is a relationship between Bed and CareCenter (see the design guidelines in the handout on design exercises). More representing diagnosing and treating Nurse and Nurse-in-Charge. In some cases you created the entity Nurse and did not provide any attributes (because no attributes of Nurse were mentioned). We cannot have the entity with no attributes. It is mentioned that one of the Employees is assigned as a Nurse-in-Charge of a Department. It should have prompted you to think of nurses as employees. This allows to Dat rare cases of duplication included attributes like PhysicianlD or PatientiD on relationships present nurses' data properly and also have all needed relationships involving nurses. In-and out-patients. There is no need to add an attribute that defines is - it can be seem from the relationship between Patient and Bed. In one case there were two entities InPatient and OutPatient, which is incorrect Not representing some requirements as attributes where you needed attributes representing Diagnosis ID/Code and Diagnosis between Patient and Physician. This will lead to inconsistencies in the database (see the design guidelines in the handout on design exercises). what kind of Patient this Name as attributes of relationship Diagnose In order to know which Patient was diagnosed by which Physician and what Diagnosis it was you need to have a ternary relationship. You have the similar situation with Patient, Physician, and Treatment. Different binary relationships between these entities do not give you this (see the analysis of the exercise about Chemists, Projects and Equipment in the handout on design exercises). ERD Design Issues to be Addressed Assumptions Project 2. Building the Logical Model Correct the problems of the conceptual model; submit the initial and corrected designs. The grade for the ERD will be computed as 45% of the first submission plus 55% of the second submission. Build the relational model of the database according to the corrected conceptual model (80 points). Item Number Name ID DOB tem Number Name Patient MRN MRN Unit Price Consumed items tems Treatment each Care Center has only one nurse in Assigned Bed Number patient can be treated and diognosis by Room Number ID Bed Room ID Refer Nurse can be male or female ID Name Name New Label has Diagnosis Employee ID Employee Care Center Name Center Name Employee ID Center ID Center ID assigned works on Employee Relationship Record ID