01- Databases (INTRODUCTION TO SQL)

GOALS of the writing:

1. Understand databases and their structures
   Will discuss how databases and the data they store are structured
2. Extract information from databases using SQL

What is a Database?

A database stores data.

Let’s imagine we are in charge of storing and organizing data for a library.

Image from fortbend.lib.tx.us

For the library database, we might set up databases that hold information on patrons, books and checkout. See the image below for reference.

Image from DATACAMP

The information you see in patrons, books and checkouts is saved in objects called tables, with data organized in rows and columns.

Our library database contains a Patrons table, a books table, and a checkout table.

Let’s have a closer look at patrons:

patrons Table From Library Database:

A closer look at the table below shows that it carries various data about our library’s patrons, like library card no, name, the year the patron became a library member, and the total overdue fine the patrons owe our library.

WHAT IS A RELATIONAL DATABASE?

A relational database defines the relationship between tables of data inside the database.

For example, each of our library patrons might be associated with many checkouts. Look at the reference image below:

Relational Database

Through these relationships, we can draw conclusions about data housed in separate tables in the same database. And we can answer the questions such as “Which book did Iqra check out during 2022?” or “Which books are checked out more often?”

WHAT ARE THE ADVANTAGES OF DATABASES?

The tables (patrons, books, checkouts) look similar to the way data is organized in spreadsheet applications such as Excel or Google sheets. But databases are far more powerful than spreadsheets.

• More storage than spreadsheets applications
• Storage is more secure due to encryption
• The biggest advantage of databases is that many users can write queries to gather insights from the data at the same time.

Many people can access data from DB at times

When a database is queried, the data inside the database does not change. Rather the database information is accessed and presented according to instructions in the query. This leads us to SQL

WHAT IS SQL(Structure Query Language)?

SQL is the most widely used programming language for creating, querying, and updating relational databases.

Once we are familiar with what data we have and which table it’s stored on. We can use SQL to begin writing queries to answer questions about our library.

Example:

SELECT *
FROM patrons 
LIMIT 30;

CHECK YOUR KNOWLEDGE!

1. What are the advantages of databases?

Imagine you are part of a discussion at work about whether or not to create a database. You’ve learned about several advantages of storing data in a database rather than other traditional formats like spreadsheets.

See if you can remember what they are by selecting all of the advantages.

Possible Answer

1. More storage

2. Many people can use it at once

3. It can be secured with encryption

4. Can easily see all data at once

5. Fast and easy setup

NOTE: You can comment on the article if you know the advantages of Databases. And this way, you can help people who are confused because they are new in the field.

2. Data organization

If you’d like to use SQL to gain insights from data, understanding the organization of a database is an important first step. Take a look at the database below. Which of the following statements correctly describes its organization?

Possible Answers

a) This is a table containing three relational databases: employees, job_levels, and departments.

b) This is a relational database containing three tables: employees, job_levels, and departments.

c) This is a database, but it is not relational because no relationship exists between job levels and departments.

d) This is not a database because there is no SQL code shown.

NOTE: You can comment on the article if you know the advantages of Databases. And this way, you can help people who are confused because they are new in the field.

Now that we know the basic organization of databases, let’s take a closer look at the main building blocks of databases.

WHAT ARE TABLES IN SQL?

We know that databases are organized into tables, which hold related data about a particular subject. And we also know that tables are organized into rows and columns.
In the world of databases, rows are often referred to as records. And columns as fields. A table’s fields are limited to those set when the dataset was created. But the number of rows is unlimited.

GOOD TABLE MANNERS

Let’s talk about table naming:
- Table names should be lower cases and should not include space. We can use underscore in place of spaces.
- And ideally, a table name refers a collective group, there for a table can have a plural name.

Table name

RECORDS:

A record is a row in the table. It holds data on individual observation.

Record example

FIELDS:

A field is a column in the table. It holds one piece of information about all the observations in the table.

A field in the table

Fields’ names are important. And they must be typed out when querying a database with SQL.

• The field name should be lowercase
• Should not involve spaces
• A field name should be singular rather than plural
• Two fields in a table can not have the same name
• The field names should have different names than the table names

Field name

WHAT IS A UNIQUE IDENTIFIER?

• A unique identifier is key.
• Unique identifiers are used to identify records in a table.
• They are unique and often numbers

Identifiers

Why Have More Tables in DATABASE?

The more, the merrier!

Having more tables, each with a clearly marked subject, is generally better than having fewer tables where information about multiple subjects is combined.

Take a look at the separate tables of patrons and checkouts.

Here is how our table would look like, if both tables were combined.

It’s the same data. But much less clear. Because it no contains duplicate information.

While we can see that Izzy has two checkouts,, and Maham has none. The card_num column is no more unique. Because of Izzy’s multiple checkouts. We can always use SQL to gather information from multiple related tables and connect them if a question requires it. But table topics should remain separate.

