Date datatypes

Let us now look at the following main date datatypes:

Now that we have discussed the available datatypes for building columns, we will use SQL to build our first table. Structured Query Language (SQL) is a multipurpose programming language that allows us to communicate with the database management system to manage and perform operations on the data. SQL operations can be divided into three groups: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). These three groups are explained in the following table:

Now that we have discussed the basics of Database Management System and SQL, let us connect to our MariaDB server. MariaDB is shipped with a few pre-existing databases that are used by MariaDB itself to store metadata such as information about databases, tables, columns, users, privileges, logs, and so on (yes, MariaDB stores its own data in MariaDB tables).

As we have installed MariaDB and have root access to the server, we will be able to view all this metadata information. To retrieve the metadata information that is currently on MariaDB, we will use the SHOW utility command and, as we are interested in retrieving the list of existing databases, we will append DATABASES to our SHOW command:

Upon executing the show databases; command, the list of existing databases will be outputted to the screen. These databases are reserved to store configurations and necessary metadata (yes, MariaDB stores its data on MariaDB itself), so it is advised to avoid using these databases for storing other data. For storing other data, we will have to create our own database. We will use the SQL commands that are part of DDL to create new databases. For creating a new database, the CREATE DDL command is appended with DATABASE and then the name of the database to be created is added. Let us create a simple course registry database that keeps a track of student records, the available courses, and the courses for which the students have registered.

We have successfully created our first database. To verify that we have created this database, let us run the show databases; command one more time to see if our new database is reflected in the list of existing databases:

Now that we have verified that our new database is available in the list of existing databases, let us access the database and build tables in the course_registry database. For accessing a database, we will utilize the USE utility command. The USE command has to be followed with the name of an existing database to avoid an error, once this command has been executed.

Now that the database has been successfully changed, note that the database name reflects in between the brackets next to MariaDB, which denotes the current database.

Now that we have chosen the course_registry database, let us take a brief look at the data that has to be housed in this database. The course_registry database keeps a track of student records, the available courses, and the courses for which the students have registered. We could do this by putting the students and the courses that they have registered for in a single table. However, the problems with this approach, similar to a spreadsheet, are twofold. The first problem is that the student information would keep repeating when a student registers for multiple courses, thereby causing unnecessary redundancy.

The second problem will be about data inconsistency, assuming that the student information was wrong. Either we will be using this erroneous information another time, or we might be employing another data entry process that allows the user to enter different data as user information, which causes data inconsistency. To avoid this, we are splitting our data into three tables; they are students, courses, and students_courses.

The student records will be stored in the students table, the data about the available courses will be stored in the courses table, and the data about the courses that the students have registered for will be stored in the students_courses table. The students_courses table will be an association table that contains common fields from the students and the courses tables. This table can also be referred to as a bridge table, paired table, or cross reference table. By using the students_courses table, we can accommodate a common case where one student can register for more than one course.

Before we begin building our tables, it is always important to understand the type data that will be housed in this table and based on the data that will be housed in that table, we will have to decide on the column names and the datatypes for those columns. Column names have to be intuitive in order to help others such as system administrators, auditors, and fellow developers to easily understand the kind of data that can be or is currently being stored in those columns, and the respective datatypes of those columns will explain the type that can be housed in a column. Let us begin with our students table.