Databases, Business Data and Analytics Foundations

Introduction

Modern organizations depend on data to operate, control costs, serve customers and make decisions. A retailer records sales transactions, a bank stores account and payment information, a manufacturer monitors inventory and production, and a university maintains student, course and assessment records. These activities generate large volumes of data, but data becomes useful only when it is accurately captured, properly organized, securely managed and meaningfully interpreted.

A database is an organized collection of related data managed so that users and applications can store, retrieve, update and analyze information efficiently. Database systems are therefore fundamental components of business information systems. They connect operational activities with managerial reporting and analytics.

This chapter introduces database concepts, data models, tables, keys and queries. It then distinguishes transactional systems from analytical systems and explains how data quality, visualization and basic analytics support managerial decisions. The emphasis is on concepts that business students can apply when evaluating information systems and interpreting organizational data.

Learning Objectives

After studying this chapter, you should be able to:

1. Explain the meaning and purpose of databases and database management systems.
2. Describe relational data models, tables, records, fields and relationships.
3. Distinguish primary keys, foreign keys and other important constraints.
4. Explain basic database queries and interpret their business use.
5. Compare transactional processing systems with analytical systems.
6. Assess data quality and identify common quality problems.
7. Select suitable visualizations and basic analytical measures for managerial decisions.
8. Recognize ethical, security and governance responsibilities associated with business data.

1. From Business Data to Information

Data consists of recorded observations, facts or measurements. Examples include a product code, an invoice date, a quantity sold or a customer rating. Information is data processed and interpreted so that it has meaning for a particular purpose. For example, a list of sales transactions is data; a report showing that sales of a product declined by 12% in the southern region is information.

The value of information depends on several qualities:

• Accuracy: It correctly represents the object or event being described.
• Completeness: Required values are present.
• Consistency: The same item is represented similarly across systems.
• Timeliness: It is available when needed and reflects the relevant period.
• Relevance: It supports the decision or task at hand.
• Validity: It conforms to defined rules, formats and permitted values.
• Accessibility: Authorized users can obtain it in a usable form.

A database helps preserve these qualities by applying structures, rules and controlled procedures. However, a database cannot automatically transform poor source data into reliable information. If employees enter incorrect customer addresses or if two systems use different product codes, the resulting reports may be misleading.

2. Databases and Database Management Systems

A database is a structured collection of related data. A database management system (DBMS) is software that defines, creates, stores, controls and provides access to databases. Examples of DBMS products include relational systems such as PostgreSQL, MySQL, Microsoft SQL Server and Oracle Database. The product is not the database itself; it is the software used to manage databases.

A DBMS generally provides the following capabilities:

• creation of data structures;
• insertion, modification and deletion of records;
• searching and reporting;
• enforcement of rules and relationships;
• user authentication and access control;
• backup and recovery;
• support for simultaneous users; and
• monitoring and administration.

A database system includes more than software. It also includes data, hardware, procedures, people and applications. A sales application may send a request to a DBMS, which validates the request, retrieves the required records and returns results to the application.

Database versus spreadsheet

Spreadsheets are useful for personal analysis, small data collections and flexible calculations. Databases are generally preferable when data is shared by many users, updated frequently, related across several categories or subject to strict security and integrity requirements.

For example, a spreadsheet may be adequate for a manager’s monthly budget. It is less suitable as the central system for thousands of customers and millions of sales transactions because duplicated values, accidental overwriting, inconsistent formats and weak access controls become significant risks.

3. The Relational Data Model

The relational model represents data as relations, commonly implemented as tables. A table describes one type of business object or event. A row, also called a record or tuple, represents one instance. A column, also called a field or attribute, represents a property of those instances.

Consider a simplified CUSTOMER table:

Here, CustomerID, CustomerName, City and CreditStatus are attributes. Each row describes one customer. The table should have a defined meaning: it stores customer entities, not arbitrary mixtures of customers, products and transactions.

A database schema is the logical design of a database: its tables, columns, data types, relationships and constraints. A data type specifies what kind of value a field may contain, such as integer, decimal, date, Boolean value or text. Data types prevent or reduce invalid entries. For example, a quantity field should not accept a word such as “many.”

Relationships among tables

Business data is often divided into multiple related tables. A SALES_ORDER table may identify the customer and order date, while an ORDER_LINE table records the products and quantities within each order. A PRODUCT table stores product descriptions and prices.

Common relationship types include:

• One-to-one: Each record in one table corresponds to at most one record in another table.
• One-to-many: One record in a table relates to many records in another table. One customer may place many orders.
• Many-to-many: Many records in each table may relate to many records in the other. Many products may appear on many orders. This is usually implemented through an intermediate junction table such as ORDER_LINE.

A relational design separates facts into suitable tables and connects them through keys. This reduces unnecessary duplication and makes updates more reliable.

Normalization as a design principle

Normalization is a set of design principles used to reduce inappropriate data duplication and update anomalies. In a simplified explanation, a well-designed table stores facts about one subject and avoids placing repeated groups of values in a single field.

Suppose an order record contains Product1, Quantity1, Product2 and Quantity2. This design becomes difficult when an order contains more products. A better design stores one order in ORDER_HEADER and one product occurrence per row in ORDER_LINE.

Normalization does not mean that every database must contain the maximum possible number of tables. Excessive separation can make queries complicated and may reduce reporting performance. Designers balance integrity, clarity and performance according to business requirements.

4. Keys, Integrity and Constraints

Keys identify records and establish relationships.

Primary key

A primary key is a field, or combination of fields, that uniquely identifies each row in a table. It must be unique and normally cannot be empty. CustomerID is a suitable primary key for CUSTOMER. A primary key may be a natural key, such as a government-issued identifier, or a surrogate key generated specifically for database use, such as an integer CustomerID.

A composite primary key contains more than one field. In ORDER_LINE, the combination of OrderID and ProductID may identify one product occurrence within an order, although a separate LineNumber is also commonly used.

Foreign key

A foreign key is a field in one table that refers to a primary key in another table. For example, CustomerID in SALES_ORDER may be a foreign key referring to CustomerID in CUSTOMER. It establishes a relationship and supports referential integrity: an order should not refer to a customer who does not exist.

Other constraints

• NOT NULL: A value must be supplied.
• UNIQUE: No two records may contain the same value in the constrained field.
• CHECK: A value must satisfy a rule, such as Quantity > 0.
• DEFAULT: A value is supplied automatically when no value is entered.
• Data-type constraint: A value must use the permitted type and format.

Constraints are important because they place controls close to the data rather than relying only on user discipline. Nevertheless, constraints must reflect actual business rules. A rule that is too restrictive may reject legitimate transactions.

5. Queries and Structured Data Access

A query is a request for data or an operation on data. In relational databases, SQL—Structured Query Language—is widely used. SQL syntax differs slightly among DBMS products, but the main concepts are transferable.

A basic query can select particular columns:

SELECT CustomerID, CustomerName, City
FROM CUSTOMER;

A WHERE clause filters rows:

SELECT OrderID, OrderDate, CustomerID
FROM SALES_ORDER
WHERE OrderDate >= '2025-01-01';

A query can sort results:

SELECT ProductID, ProductName, UnitPrice
FROM PRODUCT
ORDER BY UnitPrice DESC;

Queries can combine related tables using a JOIN:

SELECT o.OrderID, c.CustomerName, o.OrderDate
FROM SALES_ORDER AS o
JOIN CUSTOMER AS c
  ON o.CustomerID = c.CustomerID;

The JOIN condition connects matching key values. Without a correct condition, the query may produce an unintended Cartesian product in which every order is paired with every customer.

Aggregation for business questions

Managers often need summaries rather than individual records. SQL aggregation functions include COUNT, SUM, AVG, MIN and MAX. For example:

SELECT CustomerID, SUM(OrderTotal) AS TotalSpent
FROM SALES_ORDER
GROUP BY CustomerID;

This produces one result per customer. A HAVING clause filters groups after aggregation:

SELECT CustomerID, SUM(OrderTotal) AS TotalSpent
FROM SALES_ORDER
GROUP BY CustomerID
HAVING SUM(OrderTotal) > 10000;

A query must be interpreted carefully. A total can be wrong if duplicate rows exist, if canceled orders are included or if the amount field has inconsistent units. Technical correctness of SQL does not guarantee business correctness.

6. Transactional and Analytical Systems

Transactional systems

A transactional system, often called an online transaction processing (OLTP) system, supports routine operational activities such as sales, payments, bookings, stock movements and payroll. It is optimized for many short, frequent operations that must be accurate and completed promptly.

Important transactional principles are often summarized by ACID:

• Atomicity: A transaction is completed as a whole or not completed at all.
• Consistency: A completed transaction leaves the database in a state that obeys defined rules.
• Isolation: Concurrent transactions do not improperly interfere with one another.
• Durability: Once committed, the result is preserved despite an ordinary system failure.

For example, transferring money between two accounts requires both the withdrawal and deposit to succeed together. If only the withdrawal occurs, the system has violated the business requirement. Transaction management helps prevent this outcome.

OLTP databases commonly use normalized structures to reduce duplication and support frequent updates. Their queries are often narrow, such as finding one customer account or recording one sale.

Analytical systems

Analytical systems support examination of historical and current data for reporting, performance management, forecasting and decision-making. They are often called online analytical processing (OLAP) systems, data warehouses or business intelligence platforms, depending on their architecture.

Analytical queries may scan millions of records and compare dimensions such as time, product, geography, channel and customer segment. They usually prioritize complex reading and aggregation over rapid individual updates. Data may be extracted from several operational systems, transformed and loaded into an analytical repository. This process is commonly called ETL: extract, transform and load. Modern platforms may also use ELT, in which data is loaded before transformation within the target platform.

A data warehouse is a subject-oriented, integrated, relatively stable and time-aware collection designed for analysis. A data mart serves a particular function or department. Analytical models often use a fact table containing measurable events and dimension tables describing those events. In a sales model, sales amount and quantity may be facts, while date, product, store and customer segment are dimensions.

Comparison

The two system types are complementary. An organization should not normally force heavy analytical queries onto a critical order-entry database because this may slow operational work. Instead, controlled data movement can provide analytical access while protecting operational performance.

7. Data Quality and Governance

Data quality is the degree to which data is fit for its intended use. Quality is not absolute: a postal code may be adequate for regional planning but inadequate for exact delivery routing.

Common data-quality problems include:

• missing customer contact details;
• duplicate customer records;
• inconsistent spellings such as “Nairobi” and “NBI”;
• invalid dates or negative quantities;
• obsolete records that have not been updated;
• conflicting values across systems; and
• unclear definitions, such as different meanings of “active customer.”

A practical data-quality process includes profiling, validation, cleansing, monitoring and accountability. Data profiling examines patterns such as missing-value rates, distinct-value counts and unusual ranges. Validation applies rules at entry or import. Cleansing corrects, standardizes or removes problematic records. Monitoring checks whether quality improves or deteriorates over time.

A useful measurement is a completeness rate:

For example, if 9,600 of 10,000 customer records contain a required email address, the completeness rate is 96%. This measure is meaningful only when the field is genuinely required and the population has been defined correctly.

Data governance assigns responsibilities and rules for data ownership, definitions, access, retention, quality and use. A data steward may be responsible for the meaning and quality of customer data, while an information-security team controls technical safeguards. Governance helps prevent a situation in which everyone uses data but nobody is accountable for it.

8. Basic Analytics for Managerial Decisions

Analytics involves examining data to understand conditions and support action. A widely used classification is:

• Descriptive analytics: What happened? Example: monthly revenue by region.
• Diagnostic analytics: Why did it happen? Example: investigating whether a decline resulted from stock-outs or lower demand.
• Predictive analytics: What may happen? Example: estimating next month’s demand.
• Prescriptive analytics: What action should be considered? Example: recommending inventory levels under specified constraints.

At an introductory level, managers should understand the difference between a measure and an interpretation. A mean sales value is a calculation; concluding that a market is attractive requires context, comparison and assumptions.

Basic numerical measures

For values x₁, x₂, …, xₙ, the arithmetic mean is:

Here x̄ is the mean, xᵢ is an observed value and n is the number of observations. The mean is useful for balanced numerical data but can be strongly affected by extreme values.

The median is the middle value after sorting observations. If there are two middle values, the median is their arithmetic mean. The median is often more suitable for skewed data such as income or delivery time.

A percentage change from an old value to a new value is:

This formula assumes the old value is nonzero and that the comparison uses consistent definitions and units.

Worked example: Monthly sales rose from $80,000 to $92,000. The change is $12,000. Percentage change = ($12,000 ÷ $80,000) × 100% = 15%. The result describes change in sales, not necessarily change in profit, because costs may also have changed.

Correlation describes the strength and direction of a linear association between two numerical variables. It does not prove that one variable causes the other. For example, advertising expenditure and sales may move together, but both may also be influenced by season, promotions or market growth.

9. Visualization and Communication

Visualization converts data into graphical form to reveal comparisons, patterns, trends and exceptions. The visual form must match the analytical question.

Good visualizations use clear titles, labeled axes, appropriate units, readable scales and restrained color. A truncated vertical axis can exaggerate small differences in a bar chart. Three-dimensional effects, unnecessary decoration and excessive categories can obscure the message.

