For this assignment you will have to submit 3 files: (1) this Word file with your answers inserted after each problem, (2) a Visio file and (3) an Access database file (as per the instructions below). Make sure you submit all 3 files together (as one submission). Do not zip the files.

Problem #1 (40 points):UsingCrow's Foot notation andVisio, draw anEntity Relationship Diagram (ERD) that captures the business rules given below.

Note: Make sure that your ERD shows theprimary key (PK) for each entity set and each relationship is captured usingforeign keys (FKs). If you have any many-to-many relationships, make sure you break them down into 1-to-many relationships, so that you can capture them using FKs.

A small emergency center needs to record information about doctors, nurses, patients and rooms.

• Each new patient is assigned a unique patient ID on their first visit. In addition, the patient's name, date of birth, admission date, diagnosis, treatment and release date must also be recorded. (7pts)
• Each room is identified by a room number. The room type (private or semiprivate), and the room fee must also be recorded. (3pts)
• Some patients are resident patients (they spend at least one night in the emergency center) and others are outpatients (they are treated and released). Each resident patient must be assigned a room and will stay in that room only.The semiprivate rooms can have many patients at a time. (5pts)
• Doctors andnurses are both assigned a unique employee ID upon hiring. In addition, the employee's name, address and phone number must also be recorded. (10pts)
• Nurses areassigned todoctors. Each doctor is assigned multiple nurses (at least one nurse). Each nurse is assigned to only one doctor. (5pts)
• Doctors areassigned topatients. Each patient (resident or outpatient) is assigned only one doctor. But a doctor can have many patients. (5pts).

Make sure that no lines cross each other in your ERD. (5 pts)

Take a screenshot of your ERD and paste it here. Make sure you include the Visio file in your submission.

Problem #2 (35 points): Translate your ERD from problem #1 into a relational schema (a set of tables). Implement your relational schema in Access. Please recall the rules for translating an ERD into a relational schema:

• Each entity set from your ERD becomes a table (5pts)
• Each attribute of an entity set becomes a field in the corresponding table (make sure you choose appropriate data types for your fields) (5pts)
• Each PK of an entity sets becomes the PKs of its corresponding table (5pts)
• Each FK should be translated by creating a relationship between the corresponding fields in Access (make sure you "enforce referential integrity" on your relationships) (15pts)Make sure relationship lines do not cross each other in your relational diagram. (5pts)

Take a screenshot of your relational diagram and paste it here.

Problem #3 (25 points): Insert/type at least 5 rows of data into each table. Make sure your data does not violate primary key and foreign key constraints. The DBMS (Access) will not let you insert a row, if it violates any constraints. (Note: After inserting the data, ask yourself a few questions to verify your design. For example: Which patients are assigned to doctor X? Is patient Y a resident or outpatient? If resident, what room is assigned to this patient? Which nurses are assigned to doctor Z? and so on. If the answers to these questions cannot be determined from your data, it means your table design is flawed. In that case you need to revisit and fix your ERD and your relational schema.)

Include your Access database file in your Blackboard submission. (20pts)