Explaining Relation and Data Independence in SQL

USECASE

The aim of this page📝 is to explain data independence in relational and navigational database models, with a focus on Edgar F. Codd's definition of a relation, as I am not entirely by the following

The term "relational database" refers to the fact that the tables within it "relate" to one another—they contain common identifiers that allow information from multiple tables to be combined easily.

• https://mode.com/sql-tutorial/sql-joins

• Data Independence: Refers to the separation of the schema of a database and the application that uses the data.

• Relational Model: Provides data independence, making it flexible and easier to maintain.

• Physical Data Independence: Changes in physical storage do not affect logical schema.

• Logical Data Independence: Changes to the logical schema do not impact how data is accessed by applications.

• Navigational Model predating relational model: Lacks data independence.

• Tight Coupling: In navigational databases, data and application logic are tightly coupled.

• Impact of Changes: Changes to the database structure in a navigational model require updates to the application code.

• Flexibility: Relational databases allow easier updates without disrupting applications.

• Database Maintenance: Easier in relational databases due to the independence of data and schema.

• Key Differences: Understanding the main differences between relational and navigational models helps in choosing the right database for specific needs.

Definition of a Relation

• Edgar F. Codd's Definition: In his seminal 1970 paper, "A Relational Model of Data for Large Shared Data Banks," Codd introduced the concept of a relation. He defined a relation as a set of tuples, where each tuple is a sequence of attribute values. A relation is essentially a table with rows and columns, where:

  • Rows (Tuples): Represent individual records.
  • Columns (Attributes): Represent properties of the records.

• Mathematical Foundation: Codd's relational model is grounded in mathematical principles of set theory and predicate logic. A relation is defined as a subset of the Cartesian product of a list of domains. Each domain represents a possible set of values for an attribute.

• Schema and Instance:

  • Schema: Defines the structure of a relation, including the attribute names and their domains.
  • Instance: Represents the actual data stored in the relation at a specific point in time.

• Relation Example:
  
  

  Customers
  +------------+-------------+
  | CustomerID | Name        |
  +------------+-------------+
  | 1          | Alice       |
  | 2          | Bob         |
  +------------+-------------+

  Orders
  +---------+------------+----------+
  | OrderID | CustomerID | Amount   |
  +---------+------------+----------+
  | 101     | 1          | $150     |
  | 102     | 2          | $200     |
  +---------+------------+----------+

• Foreign Key Relationships: In the relational model, tables are related to each other through common attributes (keys). For example, the CustomerID in the Orders table is a foreign key that relates each order to a specific customer in the Customers table. This allows for structured and flexible data management.

LINKS

• Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377-387. https://dl.acm.org/doi/10.1145/362384.362685
• 50 Years of Queries | Communications of the ACM
• What exactly is a database - part 1
• What exactly is a database - part 2