REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 35: INTRODUCTION TO DATABASES

Note: As you may have noticed, the title of this newsletter has changed a little. It is new and improved. It is longer, anyway. It started off as simply “Computer Programming for Kids,” then changed to “Real-World  Computer Programming for Kids,” and is now “Real-World  Computer Programming for Kids of All Ages.” So the middle part -- the original name -- has been retained, but the newsletter name has grown additional verbiage both fore and aft, now emphasizing the practicality and the inclusivity of it.

We just finished the first major Leg in our Journey, traveling through the sometimes bumpy “Windows Forms Land.” We will now continue our scintillating Journey by heading towards “Databaseville.”

We will still be using what we just learned, though. In fact, a litte bit later we will create another Windows Forms app to display the data that we’re going to be creating and working with. In that project, we will continue to use C# as the programming language and, of course, we will still be using Visual Studio as the IDE (Integrated Development Environment).

So what will be new? All of the Database-specific stuff, which we will start this next project with. This includes a new programming language (similar to the case with Javascript (as opposed to Java), not all people consider SQL to be a true programming language, but in my mind their arguments are hair-splitting ones, and I will speak of SQL as a true, valid programming language -- as I will of JavaScript later, when we get into it in the next major Leg of our Journey, on Web Technologies).

Note: The acronym “SQL” is sometimes pronounced with the letters spelled out: “S-Q-L” but more often it is a homophone of the word spelled “s e q u e l” and thus it is pronounced as such. So you might say, “I’m going to watch the sequel to Waylon and Willie Meet Godzilla” and you also might say, “I am going to write some SQL to query my database for how many movies there are about Waylon and Willie.” For those who don’t know who Waylon and Willie are, think of an Outlaw Country version of Bill & Ted.

So what is SQL? It stands for “Structured Query Language.” So it’s a language (a computer language); It’s Structured (of course! what else?); But the most interesting is the “Query” part of the name/acronym.

A Query is ... what? The same thing as that was (what I just wrote): a question. So using SQL you are asking questions using a structured language. Of whom are you asking the questions? The Database. Why? The database is full of data, but the Database may be so large and the Data scattered across so many tables that it is hard to make sense of it by just looking through the tables without “querying” it (asking for specific data).

As in all programming languages, you must ask the Database the question in exactly the right way for the Database to understand your Query and return any results (that is, the data that you want, that answers your question).

Note: If you’ve ever watched the televsion game show Jeopardy, you’ve already seen an example of how you need to ask a question (submit a query) in exactly the right way in order for it to be considered valid. For example, in response to “Who is the greatest Canadian rock singer?” you must say, “Who is Burton Cummings?” not simply, “Burton Cummings”

