IE 412/512: Information Systems Engineering
Fall 2018
Term Project – Part 1: Conceptual Design
E-R Schema Diagram Business Rules
Due on Tuesday, October 23, 2018, as a HARD COPY at the beginning of the lecture session
IMPORTANT NOTE
Carefully review this entire document and pay special attention to the REQUIREMENTS section.
INTRODUCTION
The Blue Ridge Apparel Group (BRAG) sells clothing, jewelry, and footwear items directly to
consumers through its web-based portal. The products supplied to BRAG by their multiple vendors are
first packed in cases. Multiple cases are then aggregated and assembled into pallets for delivery to
BRAG’s main warehouse. After pallets arrive at BRAG’s warehouse, they are verified against a
purchase order and inspected. Once the verification and inspection processes are completed, warehouse
personnel at the receiving department break down the pallets, put away the items into their
corresponding storage location, and update the inventory record.
OVERALL TERM PROJECT OBJECTIVE
In partial fulfillment of the requirements of this course, you must develop a database application that
will facilitate the organization, maintenance, and querying of data pertaining to BRAG’s web-based
retail sales operations.
MAIN DELIVERABLES
The main deliverables of Part 1 of the term project are (1) an Entity-Relationship (E-R) schema
diagram representing the conceptual design of BRAG’s database application, and (2) the assumed
business rules that support the relationships among the main entities included in the E-R schema
diagram.
BACKGROUND
This section introduces the basic concepts and terminology associated with the mini-world of BRAG’s
web-based retail sales operations to assist you in the development of the main deliverables of Part 1 of
the term project. In addition to these basic concepts and terminology, you must interact as much as
possible with your two main customers (i.e., Dr. Porter and Sylvan Hoover) to gain a clear
understanding of the expectations for your conceptual design.
Vendor
Several vendors supply the items that BRAG sells through its web-based portal. A vendor may
have zero to many active purchase orders, but a purchase order is always associated with a single vendor.
A vendor has an identifier (vendor number), name, address, phone number, and email address.
2
Purchase Order
BRAG orders items from its vendors via a purchase order. A purchase order may be composed
of many pallets, and a single pallet may contain many cases. BRAG accepts mixed pallets from its
vendors (i.e., a pallet may contain cases of different types of items), but a case must contain a single
type of item. A purchase order has an identifier (purchase order number), receipt date, number of pallets,
and item number (i.e., the identifiers of the item types included in the pallet).
Pallet
Pallets are used by vendors to ship cases of items to BRAG. These pallets, which are owned by
BRAG, are made of a durable composite material and can be individually tracked by means of a radio
frequency identification (RFID) tag. BRAG owns several thousand pallets, which it distributes among
its vendors so that they can fulfill orders. BRAG also keeps a few hundred pallets in reserve at its
warehouse to prevent shortages within its retail supply chain. A pallet has an identifier (pallet ID),
weight, volume, and case quantity.
Case
A case is used to ship a single type of item (e.g., shirts or pants). Cases are also owned by
BRAG and can be individually tracked by means of an RFID tag. BRAG owns several thousand cases,
which it distributes among their vendors so that they can fulfill orders. BRAG also keeps a few hundred
cases in reserve at its warehouse to prevent a shortage within its retail supply chain. A case has an
identifier (case ID), item number, item quantity, volume, and weight.
Inventory
Pallets are delivered by vendors to BRAG’s main warehouse. Once a shipment arrives at the
loading dock, the truck driver presents shipping papers (e.g., a Bill of Lading and a packing list) to the
warehouse personnel in the receiving area. The warehouse personnel verif ies the shipment contents
against a purchase order to ensure that the right items and the right quantities are being delivered. If
everything is in order, warehouse personnel will break down the pallets and store the items contained in
the cases in one or many of the thousands of inventory locations within the warehouse. An inventory has
an identifier (location number) and item quantity per location.
Customer Order
Customers order items from BRAG via a customer order. Fulfilling customer orders depletes
BRAG’s inventory. A customer may order many different types of items via a purchase order. In
addition, a customer may have zero to many purchase orders in process at any time. A customer order
has an identifier (order number), order date, requested ship date, actual ship date, shipping carrier,
number of items, item number, and item quantity.
Item
BRAG sells many different types of items to customers including belts, footwear, jewelry,
neckwear, outerwear, shirts, and slacks. An item has an identifier (item number), description, price,
weight, annual sales, and stock location.
Customer
Customers order items from BRAG through their web-based sales portal. A customer may have
several active orders, but there may also be customers with no active orders. A customer has an
identifier (customer number), name, address, phone number, and email address.
3
REQUIREMENTS
To obtain full credit in Part 1 of the term project, your deliverable must include the following:
1. The cover page provided at the end of this document. You must add team member name(s) and
course section to this page electronically.
2. An E-R schema diagram of your conceptual design showing your understanding of BRAG’s web-
based retail sales operations mini-world. Your E-R schema diagram must be consistent with the E-
R modeling guidelines that have been presented and discussed in class, and must include the
following:
Entities. Identify and properly represent the main entities of BRAG’s web-based retail sales
operations mini-world.
Attributes. Identify and properly represent single, composite, multi-valued, derived, and
key attributes.
Relationships. For each relationship, properly identify the participating entities. Also,
identify and properly represent cardinality and participation associated with each side of the
relationship (using Chen’s notation), and any attributes that are associated with the
relationship.
Your E-R schema diagram must:
Be drawn electronically using a computer application such as Microsoft (MS) Visio or MS
PowerPoint.
Use names for entities, attributes, and relationships that comply with the terminology
presented in the background section of this document.
Be neatly organized and easy to read.
3. In a separate page, you must list and describe all the business rules that you assumed when
constructing the E-R schema diagram of your conceptual design.
GRADING
Part 1 of the term project will be graded for both accuracy and quality using the following criteria:
CATEGORY WEIGHT
Accuracy
Understanding of BRAG’s Internet-based retail sales operations mini-
world, as described in this document.
Level of face-to-face interaction with the project’s main customers.
Explanation and justification of the business rules depicted by E-R schema
diagram.
70 points
Quality
Use of appropriate E-R schema constructs.
E-R schema diagram is properly constructed and well organized.
30 points
100 points
4
5
TEAMS
Undergraduate Students
Undergraduate students may work in teams of up to two individuals. Teams with more than two
members must be authorized by the class and lab instructors . Term project teams will be composed
of students that are registered in the same lab session. Each team must turn-in only one project report.
Graduate Students
Graduate students must work individually.
SUBMISSION INSTRUCTIONS
The deliverable for Part 1 of the term project must be prepared electronically but submitted as a HARD
COPY at the beginning of the lecture session on Tuesday, October 23, 2018. Late submissions are
not allowed.
In preparing and submitting your solution to Part 1 of the term project, keep the following in mind:
Do not submit the project description with your solution.
Use the last page of this document as the cover page (first page) for your deliverable. Do not
forget to add the names of all team members electronically, as well as the course section
you are enrolled in (i.e., IE 412 or IE 512).
Academic Integrity requirements:
You may use books (other than your textbook), websites, and other sources to solve this
assignment as long as you cite them. For specific examples of how to cite work extracted
from different types of sources properly, please consult the APA style. guide available via
this link.
Term project solutions from prior offerings of the course are not acceptable materials to
consult.
IE 412/512: Information Systems Engineering
Fall 2018
Term Project – Part 1: Conceptual Design
E-R Schema Diagram Business Rules
Student Name(s):
Course #: