Day 2 - Three-Level Schema and Modeling Stages

📌 Key Takeaways

• The ANSI/SPARC three-level schema divides a database into three layers, external, conceptual, and internal, to achieve data independence.
• Logical independence: even if the conceptual schema changes, the external schema (application programs) is not affected.
• Physical independence: even if the internal schema (storage structure) changes, the conceptual schema is not affected.
• Modeling proceeds in the order conceptual → logical → physical, and the further along you go, the lower the level of abstraction and the more concrete it becomes.
• Normalization is done in logical modeling, and decisions about performance and storage method are made in physical modeling.

The Same Store Data, Looking Different to Each Person

A shipping clerk at the online store sees only the order number, recipient, and address. An accounting clerk sees only the order number, payment amount, and payment date. A DBA looks at which disk this data is stored on and with which indexes. In other words, the same data is viewed from three layers.

• The reason for separating layers is to keep a change in one layer from spreading to another.
• This structure was proposed by the SPARC committee of the American National Standards Institute (ANSI). That is why it is called the "ANSI/SPARC three-level schema architecture."

The Three-Level Schema

  Shipping screen    Accounting screen    Customer My Page
  ┌──────────┐      ┌──────────┐      ┌──────────┐
  │ External 1│      │ External 2│      │ External 3│     ← External schemas (many)
  └────┬─────┘      └────┬─────┘      └────┬─────┘
       └──────────────┬──┴─────────────────┘
                      │  External/conceptual mapping  → logical independence
               ┌──────┴───────┐
               │  Conceptual   │                          ← Overall structure of customers, products, orders
               │  schema       │
               └──────┬───────┘
                      │  Conceptual/internal mapping  → physical independence
               ┌──────┴───────┐
               │  Internal     │                          ← Files, indexes, blocks
               │  schema       │
               └──────────────┘

• External schemas differ per user, so there are many. There is one conceptual schema and one internal schema per database.
• The conceptual schema holds "what the entire organization stores." How it is stored (indexes, files) is the job of the internal schema.
• In terms of SQL, the external schema is closest to a view created with CREATE VIEW, the conceptual schema to table definitions, and the internal schema to storage structures such as tablespaces and indexes. It isn't an exact one-to-one correspondence; it's an analogy to aid understanding.

Two Kinds of Data Independence

The connection between schemas is called a mapping. Independence arises because the mapping absorbs the change.

In the online store example, it looks like this.

• Logical independence: A marketing_opt_in column was added to the customer table. The shipping screen doesn't use that column, so there is nothing to change.
• Physical independence: The DBA created a new index on the order table and moved it to another disk. The table structure (the conceptual schema) is unchanged, so the SQL keeps running as is.

⚠️ It's easy to mix up the name pairs. Logical independence is the ability to withstand changes to the conceptual schema, and physical independence is the ability to withstand changes to the internal schema. If you memorize "logical" as paired with "external," you'll get it wrong. The side that changes is the conceptual schema, and the side that is protected is the external schema.

Without Data Independence

• Every time the storage structure changes, application programs have to be rewritten.
• Even though each user needs a different view of the data, everyone is forced to share one structure.
• In the end, maintenance costs rise and data complexity increases. The three-level schema is a structure designed to reduce this problem.

The Three Stages of Modeling

If a schema is "the layers for viewing a finished database," then the modeling stages are "the order in which you build the database." The two are different concepts.

  High abstraction                                 Low abstraction
  ───────────────────────────────────────────────────────────▶
  Conceptual modeling   Logical modeling        Physical modeling
  (what exists)         (exactly what it        (how to put it on
                         looks like)             the DBMS)

• Going from conceptual → logical → physical, things become more concrete and closer to a specific DBMS.
• A logical model is not yet tied to a specific DBMS. That is why you can build a physical model from the same logical model, whether for Oracle or another DBMS.
• Normalization is the work of the logical modeling stage. Denormalization, which deliberately allows duplication for performance, is mainly considered in the physical modeling stage (covered in Week 2).

Putting Stages and Schemas Side by Side

The two concepts share a similar direction, "from abstract to concrete," which makes them good to ask about together.

• Both include the word "conceptual," which causes confusion. Conceptual modeling is the first stage of design, while the conceptual schema is the integrated data structure of the whole organization.
• There is no "external" among the modeling stages, and no "logical" in the three-level schema. If "logical schema" or "external modeling stage" shows up in an answer choice, the terms have been mixed up.

Where Modeling Fits in a Project

An information system construction project proceeds roughly in the order planning → analysis → design → development → testing → transition. The modeling stages overlap with this flow.

• The data model and the process model (business flow) are created together. For example, as you organize the online store's "receive order" process, the attributes of the Order entity come to light.
• This mapping varies a little from textbook to textbook. Some textbooks say the logical model is finished in the analysis phase, while others place the logical model in the design phase. The order (conceptual → logical → physical) is the same.

Tomorrow we'll look at the characteristics and classification of entities, the first element of the model.

📖 Glossary

• Schema : A definition describing the structure and constraints of a database.
• External schema : The database as seen by a single user or application program. There can be as many as there are users.
• Conceptual schema : The data structure of the entire organization, combining all users' perspectives into one.
• Internal schema : The definition of how data is actually stored on disk.
• Mapping : A link that corresponds two schemas so that a change on one side does not spread to the other.
• Data independence : The property that changing the schema of one layer does not affect the other layers.
• Normalization : The work of splitting entities to reduce duplication. Done in the logical modeling stage.
• EA (Enterprise Architecture) : A blueprint that organizes the business, data, and system structure of the entire organization at a glance.