The Database Approach to Data Management

Database technology eliminates many of the problems of traditional file organization by organizing data: centralizing data and controlling redundant data, and serve many applications and different groups at the same time.

A database management system (DBMS) is software that:

• Acts as as an interface between application programs and the data files
  
  
• Separates the logical view of the database (how the data is perceived by end users) and the physical view (how the data is actually organized on storage media). It also allows different logical views for different users.
  
  
• Helps to reduce data redundancy and eliminate data inconsistency by allowing a central, shared data source
  

FIGURE 6-3 HUMAN RESOURCES DATABASE WITH MULTIPLE VIEWS A single human resources database provides many different views of data, depending on the information requirements of the user. Illustrated here are two possible views, one of interest to a benefits specialist and one of interest to a member of the company’s payroll department.

A relational DBMS, such as Microsoft Sequel Server or MYSQL, represents data as two-dimensional tables called relations. Each table consists of a grid of columns and rows of data. Each row is a record, or tuple, divided by columns into separate fields for that record. One column in each table acts as a primary key, defining for each record a key field that is used to uniquely identify each record. Relational databases use primary keys to connect records from one table to other tables. When a primary key from one table is used in second table to locate, or look-up, records from the first table, it is called a foreign key.

Figure 6-4

FIGURE 6-4 RELATIONAL DATABASE TABLES A relational database organizes data in the form of two-dimensional tables. Illustrated here are tables for the entities SUPPLIER and PART showing how they represent each entity and its attributes. Supplier_Number is a primary key for the SUPPLIER table and a foreign key for the PART table.

In a relational database, three operations are used to develop sets of data:

• Select: Creates a subset of data of records that meet stated criteria
  
  
• Join: Combines relational data from different tables
  
  
• Project: Creates a subset of a table using only specified columns

Figure 6-5

FIGURE 6-5 THE THREE BASIC OPERATIONS OF A RELATIONAL DBMS The select, project, and join operations enable data from two different tables to be combined and only selected attributes to be displayed.

Older, less flexible types of database systems include hierarchical DBMS, which model one-to-many relationships, and network DBMS, which model many-to-many relationships. Hierarchical and network

Relational DBMS are suited for handling data, not graphics or multimedia. An object-oriented DBMS (OODBMS) stores the data and procedures that act on those data as objects that can be automatically retrieved and shared, and can manage multimedia and java applets. However, OODBMS are slower in handling large numbers of transactions. Hybrid object-relational DBMS systems are now available to provide capabilities of both object-oriented and relational DBMS.

A DBMS includes capabilities and tools for accessing and managing data in a database, including:

• Data definition language or capability: Used to specify the structure of the database content, creating and defining tables and fields
  
  
• Data dictionary: An automated or manual file that stores definitions of data elements and their characteristics
  
  
• Data manipulation language: a specialized language, such as Structured Query Language, or SQL, that is used to add, change, delete, and retrieve the data in the database

Figure 6-6, Figure 6-7, Figure 6-8

FIGURE 6-6 SAMPLE DATA DICTIONARY REPORT The sample data dictionary report for a human resources database provides helpful information, such as the size of the data element, which programs and reports use it, and which group in the organization is the owner responsible for maintaining it.

FIGURE 6-7 EXAMPLE OF AN SQL QUERY Illustrated here are the SQL statements for a query to select suppliers for parts 137 or 150. They produce a list with the same results as Figure 6-5.

FIGURE 6-8 AN ACCESS QUERY Illustrated here is how the query in Figure 6-7 would be constructed using query-building tools in the Access Query Design View. It shows the tables, fields, and selection criteria used for the query.

A DBMS may also include capabilities for generating customized reports and developing desktop system applications.

Considerations in designing a database include its:

• Physical design: How the database is arranged on storage devices
  
  
• Conceptual, or logical design: How the data elements are organized for efficiency, meeting information requirements, and minimizing redundancy
  
  

Normalization is the process of creating small, stable, yet flexible and adaptive data structures from complex groups of data and minimizes repeated data groups.

Figure 6-9, Figure 6-10

FIGURE 6-9 AN UNNORMALIZED RELATION FOR ORDER An unnormalized relation contains repeating groups. For example, there can be many parts and suppliers for each order. There is only a one-to-one correspondence between Order_Number and Order_Date.

FIGURE 6-10 NORMALIZED TABLES CREATED FROM ORDER After normalization, the original relation ORDER has been broken down into four smaller relations. The relation ORDER is left with only two attributes and the relation LINE_ITEM has a combined, or concatenated, key consisting of Order_Number and Part_Number.

An entity-relationship diagram is used to diagram a data model and describe the relationships between different groups of data in the system. Without an appropriate data model for its databases, a database system will not be able to serve a business effectively.

Figure 6-11

FIGURE 6-11 AN ENTITY-RELATIONSHIP DIAGRAM This diagram shows the relationships between the entities ORDER, LINE_ITEM, PART, and SUPPLIER that might be used to model the database in Figure 6-10.

Database design also considers how the data are to be distributed, with a centralized database or with a distributed database (data that is stored in more than one physical location). There are two main methods of distributing a database:

Partitioned: Different parts of the database are stored in separate locations

Replicated: The database is duplicated at all locations

Figure 6-12

FIGURE 6-12 DISTRIBUTED DATABASES There are alternative ways of distributing a database. The central database can be partitioned (a) so that each remote processor has the necessary data to serve its own local needs. The central database also can be replicated (b) at all remote locations.

Distributed systems:

• Reduce vulnerability
  
  
• Increase responsiveness
  
  
• May introduce unwanted data or definitions, or stray from standards
  
  
• Pose security problems