Advanced Database Foundations and Architectures

Introduction

Advanced database engineering begins with a precise understanding of what a database is, how its users and applications perceive it, and how its physical implementation affects performance, availability, scalability, governance, and cost. Contemporary database systems are no longer limited to a single relational server. Organisations commonly operate transactional databases, analytical warehouses, distributed data platforms, federated systems, cloud-native services, and machine-learning data stores at the same time.

This chapter establishes the conceptual foundation for analysing these systems. It reviews database terminology, abstraction levels, schemas, data independence, and the central ideas of relational theory. It then compares major deployment architectures: centralized, client-server, distributed, federated, and cloud architectures. Finally, it classifies workloads as online transaction processing (OLTP), online analytical processing (OLAP), hybrid transactional and analytical processing (HTAP), and artificial-intelligence-oriented workloads.

The emphasis is not merely on definitions. At postgraduate level, database architecture must be understood as a set of design trade-offs. A system that maximizes consistency may sacrifice availability or geographic performance; a system that scales analytically may be unsuitable for high-volume transactions; and a cloud service that reduces infrastructure administration may increase dependency on a provider.

Learning Objectives

After completing this chapter, the reader should be able to:

1. Explain fundamental database terminology and distinguish data, metadata, schemas, instances, and database management systems.

2. Describe the three-schema architecture and explain logical and physical data independence.

3. Interpret the relational model, including relations, attributes, tuples, domains, keys, integrity constraints, and relational operations.

4. Compare centralized, client-server, distributed, federated, and cloud database architectures.

5. Classify OLTP, OLAP, HTAP, and AI workloads according to their data, latency, consistency, and computational requirements.

6. Evaluate architectural trade-offs involving performance, scalability, availability, governance, security, and cost.

7. Apply these concepts to the analysis of a realistic enterprise database environment.

1. Database Concepts and Terminology

A database is an organized collection of related data maintained for use by one or more applications or users. The data may represent customers, products, financial transactions, sensor observations, documents, or knowledge extracted from those sources. A database management system (DBMS) is software that defines, creates, stores, retrieves, updates, protects, and administers databases.

The distinction between a database and a DBMS is important. The database is the managed collection of data; the DBMS is the software infrastructure that manages it. A complete database system normally includes the database, DBMS, applications, users, operational procedures, hardware, and network infrastructure.

1.1 Data, information, metadata, and knowledge

Data consists of recorded facts or observations. A value such as `42`, a timestamp, or a customer identifier is data, although its meaning depends on context. Information is data interpreted in a useful context—for example, “42 units were sold in the western region during March.” Metadata is data about data. Examples include a column’s name, data type, permitted values, ownership, lineage, and retention period.

Metadata may be:

• Structural, describing tables, columns, indexes, and relationships.
• Operational, describing load times, job status, or data freshness.
• Business, describing definitions such as what qualifies as an active customer.
• Governance-related, describing classification, sensitivity, retention, and access rules.

In advanced data platforms, metadata management is essential because the same field may have different meanings in different systems. A field called `customer_id` may identify a person in one system and an account in another.

1.2 Database users and roles

A database environment has several categories of users. End users interact through applications or query tools. Application developers write programs that access databases. Database administrators (DBAs) manage security, backup, recovery, performance, capacity, and availability. Data architects design structures and integration patterns. Data engineers build pipelines and data transformations. Data scientists and analysts query data for modelling and decision support.

These roles increasingly overlap, but separation of duties remains important. For example, the person who develops a data pipeline should not automatically have unrestricted authority to approve production access to confidential data.

1.3 Database state, schema, and instance

A schema is the formal description of database structure. It identifies objects such as relations, columns, domains, constraints, views, and indexes. A database instance or state is the actual content of the database at a particular time.

The schema usually changes relatively infrequently, whereas the instance changes whenever records are inserted, updated, or deleted. For example, a university schema may define `STUDENT(StudentID, Name, Programme)`. The current rows in that relation constitute one database state.

