2. DBMS Architecture and Data Models

Introduction and Context

A database management system (DBMS) is not merely a software package that stores records. It is an organized environment for defining, creating, controlling, securing, and using data. The architecture of a DBMS determines how users, applications, database services, and storage interact. A data model, by contrast, determines how the meaning and structure of data are represented. Architecture concerns the organization of the database system; the data model concerns the organization of the data itself. These ideas are closely related, but they should not be confused.

For example, a university may use a relational data model to represent students, courses, instructors, and enrolments. That database may be implemented on a centralized server, accessed through a client-server application, and described through a three-schema architecture. The same logical data could also be exposed through web services or analytical platforms without changing its underlying relational model.

Understanding these distinctions is foundational for database design, data engineering, analytics, and information-systems management. A poor architectural decision can create performance, security, and maintenance problems even when the data model is sound. Conversely, an unsuitable data model can make applications difficult to develop, queries inefficient, and organizational information difficult to interpret.

Learning Objectives

After completing this chapter, students should be able to:

1. Explain the purpose and levels of the three-schema architecture.

2. Distinguish data independence from physical or logical database implementation.

3. Compare centralized, client-server, and distributed styles of DBMS deployment.

4. Differentiate conceptual, logical, and physical data models.

5. Explain the characteristics of hierarchical, network, relational, object-oriented, and semi-structured models.

6. Select an appropriate model or architecture for a given organizational situation.

7. Evaluate trade-offs involving scalability, integrity, flexibility, performance, and governance.

Architecture as the Organization of Database Services

A DBMS usually performs several functions: data definition, data storage, query processing, transaction management, concurrency control, recovery, authorization, and metadata management. The architecture specifies where these functions reside and how they communicate.

A centralized DBMS places the principal database processing and storage at one central computer or server. Terminals or applications submit requests to that system. Centralization simplifies control, backup, security administration, and consistency management. However, a central failure can affect all users, and a heavily used server may become a performance bottleneck.

In a client-server architecture, responsibility is divided between clients and one or more servers. The client commonly provides the user interface and some application logic, while the database server manages storage, query execution, transactions, and security. A two-tier application connects a client directly to a database server. A three-tier application introduces an application server between the user interface and database server. The additional layer improves security, scalability, connection management, and separation of concerns.

A web-based student information system illustrates the three-tier arrangement. A browser is the presentation tier, an application server validates requests and applies business rules, and the DBMS stores and retrieves data. Users do not receive direct database credentials, and the application server can enforce rules such as preventing enrolment in a course when prerequisites have not been completed.

Modern enterprises may also use distributed database architectures. Data may be replicated across sites, partitioned among servers, or accessed through a cloud-managed database service. Distribution can improve availability and reduce network delay, but it introduces challenges involving synchronization, consistency, failure recovery, data sovereignty, and operational complexity.

The Three-Schema Architecture

The three-schema architecture, associated with the ANSI/SPARC view of database systems, separates user views from the logical structure of the whole database and from physical storage. Its principal purpose is to support data abstraction and data independence.

External Schema

The external level contains individual user views. A view is a representation of the database relevant to a particular user group or application. A payroll officer may see employee identifiers, salary grades, and deductions, whereas an instructor may see student names, course registrations, and assessment results. Neither user requires access to every stored attribute.

External schemas provide two important benefits. First, they simplify interaction by presenting only relevant information. Second, they support security by restricting access to sensitive or unnecessary data. Multiple external schemas can be derived from the same conceptual database.

Conceptual Schema

The conceptual level describes the complete logical structure of the database for the organization. It identifies major entities, attributes, relationships, constraints, and business meanings. For a university, the conceptual schema may represent Student, Programme, Course, Instructor, Classroom, and Enrolment, together with rules such as “a student may enrol in many courses” and “each course belongs to an academic department.”

The conceptual schema is independent of particular storage devices and, ideally, independent of individual application programs. It provides a shared organizational view of data and is therefore important for communication among analysts, designers, managers, and users.

Internal Schema

The internal level describes how data is physically stored. It may include file organization, pages, blocks, indexes, compression, partitioning, hashing, and storage allocation. These details are normally hidden from end users and application programmers.

For instance, a DBMS may create an index on StudentID, partition transaction records by date, or store frequently accessed data in memory. Such decisions can change without changing the conceptual entities or the external user views.

Mappings and Data Independence

