Reference no: EM13952664

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