Fully Attributed Data Model

MILESTONE 2 – STUDYING THE CURRENT SYSTEM

Activity 1 – Problems, Opportunities, Objectives, and Constraints
Matrix.

PROBLEMS, OPPORTUNITIES, OBJECTIVES AND CONSTRAINTS MATRIX

Project: Pinnacle Publishing Sales and Book Tracking System Project
Manager: Cindy Natale

Created by: Cindy Natale Last Updated by: Cindy Natale

Date Created: 8/15/2000 Date Last Updated: 8/15/2001



CAUSE AND EFFECT ANALYSIS SYSTEM IMPROVEMENT OBJECTIVES

Problem or Opportunity Causes and Effects System Objective System
Constraint

No way to check for duplicates among the sales leads databases of
various salespersons.

Caused by using independent databases.

Leads to unnecessary duplicate sales calls and contention among the
sales staff.

Also leads to limited ability to analyze salesperson performance.

No way to automatically pass information on sold contracts between sales
and production areas. Create integrated, shared database of all sales
leads and sold authors.

Develop sales analysis report from call logs and sales records.

New system needs remote access.



Manual Book-In-Process system does not allow easy access of book status.
Plastic bin system requires that employees physically search for files
of books in process.

Author status inquiries consume vast amounts of staff time.

No management control over the process.

Provide system and process to track books electronically that are
integrated with sales system.

Process will be controlled by a “book shepherd� responsible for
keeping record up to date throughout the process.

Pass status data to a self-service web site to be written by in-house
staff to handle author inquiries. Sales and Production are on two
different LANs. Solution must provide adequate connectivity.

Must maintain high level of security from Internet.

Present royalty calculation system is overly cumbersome. Excessive
effort is required to calculate royalties and generate checks. Provide
automated system to import data from all sources, calculate royalties,
and print checks. System must integrate with external data from
e-commerce partners.





The proposed system creates an opportunity for creating valuable
management reports from every phase of the business. This will lead to
better management and increased productivity. This will be part of Phase
II. Although part of Phase II, initial system should be designed so
that this capability can be easily added.

MILESTONE 3 – DATA MODELING

Synopsis

T

he requirements analysis phase answers the question, “What does the
user need and want from a new system?� The requirements analysis phase
is critical to the success of any new information system. In this
milestone we need to identify what information systems requirements need
to be defined from the system users’ perspectives and draw graphical,
logical, models to document the data requirements for a new and improved
system.

Data modeling is a technique for organizing and documenting a system’s
data. Data modeling is sometimes called database modeling because a data
model is usually implemented as a database. Data is viewed as a resource
to be shared by as many processes as possible. As a result, data must be
organized in a way that is flexible and adaptable to unanticipated
business requirements – and that is the purpose of data modeling.

In this milestone you will first discover those entities in the system
that are or might be described by data. With each entity we identify, we
will define it in respect to the business. Then, we will construct a
Fully- Attributed Data Model.

Exhibit 3.1

The following is a copy of the transcript of an interview between Mr.
John Bridgham of Pinnacle Publishing and Cindy Natale, the systems
analyst working on the Sales and Book Tracking System. The goal of this
interview was to discuss sample forms used for tracking books through
the production system.

Scene: The office of John Bridgham, VP Operations for Pinnacle
Publishing.

John: Good morning, Cindy. It is good to see you again. How is the
system coming?

Cindy: It is going well. From your perspective, you might not notice
much progress yet. But we are laying a firm foundation upon which we can
build a solid system. Thank you for taking time to see me.

John: No problem. What can I do for you today?

Cindy: I just have a few questions on the forms that Brett sent me. I
want to make sure I understand everything on them. I was hoping Mary
could be here, too. I understand she is out of town.

John: She is at a trade show somewhere – Seattle, I think.

Cindy: I’m e-mailing her. That will probably answer my questions. As
for the forms that relate to your area, let’s start with the
Production Control Form [Exhibit 3.6]. Is one of those filled out for
every book?

John: Yes. This is the master form that guides the book through the
production process. It is the first sheet in the book’s file folder.

Cindy: Who fills it out?

John: It is initially filled out by the technician who handles book
check-ins. Currently that is Debbie Scoloti. All manuscripts that come
in the mail are routed to her. She writes the top information on the
form and fills in the first task – File Begun – with her initials
and the date.

Cindy: Is that always the first task?

John: Yes, but from then on the tasks could vary. If the manuscript
comes in a typewritten from, there is scanning. If it comes in
electronic form, then we do a file conversion.

Cindy: I see that some tasks have a Finish date and some do not.

John: Some tasks take only minutes to complete. The Begin and Finish
dates are assumed to be the same.

Cindy: I see. What exactly is the Num Pages. Is that the number of pages
in the manuscript?

