Relational Data Model in SQL

The Relational Model is a theoretical framework that describes how data can be stored, organized, and manipulated in a database. In this model, data is structured into tables (also known as relations), which consist of rows and columns. Each row is a record, and each column is a field. A table is defined by its schema, which describes the columns, their types, and relationships to other tables. These tables are linked to each other via relationships, usually through primary and foreign keys.

For example, imagine your company maintains a database table with customer information, which contains company data at the account level. There may also be a different table, which describes all the individual transactions that align to that account. Together, these tables can provide information about the different industries that purchase a specific software product.

The columns (or fields) for the customer table might be Customer ID, Company Name, Company Address, Industry etc.; the columns for a transaction table might be Transaction Date, Customer ID, Transaction Amount, Payment Method, etc. The tables can be joined together with the common Customer ID field. You can, therefore, query the table to produce valuable reports, such as a sales reports by industry or company, which can inform messaging to prospective clients.

Key Concepts

  • Table (Relation): A set of rows and columns that holds data.

  • Row (Tuple): A single entry or record in a table.

  • Column (Attribute): A specific field of data in a table.

  • Primary Key: A column or a set of columns that uniquely identify a record within a table.

  • Foreign Key: A column or a set of columns in a table that is used to establish a link between the data in two tables.

SQL Operations on Relational Model

  • Selection (SELECT): Retrieve data from one or more tables.

  • Insertion (INSERT): Add new records to a table.

  • Updation (UPDATE): Modify existing records in a table.

  • Deletion (DELETE): Remove existing records from a table.

  • Join: Combines rows from two or more tables based on related columns.

Workshop Exercise: MySQL Example

To practice these concepts, let's work with a simple example: a database for a library system with Books and Authors tables.

  1. Setting up MySQL

    Make sure MySQL is installed and running on your computer. Open your MySQL console or client.

  2. Create Database

     CREATE DATABASE LibraryDB;
     USE LibraryDB;
    
  3. Create Tables

     CREATE TABLE Authors (
         AuthorID INT AUTO_INCREMENT,
         Name VARCHAR(50),
         PRIMARY KEY (AuthorID)
     );
    
     CREATE TABLE Books (
         BookID INT AUTO_INCREMENT,
         Title VARCHAR(50),
         AuthorID INT,
         PRIMARY KEY (BookID),
         FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
     );
    
  4. Insert Data

     INSERT INTO Authors (Name) VALUES ('J.K. Rowling'), ('George Orwell'), ('J.R.R. Tolkien');
    
     INSERT INTO Books (Title, AuthorID) VALUES ('Harry Potter', 1), ('1984', 2), ('The Hobbit', 3);
    

  5. Query Data

    • List all books and their authors

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

    • Find books by a specific author

        SELECT Books.Title
        FROM Books
        WHERE AuthorID = (SELECT AuthorID FROM Authors WHERE Name = 'J.K. Rowling');
      
  6. Update Data

     UPDATE Books
     SET Title = 'Harry Potter and the Philosopher''s Stone'
     WHERE Title = 'Harry Potter';
    

  7. Delete Data

     DELETE FROM Books WHERE Title = '1984';
    

References

  1. What is a relational database?