Chapter 2: Basic Database Concepts

Today we’re going to build the foundation of everything we’ll do in MySQL. If you understand these basic concepts really well, the rest of the course will feel like a breeze. So let’s take it slow, with lots of examples, just like we’re sitting in a classroom together.

1. What is a Database? (Super simple definition)

Imagine you are the owner of a small coaching class in Mumbai (Andheri, maybe? 😉).

Every month you get new students. For each student you need to keep track of:

• Full name
• Phone number
• Email
• Which batch they joined (Morning / Evening)
• Fees paid or pending
• Date of joining
• Subjects they are taking (Maths, Science, English, etc.)

If you write all this information on loose papers or in different notebooks, soon you will have a big mess:

• Hard to find one student’s details quickly
• Easy to lose papers
• Duplicate entries by mistake
• Hard to calculate total fees collected

A database is like a super-organized, digital filing cabinet that stores all this information in a very structured, safe, and fast way. You can add new students, search any student in seconds, update phone numbers, delete old records, and even generate reports like “How many students joined in January?” — all with just a few commands.

Formal definition (but still human-friendly): A database is an organized collection of data that is stored and accessed electronically from a computer system. It allows us to store, retrieve, update, and delete data efficiently and securely.

2. What is a Relational Database Management System (RDBMS)?

MySQL is an RDBMS. Let’s understand what that means.

Relational → The data is stored in tables, and these tables can be related (connected) to each other using common fields.

Example – Our Coaching Class Database

We will create three tables:

Table 1: Students

Table 2: Courses

Table 3: Enrollments (this table connects Students and Courses)

Because these tables are related (through student_id and course_id), we can easily answer questions like:

• “Which courses is Priya enrolled in?”
• “How many students are taking Mathematics?”
• “Total fees collected so far?”

That’s the power of Relational databases!

RDBMS = Software that helps us create, manage, and use these related tables. Examples of RDBMS:

• MySQL
• MariaDB
• PostgreSQL
• Oracle Database
• Microsoft SQL Server
• SQLite (very lightweight, used in phones)

3. Important Terms: Tables, Rows, Columns, Primary Keys, Foreign Keys

Let’s look at the Students table again:

• Table → The entire grid (like “Students”)
• Column → Vertical (like “name”, “phone”, “email”)
• Row → Horizontal (one complete record of a student)
• Primary Key → A special column (or set of columns) that uniquely identifies each row. → In our example: student_id is the Primary Key → No two students can have the same student_id → It can never be empty (NULL)
• Foreign Key → A column in one table that refers to the Primary Key of another table. → In the Enrollments table, student_id is a Foreign Key that points to student_id in the Students table. → course_id is a Foreign Key that points to course_id in the Courses table. → This creates the relationship between tables.

Why Foreign Keys are important? They prevent wrong data! Example: You cannot add an enrollment for student_id = 999 if no student with ID 999 exists → MySQL will give an error.

4. Databases vs Schemas in MySQL (Very important difference!)

In MySQL, the terms are used a little differently than in other databases.

Important points:

• When you do CREATE DATABASE college; → you are creating a database (and also a schema — same thing in MySQL).
• Inside one database (college), you can have many tables: students, courses, enrollments, teachers, batches, etc.
• You can have many databases on one MySQL server:
  • college
  • hospital_management
  • online_shop
  • blog_website
• Each database is completely separate — tables with the same name can exist in different databases.

Other databases (like PostgreSQL, SQL Server)

• They treat schema as a namespace inside a database.
• One database → many schemas → many tables.
• But in MySQL → schema = database (they are the same).

Quick MySQL commands to see this:

That’s it for Chapter 2! 🎉

Quick Summary – What we learned today:

• Database = Organized digital storage
• RDBMS = Software that manages related tables (MySQL is one)
• Table = Grid of data
• Row = One record
• Column = One field
• Primary Key = Unique ID for each row
• Foreign Key = Link between tables
• In MySQL: Database = Schema

Homework for today (very important – do it now!)

1. Open MySQL (command line or Workbench)
2. Run: SHOW DATABASES; → copy-paste what you see here
3. Create your own database:
   SQL

   PHP

   0 1 2 3 4 5 6 7 8

   CREATE DATABASE coaching_class; USE coaching_class; SHOW TABLES;   -- (should be empty)

Tell me what you see after running these commands!