Mappings connect the three levels. The external-conceptual mapping relates a user view to the conceptual schema. The conceptual-internal mapping relates the logical structure to physical storage.

Physical data independence is the ability to change internal storage structures without requiring changes to the conceptual schema or application programs. Adding an index is a typical example. Logical data independence is the ability to change the conceptual schema without requiring changes to all external views or applications. Adding a nonessential attribute or introducing a new relationship may be possible without disrupting existing users, although logical independence is generally more difficult to achieve than physical independence.

The three-schema architecture should not be interpreted as three separate copies of the database. It is a set of abstraction levels and mappings. The actual implementation may use different structures, but the conceptual separation remains useful for design and governance.

Data Models: From Organizational Meaning to Storage Design

A data model is a collection of concepts used to describe data, relationships, constraints, and operations. Modeling is a form of abstraction: it reduces a complex real-world environment to a representation that can be implemented and analyzed.

A conceptual data model is close to organizational understanding. It identifies business objects and their relationships without committing to a particular DBMS. Entity-relationship modeling is frequently used at this stage. A conceptual model might state that a Customer places an Order and that an Order contains one or more Products.

A logical data model translates the conceptual model into a structure supported by a data-modeling approach. In a relational design, entities become tables, attributes become columns, and relationships are represented through keys. The logical model also addresses normalization, domains, integrity constraints, and cardinality.

A physical data model describes implementation details for a selected DBMS. It specifies data types, indexes, partitions, tablespaces, storage parameters, materialized views, and sometimes denormalization decisions. The physical model is influenced by workload, performance requirements, security, cost, and hardware or cloud infrastructure.

The distinction among these models supports disciplined design. If physical considerations dominate too early, designers may optimize a structure before understanding the business meaning of the data. If physical implementation is ignored completely, the resulting system may be elegant but impractical at operational scale.

Major Data-Modeling Approaches

Hierarchical Model

The hierarchical model organizes records in a tree structure. Each child record normally has one parent, producing a one-to-many pattern. A departmental organization chart or a file system is naturally hierarchical.

The model can provide efficient navigation when relationships follow a stable tree. However, many-to-many relationships are awkward, and the same information may have to be repeated or represented through complex paths. Hierarchical systems were important in early database processing, particularly where predictable, high-volume transactions were required. Their main limitation is that the structure reflects predefined access paths rather than the variety of relationships found in modern organizations.

Network Model

The network model extends the hierarchical approach by allowing a record to participate in multiple sets or relationships. It can represent many-to-many associations more naturally than a strict tree. A student, for example, can be connected to several courses, while each course can be connected to many students.

Network databases can be highly efficient because applications navigate explicitly through record relationships. Their disadvantage is complexity: programs often depend closely on the physical or navigational structure. Changes to the schema may therefore require substantial program modifications. Although less common in new general-purpose systems, network concepts remain relevant when studying database evolution and navigational access.

Relational Model

The relational model represents data as relations, commonly implemented as tables. Rows represent tuples or records, and columns represent attributes. A primary key uniquely identifies rows, while foreign keys represent relationships between tables.

Consider the following logical relations:

• Student(StudentID, Name, ProgrammeID)
• Course(CourseID, Title, Credits)
• Enrolment(StudentID, CourseID, Semester, Grade)

The Enrolment relation uses StudentID and CourseID to connect students with courses. This design represents a many-to-many relationship through an associative relation. Relational algebra provides a formal foundation for operations such as selection, projection, union, and join. SQL provides a widely used declarative language through which users state what data is required rather than specifying every navigation step.

The relational model is valued for its mathematical foundation, integrity constraints, mature tools, transaction support, and broad professional adoption. Normalization can reduce redundancy and update anomalies. Nevertheless, object-relational impedance mismatch may arise when complex application objects must be mapped into tables. Relational systems can also require careful schema design when handling highly variable or deeply nested data.

Object-Oriented Model

The object-oriented model represents data as objects containing both state and behavior. Objects may belong to classes, support inheritance, and maintain identity independent of their attribute values. This approach is useful for domains involving complex structures, such as computer-aided design, multimedia, scientific simulations, and engineering systems.

An object may contain nested objects or collections, reducing the need to decompose complex structures into numerous tables. Inheritance allows a specialized class, such as GraduateStudent, to derive properties from Student. However, object-oriented databases have not displaced relational systems in mainstream business applications. Adoption may be constrained by specialized tools, interoperability concerns, and the continuing strength of relational technology.

