Follow @DBDebunk Follow @ThePostWest


Version 3 of paper #4 in the PRACTICAL DATABASE FOUNDATION series is now available to order here. The Key To Relational Keys: A New Understanding is a completely new, re-written version that incorporates David McGoveran's formalization and interpretation of Codd's true RDM and introduces a new perspective. It is distinct from and supersedes all previous versions.

Abstract

The dual theoretical foundation of the RDM — simple set theory (SST) expressible in first order predicate logic (FOPL) — is applicable to database management because it is a theory of unique objects and objects are unique in the real world that databases represent. In the real world objects are uniquely identified by (1) a combination of one or more defining properties and/or (2) names assigned to them as members of various groups. A database relation is a formal representation of an object group, with tuples representing (facts about) and attributes representing properties and/or names of object members. Relational keys are attributes that represent formally in the database those identifying properties and names.

Their fundamental database role notwithstanding, relational keys are poorly understood. 70% of hits @dbdebunk.com are about keys and misconceptions about their necessity, what kinds of keys are relational, their functions, their selection, and so on, abound.
This paper defines and explains:

• The relational key concept
• The kinds of relational keys, their properties and functions
• The formal PK mandate
• PK selection
• RDBMS key support

and debunks common misconceptions.



--------------------------------------------------------------------------------
I have been using the proceeds from my monthly blog @AllAnalytics to maintain DBDebunk and keep it free. If you deem this site worthy of continuing, please support its upkeep. A generous reader has offered to match all contributions, so please take advantage of his generosity. Regular monthly contributions and books and papers purchases can ensure this material unavailable anywhere else will continue to be free. Thanks.
---------------------------------------------------------------------------------

Table of Contents

Abstract

Introduction

1 Relation Interpretation

2 Object Properties, Names and Identification

3 Relational Keys

4 Primary Keys
4.1 Formal Primary Key Mandate
4.2 Primary Key Selection
4.2.1 Candidate and Alternate Keys
4.2.2 Composite Keys
4.2.3 Natural and Surrogate Keys
4.2.4 Some Examples
4.2.5 Surrogate Key Illusions

Conclusion

References

Appendix A: Duplicates and their consequences
1. Duplicates and SQL
1.1 Logical Validity and Semantic Correctness
1.2 Nestability
1.3 Result Interpretation
1.4 Performance Optimization
1.5 Duplicate Removal

Appendix B: Keys in SQL
References