3 Picking a unique ID

You’ve learned that a unique identifier is a unique value that identifies a record so that it can be distinguished from other records in the same table.

Let’s take a closer look at the employees' table. Which of the fields do you think is best suited to be a unique identifier?

Employee table

Possible Answers

name

dept_id

year_hired

id

NOTE: If you know the answer, do comment to share your knowledge with other learners.

Our very own table

We’ve set up a database inside this course, and the books table is available in the exercise. You'll use SQL to query this table in the next chapter, but for now, it's time to explore what data books holds!

Your task is to choose the option below that best describes the information contained in books.

SELECT * 
FROM books;

QUERY RESULT:

Query answer

Possible Answers

books contains records for id, title, author, year, and genre.

books contains fields for id, title, author, year, and genre.

books contains records for title, author, year, and genre. id is a unique identifier but not a record.

books contains fields for title, author, year, and genre. id is a unique identifier but not a field.

NOTE: If you know the answer, do comment to share your knowledge with other learners.

DATA

Now we will focus on data inside the database as well as its storage. When a table is created, a data type must be an indicator for each field. The data type is chosen based on the type of data that the field will hold

• A number
• Text
• Or a date

Different data types

WHY DO WE NEED DATA TYPES?

We use several types for several reasons.

1. Different types of data are stored differently and take up different amounts of storage space.
2. Some operations only apply to certain data types. It makes sense to multiply a number by another number. But it does not make sense to multiply one text by another text.

STRING DATA TYPE:

A string refers to a sequence of characters, such as letters or punctuation.

String

The name field in the patrons table is made up of string. Such as ‘Maham, Jasmin’.

SQL has several different data types that can hold strings. Some string data types can hold short strings only, such as up to 250 characters. Storing short strings in a small data type saves storage and space.

VARCHAR

SQL’s VARCHAR is more flexible. And can store small or large strings. VARCHAR can store up to tens of thousands of characters. Because of its flexibility, it is very commonly used for storing strings.

INTEGER DATA TYPE

Integer data type stores whole numbers.

Integer

SQL offers a few different data types for storing integers. But it depends on how big the number we would like to store.

INT

INT is a common SQL integer data type. It can store numbers from less than two billion to more than positive two billion.

FLOAT DATA TYPE

FLOAT data type stores numbers that include fractional parts. Such as 2.5, 3.907, 5.6 and more.

Float

NUMERIC

The NUMERIC data type holds float, which is up to 38 digits in total, including before and after the decimal point.

Well, now that we are familiar with data types, we can look at a Database Schema.

WHAT IS A DATABASE SCHEMA?

Schemas are often referred to as ‘blueprints’ of databases.

A schema shows a database’s design, such as what tables are included in the database and any relationship between its table. A schema also lets the reader know what data type each field can hold.

The schema for our library database shows the VARCHAR data type is used for strings like book title, author. and genre.

DATABASE SCHEMA

We can also see that the patrons table is related to the checkouts table, but not the book table. And checkouts table is related to books table.

DATABASE STORAGE

The information we find in a database is physically stored on a hard disk of a server.

WHAT ARE SERVERS?

Serves are centralized computers that perform services via requests made over a network. In our case, the service performed is data access, but servers are also used to access websites or files stored on the server.

SERVER

Any computer or laptop can be a server if it is set up to provide a service. However servers are generally very powerful and large machines, because they are the best equipment to handle a high volume of requests and data.

4. At your service

Now that you know more about how data is stored, it’s time to test those skills!

Select the statement about database storage that is false.

Possible Answers

1. Servers can be used for storing website information as well as databases.

2. A server can handle requests from many computers at once.

3. Servers are usually personal computers such as laptops.

4. Data from a database is physically stored on a server.

5. Finding data types

Imagine that you are starting a new job and have just started getting to know your new employer’s database. You know that it’s important to know the data type — such as VARCHAR, INT, or NUMERIC—corresponding to each field in a table. Where could you find this information?

Possible Answers

You can find this information by looking at each table in the database.

You can find this information by looking at a diagram of relationships between tables.

You can find this information by looking at the values in each field for each table.

You can find this information by looking at a database schema.

Note from Auther:

Reading is an excellent way to expand your knowledge and understanding of various topics. To get the most out of an article, it’s essential to read it carefully and engage with the material. One effective strategy is to answer any questions posed throughout the article. Doing so can help you to consolidate your understanding and reinforce key concepts.

Therefore, I would encourage you to take the time to read the article carefully and try to answer any questions that are presented to you. By doing this, you can develop a deeper understanding of the topic and increase your overall knowledge. Remember, the more you engage with the material, the more you are likely to retain and apply it in the future.

HAPPY LEARNING