Ever wanted to know what a database is? Or how a database works?
There are 4.2 billion users with social media accounts. Each post, picture, like, and comment gets saved in a database.
How is it possible to store and retrieve this big data? Are there different types of databases? Where is all this information stored?
This article offers a ‘databases 101’ guide to everything data related.
Discover how a database system works including the SQL language. Learn how Cloud and NoSQL databases offer scalable solutions. See how to make a database of your own using SQLite.
Read this database guide to get fully versed in the expanding world of data science.
What Is a Database?
A database offers a way to store information on a computer system.
The most basic form of database is a table. Columns hold the type of data like a person’s name or phone number. Rows store the data itself like John Doe or 123-233482.
Databases and computer systems have always gone hand-in-hand dating back to the 1960s.
One of the key issues was how to store and retrieve information without having to duplicate content. For example, a customer can make multiple orders but the database should save their name only once.
This ‘relational database’ system joined different tables of data together. It allowed controllers to easily search for the content that they wanted. It also scaled to a high level to store not just thousands but millions of records.
What Is a Database Used For?
Traditional databases stored simple types of data like text and numbers. Modern versions can collate anything.
Amazon S3 is a Cloud database service designed to store any type of data object. That includes images or videos or even database files!
Website owners and app developers can link to S3 services to retrieve content for their services. They can also upload new content using a web API or application programming interface.
Other databases like MySQL and SQLite hold e-commerce data like sales records and product reviews.
The popular e-commerce platform WooCommerce communicates with its database multiple times per minute. It searches for user queries, inserts items to the cart, and produces statistics for reports.
Types of Databases
We’ve mentioned the most basic type of database is a table in the likes of Word or Excel. However, there are multiple types of database structures, each geared for a specific purpose:
- Relational – queried through structured query language (SQL)
- NoSQL – key/value pairs with no firm structure
- Columnar – save data in columns instead of rows
- Graph – optimized for social media platforms that use data points
Until the Internet exploded in popularity, most online databases had a relational structure.
An RDBMS (Relational Database Management System) separated content into linked tables. Yet, it couldn’t cope with large volumes of updates and inserts. And when the database ran out of storage the system stopped working.
A NoSQL database offers an alternative way to store and access data.
Instead of one single database with multiple tables, NoSQL databases store each piece of content as a file. Those files have no rigid structure so they’re flexible. They also scale or grow as required which is perfect for apps that host millions of users.
There are many popular database platforms that you can tap into to store your own information. And the great news is many are free!
MySQL is an open-source database system that’s used by millions of sites and apps including Uber.
SQLite is a popular open-source RDBS database because it’s very simple to use.
Many Android developers use the database on their apps to hold content. The system is flexible and not as strict as other databases which means you can create a working system within minutes.
How to Make Databases
You can download SQLite from www.sqlite.org but be warned – it’s not for the faint of heart!
Windows users can install a precompiled binary DLL file. Mac users can download a bundle of command-line tools. In either case, you’ll need to know how to use the terminal to use the system which isn’t ideal.
The great news is there are several tools that offer a GUI or graphical way to use SQLite.
Windows users can install DB Browser for SQLite while those with Mac or iOS devices can visit https://setapp.com/apps/sqlpro-for-sqlite.
Structured Query Language
Once you’ve installed SQLite and an editor to view/edit content it’s time to learn a few basic commands.
SQL or structured query language lets you retrieve and add information. The language follows the four fundamental actions of database control based on the CRUD acronym:
For example, to create a table using SQL use a CREATE TABLE statement:
CREATE TABLE user (name, address);
You can copy this code into your editor where it says Execute SQL and run it. A new table called users will appear with two fields – name and address.
Next, you want to add some data. Use the INSERT command:
INSERT INTO user (name, address) VALUES (‘Jill’, ‘123 Street’);
When you open the user table you’ll see a new row of information. Notice that the values correspond with the fields. Also, you need to surround the text with quotes.
Simple Select Statements Database Guide
The real power of a database comes when you want to search for data.
Add more records to your user table by changing the values and executing the SQL insert statement. When you have about ten rows enter the SELECT statement below.
SELECT * FROM user WHERE name = ‘Jill’;
The command will select all the content from the user table that matches the query. In this case, all users with the name Jill.
Try changing Jill to another name on your list. The record should change to match. Then try:
SELECT * FROM user WHERE name LIKE ‘J%’;
This wildcard query searches for all users with a name starting with J. You can even put the % character at the start too so all names with a J in them will show.
More Databases 101 Tutorials
This databases 101 guide has introduced you to the world of storing information.
An RDBMS like SQLite offers a quick yet powerful way to store data and test SQL. You can also host data on the Cloud and access it from your own apps.
Experiment and have fun! Then read more database guides on our blog.