🛢️ Relational Databases

Most applications store their data in a database. This chapter explains why we use a DBMS, how the relational model organises data into tables, the terms used to describe it (relation, tuple, attribute, domain, degree, cardinality and keys), and gives a first look at MySQL and SQL.

9.1 🏁 Introduction

A database is an organised collection of related data, and a DBMS (Database Management System) is the software that lets you create, store, retrieve, update, and manage that data efficiently.

Think of it this way: A pile of Excel sheets scattered across a laptop is "data". A well-organised, searchable, secure, multi-user system built around that data is a "database" — and the software running it is the DBMS!

9.2 🎯 Purpose of DBMS

Before DBMS existed, data was stored in flat files (plain text files, spreadsheets) managed directly by application programs. This caused serious problems — and DBMS was built specifically to solve them!

Problems with File-Based Systems:

graph TD
    FILE["📁 File-Based System\n(No DBMS)"]
    P1["🔁 Data Redundancy\nSame data stored multiple times"]
    P2["⚠️ Data Inconsistency\nCopies go out of sync"]
    P3["🔒 Poor Security\nNo proper access control"]
    P4["🚫 No Concurrent Access\nOnly one user/program at a time"]
    P5["💥 Data Isolation\nData scattered in different formats"]
    P6["🔗 No Integrity Constraints\nInvalid data can creep in"]

    FILE --> P1
    FILE --> P2
    FILE --> P3
    FILE --> P4
    FILE --> P5
    FILE --> P6

    style FILE fill:#F44336,color:#fff

How DBMS Solves These Problems:

9.3 🔗 Relational Database Model

The Relational Model, proposed by Dr. E. F. Codd in 1970, organises data into two-dimensional tables (called relations) made up of rows and columns. It is the most widely used database model in the world today.

graph LR
    RM["🗄️ RELATIONAL MODEL"]
    T1["📋 Table = Relation"]
    T2["➡️ Row = Tuple"]
    T3["⬇️ Column = Attribute"]
    T4["🔑 Each row uniquely identified by a key"]

    RM --> T1
    RM --> T2
    RM --> T3
    RM --> T4

    style RM fill:#2196F3,color:#fff

Example — A Student Relation:

Properties of a Relation (Important Rules!):

9.4 📐 The Relational Model Terminology

Beyond Relation, Tuple, and Attribute, the relational model has its own precise vocabulary that you need to know precisely.

Core Terminology — Quick Reference:

graph TD
    SCHEMA["📐 Relation Schema\nStudent(RollNo, Name, Class, Marks)"]
    DEG["Degree = 4\n(4 attributes)"]
    INST["📊 Relation Instance\n(Actual rows of data)"]
    CARD["Cardinality = 3\n(3 tuples currently)"]

    SCHEMA --> DEG
    SCHEMA -.->|"populated with"| INST
    INST --> CARD

    style SCHEMA fill:#9C27B0,color:#fff
    style INST fill:#2196F3,color:#fff

9.4.1 Views 👁️

A View is a virtual table based on the result of an SQL query. It does not store data itself — it dynamically pulls data from one or more underlying base tables whenever it's accessed.

graph LR
    T1["📋 Table: Student"]
    T2["📋 Table: Marks"]
    QUERY["🔍 SELECT Query\n(joins & filters data)"]
    VIEW["👁️ VIEW\n(Virtual Table)\nNo data stored — computed live!"]

    T1 --> QUERY
    T2 --> QUERY
    QUERY --> VIEW

    style VIEW fill:#9C27B0,color:#fff

Why Use Views?

Key Facts about Views:

9.4.2 Structure of Relational Databases 🏗️

A relational database isn't just one table — it's a collection of related tables, linked together through keys, forming a complete structured system.

graph TD
    DB["🗄️ Relational Database"]
    T1["📋 Student Table\n(RollNo, Name, ClassID)"]
    T2["📋 Class Table\n(ClassID, ClassName, Teacher)"]
    T3["📋 Marks Table\n(RollNo, Subject, Score)"]

    DB --> T1
    DB --> T2
    DB --> T3
    T1 -.->|"ClassID (Foreign Key)"| T2
    T3 -.->|"RollNo (Foreign Key)"| T1

    style DB fill:#F44336,color:#fff
    style T1 fill:#4CAF50,color:#fff
    style T2 fill:#2196F3,color:#fff
    style T3 fill:#FF9800,color:#fff

