ICT$3722/104/0/2024$

Scenario: Batho Pele is a local NGO dedicated to providing support and care for old age pensioners in the community. The organisation struggles with keeping accurate records of its beneficiaries, including their personal information, health needs, medications, personalised exercises, diet preferences, allergies, sports activities, next$-$of$-$kin details, and more. As a database administrator for Batho Pele, your task is to design and implement a database system to efficiently manage and maintain these records.

Assignment Tasks:

Design a relational database schema for Batho Pele NGO using the provided tables $($See end of document$).$

Create the specified tables with appropriate attributes and relationships.

Populate the tables with sample data for testing and demonstration purposes as shown on the tables below.

Write SQL queries to perform the following tasks:

Select all beneficiaries along with their personal information.

Update the medication dosage for a specific beneficiary.

Delete the record of a beneficiary who is no longer receiving services from Batho Pele.

Select all beneficiaries who have allergies to a specific substance.

Drop the table containing exercise records $($if deemed necessary$).$

Update the next of kin details for a beneficiary.

Select all beneficiaries who participate in a specific sport.

Delete the record of a medication that is no longer prescribed to any beneficiary.

Update the diet preferences of a specific beneficiary.

Select all beneficiaries who require personalized exercises.

Assignment Questions:

Create ERD that shows the relationship between the $4$ tables. $(10$ Marks$)$

Create the specified tables with appropriate attributes and data types, and relationships. $(40$ Marks$)$

$3$