capstone propo... 2-24-04)

gasownerData Management

Jan 31, 2013 (4 years and 7 months ago)

158 views

Customized Database System

Cody Bauers

C. David Shaffer, mentor



Abstract


The goal of my project is to provide a solution to the data management needs of emerging
broadband wireless i
nternet service providers (WISP
s). Efficient management of large scale

wireless
networks can be a difficult task without the ability to store, retrieve, and perhaps most importantly, analyze
all company data pertaining to employees, customers, manufacturers, network status, and other pertinent
information. Although there ar
e a variety of approaches that I could take to accomplish my goal, I will be
using Microsoft Access to construct the database, as well as the front
-
end graphical user interface (GUI) of
the system. The majority of code for my project will be written in Vi
sual Basic, an event
-
driven language,
which
will allow for

various forms and controls of the GUI
to connect
to the database itself. Appropriate
software enginee
ring techniques will be applied throughout all phases of the project, as I will first construct

appropriate data models before entering the developmental phase of the project. A substantial amount of
communication will take
place between myself and several

people in the field of broadband wireless
internet
,
most of
who
m

work for a company that I ha
ve experience with as an intern, to ensure that the
completed system will be useful for
nearly all
WISP
s. I expect my finished product to be well designed,
efficient, and field
-
worthy, and I envision it being
a cost
-
effective
solution to the data managing

needs of
any WISP
.








2


Wireless Technology


Wireless technology has impacted the lives of many people across the country and around the
world. Beepers, cell phones, GPS systems, electric fences, and keyless entry systems are just a few
examples of dev
ices utilizing wireless technology that make our daily lives easier. Wireless technology has
long been widely used by many different government agencies. Missile guidance systems rely on accuracy
and precision, and can take out targets many miles away by

communicating with in
-
flight missiles that are
en
-
route to their targets via wireless link. NASA uses its deep space network (DSN) to communicate with
Voyager 1 at

the edges of the solar system [8]
.
While the combination of frequency and amplitude of ra
dio
waves that carry information between wireless devices may vary, wireless technology is based on line
-
of
-
sight. There is always a noise level that is associated with every transmission. Noise level is a term used
by many wireless techs that translates

to
essentially mean
interference level. Although wireless networks
are already being used in homes and offices, wireless technology provides the best choice for many
consumers for high speed internet access.

Many broadband WISP
s have begun to emerge acro
ss the country.

These companies compete for
customers with other companies who provide high
-
speed internet via cable
modem
or
direct satellite linkup
(
DSL
)
.
Anyone
who relishes the ability to retrieve information at the click of a mouse

would rather have

any type of high speed internet access by any means verses internet access via 56k modem.
All broadband
ISP’s have
advantages and disadvantages
. Cable is fast and
reliable, but for various reasons, is not offered
ev
erywhere. DSL is

available
in
many ar
eas that cable ca
nnot reach, but, on average, is

less dependable

than cable
.
Wireless internet shares a similar advantage over cable, however, both DSL and wireless both
share similar deficiencies as well, in that they may work very well in some areas, bu
t not in others.
In some
cases, a person may be able to get reliable service, but his neighbor may not. However, wireless ISPs
seem
to
have the advantage over DSL in that they can improve their potential customer base sim
ply by putting up
another antenna
,
improving

their coverage in a certain area.

There are a considerable amount of similarities between broadband wireless internet networks
(WIN) and cellular networks. A WIN, much like a cellular network, consists of a series of tower sites that

3

provide s
ervice to a given area. Once a request to transmit or receive information is made, the information
travels from a wireless communication device, which is a cell phone in the case of a cellular network, to the
appropriate tower and then routed to another t
ower or hard line to reach its destination.

Data is typically sent in groups referred to as packets. In the case of a WIN, groupings of
information are called data packets rather than voice packets as with a cellular network. Perhaps the
biggest differen
ce between the two networks is that a mobile phone can transmit and receive information
from almost anywhere, whereas a wireless communication device for a WIN must stay in a fixed location.
As a result, wireless internet is often referred to as fixed, or

point
-
to
-
point wireless communication. A
WIN also consists of an access point, or location
ultimately connecting the

entire network to the internet.
Because of this, the data paths
of information traveling on the networks
are typically much different as

