Tabular or relational databases are those that can be organized in tables.
In opposition to tabular or relational databases there is NoSQL databases.
Relational Database Design
Aspects related to relational database design:
- Relational Database Modeling
- Database Normalization
- Codd’s Rules
Relational Database Modeling
Relational databases can be designed using the Entity-Relationship (ER) model.
You can find more info about ER model on this post.
Database Normalization
Database normalization is the process of removing redundancy within a relational database. You can read this post about database normalization.
You can read more about database normalization on this post.
12 Codd’s Rules
The 12 Codd’s rules were proposed by Codd that should follow ideally a relational table.
- Rule 0: The Foundation Rule
- Rule 1: Information Rule
- Rule 2: Guaranteed Access Rule
- Rule 3: Systematic Treatment of Null Values
- Rule 4: Active/Dynamic Online Catalog based on the relational model
- Rule 5: Comprehensive Data SubLanguage Rule
- Rule 6: View Updating Rule
- Rule 7: Relational Level Operation (High-Level Insert, Update and delete) Rule
- Rule 8: Physical Data Independence Rule
- Rule 9: Logical Data Independence Rule
- Rule 10: Integrity Independence Rule
- Rule 11: Distribution Independence Rule
- Rule 12: Non Subversion Rule
Integrity Rules
Types of integrity rules
- Entity Integrity
- Referential Integrity
- Domain Integrity
- User-defined Integrity
Entity integrity means that the attributes of a primary key cannot be null.
Referential integrity
Domain integrity
User-defined integrity
Relational Database Queries
Users and programs interact with relational databases through queries.
Relational Database Query Types
Types of relational database queries:
- DDL
- DML
- DCL
- DQL
- TCL
A Data definition language (DDL) is used to define or modify the structure of database objects such as tables, schemas, indexes, etc. Examples are SQL’s CREATE, DROP and TRUNCATE.
A Data Manipulation Language (DML) is used for data handling and includes instructions like SQL’s INSERT o DELETE.
A Data control language (DCL) allows privilege management through instructions like SQL’s GRANT or REVOKE
A Data query language (DQL) includes instructions like SQL’s SELECT.
A Transaction control language (TCL) includes instructions like SQL’s COMMIT and ROLLBACK.
Relational Database Query Languages
The most popular language for relational database queries is SQL.
SQL
Structured Query Language (SQL) is the most popular query language for relational databases.
You can read this post that is an introduction to SQL.
PL/SQL
Procedural Language/SQL (PL/SQL) is a procedural extension for SQL developed by Oracle.
Database Concurrency Issues
Database concurrency issues or anomalies:
- Lost updates
- Dirty read
- incorrect summaries
- Non-repeatable read
- Phantom read
A lost update occurs when one transaction writes a value to the database that overwrites a value needed by transactions that have earlier precedence, causing those transactions to read an incorrect value.
A dirty read occurs when a transaction reads data that has been modified by another transaction, but not yet committed. In other words, a transaction reads uncommitted data from another transaction, which can lead to incorrect or inconsistent results.
A incorrect summary occurs when on transaction is using an aggregate function to summarize data stored in a database while a second transaction is making modifications to the database, causing the summary to include incorrect information.
A non-repeatable read is produced when a transaction reads a value, then another transaction modifies the read value, and the first one finds a different value.
A phantom read is produced when a transaction performs a query, then a second transaction insert or deletes a row, and the first one reads again, finding a different result.
List of Relational Databases
List of relational databases:
- PostgreSQL
- MariaDB
- MySQL
- SQLite
- CockroachDB
- Microsoft SQL Server
PostgreSQL
PostgreSQL is free and open source (FOSS), under a Postgres License.
Is developed by the PostgreSQL Global Development Group.
MariaDB
MariaDB is a free and open source software (FOSS), under a GPLv2 license.
It is a community-based project that was forked from MySQL when it was acquired by Oracle.
MySQL
MySQL is currently owned and developed by American company Oracle. MySQL was originally developed by Swedish company SQL AB, that was acquired by Sun Microsystems, that was acquired by Oracle.
MySQL Community is free and open source (FOSS), under a GPL license.
MySQL Enterprise is proprietary.
SQLite
SQLite is written in C.
It is public domain, so it is considered free and open source (FOSS).
CockroachDB
CockroachDB is a commercial proprietary database.
Microsoft SQL Server
Microsoft SQL Server, sometimes just referred as SQL Server, is a SQL server developed by Microsoft.
Machine Learning Services (MLS) is a characteristic of MS SQL Server that allows to execute Python and R scripts with relational data.
Revoscaler is a R packet in MLS that allows to perform data transformation and manipulation, statistical briefings, visualizations and other model forms.
You might also be interested in…
- Introduction to Databases
- NoSQL Databases
External Links
- 12 Codd’s Rules
- Wikipedia community; “12 Codd’s rule”; Wikipedia
- Database Integrity
- Geeks for geeks; “Integrity Rules“; Geeks for geeks