A dashboard should present a limited set of relevant indicators rather than every available measure. Each key performance indicator should have a defined calculation, data source, reporting period, target and responsible owner. Managers should be able to move from a high-level indicator to underlying details when investigation is required.

10. Applied Case: Retail Inventory and Sales Decisions

Background

This illustrative case concerns Northstar Retail, a chain with 18 stores. Its sales system records transactions, while a separate procurement system records supplier deliveries. Managers complain that weekly reports disagree and that popular products are sometimes unavailable.

Problem

The sales report counts canceled transactions as sales in some stores. Product codes are also entered differently in the two systems. Consequently, the organization cannot reliably determine true demand or stock availability.

Analysis

The information team profiles the data and finds duplicate product codes, missing delivery dates and inconsistent definitions of “available stock.” It identifies the sales database as an OLTP system and recommends that operational queries remain there. A separate analytical repository will combine validated sales, inventory and delivery data.

The team creates a PRODUCT master table with a unique ProductID. The sales and procurement tables use ProductID as a foreign key. Validation rules prevent negative quantities and require valid dates. Canceled transactions are assigned a status and excluded from net-sales measures rather than physically deleted.

An analytical model contains a sales fact table and dimensions for date, store and product. A dashboard displays weekly sales, stock-out frequency, inventory turnover and gross margin. A line chart shows trends, while a bar chart compares stores. Managers can filter by product category and investigate a sudden decline.

Outcome

After implementation, the organization obtains a single agreed definition of net sales and stock availability. Managers discover that some apparent demand declines were actually caused by stock-outs. Procurement schedules are adjusted for high-demand products, while slow-moving products receive targeted promotions.

Learning points

1. Data quality problems can create managerial problems even when reports appear professional.
2. Master data and keys are essential when several systems share business entities.
3. Operational and analytical workloads should be designed for different purposes.
4. Visualization supports decisions only when measures and definitions are trustworthy.
5. A dashboard should lead to investigation and action, not merely display attractive graphics.

11. Security, Ethics and Responsible Use

Business data may include personal, financial or commercially sensitive information. Responsible data management requires collecting only data needed for legitimate purposes, informing individuals where appropriate, restricting access and retaining information only as long as justified by policy or law.

Important controls include authentication, role-based authorization, encryption, audit logs, secure backups and tested recovery procedures. The principle of least privilege gives users only the access necessary for their responsibilities. A sales employee may need customer contact details but not payroll records.

Organizations must also consider fairness and bias. If historical lending data reflects discriminatory practices, an analytical model trained on that data may reproduce unfair outcomes. Managers should ask who is represented, who may be excluded, how variables were defined and whether automated recommendations can be challenged.

Privacy and data-protection obligations differ by jurisdiction, but common governance themes include lawful collection, purpose limitation, transparency, accuracy, security and accountability. Compliance should not be treated as a substitute for ethical judgment. A technically legal use of data may still damage trust or create unreasonable harm.

12. Contemporary Developments and Best Practices

Cloud databases provide scalable storage and computing through networked services. Organizations may combine relational databases with data lakes or lakehouse platforms that store structured and less-structured data. Application programming interfaces allow systems to exchange data, while automated pipelines support frequent updates to analytical platforms.

Artificial intelligence and machine learning can identify patterns, classify records, forecast demand and support recommendations. Their usefulness depends on data quality, representative training data, suitable evaluation and human oversight. AI-generated outputs should be treated as analytical evidence requiring review, not as unquestionable facts.

Practical best practices include:

• define business terms before building reports;
• assign data owners and stewards;
• use stable identifiers and documented relationships;
• validate data at entry and during integration;
• separate operational workloads from intensive analytics when appropriate;
• document assumptions, filters and calculation rules;
• secure sensitive information through least-privilege access;
• test backups and recovery procedures;
• visualize uncertainty and limitations honestly; and
• review whether data use remains appropriate as business conditions change.

Chapter Summary

Databases organize related data so that it can be stored, controlled, retrieved and analyzed. A DBMS provides software services for managing data, enforcing constraints, supporting concurrent users and protecting information. In the relational model, tables contain rows and columns, while primary keys uniquely identify records and foreign keys connect related tables. Constraints improve validity and referential integrity, and normalization reduces unsuitable duplication.

Queries, commonly written in SQL, retrieve, filter, join and summarize data. Transactional systems support accurate, frequent operational updates and commonly rely on ACID properties. Analytical systems integrate historical data for reporting, comparison, forecasting and decision support. Data quality—especially accuracy, completeness, consistency, timeliness, validity and relevance—is essential to both systems.