A useful analogy is that a schema resembles a type definition, while an instance resembles the values currently held by an object of that type. The analogy is not exact, because database schemas also include integrity rules and access structures.

1.4 Data dictionary and catalog

A DBMS maintains a system catalog, also called a data dictionary, containing metadata about database objects. The catalog may record table definitions, constraints, indexes, users, privileges, statistics, partitions, and query-optimization information. The query optimizer relies heavily on statistics such as approximate row counts and value distributions.

An inaccurate catalog can lead to poor execution plans even when the logical design is correct. Consequently, statistics maintenance is a practical part of database administration.

2. Abstraction and the Three-Schema Architecture

Database abstraction hides implementation details that are irrelevant to a particular user. Without abstraction, every application would need to understand file layouts, page structures, indexes, replication, and storage devices. Such coupling would make systems difficult to change.

The classical ANSI/SPARC three-schema architecture separates database descriptions into three levels.

2.1 External level

The external level contains user views. A view presents only the data required by a user, role, or application. For example, a payroll application may see employee identifiers and salary information, while a project-management application sees employee identifiers and project assignments but not salaries.

External views support:

• Simplicity for users and applications.
• Security through restricted exposure.
• Custom presentation of the same underlying data.
• Reduced application dependence on internal structures.

A view does not necessarily store independent data. It may be derived dynamically from base relations, although materialized views may store derived results for performance.

2.2 Conceptual level

The conceptual schema describes the logical structure of the entire database for the organisation. It identifies entities or relations, relationships, constraints, and business semantics without specifying storage details. In a relational system, it may include tables, columns, primary keys, foreign keys, and assertions.

The conceptual schema is the principal level for logical database design. It should represent organisational requirements rather than the limitations of a particular storage device or application screen.

2.3 Internal level

The internal schema describes how data is physically stored. It may include files, pages, records, indexes, partitions, compression, clustering, encryption, replication, and storage tiers. These details are essential to system performance but should normally be hidden from ordinary users.

2.4 Mappings between levels

Mappings connect the three levels. An external-to-conceptual mapping explains how a user view corresponds to the conceptual schema. A conceptual-to-internal mapping explains how logical objects are represented physically.

This separation creates a basis for data independence. It does not mean that changes are always free. A major change to semantics, application assumptions, or performance requirements may still require application modification. The architecture reduces unnecessary dependence; it does not eliminate all coupling.

3. Data Independence

Data independence is the capacity to change a schema at one level without requiring changes at a higher level.

3.1 Physical data independence

Physical data independence is the ability to change the internal schema without changing the conceptual schema or application views. Examples include:

• Adding or removing an index.
• Changing file organization.
• Moving data to a different storage device.
• Introducing partitioning or compression.
• Changing a table from local storage to a managed storage tier.

Physical independence is a major responsibility of the DBMS. Applications should request logical data rather than depend on page locations or index structures.

3.2 Logical data independence

Logical data independence is the ability to change the conceptual schema without requiring changes to external views or applications, provided their required semantics remain available. Examples include splitting a relation to remove redundancy, adding an attribute, or introducing a new relationship while preserving an existing view.

Logical independence is generally harder to achieve than physical independence. Applications often embed assumptions about names, meanings, optionality, and update behaviour. A change from one business definition to another cannot be hidden merely by a database view if the underlying meaning has changed.

3.3 Practical limitations

Data independence is a design goal, not an absolute guarantee. Application code may depend on:

• Specific column names or data types.
• Ordering that was never formally guaranteed.
• Timing and latency characteristics.
• Undocumented database features.
• Transaction isolation behaviour.
• Query-plan side effects.

Professional practice therefore combines abstraction with interface contracts, versioned schemas, compatibility views, automated testing, and controlled migration processes.

4. Relational Theory and Integrity

The relational model represents data as relations. In practical database systems, a relation is commonly displayed as a table, although the mathematical concept is more precise.

4.1 Relations, tuples, attributes, and domains

