09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE
Note: This
is a 11/23/17 revision of Part 3 of a three-part series that replaced all
of my previous posts on the subject (pages of which redirect here), in
order to further tighten integration with the formalization and
interpretation [1] of McGoveran's formalization and interpretation [1] of Codd's true RDM.
(Continued from Part 2)
"Is this table in 1NF?" is a common question in database practice. On the other hand, "What problems are solved by splitting street addresses into individual columns?", or What's the best way to store an array in a relational database does not seem to evoke associations with 1NF. This reveals poor foundation knowledge.
Part 1 introduced the poor understanding of 1NF and Part 2 provided a correct definition and explanation. Part 3 explains how 1NF can be enforced by the data sublanguage, which SQL does not.
Database Design Consistent with Intended Data Use
The Information Principle (IP) -- Codd's Rule 0 of the RDM [2] -- mandates all information in a relational database be represented in relations explicitly and in exactly one way -- as values of attributes defined on domains. Database relations must be in 5NF -- defined in terms of attribute dependencies [3] -- which requires that they are in 1NF -- defined in terms of simple domains and value atomicity [4]. 1NF ensures that FOPL is sufficient for data sublanguages to be declarative and decidable [5] and physically independent (PI) [6], 5NF ensures system-guaranteed logical validity, semantic correctness [7], and logical independence (LI).
No 1NF and no 5NF means no relations: some information -- namely, attribute components meaningful to users -- is represented implicitly and, thus, hidden from the DBMS, in violation of the IP and the RDM. Application access to that information requires a SOPL based data language, with loss of the relational advantages.
One implication is that database designers should make sure that all object properties of interest to users are represented by attributes defined on simple domains, and are not implicit in components thereof. Another implication is that it is impossible to ascertain whether a table is a R-table (i.e., if it displays a relation -- which must be in 1NF) by its sheer inspection. Going back to the table in Part 1,
=========================================(1) it has uniquely named columns and assuming that (2) ID represents the PK (3) no information is encoded in the order of rows and columns and (4) No values are missing, it is a R-table if and only if (5) NAME and ADDRESS are defined on simple domains and their components are not meaningful to users.
ID NAME ADDRESS
-----------------------------------------
1 Mark Tomers 56 Tomato Road
2 Fred Askalong 3277 Hadley Drive
3 May Anne Brice 225 Century Avenue
...
=========================================
Note: Tables such as
===========================================are often given as examples of non-R-tables on 1NF grounds. But while the design is poor, depending on how the domains of PHONE1, and PHONE2 are defined, it may not violate 1NF. If they are simple and have no components meaningful to users, the relation is in 1NF, otherwise it isn't. It's actually the missing values (and possibly the lack of a PK) that disqualify it as a R-table. Although Codd supported missing value "marks" -- not to be confused with SQL NULLs -- that is now considered a mistake (for a relational solution to missing data, see [8]).
LAST FIRST PHONE1 PHONE2
-------------------------------------------
Smith John 303-123-4567
Doe Mary 303-456-9933 303-456-9944
Johnson Bill 303-987-6543
...
===========================================
Enforcing 1NF
1NF has very limited meaning outside true RDBMSs. In SQL -- erroneously accepted in the industry as a relational data sublanguage (it is neither relational, nor just a data sublanguage), 1NF is a heuristic rule that is easy to treat inconsistently.
First, SQL DBMSs do not support true domains. Their built-in system data types (SDT) -- VARCHAR(n), CHAR(n), INTEGER, MONEY, DATE -- are essentially primitive domains with vendor-defined value ranges. Database designers cannot create custom domains, or derive such from SDTs.
Note: Primitive domains represent in the database abstract properties that are not observed directly, but are inferred from observed object properties. For example, people have names and addresses, from which a primitive domain VARCHAR(n), or more structured versions can be inferred, where the maximum n is vendor-defined.
Second, there is no CREATE DOMAIN SIMPLE option in SQL. Moreover, if the designer defines, for example, an attribute on a SDT, SQL has built-in functions
that can be used to subvert atomicity (e.g., YEAR() or MONTH()applicable to the primitive SDT DATE).
Note: According to McGoveran, such functions have two possible uses: (1) as a shorthand for a disjunction of values and (2) extraction of internal components to treat them as attributes in their own right.Both are useful, but we are sloppy about distinguishing between them. If (1) is intended, then it is consistent with 1NF.
"Consider the restriction name LIKE “"Jo%"”. If, for example, the permissible values of the NAME domain that begin with “Jo” are “John Smith” and “Joseph Jones”, this restriction can be understood as just shorthand for name = "”John Smith"” OR name = "“Joseph Jones”". This interpretation is reasonable even when we don'’t explicitly know all the permissible values of the NAME domain. If (2) is intended, expressions get more verbose. For example, consider the comparison name = "John Smith". If both first and last name are independently meaningful and 1NF is enforced, we have to convert NAME to (FIRST, LAST) and the comparison becomes first = "John" AND last = "Smith".
When 1NF is not enforced, if a FOPL-based data sub-language allows application access to components, it is tantamount to design changes that can have broad and unintended effects. If, for example, NAME should suddenly be treated in some relation as (FIRST LAST), NAME should be split into two attributes. But
What does that mean for the functional dependency (FD) between the primary key (PK) and NAME?
What if it turns out that the FD was really LAST and the PK?
What if NAME were referenced by a foreign key (FK) in some relation representing employee children?" --David McGoveran
Single-valued Attributes?
C. J. Date argues that value atomicity is not precisely definable and that a relation is in 1NF if it has single-valued attributes (SVA) (i.e., every attribute has a single domain value in every tuple). Thus, the table,
=============================================is purportedly not a R-table by Date's definition because PHONE is defined on a multi-valued attribute. To be a R-table, it would have to be defined on a SVA -- a simple RVD, with sets as values treated as atomic (e.g., {303-456-9933,303-456-9944}) by the data language.
LAST FIRST PHONE
---------------------------------------------
Smith John 303-123-4567
Doe Mary 303-456-9933,303-456-9944
Johnson Bill 303-987-6543
...
=============================================
Note: In which case PHONE values would not be union-compatible and the relation would not be amenable to normalization.
But "single-valuedness" is not an inherent property of the data, as is implicit in Date's position. Regardless of the corresponding domain's data type defined by the designer (e.g., RVD values that are sets of phone numbers, or a VARCHAR(n) domain's values that are concatenations of phone numbers, or values that are commalists of phone numbers), whether or not PHONE is a SVA is a function of whether it is consistent with its intended use (i.e., whether it has meaningful components) and if it does, it is not single-valued. That's how Codd's 1NF is defined.
References
[1] McGoveran, D., LOGIC FOR SERIOUS DATABASE FOLK, forthcoming.
[2] Pascal, F., Interpreting Codd: The 12 Rules.
[3] Pascal, F., Depends on the Dependencies: Normal Forms and the Conceptual-Logical Conflation.
[4] Pascal, F., Simple Domains and Value Atomicity.
[5] Pascal, F., The Interpretation and Representation of Database Relations V.1 (April 2017).
[6] Pascal, F., Physical Data Independence.
[7] Pascal, F., Object Orientation, Relational Database Design, Logical Validity and Semantic Correctness.
[8] Pascal, F., The Final NULL in the Coffin: A Relational Solution to Missing Data.
No comments:
Post a Comment