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
|
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
|
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
|
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
|
|
|
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
|
|
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
|
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
|
Distributed systems:
- Reduce vulnerability
- Increase responsiveness
- May introduce unwanted data or definitions, or stray from standards
- Pose security problems