System: Customer/Product Tracking System

BTSDate: Jan.23-27, 2006

DATA DICTIONARY

System: Customer/Product Tracking System

Content

The data dictionary for the Customer/Product Tracking System consists of the following detailed items:

• Data Flows
• With descriptions of data structures and data elements
• Data Stores
• With descriptions of data structures and data elements
• Processes
• External Entities.

The descriptions and documentations will assist with preventing redundancy of data elements and will help develop a better understanding of the flow of data as it is described in the data-flow diagrams. The data dictionary should also be used to assist with designing and creating input/output program modules and to further comprehend the content of data that is to be stored in the Customer/Product Tracking System. Modifications to the labels, alias, descriptions, or element attributes should be updated in this repository to help maintain consistency.

DATA FLOW DESCRIPTION

Data Flow Name: / Customer Information
Alias / Client Information
Description / Information about the customer for sales order processing
Origin / Customer external entity
Destination / Process Customer Information process
Record / Customer
Volume and Frequency / Approximately 30 per day
Comments

DATA FLOW DESCRIPTION

Data Flow Name: / Current Order Information
Alias / Order Status
Description / Information about customer sales order
Origin / Process Inventory Information
Destination / Employee External Entity
Record / Order
Volume and Frequency / Approximately 30 per day
Comments

DATA FLOW DESCRIPTION

Data Flow Name: / Employee Information
Alias / Employee
Description / Relevant Personal Information about employee
Origin / Employee External Entity
Destination / Process Employee Information
Record / Employee
Volume and Frequency / Approximately 2 per year
Comments

DATA FLOW DESCRIPTION

Data Flow Name: / Product Information
Alias / Inventory, Product Item
Description / Add new product information
Origin / Employee External Entity
Destination / Process Inventory Information
Record / Inventory
Volume and Frequency / Approximately 2-5 per quarter
Comments

DATA STORE DESCRIPTION

Label Name: / Customer Management
Alias / Clients
Characteristics / Entry Type:Computer Database
Number of Records: Max-30,000
Percent Growth per Year: 4.5%
Description / Information record of each customer
Composition / Attributes:
customer_id
last_name
first_name
company_name
address
city
state_province
postal
country
primary_phone
secondary_phone
fax
email_address
date_added
comments
Comments / Records are copied to an archive file

DATA STORE DESCRIPTION

Label Name: / Order Management
Alias
Characteristics / Entry Type:Computer Database
Number of Records: Max-45,000
Percent Growth per Year: 4.5%
Description / Record customer order information
Composition / Attributes:
order_id
employee_id
customer_id
order_date
paid
Comments / Records are copied to archive file.

DATA STORE DESCRIPTION

Label Name: / Employee Management
Alias
Characteristics / Entry Type:Computer Database
Number of Records: Max-8,000
Percent Growth per Year: .5%
Definition / Record of each employee
Composition / Attributes:
employee_id
last_name
first_name
title
pay_style
pay_rate
start_date
end_date
Comments / Records are copied to archive file.

DATA STORE DESCRIPTION

Label Name: / Inventory Management
Alias / Products
Definition / Record of all products in inventory
Composition / Attributes:
inventory_id
product_name
product_brand
cost
warranty_info
warranty_duration
warranty_end_date
product_description
date_added
date_ended
Comments / Records are copied to archive file.

EXTERNAL ENTITY DESCRIPTION

Entity Name: / Customer
Alias / Client
Purpose / To prompt the employee to interact with the system
Input Data Flows / Validated Customer Information
Returned Order Information
Returned Product Information
Warranty Information
Output Data Flows / Customer Information
Requested Order Information
Requested Product Information
Requested Warranty Information
Entity Description / The system will process the customer by obtaining information and fulfilling their request about our products, order status, and warranty information.
Notes: / Employees can be customers.

EXTERNAL ENTITY DESCRIPTION