all
information transmitted from a wireless communication device
in a WIN
ultimately en
ds up going to the
access point, and all information received ultimately comes from the same access point. Although this may
seem somewhat limiting in comparison to a
cellular network, it is rather useful for supplying broadband
internet to homes and offices. Perhaps one day, broadband internet will be available in any location for use
on a laptop or other computing device, much in the same way
that
a cell phone is pro
vided service

almost
anywhere. H
owever
,

that day is not yet here.


Previous Work/Research


The

growing trend in the business world
seems to be

to
manage and
store information using a
database management system (DBMS).

There are two approaches to building

a DBMS, the object oriented
approac
h and the relational approach, each with different advantages and disadvantages

[1]
.

The most
important thing that o
ne must consider
is
the purpose of building the system. Depending upon the
situation, one approa
ch may

make design and implementation of a DBMS easier than the other.
The object
oriented approach seems to allow more accurate data models of the real world.

The inherent relationship
between data and applications allow for schema to be built more easily in
an OODBMS. Also, this type of
database supports long
-
duration transactions, where the relational model tends to maintain database
consistency. Perhaps the best advantage of an OODBMS is shared by the object oriented languages

4

themselves that are used to
build the systems, encapsulation. OODBMS allow for new data types to be
built from existing ones. This type of approach is also useful for advanced database applications. And, the
final
ly,

the OODBMS has
displayed
better performance compared with the re
lational DBMS (RDBMS).

The previous statement is based upon a performance test done in 1989 and 1990. OODBMSs such as
GemStone, ObjectStore, Objectivity/DB, and Ontos were pitted against RDBMSs Sybase and INGRES

[1]
.

Unfortunately, the OODBMS
also
has co
nsiderable deficiencies
. The OODBMS lacks a universal
data model, and a general lack of standards, including no standard query language. Also, query
optimization often compromises encapsulation. Also, in general, the OODBMS is more complex.
Complexity
leads to products that are more expensive and also more difficult to use

and to build
. The
typical OOBDMS do
es not provide a view mechanism
, and often do
es

not provide sufficient security
mechanisms. All mentioned disadvantages contribute to the greatest

disadvantage of the OODBMS. The
use of OODBMSs is relatively limited because of
a rather large user preference for the RDBMS
[1]
.

Using
an object oriented approach for my project would certainly be i
nteresting and challenging, however the
relational app
roach seems to be the better option

for my purposes
.

Something

that one must

consider with any database developer suite is the costs associated with it
.
Not only is the initial cost important, but one must also consider the maintenance costs invo
lved in
m
aintaining the

system. Because every database seems to deteriorate and become unstable over time
through e
very day use, it is important to have a database administrator on hand

to keep the database
in
working condition
.

Some databas
e building tools and so
ftware are

free, and
are certainly

consideration
s

for my
project. Interbase Firebird and Supra are among a few examples

[6]
. As one may infer, these packages are
relatively brittle, and
can be difficult to use. These tools would perhaps be more

useful i
f one were to build
a simple, single user database system. However, if I were to use free tools for my project, such restrictions
associated with them will make it very difficult for me to accomplish my goal in developing a stable

and
flexible
system.

Al
so consider that it takes resources to maintain

a
ny DBMS.


I
t

would cost a business
about $500 a year to pay an employee $10 an hour for about a year to maintain the system, assuming that it
requires an hour of maintenance a week on average.


5

The next optio
n to consider is

developer tools that have
relatively
small

costs associated with
them. Maintenance costs would be larger for systems created using such tools

in comparison to free
database building tools
, especially if they were used
to build a

multi
-
use
r system
.
Microsoft Access,
FileMaker Pro and Sybase are
among those products that enable a developer to build a
simple, yet
relat
ively stable, multi
-
user system

[5, 7]
. These products cost under $400 up front, and provide a
n
effective solution to managi
ng data
.
A multi user DBMS would possibly

require at least 8 hours of
maintenance a week on average, a
nd a person responsible for maintaining the system

would probably earn
at least $20 an hour, so a company would spend approximately $8000 a year
to maint
ain the system.

And finally, one must

consider
expensive developer suites

such as Oracle, MS SQL Server,
and
DB2 among others

[5]
. Because of budget restrictions, I will not be using such tools to build my DBMS.
While initial costs are usually upwards of

$10,000 dollars, this fee is rather small compared to the cost one
would spend to maintain a database built using these products. Not only would one have to employ a
database specialist, earning
close to
a six figure salary
, depending upon the size and c
omplexity of the
system
, to maintain the database full
-
tim
e, there may be contracts that must