Building Blocks of the Structure:

9.5 📜 Brief History of MySQL

MySQL is one of the most popular open-source Relational Database Management Systems (RDBMS) in the world — powering everything from small school projects to giants like Facebook and YouTube (in their early years)!

graph LR
    Y1["1995\n👨‍💻 Created by\nMichael Widenius,\nDavid Axmark &\nAllan Larsson"]
    Y2["2008\n🏢 Acquired by\nSun Microsystems"]
    Y3["2010\n🏢 Acquired by\nOracle Corporation"]
    Y4["Today\n🌍 World's most\npopular open-source\nRDBMS"]

    Y1 --> Y2 --> Y3 --> Y4

    style Y1 fill:#4CAF50,color:#fff
    style Y2 fill:#FF9800,color:#fff
    style Y3 fill:#F44336,color:#fff
    style Y4 fill:#2196F3,color:#fff

Key Historical Facts:

9.6 🖥️ MySQL Database System

MySQL works on a Client-Server architecture — there's a central MySQL server that stores and manages all the data, and client programs (or command-line tools) connect to it to send queries and receive results.

graph LR
    C1["💻 MySQL Client\n(Workbench, CLI, App)"]
    C2["💻 MySQL Client\n(Another App)"]
    SERVER["🖥️ MySQL SERVER\nStores databases\nProcesses queries\nManages users"]
    DB["🗄️ Databases\n(Tables, Views, Data)"]

    C1 -->|"SQL Query"| SERVER
    C2 -->|"SQL Query"| SERVER
    SERVER --> DB
    SERVER -->|"Result Set"| C1
    SERVER -->|"Result Set"| C2

    style SERVER fill:#F44336,color:#fff
    style DB fill:#FF9800,color:#fff

Components of MySQL Database System:

Key Features of MySQL:

9.7 🚀 Starting MySQL

Before you can run any SQL command, you need to start the MySQL server and then connect a client to it. The exact steps differ slightly by operating system, but the core idea stays the same.

Starting MySQL — Typical Workflow:

graph TD
    A["1️⃣ Install MySQL Server\n(via installer or XAMPP/WAMP)"]
    B["2️⃣ Start the MySQL Service\n(mysqld running in background)"]
    C["3️⃣ Open MySQL Client\n(Command Line / Workbench)"]
    D["4️⃣ Login with Credentials\nmysql -u username -p"]
    E["5️⃣ Enter Password"]
    F["✅ mysql> prompt appears\nReady to run SQL commands!"]

    A --> B --> C --> D --> E --> F

    style F fill:#4CAF50,color:#fff

Common Ways to Start MySQL:

Basic Login Command:

mysql -u root -p

9.8 💻 MySQL and SQL

MySQL is the database management system (the engine); SQL (Structured Query Language) is the language used to communicate with it — create tables, insert data, retrieve records, and much more.

Real-world Analogy: MySQL is like the car engine; SQL is the steering wheel and pedals you use to actually control it. You need both — the engine alone does nothing without a way to direct it!

graph LR
    USER["👤 You"]
    SQL["💻 SQL Statement\nSELECT * FROM Student;"]
    MYSQL["🖥️ MySQL Server\n(Interprets & Executes)"]
    RESULT["📊 Result Set\n(Table of matching rows)"]

    USER -->|"types"| SQL
    SQL -->|"sent to"| MYSQL
    MYSQL -->|"returns"| RESULT
    RESULT --> USER

    style MYSQL fill:#F44336,color:#fff
    style SQL fill:#2196F3,color:#fff

9.8.1 Processing Capabilities of SQL ⚙️

SQL is a remarkably versatile language — it can do far more than just fetch data. Its capabilities are generally grouped into four major categories.

SQL's Processing Capabilities — Detailed:

9.8.2 Data Definition Language (DDL) 🏗️

