From Model to Schema

The previous lesson counted thirteen fields appearing at the logical level without their name ever occurring in the model, and showed that rescuing a dropped constraint runs, again, through adding a field not present in the model. This lesson descends to the final level.

The physical schema makes the logical model storable. Its question is: how many decisions does this conversion require, how many of these decisions does the conceptual model determine, and who determines the ones it does not. The answer produces the course’s largest ratio, and it makes the course’s rule pay out one last time: a notation’s measure is not the symbols it carries but what it leaves to someone else because it does not state it.

The Questions the Physical Level Asks

Making a field storable requires answering four questions: which type, what length, is it nullable, what is its default value. Making a table storable adds two more questions: an index for the primary key and a character set. None of these six questions is optional; if no answer is given, a default takes its place, and a default is an answer too.

• VM14 — At the physical level, every field asks four decisions, every table two. The list is the common definition and is not expanded in this lesson.
• VM15 — A decision being invented means no fact of the conceptual model determines that decision. An invented decision is not a wrong decision; it is a decision whose source is not in the model.

"""M01/K06 common definition (excerpt): the number of decisions invented by the physical level."""
FIELD_DECISION = ["type", "length", "nullability", "default value"]
TABLE_DECISION = ["primary key index", "character set"]

LOGICAL = {
    "WorkOrder": ["key", "number", "opened", "status", "Technician_key"],
    "Technician": ["key", "name", "specialty"],
    "Part": ["key", "code", "name", "unit"],
    "Machine": ["key", "code", "type"],
    "Document": ["key", "type", "date", "WorkOrder_key"],
    "WorkOrder_Part": ["key", "WorkOrder_key", "Part_key"],
    "Technician_Machine": ["key", "Technician_key", "Machine_key"],
}
CONCEPTUAL_FACTS = 26      # 5 entities + 12 attributes + 4 relationships + 5 constraints


def physical(tables):
    field = sum(len(v) for v in tables.values())
    table = len(tables)
    return {"table": table, "field": field,
            "field_decision": field * len(FIELD_DECISION),
            "table_decision": table * len(TABLE_DECISION),
            "invented_decision": field * len(FIELD_DECISION) + table * len(TABLE_DECISION)}


P = physical(LOGICAL)
print("table          fields  field decisions  table decisions  total")
for name, fields in LOGICAL.items():
    fd = len(fields) * len(FIELD_DECISION)
    td = len(TABLE_DECISION)
    print(f"{name:19s} {len(fields):4d}  {fd:11d}  {td:12d}  {fd + td:6d}")
print(f"{'total':19s} {P['field']:4d}  {P['field_decision']:11d}"
      f"  {P['table_decision']:12d}  {P['invented_decision']:6d}")
print()
print("facts the conceptual model states:", CONCEPTUAL_FACTS)
print("decisions the physical schema invents:", P["invented_decision"])
print("ratio:", round(P["invented_decision"] / CONCEPTUAL_FACTS, 4))
print()
print("even if every decision has only two options, the number of physical schemas")
print("corresponding to the same logical model is:", 2 ** P["invented_decision"])
print("digit count:", len(str(2 ** P["invented_decision"])))

table          fields  field decisions  table decisions  total
WorkOrder              5           20             2      22
Technician             3           12             2      14
Part                   4           16             2      18
Machine                3           12             2      14
Document               4           16             2      18
WorkOrder_Part         3           12             2      14
Technician_Machine     3           12             2      14
total                 25          100            14     114

facts the conceptual model states: 26
decisions the physical schema invents: 114
ratio: 4.3846

even if every decision has only two options, the number of physical schemas
corresponding to the same logical model is: 20769187434139310514121985316880384
digit count: 35

System: the conceptual model states 26 facts. Notation: the physical schema carries 7 tables and 25 fields. Cost: writing this schema requires giving 114 decisions, and none of them is written in the conceptual model. The ratio is 4.3846, meaning roughly 4.4 times as many decisions as the model states are given outside the model.