Semi-Structured Model

Semi-structured data does not require every record to follow exactly the same schema. It often uses nested documents, name-value pairs, or tagged elements. JSON and XML are common representations. Two product documents may share attributes such as name and price while one also contains dimensions and another contains warranty information.

Document databases are useful for content management, event data, product catalogs, mobile applications, and systems whose attributes evolve frequently. Their flexibility does not mean that structure is absent. Meaningful field names, validation rules, identifiers, and relationship conventions are still required. Excessive flexibility can result in inconsistent representations, duplication, difficult cross-document queries, and weak governance.

Semi-structured data is also common at system boundaries. APIs, logs, sensor messages, and data-exchange formats may produce JSON even when the analytical platform ultimately loads the information into relational tables or a columnar data warehouse.

Comparing Models Through Design Trade-offs

No data model is universally superior. Selection depends on the data’s structure, workload, consistency requirements, query patterns, development environment, and governance obligations.

The relational model is often appropriate when transactions require strong integrity, relationships are well understood, and ad hoc querying is important. A document model may be suitable when records are naturally aggregated, attributes vary, and rapid application development is valuable. A hierarchical model may fit stable one-to-many paths, while an object-oriented model may suit complex domain objects and specialized engineering data.

Several questions guide model selection:

• Are relationships central to the application, or are records mostly self-contained?
• Is the schema stable, or does it change frequently?
• Are strong multi-record transactions essential?
• Will users require flexible analytical queries?
• What level of duplication is acceptable?
• How will data quality, lineage, access, and retention be governed?

A polyglot data architecture uses different models for different workloads. An organization might use a relational system for financial transactions, a document store for product content, and a graph or analytical platform for specialized analysis. Polyglot persistence can improve fitness for purpose, but it increases integration, skills, monitoring, backup, and governance requirements. It should therefore be a deliberate architectural decision rather than a reaction to technology fashion.

Applied Case: Modernizing a University Information System

Background

This illustrative case concerns Northbridge University, which has operated a centralized relational database for admissions, registration, fees, and examination records. The database contains well-defined student and course tables and has supported administrative reporting for many years.

Situation and Problem

The university introduces a mobile application, an online learning platform, and a research-data portal. The mobile application requires rapid access to student schedules. The learning platform produces semi-structured activity events, while the research portal stores variable metadata about projects and datasets. The existing system is reliable, but direct access by new applications would create security and maintenance risks.

Analysis

The core administrative data has strong relationships and strict integrity requirements. For example, a fee payment must be associated with a valid student account, and a course registration must refer to an existing course offering. The relational model remains appropriate for this system of record.

The new applications have different access patterns. Mobile clients need controlled, simplified views rather than unrestricted database access. Learning activity events vary by tool and may be stored temporarily as semi-structured documents before being transformed for analytics. Research metadata requires flexible attributes but still needs ownership, classification, and retention rules.

Approach and Solution

The university adopts a three-tier client-server architecture. Web and mobile clients communicate with an application layer through authenticated APIs. The application layer exposes external views, validates business rules, and prevents clients from directly modifying core tables.

The existing relational database remains the conceptual system of record. A physical redesign adds indexes for frequent schedule queries and partitions large historical tables. A document store receives learning events, while a governed integration pipeline transfers approved data to an analytical warehouse. Conceptual and logical models are documented so that each application uses consistent definitions of Student, Course, Enrolment, and Academic Period.

Access is controlled by role, sensitive attributes are excluded from ordinary views, and data lineage is recorded for analytical datasets. Replication and tested backups improve availability without allowing every system to become an independent source of truth.

Outcome

The university gains a more scalable service architecture while preserving the integrity of its central records. Applications can evolve independently, but shared definitions and governance reduce semantic inconsistency. The solution does not eliminate complexity; it relocates complexity into API design, integration, monitoring, and policy enforcement, where it can be managed more systematically.

Learning Points

The case demonstrates that architecture and data-model selection should be based on distinct but connected concerns. A relational model can coexist with semi-structured stores. External schemas and application services protect the core database. Finally, flexibility is valuable only when accompanied by metadata, validation, ownership, and lifecycle controls.

Risks, Limitations, and Common Mistakes

A frequent mistake is confusing a user interface with a data model. A dashboard may present information in charts, but it does not define the underlying entities, constraints, or storage approach. Another mistake is assuming that the three-schema architecture automatically guarantees independence. Poorly designed applications may still embed table names, physical assumptions, or undocumented business rules in code.

