Database Management and SQL

Introduction and Context

Organisations in banking, finance and insurance depend on reliable information. A bank must know which customers hold accounts, how much money has been deposited, which transactions have occurred, and whether a loan application satisfies policy requirements. An insurer must maintain policy, claimant, premium and risk information. These activities require more than electronic files: they require a structured system for storing, updating, protecting and retrieving data.

A database is an organised collection of related data, while a database management system (DBMS) is software used to define, create, manipulate, secure and administer that database. Examples include Microsoft Access, MySQL, PostgreSQL, Oracle Database and Microsoft SQL Server. SQL (Structured Query Language) is the principal language used with relational databases.

This chapter develops the conceptual and practical foundations needed to design a small business database and retrieve information using introductory SQL.

Learning Objectives

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

• distinguish data, information, databases and DBMSs;
• explain tables, records, fields, entities, attributes and domains;
• identify primary, candidate, alternate and foreign keys;
• model one-to-one, one-to-many and many-to-many relationships;
• apply normalisation to reduce redundancy and update anomalies;
• explain data integrity, validation, security and governance;
• create tables, queries, forms and reports in a database application;
• write introductory SQL statements for business information retrieval; and
• evaluate database solutions in banking, finance and insurance contexts.

1. Database Foundations

Data, information and databases

Data consists of recorded facts, such as a customer number, transaction date or loan balance. Information is data processed into a form that supports understanding or decision-making. For example, a list of transactions is data; a report showing monthly withdrawals by branch is information.

A database stores related data in a manner that supports controlled access and efficient retrieval. A DBMS provides facilities for:

1. defining structures such as tables and constraints;
2. inserting, updating and deleting records;
3. querying data;
4. controlling concurrent access by multiple users;
5. enforcing integrity and security;
6. backing up and recovering data; and
7. monitoring performance and usage.

A spreadsheet can be useful for personal analysis, but it becomes difficult to control when many users edit the same data, when relationships exist between datasets, or when auditability and security are important. A DBMS is designed for these conditions.

Database terminology

A table is a collection of related rows and columns. A row, also called a record or tuple, represents one occurrence of an entity. A column, also called a field or attribute, represents a property of that entity. For example, a Customer table may contain CustomerID, FullName, DateOfBirth and RiskRating.

An entity is a distinguishable object or event about which data is stored. Customers, accounts, policies, claims and payments are common business entities. An attribute describes an entity. A customer may have a name, address and telephone number. An attribute's domain is the set of permitted values, such as dates in a valid date format or risk ratings from a defined set.

A schema is the logical design of a database: its tables, attributes, relationships and constraints. An instance is the actual data stored at a particular time. A DBMS separates the logical design from physical storage, allowing storage technology to change without changing users' views of the data.

2. The Relational Model

The relational model represents data as relations, commonly implemented as tables. Each relation has a unique name and a set of attributes. In a properly designed relational table, each cell contains one value, rows are distinguishable, and each attribute has a consistent meaning.

Consider these simplified relations:

CUSTOMER (CustomerID, FullName, Email, RiskRating)

ACCOUNT (AccountID, CustomerID, AccountType, OpenDate, Balance)

CustomerID identifies a customer in CUSTOMER. In ACCOUNT, CustomerID links an account to its owner. The repeated CustomerID value is not unnecessary duplication; it is a controlled reference that establishes a relationship.

The relational model supports operations such as selection, projection and joining:

• Selection retrieves rows satisfying a condition, such as accounts with balances greater than 10,000.
• Projection retrieves selected columns, such as AccountID and Balance.
• Join combines related rows from two or more tables using matching values.
• Union combines compatible result sets.

SQL expresses these operations in a practical form.

Keys

A superkey is any attribute or combination of attributes that uniquely identifies a row. A candidate key is a minimal superkey: no attribute can be removed without losing uniqueness. A table may have more than one candidate key.

A primary key is the candidate key selected as the main identifier. It must be unique and normally cannot be NULL. A surrogate key is an artificial identifier, such as an automatically generated CustomerID. A natural key has real-world meaning, such as a national identification number, although it may be unsuitable if it can change or has privacy implications.