A relation has a relation name and a set of attributes. An attribute is a named column. A tuple is a row containing one value for each attribute. A domain is the set of permitted values for an attribute, such as positive integers, dates, or controlled status codes.

The relational model is based on sets, so duplicate tuples are not conceptually meaningful. SQL, however, commonly permits duplicate rows in query results unless `DISTINCT` or a key constraint removes them. This difference between relational theory and SQL practice matters when reasoning about query results.

A relation schema can be written as:

`STUDENT(StudentID, Name, Programme, YearOfStudy)`

Here, `STUDENT` is the relation name and the items in parentheses are attributes. A relation instance is a finite set of tuples conforming to this schema.

4.2 Keys

A superkey is a set of attributes that uniquely identifies a tuple. A candidate key is a minimal superkey: removing any attribute would destroy uniqueness. One candidate key is selected as the primary key. Other candidate keys are alternate keys.

A foreign key is an attribute or set of attributes in one relation whose values reference a candidate or primary key in another relation. Foreign keys express referential relationships and support referential integrity.

A sound key should be stable, unique, minimal where appropriate, and managed consistently. Natural keys may carry business meaning, while surrogate keys are generated identifiers with no direct business meaning. Neither type is universally superior. Natural keys can provide meaningful uniqueness but may change; surrogate keys are stable but do not by themselves prevent duplicate business entities.

4.3 Integrity constraints

Important integrity constraints include:

• Domain integrity: values conform to their defined domains and types.
• Entity integrity: a primary-key value is unique and not null.
• Referential integrity: a foreign-key value either matches a referenced key or is null when nullability is permitted.
• Key constraints: designated keys remain unique.
• Business constraints: organisational rules such as a shipment date not preceding an order date.

Constraints should be enforced as close as practical to the data. Application-only validation is risky because multiple applications, integration jobs, and administrative scripts may bypass the same checks.

4.4 Relational operations

The relational algebra provides a formal foundation for querying. Core operations include:

• Selection, which chooses tuples satisfying a predicate.
• Projection, which chooses specified attributes.
• Union, which combines compatible relations.
• Difference, which returns tuples in one relation but not another.
• Cartesian product, which pairs tuples from two relations.
• Join, which combines related tuples according to a condition.

A query optimizer may transform an equivalent expression into a more efficient execution plan. For example, applying a selective filter before a join can reduce intermediate data, provided the transformation preserves semantics.

5. Database Architectures

Architecture concerns the distribution of data, processing, control, and responsibility. The following categories are analytical models; real systems may combine them.

5.1 Centralized architecture

In a centralized database, data and most DBMS processing reside at one central server or tightly coupled installation. Clients may be terminals or applications, but the authoritative database is centrally managed.

Advantages include:

• Simplified governance and security.
• Centralized backup and recovery.
• Consistent transaction management.
• Easier enforcement of a single schema.

Limitations include a potential single-site failure, network dependence for remote users, capacity constraints, and geographic latency. High-availability techniques such as replication and failover may reduce, but not eliminate, these risks.

5.2 Client-server architecture

In a client-server system, clients request services from database servers. A thick client may contain substantial business logic; a thin client may delegate most processing to application servers. A common three-tier model separates presentation, application logic, and database services.

The three-tier arrangement improves maintainability and security by preventing direct database access from every user device. It also permits connection pooling, centralized authorization, API versioning, and controlled transaction boundaries.

A critical design issue is where logic resides. Excessive logic in clients creates inconsistent behaviour and difficult deployment. Excessive logic in the database can create vendor dependence and make distributed integration harder. The appropriate balance depends on consistency requirements, team capabilities, and workload characteristics.

5.3 Distributed databases

A distributed database is logically integrated data whose components are stored at multiple networked sites. Distribution may involve:

• Fragmentation, dividing a relation horizontally by rows or vertically by columns.
• Replication, maintaining copies at multiple sites.
• Allocation, deciding where fragments or replicas reside.

