SQL/MySQL RDBMS Concepts

---

---

in this section we have seen about RDBMS(Relational Database Managment System) in SQL/MySQL

RDBMS(Relational Database Management System). RDBMS is the basis for SQL and for all modern database systems like MS SQL server, IBM DB2, Oracle, MySQL, and Microsoft Access. a relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational models. 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. a table is the most common and simple form of data storage in a relational database. the concept of table we have see in next point.

root@batman:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.3.20-MariaDB-1 Debian buildd-unstable

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Codeworld19        |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use Codeworld19;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [Batman]> show tables;
+-----------------------+
| Tables_in_Codeworld19 |
+-----------------------+
| users                 |
+-----------------------+
1 row in set (0.000 sec)
 
MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
+----------+--------------+----------+
3 rows in set (0.000 sec)

MariaDB [Codeworld19]> 

The data in RDBMS is stored in database objects called tables. The tables is a collection of related data entries and it consists of column and rows. Relational database model, a table is a collection of data elements organized in terms of rows and columns. A table is also considered as a convenient representation of relations. But a table can have duplicate row of data while a true relation cannot have duplicate data. Table is the most simplest form of data storage. Below is an example of an Employee table.

MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
+----------+--------------+----------+
3 rows in set (0.000 sec)

A table consists of several records(row), each record can be broken into several smaller entities known as Fields. Every table is broken up into smaller entities called fields. The fields in the USERS table consist of NAME, EMAIL and PASSWORD. a field is a column in a table is designed to maintain specific information about every record in the table.

MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
+----------+--------------+----------+
3 rows in set (0.000 sec)

In above example the different fields are highlighted with different colors.

A record, also called a row of data. is each individual entity that exists in a table. for example there are 3 records in the following USERS table.

MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
+----------+--------------+----------+
3 rows in set (0.000 sec)

A Column is a vertical entity in a table that contains all associated with a specific field in table. in Relational table, a column is a set of value of a particular type. The term Attribute is also used to represent a column. in the following example the username, email, password is a column.

MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
+----------+--------------+----------+
3 rows in set (0.000 sec)

A NULL Value in a table is a value in a field that appears to be blank, which means a field with a NULL values is a field with no value. it is very important to understand that a NULL values is different than a zero values or a field that contains spaces. A field with a NULL values is one that has been left blank during record creation.

MariaDB [Codeworld19]> insert into users(email,password) values ('cw@gmail.com','123');
Query OK, 1 row affected (0.336 sec)

MariaDB [Codeworld19]> select * from users;
+----------+--------------+----------+
| username | email        | password |
+----------+--------------+----------+
| omkar    | ac@gmail.com | abc      |
| abc      | yz@gmail.com | xyz      |
| parthe   | op@gmail.com | pass     |
| NULL     | cw@gmail.com | 123      |
+----------+--------------+----------+
4 rows in set (0.000 sec)

The above example the data has insert in only two fields EMAIL and PASSWORD and the USERNAME field is treated as NULL value.