CREATING AND USING A DATABASE

1. Creating the database
2. Determining the database structure
3. Setting up the file structure
4. Entering the data
5. Using the database

Creating the database
Consider your needs
- Report you will need
- Inquiries you will want to make

Determining the database structure
a. Sketch the table structure - what kind of data is needed in each column.
b. Determine characteristics of field - Field name (each field must have a unique field name) - Field type - Field width (the maximum number of characters, including decimal places)

Field Types
- Character field - contain descriptive data
- Numeric fields - contain numbers used for calculation (specify the number of decimal places)
- Data fields
- Logical fields - keep track of true/false or yes/no conditions


Setting up the file structure
1. Design the structure for each table (define the table - define each field in the table - define primary key)
2. Set up the table in design view

Entering the data
1. Enter data into the tables in datasheet view
2. Enter data into the tables by using a graphical form

Using the database
Many operations can be performed to view and modify the data
- List the records
- List specific fields
- Make a query
- Modify the data

List the records
a. Displays the table in datasheet view (displays all rows and all columns)
b. Can be displayed on monitor or printed

List specific fields
a. Displays only the fields you want to view
b. Use SQL to select the desired fields

Query
Use SQL or query-by-example to produce a query (use relational operator to define criteria - Can be displayed on monitor or printed - Can be formatted into a report)

Modify the data
a. Add new records
b. Update (make changes to) an existing record
c. Delete records

THE OBJECT-ORIENTED DATABASE MODEL

1. Designed to manipulated complex data types (examples include maps and audio and video files)
2. The object is the main focus
3. Designed to incorporate object-oriented programming with large amounts of complex data


An Object
a. Represent a real-world entity (person, place, or thing)
b. Includes both the entity and actions that work with that entity (Includes characteristics (properties) of entity - Includes actions the entity can perform - Include actions that can be performed on the entity)

Data Warehouses
a. Contain data that has been captured in company databases (can contain data that has been gathered from external sources)
b. Use a variey of analytical tools

