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