What is a relational database?

A relational database is a type of database (an organized collection of information usually stored in rows and columns). It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.

Tables: rows and columns

Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called records.

Tables can also have many columns of data. Columns are labelled with a descriptive name and have a specific data type.

In the table above, there are three columns (name, age, and country). The name and country columns store string data types, whereas age stores integer data type.

The set of columns and data types make up the schema of this table. The table also has four rows also known as records – one each for Natalia, Nelo, Matthew and Anastasia.

Are you wondering if that’s just a table? Why it doesn’t present or showcase anything about relationships? Don’t fret, I have got you covered.

Let’s see how data relates from one table to another in a relational database.

Understanding table relationships

Tables contain different types of data and are related to each through sharing a common column.

For example, A school’s database that contains information about student IDs, students names, addresses, ages, subjects, grades, classes, sexes etc.

All these information cannot be stored in the same table because it will be too cumbersome and also the student population varies yearly and updating it will not be easy and hence the need to store this information separately arises.

STUDENT TABLE

SUBJECT TABLE

Can you identify what column these two tables have in common?

The correct answer is the “student_id” column.

We are able to retrieve information such as subjects taken by students, teachers assigned to the subject, the grade etc. because both tables share a column which is the student_id column.

Data in different tables in relational databases are only accessible through these columns that are shared between them. Each shared column has a different name or function in each table.

Understanding the uniqueness of columns

The Student table

The student_id column in the student table identifies as the primary key (PK) of that table because it is the column that contains unique values. Other columns can have duplicate values such as same age, names, sexes, and address but not the same IDs.

The Subject table

In the same manner, the subject table has its own primary key(PK) which is the subject_id column and it also contains unique values. While columns such as student_id and teacher_id columns are known as foreign keys because they belong to other tables.

Foreign keys serve as links to other tables which enable us to create relationships between tables.

What is a Relational Database Management System (RDMS)

A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.

What is SQL?

SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret.

SQL is often pronounced in one of two ways. You can pronounce it by speaking each letter individually like “S-Q-L”, or pronounce it using the word “sequel”.

Popular Relational Database Management Systems

SQL syntax may differ slightly depending on which RDBMS you are using. Here is a brief description of popular RDBMSs:

MySQL

MySQL is the most popular open-source SQL database. It is typically used for web application development, and often accessed using PHP.

The main advantages of MySQL are that it is easy to use, inexpensive, reliable and has a large community of developers who can help answer questions.

PostgreSQL

PostgreSQL is an open source SQL database that is not controlled by any corporation. It is typically used for web application development.

PostgreSQL shares many of the same advantages of MySQL. It is easy to use, inexpensive, reliable and has a large community of developers. It also provides some additional features such as foreign key support without requiring complex configuration.

Oracle DB

Oracle Corporation owns Oracle Database, and the code is not open sourced. Oracle DB is for large applications, particularly in the banking industry. Most of the world’s top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks.

MS SQL Server

Microsoft owns SQL Server. Like Oracle DB, the code is close sourced.

Large enterprise applications mostly use SQL Server. Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.

SQLite

SQLite is a popular open source SQL database. It can store an entire database in a single file. One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server.

SQLite is a popular choice for databases in cell phones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. The SQL courses on Codecademy use SQLite.

Conclusion

Relational databases store data in tables. Tables can grow large and have a multitude of columns and records. Relational database management systems (RDBMSs) use SQL (and variants of SQL) to manage the data in these large tables. The RDBMS you use is your choice and depends on the complexity and requirements of your application.