Distribution can improve locality, availability, and scalability. It also introduces network latency, partial failures, replica coordination, distributed transactions, conflict resolution, and more complex observability.

A distributed transaction may require coordination among several services. Two-phase commit can provide atomic commitment under appropriate assumptions, but it introduces coordination overhead and can leave participants waiting when the coordinator fails. Many modern systems instead use application-level sagas, in which a sequence of local transactions is linked by compensating actions. Sagas improve autonomy but do not provide the same atomicity as a single transaction.

The CAP perspective states that, during a network partition, a distributed system cannot simultaneously guarantee both strong consistency and availability in the broad sense used by the theorem. CAP is not a general statement that systems can choose only two of three properties under normal operation; it concerns behaviour when a partition occurs. Design decisions must therefore specify the consistency model, failure assumptions, and application tolerance.

5.4 Federated databases

A federated database system integrates multiple autonomous databases while preserving some local control. The component systems may differ in schemas, DBMS products, ownership, and policies. A federation can provide a unified access layer without physically consolidating all data.

Federation is useful after mergers, across government agencies, or where legal and operational autonomy prevents centralisation. Its difficulties include schema mapping, inconsistent identifiers, semantic conflicts, local availability differences, incompatible security policies, and variable query performance.

Federation should not be confused with a distributed database in which components are usually designed as one integrated database. In a federation, autonomy and heterogeneity are central characteristics.

5.5 Cloud database architectures

Cloud databases may be delivered as infrastructure-managed by the customer, platform-managed services, or fully managed database services. Common capabilities include elastic provisioning, automated backups, multi-zone deployment, encryption, monitoring, and usage-based billing.

Cloud architecture does not automatically mean distributed architecture, although cloud services frequently use distributed storage and compute. Important design choices include:

• Single-region versus multi-region deployment.
• Shared-tenancy versus dedicated resources.
• Provisioned versus serverless capacity.
• Vertical scaling versus horizontal sharding.
• Managed relational, warehouse, key-value, document, graph, or vector services.

Cloud benefits include rapid provisioning, operational automation, and global reach. Risks include provider dependence, data-egress costs, service quotas, uncertain noisy-neighbour effects, jurisdictional issues, and the possibility that architecture becomes coupled to proprietary interfaces.

6. Workload Classification

Architecture should follow workload requirements rather than product fashion. A workload is characterized by its operations, data volume, concurrency, latency, consistency, and analytical complexity.

6.1 OLTP

Online transaction processing supports operational activities such as payments, reservations, order entry, and inventory updates. Typical characteristics are:

• Many concurrent users or services.
• Short, predictable transactions.
• Frequent inserts and updates.
• Strong correctness and integrity requirements.
• Low and consistent response-time expectations.
• Fine-grained access to current data.

OLTP schemas are often normalized to reduce update anomalies. Indexes are selected carefully because every additional index can increase write cost and storage use. Transaction isolation must prevent unacceptable anomalies while preserving concurrency.

6.2 OLAP

Online analytical processing supports reporting, trend analysis, forecasting, and exploratory queries. OLAP workloads commonly involve large scans, joins, aggregations, historical data, and relatively fewer but more computationally intensive queries.

Analytical systems often use dimensional models with fact and dimension structures, columnar storage, compression, partitioning, materialized views, and massively parallel processing. Data may be refreshed in batches or streams, so freshness must be stated explicitly. A dashboard based on data loaded six hours ago should not be presented as real-time without qualification.

6.3 HTAP

Hybrid transactional and analytical processing attempts to support operational transactions and analytical queries on closely integrated data. The objective is to reduce duplication and analytical delay, but the workloads compete for CPU, memory, storage bandwidth, locks, and cache space.

HTAP can be implemented through separated compute and storage, columnar replicas, change-data capture, workload isolation, or engines supporting multiple access paths. The central evaluation question is not whether a platform is labelled HTAP, but whether it meets both transactional service-level objectives and analytical requirements under realistic contention.

6.4 AI and machine-learning workloads

AI workloads use databases in several ways:

