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:
- DML
- DDL
- Control
- DCL
- Transaction control
A Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL).ata manipulation language (DML) is used for data handling and includes instructions like SELECT, INSERT o DELETE.
Data definition language (DDL)
Data control language (DCL) allows privilege management through GRANT or REVOKE
Data query language (DQL)
A Transaction control language includes instructions 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 Issues
Dirty read n SQL 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.
Non-repeatable read
Phantom read
List of Relational Databases
List of relational databases:
- PostgreSQL
- MariaDB
- MySQL
- SQLite
- CockroachDB
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 database.
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