"Use your words, dude."
–Vic from Red vs. Blue Season II
In our last episode we discovered that Dr. Codd did great things for database technologies and covered a little history of databases. We also discovered that Mary-Sue was going to have Billy-Bob’s love child while Jimmy-John tried to kick his addiction to Tootsie Rolls and …er…sorry wrong episode. So we just learned the database stuff forget everything else. In the grand scheme of things, you can hopefully appreciate the magnitude of Dr. Codd’s contribution.
Now I would like to turn our attention to the next big hurdle: vocabulary. Unfortunately, there is a great deal of confusion about some of the language used in database systems. In many cases there is little to no knowledge of the database vocabulary at all beyond "table" and "join" :P At this point you may be saying to yourself, "Man, I would really like a Diet Coke." If so, go get one I’ll wait… Welcome back :)
Okay let’s get down to business: Why should you give a crap about this stuff? I mean, many of you use databases every day why the hell do you need to learn anything more about them? The answer is simple and elegant: If you can’t speak the common language of databases you will be (not might be) much less effective communicating with others. Most notably, you will not be able, in some cases, to clearly articulate what you need and why you need it when talking to database professionals. With all this build-up you would think I was showing you the cure for cancer. It’s not quite that serious, but not having a common vocabulary is definitely a hindrance that can be easily avoided.
What are the words you need to know? I’m glad you asked!
NOTE: Those items denoted with an asterisk (*) are the official names used in relational database theory.
The Really Basic Basics
This section just lays out some extremely basic underpinnings to set the stage for more complex discussions.
Databases are collections of tables and other supporting items (like Indexes, Stored Procedures, etc… discussed later) that are used to store and/or manipulate data.
Table (AKA Entity*)
Tables are the fundamental building block of a database. They represent the collections of data that we store for later retrieval. Tables are also known as Entities (term taken from Set Theory in Mathematics). I know you have seen tables a gazzilion times but here is gazzilion + 1:
For the rest of this article we will essentially be breaking down the terms used to describe pieces of a table.
Columns (AKA Fields, Attributes*)
Columns store the individual pieces of data that we use in our tables. Each column is data about one thing. For example, the au_lname column stores the last name of each author in our sample table.
Rows (AKA Records, Tuples*)
Rows are collections of columns that represent all the information about one thing in a table. For example, in our table, each row consists of the author id, last name, first name, phone, address, city, and state. For example, the first row has all the information in our table on Johnson White.
The Not Quite As Basic As The Previous Basic
In this section we will dive a little deeper into the vernacular to explore some new and interesting terms you may not know about.
When we talk of rows and columns that is often where the story ends. Many of us don’t delve deeper either because we don’t know there is more or for other reasons. But there IS more. Let’s take a look at some terms used to describe multiples in a table.
You can refer to more than one column using these common terms: columns,fields,attributes. However, there is another way we can refer to them as well. The term is called the degree*. Looking at our table we can see it has a degree of 6. That is to say, there are 6 columns in our table. Pretty easy stuff.
When talking about more than one row we can, again, resort to common terms: rows, records, tuples*. Okay, maybe tuples isn’t common but you get the idea. However we also have a bit of verbal coolness we can apply here as well: cardinality*. The meaning of this word usually depends on context. For now, we will use the simplest definition of cardinality which is sometimes used to indicate the number of rows in a table. Strictly speaking this isn’t the most accurate definition but will serve as a start. Later we will expand on this definition.
Conclusion, Sort Of…
Okay so we aren’t really done but this is a good place to stop because as new things are introduced we will present their definintion at that time. Clearly there is much, much more to explore.