How Databases Give Structure to Data

Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

So far in this journey, we’ve moved steadily upward:

• From disks and blocks, where persistence is slow and unreliable
• To data items and database states, where correctness and consistency emerge

Now we arrive at the model that has dominated database systems for decades: the relational data model.

This is where raw data items begin to acquire structure, meaning, and enforceable rules.

Entities and Relationships: Modeling the Real World

At the heart of the relational data model are two fundamental ideas:

• Entities
• Relationships

An entity represents a distinguishable real-world object which is physical or logical about which users want to store information.

A student, a course, a department, or an order are all examples of entities.

A relationship represents an association between two or more entities.

Relationships are not secondary or optional; they are just as important as entities themselves.

For example, enrollment captures the relationship between a student and a course, and without it, much of the data loses its meaning.

In the relational model:

• A data item may represent entity data
• Or relationship data
• Or collections of both

This distinction matters because databases are not just storing isolated facts, they are storing connected information.

The Entity–Relationship (ER) Model

Before data is stored in tables, it is often designed using the Entity–Relationship (ER) model.

The ER model is a conceptual modeling tool. It focuses on what data exists and how pieces of data are related, not on how operations are performed.

The ER model uses three primary components:

1. Entity Sets A collection of similar entities sharing the same attributes. Example: all students belong to the Students entity set.
2. Attributes Properties that describe an entity. Each attribute has a distinct name and represents something users care about.
3. Relationship Sets Collections of similar relationships among entity sets. These describe how entities are connected.

An important concept here is the key.
A key is a set of attributes whose values uniquely distinguish one entity from another within an entity set. Without keys, identity collapses, and the model becomes ambiguous.

Relationships themselves can:

• Be binary or n-ary
• Have their own attributes
• Connect entities from the same or different entity sets

The ER model gives us a clean, human-readable view of a data world, but it deliberately avoids defining operations or storage behavior.

From ER Models to Relational Databases

Conceptual models are useful, but databases ultimately need concrete structures.

In a relational database, everything is represented as a relation.

At a conceptual level, a relation is simply a table:

• Rows and columns
• Finite in size
• Uniform in structure

Both:

• Entity sets
• Relationship sets

are translated into separate relations.

Users of a relational database see nothing but a collection of tables. There are no pointers, no graphs, no hidden storage structures, just relations.

Each relation is defined by a schema, which specifies:

• Attribute names
• Attribute types
• Constraints

A table may be empty, but it must always have a schema.

Without a schema, the database has no idea what kind of rows are allowed.

Some important terminology:

• Attributes / fields → columns
• Tuples / records → rows
• Arity (degree) → number of columns
• Cardinality → number of rows

Every row in a relation:

• Has the same structure
• Contains one value per attribute
• Represents either an entity or a relationship instance

Why Relations Are So Powerful

One of the greatest strengths of relational databases is the simplicity of their data model combined with the power of their operations.

Relational systems support a rich set of high level operations over relations. These operations are typically expressed as queries, not procedures.

Queries are:

• Declarative
• Focused on what result is desired
• Independent of storage details

The system decides:

• Which blocks to read
• Which indexes to use
• How data is accessed on disk

SQL is the most widely used language for expressing these operations. The same language is used to:

• Define schemas
• Insert data
• Update data
• Query data

This separation between logical intent and physical execution is a recurring theme in database systems.

Integrity Constraints: Defining Valid States

A database is not just a collection of tables, it is a system that enforces rules.

These rules are called integrity constraints.

An integrity constraint is a condition that must always hold true for the database state. If a modification violates a constraint, it must be rejected.

Constraints encode the semantics of the real world:

• What values are allowed
• Which combinations make sense
• How tables relate to each other

Integrity constraints are checked during:

• Insert operations
• Update operations
• Delete operations

They ensure that invalid states never become persistent.

Domain and Key Constraints

The most basic integrity rules operate at the attribute and row level.

Domain Constraints

Every attribute has a datatype defining the set of values it may take. This is its domain.

Additional restrictions such as legal ranges or conditions which can further narrow allowed values. These are commonly expressed as check constraints.

Key Constraints

Keys ensure uniqueness and identity:

• Primary keys uniquely identify rows
• Unique constraints prevent duplicate values
• NOT NULL constraints enforce mandatory attributes

Referential Constraints

Foreign keys enforce relationships between relations:

• Values in one relation must correspond to existing values in another
• They prevent dangling references and broken relationships

Together, these constraints define what it means for a relational database to represent a valid world.

Where This Leaves Us

At this point, we’ve built a layered understanding:

• Physical storage defines what is possible
• Data items define what is stored
• Relations define how information is structured
• Integrity constraints define what is allowed

What we have not yet addressed is what happens when:

• Multiple users access the same relations
• Updates overlap
• Failures occur mid-operation

That is where transactions, concurrency control, and recovery enter the picture, and where the real tension between theory and physical reality begins.

That’s the next unavoidable step in this journey.

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools