Database As Storage

A database is a central storage location that contains a large information collection. Databases are accessed digitally through a database management system which is an application software product that enables the 'manager' to create, update, and read from it. There are many types of databases, but this article focuses on relational databases that are managed with the Structured Query Language (SQL). The dictionary definition for database is 'a collection of data that is stored in a computer and that can easily be used and added to.' Furthermore, the dictionary definition of administration is 'the range of activities connected with organizing and supervising the way that an organization or institution functions.' Combining these two definitions describes how organizations, especially large ones, can utilize such a system to maintain their data about finances, customers, and products for example.

Many of the mobile and web applications we use today are tied to a database. This is how social media apps never forget your personal information. For example, a database for your favorite social app may store details such as your username, email, password, preferences, connections, and posts. This article is being retrieved from a database and if you'd like to know what database I'm using, you can contact me here😉.

SQL is a computer language used for specific types of databases and as the name suggests, it queries a database. SQL is known as a Data Definition Language (DDL), Data Query Language (DQL), Data Control Language (DCL) as well as a Data Manipulation Language (DML). These are just terms to describe its ability to carry out those kinds of tasks. In this article, I will be creating and managing a database that stores information about movie ratings. Have a look at the model below that illustrates a relational database.

Considering that a database is accessed through software, this implies the need for corresponding hardware to make this possible. A typical organization will own a large room that has many computers which house servers and databases. This is normally referred to as a data center. Ideally, organizations may install air conditioning in their data centers because the computers tend to overheat with all the operations that they do. Some advantages of databases are:

• They can store huge amounts of information compared to other traditional means.
• Security mechanisms can be applied to a database management system making it only accessible to specific people. This is important because confidential credentials can be stored in a database which may need to be kept safe from illicit individuals.
• Databases can be backed up for future use and can be accessed for historical information.
• Compared to file cabinets, databases are not prone to damage or theft.
• The format that relational database management systems use to store data is easy to access and modify.

Required Tools

In order to administer this SQL database, we will need the following tools:

1. SQL Server, here is the link for setup download.
2. Microsoft SQL Server Management Studio (MSSMS), and the link to download. There are many other alternatives to MSSMS such as PopSQL, Oracle SQL Developer, and PostgreSQL.

These are the only tools required for this range of activities. Please note, in order to use MSSMS you need to have previously installed SQL Server, you cannot use SSMS without that prerequisite. I have SQL Server 2019 installed on my computer and MSSMS 18s, but any version should be adequate. This web page is a great walk-through for installing SQL Server.

C.R.U.D

C.R.U.D is an acronym that stands for create, read, update, and delete respectively. It is commonly used in the software industry to refer to an integrated system that can carry out all 4 verbs. With SQL, a software developer is able to perform C.R.U.D actions on a database which is what makes SQL so popular and essential. A system built with an alternative language e.g. PHP or Java would require tremendous effort to do these tasks, whereas SQL has them in-built.

Relational Databases

This refers to a database setup with tabular data that depends on or 'relates' to other tables within the database. The image above is an example of how different tables in a database may require parameters in another. In my opinion, relational databases are a better alternative to non-relational databases because as the storage need grows, these systems do well with scaling. Non-relational databases organize data in ways other than tables, which include key-value stores, JSON, XML, and Graphs, with JSON being the most popular format. Did you know that data is transferred between client and server in JSON over the internet? When data is kept in a table, it is easier to transform it into other formats and I will demonstrate how SQL manipulates the columns and rows of a table, which have unique key identifiers.

An example of a non-relational database that is based on the JSON popular format:

Creating The Database

Tables are kept inside a database as descriptive collections of data. A database can have several tables and part of what creates the relational model is the ability of all the tables to interact with each other. Inside MSSMS, click on the 'New Query' button which is located in the ribbon at the top. This opens up a text editor where we can write out SQL code. To create a database, we use the following SQL command:

CREATE DATABASE Movie_Ratings;

If the command ran successfully you will get a response, 'query executed successfully' like the one displayed below:

Next, I'll need a table to store all the data. This can be done by writing the following SQL command, then pressing the 'Execute' button located in the top ribbon:

CREATE TABLE Movies(
 movie_id INT PRIMARY KEY,
 movieName VARCHAR(50),
 rating DECIMAL(5, 1),
 releaseYear INT
);

Microsoft SQL Server Management Studio:

SQL Data Types

The cells within an SQL table represent a specific form of information known as a data type. These should be specified in the table definition and cannot be altered during insertion. The basic SQL data types are:

• INT: This represents whole numbers that have no comma e.g. 10.
• DECIMAL(M, N): Stores the exact number or decimal with M being the total number of digits to be stored for this number, and N being the total number of digits to be stored after the decimal point (.).
• VARCHAR(10): This is a way to store a string of text. The number specified within parentheses specifies the total length of the string as in, a maximum of 10.
• BLOB: The BLOB data type is meant to handle large data such as images or audio files. It stands for Binary Large Object.
• DATE: This is meant to accept a value of a specific date from a calendar in the format 'YYYY-MM-DD' which is a four-digit year (YYYY), two-digit month (MM), and two-digit day (DD). New Year's Day for 2022 would be '2022-01-01.'
• TIMESTAMP: An extension to the DATE data type above. This stores a specific point in time when an action takes place. The format is 'YYYY-MM-DD HH:MM:SS.' 'HH' stands for hours, 'MM'stands for minutes and 'SS' stands for seconds.