Over-normalization can create excessive joins and operational complexity, while premature denormalization can produce inconsistent data. In document systems, uncontrolled duplication can make updates unreliable. In distributed systems, designers may underestimate latency, partial failure, conflict resolution, and consistency requirements.

Architecture also creates security risks. Centralized systems can become single points of compromise; client-server systems may expose credentials or excessive database privileges; distributed systems may replicate sensitive information beyond appropriate jurisdictions. Data models can embed bias or inappropriate classifications, particularly when categories are treated as neutral facts rather than organizational decisions.

Governance, Ethics, and Professional Practice

Database architecture must support accountability. Organizations should define data ownership, stewardship responsibilities, retention periods, access privileges, quality standards, and audit requirements. Least-privilege access is preferable to granting applications broad administrative permissions. Sensitive information should be protected through appropriate authentication, authorization, encryption, masking, and monitoring.

Logical data models should distinguish operational necessity from unnecessary collection. Storing more personal data than required increases exposure and may conflict with privacy obligations. Professionals must also document definitions: terms such as “active student,” “customer,” or “completed transaction” can have different interpretations across departments. A technically correct query can still produce a misleading result if its semantic assumptions are not governed.

Contemporary Developments

Cloud database services increasingly abstract physical infrastructure through automated provisioning, replication, scaling, backup, and monitoring. This strengthens the importance of logical and conceptual design because physical resources can change rapidly while business definitions must remain stable.

Data lakes and lakehouse platforms combine flexible storage with analytical management features. Streaming systems process events continuously, and APIs expose data as services rather than as direct tables. Knowledge graphs and graph databases are gaining attention for connected data, recommendation, fraud analysis, and semantic integration. Artificial intelligence also increases demand for well-governed data models: models require reliable features, documented lineage, controlled access, and reproducible datasets.

These developments do not make classical architecture obsolete. The principles of abstraction, independence, integrity, security, and explicit modeling remain applicable even when implementation technologies change.

Best Practices for Architectural and Model Selection

A sound practice begins with requirements rather than product selection. Identify users, business processes, data ownership, transaction boundaries, query patterns, volumes, latency expectations, regulatory obligations, and failure consequences. Develop a conceptual model before selecting physical structures. Document mappings from conceptual entities to logical structures and from logical structures to physical implementations.

Use relational constraints where relationships and consistency are critical. Use semi-structured approaches where variable structure is genuine and governed. Isolate clients from core storage through services or controlled views. Test assumptions with realistic workloads, including concurrency, recovery, and growth. Finally, treat architecture as a lifecycle: schemas, interfaces, indexes, and storage strategies require monitoring, review, migration planning, and documentation.

Chapter Summary

DBMS architecture describes how users, applications, database services, and storage are organized. Centralized systems simplify control but may create availability and scalability concerns; client-server and distributed architectures improve separation and scale while increasing integration and consistency challenges. The three-schema architecture separates external views, the organizational conceptual schema, and the internal storage schema. Its key benefit is abstraction and support for physical and logical data independence.

Conceptual, logical, and physical data models represent progressively more implementation-specific descriptions of information. Hierarchical, network, relational, object-oriented, and semi-structured models offer different ways to represent relationships, structure, identity, and access. Model selection should reflect workload, integrity needs, flexibility, governance, and organizational purpose. Contemporary systems may combine several models, but polyglot architectures require disciplined integration and stewardship.

Key Terms

Three-schema architecture: A framework separating external views, the conceptual database structure, and internal storage details.

External schema: A user or application-specific view of database information.

Conceptual schema: The integrated logical description of an organization’s data and constraints.

Internal schema: The description of physical storage structures and access mechanisms.

Physical data independence: The ability to change storage structures without changing higher-level schemas.

Logical data independence: The ability to change the conceptual schema without widespread changes to external views.

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

Conceptual data model: A technology-independent representation of business entities and relationships.

Logical data model: A representation structured according to a selected modeling approach but not yet tied to storage details.

Physical data model: An implementation-specific description of tables, indexes, partitions, data types, and storage.

Centralized architecture: An arrangement in which major database processing and storage are located at a central system.

Client-server architecture: An arrangement dividing responsibilities between requesting clients and service-providing servers.

Relational model: A model representing data as relations, usually implemented as tables connected by keys.