be paid
to the manufacturers of
a product for its licensed usage. A business may

be obliged to pay upwards of $100,000 dollars
to maintain

their database system
for one yea
r. Although such a price seems rather daunting, the payoff for
the use of
such a system is very high. These product manufacturers pride themselves on quality and usability

[3, 4]
.
For a large corporation enterprise, there simply is no
other r
easonable solution to managing large amounts
of data in a large scale multi
-
user corporate environment.

Some
developer suites provide
tools to build f
ront
-
end applications to. Otherwise, a developer
must acquire GUI building tools separately
[6, 7]
.
Beca
use MS SQL Server and MS Access integrate
seamlessly, MS SQL Server relies on tools provide
d with Access to build the GUI, whereas various Oracle
developer suites have a specialized set of front
-
end developer tools called J
-
Developer. O
pen database
connec
tivity
(
ODBC
)

is a popular standard for accessing information from various databases from different
vendors.

Because the relational model has a standard query language (SQL), there are many front
-
end
developer tools that are O
DBC
. Applications written us
ing the ODBC drivers are guaranteed to work with
various databases like PostgreSQL, Oracle, Sybase, Informix
,

and others.

Oracle has a separate set of front
-
end developer tools called J
-
Developer. MS SQL Server relies on MS Access to supply the tool
s for

6

app
lication development.
The goal of the developer is to find the best possible combination of tools that
will allow him to produce a system that will satisfy the needs of the customer.






Problem Description and Background


All companies and organizat
ions need some method of
managing data
.
Companies in the fixed
wireless industry are no different. However, the needs of WISPs are very specific as in the methods that
must be used to monitor and manage certain information. There are four key areas of o
perations that need
some type of system to manage information. A typical WISP needs an inventory tracking system, a
customer profiling and tracking system, a personnel and employee tracking system, as any other business
for payroll purposes, and finally,
a system to track important network information. The problem is finding
a specific solution
, software or otherwise,

that is inexpensive and complete enough to empower a WISP to
better manage their business
.

WISP inventory
, such as radio equipment, is
typi
cally quite expensive
. This certainly makes
tracking inventory especially important to WISPs
[9].
The task of keeping track of inventory can become
very complex rather quickly, as most WISPs are usually preoccupied with achieving a firm customer base
ini
tially.
Also, most WISP business operations usually involve frequent transfer and deployment of
inventory. Some WISPs keep track of inventory with spreadsheets, word processors or pencil and paper.
Although this can work, it is very labor intensive, and

it
provides a larger probability of human error [10].
Software can be a very useful tool in keeping track of inventory.
Because WISP
s have only recently begun
to emerge, specialized DBMS
s for such businesses are not readily available.

There are some co
mmercial
packages available, and they can provide a viable solution, as
some commercial software offer

complete
inventory management [10].
Unfortunately, these packages can be quite expensive and not tailored to the
needs of a WISP.

Some WISPs who develo
p their own inventory tracking systems try to resell their
systems to other similar companies. However, each WISP is different, from its range of operations to
infrastructure, to customer base. The Final Mile claims to have extensive experience as a func
tioning
WISP, and they offer their consulting services to other WISPs to help them set up their own system of
inventory management. The Final Mile also provide consulting services in other areas.


7

Customer satisfaction is certainly important with any servi
ce oriented business. Fine Point
Technologies
concentrate on customer satisfaction,

offering the ability of WISPs and other types of ISPs to
empower their customers. The goal is to simplify and enhance the user’s internet experience. Fine Point
Technolo
gies offer a specific packaged solution for this type of endeavor to WISPs called cyberTRUCK.
Although this type of thing may be useful, it is not an essential tool for any ISP to have, and is not
especially useful for keeping track of customer informatio
n. Information that should be stored within a
DBMS about an ISPs customers would include their installation setup, their signal strength, IP address, Wi
-
Fi card, and other pertinent information that can be obtained without a package such as cyberTRUCK. I
n
short, such technologies are nice bonuses, but not essential tools for WISPs to have.

The cyberTRUCK
suite does provide some capability for subscriber management, and this component of the software suite
could prove to be useful to WISPs. The problem ag
ain presented is the inability to process information
across different domains.

