A Study Group for Relational Theory

This page describe a study group that I am organizing to review 8 articles on Relational Theory written by Fabian Pascal.

I propose is that we understand what a proper schema should look like, so that, when we look in our day job schemas, we understand what is wrong, and we have a clue on what kind of problems we will be facing on the application side. For this goal: I propose a study group on 8 articles written by Fabian Pascal.

From the author:

two series of papers for the data professional and user who thinks critically and independently, rather than operates in the IT industry's fad driven "cookbook mode".

Motivation

The Logical Structure of Reality is Evident to the Mind

Our souls are brought into existence, and we soon realize the world is not a single thing, but instead is made of things (plural). Some of these things are similar among them, and we quickly intuit that there must be an abstract and general principle behind these commonality we observe. We realize that there are things which exist in themselves, and that there are things which can only exist as part of other things that exist in themselves. In our day to day, we constantly refeer to things that exist not in one, not it other thing, but instead somehow in between things. Furthermore when looking closer to one object, we notice that it is made of parts, and that some of these parts can be added or removed to the object and the object somehow still remains the same, but there are modifications that really transform one thing into another. To one degree or another, we all know intuitively these matters very early in life.

The Tar Pit of the Industry

Eventually though, one grows old enough and becomes a programmer, and one gets asked to design a storage system to represent the real world. And the world is such that one is frequently in a hurry. Plus, one is just getting started on this business, and one trusts the industry. After all, if Facebook and Google do it, it means it is a good practice, right? And then one represents reality as json objects.

Let's spell that out: JavaScript Object Notation. One represents the world as javascript object notation.

The result is clear: one gets used to debugging and late nights. The spirit suffers.

A Better Way

Why does the spirit suffer? Is it simply the hardship of the job? Or could it be that the spirit suffers from being forced to think of the world in unnatural ways? Could the developer describe the world such that the intuition he uses to navigate reality helps him in evolving the system he is hired to maintain? What is that manner?

This study group was created after the following answer: a properly designed relational database schema is the technique that our industry has best developed to represent the logical structure of reality. Nothing that I have read in our industry resembles classical philosophy more than what I have read on relational theory.

Keeping It Real

I understand: you probably don't maintain an relational schema on your job. Me neither. What I propose is that we understand what a proper schema should look like, so that, when we look in our day job schemas, we understand what is wrong, and have a clue on what kind of problems we will be facing on the application side. I propose we figure out what exactly is good on a proper RDBMS and relational schema, and then find ways to port it to our jobs.

For the goals outlined above I propose a study group on 8 articles written by Fabian Pascal.

Content of the Papers

From the author's page:

The Key to Relational Keys: A New Understanding – Primary Keys. PRACTICAL DATABASE FOUNDATIONS #4 (v.3 February 2018)

Table of Contents Abstract Introduction 1 Relation Interpretation 2 Entity 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

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

Logical Symmetric Access, Data Sublanguage, Kinds of Relations, Redundancy and Consistency (description) UNDERSTANDING THE REAL RDM #2 (v.1 August 9, 2019))

Table of Contents Preface Introduction

Logical Symmetric Access
Universal Data Sub-language

2.1. FOPL vs. SOPL 2.2. Relational Completeness 2.3. Computational Completeness and Hosting

Kinds of Relations

3.1. Expressible and Named Relations 3.2. Derived Relations 3.3. Relations with Stored Data

Derived Relations and Redundancy

4.1. Database Consistency

Database Catalog

Conclusion References

The Interpretation and Representation of Database Relations UNDERSTANDING THE REAL RDM #1 (v.1 April 2017)

Table of Contents Series Preface Introduction

The Interpretation of Database Relations

1.1. Attributes as Constrained Domains 1.2. Time-Varying Relations

Representation of Database Relations

2.1. Physical Independence 2.1.1. Uniquely Named Attributes 2.1.2. Primary Keys 2.1.3. Relations and R-tables

Database Normalization

3.1. Simple Domains and Normal Form 3.2. Non-simple Domains and Normalization 3.2.1. Foreign Keys Conclusion References

Business Modeling for Database Design: Formalizing the Informal PRACTICAL DATABASE FOUNDATIONS #1 (v.4 May 2015)

Table of Contents Introduction

Business Modeling

1.1. Basic Modeling Concepts 1.2. Business Rules 1.2.1. Property Rules 1.2.2. Class Rules 1.2.3. Associative Entities 1.3. Business Models

Database Design

2.1. Formalizing the Informal 2.2. Predicates and Propositions 2.3. The Relational Data Model 2.3.1. Relational Structure 2.3.2 Relational Integrity 2.3.3. Relational Manipulation 2.4. Logical Models

Understanding Database Management

3.1. Note on missing values 3.2. A Foundation Framework Appendix A: Constraint Formulation and Verification Appendix B: Integrity Constraints in Dataphor’s D4 Appendix C: Some Misconceptions Debunked References

The Costly Illusion: Normalization, Integrity and Performance PRACTICAL DATABASE FOUNDATIONS #2 (v.4 May 2015)

Table of Contents Introduction

R-tables, Keys and Dependencies
Normalization and Normal Forms
Further Normalization As Design Repair

3.1. Join Dependencies 3.2. “The Whole Key” and 2NF 3.3. “Nothing But the Key” and 3NF 3.4. “The Whole Key” and BCNF 3.5. Multivalued Dependencies and 4NF 3.6. Interval Data and 6NF

“Denormalization For Performance”

4.1. The Logical-Physical Confusion 4.2. Redundancy Control 4.3. JDC’s and SQL 4.4. The Real Problem and Solution

Conclusion and Recommendations

References

The Final NULL in the Coffin: A Relational Solution to Missing Data PRACTICAL DATABASE FOUNDATIONS #3 (v.4 May 2015)

Table of Contents Introduction

"Inapplicable Data": Nothing's Missing
Missing Data: Into the Unknown
SQL’s NULL: What-Valued Logic?
Known Unknowns: Metadata
A 2VL Relational Solution

5.1. The Practicality of Theory 5.2. 2VL vs. NULL’s in the Real World 5.3. Relation Proliferation 5.4. TRIM™ Conclusion Appendix A: What’s Wrong with this Picture?

"Not Complicated"
"Part of the Real World"
"Integral Part of Relational Databases"
"Throw a Damn Exception"
"Useless"

Appendix B: Comments on the Proposed Solution References

Truly Relational: What It Really Means PRACTICAL DATABASE FOUNDATIONS #5 (v.3 May 2015)

Table of Contents Introduction

Relations on Domains
Relation Representation
Time-Varying Relations
Relation Interpretation
Data Sub-language
Atomicity, Nested Relations, and Normalization
Foreign Keys and (First) Normal Form
Operations on Relations
Kinds of Relations
Derivability, Redundancy, Consistency

Conclusion Appendix A: Codd’s 1969 Relational Operators Appendix B: Debunking Misconceptions

Domains: The Database Glue PRACTICAL DATABASE FOUNDATIONS #6 (v.2 January 2015)

Table of Contents Introduction

Domains and Types

1.1. Meaning and Representation

Kinds of Domains

2.1. "Simple" Domains 2.2. "Complex" Domains 2.3. User-Defined Domains and System-Defined Types

Domains and SQL
Some Practical Implications

4.1. "Universal" DBMS 4.2. Database Design 4.3. ODBMS 4.4. NoSQL 4.5 Tackling Complexity Conclusion

Investment

I will by the 8 papers and share copies with participants. Total cost of the material is 170 USD (~884 BRL), which will be split by the number of participants.

Late sign ups, if any, are invited to pay the same amount that early members did. They extra money will be donated to a local monastery.

Commitment

We meet every 2 weeks, on Tuesday from 7pm to 9pm. If the time does not work for you, please let me know.

At each meeting:

Someone is responsible for presenting the paper
Someone else is responsible for taking notes about the presentation and group discussion
All participants are expected to have read the paper
Notes are emailed to participants
Meetings and discussion happen in English

Deadline for joining the group: <2023-05-26 sex>

First meeting: <2023-05-30 ter>

How to participate

Email me (juarez <dot> asf <at> gmail <dot> com) with the subject "I want to represent the nature of reality on my database" and send me your gpg public key. For the sake of hardening our asses, communication about this study group will happen with encryption.

You can find my public key below.