THE KEY TO KEYS: A Matter of Identity (PRACTICAL DATABASE FOUNDATIONS Book 4)

If entities in the real world did not have identifiers—attributes that capture their identity and uniquely identify them—we would not be able to tell them apart. It follows that an accurate database representation of a business reality must include keys, which formally represent informal the real world identifiers in the database.

Keys and the types thereof, their necessity, key selection, function and properties are often not well known and understood.

This paper
· Defines and explains the key concept;
· Explains the function and properties of the various types of key;
· Describes the criteria for key selection;
· Specifies what is proper DBMS key support;
· Assesses SQL's key support;
· Debunks some common misconceptions about keys.


Table of Contents

Introduction

1. R-tables and Integrity Constraints

2. Keys and Key Constraints

3. Kinds of Keys
3.1. Candidate and Primary Keys
3.2. Natural Keys
3.3. Simple and Composite Keys
3.4. Foreign and Surrogate Keys

4. Key Functions
4.1. Duplicate Prevention
4.2. Guaranteed Logical Access
4.3. Low Integrity Burden
4.4. View Updatability and Logical Data Independence

5. DBMS Key Support

6. Keys in SQL
6.1. SQL and duplicates

Conclusion

Appendix A: Duplicate Removal in SQL

Appendix B: Duplicates and Language Redundancy

References