In the table definition, the PRIMARY KEY specifies the column within the table that will uniquely identify a particular row. The 'movieName' column will accept a string with a maximum of 50 characters. Similarly, the 'rating' column will store a decimal with strictly two digits before and after the comma. I ran into an error when I first tried to define the last field of this table as the column name year. This is because SQL has reserved keywords that cannot be used as column names hence why I named the last column, 'releaseYear.'

If you wanted to specify that a column cannot accept empty cells, you would use the keywords NOT NULL in the table definition e.g. movieName VARCHAR(50) NOT NULL,.

Managing Tables

An SQL table can be completely erased by using the DROP keyword. For instance, if we no longer needed the table we previously created, we could type the command below. If you are using MSSMS, you will notice that the text file which contains the SQL queries we've been writing has become quite long. In order to execute a single statement, you would need to highlight the specific statement with the mouse cursor as shown below:

An SQL Text Editor With The Line To Be Executed Highlighted:

You will also notice that there is an asterisk (*) next to the file name which indicates that the file being worked on has not been saved. Save the file by pressing ctrl + s or accessing File on the ribbon and then Save All.

DROP TABLE Movies;

This is a powerful command because this could result in a major loss if the deleted table contained lots of valuable information. However, our table was empty so there are no issues here. We can re-create our table using the CREATE TABLE keywords. SQL also allows us to modify our table columns with the ALTER TABLE command. I could add a new column to the table by writing the following command:

ALTER TABLE Movies ADD star_actor VARCHAR(20);

This adds a new column to the Movies table which stores a string of 20 characters. In contrast, I could remove a column from our table by using this command:

ALTER TABLE Movies DROP COLUMN star_actor;

Inserting Data

Typing the following command will retrieve my whole table in its entirety, but at the moment, it will appear empty:

SELECT * FROM Movies;

An Empty SQL Table:

Our Movies table has no data in it and our organization is running broke! Let's insert some information so that we can appear as if we're productive:

INSERT INTO Movies VALUES(1, 'Doctor Strange', 8.0, 2022);

Now we can run the SELECT statement again, and this time, a populated table will be displayed.

SELECT * FROM Movies;

An SQL Table with 1 row:

It's also possible to specify the columns we want to insert data into:

INSERT INTO Movies(movie_id, movieName, releaseYear) VALUES(2, 'Dont Look Up', 2022);

This will omit the rating column that was written in the table definition:

Two things to note:

1. It's impossible to insert the same value twice in the column named movie_id because SQL prevents duplicate entries.
2. Fields other than the primary key will automatically assign a value of NULL in empty fields.

Updating Tables

SQL allows us to modify a previously entered row by typing the following command:

UPDATE Movies SET rating=7;

The above command is not a good one because it will allocate the value of 7 to all of the fields in the database. We instead specify the row that we want to modify by using the WHERE keyword as demonstrated below:

UPDATE Movies SET rating=7 WHERE movieName='Dont Look Up';

If you retrieve your table again, you will notice that the rating for the movie, Dont Look Up is no longer 'NULL' but is instead 7.0. This is a very small table, and I have a lot of information to enter. In order to insert multiple values into our database, we can use the following:

INSERT INTO Movies VALUES
   (3, 'Free Guy', 7.3, 2021), 
   (4, 'Jungle Cruise', 6, 2021),
   (5, 'King Richard', 8.5, 2021),
   (6, 'Minions Rise of Gru', 7, 2022),
   (7, 'Morbius', 7.4, 2022),
   (8, 'Nope', 6.8, 2022),
   (9, 'Old', 7, 2021),
   (10, 'Day Shift', 9, 2022);

Our table now has 10 values and looks like an active database 👍. This is a list of movies that I've watched between 2021 and now, however, the list is not conclusive.

If we wanted a more descriptive output, we could use the AS keyword in the SELECT statement.

SELECT movie_id AS ID, movieName AS Movie, rating AS Rating, releaseYear AS Released FROM Movies;

Notice how the column names have changed from the original:

Deleting Data

The WHERE clause is just one of the many conditional keywords in SQL that specify which item to modify. In the event that we wanted to get rid of a row, we would require a specific row otherwise all the data would be removed.

The command below erases all the information in the table: DELETE FROM Movies;

The command below erases one row from the table: DELETE FROM Movies WHERE movieName='Day Shift';

Final Remarks

These are the basics of the structured query language, with this knowledge it is easy to grasp other concepts of relational database administration. I'd also like to add that the style of SQL I've used here is completely optional, keywords can be written in lowercase. For example, I could retrieve a database table by writing:

select movie_id, movieName, rating, releaseYear from Movies;

or:

update Movies set rating=7 where movieName='Dont Look Up';

Which would provide the same result as the upper-case syntax.

Other areas of SQL worth exploring include:

• SQL Aggregate Functions.
• Database Schemas.
• SQL UNION Operator.
• Conditional Statements.
• The table foreign key.
• Sorting retrieved data.
• Creating views.
• Database triggers.

Let me know what you think of this article here

~ Thank you for reading