Chapter 10: Managing a Database

Databases, Data and Information

- Database is a collection of data organised ina manner that allows access, retrieval and use of that data
- Data is a collection of unprocessed items, include text, numbers, images, audio and video
- Information is processed data that is organised, meaningful and useful

- Database software, aka database management system (DBMS), allows users to:

• Create a computerized database
• Add, modify and delete data
• Sort and retrieve data
• Create forms and reports from the data

- Data integrity identifies the quality of the data
- Garbage in, garbage out (GIGO) points out the accuracy of a computer's output depends on the accuracy of the input
- Valuable information should have the following characteristics:

• Accuracy
• Verifiable
• Timely
• Organised
• Accessible
• Useful
• Cost-effective

The Hierarchy of Data

- Data is organised in layers
- A data file contains records, a record contains fields, and a field contains is composed of one or more characters

Characters --> Fields --> Records --> Data files

- A character is represent by a byte. Characters can be numbers, letters, blank space, punctuation marks or other symbols
- A field is a combination of one or more related characters. Fields include field name, field size and data type
- Data types:

• Text
• Number
• AutoNumber
• Currency
• Date
• Memo
• Yes/No
• Hyperlink
• Object
• Attachment

- A record is a group of related fields. A primary key is a field that uniquely identifies each record in a file
- A data file is a collection of related records stored on a storage medium

Maintaining Data

- File maintenance refers to the procedures that keep data current, include adding records to, modifying records in, and deleting records from a file
- Users add new records to a file when they obtain additional data that should be stored

- Users modify a record to correct inaccurate data or update old data with new data
- When a record no longer is needed, a user deletes it from a file

Validating Data

- Validation is the process of comparing data with a set of rules or values to determine if the data meets certain criteria
- Validity checks, aka validation rules, reduce data entry errors and thus enhance the data's integrity

• Alphabetic/Numeric check
• Range check
• Consistency check
• Completeness check
• Check digit
• Other checks

File Processing and Databases

- File processing system

• Each department within an organisation has its own set of files
• The records in one file may not relate to the records in any other file
• Weaknesses: Redundant data and isolated data

- Database

• Programs and users share the data in the database
• Advantages: Reduce data redundancy, improve data integrity, shared data, easier access, reduced development time
• Disadvantage: More vulnerable

Database Management Systems

- A data dictionary, aka a repository, contains data about each file in the database and each field in those files
- A DBMS provides several tools that allow users and programs to retrieve and maintain data in the database
- Methods to retrieve and maintain data:

• Query language
• Query by example (QBE)
• Form, aka data entry form
• Report writer, aka report generator

- A query language consists of simple, English-like statements that allow users to specify the data they want to display, print, store, update or delete
- Structured Query Language (SQL) is a popular query language that allows users to manage, update and retrieve data
- QBE is a feature that has a graphical user interface (GUI) to assist users with retrieving data

- A form is a window on the screen that provides areas for entering or modifying data in a database
- A report writer allows users to design a report on the screen, retrieve data into the report design and then display or print the report
- A DBMS provides means to ensure that only authorized users can access data

• DBMS allow different levels of access privileges to be identified for each field in the database

- A DBMS provides a variety of technique to restore the database to a usable form in case it is damaged or destroyed

• Backup should be made on a regular basis
• DBMS maintain a log
• Recovery utility
• Continuous backup

Types of Databases

- Every database and DBMS is based on a specific data model
- A data model consists of rules and standards that define how the database organizes data
- Types of database:

• A relational database is database that stores data in tables that consist of rows and columns. It stores data relationships, a link within the data
• An object-oriented database (OODB) stores data in objects. Examples of applications include media databases, groupware databases, computer-aided design (CAD) databases and hypertext databases
• A multidimensional database can stores more than two dimensions of data.
• A data warehouse is a huge database that stores and manages the data required to analyze historical and current transactions. It uses multidimensional databases.

Web Databases

- Databases on the web allow users to:

• Shop for products or services
• Buy or sell stocks
• Search for a job
• Make airline reservation
• Register for college classes
• Check semester grades

Data Administration

- Database analysts and administrators are responsible for managing and coordinating all database activities
- Database analyst (DA) decides on proper field placement, defines data relationship and identifies users' access privileges
- Database administrator (DBA) creates and maintains the data dictionary, manages security, monitors performance and checks backup and recovery procedures
- Employees should learn how to use the data in the database effectively to:

• Interact with database
• Identify new data for the database
• Maintain the database