MICROSOFT ACCESS EXPERT

Our group chose to develop a computer inventory database system for a
nationally based real estate developer, based upon group consensus and a
need by the I.T. manager: Phil Switzer. This project is more than an
upgrade compared to the previous system of separate inventory sheets
produced in Microsoft Excel. The database application was developed and
refined in a team atmosphere using the introduction of Database
Processing. In retrospect, our team can state that the database project
is an overall success. The model will be used in the actual environment
it was designed for.

Did the designs allow one to extract data into relevant informational
views?

Can the DBMS aide in making real time intelligent decisions?

Can it aide in closing the loop from decision into action?

Does it help in eliminating risks?

These were our overall goals for the DBMS.

Design Summary

This was a top-down development project. In summary, Mr. Switzer was
interviewed to discover generalizations regarding the existing data
schema, and approaches that were in place. The abstract data model was
laid out on approximately 15 pieces of loose-leaf paper. Some detail was
thrown out entirely and other detail was inserted. In other words data
modeling had began (Kroenke 42). This is where all the existing
spreadsheets were examined. The spreadsheets differed from location to
location in regards to fields, format and the logical representation of
the office computer systems data. Our goal was to develop a database
modeled upon the users requirements which can be updated from any
location and maintain data consistency without replication. This will
ensure that the Information Technology (IT) Director, will have data
that is useful, current and has integrity.

Database Considerations

The purpose of a database is to keep track of things (Kroenke 3). In our
example, the IT Director needs to maintain an up to date database of
computer assets within the corporation from five different states. The
IT Director sets protocol for information systems upgrades, deployment
and maintenance, the data one must rely upon determines the foundation
for the Director’s business decisions. The business rules
predetermined by the IT Director must be reflected in the database and
database applications.

There is an inherent relationship that exists when even a single user
designs a database. The database is supposed to be a logically coherent
collection of data with some type of inherent meaning. The database is
usually designed either directly or through the DBMS directly, built and
populated with a specific purpose in mind for an audience of users,
though it may be just one (Kroenke 25). The general relationship is that
users access applications that access the DBMS, which then accesses the
data (Kroenke 11).  Kroenke defines databases into four
characteristics: personal, workgroup, organizational, and Internet
technology.

The basics of the database design require that the developers have a
basic understanding of databases and in this scenario: Information
Systems & system development life cycles (SDLC). An information system
can be defined as the resources that enable the collection, management,
control and dissemination of information throughout of an organization.
Database is a fundamental component of an information system; therefore,
their life cycles are "Inherently" linked together. The information
systems life cycle includes the following stages, planning, requirement
collection analysis, design, prototyping, implementation testing,
conversion and operational maintenance.

The stages in the database Application lifecycle is also associated with
information systems life cycles, Its most common stages are: Database
Planning, System definition, Requirements collection and analysis,
Database design, DBMS selection, Application design, Prototyping,
Implementation, Data conversion and loading, Testing and operational
maintenance.

            Database Planning: This is planning how the
stages of the lifecycle can be done more efficiently and effectively as
possible. This involves, the identification of business plans, rules,
and goals, evaluation of current information systems available (to
determine their strengths and weaknesses), study of IT opportunities,
etc. In our assignment this was the most crucial stage.

            System Definition: This is identifying all the
boundaries of the system and how it interfaces with the other parts of
the organization. This is done in order to integrate all the scopes and
boundaries of the database application including its major application
areas and user groups. In or design the I.T. manager mandated that it be
designed for Microsoft Access, and a very limited number of users. This
is part of the physical design.

            Requirements Collection and Analysis: Is defined
as the process of collecting and analyzing information about the part of
the organization that is to be supported by the database application,
and using this information to identify the requirements of the new
system. Requirements are defined as the features to be included in the
database management system (DBMS). As we developed the application, the
I.T. manager was continually interviewed, and his needs were built into
the system as it progressed.

            Database Design: Is the process of creating a
design for the database that will support all the operations and
objectives of the organization. The major design goals in this phase
were: Represent the data and the relationships between the data required
by all major applications and user groups. Provide a data model that
supports any transactions required on the data. Specify a minimal design
that is approximately structured to achieve the stated performance
requirements for the system such as response times, or number of
concurrent users.

The DBMS Selection: This phase consisted in the selection of the
appropriate DBMS to support the database application, which was to be
Microsoft Access. Application Design: This phase consisted in the design
of the user interface, (Forms) and the application programs that use and
process in the database. Prototyping: Consisted of building a working
model of the Database application. This model does not normally have the
required features or the required functionality. In our case, extensive
Visual Basic code development was used in the form design and
development of the extensive queries. This is usually done for the users
or customer to identify the errors or the things that work well in order
to provide a better implementation in the future. Prototyping led to
three independent concurrent designs of the original model as if we were
competing consultants. The best model would be chosen as if it was
before a committee.  Implementation: This was the physical realization
of the database and application design. This is the deliverable we are
presenting in the final. Phil Switzer will perform the actual
implementation at his office.

Data Conversion and Loading: This phase consists of transferring all the
data into the new database and converting the existing applications to
run with the new database. In this specific scenario most of the data
was already provided, so the deliverable was to have approximately fifty
percent of the nation wide data, but by no means is the database
completely loaded at this time with all the nation wide data.  Testing:
Is the process of executing the application programs in order to find
its errors. This part differs from the prototyping part, because we are
now using the actual database, with all the features included.
Operational Testing: Is the process of monitoring and maintaining the
system after the installation is completed.

Team Project Overview

 

            The agenda for our Learning Team’s Conceptual
