3. Database Basics

This lesson will teach you, as a Test Analyst, just the right amount of what you need to know about databases in order for you to learn SQL.

Table of contents

1. What is a Database?
2. Database Tables
3. Rows, Columns and Data Types
4. Primary keys
5. Summary
6. Test Yourself

1. What is a database?

You will have come to this tutorial to learn SQL, however, in order to learn SQL it is important for you to know a bit about databases, about the data you will be investigating.

So…what is a database? Well, to keep it simple…a database is a store for a collection of related data. 

As an example, if we take a look at an online application, as in figure 3.1 – Home Finance’s Transaction Page, this application will have its own database where it will store its related data. Examples of some of the related data that it might store are ringed in red:

• Account (shown in brackets), e.g. Cash, Visa, Deposit Account etc
• Category (what category type your money goes against e.g. Weekly Shopping”, “Car: Maintenance”, “Holiday: Spend Money”, “Misc Expenses” etc)
• Account Transactions

3.1 – Home Finance’s Transaction Page

As can be seen from the following figure, a Database is made up of Tables. A table is made up of Rows of data. Each row is made up of Columns (which will have different Data Types)

Where * equals zero to many

3.2 – A Database structure

The rest of this section will be used to explain what all these different Database components are and how they are used, ultimately…showing you how you will be interacting with a Database to actually get (retrieve) some data.

back to table of contents

2. Database Tables

As we saw, above, an application stores related data in a database. Each specific type of data (e.g. Accounts, Categories and Account Transactions) will be organised in the database by each of them being stored in their own structured file called a Table. There will be one or many tables in a database.

A table gets its name from the structure of the data it contains, as you will see, a table is in a “table like” format, a bit like a spreadsheet…with rows and columns.

The thing to note is that each table will only store data of a specific type, e.g. an Account Table will store information about Accounts, an Account Transaction Table will store information about an Account’s transactions. You wouldn’t have a mixture of 2 specific types e.g. Account and Account Transaction!

Let’s look at an example of an Account Transaction table’s data as you would see it retrieved from a database:

SELECT *
FROM account_transaction
;

3.3 – Account Transaction Table layout

You will see from the above that the table has 3 rows of data, where each row has 9 columns.

back to table of contents

3. Rows, Columns and Data Types

3.3.1 Rows

So, as you have seen, a database table is in a “table like” structure, i.e. it has rows and columns. 

To store data in a table, it will be stored as a Row. To use figure 3.3 – Account Transaction Table layout, above, as an example, each Account’s transaction will be stored as a row, i.e. the row with id=3 is a record of an account transaction where money has gone from a VISA account to pay “The Big Supermarket”.

In the IT industry, you will find that either Row or Record is used to describe a table entry. They can be used interchangeably

3.3.2 Columns

As in any table format, we will have columns which will hold specific information. 

Again, using the example 3.3 – Account Transaction Table layout, the first column will contain the trans_id, the second is the account_name, then the trans_date and then the payee etc. Each type of info is stored in its own column.

Because each column has its own specific type of information, it will have its own different Data Type, i.e. the Data Type defines (restricts) what type of information can be stored in a column.

You may hear IT people referring to a row’s column as a Field. They can be used interchangeably, i.e. a field refers to a specific cell of a given row.

3.3.3 Data Type

You will not need to know too much about data types for you to complete this tutorial, as I will guide you through what to do by example. However, it will still be good to just briefly introduce you to some of the most common data types you will be faced with when executing SQL:

• INT
  • Stands for integer and holds whole numbers

• DECIMAL(size,d)
  • Used for anything that needs to hold a decimal number. In the brackets, for currency for example, you would replace size,d with 9,2…where you will get seven whole numbers and 2 decimal places

• VARCHAR
  • Stands for variable length character. Used to hold letters, numbers, and special characters

• DATE
  • Holds the date as a format YYYY-MM-DD. Where YYYY = year, MM = month and DD = day

• TIMESTAMP (YYYY-MM-DD HH:MM:SS)
  • Holds a date and time format of YYYY-MM-DD hh:mm:ss. Where hh = hours, mm = minutes and ss = seconds

As an example of data types used, in the table layout 3.3 – Account Transaction Table layout, the trans_id would be an INT column as it is a number,  account_name would be a VARCHAR as it could hold both characters and numerics, trans_date would be a DATE, income would be a DECIMAL.

Again, don’t get too hung up about understanding data types too much, as all will become clearer as you work through examples in the next lesson.

back to table of contents

4. Primary Keys

A Primary Key uniquely identifies each row in a table. It does this by making a column (or set of columns) a unique key.

As an example, if you live on a street, you will have a House Number, this will be the unique identifier for your house to be found. If you had the same number as another house on your street, then the postman would struggle to deliver your post 🙁 ? This is the same for Primary Keys in a Table, it is used to uniquely find a row. 

A Primary Key can be defined as one or more columns in a Table. However, there are constraints on Primary Keys:

• There can only be one Primary Key created for a Table
• They cannot contain a NULL value, i.e. be empty
• They must be unique, two rows cannot have the same Key value
• They shouldn’t really be modified or reused, as this will cause problems for a system

back to table of contents

5. Summary

This section may only have been short, but we have learned a lot. So, let’s briefly look at what new terminology we have learned.

back to table of contents

6. Test Yourself

Tests to gauge your progress…

At the end of each lesson of this tutorial, I have produced a link to some Test Yourself quizzes to see how much you have learned.

Having previously come across tests myself where the answers are so ambiguous that you end up doubting what you know and don’t know…I have attempted to make these quizzes, not as to catch you out, but so as to help you reaffirm your understanding and to give you confidence that all you’ve learned makes sense! 

Before you move on to the next section…

So, let’s see how much you have learned about Database Basics. In this first test, you will be given 25 questions, with the answers at the end of the test. 

Once complete, check your answers, if you do not score that well (let’s say for this section…not getting at least 20 correct), please either go back over the tutorial’s section again, perhaps making more notes as you go, or re-do the test to ensure you have a good understanding. 

It is always important that you understand the section that you have read / learned before you move on 🙂 !

Click here to Test Yourself.

back to table of contents