Overview of Relational Databases

Relational databases are a type of database that organizes data into tables, which are made up of rows and columns. Each table represents an entity, and relationships between these entities are established using keys. Relational databases are widely used because they provide a structured way to store, retrieve, and manipulate data using SQL (Structured Query Language).

Key Features of Relational Databases

• Data is stored in tables (relations).
• Each table has rows (records) and columns (fields).
• Data integrity is maintained using primary keys and foreign keys.
• SQL is used to query and manipulate the data.

Example: Relational Database for a Library

Let's explore a simple relational database that manages books and authors in a library.

1. Creating Tables

First, we create two tables: Books and Authors. The Books table references the Authors table through a foreign key.

      CREATE TABLE Authors (
          AuthorID INT PRIMARY KEY,
          Name VARCHAR(100),
          Country VARCHAR(50)
      );

      CREATE TABLE Books (
          BookID INT PRIMARY KEY,
          Title VARCHAR(200),
          AuthorID INT,
          Genre VARCHAR(50),
          FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
      );
    

2. Inserting Data

Next, we insert data into the Authors and Books tables:

      INSERT INTO Authors (AuthorID, Name, Country)
      VALUES 
      (1, 'J.K. Rowling', 'United Kingdom'),
      (2, 'George R.R. Martin', 'United States');

      INSERT INTO Books (BookID, Title, AuthorID, Genre)
      VALUES 
      (1, 'Harry Potter and the Philosopher''s Stone', 1, 'Fantasy'),
      (2, 'A Game of Thrones', 2, 'Fantasy');
    

3. Querying Data

To retrieve information about books and their authors, we can use a JOIN query:

      SELECT Books.Title, Authors.Name, Authors.Country
      FROM Books
      JOIN Authors ON Books.AuthorID = Authors.AuthorID;
    

4. Updating Data

To update the genre of a book, use the UPDATE statement:

      UPDATE Books
      SET Genre = 'Adventure'
      WHERE BookID = 1;
    

5. Deleting Data

To remove an author and their books, you can use the DELETE statement:

      DELETE FROM Books
      WHERE AuthorID = 1;

      DELETE FROM Authors
      WHERE AuthorID = 1;
    

Conclusion

Relational databases provide an efficient way to manage structured data. Using SQL, you can perform various operations like creating tables, inserting records, querying relationships, and maintaining data integrity. Understanding relational databases is essential for anyone working with data.