Introduction to Databases - SQL

Introduction

We shall learn about databases and SQL in this article. This article provides readers with a fundamental overview of databases and SQL. This article is the first part of the database - SQL series.

Prerequisites

• Basic computing knowledge

What is a database?

A database is a collection of related information which is in an organized form for easier access, management, and upkeep. A database can be anything from a simple grocery list to a complex computer network. A database management system manages a database - more on DBMS later.

Database Management Systems (DBMS)

A database management system is software that manages databases. DBMS provides an interface between a user and the underlying database. It enables the end user to add, read, edit, and remove data in the database.

Data management systems (DBMSs) ensure that data is identical for all users accessing the database and appears consistently throughout the database. When business logic evolves, users can more readily adjust systems with a DBMS. When a database crashes, a DBMS can quickly back up and recover the data. Languages used in programming can communicate with DBMSs.

Types of DBMS

There are two common types of databases:

• Non-relational

• Relational

Non-relational: This kind of database is anything but a traditional database. Data structures, such as key-value pairs, documents (JSON), and graphs, are used to store data.

Common examples of Non-RDBMS: MongoDB, DynamoDB, Redis

Relational: Relational databases hold data in rows and columns. These relational databases can be accessed using SQL. It is the common language used to communicate with relational databases. It's not always possible to transfer SQL code from one RDB to another without making changes.

Common examples of RDBMS: MySQL, PostgreSQL, Oracle

Database Queries.

Requests for specific information from a database are known as database queries. A database query can be either a select or an action query. A Select query retrieves data from the database. Action queries perform operations such as inserting, deleting, updating, and other data manipulations.

The default query language for RDBMS is SQL (Standard Query Language).

Mongoose is a typical example of a non-RDBMS query language.

Tables and Keys.

Tables are database objects that hold data in a database. In relational databases, every table comprises rows and columns. A table's column is its vertical component, while its row is its horizontal component.

A column will define a single attribute. An entry in the table is called a row.

The word column can be interchanged for an attribute. An entry, or tuple also means a row. A relation can also be referred to as a table

When creating a table in a relational database, we always want to define a key. A key is either a single or a set of attributes that uniquely defines a row in a table.

In the table above, the user_id column is the key. It ensures that there are no rows with duplicate values. Additionally, it aids in creating a connection between two or more database tables.

Types of Keys

• Super Keys: Supers keys are one or more columns that can uniquely identify a row in a table. Super keys are the superset of Candidate keys. Candidate keys are created by combining Super keys.

• Candidate Keys: A candidate key is a minimal set of attributes that can uniquely identify a row in a table. It is used for Primary Key selection and uniqueness constraints.

• Primary Keys: The candidate key chosen to identify each row in a table is called a primary key.

• Alternate Keys: The leftover after choosing a primary key from a set of candidate keys is the alternate key.

• Foreign Key: A foreign key is used to link two tables together. A column or set of columns in Table X connected to another in Table Y is known as a foreign key.

---

---

Comparing both tables above, we can deduce that user_id is the Foreign key because it links both tables together.

• Composite Key: This kind of key contains more than one attribute or column.

SQL Basics

SQL is a language used for interacting with RDBMSs. It helps create, retrieve, update, and delete data for us. SQL performs several administrative operations such as security, user management, and import/export of data. SQL implementation might vary between systems.

Not all RDBMS follow the SQL standard to a totality. Though the implementation could differ, the concepts are the same.

SQL is a hybrid language. It is a combination of 4 languages:

• Data Query Language: SQL as data query language queries the database for information stored.

• Data Definition Language: It defines database schemas. Schemas tell the database what is allowed to be stored.

• Data Control Language: SQL is used to control access to the database. It performs user and permission management tasks.

• Data Manipulation Language: Here, SQL can be employed to insert, update, and delete data from the database.

Conclusion

So far, we have briefly discussed the fundamentals of database and SQL up to this point; moving ahead, the next series of articles will focus on setting up an MYSQL server and taking a deeper dive into SQL by writing code.