Database Management and Business Data

Introduction

Businesses depend on data to record transactions, understand customers, control resources and support decisions. A sale, employee record, supplier invoice or inventory movement is an individual fact; when such facts are organized and interpreted, they become business information. A database is a structured collection of related data, while a database management system (DBMS) is software used to create, store, retrieve, update, secure and administer that data.

For a business student, database knowledge is important for two reasons. First, managers must understand how operational data is represented and governed. Second, many business applications—including accounting, customer relationship management, enterprise resource planning and online commerce—depend on databases. Poor database design can produce duplicate records, inaccurate reports, privacy violations and costly operational errors.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish major data models and explain the relational model;
• identify entities, attributes, relationships and keys;
• design simple relational databases using normalization principles;
• explain database integrity, data quality and the data lifecycle;
• write and interpret fundamental SQL statements;
• evaluate database designs and data-governance decisions in business settings.

Data, Information and Database Systems

Data consists of recorded observations or facts, such as a product code, purchase date or invoice amount. Information is data processed into a meaningful context—for example, “sales of Product P increased by 12% in March.” A database stores data systematically so that authorized users and applications can access it consistently.

A DBMS commonly provides the following functions:

• Definition: creation of tables, fields, constraints and relationships.
• Manipulation: insertion, updating, deletion and retrieval of records.
• Transaction management: ensuring that related operations are completed reliably.
• Security: authentication, authorization, encryption and auditing.
• Backup and recovery: restoration after hardware failure, human error or cyberattack.
• Concurrency control: coordinating simultaneous users without corrupting data.

A database is different from a collection of unrelated spreadsheets. A well-designed database has defined structures, controlled relationships, rules for valid values and mechanisms for consistent access.

Data Models

A data model is a conceptual or technical representation of how data items relate to one another. Models help designers move from business requirements to an implementable database.

Hierarchical and Network Models

The hierarchical model organizes records in a tree of parent–child relationships. Each child generally has one parent. It can be efficient for naturally hierarchical data, such as an organizational chart, but it is inflexible when a record must relate to several parents.

The network model extends this idea by allowing records to participate in multiple relationships. It can represent complex structures efficiently, but its navigational links may be difficult to design and maintain. These models are less common in new business applications than relational and non-relational approaches.

Relational Model

The relational model represents data as relations, normally implemented as tables. A table contains rows, called tuples or records, and columns, called attributes or fields. Each table describes one subject or relationship. For example, a Customer table may contain CustomerID, CustomerName and Email.

The relational model is powerful because users can retrieve data based on logical conditions rather than following fixed navigation paths. Tables are connected through keys, and SQL is used to query and manipulate them.

Object-Oriented and NoSQL Models

Object-oriented databases represent data as objects containing both data and behavior. They are useful where applications handle complex objects, such as engineering designs or multimedia.

NoSQL is a broad category rather than one single model. Document databases store flexible document structures; key-value databases associate a key with a value; column-family databases organize data by columns; and graph databases emphasize nodes and relationships. NoSQL systems can support very large volumes, rapid scaling and semi-structured data. However, flexibility does not eliminate the need for data governance, validation or careful design.

For routine business transactions involving customers, orders, products and payments, the relational model remains a central choice because it supports strong integrity rules and well-defined transactions.

Relational Database Concepts

Consider a simplified sales database:

Customer(CustomerID, CustomerName, Email)

Product(ProductID, ProductName, UnitPrice)

Order(OrderID, CustomerID, OrderDate)

OrderLine(OrderID, ProductID, Quantity, SalePrice)

A relation is represented as a table. A record is one row, such as one customer. An attribute is a column, such as Email. A domain is the set of valid values for an attribute—for example, Quantity may be restricted to positive integers.

An order and a product have a many-to-many relationship: one order may contain several products, and one product may occur in many orders. The OrderLine table resolves this relationship into two one-to-many relationships. This is a general design pattern for relational databases.

Entities, Attributes and Relationships

An entity is a distinct real-world object or concept about which an organization stores data. Examples include Customer, Employee, Supplier, Product and Invoice. An entity type describes a category; an entity instance is one particular member of that category.

An attribute describes an entity. CustomerName and Email are attributes of Customer. Attributes should be atomic where practical: storing a complete address in one field may be less useful than separate Street, City and PostalCode fields if the business must search or analyze those components.

A relationship describes an association between entities. Cardinality specifies how many instances may participate:

• one-to-one: one employee may have one employment record;
• one-to-many: one customer may place many orders;
• many-to-many: many students may enroll in many courses.

Participation may be mandatory or optional. For example, every Order may be required to reference a Customer, whereas a Customer may have no orders yet.