An alternate key is a candidate key not selected as the primary key. A foreign key is an attribute in one table that references a primary or candidate key in another table. Foreign keys enforce referential integrity: an account cannot normally refer to a customer who does not exist.

Relationships and cardinality

A relationship describes how entities are associated. Cardinality specifies how many instances may participate.

• In a one-to-one relationship, one entity instance relates to at most one instance of another entity. For example, a customer may have one secure online profile, depending on system design.
• In a one-to-many relationship, one parent may relate to many children. One customer may hold several accounts, while each account belongs to one customer.
• In a many-to-many relationship, many instances on each side may be associated. Customers may hold several products, and each product may be held by many customers.

Many-to-many relationships require an associative table. For example, CUSTOMER and PRODUCT can be linked through CUSTOMER_PRODUCT, containing CustomerID, ProductID, StartDate and Status.

Figure: A foreign key implements a one-to-many relationship between CUSTOMER and ACCOUNT.

3. Database Design and Normalisation

Database design translates business requirements into a reliable schema. A sound design begins with requirements analysis: identify users, business processes, entities, attributes, transactions, reports, security needs and retention obligations. An entity–relationship (ER) model then represents entities, attributes and relationships before implementation.

Why normalisation is needed

Poorly designed tables often store the same fact repeatedly. Repetition creates:

• Update anomaly: a fact must be changed in several rows, and inconsistent values may result.
• Insertion anomaly: a fact cannot be recorded until an unrelated fact is available.
• Deletion anomaly: deleting one fact accidentally removes another valuable fact.

Normalisation is a systematic method of organising relations to reduce unnecessary redundancy while preserving meaningful dependencies.

First normal form (1NF)

A relation is in 1NF when each field contains one atomic value rather than a list or repeating group. A Customer table should not contain a single PhoneNumbers field containing “020..., 071..., 078...”. If several telephone numbers must be stored, use a related CUSTOMER_PHONE table with one phone number per row.

Second normal form (2NF)

A relation is in 2NF when it is in 1NF and every non-key attribute depends on the whole primary key. This matters mainly when a composite key is used. In an ORDER_LINE table with primary key (OrderID, ProductID), ProductName depends only on ProductID, not on the whole key, so ProductName belongs in PRODUCT.

Third normal form (3NF)

A relation is in 3NF when it is in 2NF and non-key attributes do not depend on other non-key attributes. For example, if ACCOUNT contains BranchID and BranchAddress, and BranchAddress depends on BranchID, BranchAddress should be stored in BRANCH. This reduces inconsistent branch addresses.

Normalisation does not mean that every possible repetition is forbidden. In analytical systems, controlled denormalisation may improve reporting performance. Such decisions should be documented, tested and governed because they increase the risk of inconsistent data.

A normalisation example

Suppose a transaction sheet contains:

TransactionID, TransactionDate, CustomerID, CustomerName, AccountID, BranchID, BranchName, Amount.

CustomerName depends on CustomerID, and BranchName depends on BranchID. A more appropriate design is:

• CUSTOMER(CustomerID, CustomerName)
• BRANCH(BranchID, BranchName)
• ACCOUNT(AccountID, CustomerID, BranchID)
• TRANSACTION(TransactionID, AccountID, TransactionDate, Amount)

The design stores each major fact once and connects it through keys.

4. Data Integrity, Quality and Governance

Entity integrity requires every row to have a valid, non-null primary key. Referential integrity requires a foreign-key value either to match a referenced key or, where permitted, to be NULL. Domain integrity requires values to satisfy definitions such as data type, range, format and permitted categories.

Business rules add further constraints. For example:

• an account opening date cannot be later than the current date;
• a transaction amount must be positive for a deposit and negative for a withdrawal, depending on the chosen representation;
• a claim cannot be approved before it is submitted; and
• an insurance policy's end date cannot precede its start date.

Validation may occur at the user interface, application, database or multiple layers. Database constraints are particularly important because they protect data even when several applications access the same database.

Data quality has dimensions including accuracy, completeness, consistency, timeliness, validity and uniqueness. A database can be technically well designed yet produce poor decisions if customer addresses are obsolete or transactions are missing.

