Learning Goals
In this Tutorial you will learn that:
- A relational database consists of one or more tables.
- A table consists of a grid of vertical columns and horizontal rows (records).
- Each row must have a primary key that identifies it uniquely from all other rows in the table.
- Relationships can be created between different tables in a database that store different types of information.
- SQL (Structured Query Language) is the language for interacting with tables in a relational database.
- Modern websites typically use databases as a back end for storing data. And web developers use SQL language for interacting with that data.
About relational databases and SQL
A relational database management system (RDBMS) is just another software program – like Microsoft Word, Mozilla Firefox or Abobe Photoshop.
Whereas Photoshop is for creating and working with images, an RDBMS is for creating and working with databases.
A database is simply an organised collection of data. Here are some examples of databases:
- A list of the customers of a business
- A list of the products sold by a business
- A list of students attending a college
- A list of courses offered by a college
Relational database management systems are generally referred to more simply as just ‘relational databases’.
The language used to interact with an RDBMS is called SQL (and pronounced sequel).
For this reason, the terms ‘relational database’ and ‘SQL database’ are used interchangeably. The two terms mean the same thing.
Relational database software
Here are some popular relational database products:
- MySQL: This is an open-source, free-to-use database. It is used by Netflix, Linkedin, GitHub and also by the WordPress Content Management System (CMS). A newer, also open-source version of MySQL is mariaDB.
- Microsoft SQL Server: This is a commercial product available in a range of versions for different sizes of organisations. Newer versions of Microsoft Access can also be considered as relational databases.
- Oracle: A database used by large corporations, government agencies and financial institutions.
SQL and No-SQL databases
Not all databases are relational databases. So-called No-SQL databases such as MongoDB and Google's Firebase are becoming increasingly popular.
One reason for their popularity is that web developers can interact with No-SQL databases using only JavaScript. No server-side programming language such as PHP is required.
Relational databases and web pages
Modern websites typically use databases as a back end for storing data. And where the database is a relational one, web developers use the SQL language to interact with that data.
While websites visitors don’t directly interact with the database, their activities – such as signing up or signing in, selecting which products to display, and making purchases – are translated into SQL commands that update the information stored in the database.
In WordPress, for example, SQL databases are used ‘behind-the-scenes‘ to store posts, author information, comments and other details. Only WordPress developers actually interact with SQL, and then only to create a plugin or debug some issue.
For some reason, databases are typically shown in diagrams in the shape of a cylinder.
Source: educative.io
Tables, columns, rows, records and fields
In an SQL database, the content is organised in the form of tables.
Each table contains horizontal (left-to-right) rows and vertical (top-to-bottom) columns of data in a grid layout.
You can think of tables are similar to spreadsheets in Microsoft Excel or Google Sheets.
The example above is from the Customers table of Northwind Traders, a free sample database provided by Microsoft with their Access product.
- Columns: Each column stores a different type of information about the company's customers. Columns in a table can store strings (text) or numeric values (numbers).
Note that there are no spaces within the names of columns. Also, to make them easier to read, column names often contain a mix of upper and lower case letters.
- Rows: Each customer has a row of their own. Rows are often referred to as records.
The intersection of any column and row is known as a field.
Primary keys
In a relational database, each record in a table must contain a so-called primary key. This uniquely identifies each record in the table. Examples of primary keys would CustomerID or ProductID.
Relationships between tables
Organisations use separate tables within a common database for storing different types of information. An e-commerce company, for example, might have separate tables for the following: customers, products and orders.
The ‘relational’ in relational databases comes from designing the layout of the individual tables in such a way that the information they contain can be linked together in an efficient manner.
Return to Contents.