Opsdeck Normalization Checker (keys, minimal cover, 2NF/3NF/BCNF, decomposition)
Candidate keys, minimal cover, 2NF/3NF/BCNF and lossless decompositions from FDs.
Try an example: EMP_PROJ: partial dependencies (not 2NF) (Elmasri & Navathe) · EMP_DEPT: transitive dependency (2NF, not 3NF) (Elmasri & Navathe) · EMP_PROJ split into EMP_LOCS + EMP_PROJ1: a lossy decomposition (Elmasri & Navathe) · R(A, B, C, G, H, I): key AG (Silberschatz) · Canonical cover of A → BC, B → C, A → B, AB → C (Silberschatz) · Dependencies mined from sample order lines (hypotheses)
How it works
Enter a relation and its functional dependencies (or paste sample rows and let the tool propose dependencies as hypotheses with support counts). You get attribute closures, all candidate keys, prime attributes, a minimal cover, the highest normal form with every violating dependency explained, a 3NF synthesis (Bernstein: lossless and dependency preserving) and a BCNF decomposition, each with the chase test for lossless joins and the dependency-preservation test. Any decomposition of your own can be tested too.
- Attribute closure X+ is computed by applying the dependencies until nothing changes. Attributes that never appear on a right side are in every key; the remaining candidates are searched by increasing size, skipping supersets of keys already found.
- Minimal (canonical) cover: right sides split into single attributes, extraneous left-side attributes removed, redundant dependencies removed (Silberschatz et al., section 7.4.3).
- Normal forms (first normal form is assumed): 2NF fails when a non-prime attribute depends on part of a candidate key; 3NF fails when X → A has X not a superkey and A non-prime; BCNF fails when X → A has X not a superkey. Checking the given dependencies suffices for 3NF and BCNF of the relation itself; parts of a decomposition use the dependencies projected onto them.
- 3NF synthesis (Bernstein): one relation per left side of the minimal cover, plus a candidate key if no relation contains one; relations contained in others are removed. BCNF decomposition splits on a violating X → Y into X+ and R − (X+ − X) until every part is in BCNF.
- Lossless join: the chase (tableau) test. Dependency preservation: each dependency of the cover is checked by growing the closure of its left side through the parts only (Ullman’s algorithm, no projection needed).
- Sample rows: every left side of up to three attributes is checked against every other attribute; only minimal dependencies that hold in all rows are proposed, with the number of row groups that share the left side as support. Unique attribute sets are listed as key hypotheses.
Sources
- Silberschatz, Korth & Sudarshan: Database System Concepts, 7th ed., chapter 7 (Relational Database Design) (McGraw-Hill)
- Elmasri & Navathe: Fundamentals of Database Systems, 7th ed., chapters 14–15 (Pearson)
- Bernstein: Synthesizing Third Normal Form Relations from Functional Dependencies (1976) (ACM Transactions on Database Systems 1(4))
- Aho, Beeri & Ullman: The Theory of Joins in Relational Databases (1979), the chase test (ACM Transactions on Database Systems 4(3))
Part of Opsdeck
-
Opsdeck Developer & ops workbench
One deck for CI failures, cron, traces, k8s events, API diffs, query plans and Terraform.
Related tools
- Opsdeck ER Diagram from SQL DDL (SVG, Mermaid) – Turn CREATE TABLE statements into an ER diagram with crow’s foot notation (SVG, Mermaid).
- Opsdeck Schema Diff & Migration Generator (PostgreSQL, MySQL, SQLite) – Compare two DDL versions: ordered migration, rollback script and data-loss warnings.
- CSV/Excel Chart Studio – Turn CSV, TSV or Excel data into line, bar, pie, scatter or histogram charts with SVG/PNG export.
How to use the Opsdeck Normalization Checker
Find candidate keys, the minimal cover and the normal form of a relation, see why it fails 2NF, 3NF or BCNF, and get lossless decompositions.
What it is for
Normalisation questions come up in database courses and in real schema reviews: is this table in third normal form, what is its key, how should it be split without losing data? This checker answers them from a relation and its functional dependencies, step by step, so you can follow the reasoning instead of trusting a verdict.
It also accepts sample rows. Dependencies that hold in every row are offered as hypotheses with a support count. A sample can only show that a dependency is false, never prove that it is true, so treat those results as suggestions to confirm with whoever knows the data.
Worked example
The first example is the textbook relation EMP_PROJ(Ssn, Pnumber, Hours, Ename, Pname, Plocation) from Elmasri & Navathe, with three dependencies: Ssn, Pnumber -> Hours, Ssn -> Ename and Pnumber -> Pname, Plocation.
The checker answers EMP_PROJ is in 1NF but not in 2NF; its candidate key is {Ssn, Pnumber}. It lists two violations: "Ename depends on Ssn, only part of the key {Ssn, Pnumber} (partial dependency)" and the same for Pname and Plocation, which depend on Pnumber alone.
The closures table shows why: {Ssn}+ is only Ssn, Ename, while {Ssn, Pnumber}+ reaches all six attributes. The 3NF synthesis then proposes three relations, (Ssn, Ename), (Pnumber, Pname, Plocation) and (Ssn, Pnumber, Hours). That is exactly the book's 2NF decomposition, and all three parts are even in BCNF. Both decompositions are marked lossless and dependency preserving.
The third example tests the book's bad split into EMP_LOCS(Ename, Plocation) and EMP_PROJ1. The chase tableau never gets an all-a row, so the tool warns that the decomposition is lossy.
How to read the result
- Normal form: the highest of 1NF, 2NF, 3NF and BCNF. First normal form (atomic values) is assumed.
- Violations: each failing dependency with the rule it breaks: partial dependency (2NF), transitive dependency (3NF) or a determinant that is not a superkey (BCNF).
- Minimal cover: the dependencies reduced to single attributes on the right, without extra attributes on the left or redundant rules.
- 3NF synthesis: always lossless and dependency preserving.
- BCNF decomposition: always lossless, but it can lose dependencies; they are listed.
- Chase tableau: for your own decomposition. A row of only
asymbols proves the join is lossless.
Tips and common mistakes
- With one-letter attributes you can write
AB -> C; with longer names separate them with commas. - List every attribute in the relation, even those in no dependency: they become part of every key.
- Street, City → Zip and Zip → City is the classic case where BCNF cannot keep every dependency; the checker shows the lost one.
- For mined dependencies, prefer those with higher support: a dependency seen in many row groups is better evidence than one seen once.
FAQ
How are candidate keys found?
Attributes that never appear on a right-hand side must be in every key. The rest are added in growing combinations, skipping supersets of keys already found, until the closure covers the relation.
Why does my 3NF result have an extra relation?
If no relation built from the dependencies contains a candidate key, the synthesis adds one with just the key, so that joining the parts is lossless.
Is the minimal cover unique?
No. Several minimal covers can exist; the checker reduces in a fixed order, so the same input always gives the same cover.
How many attributes can it handle?
Up to 30 attributes per relation. Decomposition parts are analysed with projected dependencies, which works for parts of up to 16 attributes.
Guide last updated:
For scripts and AI agents
- JSON API:
POST https://opsdecknormalizationchecker.usefulweb.app/api/v1/calculate(same calculation as this page) - Interface description: OpenAPI 3.1 · AI guide: llms.txt
- MCP server (Streamable HTTP):
https://opsdecknormalizationchecker.usefulweb.app/mcp· tool nameopsdeck_normalization_checker
Example request
curl -s -X POST https://opsdecknormalizationchecker.usefulweb.app/api/v1/calculate \
-H 'content-type: application/json' \
-d '{"relation":"EMP_PROJ(Ssn, Pnumber, Hours, Ename, Pname, Plocation)","fds":"Ssn, Pnumber -> Hours\nSsn -> Ename\nPnumber -> Pname, Plocation"}'