Question $1(40$ marks$)$

a$)$ Discuss the database system environment.

$(5$ marks$)$

b$)$ United Helpers is a nonprofit organisation that provides aid to people after natural disasters. The organisation wants to develop a database system to computerise its operations. A brief description of the organisation operations is as follows.

The organisation has many volunteers who carry out the tasks. The name, address and telephone number are tracked for each volunteer, a unique number is assigned to each volunteer for identification. Each volunteer may be assigned to several tasks and some tasks require many volunteers. When a volunteer is assigned to a task, the system should track the start date and end date of that assignment.

example, there may be a task with task code $"100",$ a description of "answer the telephone", a type of "recurring", and a status of "ongoing". Another task may have

supplies", a type of "packing", and a status of "open"

For all tasks of type "packing", there is a packing list that specifies the content of the packages. There are many packing lists to produce different packages, such as basic medical packages, child$-$care packages, and food packages,

etc. Each

packing list has an ID number, a packing list name, and a packing list description, which describes the items that should make up the package. Every packing task is associated with only one packing list. A packing list may have many packing tasks.

Packing tasks result in the creation of packages. Each package of supplies produced by the organisation is tracked, each package is assigned an ID number.

The date the package was created and its total weight are recorded. A given package is associated with only one task. Some tasks $($such as "answer the phone$)$ will not produce any packages, while other tasks $($such as "prepare $5000$ packages of basic medical supplies"$)$ will be associated with many packages.

The actual items included in each package should be tracked. A package can contain many items, and a given item can be used in different packages. Each item that the organisation provides has an item ID number, item description, item value and item quantity on hand stored in the system. It is possible for the organisation to have items that have not been included in any package yet, but every package will contain at least one item.

i$)$

$)$

Provide the Entity Relationship diagram to describe the data aspect of this system. Write out any assumptions you made.

$(22$ marks$)$

Convert the ER diagram to the corresponding relational schema $($set of relations$).$ Indicating primary keys and foreign keys.

$(13$ marks$)$