The last line is this number’s counterpart in the notation’s own language. If a decision offers at least two options — nullable or not, yes or no — the number of physical schemas corresponding to the same logical model is two to the power of one hundred fourteen, a thirty-five-digit number. The real number of options is larger than this, because type and length take more than two values. The logical model fits all of this set of schemas at once and prefers none of them over another.

• VM16 — The lower-bound count assumes every decision has only two options; the real number of options varies by storage product and is therefore not written.

Who Owns the Decision

The one hundred fourteen decisions do not disappear; only their owner changes. A decision has three possible owners. The model may have stated it as a fact. The decision may be derivable from another fact. Or a person, while writing code or building the schema, answers according to their own knowledge.

The distinction between these three is measurable, and the measurement shows two separate behaviors.

• VM17 — A decision’s owner is one of three: a fact written in the model, a result derived from another fact, or a person. When counted as a model fact, the conceptual model’s fact count grows; when counted as a derived decision, it does not.

"""Who owns the 114 decisions: carrying to the model and deriving from a rule are separate jobs."""
FIELD_DECISION = ["type", "length", "nullability", "default value"]
TABLE_DECISION = ["primary key index", "character set"]
LOGICAL = {
    "WorkOrder": ["key", "number", "opened", "status", "Technician_key"],
    "Technician": ["key", "name", "specialty"],
    "Part": ["key", "code", "name", "unit"],
    "Machine": ["key", "code", "type"],
    "Document": ["key", "type", "date", "WorkOrder_key"],
    "WorkOrder_Part": ["key", "WorkOrder_key", "Part_key"],
    "Technician_Machine": ["key", "Technician_key", "Machine_key"],
}
ATTRIBUTES = {"WorkOrder": ["number", "opened", "status"], "Technician": ["name", "specialty"],
              "Part": ["code", "name", "unit"], "Machine": ["code", "type"],
              "Document": ["type", "date"]}

DECISIONS = [(t, a, k) for t, fields in LOGICAL.items() for a in fields
             for k in FIELD_DECISION]
DECISIONS += [(t, None, k) for t in LOGICAL for k in TABLE_DECISION]


def owner(decision, settings):
    t, a, k = decision
    is_conceptual = a in ATTRIBUTES.get(t, [])
    if settings["field"] and is_conceptual and k in ("type", "length"):
        return "model"
    if settings["requiredness"] and is_conceptual and k == "nullability":
        return "model"
    if settings["foreign"] and a is not None and a.endswith("_key") \
            and k in ("type", "length"):
        return "derived"
    if settings["integrity"] and a == "key" and k == "nullability":
        return "derived"
    return "person"


SETTINGS = [
    ("conceptual model as is", dict(field=0, requiredness=0, foreign=0, integrity=0)),
    ("+ field for 12 attributes", dict(field=1, requiredness=0, foreign=0, integrity=0)),
    ("+ requiredness for 12 attributes", dict(field=1, requiredness=1, foreign=0, integrity=0)),
    ("+ foreign key derivation", dict(field=1, requiredness=1, foreign=1, integrity=0)),
    ("+ entity integrity", dict(field=1, requiredness=1, foreign=1, integrity=1)),
]
print("total decisions:", len(DECISIONS))
print()
print("configuration                      in model  derived  person  model facts  total")
for name, settings in SETTINGS:
    tally = {"model": 0, "derived": 0, "person": 0}
    for d in DECISIONS:
        tally[owner(d, settings)] += 1
    facts = 26 + tally["model"]
    print(f"{name:34s}  {tally['model']:7d}  {tally['derived']:10d}  {tally['person']:4d}"
          f"  {facts:12d}  {facts + tally['person']:6d}")

total decisions: 114

configuration                      in model  derived  person  model facts  total
conceptual model as is                    0           0   114            26     140
+ field for 12 attributes                24           0    90            50     140
+ requiredness for 12 attributes         36           0    78            62     140
+ foreign key derivation                 36          12    66            62     128
+ entity integrity                       36          19    59            62     121

The table’s last column is this lesson’s most instructive number. In the first three rows the total stands fixed at 140: the facts the model carries and the decisions a person makes trade against each other one for one. Adding the type and length information of the twelve attributes to the conceptual model raises the fact count from 26 to 50 and lowers the decisions left to a person from 114 to 90. When requiredness information is added as well, facts reach 62, and the decisions left to a person reach 78. Enriching the model does not reduce the number of decisions, it changes who owns the decision — and this is a good trade, because a decision in the model is written, readable, and discussable.

The last two rows do something different, and they lower the total. The type and length of a foreign key field can be derived from the primary key it refers to; and further, the field forming a primary key cannot be null — this is a direct consequence of the entity integrity established in the Data Modeling and Relational Theory course. With these two derivations, the decisions left to a person drop from 78 to 59, and the total drops from 140 to 121. Derivation is different from trading: it genuinely reduces the number of decisions without enlarging the model, because it makes stating the same information twice unnecessary.

The Model’s Silence Makes a Decision Invisible

Each of the remaining fifty-nine decisions is a person’s answer, and this answer leaves no trace in the model. The consequence of this is not that the decision is wrong; it is that wrongness cannot be shown. Looking at a single field is enough.

• VM18 — The part code pool has eight codes, and only the first five had been seen at decision time.

"""A single invented decision: the length of the Part.code field. The model rules out no option."""
CODE = ["MK-01", "MK-002", "TR-08", "PL-7", "GK-1", "RD-1140", "HD-220-A", "SB-33012"]

print("length  rejected codes  which ones")
for length in range(4, 11):
    rejected = [k for k in CODE if len(k) > length]
    print(f"{length:6d}  {len(rejected):14d}  {', '.join(rejected) if rejected else '-'}")
print()
print("facts the conceptual model states about this decision: 0")
print("smallest safe length read from the data:", max(len(k) for k in CODE))
print()
seen = CODE[:5]
choice = max(len(k) for k in seen)
later = [k for k in CODE if len(k) > choice]
print("codes seen at decision time:", len(seen), "| chosen length:", choice)
print("codes arriving later:", len(CODE) - len(seen),
      "| rejected:", len(later), "->", ", ".join(later))

length  rejected codes  which ones
     4               6  MK-01, MK-002, TR-08, RD-1140, HD-220-A, SB-33012
     5               4  MK-002, RD-1140, HD-220-A, SB-33012
     6               3  RD-1140, HD-220-A, SB-33012
     7               2  HD-220-A, SB-33012
     8               0  -
     9               0  -
    10               0  -

facts the conceptual model states about this decision: 0
smallest safe length read from the data: 8

codes seen at decision time: 5 | chosen length: 6
codes arriving later: 3 | rejected: 3 -> RD-1140, HD-220-A, SB-33012

Of the one hundred fourteen decisions, this is just one: the length of the Part.code field. The conceptual model states zero facts about this decision; it states that the part code is unique, not how many characters it has. Because of this, every option between 4 and 10 is equally suited to the model, and none can be ruled out by looking at the model.

The only thing that rules out a decision is data, and the data is incomplete at decision time. The person making the decision saw five codes, the longest being six characters, and chose a length of 6. All three of the codes that arrive later are rejected. This is not a mistake, it is a guess — and the fact that it is a guess is not written in the model, because this row does not exist in the model at all. Five years later, someone looking at that field cannot tell whether the number 6 is a domain rule or one day’s guess.

From this follows the data-modeling counterpart of the course’s rule: every place the model is silent is a place of decision, and the decision made there cannot be discussed, because it does not appear in the model. The number one hundred fourteen is therefore not a measure of schema size but a measure of invisibility.

Summary

• The physical schema requires four decisions per field and two per table. Seven tables and twenty-five fields produce 100 field decisions and 14 table decisions, a total of 114 decisions.
• The conceptual model states 26 facts; the physical schema invents 114 decisions. The ratio is 4.3846, roughly 4.4 times what the model states.
• Even if every decision has only two options, the number of physical schemas corresponding to the same logical model is 2 to the power of 114, a thirty-five- digit number; the logical model fits all of them at once.
• Carrying a decision into the model is a trade and does not change the total: when field and requiredness information is added for 12 attributes, facts rise from 26 to 62 while the decisions left to a person fall from 114 to 78, and the total stays fixed at 140.
• Deriving a decision from a rule genuinely lowers the total: when the foreign key’s type comes from the key it refers to, and the key’s non-nullability comes from entity integrity, the decisions left to a person become 59 and the total becomes 121.
• A decision the model is silent about becomes invisible: the model states zero facts about the length of Part.code, the five codes seen at decision time lead to a chosen length of 6, and all three codes arriving later are rejected.

