Learning Objectives
By the end of this lesson, learners should be able to:
- Define a database and explain its role in business analytics.
- Distinguish between databases, database management systems and data warehouses.
- Explain tables, records, fields and attributes.
- Explain primary keys and their importance.
- Distinguish structured, semi-structured and unstructured data.
- Explain how databases support business operations and analytics.
- Identify important database quality and governance considerations.
- Evaluate database structures from a business analyst’s perspective.
1. Introduction to Databases
Modern organizations generate large volumes of data through:
- Sales transactions.
- Customer interactions.
- Financial systems.
- Inventory systems.
- Human resource systems.
- Websites.
- Mobile applications.
- Supply-chain systems.
A database provides an organized mechanism for storing and retrieving data.
For a business analyst, understanding databases is important because analytical results depend heavily on how organizational data is structured, stored and accessed.
2. What Is a Database?
A database is an organized collection of data designed to allow information to be stored, accessed, managed and updated efficiently.
For example, a retail business may maintain information about:
- Customers.
- Products.
- Orders.
- Payments.
- Suppliers.
Rather than maintaining completely independent files for every transaction, the organization can store related information in a structured database.
3. Database Management Systems
A Database Management System (DBMS) is software used to create, manage, access and control databases.
Examples include:
- MySQL.
- PostgreSQL.
- Microsoft SQL Server.
- Oracle Database.
- SQLite.
A DBMS can provide capabilities such as:
- Data storage.
- Query processing.
- User access control.
- Transaction management.
- Backup and recovery.
- Data integrity enforcement.
4. Database Versus DBMS
These concepts should not be confused.
Database
The organized collection of data.
DBMS
The software used to manage and interact with the database.
For example:
A company’s customer records constitute the database, while MySQL may provide the DBMS used to manage those records.
5. Database Users
Different users interact with databases for different purposes.
Database Administrators
Responsible for:
- Database availability.
- Security.
- Performance.
- Backup.
- Recovery.
- User permissions.
Developers
Build applications that interact with databases.
Business Analysts
Typically use databases to:
- Retrieve information.
- Analyze business performance.
- Identify patterns.
- Produce reports.
- Support decisions.
Executives
Usually consume the information generated from analytical systems rather than directly managing database structures.
6. Tables
In a relational database, data is commonly organized into tables.
For example, a Customers table might contain:
|
Customer_ID |
Customer_Name |
Region |
Customer_Type |
|
101 |
Amina |
Nairobi |
Retail |
|
102 |
Brian |
Kiambu |
Corporate |
|
103 |
David |
Kisumu |
Retail |
A table generally represents a particular entity or subject.
7. Records
A record, often called a row, represents one instance of an entity.
In the example above:
Customer 101
represents one customer record.
A table may therefore contain thousands or millions of records.
8. Fields and Attributes
A field, commonly represented by a column, stores a particular type of information.
For example:
- Customer_ID
- Customer_Name
- Region
- Customer_Type
In database terminology, these can also be referred to as attributes.
Each attribute should represent a clearly defined characteristic.
9. Data Types
Database fields are generally assigned appropriate data types.
Common types include:
- Integer.
- Decimal.
- Character/string.
- Date.
- Time.
- Boolean.
Choosing an appropriate data type is important because it affects:
- Storage.
- Validation.
- Calculations.
- Query behavior.
- Data quality.
For example, storing a transaction date as a proper date field is preferable to storing it as arbitrary text.
10. Primary Keys
A primary key is a field, or combination of fields, that uniquely identifies each record in a table.
For example:
Customer_ID
could serve as the primary key of a customer table.
A valid primary key should provide unique identification of records.
11. Why Primary Keys Matter
Primary keys help:
- Prevent duplicate identities.
- Identify individual records.
- Establish relationships between tables.
- Support efficient data retrieval.
- Maintain data integrity.
Suppose two customers have the same name:
John Mwangi
A name alone may not uniquely identify them.
A unique customer ID can distinguish them.
12. Foreign Keys
A foreign key is a field used to establish a relationship between tables.
For example:
Customers
Customer_ID
Orders
Order_ID
Customer_ID
The Customer_ID in the Orders table can reference the primary key in the Customers table.
This allows analysts to connect orders to customers.
13. Relationships Between Data
Common relational relationships include:
One-to-One
One record corresponds to one record in another table.
One-to-Many
One record can correspond to many records.
Example:
One customer → many orders.
Many-to-Many
Many records in one entity relate to many records in another.
This usually requires an intermediary or junction table.
Example:
Students ↔ Courses.
A student may take multiple courses, while each course may have many students.
14. Relational Databases
A relational database organizes data into related tables.
The relationships between tables allow organizations to avoid unnecessarily duplicating information.
For example:
Instead of storing the customer’s full details on every order, an order may store only the customer’s identifier and obtain the remaining information from the Customers table.
15. Structured Data
Structured data follows a predefined organizational structure.
Examples include:
- Customer tables.
- Sales transactions.
- Payroll records.
- Inventory tables.
Structured data is particularly suitable for relational databases and SQL-based analysis.
16. Semi-Structured Data
Semi-structured data does not follow a rigid relational table structure but contains organizational elements such as tags or keys.
Examples include:
- JSON.
- XML.
Modern business analysts may encounter semi-structured data when working with:
- APIs.
- Web applications.
- Cloud systems.
- Application logs.
17. Unstructured Data
Unstructured data does not naturally conform to a fixed tabular structure.
Examples include:
- Images.
- Videos.
- Audio.
- Free-form documents.
- Some forms of social media content.
Organizations increasingly analyze such information using specialized technologies.
18. Operational Databases
An operational database supports day-to-day business activities.
Examples include systems that process:
- Sales.
- Payments.
- Orders.
- Customer registrations.
- Inventory movements.
Operational databases are generally optimized for frequent transactions and updates.
19. Analytical Databases
Analytical environments are designed primarily for querying and analyzing data.
They may contain:
- Historical data.
- Integrated information from multiple systems.
- Aggregated information.
The structure and optimization priorities may differ from those of operational databases.
20. Database Versus Data Warehouse
A traditional operational database primarily supports business transactions.
A data warehouse is designed primarily to support analytical reporting and decision-making.
For example:
Operational database:
Record today’s customer purchase.
Data warehouse:
Analyze five years of customer purchasing patterns.
Organizations may use both systems simultaneously.
21. Data Integrity
Data integrity refers to the accuracy, consistency and reliability of data throughout its lifecycle.
Problems can arise when:
- Records are duplicated.
- Values are incorrectly entered.
- Relationships are broken.
- Data is inconsistently defined.
Database constraints and validation procedures help protect integrity.
22. Data Quality
Important dimensions of data quality include:
Accuracy
Does the data correctly represent reality?
Completeness
Is required information present?
Consistency
Does information agree across systems?
Timeliness
Is the information sufficiently current?
Validity
Does the data conform to required rules?
Uniqueness
Are duplicate records appropriately controlled?
Poor data quality can undermine otherwise sophisticated analysis.
23. Database Security
Organizations must protect databases against:
- Unauthorized access.
- Accidental modification.
- Data theft.
- Destruction.
- Improper disclosure.
Security mechanisms may include:
- Authentication.
- Authorization.
- Encryption.
- Access controls.
- Audit logs.
A business analyst should understand that access to business data should be governed according to organizational policies and responsibilities.
24. Database Transactions
A transaction is a logical unit of work performed against a database.
For example:
A customer payment may involve:
- Recording the payment.
- Updating the customer’s balance.
- Updating the transaction record.
These operations may need to be treated as one coherent transaction.
25. ACID Properties
Database transactions are commonly associated with four ACID properties:
Atomicity
A transaction is treated as an indivisible unit.
Consistency
The database moves from one valid state to another.
Isolation
Concurrent transactions should not improperly interfere with one another.
Durability
Once a transaction is successfully committed, its changes should persist.
These properties are especially important in systems such as banking and payment processing.
26. Database Normalization
Normalization is a database design approach used to reduce unnecessary data duplication and improve structural integrity.
For example, storing customer information repeatedly in every order record can create update problems.
Normalization helps separate related information into appropriate tables.
However, analytical systems may sometimes use deliberately denormalized structures to improve query performance.
Therefore, database design depends partly on the system’s purpose.
27. Business Analyst’s Role
A business analyst does not necessarily need to become a database administrator.
However, the analyst should understand:
- Where data comes from.
- How entities are related.
- What fields mean.
- Which identifiers are reliable.
- How data is updated.
- What limitations exist.
- Whether the data is appropriate for analysis.
This allows the analyst to ask better questions and avoid incorrect conclusions.
28. Example: Retail Database
A simplified retail database might contain:
Customers
Customer_ID, Name, Region
Products
Product_ID, Product_Name, Category, Price
Orders
Order_ID, Customer_ID, Order_Date
Order_Details
Order_ID, Product_ID, Quantity
These tables can be connected to answer questions such as:
- Which customers generate the most revenue?
- Which products sell most frequently?
- Which regions have the highest sales?
- What products are commonly purchased together?
Lesson Summary
Databases provide structured environments for storing and managing organizational information.
Business analysts should understand:
- Databases and DBMSs.
- Tables, records and fields.
- Primary and foreign keys.
- Relationships.
- Data types.
- Data quality.
- Data integrity.
- Operational and analytical databases.
- Data warehouses.
- Database security.
- Transactions.
- Normalization.
Understanding these concepts provides the foundation for using SQL effectively in business analytics.
References
- MySQL Documentation
- PostgreSQL Documentation
- Microsoft SQL Server Documentation