IBM, And The Paper That Built Our World of Data

One company I have so much respect for is IBM. For decades, it was IBM who were at the forefront of computing, and who conducted world-leading research. And, so, in 1957, they created one of the first programming languages: FORTRAN — aka FORTRAN 57. At that time there were no large-scale computer disks, but where the program was stored on punch cards:

Figure: https://wp.ufpel.edu.br/diehl/files/2016/05/f90_lec1.pdf

FORTRAN received a major boost in 1977 with the creation of FORTRAN 77. The language was good at translating maths formulas into code, but not so good at building databases. And, so, it was in 1970 that Edgar Frank Codd published one of the most significant papers ever published in the world of software:

The paper outlined the world of relational databases - and which spawned the SQL programming language. Codd was awarded the Turning Award (the Nobel Prize of Computing) for his work in 1981.

In the paper, Edgar proposed a new way of modeling data and used relationships — aka tables. This allowed us to change data within certain tables without affecting other tables — all of the existing methods at the time involved new data disrupting the complete organisation of the data in the database. The tables themselves are arranged in rows and columns, and where the columns represent attributes of the data. It took until 1973 before Codd had the opportunity to scale up his work. Up to this point, Codd had defined his query system using the mathematical notation:

The drive was then to codify the maths, and it was Don Chamberlin and Ray Boyce who created a query language for relational databases based on Codd's algebraic notation. This led to SQUARE (Specifying Queries as Relational Expressions), and then Sequel (Structured English Query Language), and which was finally renamed SQL:

In 1974, Ray Boyce — at the age of 26 — died soon after the initial creation of SQL, and never saw the true impact of his work. Over the years, SQL has taken off in ways that could never have been imagined.

SQL in the Cloud

SQL (Structured Query Language) is one of the oldest programming languages and has done us well. It was created in 1974 and is more than 49 years old! But, it is rather fixed in its ways and does not cope well with newer data object formats, such as JSON. SQL databases use rows and columns to store data, and which are matched to a fixed schema. For a more traditional database,

AWS offers the Amazon Relational Database Service (Amazon RDS), Amazon Redshift, and Amazon Aurora. RDS is used for relational databases, and where there are complex transactions or complex queries. They have a medium to high query or write rate, and supported high durability. The database types supported are Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server.

With a relational database, we have a fixed schema, and where records are stored in rows and columns. We then use SQL to query the records. In a non-relational mode, we can have a dynamic schema and use key-value pairs:

Figure 1: Relational (SQL) and Non-relational databases Ref: AWS Academy Cloud Foundations slides

Amazon Aurora is an enterprise-class relational database and is compatible with MySQL and PostgreSQL. Overall, it automates key tasks such as provisioning, backup, recovery, and failure detection. Amazon Redshift has applications into Big data applications, including those related to enterprise data warehouses (EDWs). It has a core focus on the data, and less on database management.

RDS

We will first create our RDS database, by selecting RDS, and with “Create database”:

Next we will create a MySQL database:

Scroll down and select “Free tier”:

Next, we will give the MySQL database the name of “MyDataBase”, and a master account name of “admin”, and a password of “napier123”:

We will create a 20GB database with SSD storage:

We will then enable public access to the database, and create a new VPC security group to open up TCP Port 3306:

Then finally to create the database:

It will then take a few minutes to create the database:

We should then get the details:

We then take note of the connection. In this case, it is:

mydatabase.cll88eb5nhtz.us-east-1.rds.amazonaws.com

and the TCP port is 3306.

Install MySQL Workbench

Now install MySQL Workbench [here].

Connecting to the database

We can then connect to the SQL service using the public endpoint provided by AWS:

For each SQL command, click on this button:

We can create the database schema with:

CREATE DATABASE MyDataBase;

Next right-click on the SCHEMAS area on the left-hand side, and refresh. You should now see the MyDataBase schema. Now double-click on it, to select it.

Now enter the definition for the table:

CREATE TABLE Students (
            Username varchar(255),
            LastName  varchar(255),
            FirstName varchar(255)
            );

Now we will add some data:

INSERT INTO Students (Username, LastName, FirstName) values ('fsmith','Smith',"Frank");
INSERT INTO Students (Username, LastName, FirstName) values ('bsmyth',"Smyth","Bob");
INSERT INTO Students (Username, LastName, FirstName) values ('csmithes','Smithes',"Colin");

Now we will enter:

SELECT * FROM Students;

Now we can order based on the first name:

SELECT * FROM Students ORDER BY FirstName

Now we can order based on the first name (in reverse order):

SELECT * FROM Students ORDER BY FirstName DESC

If we try:

SELECT LastName,FirstName FROM Students ORDER BY LastName

We get:

LastName  FirstName
-------------------
Smith     Frank
Smithes   Colin
Smyth     Bob

Security of the database

Note, the security group has opened up the firewall for just one IP address, and which is the address of the host that created the RDS database:

The /32 subnet matches all the parts of the IP address.

Conclusions

SQL has changed the world in so many ways. The future, though is likely to be NoSQL:

https://asecuritysite.com/aws/lab11

https://asecuritysite.com/aws/lab12