We need a database table when we are working with a large amount of data. Because simple text files and spreadsheets become messy and inefficient for large files.
Think of a database table as a well-organized digital filing box where data is stored in a structured way. It’s similar to a spreadsheet but has more rules, power, and consistency because it is created inside a database system.
Structure of a Database Table
A table has main three parts:
- Columns (Fields / Attributes)
- Rows (Records / Entries)
- Primary key (Unique Identifier)
1) Columns (Fields):
- A column defines what kind of data is stored in the table, and each column in a table represents a specific attribute of the data.
- For example, customer table has a multiple columns like, CustomerID, Name, Email, or JoinDate.
- The column name tells you what type of data each cell contains.
2) Rows (Records):
- Rows are the actual data stored under those columns.
- Each row is one complete piece of information (one record).
- For example: A row in the Customers table look like this:
- In simple words, individual pages in a file where each page has details about one person or item.
3) Primary Key:
- A primary key is a unique identifier for each row. It means, no two rows can have same primary key.
- For example: CustomerID could be the Primary Key because every customer has a unique ID.
- This prevents duplicate and mismatched records, such as two customers having the same CustomerID.
- A primary key is like a roll number in a classroom; even if two students have the same name, their roll numbers will always be unique to identify specific students.
Here you can see the unique ID of the customer for the particular customers.
Why Are Database Tables Important in DS?
Imagine you are a data scientist working with millions of customer records, transactions, or sensor readings. If all the information were stored in random files, then finding even one file would be hardest and challenging for us.
That’s why database tables are crucial; they give structure, speed, and reliability in the stored data.
1) Organized Data Storage:
It means all the data is stored in rows and columns, which makes it easy to find, sort, and analyze information.
For example, a sales table with customer information for OrderID, ProductName, and Price makes it simpler to analyze sales patterns.
2) Efficient Querying:
We can easily find specific data from a database table using SQL (Structured Query Language). It provides queries to find details.
For example, if you want to see all orders above ₹5000, one SQL query can filter it in milliseconds.
We don’t need to check 10,000 receipts manually, just ask the system “Show me all purchases above ₹5000, and you will get the answer instantly.
3) Data Relationships
As per our previous talk, tables don’t run independently; they can be connected with Primary Key and Foreign Key.
For example, we have two different tables
- The Customer table stores customer details, and the Orders table stores order details
- We can link both tables to each other with a CustomerID (Primary Key), and you can find out which customer placed which order.
- This is like connecting puzzle pieces, one table has personal info, another has purchase info, and together they tell the full records.
4) Data Integrity
This means that unique keys not allowing null values, or foreign key constraints, keep data clean and reliable.
For example, no two customers can have the same CustomerID. An Order table must be linked to a valid CustomerID table. This method prevents errors, duplicate entries, and missing details.
SQL Queries: Working with Database Tables
Here are some common SQL commands that can access the data from the database table
1) CREATE TABLE: This command creates a new table that will store the information
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100),
JoinDate DATE
);
In this example:
- We are making a table called customers.
- CustomerID is a unique number, like a roll number in school.
- Name, Email, and JoinDate are extra details we want to keep.
2) INSERT INTO: This command is used to insert a new record into the table.
INSERT INTO Customers (CustomerID, Name, Email, JoinDate)
VALUES (004, 'Emily Green', 'emily.green@email.com', '2024-04-01');
- We are adding a new row to the Customers table.
- This row is for Emily Green with her ID, email, and joining date.
3) SELECT: This command retrieves data from a table. Data scientists use this command to filter and analyze data.
SELECT Name, Email FROM Customers WHERE JoinDate > '2024-01-01';
- If you want to see any particular customer number, it will show that data from the table with this command.
- Here, we retrieve only the 2024-01-01 Join Date, so it will show according to the data.
4) UPDATE: We can modify and update existing data records using this command.
UPDATE Customers
SET Email = 'john.new@email.com'
WHERE CustomerID = 001;
- Here, we are going to the ID number 001 and change the Email to ‘john.new@email.com’.
5) DELETE: This command removes records from a table.
DELETE FROM Customers WHERE CustomerID = 003;
Write this above query, and it will delete the record for CustomerID 003.
Database Table Relationships
When we are working with only one table, we store customers in a Customers table and store orders in an Orders table.
But in real life, information is usually spread across multiple tables, and those tables need a way to “talk” to each other.
- CustomerID is the primary key in the Customers table, and the same CustomerID appears in the Orders table as a foreign key.