Database Foundations and Terminology

Introduction

Modern organisations depend on data to record events, coordinate work, serve customers, comply with regulations, and make decisions. A university may store student admissions, enrolment, assessment, and fee information. An online retailer records products, customers, orders, payments, and deliveries. A hospital maintains patient histories, appointments, diagnoses, and prescriptions. In each case, data must be stored systematically and retrieved accurately.

A database is more than a collection of files. It is an organised representation of related data, managed according to defined rules. A database management system (DBMS) provides the software needed to define, create, maintain, secure, and use databases. Understanding database foundations is essential before learning SQL, relational modelling, application development, or database administration.

This chapter introduces the central concepts and terminology used in database management. It also explains the roles of database users, the difference between a database and a DBMS, the meaning of schemas and instances, metadata, data independence, and the advantages and limitations of database systems.

Learning Objectives

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

• distinguish data from information;
• define a database and a DBMS;
• describe the major components of a database environment;
• explain schemas, instances, metadata, and data independence;
• identify common database users and their responsibilities;
• describe the principal advantages and limitations of database systems; and
• apply foundational terminology to a practical organisational scenario.

1. Data and Information

Data consists of recorded facts, observations, symbols, or measurements. Examples include a student number, a date of birth, a product price, or the value 42. Data may be numeric, textual, visual, audio, spatial, or temporal. By itself, a data item may have limited meaning. The number “42” is ambiguous unless its context is known.

Information is data that has been processed, organised, interpreted, or presented so that it has meaning and supports understanding or action. For example, “42 students passed the database examination” is information because the value has context and communicates a result.

The distinction is not absolute. Data for one person may be information for another, depending on context and purpose. A transaction record is data in an operational system but may become information in a management report.

Useful information normally has several qualities:

• Accuracy: it correctly represents the recorded reality.
• Completeness: important required values are present.
• Consistency: the same fact does not conflict across records or systems.
• Timeliness: it is available when needed and reflects an appropriate point in time.
• Relevance: it supports the task or decision at hand.
• Accessibility: authorised users can obtain it in a usable form.

Database design contributes to these qualities, but cannot guarantee them by itself. Incorrect data entered by users may remain incorrect even when it is stored in a technically well-designed database.

2. What Is a Database?

A database is an organised collection of logically related data, together with descriptions of the data and, commonly, rules governing its use. The data is structured so that it can be stored, searched, updated, shared, and protected efficiently.

A database may contain entities such as customers, products, and orders. In a relational database, these entities are commonly represented as tables. A customer table may contain one row per customer and columns such as CustomerID, Name, Email, and RegistrationDate.

A database is characterised by several features:

1. Shared use: multiple authorised users or applications can access the data.
2. Persistence: data remains available after an application terminates or a computer is switched off.
3. Organisation: relationships and constraints give the data structure.
4. Controlled access: permissions determine who may view or change data.
5. Integrity: rules help keep data valid and consistent.
6. Multiple views: different users may see different portions or representations of the same underlying data.

A database should be distinguished from a spreadsheet or a simple collection of text files. A spreadsheet can be useful for small, individual tasks, but databases provide stronger facilities for concurrent access, integrity enforcement, security, recovery, and complex querying.

3. Database Management Systems

A database management system (DBMS) is software that enables users and applications to define, create, store, retrieve, update, protect, and administer databases. Examples include PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, SQLite, and MongoDB. These products differ in architecture and data model, but all provide mechanisms for managing persistent data.

A DBMS acts as an intermediary between users or application programs and stored data. Instead of every application implementing its own file-handling, security, recovery, and concurrency mechanisms, these responsibilities are provided centrally by the DBMS.

3.1 Major DBMS functions

A DBMS commonly performs the following functions:

• Data definition: creating tables, fields, relationships, views, indexes, and constraints.
• Data manipulation: inserting, updating, deleting, and retrieving records.
• Query processing: interpreting queries and selecting an efficient execution plan.
• Transaction management: treating related operations as controlled units of work.
• Concurrency control: coordinating simultaneous users so that their operations do not produce unacceptable results.
• Security and authorisation: controlling access to databases and database objects.
• Integrity enforcement: checking rules such as uniqueness, valid ranges, and valid references.
• Backup and recovery: restoring data after failures, errors, or disasters.
• Metadata management: maintaining descriptions of database structures and rules.
• Performance management: using indexes, caching, and query optimisation to improve response time.

