Question

case study

1.    Draw an Entity Relationship Diagram (ERD) for the following problem. Make sure you identify correct relationships, attributes, and identifiers (keys).

2.    Implement the database design into tables using an industrial-strength database management system (e.g. Oracle SQL+).

case:

HELP is a voluntary organization that provides aid to people. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot ERD.

Individuals volunteer their time to carry out the tasks of the organization. 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.  

For each task, there is a task code, task description, task type, and a task status. For example, there may be a task with task code “101,” description of “answer the telephone,” a type of “recurring,” and a 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, child care 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.

Answer 1

Based on given scenario, the Entities are:

• Volunteer
• Assignement
• Task
• Item
• Content
• Package
• Packagelist

Key points:

• Each volunteer may be assigned to several tasks, and some tasks require many volunteers. (M:M relationship)
• When a volunteer is assigned to a task, the system should track the start time and end time of that assignment. As relationship betweem Volunteer and Task is M:M, we need seperate table as Assignment to track assignment details.
• For all tasks of type “packing,” there is a packing list that specifies the contents of the packages.
• 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

Entity Relationship Diagram (ERD) for the given scenario:

Tables:

Volunteer Table:

CREATE TABLE Volunteer(
Vol_ID int NOT NULL,
Name VARCHAR(255),
Telephone VARCHAR(20),
Address VARCHAR(255),
PRIMARY KEY (Vol_ID)
);

Task Table:

CREATE TABLE Task(
Task_Code int NOT NULL,
List_ID int NOT NULL,
Description VARCHAR(255),
Type VARCHAR(255),
Status VARCHAR(255),
PRIMARY KEY (Task_Code),
FOREIGN KEY (List_ID) REFERENCES Pachagelist(List_ID)
);

Assignment Table

CREATE TABLE Assignment(
Task_Code int NOT NULL,
Vol_ID int NOT NULL,
Description VARCHAR(255),
Start_time DATE,
End_time DATE,
PRIMARY KEY (Task_Code),
PRIMARY KEY (Vol_ID),
FOREIGN KEY (Vol_ID) REFERENCES Volunteer(Vol_ID),
FOREIGN KEY (Task_Code) REFERENCES Task(Task_Code)
);

Packagelist Table:

CREATE TABLE Packagelist(
List_ID int NOT NULL,
Name VARCHAR(255),
Description VARCHAR(255),
PRIMARY KEY (List_ID)
);

Package Table:

CREATE TABLE Package(
Pack_ID int NOT NULL,
Task_Code int NOT NULL,
Date DATE,
Weight int,
PRIMARY KEY (Pack_ID),
FOREIGN KEY (Task_Code) REFERENCES Task(Task_Code)
);

Item Table:

CREATE TABLE Item(
Item_ID int NOT NULL,
Description VARCHAR(255),
Value REAL(8,2),
Quantity int,
PRIMARY KEY (Item_ID)
);

Content Table

CREATE TABLE Content(
Item_ID int NOT NULL,
Pack_ID int NOT NULL,
Quantity int,
PRIMARY KEY (Item_ID),
PRIMARY KEY (Pack_ID),
FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID),
FOREIGN KEY (Pack_ID) REFERENCES Package(Pack_ID)
);