This is a followup to previous question at this link:
http://www.experts-exchange.com/Database/Miscellaneous/Q_22756249.htmlI am posing more specific questions here, but am also interested on any feedback about my findings from that question, which I've copied below. The topic at hand is 1NF, specifically as it pertains to database designs with Oracle, DB2, Access, SQL Server, or MySQL.
Q1. Can we definitively say a table is 1NF (without inspecting its data)? If yes, explain how a comma-delimited list could possibly be a consideration. If no, explain how a relation could possibly be 1NF, since it has no data to inspect.
Q2. I have a table with a unique constraint that does not permit nulls. If it is not definitively 1NF, why not? If it is definitively 1NF, does permitting nulls change that?
Summary of previous thread
--------------------------
---------
1. There is consensus that 1NF is a characteristic of design. Particularly, it applies to the design of relations which are, by definition, always 1NF. But 1NF can be extended to database designs in so much as they represent relations.
2. SQL databases have diverged significantly from the relational model.
3. There is still some controversy about nulls in relations. Purests claim they are not permitted and therefore any database that permits nulls is not 1NF. Pragmatists consider nulls more of a content issue than a design issue and therefore do not believe they are relevant to 1NF. Personally, I find the pragmatic view hard to accept. Nullable columns permit some rows to represent more values than others, which is not allowed in a relation.
4. 1NF stipulates that every row in a relation must be unique. Extended to logical database design, that means each ENTITY must have a primary key. However, a 1NF does not stipulate how uniqueness is enforced, therefore a TABLE needn't have a primary key constraint if uniqueness is enforce some other way.
5. Many experts (including the referenced Joe Celko) claim that representing a 1:many relationship with repeating groups violates 1NF. However, the literature suggests this is not the case. The misconception seems to originate from the assertion that a relation may not contain a VARIABLE NUMBER OF repeating groups. That amounts to another way for a table to represent more values in some rows than others, which is not allowed in a relation. However, while a fixed number of repeating group is often a limiting design, it does not appear to contradict 1NF.
6. Another way to represent a 1:many relationship is using an embedded list, for example a list of items separated by commas. Some argue that this violates 1NF since the list is not "scalar" or "atomic". That argument is problematic on two counts. First, because the very notion is ambiguous--what is scalar for one
purpose may not be scalar for another. Second, the criteria is based on content, which neither a relation nor a table design have. Since accepting the argument leads me to 2 dead ends, I take exception. It makes more sense that, while the embedded list may be a result of poor table design or a weak domain definition, 1NF does not preclude it.