Banking and insurance data is sensitive. Appropriate controls include role-based access, least privilege, strong authentication, encryption in transit and at rest, audit logs, backup testing, retention schedules, segregation of duties and incident-response procedures. Regulatory requirements vary by jurisdiction, but financial institutions commonly face obligations concerning privacy, anti-money-laundering controls, customer identification, record retention and operational resilience. Staff should never copy confidential production data into unsecured spreadsheets or use real customer data for classroom testing.

5. Creating Tables and Database Objects

A database application commonly provides four object types:

• Tables store data.
• Queries retrieve, calculate or modify data.
• Forms provide controlled screens for entering and viewing data.
• Reports present formatted information for management, customers or regulators.

A table design should specify field name, data type, required status, default value, validation rule and key status. Common data types include integer, decimal, character/text, date/time, Boolean and currency. Monetary values should use an appropriate fixed-precision decimal or currency type rather than an approximate floating-point type, because rounding errors can be significant in financial records.

An illustrative SQL definition is:

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

CREATE TABLE Account (
    AccountID INTEGER PRIMARY KEY,
    CustomerID INTEGER NOT NULL,
    AccountType VARCHAR(30) NOT NULL,
    OpenDate DATE NOT NULL,
    Balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    CHECK (Balance >= 0)
);

SQL syntax varies slightly among DBMS products. A CHECK constraint may not express every business rule, and a simple non-negative balance rule may not suit overdraft products. Requirements must therefore be confirmed before implementation.

Forms can enforce required fields, provide drop-down lists and reduce accidental editing. A form should not replace database constraints. Reports should identify their data source, reporting period, preparation date and relevant filters so that users can interpret results correctly.

6. Introductory SQL for Business Retrieval

SQL is declarative: the user states what result is required, while the DBMS determines an execution strategy. SQL keywords are conventionally written in uppercase, although many systems are not case-sensitive.

Selecting and filtering data

SELECT AccountID, AccountType, Balance
FROM Account
WHERE Balance > 10000
ORDER BY Balance DESC;

This query selects three columns, filters rows above 10,000, and sorts the result from highest to lowest balance. Text values require quotation marks, while numeric values do not.

SELECT CustomerID, FullName
FROM Customer
WHERE RiskRating IN ('High', 'Medium')
  AND FullName LIKE 'A%';

LIKE uses pattern matching; in many DBMSs, % represents zero or more characters. NULL means unknown or missing, not zero or an empty string. Therefore, use IS NULL rather than = NULL.

SELECT AccountID
FROM Account
WHERE OpenDate BETWEEN '2024-01-01' AND '2024-12-31';

Date literal syntax differs across products, so production systems should use parameterised queries and DBMS-approved date formats.

Calculations and aggregation

SQL can calculate expressions and summarise groups.

SELECT AccountType,
       COUNT(*) AS NumberOfAccounts,
       SUM(Balance) AS TotalBalance,
       AVG(Balance) AS AverageBalance
FROM Account
GROUP BY AccountType
HAVING SUM(Balance) > 100000;

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. COUNT, SUM and AVG handle NULL values according to DBMS rules, so the treatment of missing values should be understood before interpreting results.

Joining tables

SELECT c.CustomerID, c.FullName, a.AccountID, a.Balance
FROM Customer AS c
INNER JOIN Account AS a
    ON c.CustomerID = a.CustomerID
WHERE a.Balance > 5000;

An INNER JOIN returns only matching rows. A LEFT JOIN returns every row from the left table and matching rows from the right table; unmatched right-side columns are NULL.

SELECT c.CustomerID, c.FullName, a.AccountID
FROM Customer AS c
LEFT JOIN Account AS a
    ON c.CustomerID = a.CustomerID
WHERE a.AccountID IS NULL;

This retrieves customers with no account. Joins should use appropriate keys; joining on names can produce incorrect matches because names may be duplicated or spelled differently.

Inserting, updating and deleting

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

UPDATE Customer
SET RiskRating = 'Medium'
WHERE CustomerID = 101;

A missing WHERE clause in an UPDATE or DELETE statement can affect every row. For important changes, use transactions, test the selection with a SELECT statement first, and ensure that authorised users alone can modify production data.

A transaction groups related operations so that they succeed or fail as a unit. The familiar ACID properties are atomicity, consistency, isolation and durability. For example, transferring money should not debit one account while failing to credit the other. A DBMS transaction may use COMMIT to make changes permanent or ROLLBACK to undo uncommitted changes. Exact behaviour depends on configuration and transaction isolation.

