Business rules and requirements:

Aotearoa Helpers is a nonprofit organization that provides aid to people after natural

disasters. They mainly work with volunteers to provide their services. The organization

has been relying on Excel to manage its data. Recently, the Director of Operations

has realized that it has many data quality problems and asked you to help them design

a database to better manage its data.

$(1)$ Create an ERD using the following business rules and requirements. Please make

sure that the ERD follows good database design practices covered in Chapter $6.$

$$ Individuals volunteer their time to carry out the tasks of the organisation. For

each volunteer, their name, address, and telephone number are tracked. Each

volunteer may be assigned to several tasks during the time that they are doing

volunteer work, and some tasks require many volunteers. It is possible for a

volunteer to be in the system without having been assigned a task yet. It is

possible to have tasks that no one has been assigned. When a volunteer is

assigned to a task, the system should track the start time and end time of that

assignment.

$$ Some volunteers are part of volunteer organisations $($e$.$g$.,$ Volunteering New

Zealand, Volunteer Auckland$),$ while others are not part of any volunteer

organisations. A volunteer may be part of more than one volunteer organisation.

A volunteer organisation may have many members. We need to keep track of

which organisations these volunteers are members of and the start dates of

their membership. For each volunteer organisation, their name, address,

contact person and telephone number are tracked.

$$ For each task, there is a task code, task description, task type, and task status.

For example, there may be a task with task code $101,$ description of $$answer

the telephone,$$ a type of $$recurring$,$ and status of $$ongoing$.$ There could be

another task with a code of $102,$ description of $$prepare $5000$ packages of

basic medical supplies,$$ a type of $$packing$,$ and a status of $$open$.$

$$ For all tasks of type $$packing$,$ there is a packing list that specifies the contents

of the packages. There are many different packing lists to produce different

packages, such as basic medical packages, childcare packages, food

packages, etc. Each packing list has a packing list ID number, packing list

name, and a packing list description, which describes the items that ideally go into making that type of package. Every packing task is associated with only

one packing list. A packing list may not be associated with any tasks, or may

be associated with many tasks. Tasks that are not packing tasks are not

associated with any packing list.

$$ Packing tasks result in the creation of packages. Each individual package of

supplies that is produced by the organisation is tracked. Each package is

assigned an ID number. The date the package was created, and the total weight

of the package are recorded. A given package is associated with only one task.

Some tasks $($e$.$g$.,$answer the phones$)$ will not have produced any packages,

while other tasks $($e$.$g$.,$prepare $5000$ packages of basic medical supplies$)$ will

be associated with many packages.

$$ The packing list describes the ideal contents of each package, but it is not

always possible to include the ideal number of each item. Therefore, the actual

items included in each package should be tracked. A package can contain

many different items, and a given item can be used in many 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. Along

with tracking the actual items that are placed in each package, the quantity of

each item placed in the package must be tracked as well. For example, a

packing list may state that basic medical packages should include $100$

bandages, $4$ bottles of iodine, and $4$ bottles of hydrogen peroxide. However,

because of the limited supply of items, a given package may include only $10$

bandages, $1$ bottle of iodine, and no hydrogen peroxide. The fact that this

package includes bandages and iodine needs to be recorded along with the

quantity of each item included. 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.

$(2)$ Choose three entities from the ERD that you develop, create three tables, and

enter five rows of data into the tables. One of the tables should have at least one

foreign key that is a primary key of one of the other two tables. Capture the

screenshots of the table structure and rows of meaningful data and put them in

the WORD document after the ERD. For instance, a volunteer name should not

be $$aaaaaaa$.$ Save the commands that you use to create tables and enter data

into the tables in a SQL script file.