• Supplying curated training data.
• Storing features for model training and inference.
• Maintaining embeddings and vector representations.
• Recording predictions, feedback, and model lineage.
• Supporting retrieval-augmented generation.
• Managing evaluation and monitoring data.

Training is commonly throughput-oriented and may process large historical datasets. Inference may require low latency and high availability. Feature stores need consistency between training and serving data to reduce training-serving skew. Vector search introduces similarity queries, usually based on an embedding distance or similarity measure, but the appropriate index and metric depend on the embedding model and application.

AI does not replace database fundamentals. Data quality, provenance, access control, retention, and reproducibility remain necessary. A model can generate a plausible output from incorrect or unauthorized data, so database governance directly affects AI reliability.

7. Comparative Architectural Evaluation

A useful architecture evaluation considers at least the following dimensions:

• Consistency: how quickly and strongly updates become visible.
• Availability: whether the service remains usable during failures.
• Latency: response time, including tail latency at high percentiles.
• Scalability: ability to handle increased load or data volume.
• Elasticity: speed and automation of resource adjustment.
• Autonomy: local control over data and operations.
• Governance: ability to enforce policy, lineage, retention, and audit.
• Security: protection against unauthorized access or alteration.
• Cost: infrastructure, licensing, operations, migration, and egress.
• Recoverability: ability to restore service and data after failure.

No architecture dominates on all dimensions. Centralization often simplifies control; distribution improves locality and resilience but increases coordination complexity; federation preserves autonomy but creates semantic challenges; cloud services accelerate delivery but may increase external dependency.

A decision should begin with workload and risk requirements. For example, a payment ledger may prioritize strict integrity and recoverability, while a global recommendation service may prioritize low-latency regional reads and eventual convergence. These are different engineering problems even if both use databases.

8. Illustrative Case: A Global Retail Platform

Background

Consider a fictional retailer, Northstar Retail, operating physical stores, an e-commerce site, a mobile application, and a data-science division. It sells in several countries and must comply with local privacy and financial regulations.

Problem

The company’s original centralized relational database supports order processing but has become a bottleneck. International customers experience high latency, analytical reports interfere with order transactions, and data scientists copy extracts into uncontrolled environments. Management wants near-real-time inventory visibility and machine-learning recommendations.

Analysis

The workload is mixed. Order creation, payment authorization, stock reservation, and returns are OLTP operations requiring strong integrity. Sales reporting and demand forecasting are OLAP workloads. Inventory dashboards require fresher data than the nightly warehouse load. Recommendations and fraud detection are AI workloads with different latency and feature requirements.

A single database cannot necessarily optimize all these workloads simultaneously. Replicating analytical queries onto the primary transaction server would create resource contention. Fully independent systems, however, could produce inconsistent inventory views unless data movement and freshness are explicitly managed.

Approach

Northstar adopts a controlled polyglot architecture:

1. A relational OLTP database remains the authoritative system for orders, payments, and stock reservations.

2. Read replicas serve selected geographically local read operations where the business can tolerate replica delay.

3. Change-data capture publishes committed changes to an event stream.

4. A cloud analytical platform stores historical facts for OLAP and forecasting.

5. A near-real-time inventory projection is built from operational events, with reconciliation against the authoritative database.

6. A feature store supports fraud and recommendation models, recording feature definitions and lineage.

7. Access is governed through identity-based roles, encryption, audit logging, retention rules, and regional data controls.

The design does not treat every derived store as authoritative. It documents freshness objectives, replay procedures, duplicate-event handling, and reconciliation processes.

Outcome

Transaction performance improves because large analytical scans leave the primary system. Analysts gain a scalable historical platform, while operational teams receive more current dashboards. However, the company must manage delayed events, schema evolution, model drift, cloud costs, and the possibility that a derived inventory view temporarily differs from the transactional truth.

Learning points