Course Wrap-Up

Ten lessons modeled a single maintenance shop and asked a single question: how many other systems can a notation not distinguish the system from. That was the course’s rule — a notation that does not state how many systems it cannot tell apart is counted as unmeasured. In every lesson, three numbers stood side by side: the system’s real fact count, the number of symbols the notation writes, and the cost between them.

Lesson	System	Notation	Cost
Notation Family and Loss	5 associations, 11 hidden fields	20 symbols	2048 systems fall onto the same diagram
Class Diagrams	the same 5 associations	25 and 31 symbols	64 systems remain at 25 symbols, 1 at 31
Component and Deployment Diagrams	6 classes, 203 partitions; 540 covering assignments	3 boxes + 2 links	the crudest drawing is consistent with 10 partitions; link symbols reduce 540 to 20, content symbols to 1
Use Case Diagrams	20 facts: 2 actors, 3 scenarios, 5 associations, 3 ownerships, 7 internal steps	9 symbols	512 systems fall onto the same diagram; baseline ambiguity 128
Sequence Diagrams	24 traces	1 diagram, 1 trace	coverage 1/24 = 0.0417; four diagrams 0.1667
Activity Diagrams	10 tasks, 13 prerequisites	23 symbols, 1 partial order	168 total orders are accepted, a single order’s coverage is 0.006; a decision node carries it to 492
State Machine Diagrams	6 real sequences	10 symbols (4 states, 6 transitions)	under-acceptance 2, over-acceptance 2; adding two transitions gives 0 and 26 — thirteenfold
The Entity–Relationship Model	26 facts	26 written items	1,048,576 systems fall onto the same notation
Conceptual, Logical, and Physical Model	26 facts	7 tables, 25 fields	2 dropped constraints, 13 invented fields, 175 over-acceptances
From Model to Schema	26 facts	7 tables, 25 fields	114 invented decisions, 4.4 times what the model states

A second reading of the table is more valuable than the first: fixing a notation can make it more wrong. Adding two transitions to the state machine zeroed out the rejected sequence but raised over-acceptance thirteenfold. Demoting a fact from entity to attribute simplified the model but made eleven states unrepresentable. Rescuing a dropped constraint required adding a field not present in the model at all. Adding detail is not always a gain, and it is not added without counting the loss.

The third and most expensive result came from data modeling: a level of abstraction both forgets and invents, and the two are counted separately. On the way down from the conceptual model to the physical schema, two of the five constraints dropped, but 114 decisions appeared that never were written in the model. The two forgotten constraints were visible, because they had once been written in the model; the 114 invented decisions are invisible, because they were never written at all. The most dangerous thing about a notation is not what it erases, but what it never states.

No diagram was drawn throughout the course. Every notation was built and counted as a list of the information items it carries; what was measured was not the picture’s beauty, but how many systems the picture collapsed onto one picture. Which diagram type suits which question, and what it costs to keep a set of diagrams current, were not this course’s questions; both were measured in the Software Architecture curriculum’s Architectural Decisions and Documentation course and were not carried over here.

The next course, Advanced Algorithms and Problem Solving, carries the same discipline forward on a different notation. There, reducing a problem to a recognized algorithmic pattern is taught — divide and conquer, greedy choice, dynamic programming, backtracking. Reduction is itself a choice of notation: fitting a problem into a pattern makes some of its aspects invisible, and if the pattern’s conditions are not met, the answer comes out silently wrong. This is why a justification stands at the center of that course: showing that the optimal-substructure and overlapping-subproblem conditions are satisfied. The rule here holds there too — until what a reduction drops is written down, the reduction is counted as unmeasured.