John: Yes. It is there just to give everyone an idea of the size of the
book. Of course as it goes through various editing stages, that number
can change.

Cindy: Do you ever get more than one book from a single author?

John: Oh, yes. Most authors just have one book, but some have a dozen or
more books we publish.

Cindy: Okay. I think that’s all I need to ask you about that form. The
other form that falls under your area is the Royalty Compensation
[Exhibit 3.4]. First of all, what am I looking at here? Is this form
sent out to authors, or is it used as an internal document?

John: This is what is sent to the author. Internally we use a
spreadsheet right now. I’m hoping your system will calculate all this
for us.

Cindy: I see there are various Sources. Do all books have the same three
sources of sales?

John: They all have the same potential sources. There are six or eight
different sources. But I don’t include on the statement any sources
without sales for that book in the quarter.

Cindy: Where does this information come from?

John: Each of the sales sources send us a report. Most of them send it
in the form of a text file we can read in electronically. Some send a
printed report.

Cindy: What do those files and reports look like?

John: They have varying formats. But they each report each book with
sales in the quarter and the dollars of those sales. Right now we
hand-enter that information from the reports or file printouts into our
spreadsheet.

Cindy: Will the Pinnacle sales be handled the same way? So far no one
has said anything about the system needed to track individual sales from
your own web site.

John: And it should not. Our e-commerce site is a separate system. It
will export a text file each quarter that you can use as input.

Cindy: Is the 15% commission standard for every book?

John: It is right now. But Jake is talking about having various levels
for various types and sizes of books.

Cindy: Okay! That answers the questions I have for you. Thank you again
for your help.

Exhibit 3.2

E-Mail from Mary O’Neal

From: Mary O’Neal

To: Cindy Natale

CC:

Subject: Sales System

Cindy,

Sorry I could not meet with you in person. But your questions seemed
clear, and I believe I can answer them in this e-mail.

1) The Entry Form is THE entry screen from the salespeople’s
databases. That is all the information we track.

2) Yes, when we combine the databases, we MUST keep track of which
salesperson gets each lead.

3) The Call Log is a daily log filled out by hand by each salesperson.
As I said the other day, we want to automate that into the system, so we
can view the history of calls made to each lead. The notes on the sample
Call Log I gave you are representative of the length of the notes they
make.

If you have any other questions, e-mail me back.

MO

Exhibit 3.3

Salesperson Lead Database Entry Form

Exhibit 3.4

Royalty Compensation Form

Exhibit 3.5

Salesperson Call Log

Exhibit 3.6

Production Control Form



Exhibit 3.7

Fully Attributed Data Model Example

Prepared by Kevin C. Dittman for

Systems Analysis & Design Methods 4ed

by J. L. Whitten & L. D. Bentley Copyright Irwin/McGraw-Hill 1998

Entity/Definition Matrix

ENTITY BUSINESS DEFINITION

TECHNICIAN Person who had been assigned with an aspect or aspects of
company responsibility e.g. handling books checked in etc

Attributes: T_ID, T_First_Name, T_Last_Name, T_Address, T_Phone,
T_Skill(s)

BOOK Details kept about each book e.g. Title, Sub Title, Author, Number
of pages, Description

Attributes: B_ID, A_ID, B_Title, B_Sub_Title, B_Author,
B_Number_Of_Pages, B_Description

AUTHOR Details recorded about each author

Attributes: A_ID, A_First_Name, A_Last_Name, A_Address, A_Phone

PRODUCTION Activities each book went through during its production
process, while the book is being created

Attributes: P_ID, B_ID, T_ID, P_Task, P_Begin_Date, P_Finish_Date

PUBLISHER Details of each publisher including self e.g. Pinnacle
Publishing, Webooks etc

Attributes: Pub_ID, Pub_Name, Pub_Address, Pub_Phone

PUBLISHER_SALES Details recorded of all sales submitted by each
publisher on a quarterly basis

Attributes: Pub_S_ID, B_ID, Pub_S_NetSales, Pub_S_Date,
Pub_S_CommissionLevel, Pub_S_TypeOfBooks, Pub_S_SizeOfBooks

SALESPERSON Person responsible for handling and chasing up each new
sales lead

Attributes: S_ID, S_First_Name, S_Last_Name, S_Address, S_Phone

LEAD Potential sales sources

Attributes: L_ID, L_First_Name, L_Last_Name, L_Address, L_Phone,
L_Description, L_Sold_Status

SALES_LEAD Each salesperson is assigned a limited number of leads, to
avoid unnecessary duplicate of sales calls and for a history to be kept
of calls to each lead for future references

Attributes: Sales_L_ID, L_ID, Sales_L_Date, Sales_L_Notes