3.2 Database environment components

A database environment includes more than the DBMS. Its principal components are:

A failure in any component can affect the database service. For example, a secure DBMS cannot fully protect data if administrators use weak passwords or procedures permit unauthorised copying.

4. Database Models and Terminology

A data model is a set of concepts used to describe data, relationships, constraints, and operations. It provides a conceptual framework for database design.

4.1 Relational model

The relational model represents data as relations, usually implemented as tables. A table contains rows and columns. A row, also called a tuple or record, represents one occurrence of an entity. A column, also called an attribute or field, represents a property of that entity.

For example, a STUDENT relation might contain:

A primary key is an attribute, or combination of attributes, that uniquely identifies each row. StudentID is a suitable primary key if no two students share it. A foreign key is an attribute that refers to a primary key in another table. For example, an Enrolment table may contain StudentID as a foreign key referring to STUDENT.

A relationship describes an association between entities. A student may register for many modules, and a module may have many students. This is a many-to-many relationship, commonly represented through an associative table such as ENROLMENT.

4.2 Other database models

The hierarchical model organises data in a tree of parent-child relationships. The network model permits more complex interconnected relationships. Object-oriented databases represent persistent objects and their behaviour. NoSQL databases include document, key-value, column-family, and graph systems. They are often selected for particular scalability, flexibility, or data-structure requirements.

The relational model remains especially important because it offers a precise structure, declarative querying through SQL, and well-developed integrity principles. However, no model is universally best. The choice depends on data characteristics, access patterns, consistency requirements, performance needs, skills, cost, and operational context.

5. Schema, Instance, and Metadata

A schema is the overall logical design or blueprint of a database. It specifies structures such as tables, attributes, data types, relationships, constraints, and views. For example, a schema may state that STUDENT has StudentID as an integer primary key, StudentName as required text, and Email as a unique value.

An instance, also called a database state or snapshot, is the actual data stored in the database at a particular time. If a new student enrols, the schema normally remains unchanged while the instance changes.

The distinction can be summarised as follows:

A useful analogy is a form and the completed forms. The form design resembles the schema; the completed entries resemble an instance. The analogy is not exact, because a database schema also includes relationships, constraints, views, and other technical definitions.

Metadata is data about data. It may describe table names, column names, data types, permitted values, key definitions, relationships, data owners, creation dates, and security classifications. A DBMS stores much of this information in a system catalogue, also called a data dictionary or catalogue.

Metadata supports several activities:

• developers use it to understand available structures;
• administrators use it to manage security and performance;
• query processors use it to interpret and optimise statements;
• governance teams use it to identify ownership, sensitivity, and retention requirements.

6. Levels of Database Abstraction

Users do not all need to know how bytes are arranged on storage devices. Database systems therefore provide levels of abstraction.

1. External level: the user or application view. A finance officer may see invoices and payments, while a customer sees only their own orders.
2. Conceptual level: the community-wide logical structure of the database, including entities, relationships, and constraints.
3. Internal level: the physical representation, including files, pages, indexes, partitions, and storage arrangements.

These levels separate concerns. A change in physical storage should not normally require every application to change. Similarly, an application view can hide sensitive attributes or unnecessary complexity.

7. Data Independence

Data independence is the ability to change one level of database description without requiring changes at the next higher level.

7.1 Physical data independence

Physical data independence is the ability to change the internal storage structure without changing the conceptual schema or application programs. Examples include adding an index, moving data to a different storage device, or changing file organisation.

7.2 Logical data independence

Logical data independence is the ability to change the conceptual schema without requiring changes to external views or application programs, provided their required information remains available. Examples include adding an attribute, splitting a table, or introducing a new relationship while preserving existing views.

Logical data independence is generally more difficult to achieve than physical data independence because changes to the logical meaning or structure may affect queries and application assumptions.

Data independence does not mean that changes are always cost-free. A redesigned table may require migration, testing, revised documentation, or performance tuning. The concept describes insulation from change, not the absence of all consequences.

8. Database Users and Professional Roles

Different people interact with a database in different ways.

