Basic Database Design - Optional Participation

Question

Working on basic database design homework and ERD drawing in Visio, and can't wrap my head around this problem.

United Helpers is a nonprofit organization that provides aid to people after natural disasters. 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. The name, address, and telephone number for each volunteer are tracked. Each volunteer may be assigned to several tasks during the time that he or she is 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 task status. For example, there may be a task with task code “101,” a description of “answer the telephone,” a type of “recurring,” and a status of “ongoing.” There could be another task with a code of “102,” a 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, a 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 organization 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.

•For each item that the organization provides, there is 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 too. 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 that is included.It is possible for the organization to have items donated that have not been included in any package yet, but every package will contain at least one item.

My thought would be entity VOLUNTEER & TASK creating the composite entity ASSIGNMENT which could generate the task PACKING. That task uses PACKING LIST & ITEMS which generates PACKAGE.

However my confidence in this solution is zero. Wondering if it's remotely correct? Or am I completely going about this the wrong way?

Thanks

Lothar

Answer 1

You're going about it in the right way, or at least heading in the right direction, but the end result is wrong (although the entities in the top row of your diagram are more or less correct)

Pay attention to the phrases "For each task, there is a ... task type" and "For all tasks of type “packing,” there is a packing list". This would suggest that a "packing task" is a type of task, not a type of assignment.

Mcmurray

Cheers. Any way you can elaborate on how to make that bridge? I suspected as much, and have tried every way I can think of... – Lothar

I would suggest that a "PackingTask" would have a "TaskID", not a Volunteer or AssignmentID – Mcmurray

I think I get what you are saying, like this? link – Lothar

@Lothar Better. Now look at what Items can belong to. :) – Mcmurray

Answer 2

Before you can do this homework assignment in any meaningful way, you and your teacher need to be of one accord about whether an ERD expresses Data Analysis or Database Design. In previous questions concerning ERD, I've always offered the view that ERD is for data analysis, and database design ought to be expressed in some other diagramming scheme, such as a relational schematic diagram.

However, it's very possible that your teacher doesn't see this the same way I do. A large number of professionals use ERD as an alternative to relational schematics, and express database design in an ERD. Your solution looks like design to me, and not analysis.

If your teacher doesn't distinguish between analysis and design (and some don't) there is something fundamental that you need to learn, and that your teacher will not be able to teach you. You need to distinguish between the features of the problem and the features of the proposed solution to the problem. If you don't make that distinction you end up in one of several pitfalls.

The most common pitfall is coming up with the right solution to the wrong problem. I've seen this happen over and over again in the field.

The second most common pitfall is changing the problem definition in order to make the problem easier to solve. Sometimes this is done intentionally, in order to meet a deadline with limited resouirces. But when it's done unintentionally, a host of problems crop up.

The third pitfall is what may be called "thinking inside of the box". In this pitfall, the would be solver adds a constraint that was not in the original problem definition, but is a feature of the first faulty solution. The "box" is a feature of a proposed (faulty) solution and not a feature of the problem as originally stated. But once it's been accepted as part of the problem, the problem becomes unsolvable.

Sturtevant

Thank you for taking the time to write all of this. The course is in basic database design, and we're learning ERD's Object Oriented Models, UML class diagrams, etc. However I've only had 10 hours of instruction, just covering basics like 'this is a table' so my understanding is rather limited. When studying this type thing it usually seems like a foreign language until I have the epiphany where it all falls into place. I am struggling to find that at this point. I think I understand what you are trying to say but lack the experience to put it in practice? – Lothar

Do you understand the difference between analysis and design? – Sturtevant

Do you understand the difference between a table and an entity? – Sturtevant

Do you understand the role that an ATTRIBUTE plays in relational modeling? In ER modeling? In Object oriented modeling? – Sturtevant

Do you have a background in programming? – Sturtevant

Answer 3

I think it should be like this

A there's only five tables Specified, I thing the packing task, is the task table.

Recalescence