Submission of Case Analysis
1. The format is as follows:
i. No. of pages: 3-4 pages (approximately 900-1000 words)
ii. Font: Arial
iii. Font size: 12pt
iv. Spacing: 1.5 spacing
2. Do not forget to write your NAME, your STUDENT ID on the front page of your answers.
3. For desktop research, all references (URL) need to be listed.
4. You are expected to provide quality contributions, analysis and assessment of the case.
5. Submission is using Words format.
6. Due date for this assignment is 30 Nov 2020
Case Study
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:
(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) Produce sample records for each relation (table) that observes the relational integrity rules. FIVE records per relations are enough.
(f) 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).