ICT3722 Assignment 4 solution 2024

ICT3722 Assignment 4 solution 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: oSelect all beneficiaries along with their personal information. oUpdate the medication dosage for a specific beneficiary. oDelete the record of a beneficiary who is no longer receiving services from Batho Pele. oSelect all beneficiaries who have allergies to a specific substance. oDrop the table containing exercise records (if deemed necessary). oUpdate the next of kin details for a beneficiary. oSelect all beneficiaries who participate in a specific sport. oDelete the record of a medication that is no longer prescribed to any beneficiary. oUpdate the diet preferences of a specific beneficiary. oSelect all beneficiaries who require personalized exercises. Assignment Questions: 1. Create ERD that shows the relationship between the 4 tables. (10 Marks) 2. Create the specified tables with appropriate attributes and data types, and relationships. (40 Marks) 33. Explain the importance of maintaining accurate records for old age pensioners in organizations like Batho Pele. (5 Marks) 4. Discuss the relationship between the Beneficiaries table and the Medications table in the database schema. (5 Marks) 5. Provide an SQL query to select all beneficiaries along with their personal information from the database. (5 Marks) 6. Explain the significance of using constraints in database design and how they are applied in the Batho Pele database. (5 Marks) 7. Write an SQL query to update the medication dosage for a beneficiary with the ID ‘1’. (5 Marks) 8. Discuss the potential challenges and ethical considerations in managing sensitive health information in a database system. (5 Marks) 9. Provide an SQL query to delete the record of a beneficiary with the ID ‘6’ from the database. (5 Marks) 10. Explain the concept of normalization and how it applies to the database schema of Batho Pele NGO. (5 Marks) 11. Discuss the benefits and limitations of using SQL for database management tasks in organizations like Batho Pele. (5 Marks)