BCIS 4620 – Assignment #2
1. A company wants to track which employees are managed by other employees. An employee does
not have to manage employees, but could manage multiple employees. Additionally, an employee
may not have a manager, but if they do, they have only one. Model this using Crow’s Foot
notation.
2. Now let’s say that the company has changed their business rules. An employee still does not have
to manage employees, but could manage multiple employees. However, now, an employee may
not have a manager, but if they do, they have up to three managers. Model this using Crow’s Foot
notation.
3. Denton Assist 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.
Volunteers carry out the tasks of the organization. The name, address, and telephone number are
tracked for each volunteer. Each volunteer may be assigned to several tasks, and some tasks require
many volunteers. A volunteer might 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.
Each task has 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.” Another task might have a code of “102,” a description of “prepare 5,000
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 packing lists to produce different packages, such as basic medical packages, child-
care packages, and food packages. 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 not be associated with any tasks, or it
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 produced by the
organization is tracked, and 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 phones”) will not produce any packages, while other tasks (such as
“prepare 5,000 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
Each item that the organization 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
the package includes bandages and iodine needs to be recorded along with the quantity of each
item included. It is possible for the organization to have items that have not been included in any
package yet, but every package will contain at least one item.
4. Using the Crow’s Foot notation, create an ERD that can be implemented for a physical therapy clinic
using the following business rules:
A patient can make many appointments with one or more therapists in the clinic, and a therapist
can accept appointments with many patients. However, each appointment is made with only one
therapist and one patient.
The clinic accepts walk-in cases that do not require an appointment. However, for appointment
management purposes, a walk-in appointment is entered in the appointment book as
“unscheduled.”
If kept, an appointment yields a visit with the therapist specified in the appointment. The visit yields
the condition they are receiving treatment for, an evaluation of the condition and, when
appropriate, treatment.
With each visit, the patient’s records are updated to provide a medical history.
Each patient visit creates a bill. Each patient visit is billed by one therapist, and each therapist can
bill many patients.
Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover
more than one bill.
A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance
company.
If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.
5. Luxury-Oriented Scenic Tours (LOST) provides guided tours to groups of visitors to the Washington,
D.C. area. In recent years, LOST has grown quickly and is having difficulty keeping up with all of the
various information needs of the company. The company’s operations are as follows:
LOST offers many different tours. For each tour, the tour name, approximate length (in hours), and
fee charged is needed. Guides are identified by an employee ID, but the system should also record a
guide’s name, home address, and date of hire. Guides take a test to be qualified to lead specific
tours. It is important to know which guides are qualified to lead which tours and the date that they
completed the qualification test for each tour. A guide may be qualified to lead many different
tours. A tour can have many different qualified guides. New guides may or may not be qualified to
lead any tours, just as a new tour may or may not have any qualified guides.
Every tour must be designed to visit at least three locations. For each location, a name, type, and
official description are kept. Some locations (such as the White House) are visited by more than one
tour, while others (such as Arlington Cemetery) are visited by a single tour. All locations are visited
by at least one tour. The order in which the tour visits each location should be tracked as well.
When a tour is actually given, that is referred to as an “outing.” LOST schedules outings well in
advance so they can be advertised and so employees can understand their upcoming work
schedules. A tour can have many scheduled outings, although newly designed tours may not have
any outings scheduled. Each outing is for a single tour and is scheduled for a particular date and
time. All outings must be associated with a tour. All tours at LOST are guided tours, so a guide must
be assigned to each outing. Each outing has one and only one guide. Guides are occasionally asked
to lead an outing of a tour even if they are not officially qualified to lead that tour. Newly hired
guides may not have ever been scheduled to lead any outings. Tourists, called “clients” by LOST, pay
to join a scheduled outing. For each client, the name and telephone number are recorded. Clients
may sign up to join many different outings, and each outing can have many clients. Information is
kept only on clients who have signed up for at least one outing, although newly scheduled outings
may not have any clients signed up yet.
6. An art museum owns a large volume of works of art. Each work of art is described by an item
code (identifier), title, type, and size; size is further composed of height, width, and weight. A
work of art is developed by an artist, but the artist for some works is unknown. An artist is
described by an artist ID (identifier), name, date of birth, and date of death (which is null for still
living artists). Only data about artists for works currently owned by the museum are kept in the
database. At any point in time, a work of art is either on display at the museum, held in storage,
away from the museum as part of a traveling show, or on loan to another gallery. If on display at
the museum, a work of art is also described by its location within the museum. A traveling show
is described by a show ID (identifier), the city in which the show is currently appearing, and the
start and end dates of the show. Many of the museum works may be part of a given show, and
only active shows with at least one museum work of art need be represented in the database.
Finally, another gallery is described by a gallery ID (identifier), name, and city. The museum
wants to retain a complete history of loaning a work of art to other galleries, and each time a
work is loaned, the museum wants to know the date the work was loaned and the date it was
returned.
