Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 79 ยท Database Design Theory

Schema Design Strategies and Schema Integration

Compare top-down, bottom-up, inside-out and mixed schema design, then learn how to integrate two schemas by resolving naming and structural conflicts.

You know how to check whether a schema is good, with functional dependencies and normal forms. That leaves an earlier question: where does a schema come from in the first place? A small app can be designed in an afternoon, but a hospital, a bank or a merged company has hundreds of entities and many people with different views of the data. For work of that size you need a strategy, and you need a method for the common case where two databases that already exist must become one.

This lesson covers the four classic design strategies and the four steps of schema integration.

Four ways to approach a design

A design strategy answers one question: in what order do we discover the pieces of the schema? The four traditional answers are below.

Four design strategies

Top-down Big concepts first, then refine.
Bottom-up Attributes first, then group them.
Inside-out Start at a core, spread outward.
Mixed Split the problem, combine the parts.

Real projects often use a mix.

1 / 3

Top-down

You start with the broadest picture and refine it. A first sketch of a university might contain just Person, Course and Building. Then you split Person into Student and Instructor, add the relationship teaches, and attach attributes to each entity. This is the natural style of ER modelling and its extensions: the generalization and specialization steps are exactly top-down refinements.

Strengths: the design always has a coherent overall shape, and non-technical stakeholders can review the first sketch. Weakness: it needs a good understanding of the whole domain at the start, and it is easy to miss attributes that only show up in the details.

Bottom-up

You start from the smallest pieces: the individual attributes and the facts that connect them. You list every field the application needs, write down the functional dependencies between them, and then group attributes into relations. This is the style of normalization by synthesis that you have already met.

Strengths: nothing is forgotten, and dependencies are explicit. Weakness: with hundreds of attributes, the dependencies become unmanageable, and the overall structure is easy to lose.

The next program is a deliberately simplified bottom-up designer. Each fact says that a set of attributes determines others; each fact becomes one relation, and foreign keys appear wherever a relation holds another relation's key.

Python Bottom-up: attributes and facts to relations

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The result is the familiar four-table design: employees belong to a department, and the assignment table links employees to projects. Notice the two checks the program makes for you: no attribute is left unplaced, and every foreign key is found by looking for another relation's key inside a relation. Real tools add the minimal cover and key analysis from attribute closure, which this sketch skips.

Inside-out

Inside-out is a variant of bottom-up. You begin with the one or two concepts you understand best, the core, and then keep adding whatever is connected to the part already designed: the next most related entity, then the one after. A library design might start from Book, add Author and Loan, then Member, and later Branch and Fine.

Strengths: it works when you discover requirements gradually, and each step is small. Weakness: there is no global plan, so the schema can grow lopsided around the core.

Mixed strategy

A mixed strategy splits the problem into sub-problems, designs each one with whichever style suits it, and then combines the sub-schemas. A typical split is a top-down skeleton of the main modules (orders, inventory, staff), followed by bottom-up detail design inside each module. The final step is to integrate the parts, which is where the second half of this lesson picks up.

In practice, the best results usually come from a mixed strategy. Top-down gives you the map, bottom-up and normalization check the details, and several rounds of review keep both consistent.

Why schemas need integrating

Integration is needed in two situations:

  • View integration. Several teams each design a schema for their view of the application (billing, shipping, marketing). The overall schema is the combination of all views.
  • Database integration. Two or more existing databases, say after a company merger, have to be unified into one global schema.

In both cases, the schemas were designed independently, so the same real-world thing is described differently in each. The job is to find those differences and resolve them.

The four steps of schema integration

  1. Pre-integration. Study the input schemas, decide their order and the strategy (all at once, or two at a time), and collect the information you need about each: meaning of the entities, constraints and how the data is used.
  2. Comparison. Find correspondences between the schemas and detect conflicts.
  3. Conformation. Resolve each conflict by changing the schemas so that they become compatible.
  4. Merging and restructuring. Merge the conformed schemas into one, then remove redundancy and check the result, for example against the normal forms.

Kinds of conflicts

Naming conflicts

  • Synonyms: two names for the same thing. Client in one schema and Customer in the other.
  • Homonyms: one name for different things. status means "marketing status" in one schema and "account status" in the other. Merging them by name would silently mix two meanings.

Structural conflicts

  • Type conflicts: the same concept is modelled with different constructs, such as an entity in one schema and an attribute in another (Department as an entity, versus dept_name as an attribute of Employee).
  • Dependency conflicts: the same relationship has different cardinality: one-to-many in one schema, many-to-many in the other.
  • Key conflicts: different primary keys for the same entity, such as a generated number in one schema and a natural key in another.
  • Behaviour conflicts: different rules on update and delete for the same relationship. One schema deletes dependents with the parent, the other forbids deleting.

Domain conflicts also appear at the attribute level: the same attribute has different data types, units or encodings (an integer customer number in one place, text in another; prices in euros versus dollars).

Conformation: decisions, not algorithms

Resolving conflicts needs human knowledge of what the data means. Software can detect many conflicts (same name, different type; different name, same key), but it cannot decide that Client and Customer are the same concept. The decisions are recorded as a mapping, and a program can then apply them mechanically.

Python Integrating two schemas by applying conflict decisions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The dictionaries in the middle are the output of the comparison step. They list synonym decisions (Client and Customer, Item and Product, client_no and cust_id), homonym decisions (the two status attributes become marketing_status and account_status), and a domain decision (customer numbers are integers). The loops after them are the merging step. The note beside customer_id reports the type conflict that was resolved, and name shows what a correct synonym merge looks like: name and full_name are now one attribute.

Merging and restructuring

After merging, check the integrated schema against three criteria:

  • Completeness: every concept and constraint of every input schema can be found in the result.
  • Minimality: each concept is represented once. Look for redundancy that appears when the schemas are overlaid, for example an attribute that can be derived from a relationship in the other schema.
  • Understandability: a person can still read the result. If merging produced an unreadable tangle, restructure: introduce generalizations (Customer and Supplier as kinds of Party) or split oversized entities.

Finally, run the integrated relations through the normal-form checks of the previous lesson, because the union of two good schemas is not automatically a good schema.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A designer lists every attribute the application needs, writes the functional dependencies between them and groups them into relations. Which strategy is this?

    Starting from attributes and dependencies and building relations upward from them is the bottom-up strategy.
  2. 2. Which strategy starts from the one or two best-understood concepts and keeps adding whatever is connected to what already exists?

    Inside-out grows the schema outward from a core, one related concept at a time.
  3. 3. In one schema status means the marketing status of a customer, in another it is the account status. What kind of conflict is this?

    One name with two different meanings is a homonym. Merging by name would mix them, so one or both must be renamed.
  4. 4. Which step of schema integration finds that Client and Customer may describe the same entity type?

    Finding correspondences and conflicts is the comparison step. Tools can suggest candidates, but a person confirms what the data means.
  5. 5. After merging two schemas, why check minimality?

    Overlaying schemas can produce duplicate or derivable information. Minimality checks that each concept appears once.

Summary

  • Top-down refines broad concepts, bottom-up groups attributes by dependencies, inside-out grows from a core, and a mixed strategy splits the problem and combines the parts.
  • Schema integration unifies several views or existing databases in four steps: pre-integration, comparison, conformation, merging and restructuring.
  • Naming conflicts are synonyms and homonyms; structural conflicts are type, dependency, key and behaviour conflicts; domain conflicts concern types, units and encodings.
  • Software can detect conflicts, but people decide how to resolve them; the decisions can then be applied mechanically.
  • Check the merged schema for completeness, minimality and understandability, then for normal forms.

Next: What Is a Transaction?.