Cddb3103 Introduction To Database Assessment Answer

This assignment requires student to draw a complete Entity Relationship Diagram (ERD) and create the relational database based on the given scenario with its business rules. 

REQUIREMENT / ASSIGNMENT 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 Organisation (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:

(a) 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.)

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

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

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

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

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

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