Photo of Burton Cummings (formerly of The Guess Who, not to be confused with The Who) by Shayne Kaye from Victoria, Canada / CC BY (https://creativecommons.org/licenses/by/2.0)

Let’s compare this machine/computer language (SQL) to human speech. When humans interact with each other, they can ask the same question in several ways, and still be understood. For example, to ask for the weather prophecy (prediction, forecast), one could say any of the following:

What’s the weather going to be today?

What’s the weather going to be like today?

What’s the weather today?

How’s the weather looking for today?

What does the weather man say?

How hot is it going to be today?

-- or even:

Am I going to need my umbrella today? (or coat, or galoshes, or whatever).

All of these things are understood by humans, most of them being virtually indentical. But in programming languages (C#, SQL, and all the others) only one very specific way of expressing things is accepted. Otherwise, the love letter that you write to the compiler (your code, that is) would be considered as having syntax errors, and the Database would refuse to give you even a clue about today’s weather.

Here is one more example, specific to databases: If you wanted to get information from a Database, but you knew nothing about SQL, you might go to the resident Database expert, whether he be a programmer or even a Database Administrator (more on this type of cat later*) and say to him, “Say, Fred, can you get me a list of all of our customers who live in Missouri?”

* It’s usally a cat, not a ... is it okay to say “chick”? Those two words usually go together (“cats and chicks”) ]

Photo of cats by Raul Varzar; photo of chicks by Olivia Colacicco; both from Unsplash.com

The dude would know what you meant, and write SQL like the following to obtain the information you so earnestly desire:

SELECT * FROM CUSTOMERS WHERE STATE = ‘MO’

That would retrieve all the records from a table named “CUSTOMERS” which has a Column named “STATE” which stores values in two-character abbreviations.

Whereas humans can “make up for” sloppiness in speech and understand a variety of ways of asking a question, computers won’t do that. You must “talk to them” in very specific terms. Any change in the above SQL statement would be seen by the Database as incomprehensible.

Let’s regress a little now, to go back to the very basics, and take it from there.

What is a Database?

A Database is a system that allows you to store information, in a structured way (hence, SQL, Structured Query Language, is the appropriate lingo to use in communicating with it). What people often mean when they use the word “Database” is simply “the data in the Database.” What we mean by “Database,” though, is more rightly termed a DataBase Management System (or DBMS). A DBMS includes, among other things, the following:

Tables

Triggers

Stored Procedures

Views

Of those, we will first concentrate on Tables. Tables contain columns. Each set of columns comprises a Row. To picture this in your mind, look at a spreadsheet that contains data. It will have columns labeled alphabetically (A, B, C, ... Z, AA, AB, AC, etc.) running from left to right across the top of the grid. Below that, starting at the far left, are rows running down the sheet labeled numerically (1,2,3, etc., to whatever the last row is). Here is an example of a spreadsheet with a few columns:

Softwaregenius / CC BY-SA (https://creativecommons.org/licenses/by-sa/4.0)

In this case, the data doesn’t actually begin until Row 3. Row 1 sports a Logo, and Row 2 contains the names of the Columns, or the “Header.” This is basically the view of what a very simple table in a database contains.

As you can see in that example above, a set of Columns make up a Row – both in a spreadsheet and in a Database Table.

To sum that part up, in a rudimentary way we can say that a Database is made up primarily of Tables. And Tables are made up of Rows of data, and the Rows are made up of Columns. But what are Columns, exactly? A column is a discrete (and potentially discreet, or restricted) bit of data. Columns, in turn, comprise a Name and a Data Type (like a variable in C# or just about any other programming language) – and possibly some other metadata* about them, such as a length value and whether they are considered the ID value or the Primary Key for the table. But discussion of such things is postponed for now.

* metadata is “data about data”

So, from a hierarchical perspective, you can imagine the structure of a Database as follows:

DATABASE

	Table1

		Row1

			Column1

				Name

				Data Type

				Other Characteristics

			Column2

			Column3

			... ColumnN

		Row2

		Row3

		... RowN

	Table2

	Table3

	... TableN

That’s a rather simplistic view of it, but it’s a helpful way to start off.

The next main point for this Step is, Why are Databases Useful?

That needs to be asked (and answered) because some might wonder why they can’t just store all of their data on spreadsheets and look it up there when they want information.

If your information is straightforward and relatively small in size, that’s okay; You can do that. But once your data grows large and/or complex, it will help you a lot to store it in a database. And doing so can also save space and prevent redundancy.

A word (or two, or so) about redundancy: You may recall we previously spoke about the DRY programming Principle (DRY = Don’t Repeat Yourself). In regular programming terms this means that once you have written something, there’s no reason to do it again (and there are very good reasons against doing so). That is to say, once you’ve got code in one place, don’t repeat it in another place if you can help it. Just retain the one block of code and call it from as many places as you need to. Specifically for Databases, though, what we’re thinking about when we use the DRY aronym is: Don’t store a value more than once.

By way of example, a Customer’s Address. Why store “157 Riverside Avenue, Champaign, Illinois 61821” over and over again throughout your Database, such as for every order the Customer at this address places. For one thing, it saves space to only store the Address once. For another thing, it prevents multiple unique versions of what is really the same address.

What I mean by “multiple unique versions of what is really the same address” is this: If you were to store the Address anew each time the Customer placed an order, you could end up with the following in your Database:

157 Riverside Avenue, Champaign, Illinois 61821

157 Riverside Avenue, Champain, Illinois 61821

157 Riverside Avenue, Champagne, Illinois 61821

157 Riverside Avenue, Champaign, IL 61821

157 Riverside Ave, Champaign, IL 61821

(etc.)

...and the software you use to send out mailings may see all of these as distinct (different from each other) and send out the same notice or advertisement to an address umpteen times – costing you extra money in postage and “shipping and handling” expenses, and quite possibly irritating the Customer (who is perhaps now an ex-Customer).

And for yet another thing, you don’t want to store a value such as a full address in a single column, anyway. Typically, each element of data should be stored in its own column. An Address Table might contain the following Columns: Street Address 1, Street Address 2, City, State, and PostalCode.

Notice that I alluded to having a separate Address Table. In other words, rather than having one humongous Table that contains everything and whose columns stretch off to the right into infinity (and possibly beyond), it’s more efficient to break your data up into multiple tables.

Photo by Benh LIEU SONG (Flickr) / CC BY-SA (https://creativecommons.org/licenses/by-sa/4.0)

But what is probably the main reason for using a Database to store your information? It is the fact that you can query it for exactly the data you need (such as “all the Customers in Missouri” or “all the States with more than 10 million Residents” or “all the Teams that have won multiple Super Bowls,” or what have you).

That being the case – that we want to use a database to store our data (rather than in a physical file cabinet, or in text files, or even in spreadsheet files), what we need to know is how to use SQL to get the data we want from the database. We will explore SQL as we take our next steps in our Journey, starting with some basic SQL in the next Step.

Until then!

$$$$$$$$$$$

The first volume of my four-volume series, Real-World Computer Programming for Kids of All Ages, namely Volume 1: Windows Forms Apps Using C# and Visual Studio is now available in both Paperback and Kindle formats. Volume 1 contains everything that has been printed in this Newsletter so far and through Step 34.

The kindle version of the book can be accessed here: https://www.amazon.com/dp/B08H7DKKCS/ and the Paperback version can be accessed here https://www.amazon.com/dp/B08H9RB1WG/

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: