Reference no: EM132938707

PURPOSE

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 COVID-19 vaccination programme in Malaysia is a vaccination campaign that is currently being implemented by the Malaysian government and was launched on 24th Feb 2021. It is an approach in curbing and ending the spread of coronavirus disease 2019 (COVID-19) by successfully achieving the herd immunity among its citizens and non-citizens that are residing in Malaysia.

Assume that you have been appointed as a database designer for a Non-Governmental Organization (NGO) in your residential community. The main task of this NGO is to gather information on the implementation of immunisation programs among community members. This includes information on individuals who have / have not registered for vaccinations, information on appointments and vaccination facilities, and information on individuals who are / have completed their vaccinations. The NGO plans to implement a database system in managing all information to ensure community members are not left behind from this immunization program.

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

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

Attachment:- Database.rar