Database Design was to select a suitable inventory model that was
originally designed for DBM 380.1. This first team meeting was focused
on an evaluation of whose design was superior for the course work and
for the client. It was at this first meeting that James Flippen’s
design was selected because it was efficient and utilized more
functionality as it integrated many more features that were impossible
to achieve without a course in Visual Basic. This set a de facto
standard in our team vision. This application database almost seemed
trainable with the added functionality of using additional Visual Basic
coding to achieve stellar query and efficiency.

            At meeting two we compared the concurrent
modifications of existing database model to elucidate the structure of
the data schema, relationships and data modeling. Our team wanted the
logical relationships amongst the data to support and represent business
relationships and rules. The more relevant, useful intelligence you have
at your fingertips about your business, your customers, your partners,
and your operations determines the more your organization can make
better decisions and increase competitive advantage. Each team member
and most particularly Phil Switzer examined the Data Architecture.

Did the designs allow one to extract data into relevant informational
views?

Can the DBMS aide in making real time intelligent decisions?

Can it aide in closing the loop from decision into action?

Does it help in eliminating risks?

Quality Control

            In meetings three and four, concurrent designs
using the same basic application model achieved an independent analysis
and quality control of the DBMS. Now the designs were viewed for
functionality regarding business rules, reporting abilities, and forms
that would provide that relevant information to make the best business
decisions. The best parts of each design were integrated into the
evolving model.  We analyzed the queries and results. Continually
building upon structured query language within the program and the
information we learned in the classroom, the database evolved into a
system that could provide those answers.

            Form design was the creative session of the
requirements. Simple forms were redesigned for functionality, while new
forms brought in new and efficient ways to deliver the data to the user.
Queries retrieved the specific data in a manner that made it relevant to
business decisions.

Again our goals were examined. Did the designs allow one to extract data
into relevant informational views? Can the DBMS aide in making real time
intelligent decisions?  Can it aide in closing the loop from decision
into action? Does it help in eliminating risks? Yes our collaborative
approach allowed our team to meet our milestones, goals and produce the
results that we were looking for.

 

Milestone Date completed Deliverable(s) completed

Project planning 08/31/04 ·        Select Inventory System

·        Share System

Milestone 1 08/31/04 ·        Submit Database

·        Individual Designs

Milestone 2 09/07/04 ·        Schema/Table/Relationships

Milestone 3 09/14/04 ·        Input Forms/Queries/SQL

Milestone 4 09/21/04 ·        Reports

Project conclusion 09/21/04 ·        Presentation & Report

 

Original Tables

            These fields were then modified into a new list
as follows.

1.      Type of Computer

2.      Computer Serial Number

3.      Warranty Expiration Date

4.      Hard Drive Size and Manufacturer

5.      Memory Size and Manufacturer

6.      Network Interface Card and Manufacturer

7.      Monitor Size and Manufacturer

8.      Video Card and Manufacturer

9.      Audio Card and Manufacturer

10.   CPU

The tables were re-modeled and entity relationships developed. The
tables are displayed below.

 









 

 

 

 

 

Entity Relationships were developed based on the Business Rules
established by the IT Director. The following are the primary entity
relations.



There can be many computers in one location.

 



 



                        There can be one monitor
assigned to many employees.







            There can be one employee assigned to many
computers.

     These PRIMARY entity relationships were desired by the IT
Director. This data model is based upon reviewing, redesign, pre-testing
and functionality within the IT Director’s organization. As the
development life cycle matures, design changes are inevitable.  A
flowchart of the design of the database is included in the appendix.

 

Redesigned Tables, Schema and Relationships for Report Queries

COMPUTER

            LocationDesc   TagNumber    
UserName       DeviceDeviceMake    Software

MONITOR

  LocationDescription    TagNumber          
MonitorMakeMo    SerialNumber        UserName

 

            Riverwalk

  PRINTER

  TagNumber            PrinterMakeMode 
SerialNumber            LocationDescription     
UserName

 

SERVER

  LocationDescription   TagNumber     
Device           DeviceMakeModel    SerialNumber

UserName

 

  SOFTWARE

 
TagNumber                               Â
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
Software                                
                               UserName

 

 

 

Redesign continued

            Of the 96 tables that were designed in all three
team members’ databases only 22 were chosen to be in the final
project. 22 Tables were created to allow flexibility, avoid data
replication, and customize queries. 17 Queries were designed to allow
maximum business intelligence based upon the clients need.38 Forms were
designed, but only 10 selected. 24 reports were generated, only 5 made
it into the final deliverable.

One team member had designed three separate computer inventory databases
with many cross linked tables using both Table Designer and DbAPP listed
in the last two chapters of the book. A good database is a model of the
user data, it is only as good as the foundation or Schema. We could have
overdone the design, tables, forms, queries and reports, but we chose to
follow though and intensively work on the Flippen design. Our team spent
more time in design and development to make this a database that could
be utilized for good business decisions immediately. It met the criteria
that we established early on in the first meeting.

Design Goals

Did the designs allow one to extract data into relevant informational
views?

Can the DBMS aide in making real time intelligent decisions?

Can it aide in closing the loop from decision into action?

Does it help in eliminating risks?

Conclusion

            Our goals were met; we learned many valuable
lessons developing this database application and SQL statements. These
lessons will prove valuable in all of our future endeavors.

Works Cited

Kroenke, D. M. (2000). Database processing: Fundamentals, design and
implementation. [UOP Special Cover Edition]. Upper Saddle River, NJ:
Prentice Hall.