Equinox

Relational Databases

3 min read

Relational Databases

Tables

A table, also called entity or relation, is a data structure composed by tuples and attributes.

  • It represents a set of entities or records.
  • It has a fixes schema
  • Each row follow the same structure

The number of tables in a SQL Database is limited by the number of allowed objects in said base, usually 2 147 483 647. The number of Tuples is limited only by server storage capacity.

Attributes

Attributes are the named columns of a table.

  • Define the type of data stored (eg: INT, TEXT)
  • Each attribute has a domain (allowed values)

Example:

Students(id, name, age)
  • id, name, age → attributes
  • Domains:
    • id → integers
    • name → strings
    • age → integers

Tuples

A tuple is a single row in a table.

  • it represents one record/instance
  • it is an ordered set of values, one per attribute

Example:

(1, "Emiliano", 21)
  • This is one tuple in Students

Indexes

An Index is an auxiliary data structure associated with a table that allows faster lookup of tuples based on one or more attributes.

Key properties

  • A table is a set of tuples → no duplicate rows (in pure relational theory)
  • Order of rows does not matter
  • Order of attributes does not matter (though we write them in order)

To avoid duplicate data, we use normalization. Indexes improve read time, but decrease write time, as indexes require updating when adding more data.

Relationships

In a relational model, relationships describe how tuples in one table are associated with tuples in another (or the same) table.

Types of relationships

  • Cardinality → how many? (1:1, 1:N, N:M)
  • Participation → required or optional?
  • Degree → how many tables involved?
  • Identification → does the child depend on the parent’s identity?

By Cardinality

One-to-One (1:1)

Each tuple in A is related to at most one tuple in B, and vice versa.

  • Example: Person ⇐> Passport

Implementation: Foreign Key with a unique Constraint.

Meaning: each entity participates in at most one relationship instance.

Note: In practice, 1:1 is often merged into a single table unless there’s a strong reason (e.g., optional data, security, or modular design).

One-to-Many

One tuple in A can relate to many tuples in B, but each tuple in B relates to one in A.

  • Example: Company ⇐> Employees

Implementation: Foreign key goes in the “many” side. It usually is the primary key.

Employees(company_id → Company.id)

Many-to-Many

Tuples in A can relate to many in B, and vice versa.

  • Example: Students ⇐> Course

Implementation: requires a junction table.

Enrollments(student_id, course_id)

By Participation

Optional vs Mandatory

Total participation

Mandatory

Every tuple must participate in the relationship.

  • Example: every Employee must belong to a Company

Partial Participation

Optional

A tuple may or may not participate.

  • Example: a User may have a Profile Picture

By Degree

Unary (Recursive)

A table relates to itself.

  • Example: Employee manages Employee

Binary

Between two tables. Most common relationship.

Ternary (or higher)

Between three or more tables.

  • Example: Supplier supplies Product to Store

Identifying vs Non-Identifying

Identifying Relationship

  • Child’s primary key depends on parent
  • Used in weak entities

Non-Identifying Relationships

  • Child has its own independent primary key

Views

Views are non-persistent entities generated by SELECT SQL queries that present data as tables.

Views act as an abstracted layer above one or multiple tables, or even other views. They allow to simplify complex queries, encapsulate filter / join logic. Views can’t be updated in all database systems (eg.: PostgreSQL supports CREATE OR REPLACE VIEW).

Graph View

Backlinks