The case demonstrates that workload separation can be beneficial, but separation creates data-integration obligations. A distributed or cloud architecture is successful only when consistency expectations, ownership, recovery, security, and observability are explicitly designed. The best architecture is therefore a governed combination of patterns rather than an indiscriminate adoption of multiple technologies.

9. Governance, Ethics, and Regulation

Database architecture determines who can access information, where it is stored, how long it is retained, and whether actions can be audited. Security should include least privilege, strong authentication, encryption in transit and at rest, secure key management, vulnerability management, and monitoring.

Privacy principles commonly require purpose limitation, data minimization, accuracy, retention control, transparency, and appropriate safeguards for sensitive information. Distributed and cloud deployments complicate these duties because replicas, backups, logs, caches, and derived datasets may exist in several jurisdictions.

AI-related databases require additional attention to provenance, consent where applicable, bias, explainability, and deletion propagation. If a person’s data is removed from a source system, an organisation must determine whether and how that removal applies to analytical copies, features, embeddings, backups, and trained models.

Governance should be treated as an architectural capability, not as paperwork added after implementation. Data classification, lineage, ownership, quality rules, and access reviews should be built into the operating model.

10. Best Practices and Contemporary Directions

Effective database foundations support the following practices:

• Model business meaning before selecting products.
• Define authoritative sources for important data.
• Separate logical interfaces from physical implementation.
• Enforce integrity constraints close to the data.
• Design for failure, backup, restoration, and disaster recovery.
• Measure tail latency, not only average latency.
• Document consistency and freshness guarantees.
• Use workload isolation when OLTP and analytical operations compete.
• Automate schema migration, testing, and observability.
• Maintain metadata, lineage, ownership, and data-quality rules.
• Minimize privileges and audit sensitive access.
• Test cloud exit, portability, and cost assumptions.
• Treat AI data pipelines as production database systems with reproducibility and governance requirements.

Contemporary directions include cloud-native distributed SQL, serverless databases, lakehouse architectures, real-time analytical replicas, vector indexing, confidential computing, and automated database tuning. These developments are valuable when they solve a defined requirement. They do not remove the need to understand schemas, constraints, transactions, failure modes, and data semantics.

Chapter Summary

A database system combines data, a DBMS, users, applications, procedures, and infrastructure. Schemas describe structure, instances describe current state, and metadata describes data and its management context. The three-schema architecture separates external views, the conceptual schema, and the internal schema, enabling logical and physical data independence, although practical dependencies can remain.

Relational theory represents data through relations, attributes, tuples, domains, keys, and integrity constraints. Relational operations provide a formal basis for querying and optimization. Centralized, client-server, distributed, federated, and cloud architectures offer different balances of control, locality, autonomy, scalability, resilience, and complexity.

OLTP emphasizes short, concurrent, integrity-sensitive transactions. OLAP emphasizes large analytical queries and historical data. HTAP attempts to combine both, while AI workloads require training, serving, feature, vector, provenance, and governance capabilities. Architectural decisions should begin with workload requirements and explicitly evaluate consistency, latency, availability, scalability, security, governance, recoverability, and cost.

The Northstar Retail case illustrates that modern systems often combine architectures and workload-specific platforms. Such combinations can improve performance and agility, but they increase the need for data lineage, reconciliation, schema governance, security, and operational discipline.

Key Terms

Database: An organized collection of related data.

Database Management System (DBMS): Software that defines, stores, retrieves, protects, and administers databases.

Metadata: Data describing the structure, meaning, origin, quality, or governance of other data.

Schema: A formal description of database structure and constraints.

Database instance: The actual contents of a database at a particular time.

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

Conceptual schema: The logical enterprise-wide description of database data.

Internal schema: The physical description of how data is stored.

Physical data independence: Ability to change physical storage without changing logical views.

Logical data independence: Ability to change the conceptual schema while preserving required external views.

Relation: A relational-model structure consisting of attributes and tuples.

Tuple: A row or record in a relation.

Candidate key: A minimal set of attributes that uniquely identifies a tuple.

Foreign key: An attribute set referencing a key in another relation.

