Learning Objectives
By the end of this chapter, you will be able to:
- Define the database approach and the role of a Database Management System (DBMS).
- Explain the key advantages of the database approach, such as minimizing redundancy and improving security.
- Describe the basic components of the relational database model (tables, rows, columns, keys).
- Understand the purpose of a data model and an Entity-Relationship Diagram (ERD).
The Database Approach: A Single Source of Truth
The database approach was developed to overcome the significant problems of the traditional file-based approach. It involves creating a centralized repository of data that can be managed efficiently and shared across multiple applications and departments within an organization.
Figure 1: The Database Approach
flowchart TB
subgraph APPS["Applications"]
SALES["📊 Sales App"]
ACCT["💰 Accounting"]
MKT["📣 Marketing"]
HR["👥 HR"]
end
APPS <--> DBMS["🛡️ DBMS\nDatabase Management System"]
DBMS <--> DB[("🗃️ Centralized\nDatabase")]
DB --> TRUTH["✅ Single Source of Truth"]
style DB fill:#1565c0,color:#fff
style TRUTH fill:#2d5016,color:#fff
Figure 2: Centralized Database Architecture
At the heart of this approach are two key components: the database and the Database Management System (DBMS).
- Database: A logically coherent collection of related data, designed to provide a shared and organized source of information.
- Database Management System (DBMS): A software application that acts as the interface between the users/applications and the database itself. The DBMS is responsible for creating, managing, and protecting the data. Examples include MySQL, Oracle Database, Microsoft SQL Server, and PostgreSQL.
Advantages of the Database Approach
The database approach directly addresses the flaws of the file-based system:
-
Minimizes Data Redundancy and Inconsistency: By centralizing data storage, the database approach aims to create a single, authoritative version of the data (a “single source of truth”). When a piece of data like a customer’s address is updated in one place, it is immediately available to all applications that have permission to access it. This greatly reduces inconsistency.
-
Improves Data Sharing and Integration: With data stored in a central location, it is much easier for different departments and applications to share information. This provides a more holistic and integrated view of the organization’s operations.
-
Increases Data Security: A DBMS provides a sophisticated security framework. Administrators can define precise access controls, granting specific users permission to only read, create, update, or delete certain data. This is a major improvement over the scattered and difficult-to-secure nature of separate data files.
-
Enforces Data Standards and Integrity: The DBMS can enforce rules and constraints on the data, ensuring its integrity. For example, a rule can be set to ensure that an
order_quantityfield can only contain a positive number, preventing data entry errors.
The Relational Database Model
The most common type of database model is the relational model. In this model, data is organized into tables (also called relations).
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_ITEM : contains
PRODUCT ||--o{ ORDER_ITEM : "is in"
CUSTOMER {
int CustomerID PK
string Name
string Email
string Address
}
ORDER {
int OrderID PK
int CustomerID FK
date OrderDate
decimal Total
}
PRODUCT {
int ProductID PK
string ProductName
decimal Price
}
ORDER_ITEM {
int OrderID FK
int ProductID FK
int Quantity
}
Figure 3: Entity-Relationship Diagram (ERD) Example
- Tables: Consist of rows and columns.
- Rows (Records): Represent a single instance of an entity (e.g., one customer, one product).
- Columns (Fields/Attributes): Represent a characteristic of that entity (e.g.,
customer_name,product_price).
Tables are linked together using keys:
- Primary Key: A unique identifier for each row in a table (e.g.,
CustomerID). No two rows in a table can have the same primary key. - Foreign Key: A primary key from one table that is included in another table to create a link between them.
Database Design: Data Models and ERDs
Before a database is built, it must be designed. This is done using a data model, which is an abstract diagram that illustrates the structure of the data and the relationships between different entities.
A common tool for creating a data model is the Entity-Relationship Diagram (ERD). An ERD visually represents:
- Entities: The things we want to store data about (e.g.,
Customer,Product,Order). - Attributes: The properties of those entities (e.g.,
CustomerName,ProductPrice). - Relationships: How the entities are connected (e.g., a
Customerplaces anOrder).
Summary
The database approach provides a robust solution to the problems of traditional file systems by creating a centralized, shared repository of data managed by a DBMS. This approach minimizes data redundancy and inconsistency, improves security, and allows for better data sharing. The most popular implementation of this approach is the relational model, which organizes data into linked tables and is designed using tools like Entity-Relationship Diagrams.
Key Takeaways
- The database approach uses a central database and a DBMS to manage data.
- It creates a “single source of truth,” solving the problems of redundancy, inconsistency, and isolation.
- The relational model organizes data into tables linked by primary and foreign keys.
- ERDs are used to design the structure of a database before it is built.
Discussion Questions
- Explain in your own words what a “single source of truth” is and why it is important.
- What is the role of a DBMS? Why can’t applications just access the database directly?
- Consider a simple university database. What would be the main entities you would need to model in an ERD?