8.1 Database administrator

The database administrator (DBA) is responsible for the operational management of one or more databases. Duties may include installation, configuration, user authorisation, backup, recovery, monitoring, performance tuning, capacity planning, and security controls.

8.2 Database designer or data modeller

A database designer analyses organisational requirements and creates the conceptual and logical design. This includes identifying entities, attributes, relationships, keys, constraints, and appropriate normalisation. The designer must understand both business processes and technical database principles.

8.3 Application developer

An application developer creates software that interacts with the DBMS. Developers write queries, implement validation, manage transactions, and present data through websites, mobile applications, or services. They must protect applications against threats such as SQL injection and must avoid exposing data unnecessarily.

8.4 End users

• Naïve or parametric users perform routine, predefined tasks, such as recording a sale or checking in a patient.
• Casual users access the database occasionally and may use reports or ad hoc queries.
• Sophisticated users conduct complex analysis using advanced queries or analytical tools.
• Standalone users may use packaged software to maintain a personal database.

8.5 System analysts and managers

System analysts translate organisational requirements into system specifications. Managers and data owners determine business priorities, acceptable risks, access policies, retention periods, and compliance obligations.

Effective database work is therefore collaborative. Technical quality depends on communication among business stakeholders, designers, developers, administrators, security professionals, and users.

9. Advantages of Database Systems

9.1 Reduced redundancy and improved consistency

Central management can reduce unnecessary duplication. If a customer address is stored in many independent files, one file may be updated while others remain outdated. A well-designed database stores facts in controlled structures and uses relationships to connect them.

Redundancy is not always eliminated. Replication, caching, reporting stores, and denormalised structures may be deliberately used for performance or availability. Such duplication must be controlled so that conflicting values do not arise.

9.2 Data sharing and integration

A database allows multiple applications and departments to use a common data resource. This can improve coordination and provide a more complete view of organisational activity.

9.3 Integrity enforcement

Constraints can require a value to be present, ensure that identifiers are unique, restrict values to valid ranges, and ensure that a foreign key refers to an existing record. These mechanisms prevent many common errors.

9.4 Security

DBMS facilities can support authentication, authorisation, roles, views, encryption, auditing, and monitoring. Security is strongest when technical controls are combined with sound procedures and staff training.

9.5 Backup and recovery

A DBMS can automate backups and maintain logs that support recovery after hardware failure, software errors, accidental deletion, or other incidents.

9.6 Concurrent access and transaction processing

Many users can work with the same database while the DBMS coordinates their operations. Transactions help preserve reliable results when several related changes must succeed together.

9.7 Flexibility and reporting

Users can retrieve data in different ways without creating a separate file for every question. Views and query tools support operational reporting, monitoring, and analysis.

10. Limitations and Risks

Database systems also introduce costs and risks.

• Cost: licensing, hardware, cloud services, development, administration, training, and maintenance may be significant.
• Complexity: design and operation require specialised knowledge.
• Central point of impact: a failure or security breach may affect many applications and users.
• Performance trade-offs: strong consistency, security checks, and integrity validation may add processing overhead.
• Migration difficulty: moving from a legacy system or changing a data model can be expensive.
• Data quality problems: a DBMS stores and processes values; it does not automatically determine whether those values are truthful.
• Privacy risks: centralised data may make surveillance, misuse, or large-scale exposure more damaging.
• Vendor dependence: proprietary formats or services may make future migration difficult.
• Overengineering: a database may be unnecessary for a small, isolated task with simple requirements.

A database solution should therefore be proportionate to the problem. The correct question is not whether databases are always better than files, but which storage and management approach satisfies the requirements at acceptable cost and risk.

11. Applied Case: Student Enrolment System

The following is an illustrative case.

Background

A training institute initially records student enrolment in separate spreadsheets maintained by admissions, teaching, and finance departments.

Problem

A student may appear under different spellings in different spreadsheets. A module may be assigned an incorrect fee, and staff cannot reliably determine which students are enrolled in a class. Reports require manual consolidation, and access to personal information is poorly controlled.

Analysis

