Structure of a Relational Database

The structure of a Relational Database, for example, a MySQL Database, is oriented around rows, columns, and tables. Tables contain rows and columns. Tables can be marked under a schema. A schema is simply a way of grouping together related tables. We mark all the related tables under a certain name, and that name is called a schema. You can learn more about this in a dedicated SQL course.

A database is hosted on a database server, the server enables us to make requests to the database for storing and retrieving data. A database system comes with a database server. There can be multiple databases inside a server. Typically we use only one database for an application.

In most MySQL servers, there's a limit of 4 billion tables which is more than enough. A table contains rows of data that are stored in the format specified by that table. For example consider a table called students, which stores some basic student information like id, which is of type int, name, which is of type string, and age, which is of type int:

We define the number and datatypes of columns when creating a new table. A table can have a maximum of 4096 columns.

Each row represents a unit of data, and the columns represent the attributes of that data. In this case, each row is about a specific student where id, name, and age are the attributes. There can be over a billion rows in a table.

Although we can not directly see the tables in a MySQL database, they are similar to the tables in a spreadsheet. We use SQL commands or queries to create tables, add data to the tables, and retrieve data from the tables.

We can store, change, or delete data using the four common operations or statements:

1. SELECT - The SELECT statement is used for selecting and retrieving certain data from the database;
2. INSERT - The INSERT statement is used for inserting rows into tables;
3. CREATE - The CREATE statement is used for creating tables or databases;
4. DROP - The DROP statement is used for deleting tables or databases; We will see examples of these statements in this section; however, it is not necessary to understand them fully. In the next section, we will look at the syntax of these statements in detail, along with examples.