Parameterisation and security

Applications should use parameterised queries rather than concatenating user input into SQL strings. This reduces SQL injection risk and improves separation between code and data. Database accounts should receive only the permissions required for their functions. Views can expose selected columns or rows without granting direct access to underlying tables.

7. Applied Case: Illustrative Community Bank Database

Background

This illustrative case concerns Riverside Community Bank, which wants a dashboard showing customer accounts, balances and branch performance. Its existing spreadsheet contains repeated customer and branch information, inconsistent account types and manual monthly totals.

Problem

Managers cannot reliably identify dormant accounts or compare balances by branch. A correction to a branch name must be made in many spreadsheet rows. The bank also wants customer service staff to view account information without seeing sensitive risk-management fields.

Analysis

Requirements analysis identifies CUSTOMER, BRANCH, ACCOUNT and TRANSACTION as core entities. A customer may own many accounts; a branch manages many accounts; an account has many transactions. The design uses primary keys and foreign keys and separates branch data from account data.

Approach and solution

The bank creates the following schema:

• CUSTOMER(CustomerID, FullName, RiskRating)
• BRANCH(BranchID, BranchName, Region)
• ACCOUNT(AccountID, CustomerID, BranchID, AccountType, OpenDate, Status)
• TRANSACTION(TransactionID, AccountID, TransactionDate, Amount, TransactionType)

A monthly balance report can aggregate transactions by account and branch. A customer-service view exposes CustomerID, FullName, AccountID, AccountType and Status but omits RiskRating. Forms validate account type and status through controlled lists. Reports show reporting date and user-generated filters. Audit logs record changes to customer and account information.

Outcome

The revised design reduces duplicate branch data, improves consistency and enables repeatable queries. Managers can identify accounts with no transactions during a defined period, while customer-service staff receive only the information needed for their duties. The bank still needs reconciliation procedures, backup tests, access reviews and data-quality monitoring; a database does not automatically guarantee correct business processes.

Learning points

The case demonstrates that database work is both technical and organisational. Good outcomes depend on requirements, modelling, normalisation, SQL, security, controls and user training. A fast query is not useful if it answers the wrong business question or uses incomplete data.

8. Challenges, Limitations and Contemporary Developments

Database projects may fail because requirements are ambiguous, legacy data is inconsistent, users resist new procedures, or performance is assessed only after deployment. Normalisation can increase the number of joins, while excessive denormalisation can create inconsistency. Large organisations may use data warehouses, data lakes or distributed databases alongside operational relational systems.

Cloud databases can provide scalability and managed backup, but they introduce supplier dependency, configuration risk, jurisdictional concerns and shared-responsibility obligations. Business intelligence tools allow non-technical users to explore data, but poorly defined metrics can produce contradictory reports.

Artificial intelligence can assist with anomaly detection, document classification and natural-language query interfaces. However, AI-generated SQL must be reviewed for incorrect joins, unsafe updates, privacy violations and misleading interpretations. Sensitive financial decisions require explainability, human oversight, bias monitoring and auditability. AI should augment, not replace, sound data governance and professional judgement.

Best practice includes documenting definitions, assigning data owners, enforcing constraints, using test data, parameterising queries, reviewing permissions, monitoring quality, maintaining backups, testing recovery, recording changes and periodically validating reports against trusted source records.

Chapter Summary

A database is an organised collection of related data, and a DBMS provides facilities for storing, querying, protecting and administering it. The relational model represents business facts in tables connected through keys. Entities and attributes describe the subject matter; primary keys identify rows, while foreign keys establish relationships and support referential integrity.

Effective design considers cardinality, normalisation and business rules. First, second and third normal forms reduce repeating groups, partial dependencies and transitive dependencies. Integrity includes entity, referential and domain integrity, supported by validation, constraints, security and governance.

Tables store data, queries retrieve or manipulate it, forms support controlled interaction, and reports communicate results. SQL uses SELECT, WHERE, JOIN, GROUP BY, HAVING, INSERT, UPDATE and other commands to answer business questions. Parameterised queries, transactions, least privilege and auditability are essential in financial environments. Finally, database quality depends not only on technology but also on requirements, data stewardship, controls and ethical use.

