Sql-server – 6th normal form, recomposition query, efficient implementation

database-designnormalizationrelational-theorysql server

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the language tutorial D. I understand how to convert all this logic into SQL Server. But at the end of the he shows how well this can be implemented in current database management systems, and then I see this part I need to implement:

Recomposition query: can be done but likely to perform horribly.
Might be preferable to store PERS_INFO as a single table under the
covers, so that the tables resulting from decomposition can be
implemented as mappings to that. But current technology doesn’t give
clean separation of physical storage from logical design. Perhaps
something for the next generation of software engineers to grapple
with?

It suggest storing PERS_INFO as a single table under the covers, but what does that really mean? How would I implement that in SQL Server?

Best Answer

Might be preferable to store PERS_INFO as a single table under the covers, so that the tables resulting from decomposition can be implemented as mappings to that. But current technology doesn’t give clean separation of physical storage from logical design.

He's talking about the dbms storing it as a single table, not you storing it as a single table.

The relational model itself says nothing about physical storage. It only requires that "the database be perceived by the user as tables. . . . At the physical level, in fact, the system is free to store the data any way it likes . . ." (An Introduction to Database Systems, 7th ed., CJ Date, p 61)

So Darwen is saying that if the database designer declares a set of 6NF tables, along with their

candidate keys,
distributed keys,
foreign keys,
foreign distributed keys,
and so on,

then the dbms is free to

implement that set of 6NF tables as a single table in a SQL tablespace,
hide that single-table implementation from the user, and
expose to the users a set of updatable views that are indistinguishable from the 6NF tables the database designer declared.

Having said all that, you might be able to work around the lack of dbms support by creating a single table on top of the covers (so to speak), and create a set of updatable views that are indistinguishable from your 6NF design. Revoke permissions on the single table, and require all users to use only the updatable views. What you can't do is have the dbms take care of all those details based solely on a series of logically correct, 6NF CREATE TABLE statements.

Related Solutions

Is this table in third normal form

Here's one attempt (this is not a day to day activity for me, so I may be doing some weird errors below):

A and B are clearly candidate keys of R. Therefore C is the only non-prime attribute of R

R is in 3NF iff:

a) R is in 2NF

b) Every non-prime attribute of R (C) is non-transitively dependent on every superkey of R.

The superkeys of R is (A, B), (A, C), (A, B, C)

By reflexivity (A, C) -> C AND (A, B, C) -> C By composition of A->C and B->C we get (A, B) -> (C, C), i.e. (A, B) -> C

C is therefore non-transitively dependent on every superkey of R, and hence R is in 3NF.

Transforming table to third normal form

I would take following approach.

From left side of the relations we see 3 primary keys:

Key AB with AB->CDE relation is represented in LAB table.
Key C with C->D relation is represented in LC table.
Key D with D->E and D->B relations are represented in LD table.

Does it make sense?

CREATE TABLE LD (
  D INT,
  B INT, /* B can be omitted here, and derived from AB */
  E INT,
  CONSTRAINT LD_PKEY PRIMARY KEY ( D )
);

CREATE TABLE LC (
  C INT,
  D INT,
  CONSTRAINT LC_PKEY PRIMARY KEY ( C ),
  CONSTRAINT LC_D_FKEY FOREIGN KEY ( D ) REFERENCES LD

);

CREATE TABLE LAB (
  /* Attributes D and E are dependent on C, so we don't store them here */
  A INT,
  B INT,
  C INT,
  CONSTRAINT LAB_PKEY PRIMARY KEY ( A, B ),
  CONSTRAINT LAB_C_FKEY FOREIGN KEY ( C ) REFERENCES LC
);

Best Answer

Related Solutions

Is this table in third normal form

Transforming table to third normal form

Related Question