A tool that has become widely used by many organizations to track network information is a
software solution called Nagios. Tivoli is Nagios’ competitor, but does not seem to
have as wide of a user
base, with lesser user satisfaction

[11]
. Nagios is an open source host, service and network monitoring
program. This software offers a useful solution to network information management. A user can receive
automated notifications

when there is a problem, as well as inspect many parts and components of the
network. Nagios comes with capabilities to interface with an external database through event handlers.
Given a certain condition, Nagios will export valuable information to the

external source. Nagios is written
in C, and is open source. It should be somewhat difficult, but very possible to interface an Access DBMS
with Nagios, which will provide the ability for network tracking information to cross domains.

The Chief Technica
l Officer (CTO) of YYireless1
claims

that “there is no 100% solution
available.” A complete solution would be one that stores and retrieves information, with the ability to
integrate information from different aspects of business operations.
One company
that claims to offer a
complete solution to WISPs is Wireless Network Operations Center (WNOC). The goal of the company is
to provide a total solution to allow WISP personnel to spend less time in the office and more time getting
new customers. A subscri
ber is instructed to place a WNOC box at their internet gateway, and WNOC will
remotely configure it and manage it. They offer several types of services that can be packaged according to

8

the needs of a specific WISP.
While some of these services seem to
be useful, they involve surrendering
control of some portion of operations to WNOC, services that most successful WISPs have already been
doing for themselves in order to become successful. Services offered by WNOC include network
monitoring, trouble tick
eting, billing management, and bandwidth shaping and control. The service that
seems most useful to any WISP would be inventory management
, given its importance and absence of such
a system
. WNOC contends that inve
ntory will be tracked using GPS

systems.

Although these services
may be useful, WNOC seems to provide a service to WISPs, rath
er than arming them with

tool
s

to manage
their own business operations.
It seems that their solution targets a business
that wishes to relinquish
control of some portio
n of operations. Essentially, a WNOC becomes the “middle man” between a
company and its customers and its network.
Many WISPs choose to build their own DBMSs [9].
Sufficient planning can result in a valuable piece of software [10].


Proposed Solution



The goal of my project is to provide a
free

solution to the data managing needs of WISP’s.
Most
software is usually developed for the large scale corporation, employing several hundred people, possibly
operating out of several locations. Other software
is free, and certainly useful for small businesses, but is
us
ually brittle and not customizable
. As it turns out, there are three solutions for WISPs to manage data.
They may chose simplicity, and use spreadsheets to keep track of valuable information.
This solution
works well for the start
-
up business, where there are a small number of employees and customers being
serviced.
The larger WISP
might choose Oracle or MS SQL server to keep track of information in a large,
multi
-
user
corporate
environment.
The middle
-
sized WISP seems to be left without a complete solution to
their needs. In a growing environment, managing data without some type of DBMS will soon grow very
tedious and time consuming, however a middle
-
sized business may not be able to afford
expensive
software, and even if they could, they may be paying for features that they don’t need. The most effective
solution left for these types of WISPs is to build their own system.

I chose this type of small business to t
arget because there are many

businesses emerging in the
field

that are in need of such a system. My experience as an intern with YYireless1, a WISP, will assist me

9

in my endeavor.
The finished system
will be flexible, as

one should be able to add functionality to the
system even aft
er
its completion
.
As

mentioned earlier, communication and feedback with
my former co
-
workers from YYireless1
will provide

a great deal of information about

how to
construct appropriate data
models, which will ultimately assist me in implementing the syst
em in Access. As I am the developer of
the system, YYireless1 will be treated as my customer for all intents and purposes.

I expect my finished
project to be well designed, efficient, and field
-
worthy. I envision my database system being used by
wireles
s ISPs for their data management needs.

I chose Microsoft Access
developer suite
to build the
system

for
several reasons. I
t is not
expensive to purchase, and maintenance expenses for a DBMS created using
A
ccess are relatively low.

Access is also a progr
am that is easily obtained, as it is offered in several additions of Microsoft Office,
which comes standard with nearly all releases of Windows operating systems.

Many people have at least a
general knowledge of Access, and in any case, t
he learni
ng curve

is not especially steep.

With an
understanding of databases, one can implement
a new system

or make changes to an existing
system

in
Access
with relatively little training
.
And, the best advantage to using Access is that it comes as a complete
developer

suite.

Tools for building a

front
-
end application are included along with necessary tools for
building the database itself. These tools integrate seamlessly, eliminating any compatibility issues.