The institute identifies the main entities as STUDENT, PROGRAMME, MODULE, ENROLMENT, and PAYMENT. A student can have many enrolments, and a module can have many students. ENROLMENT therefore resolves the many-to-many relationship between STUDENT and MODULE. StudentID, ProgrammeID, ModuleID, and EnrolmentID are selected as identifiers. Constraints are defined so that an enrolment must refer to an existing student and module.

Approach and solution

A relational DBMS is implemented. Admissions staff can create and update student records. Teaching staff can view class lists but cannot view payment details. Finance staff can view payment information but have limited access to academic notes. The application uses transactions when an enrolment and its associated payment record are created. Backups are scheduled, access is role-based, and audit logs record important changes.

Outcome

The institute obtains consistent class lists, faster reports, better control of personal data, and a clearer process for backup and recovery. Some work remains: staff must validate data at entry, administrators must monitor performance, and the institute must establish retention and privacy procedures.

Learning points

The case demonstrates that database benefits arise from a combination of data modelling, DBMS functionality, application design, governance, and responsible human practice. Merely purchasing a DBMS would not solve inconsistent definitions or poor data-entry procedures.

12. Governance, Ethics, and Contemporary Practice

Database professionals have responsibilities beyond technical operation. Personal data should be collected for legitimate purposes, accessed only by authorised people, retained no longer than necessary, and protected against unauthorised disclosure. Organisations should identify data owners, classify sensitive data, document retention rules, and maintain audit trails.

Important governance principles include least-privilege access, separation of duties, secure authentication, encryption where appropriate, tested backups, incident response, and regular review of permissions. Legal requirements vary by jurisdiction, but privacy and data-protection obligations commonly include transparency, purpose limitation, accuracy, security, and rights of individuals.

Cloud databases and database-as-a-service platforms can provide elasticity, managed backups, and global availability, but they require careful attention to provider responsibilities, location of data, access configuration, service availability, and exit strategies. Distributed and NoSQL databases may support large-scale or flexible workloads, but their design choices can affect transaction behaviour, consistency, and operational complexity.

Artificial intelligence increasingly uses databases as sources of training, retrieval, and operational information. AI systems do not remove the need for accurate metadata, access controls, provenance, and human oversight. An AI application connected to a database must be prevented from disclosing information that the requesting user is not authorised to see. Outputs should be evaluated for accuracy, bias, and traceability.

Best-Practice Principles

For introductory database projects, the following principles are valuable:

1. Clarify business requirements before choosing a technology.
2. Define terms and data ownership so that important concepts have consistent meanings.
3. Choose stable, unique keys and enforce appropriate constraints.
4. Separate sensitive information and restrict access according to job responsibilities.
5. Validate data at both the application and database levels.
6. Plan backup, recovery, monitoring, and maintenance before deployment.
7. Test concurrent use, failure recovery, and security controls.
8. Document the schema, metadata, assumptions, and change history.
9. Monitor data quality after implementation rather than assuming design alone is sufficient.
10. Review whether the selected database model remains suitable as requirements change.

Chapter Summary

Data consists of recorded facts, while information is data interpreted in a meaningful context. A database is an organised, persistent collection of related data; a DBMS is the software that defines, stores, retrieves, protects, and administers that data. A complete database environment includes hardware, software, data, procedures, people, applications, and metadata.

The relational model represents data in tables connected through keys and relationships. A schema describes the database structure, whereas an instance is the actual data at a particular time. Metadata describes data structures, meaning, ownership, and constraints. The three abstraction levels—external, conceptual, and internal—support data independence. Physical data independence concerns changes to storage; logical data independence concerns changes to the conceptual design.

Database administrators, designers, developers, analysts, managers, and end users have different responsibilities. Database systems offer sharing, integrity, security, recovery, concurrent access, and flexible reporting, but they also involve cost, complexity, privacy risks, migration challenges, and the possibility of widespread impact when failures occur. Successful database use requires technical design together with governance, security, data-quality controls, and responsible professional practice.

Key Terms

Data: Recorded facts, observations, symbols, or measurements.

Information: Data processed or interpreted so that it has meaning and supports understanding or action.

Database: An organised collection of logically related, persistent data.

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

Data model: A set of concepts for describing data, relationships, constraints, and operations.

Relational model: A model that represents data as relations, usually implemented as tables.

Table: A relational structure consisting of rows and columns.

