ER Diagram

You have to do Alternate Assignment!

I attached sample solution.

CIS360 Business Database Concepts
Assignment 2 Alternate

E/R Diagramming
Due October 24, 2012

The following assignment is to be drawn using any software drawing package you have access to (Gliffy, PowerPoint,
Visio, etc.). Identify and label all components.

Make sure it is clear and easy to read. Professional quality is expected.

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. 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.

 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 total
weight of the package is 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.

CIS 360 Business Database Concepts
Assignment 2

E/R Diagramming
Due October 3, 2012

The following assignment is to be drawn using any software drawing package you have
access to (Gliffy, PowerPoint, Visio, etc.). Identify and label all components.

Make sure it is clear and easy to read. Professional quality is expected.

1. Draw an E/R diagram for the following situation:

PROJECTS INC., is an engineering firm with approximately 500 employees. A database is
required to keep track of all employees, their skills, projects assigned and departments
worked in. Every employee has a unique number assigned by the firm, required to store
his or her name and date-of-birth. If an employee is currently married to another
employee of Projects, Inc., the date of marriage and who is married to whom must be
stored; however, no record of marriage is required if an employee’s spouse is not also
an employee. Each employee is given a job title (for example, engineer, admin assistant,
foreman, and so on). We are interested in collecting more data specific to engineers
and admin assistants. The relevant data to be recorded for engineers is the type of
degree (e.g., electrical, mechanical, civil, etc.); for admin assistants, it is their typing
speed. An employee does only one type of job at any given time, and we only need to
retain information for an employee’s current job.

There are 11 different departments, each with a unique name. An employee can report
to only one department. Each department has a phone number.

To procure various kinds of equipment, each department deals with many vendors. A
vendor typically supplies equipment to many departments. We are required to store
the name and address of each vendor.

Many employees can work on a project. An employee can work in many projects (for
example, Southwest Refinery, California Petrochemicals, and so on) but can only be
assigned to at most one project in a given city. For each city, we are interested in its
state and population. An employee can have many skills (preparing material
requisitions, checking drawings, and so on), but (s)he may use only a given set of skills
on a particular project. (For example, employee MURHPY may prepare requisitions for
Southwest Refinery project and prepare requisitions as well as check drawings for
California Petrochemicals.) Employees use each skill that they possess in at least one
project. Each skill is assigned a number, and we must store a short description of each
skill. Projects are distinguished by project numbers, and we must store the estimated
cost of each project.

CIS 360 Assignment 2 Solution