Last modified: July 18, 2020
This article is written in: 🇺🇸
Databases are the backbone of modern applications, serving as organized repositories where data is stored, managed, and retrieved efficiently. Think of a database as a digital library where information is cataloged systematically, making it easy to find and use. Whether it's a simple contact list on your phone or a massive system powering a social media platform, databases play a crucial role in handling data effectively.
+-------------------------------------------------------------+
| Database |
|-------------------------------------------------------------|
| [ Tables ] |
| |
| +----------------+ +--------------+ +--------------+ |
| | Users | | Orders | | Products | |
| +----------------+ +--------------+ +--------------+ |
| | UserID | | OrderID | | ProductID | |
| | Name | | UserID | | Name | |
| | Email | | Date | | Price | |
| +----------------+ +--------------+ +--------------+ |
| |
| [ Relationships ] |
| |
| Users.UserID <--------> Orders.UserID |
| Orders.ProductID <-----> Products.ProductID |
+-------------------------------------------------------------+After reading the material, you should be able to answer the following questions:
At its simplest, a database is a collection of information organized in a way that allows for easy access and management. Databases enable applications to store data persistently, ensuring that information remains available even after the application is closed or the system is restarted.
Databases offer several advantages over simpler data storage methods like text files or spreadsheets:
To communicate with a database, we use a language called SQL (Structured Query Language). SQL provides commands to perform various operations like creating tables, inserting data, querying, updating, and deleting records.
I. Creating a Table
To define a new table in the database:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);This command creates a "Users" table with three fields: UserID, Name, and Email.
II. Inserting Data
To add a new record to a table:
INSERT INTO Users (UserID, Name, Email)
VALUES (1, 'Alice Smith', 'alice@example.com');This inserts a new user into the "Users" table.
III. Querying Data
To retrieve data from a table:
SELECT * FROM Users;Output:
| UserID | Name | |
| 1 | Alice Smith | alice@example.com |
This command fetches all records from the "Users" table.
IV. Updating Data
To modify existing data:
UPDATE Users
SET Email = 'alice.smith@example.com'
WHERE UserID = 1;This updates Alice's email address in the "Users" table.
V. Deleting Data
To remove a record:
DELETE FROM Users
WHERE UserID = 1;This deletes the user with UserID 1 from the "Users" table.
Establishing relationships between tables allows for more complex and meaningful data queries. The most common types of relationships are one-to-one, one-to-many, and many-to-many.
An example is a user who can place multiple orders:
+-----------+ +-----------+
| Users | | Orders |
+-----------+ +-----------+
| UserID | | OrderID |
| Name | | UserID |
| Email | | Date |
+-----------+ +-----------+The "Orders" table references the "Users" table through the UserID field, indicating which user placed each order.
To retrieve data that spans multiple tables, we use SQL JOIN operations.
SELECT Users.Name, Orders.OrderID, Orders.Date
FROM Users
JOIN Orders ON Users.UserID = Orders.UserID;Output:
| Name | OrderID | Date |
| Alice Smith | 1001 | 2024-02-01 |
| Bob Jones | 1002 | 2024-02-02 |
This query combines data from the "Users" and "Orders" tables to show which orders were placed by each user.
Imagine a database as a warehouse filled with filing cabinets:
This structure allows anyone to find specific information quickly, much like a well-organized database facilitates efficient data retrieval.
While relational databases using SQL are common, there are other types of databases designed for specific needs.