Keys and Integrity Constraints

A key is an attribute or combination of attributes used to identify records or connect tables.

• A candidate key is a minimal attribute set that uniquely identifies a record.
• A primary key is the candidate key selected as the main identifier. It must be unique and should not be null.
• A composite key uses multiple attributes, such as (OrderID, ProductID) in OrderLine.
• A foreign key is an attribute that references a primary key in another table.
• A surrogate key is an artificial identifier, such as an automatically generated CustomerID, with no business meaning.
• A natural key is an identifier already meaningful in the business, such as an official registration number.

Primary and foreign keys enforce referential integrity. If Order.CustomerID references Customer.CustomerID, an order cannot normally refer to a customer that does not exist. The DBMS may also restrict deletion of a customer with existing orders, or apply a documented cascade rule.

Other constraints include:

• domain constraints, such as Quantity > 0;
• NOT NULL constraints, where a value is required;
• UNIQUE constraints, such as a unique email address where appropriate;
• CHECK constraints, such as valid status values;
• business rules, such as preventing shipment before payment authorization.

Integrity has several dimensions. Entity integrity requires valid primary keys. Referential integrity requires valid references. Domain integrity requires acceptable values. Business integrity applies organizational rules that may require triggers, application logic or periodic controls.

Database Design and Normalization

Database design begins with requirements analysis. The designer identifies business objects, required facts, transactions, reporting needs, users, security requirements and retention obligations. A conceptual entity–relationship model is then converted into relational tables.

Normalization is a systematic method for reducing unnecessary redundancy and preventing anomalies. It does not mean that every database must be maximally normalized; analytical systems may intentionally duplicate data for performance. However, transactional systems usually benefit from normalization.

First Normal Form

A relation is in First Normal Form (1NF) when each field contains one atomic value and repeating groups are removed. For example, storing Product1, Product2 and Product3 in an Order table violates 1NF. A separate OrderLine table is preferable.

Second Normal Form

A relation is in Second Normal Form (2NF) when it is in 1NF and every non-key attribute depends on the whole primary key, not merely part of a composite key. In OrderLine(OrderID, ProductID, Quantity, ProductName), ProductName depends only on ProductID, not on the whole composite key. ProductName therefore belongs in Product.

Third Normal Form

A relation is in Third Normal Form (3NF) when it is in 2NF and non-key attributes do not depend on other non-key attributes. Suppose Employee(EmployeeID, DepartmentID, DepartmentName) stores DepartmentName. DepartmentName depends on DepartmentID, not directly on EmployeeID. It should be placed in Department(DepartmentID, DepartmentName).

Normalization helps prevent three major anomalies:

• insertion anomaly: a department cannot be recorded until an employee exists;
• update anomaly: a department name must be changed in many rows;
• deletion anomaly: deleting the last employee unintentionally removes department information.

Normalization is based on functional dependency. If a value of attribute A determines exactly one value of B, this may be written as A → B. In Employee(EmployeeID, EmployeeName), EmployeeID → EmployeeName. The dependency must reflect business meaning, not merely current sample data.

Denormalization and Practical Judgment

Denormalization deliberately stores some repeated or derived data to improve reporting speed or simplify access. It can be justified in data warehouses or high-volume applications, but it increases update risk and storage redundancy. A designer should document the reason, define the authoritative source and establish controls to keep copies synchronized.

SQL Fundamentals

Structured Query Language (SQL) is the standard language family used with relational databases. Exact syntax varies by DBMS, but the following forms are widely supported.

Creating Tables

CREATE TABLE Customer (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE
);

