Relational databases, established by EF Codd in 1969, define a structured organization of data. Codd created 13 principles, also known as Codd’s 12 Commandments (rules 0-12), to characterize what makes a database system relational.
While SQL (Structured Query Language) is often used to interact with these databases, it is just one among many. Remember, the relational model isn’t confined to a specific product or language; it embodies principles of how data interacts with each other.
Codd’s 12 Commandments: Pillars of Relational Databases
These rules help identify a database system as relational:
- Rule 0 – A system should be a relational database and a management system.
- Rule 1 – All data should be represented in one way: as a value in a column in a table.
- Rule 2 – Guaranteed access: all data should be accessible with a table, column, and primary key.
- Rule 3 – Every column should permit null values.
- Rule 4 – The online catalog, containing metadata about the tables, should be accessible using the same queries as those used for data access.
- Rule 5 – The system should provide a comprehensive set of data sub-language commands.
- Rule 6 – The system must enable the update of views.
- Rule 7 – CRUD (Create, Read, Update, Delete) requests must be available from different entry points, allowing multiple row operations.
- Rule 8 – Changes to the physical database should not mandate alterations to the applications interacting with it.
- Rule 9 – Alterations to the logical level (metadata) of data should not require application changes.
- Rule 10 – Integrity constraints should be separate from the applications interacting with the database.
- Rule 11 – Distributing database parts across a cluster should not impact the end user.
- Rule 12 – Non-subversion: CRUD requests at the record level should never bypass validation or relational security.
While no relational database management systems fully adhere to each commandment, these principles should be pursued whenever possible.
Core Components of a Relational Database
A relational database consists of relations or tables. Each table has a heading and a body. The heading contains attributes or columns, while the body comprises tuples or records (rows). The degree or arity of a table is the number of its columns, and the cardinality is the number of its records.
Data types, integral to the database, define the type or domain of data stored. This includes character strings (CHAR or VARCHAR), integers (INT, TINYINT, BIGINT), floating numbers (FLOAT, DOUBLE), date and time values, and boolean values (TRUE or FALSE). Each field in the database should store a single value, termed as data value atomicity.
Primary keys, unique identifiers for each record in a table, make the database relational. Integrity rules state that every table must have a primary key that is unique and not null. These keys can be natural (relevant data) or surrogate (an auto-incrementing number). It is recommended to use surrogate keys for simplicity.
Foreign keys are attributes in one table that correspond to the primary key in another table, facilitating inter-table relationships. Relational databases also maintain referential integrity, ensuring that there are no unmatched foreign key values.
Database design also requires a careful understanding of relationships:
- One-to-many – Each record in one table can be related to many records in another.
- One-to-one – Each record can only have one associated record in another table.
- Many-to-many – This more complex relationship requires a junction table to bridge the association.
Normalization is a crucial process of organizing tables and attributes to minimize redundancy and duplication, aiming to achieve various levels of normal forms.
Interacting with a Relational Database: Queries and Views
Queries, executed through SQL or other languages, facilitate interaction with the database. They can be manually executed or dynamically based on the data.
Views, virtual tables derived from base tables, are “canned queries” that simplify the process of writing complex queries. They maintain a 1-to-1 correspondence with records in the base table, enabling data insertion, updation, and deletion through views.
Designing a Data Model
Designing a data model requires an understanding of the database’s purpose and careful planning of tables, fields, relationships, and normalization. Implementing integrity rules, ensuring atomicity of data values, and correctly utilizing primary and foreign keys are integral steps in creating a robust relational database.
To conclude, understanding and using relational databases is not just about mastering SQL. It involves grasping a core set of principles and rules, understanding the structures and relationships within the data, and using this knowledge to create, query, and manage your data effectively.