Itemized List Template

Itemized List Template Workbook

Harold Pardue, Professor of CIS
Kent Haley, IS Graduate student

The objectives of this workbook are twofold: to assist the student in mastering the mechanics of ER modeling and to illustrate a modeling template that provides a good starting place as a solution to a class[1] of ER modeling problems.

The Itemized List Template (ILT) abstracts a common pattern in ER modeling, namely, cases involving an itemized list. Examples of itemized lists abound in practice. An itemized list can be defined as any document or transaction that lists, enumerates, records, catalogs, specifies, or tallies a collection of items. An item can be anything from a product or service to an mp3 request in a peer-to-peer file swapping session.

The vast majority of business transactions are “lists” which involve a customer or client engaging an agent of an organization (say a clerk or sales representative) to either purchase/receive a product or have a service rendered. These transactions are recorded as, for example, an invoice, order, back order, pick list, sales receipt, lease agreement, insurance claim, or bill of laden.

Beyond contractual business transactions, much of what is stored in databases are lists; further, these lists tend to be associated with or related to other data in the database in a predictable way. Hence an advantage to learning the ILT is that it can provide an effective and efficient starting point for solving much larger and complex modeling problems. The ILT is depicted in Figure 1.

At the center of the ILT are the List and ListItem entities. An itemized list is modeled as two entities: information about the List and the items being enumerated on the list, ListItem.

Note: if the list always contains one and only one instance of the list item, the ListItem entity can be omitted. Examples are provided in subsequent scenarios.

List will usually contain time-oriented information such as the time at which the list was created and last modified and when the product was shipped or service rendered.

Additionally, the List entity typically includes (to list a few):

· Summary or statistical information such as the number of items on the list or the total value of all items on the list

· Client information specific to the list such as shipping or billing address

· The state of the list (active, deleted, archived, back-ordered, in-process…)

ListItem itemizes or enumerates the items associated with List. ListItem typically includes Item information specific to the current instance of the list such as extended price or quantity. ListItem sometimes includes a sequence number.

Item is the entity being itemized or enumerated on the list. In business transactions, this is typically a product or service.

Type classifies or categorizes Item. For example, in a database of musical titles, Type would be genre. In a business context, this could be a product catalog or menu hierarchy. It is often the case that we must model classification schemes or taxonomies associated with item entities.

Most lists involve two participants. One participant acts as a Client who orders, purchases, requests, or is associated with a list of items. A second participant acts as a Broker or agent for the organization, system, or ad hoc interaction. These roles are sometimes interchangeable and the same person. The broker works with the client to negotiate the terms and content of the list and represents the organization or system. Clients and brokers can be persons (customers, renters, sales associates, or clerks for example) or computing devices such as servers, routers, or ATMs.

The relationships and cardinality depicted in Figure 1 are representative only. See our textbook for Crow’s foot notation. I’ll elaborate on the ILT in class.

Figure 1 Itemized List Template

What follows is a selection of database scenarios each with a blank ILT. The objective is to “fill-in” each of the templates using the information provided in the scenario. The notation used throughout this workbook is described in the next paragraph.

Words that give hints for potential entities are shown in bold, e.g. Officer. Words that give hints for potential relationships (i.e. 1:1, 1:M or M:N) are underlined, e.g. one, more, only, single, multiple etc. Words that give hints for optional or required entities and their cardinality are shown in italics, e.g. is, may, must, can, optional, could, at least one, no more than 10 etc. Verb phrase hints are indicated between braces { }.

Appendix I includes ERD solutions, SQL, and transactions.

Scenario: WebDIY

You are responsible for designing a sales database for a web-based Do-It-Yourself (WebDIY) store that specializes in sales to contractors.

The DIY sells a wide range of products (tools, hardware, plumbing etc) via their web site (depending on the availability of shipping). Each contractor is assigned an account manager who completes sales.

The web site contains a hierarchical menu of product categories. For example, one branch in the menu hierarchy is:

Home: Building Products: Plumbing: Pipe & Fittings

The product category must be implemented in the database.

A sample (simplified) sales receipt is given below.

WebDIY
“Brick-and-Mortar meets Click-and-Order”

Sale number: 45634 Account manager: 2345

Contractor number: 3387 Date: December 11, 2005

ProductID Description Quantity Unit Price Ext Price

23229 QT DRYDEX SPACKLI 100 $ 4.87 487.00

95967 JH 1.88" X 60YED CLD 100 $ 6.44 644.00

17705 1/16" TITANIUM DR 200 $ 2.96 296.00

79562 WH IVY CASE 3" 100 $ 14.97 149.70

------

Total $ 3220.70

Business Rules

1. A single sale {itemizes} many product saleitems. A sale must contain at least one product saleitem. A product saleitem must be associated with a sale.

2. A single account manager {completes} many sales. A sale must be completed by an account manager. An account manager is not required to complete a sale (could be a trainee or new hire).

3. A single contractor {requests} many sales. A sale must be for a contractor. A contractor is required to have at least one sale to be in the database.

4. A single product {is sold as} a product saleitem many times. A product saleitem must be a product. A product is not required to be sold as a product saleitem (could be a new product that hasn’t sold yet).

5. A single product category {classifies} many products. A product must be classified as a single category. A category is not required to classify products (could be a new category for which no products are yet stocked).

6. A single product category {contains} many product subcategories. A product subcategory must be classified under a product category. A product category is not required to contain a product subcategory.

Given the information in the above scenario, fill in the ERD template shown on the next page. At the discretion of your instructor, you may work in small groups to discuss this scenario. However, do your best to work on your own to do the following:

1. Fill in the names of all entities on the template (inside grey box).

2. Draw the relationships between the entities.

3. Indicate on the template if the entity is required or optional.

4. Fill in the appropriate verb phrase between each entity.

5. Fill in the attributes for each entity directly mentioned or depicted in the scenario.

6. List the identifiers for each entity.

Scenario: WebDIY

Notes:

Scenario: FEMA cabin assignments

You are responsible for designing a database to track the cabin assignments of hurricane Katrina evacuees on the cruise ship docked in Port of Mobile as part of FEMA’s Katrina relief program.

FEMA agents coordinate with a boarding officer (an employee of the cruise ship) to have an evacuee assigned to a cabin onboard the ship.

A cabin is assigned to an individual evacuee (the head of household in the case of a married couple or family).

If an evacuee is re-assigned to a new cabin, a new cabin assignment is generated. An evacuee occupies a cabin. An occupancy is for one 24 hour period. Consumables (food, personal items, or services) and incidents (medical emergencies, criminal, or civil disturbances) are associated with a specific day of occupancy. Assume that consumables and incidents are beyond the scope of the current analysis.

If an evacuee occupies a specific cabin for say 20 days, the cabin assignment will record (itemize) 20 occupancy records, one for each day.

Business Rules

1. A single cabin assignment {itemizes} many occupancy(ies). A cabin assignment must itemize at least one occupancy. An occupancy must be associated with a cabin assignment.

2. A cabin assignment includes the name of the head of household (evacuee), the number of persons occupying the cabin, and the cabin assignment request date.

3. An occupancy has a begin time and end time of occupancy and the maximum number of days a cabin can be occupied.

4. A single cruise ship boarding officer {grants} many FEMA cabin assignments. A cabin assignment must be granted by a boarding officer. A boarding officer is not required to grant a cabin assignment.

5. A single FEMA agent {requests} many cabin assignments. A cabin assignment must be requested by a FEMA agent. A FEMA agent is not required to request a cabin assignment.

6. A FEMA agent can be a supervisor to other FEMA agents. A single FEMA agent {supervises} many FEMA agents. A FEMA agent is not required to be supervised and a FEMA agent is not required to be a supervisor.

7. A single cruise ship cabin {is occupied as} an occupancy many times. An occupancy must be a cabin. A cabin is not required to be occupied as a FEMA occupancy (for example, cabins classified as crew quarters).

8. A single cabin type {classifies} many cabins. For example, a cabin can be classified as (standard inside, ocean view, balconied, or suite) A cabin must be classified under a single cabin type. A cabin type is not required to classify a cabin.

1. Fill in the names of all entities on the template (inside grey box).

2. Draw the relationships between the entities.

3. Indicate on the template if the entity is required or optional.

4. Fill in the appropriate verb phrase between each entity.

5. Fill in the attributes for each entity directly mentioned or depicted in the scenario.

6. List the identifiers for each entity.

Scenario: FEMA cabin assignments

Notes:
Scenario: Exam builder

You are responsible for designing a database for a web-based multiple-choice exam system for the school of CIS course assessment program. The effectiveness of each course in the CIS curriculum will be assessed each semester by administering a standardized multiple-choice exam designed specifically for each course.

Questions that appear on a course-specific assessment exam are classified by learning units. Learning units specify educational goals such as “to present architectures, topologies, and protocols of telecommunications.”

A sample exam containing two questions is depicted below.

Business Rules

1. A single exam {contains} many exam questions. An exam must contain at least one exam question. An exam question must be associated with an exam.

2. An exam question has a question stem, four choices (choiceA, choiceB, choiceC, and choiceD) and a correct answer. We’ll discuss this (A, B, C, D) structure later in the chapter on normalization. Question items are number sequentially.

3. A single instructor {creates} many exams. An exam must be created by an instructor. An instructor is not required to create an exam (an instructor could use an exam created by another instructor).

4. A single course {is assessed by} many exams (a different exam each semester perhaps). An exam must be created for a specific course. A course is not required to be assessed by an exam (some elective courses are not assessed).

5. A single question {appears as} an exam question many times. An exam question must be a question. A question is not required to appear as an exam question (could be a newly written question that hasn’t been put on an exam).

6. A single leaning unit {classifies} many questions. A question must be classified under a single learning unit. A learning unit is not required to classify questions (we don’t teach all learning units in the curriculum).

1. Fill in the names of all entities on the template (inside grey box).

2. Draw the relationships between the entities.

3. Indicate on the template if the entity is required or optional.

4. Fill in the appropriate verb phrase between each entity.

5. Fill in the attributes for each entity directly mentioned or depicted in the scenario.

6. List the identifiers for each entity.

Scenario: Exam builder

Notes:

Scenario: Military Officer Promotion List

Your company has won a contract with the government to implement a database solution which produces a military officer promotion list. Once this solution has been tested and fully approved by your company, it will be used specifically by the U.S. Coast Guard to help produce promotion lists for the annual promotion cycle. During your interview with the Coast Guard contracting officer, you have determined that your solution needs to include the following business rules: