NL to Relational Schema Decomposition

These examples and illustrative results are independently authored teaching materials, not measured model results.

Use case

Convert order requirements into relational tables. The teaching requirements say each order belongs to one customer, each order has at least one line, and a line records product ID, quantity and unit price. An order may have a discount, but its per-line or per-order scope is unspecified. Make the relationships inspectable before generating DDL.

Mechanism

1. List entities, attributes and relationships, separating explicit requirements from inference.
2. State cardinalities, such as one customer to many orders and one order to many lines; record the undecided discount scope.
3. Derive keys and creation dependencies from confirmed relationships. Do not turn unknown rules into silent defaults.
4. Check the model against the requirements, then generate DDL using an explicitly chosen database dialect.

Bad example

Create every necessary database table directly from the order requirements above. Ensure business correctness and add the discount field without listing assumptions.

Good example

From the order requirements above, first model Customer, Order and OrderLine with attributes, cardinalities and requirement references. List discount scope as undecided; do not guess whether it is per line or per order. Propose keys and creation dependencies. Generate DDL only after the discount rule and database dialect are confirmed. For now deliver the model and open questions.

Why the change matters

Direct generation can harden an unconfirmed rule into columns and constraints. An intermediate model makes the customer/order/line relationships reviewable first. Recording the discount gap prevents a seemingly complete schema from hiding it.

Observable expectation

An illustrative model has Customer 1→many Order, Order 1→many OrderLine, and OrderLine.order_id referencing Order.id. Discount scope remains undecided.

Trace every foreign key to a stated relationship. Keep ‘at least one line’ as a separate implementation requirement: an ordinary foreign key does not enforce it. A vanished open question or an unsupported relationship fails this check.

Limits

Modeling does not replace business confirmation. Cross-table count rules may require transactions or application checks; DDL capabilities depend on the database. Business needs determine table count, without a universal three-to-seven-table cap. The source remains unconfirmed; related modeling methods are linked below.