Key Terms

Database: An organised collection of related data.

DBMS: Software that defines, stores, manipulates, secures and administers databases.

Table: A relational structure of rows and columns.

Record: A row representing one occurrence of an entity.

Attribute: A property or column describing an entity.

Entity: A distinguishable object, person, event or concept about which data is stored.

Schema: The logical structure of a database.

Primary key: The selected unique identifier for rows in a table.

Candidate key: A minimal attribute set capable of uniquely identifying a row.

Foreign key: An attribute referencing a key in another table.

Referential integrity: The requirement that foreign-key references remain valid.

Cardinality: The permitted number of entity instances participating in a relationship.

Normalisation: The systematic organisation of relations to reduce redundancy and anomalies.

SQL: Structured Query Language used to define, retrieve and manipulate relational data.

Query: A request for data or an operation on stored data.

Transaction: A logical unit of database work.

ACID: Atomicity, consistency, isolation and durability properties of transactions.

Data integrity: The accuracy, validity, consistency and reliability of stored data.

Parameterised query: A query that supplies user values separately from SQL code.

Review Questions

Short Answer Questions

1. Distinguish a database from a DBMS.
2. What is the purpose of a primary key?
3. Explain the difference between a one-to-many and a many-to-many relationship.
4. What problem does third normal form address?
5. Why should IS NULL be used instead of = NULL in SQL?

Long Answer Questions

1. Explain the relational model and discuss how primary and foreign keys support business data design.
2. Describe the first three normal forms and illustrate each with a banking example.
3. Compare tables, queries, forms and reports in a business database application.
4. Explain entity, referential and domain integrity and identify controls that support each.
5. Discuss the ACID properties and explain why they matter in a financial transaction system.

Analytical / Application Questions

1. A bank stores CustomerName, BranchName and BranchAddress in every transaction row. Analyse the likely anomalies and propose a normalised design.
2. Write an SQL query that lists each branch and the total balance of its active accounts, showing only branches whose total exceeds 1,000,000.
3. A customer-service application displays risk ratings to all employees. Evaluate the governance risks and recommend database and process controls.

Multiple Choice Questions with Answers

Question 1

Which database object uniquely identifies each row in a table?

A. Primary key

B. Foreign key

C. Validation rule

D. Report

Correct Answer: A. Primary key

Question 2

A foreign key primarily supports which form of integrity?

A. Entity integrity

B. Referential integrity

C. Visual integrity

D. Arithmetic integrity

Correct Answer: B. Referential integrity

Question 3

Which relationship normally requires an associative table?

A. One-to-one

B. One-to-many

C. Many-to-many

D. Unary one-to-one only

Correct Answer: C. Many-to-many

Question 4

Which SQL clause filters individual rows before grouping?

A. HAVING

B. ORDER BY

C. WHERE

D. GROUP BY

Correct Answer: C. WHERE

Question 5

Which SQL condition correctly tests for a missing value?

A. = NULL

B. IS NULL

C. NULL = 0

D. LIKE NULL

Correct Answer: B. IS NULL

Question 6

What is the main purpose of normalisation?

A. To increase duplicate data

B. To reduce redundancy and update anomalies

C. To remove all foreign keys

D. To eliminate the need for validation

Correct Answer: B. To reduce redundancy and update anomalies

Question 7

Which SQL operation combines related rows from two tables?

A. JOIN

B. SORT

C. FORMAT

D. INDEXONLY

Correct Answer: A. JOIN

Question 8

Which ACID property ensures that a transaction is treated as an indivisible unit?

A. Durability

B. Isolation

C. Atomicity

D. Consistency

Correct Answer: C. Atomicity

Question 9

Which practice best reduces SQL injection risk?

A. Concatenating user input into SQL

B. Using parameterised queries

C. Removing all constraints

D. Giving every user administrator rights

Correct Answer: B. Using parameterised queries

Question 10

In a one-to-many relationship between CUSTOMER and ACCOUNT, where is the foreign key normally stored?

A. Only in CUSTOMER

B. In ACCOUNT, referencing CUSTOMER

C. In neither table

D. Only in a report

Correct Answer: B. In ACCOUNT, referencing CUSTOMER