DDL (Data Definition Language) consists of SQL commands used to define, modify, and remove the structure of database objects like tables, views, and indexes — NOT the data inside them.

graph TD
    DDL["🏗️ DDL Commands"]
    C1["CREATE\nMake a new object\n(table, database, view)"]
    C2["ALTER\nModify an existing\nobject's structure"]
    C3["DROP\nPermanently delete\nan object"]
    C4["TRUNCATE\nRemove all rows but\nkeep table structure"]

    DDL --> C1
    DDL --> C2
    DDL --> C3
    DDL --> C4

    style DDL fill:#9C27B0,color:#fff

The Four DDL Commands:

DROP vs TRUNCATE vs DELETE — Critical Comparison:

9.8.3 Classification of SQL Statements 📂

SQL commands are formally classified into five categories, based on what kind of task they perform.

graph TD
    SQL["💻 SQL STATEMENTS"]
    DDL["🏗️ DDL\nData Definition\nCREATE, ALTER, DROP"]
    DML["✍️ DML\nData Manipulation\nINSERT, UPDATE, DELETE, SELECT"]
    DCL["🔐 DCL\nData Control\nGRANT, REVOKE"]
    TCL["🔄 TCL\nTransaction Control\nCOMMIT, ROLLBACK, SAVEPOINT"]
    DQL["🔍 DQL\nData Query\nSELECT"]

    SQL --> DDL
    SQL --> DML
    SQL --> DCL
    SQL --> TCL
    SQL --> DQL

    style DDL fill:#9C27B0,color:#fff
    style DML fill:#2196F3,color:#fff
    style DCL fill:#F44336,color:#fff
    style TCL fill:#FF9800,color:#fff
    style DQL fill:#4CAF50,color:#fff

SQL Statement Categories — Complete Reference:

⚠️ Common Errors and Misconceptions

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) Who proposed the Relational Model, and in which year? → Dr. E. F. Codd, in 1970

b) What is the full form of DDL? → Data Definition Language

c) Name the company that currently owns MySQL. → Oracle Corporation

d) What does "Cardinality" of a relation mean? → The number of tuples (rows) in the relation

e) Name any one DCL command. → GRANT or REVOKE

Q2: Short Answer [2 marks]

Q: What is a View? Give one advantage of using a View.

A View is a virtual table based on the result of an SQL SELECT query. It does not store data physically; it is computed dynamically each time it is accessed. Advantage: Views improve security by allowing users to see only specific columns/rows of a table, hiding sensitive data from underlying base tables.

Q3: Short Answer [2 marks]

Q: Differentiate between Degree and Cardinality of a relation.

Q4: Short Answer [3 marks]

Q: Explain the purpose of a DBMS over traditional file-based systems.

A DBMS is software designed to overcome the limitations of file-based data storage. It provides:

1. Reduced Redundancy — centralised data, stored once
2. Consistency — single source of truth for all applications
3. Security — user authentication and permission-based access
4. Concurrent Access — multiple users can safely use the data at once
5. Integrity Constraints — rules like PRIMARY KEY and NOT NULL prevent invalid data

Q5: Output/Analysis Question [2 marks]

Q: Classify the following SQL statements into DDL, DML, DCL, or TCL:

a) ALTER TABLE Student ADD COLUMN Age INT; → DDL

b) UPDATE Student SET Marks = 95 WHERE RollNo = 1; → DML

c) REVOKE SELECT ON Student FROM user2; → DCL

d) ROLLBACK; → TCL

✏️ Practice Problems

1. Define Primary Key, Candidate Key, and Alternate Key with one example each.
2. Explain with a diagram how MySQL's Client-Server architecture works.
3. Write the steps to start MySQL from the command line and log in as the root user.
4. Compare DROP, TRUNCATE, and DELETE on three different parameters.
5. What is the difference between Relation Schema and Relation Instance? Give an example.
6. Why is a View called a "virtual table"? Can a View always be updated? Explain.
7. List and briefly explain the five categories of SQL statements with one command example each.
8. Explain why file-based systems suffer from data redundancy and inconsistency, with an example.