Introduction to relational (SQL) databases: Basic concepts

SQL DataBase (DB) – is a number of tables (objects) which are connected which each other. Using a special software called management system the one can interact with such a system by writing data into database and when needed reading it back. It is possible to read or fetch data based on some condition and the final result could be sorted by one or multiple features (columns).

SQL databases organize all data into tables, which store data in columns. Each table should have at least one column.

Database management system (DBMS) – is a special software, which organizes data into SQL database, ensuring its storage capabilities, data access whenever it is needed. DBMS also takes care about data integrity and security. The one could also use DBMS to import / export data from / to other DBMSs using standard formats / sequence of SQL commands.

Relational model

In this and upcoming posts I will provide detailed guides related to relational or SQL databases. As word relational implies, all tables in such a database are connected with relations. And SQL (Structured Query Language) is a standard language to work with such relational databases. But each DBMS development company, like Microsoft, Oracle, etc. could add some extra features to their DBMS. On one hand, it enriches such DBMS functionality, allowing to complete some tasks more easily. On the other hand, it introduces some commands / query syntax, which is not part of standard SQL syntax. So you can face some migration related problems if you someday you decide to migrate from Oracle to Microsoft DBMS. So keep that in mind!

Data storage in SQL tables

All tables in a relational (SQL) database are connected with each other. The one could ask – couldn’t it be so – there is a table / multiple tables in a database, which are not connected to any other table? Yes, it could. But as we are talking about relational databases, most probably unconnected table highlights a database structure related problems. So if a table is not connected (is not related) to any other table in the database, what’s the point in having such a table inside this particular database?

The other question to discuss – why do we need to store our data into multiple tables? Why can’t we just put all the information we have into one SQL table?

There are at least two problems related to that. The first one – the table will be rather huge with a lot of data being duplicated. So we will use our storage resources to store duplicated data. The second problem – we will use our computational resources to process duplicated data. Because of that we divide our data into multiple tables which are connected (related) to each other.

In the next post we will talk about basic units of relational (SQL) database – table, column, field, row.

As a reminder – in some previous guides we have already touched SQL a bit while talking about Sqoop – a big data tool which allows us to migrate data to / from big data systems to SQL. If you have missed these guides, maybe it is a good time to check them now:
– SQOOP – DATA IMPORT FROM MYSQL USING CUSTOM SQL QUERY
– SQOOP – EXPORT FROM HDFS TO MYSQL VIDEO IS AVAILABLE NOW

Stay turned!