Analytical Tools
1. Online analytical processing (OLAP) software (Analyzes data from all database in the data warehouse -Provides different "views" of the same data)
2. Data mining uses sophisticated statistical and artificial intelligence techniques (Looks for previously unrecognized patterns, relationship, and trends among the data

DBMS (3) - Data Integrity

Data Integrity

• Refers to the degree to which data is accurate and reliable
• Integrity constraints - rules all data must follow

- if integrity constraints are not followed, the data is unreliable

Integrity Constraints

• Define acceptable values for a field (for example, the value of a month cannot be greater than 12)
• Primary keys cannot be duplicated
• Foreign keys cannot be used unless they exist as a primary key (A SalesID that is used in the customer table must exist as a primary key in the salesperson table)

DATABASE MANAGEMENT

• Database Management System (DBMS) - allows you to create a database and enter, modify, and retrieve data as needed
• Available at all levels (Sophisticated systems for a mainframe environment) - (Inexpensive, easy-to-use personal computer systems)

DATABASE FEATURES

• Data dictionary
• Data maintenance
• Data retrieval
• Concurrency control
• Security
• Backup and recovery

Data Dictionary

• Stores data about the tables and fields within the database (for each table, it stores the table name and relationships to other tables) - (For each field, it records information about that field)
• Also called a catalog
  

Data Maintenance

• Three basic operation (Adding new data - Modifying existing data - Deleting data)
• Two methods for performing operations (Interacting directly with the DBMS - Using programs to access the data using special commands)

Data Retrieval

• Involves extracting the desired data
• Two primary forms of data retrieval (Queries - Reports)

QUERIES

• You present a set of criteria; the DBMS selects matching data from the database
• Use a query language (Structured Query Language [SQL] is supported by most relational database) - (Query-by-example [QBE] uses a graphical interface to generate the SQL) - (Display results in a table-like grid)

SQL - Uses English-like statements (-SELECT specifies the field(s) to be selected - FROM specifies the table(s) containing the fields - JOIN specifies the relationship between tables - WHERE specifies the criteria that must be met

QUERY BY EXAMPLE

• User adds tables to query (user drags fields down into grid at bottom of window - criteria can be added by typing them in the Criteria row of the grid)
• Use can execute the query by clicking an icon a toolbar

QUERY RESULTS

• Retrieved records are displayed as rows
• Field specified in the select clause are displayed as columns

REPORTS

• Provide a formatted presentation of data from the database (Allow you to group reports and total by group, if desires - Normally contaon more data than querues)
• Reports are designed with a report generator

Concurrency control

• Manages simulataneous database users (If several users tried to update the same record at the same time, apdates might not be processed correctly)
• Employs a record-locking scheme (Once a user accesses a record, it is locked until the first update is complete
  

Security

• Many security features are built into most DBMS software (Users can be required to enter a user ID and password - Each user ID may have different access to the data [Read-only, permits the user to look at the data but not change it - Update, permits the user to make changes to the data - No privilages, user can not read or update the data
  

Backup and recovery

• Backup - a copy of database (Backup should be made periodically)
• Recovery - replace a damaged database with the good backup
  

MANAGEMENT INFORMATION SYSTEMS: CLASSIC MODELS AND NEW APPROACHES

Classical Management Functions

• Planning -devising plans for the organization and setting goals to achieve the plan.
• Organizing - deciding how to use the organization's resources
• Staffing - hiring and training workers
• Directing - guiding employees to perform their work in a way that support to organization's goals
• Controlling - monitoring the organization's progress toward reaching its goals

Strategic Level

• Managers at this level are often called executives
• Focus - long-range view of the firm
• Primary function - planning

Tactical level

• Managers at this level are often called middle managers
• Focus - carrying out the visions of top-level managers
• Primary functions - organizing and staffing

Operational Level

• Managers at this level are often called supervisors
• Focus - meeting that day's objectives
• Primary functions - directing and controlling

Management Information System

• A set of formal business system designed to provide information for an organization

1. - Integrates the five management functions

• MIS manager runs the MIS department

1. - Also called Chief Information Officer
2. - Must be comfortable with computer technology and the organization's business

The New Management Model

• The traditional model has changed over the years

1. - Largely due to advances in technology
2. - Also due to changes in the global business environment

• Features of the new model

1. - A flattened pyramid
2. - The impact of groupwave
3. - Team work

A Flattened Pyramid

• Networks connect people to data and other people

1. - Make information more readily available
2. - Allow managers to supervise more people
3. - Allow decision-making authority to be spread throughout the organization

The Impact of Groupware

• Groupware permits information to be assembled in central database

1. - Employees in the different department or different offices can share information directly

• Impact managers in different ways

The Impact on Managers

• The way information is shared

1. - People acquire power in an organization by knowing things others do not
2. - Groupware reduces this imbalance
   

• Changes in managerial authority

1. - with information more readily available, decisions are more prone to being second guessed

DBMS: Getting Data Together

The Hierarchy of Data

• Field – a group of one or more characters that has a specific meaning
• The smallest meaningful unit of data
• Describes one characteristic of a person, place, or thing
• Record – the set of fields containing data about a person, place, or thing
• File – a collection of related records

File System Approach

• Each application had its own file

Data was not shared among application

• Resulted in a great deal of data redundancy, the repetition of the same data value

Increased the risk of inaccurate data
Increased the amount of storage space needed.

Database Approach

A collection of related tables

• In database technology, a file is called a table
• Each entity is stored in a separate table
• Tables are linked by a relationship between primary and foreign keys
  

Primary Key

A field that uniquely identifies a record

• SalesID can be a primary key for the Salesperson table
• Once a salesID appear in the table, no other salesperson can have that ID

Foreign Key

A field in one table that is a primary key in another table

• SalesID can be used in the Customer table to identify the salesperson who serves that customer
• The same SalesID can appear in many customer records (a salesperson can serve many customers)

DATABASE MODEL

• Define the way a database organizes data
• Four main models

1. Hierarchical
2. Network
3. Relational
4. Object-oriented

Hierarchical Model
1. Arranges data in hierarchical "parent-child" relationship

• Each parent record can have many child records
• Each child record has only one parent record

2. Complex and inflexible


Network Model
1. Arranges data in complex network of "parent-child" relationship

• Each parent record can have many child records
• Each child record can have many parent records

2. Complex and inflexible

Relational Model
1. Data organized in table format

• Columns represent fields
• Row represent record

2. Tables related by primary/foreign key relationship
3. Most current database development uses this model



Object-oriented Model
1. Designed to deal with complex data types
2. Focuses on the object

• An object represents an entity
• Represents data about that entity and the types of operations that change that entity

Database Management: Getting Data Together

Objectives

• Describe the hierarchy of data
• Explain the differences between files and databases
• List the four database models
• Describe the concept of data integrity
• Describe the functions of a database management system
• Describe the process of creating a database in general terms
• Compare and contrast relational and object-oriented databases
• Explain what a data warehouse is and how it differs from a database

Summary

• The field is the smallest meaningful unit of data and consists of a group of one or more characters. The group of fields containing data about a single entity makes up a record, and the collection of related records is a file.
• The traditional file processing approach to information systems results in a great deal of data redundancy—duplication of data. Records in a file are identified by their primary key field (or just primary key), which contains a unique value for each record.
• A database is a collection of related files (tables in database terminology) in which each table contains data concerning only a single entity. Tables are linked, or related, when a field, called a foreign key, in one table contains the value of a primary key in another table.
• There are four main database models (methods of organizing data within a database): hierarchical, network, relational, and object-oriented. Most databases today use the relational database model, which organizes data in a table format using rows and columns.
• Data integrity refers to the degree in which data is accurate and reliable. Integrity constraints are rules that data must follow to maintain integrity.
• A Database Management System (DBMS) is a software package that allows the user to create a database, enter data into the database, modify the data as required, and retrieve information from the database.
• Databases contain a data dictionary that stores data about the tables and fields within the database.
• Data maintenance consists of three basic operations: adding new data, modifying existing data, and deleting data; it is performed either directly through DBMS routines or by programs containing special commands.
• Data retrieval involves extracting the desired data from the database. Queries retrieve data from a database by presenting criteria that the DBMS uses to select the desired data. Queries are written using a query language. Structured Query Language (SQL) is a standard query language supported by most DBMSs.
• Queries can also be prepared by using a graphical interface to set criteria. This method is called query-by-example (QBE).
• A report provides a formatted presentation of data from the database. Whereas query results are normally displayed on the screen, reports are generally printed. Reports are designed using a report generator built into the DBMS.
• The DBMS employs a record locking scheme to prevent problems that can occur when two users attempt to update the same record concurrently.
• One way to provide database security is for the DBMS to assign specific privileges to each user. Read-only privilege permits record viewing only, update privilege allows the user to make changes, and a user with no privilege is prevented from even seeing the data.
• Backup and recovery features of a DBMS assist the user in creating a backup, or duplicate copy, of the database at periodic intervals, and allow for recovery by restoring the database from the backup copy. Some DBMSs assist recovery by automatically reprocessing transactions processed after the backup was made.
• Creating a database starts with specifying the table structure—a list of fields including the field name, type of data stored in the field, and the size, or width, of the field. Data types supported by most DBMSs include character fields, which contain descriptions; numeric fields, which contain numbers for calculations; date fields, which contain dates; and logical fields, which keep track of true/false, yes/no conditions. In Microsoft Access, table structure is specified in the design view of the table.
• Data is entered into the table in the datasheet view, which lists all the records stored in the table.
• Relational operators are symbols that are used to make comparisons when formulating queries.
• The object-oriented database model was developed to meet the need of manipulating complex data types such as audio and video. It focuses on the object, which represents a real-world entity and contains both data and operations on the data. Object-oriented database management systems (OODBMS) create and manipulate object-oriented databases. Object/relational database management systems (O/RDBMS) are relational DBMSs enhanced with object-oriented capabilities.
• Online transaction processing (OLTP) software processes the day-to-day transactions of an organization by updating data stored in databases. Since databases are not organized to provide the data that management needs to make decisions, data warehouses were developed. A data warehouse contains data that has been captured from the database in summary form, on a scheduled basis, over a period of time. Data in a data warehouse is analyzed by online analytical processing (OLAP) software to provide information to management.
• Data mining uses sophisticated statistical and artificial intelligence techniques to discover previously unrecognized patterns, relationships, correlations, and trends among the data in the data warehouse.

Key Terms

backup - character fields - data dictionary - Data integrity - Data maintenance - Data mining - data redundancy - Data retrieval - data warehouse - database - Database Management System (DBMS) - datasheet view - date fields - design view - field - file - foreign key -Integrity constraints - logical fields - models (methods of organizing data within a database) -numeric fields - Object/relational database management systems (O/RDBMS) -Object-oriented database management systems (OODBMS) - object-oriented database model - online analytical processing (OLAP) - Online transaction processing (OLTP) -primary key field or primary key - Queries - query language - query-by-example (QBE) -Read-only privilege - record -record locking - recovery -related - relational database -Relational operators -report - report generator - Structured Query Language (SQL) - table structure -tables -update privilege

Management Information Systems: Classical Models and New Approaches

Objectives

• List and describe the classic functions of managers: planning, organizing, staffing, directing, and controlling
• Describe the purpose and components of a management information system (MIS)
• Explain how computer networking and related software have flattened the classic management pyramid
• Describe how companies use employees in task-oriented teams
• Describe the purpose and function of sophisticated software for top managers
• Explain the problems and solutions related to managing personal computers
• Explain the concept of total cost of personal computer ownership

Summary

• All managers have five main functions: planning, organizing, staffing, directing, and controlling. A management pyramid shows that top-level managers focus primarily on strategic functions, especially long-range planning; middle-level managers focus on the tactical, especially the organizing and staffing required to implement plans; and low-level managers are concerned mainly with operational functions—controlling schedules, costs, and quality—as well as with directing personnel.
• A management information system (MIS) is a set of business systems designed to provide information for decision making. A computerized MIS is most effective if it is integrated throughout the entire organization.
• The MIS manager, a person who is familiar with both computer technology and the organization’s business, runs the MIS department.
• The traditional management pyramid has been flattened by the dissemination and sharing of information over computer networks. The impact of groupware has removed exclusive manager access to information and has forced managers to share decision-making. Some companies are organizing workers into teams around tasks.
• A decision support system (DSS) is a computer system that supports managers in nonroutine decision-making tasks. A DSS involves a model, a mathematical representation of a real-life situation. A computerized model allows a manager to try various "what-if" options by varying the inputs, or independent variables, to see how they affect the outputs, or dependent variables. The use of a computer model to reach a decision about a real-life situation is called simulation. Since the decision-making process must be fast, the DSS is interactive, allowing the user to communicate directly with the computer system and affect its activities.
• An executive information system (EIS) is a decision support system for senior-level executives, who make decisions that affect an entire company.
• When personal computers first became popular in the business world, most businesses did not have general policies regarding them, which led to several problems. Many businesses created the position of personal computer manager (later called the network manager or LAN manager) to ensure coordination of personal computers, established acquisition policies to solve the compatibility problem, established information centers or support centers to provide assistance to users, provided formal and informal training for users, and used software to monitor their existing personal computers.
• Many organizations rely on the one-shot, teacher-in-the-classroom model for training, if they offer training at all. Follow-up support is necessary, and can be provided through in house gurus or information centers. CD-ROM and Web-based training have also proved to be effective, both for initial and follow up training.
• Managers often have little idea of how many computers are in use within a department or company or what software is being used on them. Software is available to keep track of all hardware and software installed on company networks.
• Total Cost of Ownership (TCO) refers to a methodology originally developed by the Gartner Group to determine all the costs involved in owning personal computers. These costs involve not only the original purchase price of the hardware and software, but also less obvious costs such as training, support, upgrades, and maintenance.

Key Terms

controlling - decision support system (DSS) - dependent variables - directing - executive - information system (EIS) - independent variables - information centers or support centers - management information system (MIS) - MIS manager - model - organizing - personal computer manager or network manager or LAN manager - planning - simulation - staffing - Total Cost of Ownership (TCO)