In response to the ongoing challenges posed by the COVID$-19$ pandemic, the Ministry of Health for each country has initiated a domestic healthcare campaign to promote healthcare services and support local medical facilities.

Assuming that you are assigned to design and implement a Hospital Information System for a private hospital in Vietnam. The hospital has various departments, medical staff, patients, and administrative operations that need to be managed efficiently. Your database should facilitate the storage and retrieval of information related to patients, appointments, medical records, and staff.

The scope of this project includes the database design and implementation by providing the following tasks:

$($a$)$ Identify ALL possible entities that are related to Hospital Information System $($give suitable entity name$),$ and complete all the entities with suitable attributes together with a primary key.

$($Use your assumption to determine the attributes.$)$

Determination of main entities that might be included in the scenario. The entities should cover these details:

At least FIVE entities are determined with clear description.

ALL identified entities are relevant with the scenario given.

Using accurate labelling and symbols.

Determination of attributes and primary key for each entity. The attributes should cover these details:

ALL identified entities have at least THREE attributes and ONE attribute key $($primary key$).$

ALL identified attributes and keys are relevant with the scenario given.

Using accurate labelling and symbols.

$($b$)$ Identify ALL relationships, cardinality, and$/$or participation for each identified entity.

Determination of relationships, cardinality and$/$or participation. The relationships should cover these details:

ALL identified entities are connected by relevant relationships.

ALL relationships have accurate cardinality and$/$or participation

ALL identified relationships are relevant with the scenario given.

Using accurate labelling and symbols.

$($c$)$ Draw an Entity Relationship Diagram $($ERD$)$ by clearly stating the relationships between entities, cardinalities and attributes by using the correct notation.

Entity$-$Relationship Diagram $($ERD$)$ drawing for the database proposed. The diagram should cover these details:

ALL identified entities and attributes are presented on the ER diagram.

ALL identified relationships with their cardinalities$/$ participations are presented on the ER diagram.

Using accurate labelling and symbols.

Using correct arrangement of diagrams

$($d$)$ Based on ERD created in $($c$),$ convert it into a relational schema. State the primary key and foreign key for each relational schema $($relations$).$

Produce Relational Schema. The schemas should cover these details:

ALL identified entities and attributes are presented on the relational schema.

ALL relational schemas have accurate primary and foreign keys.

Using appropriate labeling and symbols.

$($e$)$ Create all relevant relations $($tables$)$ based on the relational schema in $($d$).$ Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each relation $($table$)$ created.

Create all relevant relations $($tables$)$ based on the identified relational schema in $($d$)$:

Creation of relations $($tables$)$ are based on relational schema that has been identified.

The name of the tables and its fields follows the relational schema stated.

The data types for every field are suitable.

Provide the SQL screenshot

$($f$)$ Produce sample records for each relation $($table$)$ that observe the relational integrity rules. FIVE records per relation are enough.

Produce sample record and the records should cover these details:

At least FIVE records for each relation $($table$).$

ALL the records follow the relational integrity.

$($g$)$ Then, produce FIVE SQL queries that can be used to manipulate the data from each relation and show ALL the results from each of the queries. $($The queries should include a simple query, query for sorting results, aggregate function, sub$-$queries and query to alter the existing tables.$)$

Produce SQL queries to manipulate the data from each relation $($table$)$:

At least FIVE SQL queries.

The queries should include a simple query, query for sorting results, aggregate

function, sub$-$queries and query to alter the existing tables.

Result of the SQL queries generated:

Show all the results from each of the queries generated.

Note:

You are free to use any Databases Management System $($DBMS$)$ tool $($using DDL SQL or GUI$).$

You should submit a printout $($screen capture$)$ of:

$1.$ Each table shows the table name, fields and sample records.

$2.$ Each query in SQL View, so that the grader can see your SQL code.

$3.$ The result of each query $($in table format$).$