Database system structure and data models
Structure of a database system (three-schema architecture, schemas vs instances, data independence) and common data models (relational, hierarchical, network, object-oriented) with precise definitions, diagrams to draw, formulas, worked model answers, exam tips and quick checks.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Database system structure and data models explain how data is organized, stored and accessed. An engineer uses these ideas when choosing a database type for an application, writing schemas, and arguing for performance, portability or maintainability choices.
Key ideas
Define terms first: a database is a collection of related data stored for use by applications. A Database Management System (DBMS) is software that stores, retrieves and manages a database, providing interfaces for users and applications. A schema is the formal description of the structure of the database (types of data, relations, constraints). An instance is the actual data stored in the database at a moment in time. A data model is a collection of concepts and notations for describing data, their relationships, operations and constraints.
Three-schema architecture (the standard model of DBMS structure):
- External level (view level): the part seen by individual users or applications. Each external schema (view) defines a subset of the database and the way a particular user sees it.
- Conceptual level (logical level): a single global description of the whole database content, independent of any storage details and independent of any particular user view.
- Internal level (physical/storage level): how data are physically stored (files, indexes, record formats, pages).
Typical DBMS components you should know for system structure: query processor (parser, rewriter, optimizer, executor), storage manager (buffer manager, file and index manager), transaction manager, concurrency control and recovery manager, and the system catalog (data dictionary/metadata). Also know language layers: DDL (Data Definition Language) for defining schemas and constraints, and DML (Data Manipulation Language) for queries and updates.
Universities expect you to draw this: draw three horizontal boxes stacked vertically. Label top box "External level (many views)" and draw several small user-view boxes inside or to the left with arrows pointing to the top box. Middle box labeled "Conceptual level (single global schema)" with an arrow to/from the top box (show mappings). Bottom box labeled "Internal level (storage)" with an arrow to/from conceptual. Label the arrows "mapping" or "translation" and indicate "logical data independence" between the external and conceptual levels and "physical data independence" between the conceptual and internal levels.
Data independence — exact university-style statements:
- Logical data independence: the capacity to change the conceptual schema without requiring changes in external schemas or application programs. Assumption: mappings between external and conceptual schemas are maintained by the DBMS.
- Physical data independence: the capacity to change the internal schema (physical storage structures) without requiring changes to the conceptual schema. Assumption: the DBMS provides a translation layer between conceptual and internal levels.
Each of these is a property of the three-schema design: physical changes (e.g., new index or new file organization) should not force rewriting application programs; logical changes (e.g., adding an attribute) should not force changes to user views unless the view depends on that attribute.
Components of a data model (things to name and define):
- Structural part: the data structures for representing data (e.g., relations, records, trees). Define each structure before using it.
- Integrity constraints: rules that data must follow (e.g., keys, referential integrity). Define primary key and foreign key where used.
- Manipulation operators: allowed operations to query and update data (e.g., relational algebra operations, navigation operations in network model).
- Data semantics: meaning of constructs, e.g., what a relation or object represents.
Main data models (short exact definitions):
- Relational model: data represented as relations (tables). A relation is a set of tuples (rows) with the same set of attributes (columns). Each relation has a degree (number of attributes) and a cardinality (number of tuples). Integrity constraints include primary key (a minimal set of attributes that uniquely identifies each tuple) and foreign key(s) (attribute(s) in one relation that reference a candidate/primary key in another relation, enforcing referential integrity). Relational model emphasizes declarative queries and set-oriented operations.
- Hierarchical model: data stored in tree structures. Each record type has a single parent (except root). Navigation is parent-child; good for strictly hierarchical data such as XML-like trees.
- Network model: records connected by explicit set relationships (general graph of owner-member); supports many-to-many navigation using explicit pointers/sets. An owner record type is the parent of a CODASYL set type and a member record type participates as the child; one owner can have many member records within a set occurrence, enabling 1:N and M:N relationships via membership.
- Object-oriented data model: data items are objects that encapsulate state (attributes) and behavior (methods); supports inheritance, encapsulation, and complex types (nested objects, collections).
- Entity-Relationship (ER) model: a high-level conceptual model using entities (with attributes) and relationships; supports cardinalities (1:1, 1:N, M:N) and participation constraints (total/partial); often used for conceptual design and then mapped to the relational model.
- Document model: data stored as self-describing documents (e.g., JSON), supporting nested, variable schemas; good for content with evolving structure; queries typically path-based.
- Graph model: data as nodes and edges with properties; optimized for traversal queries (e.g., friends-of-friends, shortest paths); supports declarative graph query languages (e.g., Cypher, SPARQL).
Comparing models: list the properties to compare (structure, navigation vs declarative, support for complex objects, typical use-cases, ease of query optimization). Note assumptions: e.g., relational model assumes data normalized into tabular form; object model assumes application wants complex types and methods bound to data.
When a DBMS is described as a "data model implementation" (for example, a relational DBMS), it implements the structural part, the allowed operations, and enforces integrity constraints at the conceptual level while hiding most internal-level details.
Formulas
|R| = number of tuples (rows) in relation R — |R| is a count (unit: none), used when reasoning about cardinality or storage.
degree(R) = number of attributes (columns) in relation R — unit: none.
Record_Size_R = sum(attribute_i_size for i=1..degree(R)) — Record_Size in bytes; applies when estimating storage per tuple.
Storage_Pages = ceil(Record_Size_R * |R| / PageSize) — Storage_Pages in pages; PageSize in bytes; use when estimating number of disk pages used.
Selectivity(condition) = qualifying_tuples / |R| — unitless fraction; used in query cost estimation.
Fanout = average number of children per parent in hierarchical model — unitless; used for traversal cost estimates.
Note: PageSize is usually 4096 bytes or 8192 bytes in many systems; verify the value for your DBMS when estimating.
Worked examples
Example 1 — Model answer: Explain three-schema architecture and show how it enables logical and physical data independence.
- Define three schemas and draw the three boxes as described in Key ideas. Label arrows "external-conceptual mapping" and "conceptual-internal mapping".
- Give an example change at internal level: change file organization from heap file to clustered index. Explain: because conceptual schema and external views are mapped to internal via the DBMS, application programs and user views need not change. This is physical data independence. State the assumption that mappings and access methods are handled by the DBMS.
- Example change at conceptual level: add attribute PhoneNumber to Employee relation. If external views that do not use PhoneNumber remain valid, those views need not change; mapping updates can hide the new attribute from some views. This demonstrates logical data independence. Conclude by noting that full independence depends on how views are defined: if a view referenced every attribute, more changes are needed.
Example 2 — Model answer: Choose an appropriate data model for two applications and justify. Problem statement: For a banking ledger system (transactions, accounts, customers) and for a social network (users, posts, friends, likes), choose data models. Answer (model):
- Banking ledger: pick relational model. Justification: data is highly structured and fits tabular form (Accounts, Transactions, Customers). Strong consistency, ACID transactions (topic later) required. Relational integrity constraints (keys, foreign keys) model account-transaction relationships clearly. Draw relation schemas: Customer(CustID PK, Name, Address), Account(AccNo PK, CustID FK, Balance), Transaction(TxID PK, AccNo FK, Date, Amount).
- Social network: pick either graph/NoSQL document or object-oriented model depending on requirement. If queries are graph-traversal heavy (friends-of-friends), a graph or network-style model is better; if posts have variable nested content, a document/object model is better. Justify by matching navigation patterns and schema flexibility. Conclude with trade-offs: query patterns, consistency needs, and schema flexibility guide choice.
Common mistakes
- Claiming that logical data independence is always easy — it is often harder than physical independence; mention typical exam expectation: explain why.
- Mixing data model operations: stating that relational model uses navigation pointers like hierarchical/network models do.
- Failing to draw or label the three-schema diagram properly (missing mapping arrows or mislabelled levels).
For your exam
Question types you will see: definitions (define DBMS, schema, instance, data model), short notes (compare models; explain data independence), draw-and-explain (draw three-schema architecture and explain mappings), scenario-based design (choose an appropriate model and justify), and short derivations (storage page estimates using formulas above). What a full-marks answer includes:
- Clear definitions of each term used (schema, instance, data model, each model named).
- For diagrams: clean boxes, labels, arrows for mappings, and notes on where data independence sits.
- For comparisons: list 3–4 criteria (structure, navigation vs declarative, typical use-cases, integrity support) and apply them to each model.
- For scenario questions: state assumptions about workload and constraints (e.g., read-heavy, need for consistency), select model, provide schema sketch, and justify with at least two bullet points of reasoning.
Quick check
- What is the difference between a schema and an instance?
- Name the three levels of the three-schema architecture.
- Which data model is best for strictly hierarchical data and why? Answers: A schema is the formal structure/definition of the database; an instance is the actual data at a point in time. — External (view) level, Conceptual (logical) level, Internal (physical) level. — The hierarchical model; because it stores records in a tree structure with single-parent relationships and direct parent-child navigation.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?