Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 sqlserver. But at the end of the he shows how well this can be implemented in current rdbm 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 sqlserver?

share|improve this question

migrated from stackoverflow.com Dec 9 '11 at 13:14

This question came from our site for professional and enthusiast programmers.

    
I believe they are indicating that 6th normal form isn't realistic in current databases. You'd be better off backing down to 3-4th normal form; if you care about performance. –  xQbert Dec 9 '11 at 12:13
3  
Pinging @onedaywhen... –  gbn Dec 9 '11 at 12:20
5  
This sounds like a paper written by someone who has never had to develop (and maintain) a complex, real-world production database. If you can achieve a third to fourth normal form database, you're doing better than 90% of the people hacking databases together. –  Randy Minder Dec 9 '11 at 12:22
1  
@RandyMinder: If you don't know who Hugh Darwen is, try looking here: en.wikipedia.org/wiki/Database_normalization#Normal_forms and here: en.wikipedia.org/wiki/Hugh_Darwen . –  Mark Bannister Dec 9 '11 at 12:44
1  
@gbn: shame xQbert isn't over here, "You'd be better off backing down to 3-4th normal form" is a classic ;) –  onedaywhen Dec 9 '11 at 13:41

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

I suggest you download Dr Darwen's free PDF book "An Introduction to Relational Database Theory" from this page (which also includes a link the errata), specifically chapter 7 and the WIFE_OF_HENRY_VIII example. In summary, 6NF is always achievable but may not always be desirable in practice if it makes constraints harder to write.

To answer your question, it sounds like the suggestion is to create views, possibly with INSTEAD OF triggers, based on a base table then remove privileges from the base table so that users target the views only but that such implementation details are beyond the scope of his paper.

share|improve this answer
2  
Thanks, I'll put it on my list! Am currently reading "Sql and Relational Theory, How to Write Accurate SQL Code by Chris Date." Gotta finish that one first! –  NomenNescio Dec 9 '11 at 14:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.