Entity Name: / Employees
Alias
Purpose / Access the system to handle the customer, inventory, and order information.
Input Data Flows / Login Result
Returned Employee Information
Provided Customer Information
Current Order Information
Inventory Information
Output Data Flows / Login Attempt
Employee Information
Processed Customer Information
Completed Customer Orders
Product Information
Entity Description / The employee will interact with the system by entering customer information and initiating the order process. The employee should also provide product information at the customer’s request. The employee will also maintain the inventory information for information retrieval and for order processing.
Notes: / Primary Departments to use the system: Inventory, Sales, Operation Management
Primary employees to use the system: data entry operators, Sales staff, Managers, inventory specialists

RECORD DESCRIPTION

Name: / Customer
Alias / None
Definition / Store the personal and contact information of the customer
Data Element
Content / Attributes:
customer_id
last_name
first_name
company_name
address
city
state_province
postal
country
primary_phone
secondary_phone
fax
email_address
date_added
comments

RECORD DESCRIPTION

Name: / Order
Alias
Definition / Customer order information in regards to assigned employee for order, customer name, total cost, and payment status (Y/N?)
Data Element
Content / Attributes:
order_id
employee_id
customer_id
order_date
paid

RECORD DESCRIPTION

Name: / Employee
Alias
Definition / Employee personnel and contact information
Data Element
Content / Attributes:
employee_id
last_name
first_name
title
pay_style
pay_rate
start_date
end_date

RECORD DESCRIPTION

Name: / Product
Alias / Inventory item
Definition / Add and identify product in inventory. Includes warranty information.
Data Element
Content / Attributes:
inventory_id
product_name
product_brand
cost
warranty_info
warranty_duration
warranty_end_date
product_description
date_added
date_ended

ELEMENT DESCRIPTION

Name / customer_id
Label / ID
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / int (8)
Null / No
Comment / Primary Key (PK)
Name / last_name
Label / Last Name
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (100)
Null / No
Comment

ELEMENT DESCRIPTION

Name / first_name
Label / First name
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (100)
Null / No
Comment
Name / company_name
Label / Company Name
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (100)
Null / Yes
Comment / If just an individual customer, this will not have to be entered
Name / address
Label / Address
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (255)
Null / Yes
Comment

ELEMENT DESCRIPTION

Name / city
Label / City
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (100)
Null / Yes
Comment
Name / state_province
Label / State/Province
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (100)
Null / Yes
Comment
Name / postal
Label / Postal (Zip)
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (20)
Null / Yes
Comment

ELEMENT DESCRIPTION

Name / country
Label / Country
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / enum (‘US’)
Null / No
Comment
Name / primary_phone
Label / Phone (Primary)
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (20)
Null / No
Comment
Name / secondary_phone
Label / Phone (Secondary)
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (20)
Null / Yes
Comment

ELEMENT DESCRIPTION

Name / fax
Label / Fax
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (20)
Null / Yes
Comment
Name / email_address
Label / Email
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / varchar (255)
Null / Yes
Comment
Name / date_added
Label / Date Added
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / date
Null / No
Comment

ELEMENT DESCRIPTION

Name / comments
Label / Comments
Data Structure / Customer
Data Flow / Customer Information
Data Store / Customers Table
Type (Length) / text
Null / Yes
Comment
Name / order_id
Label / ID
Data Structure / Order
Data Flow / Current Order Information
Data Store / Orders Table
Type (Length) / int (8)
Null / No
Comment / Primary Key (PK)
Name / employee_id
Label / Employee
Data Structure / Order
Data Flow / Current Order Information
Data Store / Orders Table
Type (Length) / int (8)
Null / No
Comment / Foreign Key (FK1) from Employees Table

ELEMENT DESCRIPTION

Name / customer_id
Label / Customer
Data Structure / Order
Data Flow / Current Order Information
Data Store / Orders Table
Type (Length) / int (8)
Null / No
Comment / Foreign Key (FK2) from Customers Table
Name / order_date
Label / Order Date
Data Structure / Order
Data Flow / Current Order Information
Data Store / Orders Table
Type (Length) / date
Null / No
Comment
Name / paid
Label / Paid
Data Structure / Order
Data Flow / Current Order Information
Data Store / Orders Table
Type (Length) / enum (‘N’, ‘Y’)
Null / No
Comment

ELEMENT DESCRIPTION

Name / employee_id
Label / ID
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / int (8)
Null / No
Comment
Name / last_name
Label / Last Name
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / varchar(100)
Null / No
Comment
Name / first_name
Label / First Name
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / varchar (100)
Null / No
Comment

