Unit 8: Data Base Management Systems

DECAP145 8 min read

A database management system solves a single recurring problem: how to store large volumes of related data so that many users and programs can retrieve, update, and share it reliably without duplication or corruption. This unit moves from the container (the database), to the software that governs it (the DBMS), to the operations performed on it, to real deployments, and finally to the commercial products in wide corporate use.

I. Foundations

The field rests on a hierarchy that turns raw symbols into shared, structured information. Everything in later sections is defined against these building blocks.

Data hierarchy: the ladder from smallest to largest unit of stored data.
- Bit and byte: a bit is a binary 0/1; eight bits form a byte, encoding one character (e.g. A).
- Field: a single attribute, such as LastName or Salary; the smallest meaningful unit.
- Record: a group of related fields describing one entity, e.g. one employee's row.
- File (table): a collection of related records of the same type, e.g. all employees.
- Database: an integrated collection of related files sharing common definitions.
Data versus information: data are raw facts (37); information is data processed into a meaningful, useful form (average age = 37 years).
Key defining goals: minimise redundancy, enforce data integrity, allow controlled sharing, and provide data independence — programs need not change when storage details change.

II. The Database

The organised, integrated store of related data

A database is a self-describing collection of integrated records, meaning it holds both the data and a description of its own structure.

A. Structure and models

Relational model: data held in two-dimensional tables (relations) of rows and columns; dominant model since the 1980s (proposed by E.F. Codd, 1970).
Hierarchical model: records arranged in a parent-child tree; each child has one parent. Fast but rigid.
Network model: extends the hierarchy so a child may have several parents, via linked sets.
Object-oriented model: stores objects (data plus methods), suited to multimedia and complex types.

B. Keys and relationships

Keys are the fields that identify rows and link tables, which is what makes a database "relational".

Primary key: a field (or combination) uniquely identifying each record, e.g. EmployeeID. No duplicates, no nulls.
Foreign key: a field in one table matching the primary key of another, creating the link.
- Example: an Orders table stores CustomerID as a foreign key pointing to the Customers table.
Referential integrity: the rule that every foreign key value must match an existing primary key, preventing orphan records.

C. Schema and metadata

Schema: the overall logical design — the tables, fields, data types, and relationships.
Metadata: "data about data"; field names, lengths, types, and constraints stored in the data dictionary.
Data independence: because the schema is separate from the data, structure can change without rewriting applications.

III. The DBMS

The software layer between users and stored data

A DBMS is a software system that creates, maintains, and controls access to a database, sitting between the physical files and the people or programs that use them.

A. Core functions

Data definition: defining tables, fields, and relationships through a Data Definition Language (DDL).
Data manipulation: inserting, querying, updating, and deleting through a Data Manipulation Language (DML).
Data storage management: deciding how data is physically stored and indexed for speed.
Concurrency control: letting many users access data simultaneously without conflict, using record locking.
Backup and recovery: restoring the database to a consistent state after failure via logs and checkpoints.

B. The data dictionary

Definition: a central catalogue holding all metadata about the database.
Contents: field definitions, table structures, user permissions, and validation rules.
Use: the DBMS consults it on every operation to enforce types and constraints.

C. Users and views

The same database presents differently to different people, controlled by the DBMS.

Database administrator (DBA): designs the schema, sets security, tunes performance, and manages backups.
End users and programmers: access only a view — a tailored subset of the database — so payroll staff see salary fields while others do not.

D. Advantages and limitations

Advantages: reduced redundancy, improved integrity, shared access, centralised security, and data independence.
Limitations: high cost of software and hardware, complexity requiring skilled staff, and vulnerability — a single failure can affect all applications relying on the database.

IV. Working with a Database

Creating, querying, and maintaining the data

Working with a database means the full cycle of designing the structure, entering data, retrieving it through queries, and producing output.

A. Creating tables and entering data

Table design: specify each field's name, data type (text, number, date, currency), and size.
Validation rules: constraints checked on entry, e.g. Age must be between 18 and 65.
Forms: on-screen layouts that guide data entry into one record at a time.

B. Querying with SQL

Structured Query Language (SQL) is the standard language for retrieving and manipulating relational data.

SELECT: retrieves rows meeting a condition.
WHERE, ORDER BY: filter and sort the result set.
Aggregates: COUNT, SUM, AVG summarise groups of rows.

SQL

SELECT LastName, Salary
FROM Employees
WHERE Department = 'Sales'
ORDER BY Salary DESC;

Reads: list the surname and salary of every Sales employee, highest salary first.

C. Sorting, filtering, and reports

Sorting: arranging records by a field, ascending or descending.
Filtering (query by example): showing only records matching criteria without writing SQL.
Reports: formatted, printable output that groups records and computes subtotals, e.g. total sales per region.

D. Maintaining integrity

Updating and deleting: UPDATE changes existing values; DELETE removes rows, subject to referential integrity.
Transactions: a group of operations treated as one unit — all succeed or all roll back (e.g. a bank transfer debiting one account and crediting another).
Indexing: building an index on a field to speed searches, at the cost of slower inserts.

V. Databases at Work

How organisations apply databases in practice

Databases underpin most day-to-day computing in business, government, and science, usually invisibly behind an application.

A. Everyday applications

Transaction processing: point-of-sale systems, ATMs, and airline reservations record events as they happen.
Inventory and ordering: stock levels update automatically as sales post, triggering reorders.
Customer records: banks, hospitals, and utilities store account and history data keyed by customer ID.

B. Analytical uses

Beyond daily transactions, databases feed decision-making.

Data warehouse: a large store consolidating data from many operational databases for analysis, not daily updates.
Data mining: software sifts the warehouse for patterns, e.g. which products sell together, supporting cross-selling.
OLAP (online analytical processing): multidimensional queries such as sales by region by quarter.

C. Distributed and web databases

Distributed database: data physically split across several sites but appearing as one, e.g. a chain with regional servers.
Web-enabled databases: power e-commerce catalogues and search, with the web page querying a back-end database in real time.
Security concern: databases exposed to networks require authentication, encryption, and access controls, since a breach exposes bulk sensitive data.

VI. Common Corporate Database Management Systems

The commercial products in wide use

Large organisations rely on a small set of established DBMS products, chosen for scale, reliability, and vendor support.

A. Enterprise relational systems

Oracle Database: high-end relational DBMS favoured for very large, mission-critical systems; strong scalability and clustering.
Microsoft SQL Server: relational DBMS integrated with Windows and Microsoft tools; common in mid-to-large enterprises.
IBM Db2: relational system rooted in mainframe environments, valued for stability and transaction volume.

B. Open-source systems

MySQL: free, fast, widely used behind websites and small-to-medium applications; part of the common web stack.
PostgreSQL: open-source system emphasising standards compliance and advanced features such as complex data types.

C. Desktop and workgroup systems

Microsoft Access: file-based DBMS for individuals and small workgroups, combining tables, queries, forms, and reports in one file.
Scope: suited to smaller data volumes and few simultaneous users, not enterprise-scale concurrency.

D. Choosing a corporate DBMS

The selection balances several practical factors.

Scale and concurrency: number of records and simultaneous users the system must support.
Cost: licensing and hardware for Oracle or Db2 versus free open-source options.
Integration and support: compatibility with existing tools and availability of vendor support and skilled administrators.
Security and compliance: built-in encryption, auditing, and access control needed for regulated data such as financial or health records.