Question

The pandemic of coronavirus disease 2019 (COVID-19) in Malaysia was first detected on 25 January. Reported cases remained relatively low and largely confined to imported cases, until localised clusters began to emerge in March. To stem the number of new cases, the government imposed a Movement Control Order (MCO) starting March 18, 2020.

Assume that you have been appointed as a database designer for a Non-Governmental Organization (NGO) in your residence community. The NGO's main task is to gather information on the current situation of the pandemic in the local community during the MCO period. This includes information on the percentage of cases detected, the quarantine center provided as well as the services available to assist affected community members. The NGO is planning to implement a database system to manage all information to ensure community members are aware of current situations.

Therefore, as a database designer, you are required to propose a database design prior to its implementation by presenting the following:

1. Identify ALL possible entities that could exist in the business process (give suitable entity name), complete all the entities with suitable attributes together with assigned primary key. (Use your own assumption to determine the attributes.)
2. Identify ALL relationships, cardinality, and/or participation for each identified entity.
3. Draw an Entity Relationship Diagram (ERD) by clearly stating the relationships between entities, cardinalities and attributes with correct notation.
4. Based on ERD created in (c), convert it into relational schema. State the primary key and foreign key for each relational schema (relations).
5. Produce sample records for each relation (table) that observes the relational integrity rules. FIVE records per relations are enough.
6. 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.)

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 which 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).

Answer 1

a) entities with attributes

PATIENT_DATA(p_name,p_address,p_id,p_mobileNO,p_age)

Q_CENTRE(Q_name,address,capacity,staff_capacity)

No_of_cases_detected(date,address,p_id,Q_name)

No_of_cases_recovered(date,address,p_id,Q_name)

Deaths(p_id,address,Q_name)

services(equipments,specialists,Q_name)

source_of_infection(address,p_id)

b,c):d).