Just so that everyone is aware, this is not an actual assignment.  I
need the answer to this problem from my textbook, as I will be using
this to study from for my in class Database Management problem.  For
the final part of the problem, please use excel, as this is the only
data base program which I am familiar with.

PC Inventory Assignment
You have been asked to conduct a survey of the IT assets in a large
company.
There are approximately 30 departments with some type of computer
equipment (terminals, printers, a few scanners, etc.) located in three
separate buildings on the campus.  The last inventory was conducted
two years ago, and management thinks that there are somewhere around
250 to 300 individual pieces of equipment.  The majority of these are
used for general administrative applications(word processing, e-mail,
spreadsheet applications, etc.) but some are used for specialized
applications that must be available 24x7.  All are connected in a
local LAN network with several servers connected to a mainframe
enterprise system with a mainframe DBMS.
There are over 350 individuals who have access (individual passwords).
Recently, the company recieved a request from Microsoft to agree to an
enterprise licensing agreement (a single annual fee covering all the
icrosoft installed on all machines.  The fee is based upon the total
number of software licenses installed.
For budgeting reasons, the company wishes to pay only for the software
products that are necessary and critical to the business (i.e. some
systems have software applications installed that are rarely used). 
In addition, management needs to know what departments 'own' the
asset, who the users are, what users use what software products, and
what applications are run on what hardware.

Your assignment is to:
1) Develop a plan to conduct a complete inventory of the IT assets
(hardware and licensed software programs).
2) List the data elements that should be collected to achieve the
business objectives.
3) Organize these data elements into some logical file or data base
(e.g. Excel, or Access, or some other data base model of your choice.)

---

Clarification of Question by bildy-ga on 01 Oct 2002 18:10 PDT

The final part of this question can be answered in either Excel or
Access, whichever the answerer prefers.

bildy-ga,

This description is that of what is generally referred to as an "asset
tracking" relational database management system (RDBMS). The given
problem is just a fancy way of saying, "We want to know exactly what
we have, how much of each, who's using it for what and when." [And, in
real life: "We also need to know how much it will cost us to develop
and maintain it, how long it will take you to make it, and what kind
of support you will offer for it after we buy it."]

In this example, the "assets" to track are individual pieces of IT
equipment, such as computers and associated peripherals. Additional
program "requirements" as outlined in the question are that the
database will track which software applications are on each computer,
who uses each system and for what.

Keep in mind that there are many different solutions for this same,
general problem, but all of them would involve the same process of
establishing requirements (what the program should do when finished),
designing a solution (i.e. your database schema: table relationships
and field types), creating it (actually building it in the database
application chose in your Design phase, (i.e. MS Access 2000,
FileMaker Pro or Oracle 8i, for example), implementing it (actually
making it happen--deployment, end-user training) and then maintaining
that solution (data back-up, user support, capacity planning, program
enhancements). That's the basic, overall "plan."


Now, it's a little tricky because in real life, people move things
around--a printer that's in Accounting today could be in Admin
tomorrow, so it's important to be able to track each piece of
equipment by itself, regardless of where it is. This is usually done
by assign ID #s, or by using the equipment Serial #.

I propose a general schema with 4 tables in the database: One Table
will be called, "Computers," one "Peripherals," "Software" and
"Personnel."

The primary keys for the Computers and Peripherals tables are the ID
#s. Every computer gets a unique "PC ID" or the like, every printer,
scanner, ZIP drive, etc. also gets a unique ID #, although these ID #s
are independent of the PC IDs.

The Software table uses the PC ID field to reference which computer
the software listed is associated with.

The Computer and Peripherals tables each have a field for Name of
user, which is a foreign key  from the Personnel table.

Open MS Access 2000 and create new file, name it "IT Assets".  Create
a New Table by clicking the "Table" button under the Objects list on
the left side of the Access window, create the fields as described
above, save, and create the Next table, create its fields, until all 4
tables are created.

Then, Open the Entity-Relationship diagram by clicking the icon with
three tables linked on the toolbar. Select the Tables tab, click OK.
Link the related fields here.

This is the basic process.

Then, you would create queries to extract desired information from
your database, and create Reports to display that information in a
professional manner according to user preference.

Below is a link to a site on database design which you will find
useful:

"Your First Database," by Jay Greenspan,
http://hotwired.lycos.com/webmonkey/backend/databases/tutorials/tutorial3.html

From Xiacon.com, here is a page on a professional asset tracking
database design, so you can get an idea of what to shoot for
eventually, and see how the basic concepts are implemented
professionally:

http://www.xiacon.com/asset-tracking/database.asp

Google search strategy:

keywords: "database design"

://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=+database+design&btnG=Google+Search
,

"asset tracking database design" 

://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=+asset+tracking+database+design&btnG=Google+Search

Good luck in continuing your inquiries!

~omniscientbeing-ga

---

Request for Answer Clarification by bildy-ga on 01 Oct 2002 19:39 PDT

I asked for the researcher to do the final 3 questions, not just to
state how to do them, so that I could use the way that they solved the
problem to solve another problem in class.  Please submit a
clarification which solves the 3 questions posed, which should include
an actual Microsoft Access or an Excel file which can be posted on a
website.

Thanks Again,
Bildy

---

Request for Answer Clarification by bildy-ga on 01 Oct 2002 19:42 PDT

What you have provided is excellent, it just needs to be completed as
per the three questions to be a five star answer.

---

Clarification of Answer by omniscientbeing-ga on 01 Oct 2002 21:31 PDT

Hi bildy-ga,

I'm sorry, but there is no way to attach or "post" a file to a Google
Research Answer, as you asked for in your request for Clarification.
The table relationships are not easily described in text only format,
but here is what you can do, in lieu of my creating and posting the
file here for you, which will still show you how to do your future
assignments:

Open whichever version of MS Access you are using, and select from the
File menu, New File. In the New dialog box, select the "Databases"
tab. Select the "Asset Tracking" template and click "OK." Name your
new file "IT Assets" and click Save. Follow through the dialogs of the
Database Wizard, which will automatically create for you an Asset
Tracking database which you customize with your own desired fields and
macros/VBA modules, if desired.

Then, just as with any Access .mdb file you make from scratch, you can
open the Entity Relationship Diagram and define/modify relationships
between the different tables and queries. Simply observe how the
Primary Keys are linked from table to table, and then make one on your
own "from scratch." Also, you can view the Tables and Forms in Design
View to see their components and how they are constructed.

This is really the best way to learn, as A) you know the design is
solid because they are standard, widely accpeted templates, and B)
you're going to build it from the ground up afterwards to solidy and
demonstrate your understanding.

Have fun!

~omniscientbeing-ga

~omniscientbeing-ga

I previously requested to repost this question, which I though would
be automated.  If you are unable to repost this question before
Thursday at 10:00am Eastern Time, then please provide a refund.

Thanks Again,
bildy

Povided some decent information, but never completed the task as
stated in my original question.  Answerer stated that it could not be
done, but in reality all he needed to do was to post the file to a
website, etc...If the researcher knew that he could not fully answer
the question from the beginning, he should never have accepted the
assignment.

MS Excel is not in itself a "database" application--it's a spreadsheet
application. It can be modified, as with VBA code, to have a data
entry GUI, but Access is the true MS Office database application, not
Excel.