ELEMENT DESCRIPTION

Name / title
Label / Title
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / varchar (100)
Null / No
Comment
Name / pay_style
Label / Pay Style
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / enum (‘H’, ‘S’, ‘C’)
Null / No
Comment
Name / pay_rate
Label / Pay Rate
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / float (8, 2)
Null / No
Comment

ELEMENT DESCRIPTION

Name / start_date
Label / Start Date
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / date
Null / No
Comment
Name / end_date
Label / End Date
Data Structure / Employee
Data Flow / Employee Information
Data Store / Employees Table
Type (Length) / date
Null / No
Comment

ELEMENT DESCRIPTION

Name / inventory_id
Label / ID
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / int(8)
Null / No
Comment
Name / product_name
Label / Product Name
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / varchar(100)
Null / No
Comment
Name / product_brand
Label / Product Brand
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / varchar (100)
Null / No
Comment

ELEMENT DESCRIPTION

Name / cost
Label / Cost
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / float(8,2)
Null / No
Comment
Name / warranty_info
Label / Warranty Info.
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / varchar(2000)
Null / Yes
Comment
Name / warranty_duration
Label / Warranty Duration
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / double(3,2)
Null / No
Comment / 1 = 1year, .5 = 6 months

ELEMENT DESCRIPTION

Name / warranty_end_date
Label / Warranty Expiration
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / date
Null / Yes
Comment
Name / product_descr
Label / Product Description
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / varchar (2000)
Null / Yes
Comment

ELEMENT DESCRIPTION

Name / date_added
Label / Date added
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / date
Null / No
Comment
Name / date_ended
Label / Date Ended
Data Structure / Product
Data Flow / Product Information
Data Store / Inventory Table
Type (Length) / date
Null / Yes
Comment

PROCESS DESCRIPTION

Process Name: / Process Customer Information
ID / 1
Purpose / Obtain customer information for order processing
Input Data Flows / Customer Information
Returned Customer Information
Processed Customer Information
Output Data Flows / Customer Information
Validated Customer Information
Provided Customer Information
Process Description / The entry of new or updated customer information is gathered by employee and is stored in the Customer data store. The employee will search the Customer data store for repeat customers to begin a quick order process.
Notes: / Employees can be customers. Customer information can be considered an individual, or organization using the company name.

PROCESS DESCRIPTION

Process Name: / Process Customer Orders
ID / 2
Purpose / Initiate customer’s orders
Input Data Flows / Customer Information
Requested Order Information
Returned Product Information
Completed Customer Orders
Returned Product Information
Returned Order Information
Returned Order/Product Reference Information
Output Data Flows / Returned Order Information
Returned Product Information
Current Order Information
Requested Order Information
Order Information
Order/Product Reference Information
Process Description / The employee will start the order process by obtaining customer information and will obtain product information from Inventory to be included on the order. The employee should also provide product information at the customer’s request. The employee should complete the order.
Notes: / This process should have access to up-to-date customer and inventory information.

PROCESS DESCRIPTION

Process Name: / Process Inventory Information
ID / 3
Purpose / Enter product information and to maintain the inventory for information retrieval and order processing.
Input Data Flows / Product Information
Requested Product Information
Returned Inventory Information
Requested Warranty Information
Output Data Flows / Inventory Information
Returned Product Information
Request Inventory Information
Warranty Information
Process Description / The entry of new or updated product information is done by the employee and is stored in the Inventory data store. The employee will search the Inventory data store for product information and available inventory for order processing. The Inventory data store will be updated as orders are completed.
Notes: / Inventory data store will be a reference to the Process Customer Orders.

PROCESS DESCRIPTION

Process Name: / Process Employee Information
ID / 4
Purpose / Enter employee information for system access
Input Data Flows / Login Attempt
Employee Information
Output Data Flows / Login Result
Returned Employee Information
Provided Employee Information
Process Description / The entry of new or updated employee information is added to the Employee data store. The employee will access the system via a valid log-in.
Notes: / Terminated employees should be denied access to the system, but not removed as customers.