Hierarchical model: A tree-based model in which records are organized through parent-child relationships.

Network model: A navigational model allowing records to participate in multiple interconnected relationships.

Object-oriented model: A model representing objects that combine state, identity, behavior, and often inheritance.

Semi-structured data: Data with flexible or varying organization, commonly represented using JSON or XML.

Schema: A formal description of database structure, meaning, and constraints.

Data independence: The capacity to change one level of database description without requiring equivalent changes at another level.

Polyglot persistence: The deliberate use of multiple data-storage models or technologies for different workloads.

Review Questions

Short Answer Questions

1. What is the principal purpose of the three-schema architecture?

2. Distinguish an external schema from a conceptual schema.

3. Define physical data independence and give one example.

4. What role does a foreign key play in a relational model?

5. Why can semi-structured data still require governance?

Long Answer Questions

1. Explain the three-schema architecture and discuss how its mappings support data independence.

2. Compare centralized, two-tier client-server, and three-tier client-server architectures.

3. Differentiate conceptual, logical, and physical data models using a university database example.

4. Compare hierarchical, network, relational, object-oriented, and semi-structured models in terms of structure and typical use.

5. Discuss the advantages and risks of adopting a polyglot data architecture.

Analytical / Application Questions

1. A hospital needs strict transaction integrity for billing but also stores variable medical-device messages. Recommend an architecture and combination of data models, and justify the choice.

2. An organization adds indexes and partitions to a database without changing application views. Identify the level of change and explain the relevant form of data independence.

3. A product catalog contains different attributes for different product categories, but finance requires reliable pricing and inventory relationships. Analyze whether a purely document-based design is appropriate, and propose an alternative.

22. Multiple Choice Questions with Answers

Question 1

Which change most directly illustrates physical data independence?

A. Adding an index without changing application views

B. Changing the meaning of the Customer entity

C. Adding a new business department

D. Replacing a user role with another role

Correct Answer: A. Adding an index without changing application views

Question 2

In the three-schema architecture, which level describes the organization-wide logical structure of the database?

A. External level

B. Conceptual level

C. Internal level

D. Presentation level

Correct Answer: B. Conceptual level

Question 3

A university application hides database credentials from mobile users and applies enrolment rules through an application server. Which architecture is being used?

A. Single-file architecture

B. Hierarchical architecture

C. Three-tier client-server architecture

D. Peer-to-peer file architecture

Correct Answer: C. Three-tier client-server architecture

Question 4

Which model most naturally represents a many-to-many relationship between students and courses using keys and an associative relation?

A. Hierarchical model

B. Relational model

C. Single-level file model

D. Strict tree model

Correct Answer: B. Relational model

Question 5

Which characteristic is most associated with a semi-structured data model?

A. Every record must contain exactly the same attributes

B. Records are organized only through parent-child pointers

C. Data may contain varying attributes and nested structures

D. Data cannot contain identifiers

Correct Answer: C. Data may contain varying attributes and nested structures

Question 6

A database design begins by identifying Customer, Order, and Product without selecting tables, indexes, or a specific DBMS. What kind of model is this?

A. Physical data model

B. Conceptual data model

C. Internal schema

D. Storage allocation model

Correct Answer: B. Conceptual data model

Question 7

Which is a principal limitation of a hierarchical model?

A. It cannot represent any one-to-many relationship

B. It has no concept of records

C. Many-to-many relationships are difficult to represent naturally

D. It always requires JSON documents

Correct Answer: C. Many-to-many relationships are difficult to represent naturally

Question 8

Why might an organization retain a relational system for financial transactions while using a document store for product metadata?

A. Different workloads may benefit from different data models

B. Relational systems cannot store financial data

C. Document stores always provide stronger integrity

D. The three-schema architecture prohibits relational databases

Correct Answer: A. Different workloads may benefit from different data models

Question 9

Which concern becomes especially important when data is replicated across several sites?

A. Eliminating all metadata

B. Synchronization and consistency management

C. Removing access controls

D. Avoiding all forms of abstraction

Correct Answer: B. Synchronization and consistency management

Question 10

In the illustrative university case, why were clients prevented from directly modifying core database tables?

A. To eliminate the need for a conceptual schema

B. To allow business rules and security controls to be enforced centrally

C. To ensure that all data became semi-structured

D. To prevent the use of relational tables

Correct Answer: B. To allow business rules and security controls to be enforced centrally