1. Databases, Database Design and SQL

In today’s modern digital world dominated by Artificial Intelligence and Machine Learning, data is the new fuel. Data is at the core of every application - from fitness apps, healthcare and e-commerce to banking and social media. We use databases to efficiently store, manage and retrieve this data.

What is a Database ?

A database is a organised collection of structured information, typically stored electronically inside a computer. It allows data to be easily stored, retrieved, modified and deleted.

Databases are managed by software known as a Database Management System (DBMS). Popular databases include MySQL, PostgreSQL, Oracle, or SQLite.Why Database Design Matters

Why Database Design matters ?

Good Database Design like good architecture ensures the following :

1. Data consistency and integrity

This means that the data in the database is always correct and follows the rules.

Examples:

• The user email needs to be stored in a proper format.

• A product order on an e-commerce website must always be linked to a real user.

• The User record must not be duplicated or missing.

2. Scalability

As more people start using the application, more data is added to the database. As the size grows, a solid database design will ensure that the data does not slow or break the functionality of the application. A good database design ensures high scalability and acts as a bulwark against data loss, inconsistency and system crashes as the number of users using the application increase in the future.

3. Efficient queries and performance

When users use the app, they expect it respond fast. Efficient queries help the database to return the needed data quickly without wasting resources and time.

Example: We have a healthcare app wherein the user wants to find “all protein supplements under ₹500”. The SQL query that we write must execute this requirement in a jiffy as per user’s expectations.

4. Ease of maintenance

This means that the database is easy to update, fix bugs or improve later.

A well-design database makes it easier to add new features.

For example the user who orders protein supplements on a regular basis from our app, gets a discount coupon to use after every purchase.

Database Design involves defining tables, relationships, keys, and constraints that map real-world entities (like users, products, and orders) into a relational structure.

What is SQL or Structured Query Language ?

SQL is the standard language for interacting with relational databases. SQL is often pronounced as “sequel”. It helps the user to :

• Query data (using the SELECT statement)

• Insert new records (using the INSERT statement)

• Update the existing data (using the UPDATE statement)

• Delete data (using the DELETE statement)

• Create and modify schema (CREATE, ALTER, DROP)

Example :

SELECT name, price FROM products WHERE category = 'Supplements';

What is a Schema in a Database ?

A schema is a blueprint or layout of the database.
It defines:

• What tables the database will have (like Users, Products, and Orders)

• What columns are in each table (like name, email, and price)

• What type of data each column can hold (like text, numbers, dates)

• How tables are related (like which user placed which order)

  If we compare database design to building a home:

• The schema is like a floor plan.

• The tables are like rooms, kitchens, bedrooms etc.

• The columns are like furniture and fixtures (stove, fridge, air-conditioner, bed etc)

• The data is what fills the rooms (real people and real items)

Example of a schema

CREATE TABLE Users(user_id INT PRIMARY KEY, name VARCHAR (100),
email VARCHAR (80),
password VARCHAR (25)
);

CREATE TABLE Orders(order_id INT PRIMARY KEY, 
user_id INT, 
order_date DATE, 
total_amount DECIMAL(100,2),
FOREIGN KEY (user_id) REFERENCES Users(user_id));

The above schema says that there are two tables - Users and Orders. Orders is linked to Users using a foreign key.

We will discuss primary keys, foreign keys and others such details in a later chapter.