Referential integrity: The rule that references must correspond to valid referenced keys, subject to nullability.

Distributed database: A logically integrated database whose components reside at multiple networked sites.

Federated database: An integration of autonomous and possibly heterogeneous databases.

OLTP: Workload focused on frequent, short, concurrent operational transactions.

OLAP: Workload focused on complex analysis of large and often historical datasets.

HTAP: Workload or architecture intended to support transactional and analytical processing together.

Review Questions

Short Answer Questions

1. Distinguish a database schema from a database instance.

2. What is physical data independence? Give two examples.

3. Define a candidate key and a foreign key.

4. State two characteristics of an OLTP workload.

5. Why does federation create semantic integration challenges?

Long Answer Questions

1. Explain the ANSI/SPARC three-schema architecture and discuss how it supports data independence.

2. Compare centralized, client-server, distributed, federated, and cloud database architectures.

3. Explain relational integrity constraints and why they should not rely solely on application code.

4. Compare OLTP, OLAP, HTAP, and AI workloads in terms of data access, latency, consistency, and processing requirements.

5. Discuss the architectural and governance implications of separating operational, analytical, and machine-learning data platforms.

Analytical / Application Questions

1. A bank wants to run complex risk reports directly on its payment database during business hours. Analyse the risks and propose an architecture that protects transaction performance while meeting reporting requirements.

2. A multinational organisation must keep customer data under regional legal control but provide global management reporting. Design a federated or distributed approach and identify the principal consistency, privacy, and governance challenges.

3. An AI fraud-detection service requires low-latency features derived from recent transactions. Propose a data architecture, identify possible training-serving consistency problems, and specify controls for monitoring and recovery.

Multiple Choice Questions with Answers

Question 1

Which schema level describes indexes, partitions, and file organization?

A. External level

B. Conceptual level

C. Internal level

D. Business level

Correct Answer: C. Internal level

Question 2

Which change is primarily an example of physical data independence?

A. Renaming a business concept used by all applications

B. Adding an index without changing application views

C. Changing the definition of customer ownership

D. Removing a required attribute from an external view

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

Question 3

What is the defining property of a candidate key?

A. It may contain duplicate values

B. It is always a foreign key

C. It is a minimal superkey

D. It must be generated automatically

Correct Answer: C. It is a minimal superkey

Question 4

Which constraint ensures that a foreign-key value refers to an existing referenced key, when non-null?

A. Domain integrity

B. Entity integrity

C. Referential integrity

D. Presentation integrity

Correct Answer: C. Referential integrity

Question 5

Which workload is most associated with short, concurrent, update-intensive transactions?

A. OLTP

B. OLAP

C. Offline archival processing

D. Batch-only machine learning

Correct Answer: A. OLTP

Question 6

Which characteristic is most typical of OLAP systems?

A. Frequent single-row updates

B. Large scans and aggregations over historical data

C. Exclusive use of foreign keys

D. No requirement for data consistency

Correct Answer: B. Large scans and aggregations over historical data

Question 7

What is a central characteristic of a federated database system?

A. All data must be stored on one server

B. Component databases retain a degree of autonomy

C. It cannot integrate heterogeneous systems

D. It eliminates schema-mapping problems

Correct Answer: B. Component databases retain a degree of autonomy

Question 8

In the CAP perspective, the trade-off concerns system behaviour primarily during what condition?

A. A network partition

B. A routine index rebuild

C. A successful local query

D. A completed backup

Correct Answer: A. A network partition

Question 9

Why can running analytical queries on an OLTP primary database be problematic?

A. Analytical queries never use indexes

B. Analytical workloads can compete for resources with transactions

C. OLTP databases cannot enforce constraints

D. Transactions cannot be replicated

Correct Answer: B. Analytical workloads can compete for resources with transactions

Question 10

Which issue is especially important for AI feature stores?

A. Training-serving skew

B. Eliminating all metadata

C. Avoiding data lineage

D. Removing access controls

Correct Answer: A. Training-serving skew