Understanding Relational Databases

Imagine you have a LEGO set with different pieces. Each piece has a specific shape, color, or purpose. Relational databases are like a LEGO set for organizing information. They store data in tables (like grids or spreadsheets), where every piece of data is carefully placed in its correct spot. Let’s dive into the world of relational databases, step by step.

What is a Relational Database?

A relational database is a system that stores and manages data in a structured way. The data is organized into tables, which are made up of rows and columns.

• Table: A collection of related data. Think of it as a page in a notebook.
• Row: A single record in the table (like one entry in your notebook).
• Column: A category of information (like the title of each section in your notebook).

For example, here’s a table of students:

Key Terms in Relational Databases

1. Primary Key: A unique identifier for each row in a table. In our table above, StudentID is the primary key because it uniquely identifies each student.
2. Foreign Key: A column in one table that links to the primary key of another table. This helps connect tables together.
3. Relationships: Tables can be connected through keys, forming relationships like:
   • One-to-One: One row in a table matches one row in another.
   • One-to-Many: One row in a table matches multiple rows in another.
   • Many-to-Many: Multiple rows in one table match multiple rows in another.

Querying a Relational Database

We use a language called SQL (Structured Query Language) to interact with relational databases. Let’s learn some basic SQL commands:

1. Creating a Table

This command sets up a new table.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(1)
);

2. Inserting Data

Add records to the table.

INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES (1, 'Alice', 10, 'A'),
       (2, 'Bob', 11, 'B'),
       (3, 'Charlie', 10, 'A');

3. Retrieving Data

Fetch records from the table.

• Get all students:

SELECT * FROM Students;

• Get students who scored an ‘A’:

SELECT Name FROM Students WHERE Grade = 'A';

4. Updating Data

Modify existing records.

UPDATE Students
SET Grade = 'A+'
WHERE Name = 'Bob';

5. Deleting Data

Remove records from the table.

DELETE FROM Students
WHERE Age = 10;

Practice Questions

1. Create a table for books with columns: BookID, Title, Author, and Price.
2. Insert three books into the table.
3. Write a query to find all books priced above $20.
4. Update the price of a specific book.
5. Delete a book written by a specific author.

Practice Solutions

1. Create a books table:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(50),
    Price DECIMAL(5, 2)
);

2. Insert books:

INSERT INTO Books (BookID, Title, Author, Price)
VALUES (1, 'Harry Potter', 'J.K. Rowling', 25.99),
       (2, 'The Hobbit', 'J.R.R. Tolkien', 19.99),
       (3, '1984', 'George Orwell', 22.49);

3. Find books above $20:

SELECT * FROM Books WHERE Price > 20;

4. Update the price of ‘1984’:

UPDATE Books SET Price = 20.49 WHERE Title = '1984';

5. Delete a book by J.R.R. Tolkien:

DELETE FROM Books WHERE Author = 'J.R.R. Tolkien';

Interview Questions

1. What is a primary key, and why is it important?
   • A primary key uniquely identifies a record in a table, ensuring no duplicate entries.
2. Explain the difference between WHERE and HAVING clauses in SQL.
   • WHERE filters rows before grouping; HAVING filters groups after grouping.
3. What is a foreign key, and how is it used?
   • A foreign key connects two tables by referencing the primary key of another table.
4. Write a query to join two tables (Students and Classes) and list all students with their class names.

SELECT Students.Name, Classes.ClassName
FROM Students
JOIN Classes ON Students.StudentID = Classes.StudentID;

5. How do you ensure data integrity in a relational database?
   • Use constraints like primary keys, foreign keys, unique keys, and checks.

Final Thoughts

Relational databases are powerful tools for organizing and retrieving data efficiently. By mastering SQL and understanding how tables relate, you can build and manage robust databases. Start with simple queries and practice regularly to become a pro!