Is bottom-up design a single algorithm?

No. It is a design approach; particular synthesis or normalization algorithms provide specific steps and guarantees.

Does it replace conceptual modeling?

No. The perspectives can complement each other and reveal different omissions.

Why are dependencies important?

They express which facts are fixed by which identifiers or attribute combinations, guiding how relations are organized.

AI Glossary / Data engineering and architecture / Intermediate

Bottom-up design

Definition

Bottom-up design, in the relational database context used here, develops a schema from detailed attributes and their dependency rules. A designer starts with the information that must be represented, analyses how attributes determine one another and constructs suitable relations. It is commonly associated with relational synthesis and normalization, rather than starting solely from a high-level entity diagram.

What it means

The phrase has broader meanings in software and conceptual modeling. This entry follows the supplied glossary's database-design scope: beginning with the attributes of interest, sometimes represented as a universal relation, and using dependency analysis to organize them.

A universal relation is a design abstraction containing the relevant attributes. It need not be a giant production table that the team actually builds. The objective is to understand which facts belong together and which repetitions would cause update problems.

Bottom-up work can complement top-down modeling. Entity and relationship discussions help explain the business, while detailed dependency analysis tests whether the proposed tables faithfully represent those rules. Teams can move between these perspectives rather than choosing one exclusively.

How it works

Collect the attributes required by the application and define their meanings. Identify functional dependencies from valid business rules: for example, whether a course identifier determines one course title in the intended time scope.

Analyze candidate keys and simplify the dependency set without changing its implications. A synthesis method can then construct relations from that information. The chosen method and additional checks determine properties such as normal form, dependency preservation and lossless reconstruction.

Review the resulting schemas for meaningful row definitions, missing attributes and unintended combinations. Formal correctness depends on the dependency assumptions, so these assumptions should be verified with domain experts rather than inferred uncritically from a small sample.

Finally, translate the logical design into an implementation, considering constraints, transactions, access patterns and migration. Normalization does not itself choose all indexes or prove that a workload will meet its performance target. Those are further design decisions.

Three practical examples

A team replaces several spreadsheets by cataloging their fields, agreeing identifier rules and synthesizing relations that reduce repeated facts.
A database designer checks whether a proposed order table repeats product descriptions because ProductID determines those descriptions independently of orders.
An AI-assisted design review requires explicit functional dependencies before accepting generated keys and table groupings.

Think of it this way

Think of organizing a workshop by examining the tools, parts and their relationships before deciding which drawers and cabinets are needed. The detailed inventory guides the arrangement.

The analogy is limited because database design depends on semantic rules, not just physical similarity. Two attributes appearing next to each other in a spreadsheet do not necessarily belong in one relation. The designer must understand what each fact means and which values can legitimately vary together.

Worked example

Constructing three relations

Consider an invented training dataset with LearnerID, LearnerName, CourseID, CourseTitle and CompletionDate. Assume each learner identifier determines one current learner name, each course identifier determines one current course title, and each learner-course pair has at most one completion date in this simplified model.

The dependencies are LearnerID → LearnerName, CourseID → CourseTitle and LearnerID, CourseID → CompletionDate. They suggest Learner(LearnerID, LearnerName), Course(CourseID, CourseTitle) and Completion(LearnerID, CourseID, CompletionDate).

The completion relation contains the original relation's identifying pair. The learner and course details can be joined through their identifiers without inventing alternative names or titles under the stated dependencies. Each given dependency is represented locally.

However, the model is wrong if a learner can complete the same course several times and every completion must be recorded. An attempt identifier or other justified episode identity would be needed. The method does not discover that missing requirement automatically; it faithfully works from the rules supplied to it.

Illustrative case study

A fictional professional association asks an assistant to design a database from exported registration spreadsheets. The assistant treats Email → PersonID as a business rule because every email happens to be unique in the extract.

Staff explain that family members can share an email address and that addresses can change. The team removes the unsupported dependency, clarifies person identity and keeps contact information under an appropriate model. It then repeats the dependency analysis before generating the schema.

The case shows why bottom-up design is not merely automatic pattern extraction. Observed uniqueness can suggest a question for the business, but it does not establish a durable rule. Design quality depends on correct, complete rules.

Common misconceptions and mistakes

Bottom-up design requires loading everything into one permanent table first. A universal relation can remain a conceptual attribute inventory.
Normalization can infer the organization's complete rules from sample data. Samples can miss exceptions and cannot establish every intended dependency.
A normalized logical design settles physical performance. Indexes, workload patterns and operational constraints still require evaluation.

Business use

This approach is valuable when detailed data already exists but its structure and ownership are unclear. It can expose repeated facts, ambiguous identifiers and dependencies hidden in legacy reports.

Start with a field dictionary and a list of claims that business owners can confirm or reject. Specify whether facts are current-state or historical, and distinguish an entity from an event or episode. These choices often matter more than selecting a normal-form label early.

For an AI project, use generated dependency suggestions as review material. Ask the assistant to show the assumptions behind each key and decomposition, then test counterexamples. Reconcile the resulting schema with required reports and workflows so the formal design supports the work users actually need to do.

Check your understanding

A sample shows that every email address appears once. Is that enough to make Email a candidate key for a person database?

Answer: No. The business must guarantee the required uniqueness and stability for the intended model. Shared addresses or multiple historical addresses can invalidate the assumption.

Related terms

Learn next

Read relational synthesis and minimal covers. Choose one spreadsheet, define each row's meaning and list its genuine dependency rules before suggesting tables or keys.

Sources

Elmasri and Navathe, Relational Database Design Algorithms, author slides hosted by Purdue: https://www.cs.purdue.edu/homes/bb/cs448_Fall2017/lpdf/Chapter15.pdf. Primary textbook materials describe relational synthesis as bottom-up design.

Uppsala University, Normalization assignment: https://www.it.uu.se/research/group/udbl/dbt-ht2005/ass1_2005_ht.pdf. Primary teaching comparison of design approaches.

Silberschatz, Korth and Sudarshan, Relational Database Design: https://www.db-book.com/slides-dir/PDF-dir/ch7.pdf. Primary dependency and normalisation material. The training and association cases are original illustrations.

Previous: Botnet Next: Bound pronoun