CREATE TABLE OrderHeader (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER NOT NULL,
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

The table name OrderHeader is used because ORDER may be reserved in some systems. Data types should match the intended values. Monetary amounts should normally use a fixed-precision DECIMAL type rather than an approximate floating-point type.

Adding and Retrieving Data

INSERT INTO Customer (CustomerID, CustomerName, Email)
VALUES (101, 'Amina Khan', '[email protected]');

SELECT CustomerName, Email
FROM Customer
WHERE CustomerID = 101;

A query may sort and limit results:

SELECT ProductID, ProductName, UnitPrice
FROM Product
WHERE UnitPrice >= 100
ORDER BY UnitPrice DESC;

The asterisk in SELECT * requests all columns, but explicitly naming required columns is generally clearer and less fragile.

Joins and Aggregation

A join combines related rows using a matching condition:

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM Customer AS c
JOIN OrderHeader AS o
  ON c.CustomerID = o.CustomerID;

An INNER JOIN returns matching rows. A LEFT JOIN returns all rows from the left table, including those without a match—useful for finding customers who have not placed orders.

Aggregation summarizes data:

SELECT CustomerID, COUNT(*) AS NumberOfOrders
FROM OrderHeader
GROUP BY CustomerID
HAVING COUNT(*) >= 5;

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. NULL represents missing or unknown information, not zero or an empty string. Comparisons with NULL require IS NULL or IS NOT NULL.

Updating, Deleting and Transactions

UPDATE Customer
SET Email = '[email protected]'
WHERE CustomerID = 101;

DELETE FROM Customer
WHERE CustomerID = 101;

An UPDATE or DELETE without an appropriate WHERE clause can affect every row, so production systems should use authorization, testing and transaction controls.

A transaction is a logical unit of work. The ACID properties are:

• Atomicity: all operations occur or none do;
• Consistency: constraints remain satisfied;
• Isolation: concurrent transactions do not produce unacceptable interference;
• Durability: committed changes survive system failure.

For example, recording an order and reducing inventory should be handled as one controlled transaction where the business rule requires both operations to succeed together.

Data Quality and Data Lifecycle Management

Data quality is fitness for intended use. Important dimensions include:

Quality should be measured against business requirements. A missing optional middle name may be acceptable, while a missing tax identifier may prevent lawful invoicing.

The data lifecycle usually includes creation or acquisition, storage, use, sharing, maintenance, archival and disposal. At creation, organizations should define ownership, purpose, classification and retention requirements. During use, access should follow least privilege: users receive only the access necessary for their roles. During archival, records remain retrievable and protected. Disposal should be secure and documented when the retention period ends.

Metadata—data about data—supports lifecycle management. A data dictionary may record field definitions, formats, owners, permissible values, sensitivity and retention periods. Without metadata, different departments may interpret “customer,” “revenue” or “active account” differently.

Security, Privacy and Governance

Database security involves authentication, authorization, encryption, backups, monitoring and secure development. Role-based access can separate duties: a sales employee may view customer contact information, while only authorized finance staff may change payment data. Sensitive data should be collected only for legitimate purposes, retained no longer than necessary and protected from unauthorized disclosure.

Privacy regulations vary by jurisdiction but commonly emphasize transparency, lawful purpose, data minimization, access rights, correction and breach management. Organizations should identify applicable laws rather than assume that technical capability creates legal permission.

Governance assigns accountability. A data owner decides how data is used; a data steward manages definitions and quality; an administrator manages technical operation. Audit trails help determine who accessed or changed important records. AI and analytics increase the value of business data but also increase risks of biased outcomes, unauthorized reuse, re-identification and opaque decision-making. High-quality source data, documented lineage, human oversight and access controls are therefore essential.

Applied Case: GreenBasket Online Retailer

Background and Problem

GreenBasket is an illustrative online grocery retailer. Its initial spreadsheet stores customer details, product descriptions and order items in a single row. The same customer and product information is repeated across many rows. Managers report inconsistent addresses, duplicate products and difficulty identifying customers with no recent orders.

Analysis

The repeated structure violates 1NF and creates update anomalies. Product price changes must be entered in many rows. A customer cannot be recorded unless an order exists, and deleting an old order may remove the only copy of a customer’s contact details. The business also lacks a reliable key and allows free-text product categories.

Approach and Solution

The designer identifies Customer, Product, OrderHeader and OrderLine entities. CustomerID, ProductID and OrderID become primary keys. OrderLine uses the composite key (OrderID, ProductID), together with Quantity and SalePrice. Foreign keys connect OrderHeader to Customer and OrderLine to both OrderHeader and Product. A controlled category table and CHECK constraints standardize values.

The company creates a data dictionary, assigns a data steward, validates email and postal-code formats, and runs duplicate detection before migration. Historical records are retained according to a documented schedule. Sales staff receive limited access, while finance staff can access payment-related information. A nightly backup and tested recovery procedure are introduced.

Outcome and Learning Points

Reports become more reliable because each fact has an authoritative location. A LEFT JOIN can identify customers without orders, while aggregation can show order counts by customer or category. The case demonstrates that database design is not merely a technical task: it requires business-rule analysis, data-quality controls, security decisions and lifecycle governance.

Limitations and Best Practices

A relational database is not automatically the best solution. Very large event streams, highly variable documents or graph-intensive problems may require other models. Likewise, normalization cannot correct inaccurate source data, unclear definitions or poor organizational processes.

Best practices include:

1. begin with documented business requirements;
2. define entities and keys before writing SQL;
3. enforce rules in the database as well as in applications;
4. normalize transactional data and document justified denormalization;
5. use meaningful metadata and controlled vocabularies;
6. test queries, constraints, backups and recovery procedures;
7. protect personal and confidential data throughout its lifecycle;
8. monitor quality continuously rather than treating migration as a one-time exercise.

Chapter Summary

Databases organize business facts for reliable operational use and analysis. Data models provide alternative ways to represent relationships; the relational model uses tables connected by keys and queried through SQL. Entities represent business objects, attributes describe them, and relationships express associations with defined cardinality. Primary, candidate, composite, surrogate and foreign keys support identification and referential integrity.

Normalization separates facts into appropriate tables and reduces insertion, update and deletion anomalies. The first three normal forms address atomic values, partial dependencies and transitive dependencies. SQL supports table creation, insertion, retrieval, joins, aggregation, updating and deletion. Transactions provide ACID reliability. Effective database management also requires attention to accuracy, completeness, validity, consistency, timeliness and uniqueness. Finally, security, privacy, metadata, governance, retention and secure disposal ensure that data remains useful and responsible throughout its lifecycle.

Key Terms

Data: Recorded facts or observations.

Information: Data processed into meaningful context.

Database: Structured collection of related data.

DBMS: Software that manages databases.

Data model: Representation of data structures and relationships.

Relational model: Model that represents data in related tables.

Entity: Distinct object or concept about which data is stored.

Attribute: Property describing an entity.

Relationship: Association between entities.

Primary key: Selected unique identifier for a table record.

Candidate key: Minimal attribute set capable of unique identification.

Composite key: Key formed from multiple attributes.

Foreign key: Attribute referencing a key in another table.

Referential integrity: Requirement that references point to valid records.

Normalization: Process of reducing redundancy and dependency problems.

SQL: Language used to define, manipulate and query relational data.

Transaction: Logical unit of database work.

Data quality: Fitness of data for its intended use.

Metadata: Data describing the meaning, structure or management of data.

Data lifecycle: Stages from data creation through use, retention and disposal.

Review Questions

Short Answer Questions

1. What is the difference between a database and a DBMS?
2. Define a primary key and a foreign key.
3. What problem does a junction table solve?
4. State the main purpose of normalization.
5. Why is NULL different from zero in a database?

Long Answer Questions

1. Explain the relational model and illustrate how entities, attributes, relationships and cardinality are represented.
2. Describe 1NF, 2NF and 3NF using suitable business examples.
3. Explain the ACID properties and their importance in business transactions.
4. Discuss the major dimensions of data quality and methods for improving them.
5. Explain how database security, privacy and lifecycle governance work together.

Analytical / Application Questions

1. A retail table contains CustomerName, CustomerAddress, Product1, Product2 and Product3 in each row. Identify its design problems and propose normalized tables.
2. Design tables, primary keys and foreign keys for a university system in which students enroll in many courses and each course has many students.
3. Write SQL conceptually or syntactically to identify customers who have placed no orders, and explain why your chosen join is appropriate.

Multiple Choice Questions with Answers

Question 1

Which database model represents data primarily as related tables?

A. Hierarchical model

B. Relational model

C. Network model

D. Object-oriented model

Correct Answer: B. Relational model

Question 2

What is the main purpose of a primary key?

A. To store repeated descriptive text

B. To sort every table alphabetically

C. To uniquely identify each record

D. To encrypt the database

Correct Answer: C. To uniquely identify each record

Question 3

Which table normally resolves a many-to-many relationship between orders and products?

A. Customer

B. ProductCategory

C. OrderLine

D. Employee

Correct Answer: C. OrderLine

Question 4

A table is in First Normal Form when each field contains:

A. Only numerical values

B. An atomic value rather than a repeating group

C. A foreign key

D. A calculated value

Correct Answer: B. An atomic value rather than a repeating group

Question 5

Which SQL clause filters groups after aggregation?

A. WHERE

B. ORDER BY

C. FROM

D. HAVING

Correct Answer: D. HAVING

Question 6

What does a foreign key primarily support?

A. Referential integrity

B. Data encryption

C. Automatic normalization

D. Password recovery

Correct Answer: A. Referential integrity

Question 7

Which join returns all rows from the left table, including rows without a match?

A. INNER JOIN

B. LEFT JOIN

C. CROSS JOIN only

D. SELF JOIN only

Correct Answer: B. LEFT JOIN

Question 8

Which ACID property means that a committed change survives system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 9

Which is an example of a data-quality dimension?

A. Completeness

B. Inheritance

C. Polymorphism

D. Compilation

Correct Answer: A. Completeness

Question 10

Why should monetary values generally use fixed-precision decimal types?

A. They prevent all unauthorized access

B. They represent currency amounts more predictably than approximate floating-point values

C. They automatically create foreign keys

D. They eliminate the need for validation

Correct Answer: B. They represent currency amounts more predictably than approximate floating-point values