Record or tuple: A row representing one occurrence of an entity.

Attribute or field: A named property represented by a column.

Primary key: An attribute or set of attributes that uniquely identifies a row.

Foreign key: An attribute or set of attributes that refers to a key in another table.

Schema: The defined logical structure and rules of a database.

Instance: The actual contents of a database at a particular time.

Metadata: Data describing the meaning, structure, origin, ownership, or constraints of data.

System catalogue: DBMS-maintained repository of metadata.

Data independence: The ability to change one level of database description without requiring changes at a higher level.

Database administrator: The professional responsible for operating, securing, maintaining, and tuning databases.

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

Transaction: A controlled unit of database work, often consisting of multiple related operations.

View: A user- or application-specific representation of data derived from one or more database structures.

Review Questions

Short Answer Questions

1. What is the difference between data and information?
2. Define a database management system and state two of its functions.
3. What is the difference between a database schema and a database instance?
4. Distinguish a primary key from a foreign key.
5. What is metadata, and why is it useful?

Long Answer Questions

1. Explain the main components of a database environment and describe how they work together.
2. Explain the external, conceptual, and internal levels of database abstraction.
3. Compare physical data independence and logical data independence, using an example of each.
4. Discuss five advantages and four limitations of database systems.
5. Describe the responsibilities of a database administrator, database designer, application developer, and end user.

Analytical / Application Questions

1. A college stores student names and addresses separately in admissions, library, and finance spreadsheets. Analyse the likely problems and propose a database-based solution.
2. An organisation wants all employees to have unrestricted access to every database table because “shared data improves efficiency.” Evaluate this proposal using security, privacy, and governance principles.
3. A small community club has one treasurer maintaining a simple membership list with fewer than 100 records and no concurrent users. Analyse whether a full DBMS is justified, identifying factors that could change your recommendation.

Multiple Choice Questions with Answers

Question 1

Which statement best defines a database?

A. A temporary list held only in computer memory

B. An organised collection of logically related, persistent data

C. A program used exclusively for designing websites

D. A hardware device used for network communication

Correct Answer: B. An organised collection of logically related, persistent data

Question 2

What is the primary role of a DBMS?

A. To replace all application programs

B. To manage the definition, storage, access, security, and maintenance of databases

C. To manufacture storage hardware

D. To convert every database into a spreadsheet

Correct Answer: B. To manage the definition, storage, access, security, and maintenance of databases

Question 3

Which item is an example of metadata?

A. A customer’s actual telephone number

B. The number of products sold today

C. The data type and constraint defined for a CustomerID column

D. A photograph stored in a customer record

Correct Answer: C. The data type and constraint defined for a CustomerID column

Question 4

What does a database instance represent?

A. The database’s physical storage technology only

B. The actual data stored at a particular time

C. The software licence for the DBMS

D. The job description of the database administrator

Correct Answer: B. The actual data stored at a particular time

Question 5

Which key uniquely identifies a row in a relational table?

A. Foreign key

B. Candidate report

C. Primary key

D. View key

Correct Answer: C. Primary key

Question 6

Adding an index without changing application queries is primarily an example of which concept?

A. Physical data independence

B. Logical data independence

C. Data duplication

D. Entity inheritance

Correct Answer: A. Physical data independence

Question 7

Which professional is primarily responsible for backup, recovery, authorisation, and database performance monitoring?

A. Database administrator

B. End user

C. Graphic designer

D. Technical author

Correct Answer: A. Database administrator

Question 8

A table contains StudentID values that must refer to existing rows in a STUDENT table. This rule is normally enforced using a:

A. Foreign key constraint

B. Colour code

C. File extension

D. Screen resolution

Correct Answer: A. Foreign key constraint

Question 9

Which is a limitation or risk of database systems?

A. They can never support multiple users

B. They eliminate the need for data validation

C. They may involve significant cost and operational complexity

D. They cannot provide access controls

Correct Answer: C. They may involve significant cost and operational complexity

Question 10

In the student enrolment case, why is an ENROLMENT table useful?

A. It removes the need for students and modules

B. It resolves the many-to-many relationship between students and modules

C. It stores only database passwords

D. It prevents all changes to enrolment data

Correct Answer: B. It resolves the many-to-many relationship between students and modules