Customer communication is critical to the success of my p
roject. What information should the
system handle? How should this information be organized within the system? Who will have access to
what information? What relevant questions should a user of the system be able to answer relatively easily?
By answer
ing these questions, and others, I will accomplish my goal of building a DBMS that could one
day become an int
egral part of any emerging WISP
s business operations. Prototyping is a software
engineering technique that ensures the quality of a finished prod
uct, and I will therefore use it to assist me
in

accomplishing my goal.

I intend for the system to support inventory
management

by keeping track of equipment at
customer sites, as well as equipment in company vehicles and in storage areas. The DBMS will a
lso store
customer profiles and information about particular installations to assist customer service operations.
The
system will
also keep information about the network status, including informati
on about tower sites,
customers, and bandwidth allocation.

Employee information will also be stored in the system, perhaps for

10

payroll purposes, but more importantly, for the relationship between employees and installations. By
taking a relational approach to building the system, users should be able to answer
important and relevant
questions about their business without difficulty.

The most important feature is the integration of
information, the ability to answer reasonable questions about business operations that will make my
solution very useful.

The system

will support a degree of multi
-
user capabilities.
By storing the DBMS in a remote
location, many users can access the system at the same time over a local area network

(LAN)
. Portions of
the database will be locked during transactions to help maintain d
ata integrity. It would be a bonus for the
database to allow secure user access from nearly any location.
I would like users to be able to access the
system remotely via internet. This will certainly
increase

efficiency of operations. However, internet

access will only be a part of the system if time permits.



Proposed Timeline


Jan 2004



Research various database building tools.



Decide upon the best approach to building the system.



Begin conceptual design of the DBMS.


Feb 2004



Continue developing conc
eptual schemas



Begin
conceptual
work on the
GUI



ER and EER diagrams



Finish proposal

March 2004



Finalize schema of
the DBMS



Work on Inventory Tracking module.


11



Begin writing
VB code



Communicate with former co
-
workers from my internship

April 2004



Work on Cus
tomer and Employee tracking module



Continue writing VB code



Communicate with former co
-
workers as others as needed.

May 2004



Begin supporting required functionality



Continue gathering more experience in the field

September 2004



Work on Network Information
Tracking



Continue writing VB code



Communicate with former co
-
workers as others as needed.

October 2004



Make any
changes to desired functionality



Finish
front
-
end prototype

November 2004



Show prototype to YYireless1 and respond to feedback appropriately



Con
nect front
-
end prototype to the Database, supporting desired functionality



Finalize Queries and Reports

December 2004



Make any final changes
to the system



Support online access (time permitting)



Final Presentation of project


12



Works Cited


1. Connolly, Th
omas and Begg, Carolyn. “Database Systems: A Practical Approach to Design,
Implementation, and Management.” 2002.

2. Bradley, Julia Case and Millspaugh, Anita C. “Programming in Visual Basic version 6.0 Update
Edition. McGraw
-
Hill Companies, Inc. Ne
w York, NY, 10020. 2002

3
. Oracle. “
Globalization. Simplification. Standardization. Automation. Innovation.
” Online. Internet.
http://www.oracle.com.

4
.

Microsoft. “Microsoft SQL Server.” Online. Internet.
http://www.microsoft.com/sql/default.asp

5
.
A listing of
databases or connections to databases or frameworks for connecting to databases
.
Online.
Internet.
http://wiki.cs.uiuc.edu/VisualWorks/Database+packages.

6
. Debian: GNU, Linux. “Database Packages.” Online. Internet. http://olympus.het
.brown.edu/cgi
-
bin/info2www?(slib)Database+Packages.

7
. Olympus. “Database Packages.” Online. Internet.
http://olympus.het.brown.edu/cgi
-
bin/info2www?(slib)Database+Packages

8. Harsany, Jennifer. “Out of This World.” PC Magazine. January, 2004

9.
Sanders, Tim. “WISP Inventory Management.”
Online. Internet.
www.isp
-
planet.com/fixed_wireless/business/2003/inventory.html

10.
Sanders, Tim. “To Each Their Own Database.” Online. Internet.
www.isp
-
planet.com/fixed_wireless/business/2002/inventory
.html

11. Stanford, Jan. “
The Wrong Choice: Two true tales of misspent energy,
C
ash
.” Online. Internet.
www.searchenterpriselinux.com.