Descriptive, diagnostic, predictive and prescriptive analytics address progressively more interpretive questions. Measures such as means, medians and percentage changes must be interpreted in context. Effective visualizations match graphical forms to analytical questions and communicate results without distortion. Finally, data governance, security, privacy, fairness and human oversight are necessary for responsible business use of databases and analytics.

Key Terms

Database: An organized collection of related data.

Database management system (DBMS): Software used to create, manage, secure and access databases.

Relational model: A data model that represents information through related tables.

Table: A structured collection of rows and columns representing a subject or event.

Record: A row representing one instance of an entity or event.

Field: A column representing an attribute.

Schema: The logical design of tables, fields, relationships and constraints.

Primary key: A unique identifier for a table record.

Foreign key: A field that refers to a key in another table.

Referential integrity: The requirement that relationships between related records remain valid.

Normalization: Design principles that reduce inappropriate duplication and update anomalies.

Query: A request to retrieve or manipulate data.

SQL: Structured Query Language used widely with relational databases.

OLTP: Online transaction processing supporting routine operational activities.

OLAP: Online analytical processing supporting complex analysis and reporting.

Data warehouse: An integrated, historical repository designed for analysis.

Data quality: The fitness of data for its intended use.

Data governance: Rules, responsibilities and processes for managing data.

Dashboard: A visual display of selected indicators and measures.

Correlation: A statistical association between variables that does not by itself establish causation.

Review Questions

Short Answer Questions

1. What is the difference between a database and a DBMS?
2. What roles do primary keys and foreign keys perform?
3. Define referential integrity and give one example.
4. State two differences between OLTP and analytical systems.
5. Why can a technically correct query still produce a misleading business conclusion?

Long Answer Questions

1. Explain the relational data model using tables, records, fields, keys and relationships.
2. Discuss normalization and explain how it can reduce update anomalies.
3. Compare transactional systems and analytical systems in terms of purpose, users, data structure and workload.
4. Explain the major dimensions of data quality and describe a practical data-quality improvement process.
5. Discuss how visualization and basic analytics can support managerial decision-making while avoiding misleading interpretations.

Analytical / Application Questions

1. A company stores customer details, products and multiple order items in one spreadsheet row. Design a relational structure that would improve integrity, and identify likely primary and foreign keys.
2. A manager reports that revenue increased by 20% but profit declined. What additional data, queries and visualizations would you use to investigate the result?
3. An automated credit-screening model rejects applicants from one region at a much higher rate than others. Analyze the data-quality, fairness, governance and managerial actions that should be considered.

Multiple Choice Questions with Answers

Question 1

Which database component uniquely identifies each record in a table?

A. Foreign key

B. Primary key

C. Query

D. Data type

Correct Answer: B. Primary key

Question 2

What is the main purpose of a foreign key?

A. To sort records alphabetically

B. To store passwords

C. To connect a record to a related table

D. To calculate an average

Correct Answer: C. To connect a record to a related table

Question 3

Which system is primarily designed to record daily sales transactions?

A. OLTP system

B. Data warehouse

C. OLAP dashboard

D. Data visualization system

Correct Answer: A. OLTP system

Question 4

Which SQL clause filters individual rows before grouping?

A. ORDER BY

B. HAVING

C. WHERE

D. JOIN

Correct Answer: C. WHERE

Question 5

A report shows 9,600 complete email values out of 10,000 required values. What is the completeness rate?

A. 9.6%

B. 40%

C. 94%

D. 96%

Correct Answer: D. 96%

Question 6

Which visualization is generally most suitable for showing revenue trends over twelve months?

A. Line chart

B. Pie chart

C. Scatter plot

D. Network diagram

Correct Answer: A. Line chart

Question 7

Which statement about correlation is correct?

A. It always proves causation

B. It measures only categorical data

C. It can indicate association without proving causation

D. It eliminates the need for data-quality checks

Correct Answer: C. It can indicate association without proving causation

Question 8

Which ACID property means that a committed transaction remains preserved after an ordinary system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 9

Which analytical question is diagnostic?

A. What were total sales last month?

B. Why did sales decline in the northern region?

C. What might sales be next month?

D. What action should be recommended?

Correct Answer: B. Why did sales decline in the northern region?

Question 10

If sales rise from $80,000 to $92,000, what is the percentage change?

A. 10%

B. 12%

C. 15%

D. 20%

Correct Answer: C. 15%