At this point, we have done all we can with Entity-Relationship Diagrams (ERD). Most people will stop here because this is usually pretty good. However, another modeling style called Object Role Modeling (ORM) can display relationships that cannot be expressed in ERD. Therefore there are more normal forms beyond 5th. With Optimal Normal Form (OMF)
It is defined as a model limited to only simple (elemental) facts, as expressed in ORM.
7. Domain-Key Normal Form
This level of normalization is simply a model taken to the point where there are no opportunities for modification anomalies.
- A relation in DK/NF has no modification anomalies, and conversely.
- DK/NF is the ultimate normal form; there is no higher normal form related to modification anomalies
- Defn: A relation is in DK/NF if every constraint on the relation is a logical consequence of the definition of keys and domains.
- Constraint is any rule governing static values of attributes that is precise enough to be ascertained whether or not it is true
- E.g. edit rules, intra-relation and inter-relation constraints, functional and multi-valued dependencies.
- Not including constraints on changes in data values or time-dependent constraints.
- Key - the unique identifier of a tuple.
- Domain: physical and a logical description of an attributes allowed values.
- Physical description is the format of an attribute.
- Logical description is a further restriction of the values the domain is allowed
- Logical consequence: find a constraint on keys and/or domains which, if it is enforced, means that the desired constraint is also enforced.
- Bottom line on DK/NF: If every table has a single theme, then all functional dependencies will be logical consequences of keys. All data value constraints can them be expressed as domain constraints.
- Practical consequence: Since keys are enforced by the DBMS and domains are enforced by edit checks on data input, all modification anomalies can be avoided by just these two simple measures.
