1

QUESTION 1: THE DATA DESIGN PROCESS.

Imagine that you have been assigned to a team that will be developing an inventory tracking
system. As part of the project startup, your manager has asked each team leader to bring a basic
work plan to the next meeting. At that meeting, these work plans will be analyzed to determine
the overall project timeframe, costs, personnel requirements and software requirements.

For now, as the team leader for the data design team, you have been asked to bring a work plan
that identifies the phases of data design and includes the following information for each phase:
a). A description of the data design phase,
b). The inputs of the phase,
c). The outputs of the phase,
d). A key issue addressed in the phase
e). A challenge that you can anticipate would occur in the phase.

Please prepare the response you will bring to the meeting.

Solution:
Description Issue Input Output Challenge
a. Conceptual Design - Capturing all - Functional - ER diagram
Create model that captures major data specs
entities, relationships among entities, - Capturing - General
and attributes of entities required for relationships understanding
a particular system. - Data integrity of problem
b. Logical Design - Providing - ER diagram - Relational -
Transform the major entity/attribute location for all database
/relationship requirements into high data schema
level specification for database - Data integrity

b2 Improving Logical Design - Minimizing - Relational - Relational -

Improve the high-level database redundancy database database
specification. - Minimizing schema schema
ambiguity
c. Physical Design - Performance - Relational - Technical -
Transform the high-level - Data integrity database specifications
specifications for database into schema for construction
detailed specifications for how to - Meaning of of the database
construct actual database in a data
specific relational database software.
 QUESTION 2: CREATING AN ENTITY-RELATIONSHP DIAGRAM .

UPS prides itself on having up-to-date information on the processing and current location of each
shipped item. To do this, UPS relies on a company-wide information system. Shipped items are
the heart of the UPS product tracking information system. Shipped items can be characterized
by item number (unique), weight, dimensions, insurance amount, destination, and final delivery
date. Shipped items are received into the UPS system at a single retail center. Retail centers are
characterized by their type, uniqueID, and address. Shipped items make their way to their
destination via one or more standard UPS transportation events (i.e., flights, truck deliveries).
These transportation events are characterized by a unique scheduleNumber, a type (e.g, flight,
truck), and a deliveryRoute.

Please create an Entity Relationship diagram that captures this information about the UPS
system. Be certain to indicate identifiers and cardinality constraints.

Solutions:

ItemNumber

Weight

Type
Dimension

Transportation Event
Shipped Items Shipped Via
InsuranceAmt ScheduleNumber

DeliveryRout
Destination

Received
FinalDellivery
From
Date

Type

Retail Center
UniqueID

Address
1.

 Fact finding techniques.

 List down facts in the form sentences given in case study.

 Give a proper reason as well for each fact.

2. Please convert the ER diagram into a relational database schema. Be certain to indicate primary keys
and referential integrity (Foreign Key) constraints
QUESTION 3: CREATING A RELATIONAL DATABASE SCHEMA.

Production tracking is important in many manufacturing environments (e.g., the pharmaceuticals

industry, children’s toys, etc.). The following ER diagram captures important information in the
tracking of production. Specifically, the ER diagram captures relationships between production
lots (or batches), individual production units, and raw materials.

LotNumber

CreateDate serial-# exactWeight

Cost-Of-Materials

ProductType
Lot Production
Includes
Units

qualityTest? ProductDesc

Created From Units

type

Raw Materials material-ID

UnitCost

a. Please convert the ER diagram into a relational database schema. Be certain to indicate
primary keys and referential integrity (Foreign Key) constraints.
 Solutions:
Production Units
Serial# ExactWeight ProductType ProductDesc QualityTest? LotNumber

Lot
LotNumber CreateDate CostOfMaterials

Raw Materials Usage

LotNumber MaterialID Units

Raw Materials
MaterialID Type UnitCost
Note: The following questions refer back to the above ER diagram for problem 3.

b. Please identify an attribute in the above ER diagram that might represent a composite
attribute, and explain why/how it might represent a composite attribute

Answer: Many of the attributes could actually represent composite attributes:

- Weight might be stored as pounds and ounces.
- Product Description might have several components
- Product type might have several components
- Create date might be stored as both date and time.

c. Please identify an attribute in the ER diagram that could represent a derived attribute and
explain why/how it might represent a derived attribute.

Answer: Cost-of-Materials (associated with the Lot entity) most likely represents a derived
attribute. The cost of materials could be computed based on the materials unit cost (from the
raw materials entity) and the number of units required for a lot (on the relationship).

d. The ER diagram/relational database schema contains several instances of data redundancy.

Please identify one instance where a data redundancy issue exists.
Answer: Examples include:
- Both production description and product type are being stored for each and every
production unit. The production description could be stored elsewhere.
- Production type and production description are being stored on each and every
production unit, when these parameters are most likely the same for the entire lot.
They could be stored on the Lot entity.

e. The current ER diagram has the following relationship, “raw materials are used in 0 to many
lots.” Please explain, in the context of the manufacturing environment, how the meaning
changed if the minimal cardinality is changed to “1” (i.e., the relationship becomes "raw
materials are used in 1 to many lots.”).
f.
Answer: The current representation of “raw materials are used in 0 to many lots” implies
that raw materials can be in the system without being designated for a specific lot. If the
minimum cardinality was changed to 1, this would imply that all raw materials must be
designated as related to at least one lot