Designing a fully functional Hospital Database using MySQL and a project report

Database Must meet the following requirements:

• The goal of this project is to develop a general database for a hospital that meets the following requirements:
• This hospital has physicians and nurses and gives services to patients.
• The hospital contains several rooms.
• Every room has a unique number, capacity, and fee for one night.
• A patient has a name, unique id, address, and phone number.
• A patient may have a health record that includes a unique id for each patient, disease, date, status, and description.
• A physician has a unique id, name, certification number, field of expertise, address, and phone number.
• A nurse has a unique id, name, certification number, address, and phone number.
• Every patient is hospitalized in a specific room for some nights.
• Every patient has some physicians who monitor the patient for a specific duration.
• A physician orders some instructions for a patient on a specific date.
• A number of nurses execute the physician's order for a specific patient on a date, and the execution results in a status.
• Instruction has a unique code, fee, and description.
• Nurses also provide medications to patients.
• Each patient has specific medications, and there is a specific amount of medication that is given to the patient daily by nurses.
• Patients get medicine from outside pharmacies, so the hospital does not charge them for medicine.
• All the cares that are provided for a patient are recorded, and the hospital issues an invoice for payable items such as rooms and instructions.
• Patients make payments for the hospital that includes date and amount.

Your report must contain:

1. A complete EERD that satisfies the requirements. Make sure the ERD is on one page.

• Must contain the cardinality of all the relationships in the diagram
• Capturing all the entities and attributes of the universe of discourse
• Specifying the entity types including their primary keys and their attributes
• Specifying the correct relationships and the cardinality of the relationships and also attributes of the relationships

2. The set of all used assumptions that are not covered by the given requirements.

3. The set of relations, including their primary keys and foreign keys.

4. The description, body, and execution result of your SQL queries (Your need to include at leas 15 queries.

5. The set of queries must contain at least three join queries, three aggregation queries, and three nested queries)

6. The description and body of views. Also, include a discussion on why the provided view is useful for your database.

7. The description and body of triggers. Also, include a discussion on why the provided trigger is useful for your database.

8. The description and body of transactions. Also, include a discussion on why the provided transaction is useful for your database.

The outcome of this project is a working hospital database in the MySQL environment as well as a full report.

You must have directory -hospitalcontaining the following items:

• hospital-schema.sql that constructs your tables and views.
• hospital-data.sql that populates your tables.
• hospital-query.sql that contains the description and SQL of your queries.
• Final report