You know **what** a database is ā now let's go deeper! This chapter answers the BIG questions: **What is a DBMS for? How does the relational model organise data? How does MySQL actually work under the hood?** Master this chapter and you'll understand the backbone of every app you use! š
> [!TIP]
> **How to use these notes:** This chapter covers **DBMS fundamentals**, the **Relational Model**, and **MySQL basics**. Board Exam Tips appear throughout ā don't skip them! Focus especially on **Sections 9.3, 9.4, and 9.8** ā examiner favourites every year!
---
## 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!
::: grid
::: card šļø | Database | Organised collection of related data | Student records, library catalogue, bank accounts
::: card āļø | DBMS | Software to create, manage and query databases | MySQL, Oracle, PostgreSQL
::: card š | Relational Model | Organises data into tables (relations) linked by common values | Most widely used database model today
::: card š» | SQL | The language used to talk to relational databases | Structured Query Language
:::
---
## 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:**
```mermaid
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:**
| Problem in File System | How DBMS Fixes It |
| :--- | :--- |
| **Data Redundancy** | Centralised storage ā data stored once, referenced everywhere |
| **Data Inconsistency** | Single source of truth ā update once, reflects everywhere |
| **Poor Security** | User-level permissions, authentication, role-based access |
| **No Concurrent Access** | Built-in concurrency control ā multiple users safely at once |
| **Data Isolation** | Unified schema ā all data follows one consistent structure |
| **No Integrity Constraints** | PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK constraints enforce valid data |
::: grid
::: card ā | Data Independence | Applications don't need to know how data is physically stored | Change storage without rewriting programs
::: card š | Security & Access Control | Different users get different permission levels | Admin can edit; student can only view marks
::: card š¤ | Concurrent Access | Many users can use the database simultaneously, safely | Hundreds of students checking results at once
::: card š”ļø | Backup & Recovery | Built-in tools to recover data after crashes | Automatic backups, transaction logs
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the purpose of DBMS? / Why do we need a DBMS instead of file systems?" ā **2-3 mark** question asked very often!
> Core answer: A DBMS centralises data to **eliminate redundancy**, **maintain consistency**, **enforce security and integrity constraints**, and **support concurrent multi-user access** ā none of which is reliably possible with plain file-based storage.
---
## 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.
```mermaid
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:**
| RollNo | Name | Class | Marks |
| :--- | :--- | :--- | :--- |
| 1 | Priya Sharma | 12A | 91 |
| 2 | Arjun Kumar | 12B | 88 |
| 3 | Sita Roy | 12A | 95 |
::: grid
::: card š | Relation (Table) | The entire table ā a set of related tuples | The "Student" table itself
::: card ā”ļø | Tuple (Row) | One single record/entry in the table | The row for "Priya Sharma"
::: card ā¬ļø | Attribute (Column) | A single property/field of the relation | "Marks" column
::: card š¢ | Domain | The set of allowed/valid values for an attribute | Marks domain = 0 to 100
:::
> [!NOTE]
> **Why "Relational"? š§ **
> The word "Relational" does NOT mean tables are related to each other (though they often are!). It comes from the mathematical term **"relation"**, which is simply the formal name for a table ā a set of tuples (rows). Tables being *linked* via keys is a separate, additional feature of the model!
**Properties of a Relation (Important Rules!):**
| Property | Rule |
| :--- | :--- |
| **Atomic Values** | Each cell holds a single, indivisible value (no lists inside a cell) |
| **Unique Column Names** | No two columns in a relation can have the same name |
| **Row Order Doesn't Matter** | Rows can be in any order; the data is still the same relation |
| **Column Order Doesn't Matter** | Columns can be rearranged without changing meaning |
| **No Duplicate Rows** | Every tuple in a relation must be unique |
> [!IMPORTANT]
> **Board Exam Tip**
> "Define Relation, Tuple, and Attribute with examples." ā **3-mark** question, asked nearly every year!
> Answer: A **Relation** is a table consisting of rows and columns. A **Tuple** is a single row of the relation, representing one record. An **Attribute** is a single column of the relation, representing one property/field of the data.
---
## 9.4 š The Relational Model Terminology
Beyond Relation, Tuple, and Attribute, the relational model has its own precise vocabulary that examiners love to test directly!
**Core Terminology ā Quick Reference:**
| Term | Meaning | Example |
| :--- | :--- | :--- |
| **Degree** | Number of attributes (columns) in a relation | Student table with 4 columns ā Degree = 4 |
| **Cardinality** | Number of tuples (rows) in a relation | Student table with 3 rows ā Cardinality = 3 |
| **Relation Schema** | The structure/blueprint of a relation (name + attributes) | `Student(RollNo, Name, Class, Marks)` |
| **Relation Instance** | The actual data present in a relation at a given moment | The current set of rows in the table |
| **Candidate Key** | An attribute (or set) that can uniquely identify each tuple | RollNo, Admission Number |
| **Primary Key** | The candidate key chosen to uniquely identify tuples | RollNo (chosen as the Primary Key) |
| **Alternate Key** | Candidate keys NOT chosen as the Primary Key | Admission Number (if RollNo is PK) |
| **Foreign Key** | An attribute in one table that refers to the Primary Key of another table | `ClassID` in Student table referring to Class table |
```mermaid
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
```
> [!TIP]
> **Memory Trick: Degree vs Cardinality! šµ**
> Think **D**egree = **D**own the columns (how many fields).
> Think **C**ardinality = **C**ount the rows (how many records).
> If it helps, remember: **"Cardinality Counts rows, Degree Defines columns"**
---
### 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.
```mermaid
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?**
::: grid
::: card š | Security | Hide sensitive columns; show only what's needed | Hide salary, show only name & department
::: card šÆ | Simplicity | Hide complex joins behind a simple table-like name | Users query the view, not the messy joins
::: card š§© | Logical Data Independence | Underlying table structure can change without breaking the view's users | Insulates apps from schema changes
::: card ā»ļø | Reusability | Write a complex query once, reuse it like a table forever | No need to repeat long JOIN queries
:::
**Key Facts about Views:**
| Property | Detail |
| :--- | :--- |
| **Stores data?** | ā No ā always computed fresh from base tables |
| **Created using** | `CREATE VIEW view_name AS SELECT ...` |
| **Can be queried like** | A normal table ā `SELECT * FROM view_name` |
| **Updatable?** | Only simple views (based on a single table, no aggregate functions) |
| **Dropped using** | `DROP VIEW view_name` |
> [!NOTE]
> **View = A Window, Not a Photocopy! šŖ**
> A View is like looking through a **window** at the base table ā you see a live, current picture of the data. It's NOT like taking a **photocopy** (which would freeze the data at that moment). Change the base table, and the view's output changes instantly too!
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a View in SQL? Why is it used?" ā **2-mark** question.
> Answer: A **View** is a **virtual table** derived from one or more base tables using a SELECT query. It does not store data physically but is computed dynamically when accessed. Views are used for **security** (hiding columns), **simplicity** (hiding complex joins), and **data independence**.
---
### 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.
```mermaid
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:**
| Component | Role |
| :--- | :--- |
| **Tables (Relations)** | Store the actual organised data |
| **Rows (Tuples)** | Individual records within each table |
| **Columns (Attributes)** | Properties describing each record |
| **Primary Keys** | Uniquely identify rows within their own table |
| **Foreign Keys** | Link rows in one table to rows in another table |
| **Constraints** | Rules (NOT NULL, UNIQUE, CHECK) ensuring data validity |
| **Schema** | The overall blueprint describing all tables and their relationships |
::: grid
::: card š | Primary Key Rule | Must be unique and NOT NULL for every row | RollNo can never repeat or be blank
::: card š | Foreign Key Rule | Must match an existing Primary Key value in the parent table (or be NULL) | ClassID in Student must exist in Class table
::: card š§± | Referential Integrity | The rule that Foreign Keys must always point to valid existing data | Prevents "orphan" records
:::
> [!WARNING]
> **Common Mistake**
> Students often think a database is just "one big table with everything in it." In reality, **good relational database design splits data into multiple smaller, linked tables** (this process is called **Normalisation** ā covered in detail later) to avoid redundancy and keep data consistent.
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the structure of a relational database?" ā **3-mark** question.
> Answer: A relational database consists of **multiple tables (relations)**, each made up of **rows (tuples)** and **columns (attributes)**. Tables are linked using **Primary Keys** and **Foreign Keys**, and **constraints** ensure data validity. This structure is collectively described by the database's **schema**.
---
## 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)!
```mermaid
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:**
| Fact | Detail |
| :--- | :--- |
| **Released** | 1995 |
| **Creators** | Michael "Monty" Widenius, David Axmark, Allan Larsson |
| **Name Origin** | "My" ā daughter of co-founder Monty Widenius; "SQL" ā Structured Query Language |
| **Current Owner** | Oracle Corporation (since 2010) |
| **License Type** | Open-source (GPL) with paid commercial editions also available |
| **Written In** | C and C++ |
> [!NOTE]
> **Fun Fact! šµ**
> MySQL is named after co-founder Monty Widenius's daughter, **My**! Combine that with **SQL** (the language it uses), and you get **MySQL**. A cute, personal origin story for one of the world's biggest pieces of software infrastructure!
---
## 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.
```mermaid
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:**
::: grid
::: card š„ļø | MySQL Server (mysqld) | The core engine that stores data and processes all queries | Runs continuously in the background
::: card š» | MySQL Client | Software/tool used to connect to and interact with the server | MySQL Workbench, CLI, phpMyAdmin
::: card šļø | Storage Engine | The underlying mechanism that actually reads/writes data to disk | InnoDB (most common), MyISAM
::: card š | User Management | Controls who can connect and what they're allowed to do | GRANT and REVOKE privileges
:::
**Key Features of MySQL:**
| Feature | Benefit |
| :--- | :--- |
| **Open Source** | Free to use, modify, and distribute |
| **Cross-Platform** | Runs on Windows, Linux, macOS |
| **Multi-User Support** | Many clients can connect and query simultaneously |
| **Scalability** | Handles everything from tiny apps to massive websites |
| **High Performance** | Optimised query engine, indexing support |
| **Security** | User authentication, privilege-based access control |
| **ACID Compliance (InnoDB)** | Ensures reliable transactions even during crashes |
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the Client-Server architecture of MySQL?" ā **2-mark** question.
> Answer: MySQL follows a **Client-Server model** where the **MySQL Server** stores and manages all data and processes queries, while **Client programs** (command line, applications, tools like MySQL Workbench) connect to the server, send SQL queries, and receive results back.
---
## 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:**
```mermaid
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:**
| Platform / Tool | Method |
| :--- | :--- |
| **Windows (Command Line)** | Open Command Prompt ā `mysql -u root -p` |
| **XAMPP / WAMP** | Open Control Panel ā Click "Start" next to MySQL |
| **Linux** | `sudo service mysql start` then `mysql -u root -p` |
| **MySQL Workbench** | Open the app ā Double-click the saved connection |
**Basic Login Command:**
```
mysql -u root -p
```
| Part | Meaning |
| :--- | :--- |
| `mysql` | The MySQL client program being launched |
| `-u root` | Login as the user named `root` (default admin account) |
| `-p` | Prompt for a password before connecting |
::: grid
::: card ā | Successful Login | Server running + correct credentials ā `mysql>` prompt appears | Ready to type SQL commands
::: card ā | Connection Refused | Server isn't running, or wrong host/port | Start the MySQL service first
::: card ā | Access Denied | Wrong username or password | Double-check credentials and try again
:::
> [!NOTE]
> **The `mysql>` Prompt is Your Command Centre! š§ **
> Once you see `mysql>`, you are inside the MySQL client, connected to the server, and ready to type SQL statements directly. Every statement you type here must end with a semicolon `;` to execute!
---
## 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!
```mermaid
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.
::: grid
::: card šļø | Data Definition | Define and modify the structure of database objects | CREATE, ALTER, DROP
::: card āļø | Data Manipulation | Insert, update, delete, and retrieve actual data | INSERT, UPDATE, DELETE, SELECT
::: card š | Data Control | Manage user permissions and access rights | GRANT, REVOKE
::: card š | Transaction Control | Manage groups of operations as a single safe unit | COMMIT, ROLLBACK, SAVEPOINT
:::
**SQL's Processing Capabilities ā Detailed:**
| Capability | What It Does | Example |
| :--- | :--- | :--- |
| **Define Data** | Create/modify database structures | `CREATE TABLE`, `ALTER TABLE` |
| **Retrieve Data** | Query and fetch data matching conditions | `SELECT ... WHERE ...` |
| **Manipulate Data** | Insert, update, or delete records | `INSERT INTO`, `UPDATE`, `DELETE` |
| **Control Access** | Grant or revoke permissions to/from users | `GRANT SELECT ON Student TO user1` |
| **Ensure Integrity** | Enforce rules so invalid data can't be entered | `PRIMARY KEY`, `NOT NULL`, `CHECK` |
| **Manage Transactions** | Group multiple statements as one all-or-nothing unit | `COMMIT`, `ROLLBACK` |
> [!IMPORTANT]
> **Board Exam Tip**
> "What are the processing capabilities of SQL?" ā **3-mark** question.
> Answer: SQL can **define** database structures (DDL), **manipulate** data within them (DML), **control** user access (DCL), and **manage transactions** (TCL) ā making it a complete language for working with relational databases, not just a query tool.
---
### 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.
```mermaid
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:**
| Command | Purpose | Example |
| :--- | :--- | :--- |
| **CREATE** | Build a new table, database, view, or index | `CREATE TABLE Student (RollNo INT, Name VARCHAR(30));` |
| **ALTER** | Change an existing table's structure | `ALTER TABLE Student ADD Marks INT;` |
| **DROP** | Permanently delete a table/database/view entirely | `DROP TABLE Student;` |
| **TRUNCATE** | Delete all rows instantly, but keep the table structure | `TRUNCATE TABLE Student;` |
::: grid
::: card ā ļø | DROP vs TRUNCATE vs DELETE | All three remove data, but very differently! | See comparison table below
::: card š | DDL is Auto-Committed | DDL changes are saved permanently the moment they run ā ROLLBACK won't undo them | Be careful before running DROP!
:::
**DROP vs TRUNCATE vs DELETE ā Critical Comparison:**
| Feature | DROP | TRUNCATE | DELETE |
| :--- | :--- | :--- | :--- |
| **Category** | DDL | DDL | DML |
| **Removes** | Entire table structure + data | All rows, keeps structure | Specific rows (or all, with no WHERE) |
| **Can use WHERE?** | ā No | ā No | ā Yes |
| **Can Rollback?** | ā No (auto-committed) | ā No (auto-committed) | ā Yes (if not committed) |
| **Speed** | Fast | Very Fast | Slower (row-by-row logging) |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between DROP, TRUNCATE, and DELETE." ā **2-3 mark** question asked very frequently!
> Core answer: **DROP** removes the entire table (structure + data) permanently. **TRUNCATE** removes all rows but keeps the empty table structure intact. **DELETE** removes rows selectively (using WHERE) and can be rolled back, unlike DROP/TRUNCATE which are auto-committed.
---
### 9.8.3 Classification of SQL Statements š
SQL commands are formally classified into **five categories**, based on what kind of task they perform.
```mermaid
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:**
| Category | Full Form | Purpose | Commands |
| :--- | :--- | :--- | :--- |
| **DDL** | Data Definition Language | Define/modify database structure | CREATE, ALTER, DROP, TRUNCATE |
| **DML** | Data Manipulation Language | Insert, update, delete data | INSERT, UPDATE, DELETE |
| **DQL** | Data Query Language | Retrieve/fetch data | SELECT |
| **DCL** | Data Control Language | Manage user access rights | GRANT, REVOKE |
| **TCL** | Transaction Control Language | Manage transactions | COMMIT, ROLLBACK, SAVEPOINT |
> [!NOTE]
> **Note on SELECT! š§ **
> Many textbooks (and CBSE itself, in older syllabi) club **SELECT** under DML rather than giving it a separate DQL category. Both classifications are accepted in CBSE answers ā just be consistent and mention SELECT's purpose (retrieving data) clearly!
> [!IMPORTANT]
> **Board Exam Tip**
> "Classify the following SQL statements: CREATE, INSERT, GRANT, COMMIT, SELECT, DROP." ā **Common 1-mark-per-item** question.
> Answer: **CREATE, DROP** ā DDL | **INSERT** ā DML | **GRANT** ā DCL | **COMMIT** ā TCL | **SELECT** ā DQL (or DML)
---
## ā ļø Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ā MySQL and SQL are the same thing | ā MySQL is a DBMS *software*; SQL is the *language* used to interact with it (and other RDBMS too) |
| ā A View stores its own copy of data | ā A View is virtual ā it computes results live from base tables every time it's queried |
| ā DELETE and TRUNCATE do the same thing | ā DELETE can be selective (WHERE) and rolled back; TRUNCATE removes everything and is auto-committed |
| ā Degree refers to number of rows | ā Degree = number of **columns**; Cardinality = number of **rows** |
| ā Database = one giant table | ā A relational database is multiple linked tables, connected via Primary/Foreign Keys |
| ā Candidate Key and Primary Key are different concepts entirely | ā Primary Key IS a Candidate Key ā just the one chosen to be the main identifier |
---
## š Quick Revision ā Exam Ready!
**Core Concepts ā One-Line Summary:**
- **DBMS** ā Software to manage databases; solves redundancy, inconsistency & security issues of file systems
- **Relational Model** ā Organises data into tables (relations) of rows (tuples) and columns (attributes)
- **Degree** ā Number of columns | **Cardinality** ā Number of rows
- **Primary Key** ā Uniquely identifies each row | **Foreign Key** ā Links to another table's Primary Key
- **View** ā Virtual table computed from a SELECT query; stores no data of its own
- **MySQL** ā Popular open-source RDBMS, created 1995, now owned by Oracle
- **SQL** ā The language used to define, manipulate, query, and control data in MySQL (and other RDBMS)
**SQL Classification ā Quick Table:**
| Category | Commands |
| :--- | :--- |
| DDL | CREATE, ALTER, DROP, TRUNCATE |
| DML | INSERT, UPDATE, DELETE |
| DQL | SELECT |
| DCL | GRANT, REVOKE |
| TCL | COMMIT, ROLLBACK, SAVEPOINT |
---
## šÆ 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.**
| Feature | Degree | Cardinality |
| :--- | :--- | :--- |
| **Meaning** | Number of attributes (columns) | Number of tuples (rows) |
| **Changes when** | Columns are added/removed (ALTER) | Rows are inserted/deleted |
| **Example** | Student(RollNo, Name, Marks) ā Degree = 3 | 50 students in the table ā Cardinality = 50 |
---
### 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.
> [!TIP]
> **How to use these notes:** This chapter covers **DBMS fundamentals**, the **Relational Model**, and **MySQL basics**. Board Exam Tips appear throughout ā don't skip them! Focus especially on **Sections 9.3, 9.4, and 9.8** ā examiner favourites every year!
---
## 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!
::: grid
::: card šļø | Database | Organised collection of related data | Student records, library catalogue, bank accounts
::: card āļø | DBMS | Software to create, manage and query databases | MySQL, Oracle, PostgreSQL
::: card š | Relational Model | Organises data into tables (relations) linked by common values | Most widely used database model today
::: card š» | SQL | The language used to talk to relational databases | Structured Query Language
:::
---
## 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:**
```mermaid
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:**
| Problem in File System | How DBMS Fixes It |
| :--- | :--- |
| **Data Redundancy** | Centralised storage ā data stored once, referenced everywhere |
| **Data Inconsistency** | Single source of truth ā update once, reflects everywhere |
| **Poor Security** | User-level permissions, authentication, role-based access |
| **No Concurrent Access** | Built-in concurrency control ā multiple users safely at once |
| **Data Isolation** | Unified schema ā all data follows one consistent structure |
| **No Integrity Constraints** | PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK constraints enforce valid data |
::: grid
::: card ā | Data Independence | Applications don't need to know how data is physically stored | Change storage without rewriting programs
::: card š | Security & Access Control | Different users get different permission levels | Admin can edit; student can only view marks
::: card š¤ | Concurrent Access | Many users can use the database simultaneously, safely | Hundreds of students checking results at once
::: card š”ļø | Backup & Recovery | Built-in tools to recover data after crashes | Automatic backups, transaction logs
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the purpose of DBMS? / Why do we need a DBMS instead of file systems?" ā **2-3 mark** question asked very often!
> Core answer: A DBMS centralises data to **eliminate redundancy**, **maintain consistency**, **enforce security and integrity constraints**, and **support concurrent multi-user access** ā none of which is reliably possible with plain file-based storage.
---
## 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.
```mermaid
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:**
| RollNo | Name | Class | Marks |
| :--- | :--- | :--- | :--- |
| 1 | Priya Sharma | 12A | 91 |
| 2 | Arjun Kumar | 12B | 88 |
| 3 | Sita Roy | 12A | 95 |
::: grid
::: card š | Relation (Table) | The entire table ā a set of related tuples | The "Student" table itself
::: card ā”ļø | Tuple (Row) | One single record/entry in the table | The row for "Priya Sharma"
::: card ā¬ļø | Attribute (Column) | A single property/field of the relation | "Marks" column
::: card š¢ | Domain | The set of allowed/valid values for an attribute | Marks domain = 0 to 100
:::
> [!NOTE]
> **Why "Relational"? š§ **
> The word "Relational" does NOT mean tables are related to each other (though they often are!). It comes from the mathematical term **"relation"**, which is simply the formal name for a table ā a set of tuples (rows). Tables being *linked* via keys is a separate, additional feature of the model!
**Properties of a Relation (Important Rules!):**
| Property | Rule |
| :--- | :--- |
| **Atomic Values** | Each cell holds a single, indivisible value (no lists inside a cell) |
| **Unique Column Names** | No two columns in a relation can have the same name |
| **Row Order Doesn't Matter** | Rows can be in any order; the data is still the same relation |
| **Column Order Doesn't Matter** | Columns can be rearranged without changing meaning |
| **No Duplicate Rows** | Every tuple in a relation must be unique |
> [!IMPORTANT]
> **Board Exam Tip**
> "Define Relation, Tuple, and Attribute with examples." ā **3-mark** question, asked nearly every year!
> Answer: A **Relation** is a table consisting of rows and columns. A **Tuple** is a single row of the relation, representing one record. An **Attribute** is a single column of the relation, representing one property/field of the data.
---
## 9.4 š The Relational Model Terminology
Beyond Relation, Tuple, and Attribute, the relational model has its own precise vocabulary that examiners love to test directly!
**Core Terminology ā Quick Reference:**
| Term | Meaning | Example |
| :--- | :--- | :--- |
| **Degree** | Number of attributes (columns) in a relation | Student table with 4 columns ā Degree = 4 |
| **Cardinality** | Number of tuples (rows) in a relation | Student table with 3 rows ā Cardinality = 3 |
| **Relation Schema** | The structure/blueprint of a relation (name + attributes) | `Student(RollNo, Name, Class, Marks)` |
| **Relation Instance** | The actual data present in a relation at a given moment | The current set of rows in the table |
| **Candidate Key** | An attribute (or set) that can uniquely identify each tuple | RollNo, Admission Number |
| **Primary Key** | The candidate key chosen to uniquely identify tuples | RollNo (chosen as the Primary Key) |
| **Alternate Key** | Candidate keys NOT chosen as the Primary Key | Admission Number (if RollNo is PK) |
| **Foreign Key** | An attribute in one table that refers to the Primary Key of another table | `ClassID` in Student table referring to Class table |
```mermaid
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
```
> [!TIP]
> **Memory Trick: Degree vs Cardinality! šµ**
> Think **D**egree = **D**own the columns (how many fields).
> Think **C**ardinality = **C**ount the rows (how many records).
> If it helps, remember: **"Cardinality Counts rows, Degree Defines columns"**
---
### 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.
```mermaid
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?**
::: grid
::: card š | Security | Hide sensitive columns; show only what's needed | Hide salary, show only name & department
::: card šÆ | Simplicity | Hide complex joins behind a simple table-like name | Users query the view, not the messy joins
::: card š§© | Logical Data Independence | Underlying table structure can change without breaking the view's users | Insulates apps from schema changes
::: card ā»ļø | Reusability | Write a complex query once, reuse it like a table forever | No need to repeat long JOIN queries
:::
**Key Facts about Views:**
| Property | Detail |
| :--- | :--- |
| **Stores data?** | ā No ā always computed fresh from base tables |
| **Created using** | `CREATE VIEW view_name AS SELECT ...` |
| **Can be queried like** | A normal table ā `SELECT * FROM view_name` |
| **Updatable?** | Only simple views (based on a single table, no aggregate functions) |
| **Dropped using** | `DROP VIEW view_name` |
> [!NOTE]
> **View = A Window, Not a Photocopy! šŖ**
> A View is like looking through a **window** at the base table ā you see a live, current picture of the data. It's NOT like taking a **photocopy** (which would freeze the data at that moment). Change the base table, and the view's output changes instantly too!
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a View in SQL? Why is it used?" ā **2-mark** question.
> Answer: A **View** is a **virtual table** derived from one or more base tables using a SELECT query. It does not store data physically but is computed dynamically when accessed. Views are used for **security** (hiding columns), **simplicity** (hiding complex joins), and **data independence**.
---
### 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.
```mermaid
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:**
| Component | Role |
| :--- | :--- |
| **Tables (Relations)** | Store the actual organised data |
| **Rows (Tuples)** | Individual records within each table |
| **Columns (Attributes)** | Properties describing each record |
| **Primary Keys** | Uniquely identify rows within their own table |
| **Foreign Keys** | Link rows in one table to rows in another table |
| **Constraints** | Rules (NOT NULL, UNIQUE, CHECK) ensuring data validity |
| **Schema** | The overall blueprint describing all tables and their relationships |
::: grid
::: card š | Primary Key Rule | Must be unique and NOT NULL for every row | RollNo can never repeat or be blank
::: card š | Foreign Key Rule | Must match an existing Primary Key value in the parent table (or be NULL) | ClassID in Student must exist in Class table
::: card š§± | Referential Integrity | The rule that Foreign Keys must always point to valid existing data | Prevents "orphan" records
:::
> [!WARNING]
> **Common Mistake**
> Students often think a database is just "one big table with everything in it." In reality, **good relational database design splits data into multiple smaller, linked tables** (this process is called **Normalisation** ā covered in detail later) to avoid redundancy and keep data consistent.
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the structure of a relational database?" ā **3-mark** question.
> Answer: A relational database consists of **multiple tables (relations)**, each made up of **rows (tuples)** and **columns (attributes)**. Tables are linked using **Primary Keys** and **Foreign Keys**, and **constraints** ensure data validity. This structure is collectively described by the database's **schema**.
---
## 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)!
```mermaid
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:**
| Fact | Detail |
| :--- | :--- |
| **Released** | 1995 |
| **Creators** | Michael "Monty" Widenius, David Axmark, Allan Larsson |
| **Name Origin** | "My" ā daughter of co-founder Monty Widenius; "SQL" ā Structured Query Language |
| **Current Owner** | Oracle Corporation (since 2010) |
| **License Type** | Open-source (GPL) with paid commercial editions also available |
| **Written In** | C and C++ |
> [!NOTE]
> **Fun Fact! šµ**
> MySQL is named after co-founder Monty Widenius's daughter, **My**! Combine that with **SQL** (the language it uses), and you get **MySQL**. A cute, personal origin story for one of the world's biggest pieces of software infrastructure!
---
## 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.
```mermaid
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:**
::: grid
::: card š„ļø | MySQL Server (mysqld) | The core engine that stores data and processes all queries | Runs continuously in the background
::: card š» | MySQL Client | Software/tool used to connect to and interact with the server | MySQL Workbench, CLI, phpMyAdmin
::: card šļø | Storage Engine | The underlying mechanism that actually reads/writes data to disk | InnoDB (most common), MyISAM
::: card š | User Management | Controls who can connect and what they're allowed to do | GRANT and REVOKE privileges
:::
**Key Features of MySQL:**
| Feature | Benefit |
| :--- | :--- |
| **Open Source** | Free to use, modify, and distribute |
| **Cross-Platform** | Runs on Windows, Linux, macOS |
| **Multi-User Support** | Many clients can connect and query simultaneously |
| **Scalability** | Handles everything from tiny apps to massive websites |
| **High Performance** | Optimised query engine, indexing support |
| **Security** | User authentication, privilege-based access control |
| **ACID Compliance (InnoDB)** | Ensures reliable transactions even during crashes |
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the Client-Server architecture of MySQL?" ā **2-mark** question.
> Answer: MySQL follows a **Client-Server model** where the **MySQL Server** stores and manages all data and processes queries, while **Client programs** (command line, applications, tools like MySQL Workbench) connect to the server, send SQL queries, and receive results back.
---
## 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:**
```mermaid
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:**
| Platform / Tool | Method |
| :--- | :--- |
| **Windows (Command Line)** | Open Command Prompt ā `mysql -u root -p` |
| **XAMPP / WAMP** | Open Control Panel ā Click "Start" next to MySQL |
| **Linux** | `sudo service mysql start` then `mysql -u root -p` |
| **MySQL Workbench** | Open the app ā Double-click the saved connection |
**Basic Login Command:**
```
mysql -u root -p
```
| Part | Meaning |
| :--- | :--- |
| `mysql` | The MySQL client program being launched |
| `-u root` | Login as the user named `root` (default admin account) |
| `-p` | Prompt for a password before connecting |
::: grid
::: card ā | Successful Login | Server running + correct credentials ā `mysql>` prompt appears | Ready to type SQL commands
::: card ā | Connection Refused | Server isn't running, or wrong host/port | Start the MySQL service first
::: card ā | Access Denied | Wrong username or password | Double-check credentials and try again
:::
> [!NOTE]
> **The `mysql>` Prompt is Your Command Centre! š§ **
> Once you see `mysql>`, you are inside the MySQL client, connected to the server, and ready to type SQL statements directly. Every statement you type here must end with a semicolon `;` to execute!
---
## 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!
```mermaid
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.
::: grid
::: card šļø | Data Definition | Define and modify the structure of database objects | CREATE, ALTER, DROP
::: card āļø | Data Manipulation | Insert, update, delete, and retrieve actual data | INSERT, UPDATE, DELETE, SELECT
::: card š | Data Control | Manage user permissions and access rights | GRANT, REVOKE
::: card š | Transaction Control | Manage groups of operations as a single safe unit | COMMIT, ROLLBACK, SAVEPOINT
:::
**SQL's Processing Capabilities ā Detailed:**
| Capability | What It Does | Example |
| :--- | :--- | :--- |
| **Define Data** | Create/modify database structures | `CREATE TABLE`, `ALTER TABLE` |
| **Retrieve Data** | Query and fetch data matching conditions | `SELECT ... WHERE ...` |
| **Manipulate Data** | Insert, update, or delete records | `INSERT INTO`, `UPDATE`, `DELETE` |
| **Control Access** | Grant or revoke permissions to/from users | `GRANT SELECT ON Student TO user1` |
| **Ensure Integrity** | Enforce rules so invalid data can't be entered | `PRIMARY KEY`, `NOT NULL`, `CHECK` |
| **Manage Transactions** | Group multiple statements as one all-or-nothing unit | `COMMIT`, `ROLLBACK` |
> [!IMPORTANT]
> **Board Exam Tip**
> "What are the processing capabilities of SQL?" ā **3-mark** question.
> Answer: SQL can **define** database structures (DDL), **manipulate** data within them (DML), **control** user access (DCL), and **manage transactions** (TCL) ā making it a complete language for working with relational databases, not just a query tool.
---
### 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.
```mermaid
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:**
| Command | Purpose | Example |
| :--- | :--- | :--- |
| **CREATE** | Build a new table, database, view, or index | `CREATE TABLE Student (RollNo INT, Name VARCHAR(30));` |
| **ALTER** | Change an existing table's structure | `ALTER TABLE Student ADD Marks INT;` |
| **DROP** | Permanently delete a table/database/view entirely | `DROP TABLE Student;` |
| **TRUNCATE** | Delete all rows instantly, but keep the table structure | `TRUNCATE TABLE Student;` |
::: grid
::: card ā ļø | DROP vs TRUNCATE vs DELETE | All three remove data, but very differently! | See comparison table below
::: card š | DDL is Auto-Committed | DDL changes are saved permanently the moment they run ā ROLLBACK won't undo them | Be careful before running DROP!
:::
**DROP vs TRUNCATE vs DELETE ā Critical Comparison:**
| Feature | DROP | TRUNCATE | DELETE |
| :--- | :--- | :--- | :--- |
| **Category** | DDL | DDL | DML |
| **Removes** | Entire table structure + data | All rows, keeps structure | Specific rows (or all, with no WHERE) |
| **Can use WHERE?** | ā No | ā No | ā Yes |
| **Can Rollback?** | ā No (auto-committed) | ā No (auto-committed) | ā Yes (if not committed) |
| **Speed** | Fast | Very Fast | Slower (row-by-row logging) |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between DROP, TRUNCATE, and DELETE." ā **2-3 mark** question asked very frequently!
> Core answer: **DROP** removes the entire table (structure + data) permanently. **TRUNCATE** removes all rows but keeps the empty table structure intact. **DELETE** removes rows selectively (using WHERE) and can be rolled back, unlike DROP/TRUNCATE which are auto-committed.
---
### 9.8.3 Classification of SQL Statements š
SQL commands are formally classified into **five categories**, based on what kind of task they perform.
```mermaid
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:**
| Category | Full Form | Purpose | Commands |
| :--- | :--- | :--- | :--- |
| **DDL** | Data Definition Language | Define/modify database structure | CREATE, ALTER, DROP, TRUNCATE |
| **DML** | Data Manipulation Language | Insert, update, delete data | INSERT, UPDATE, DELETE |
| **DQL** | Data Query Language | Retrieve/fetch data | SELECT |
| **DCL** | Data Control Language | Manage user access rights | GRANT, REVOKE |
| **TCL** | Transaction Control Language | Manage transactions | COMMIT, ROLLBACK, SAVEPOINT |
> [!NOTE]
> **Note on SELECT! š§ **
> Many textbooks (and CBSE itself, in older syllabi) club **SELECT** under DML rather than giving it a separate DQL category. Both classifications are accepted in CBSE answers ā just be consistent and mention SELECT's purpose (retrieving data) clearly!
> [!IMPORTANT]
> **Board Exam Tip**
> "Classify the following SQL statements: CREATE, INSERT, GRANT, COMMIT, SELECT, DROP." ā **Common 1-mark-per-item** question.
> Answer: **CREATE, DROP** ā DDL | **INSERT** ā DML | **GRANT** ā DCL | **COMMIT** ā TCL | **SELECT** ā DQL (or DML)
---
## ā ļø Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ā MySQL and SQL are the same thing | ā MySQL is a DBMS *software*; SQL is the *language* used to interact with it (and other RDBMS too) |
| ā A View stores its own copy of data | ā A View is virtual ā it computes results live from base tables every time it's queried |
| ā DELETE and TRUNCATE do the same thing | ā DELETE can be selective (WHERE) and rolled back; TRUNCATE removes everything and is auto-committed |
| ā Degree refers to number of rows | ā Degree = number of **columns**; Cardinality = number of **rows** |
| ā Database = one giant table | ā A relational database is multiple linked tables, connected via Primary/Foreign Keys |
| ā Candidate Key and Primary Key are different concepts entirely | ā Primary Key IS a Candidate Key ā just the one chosen to be the main identifier |
---
## š Quick Revision ā Exam Ready!
**Core Concepts ā One-Line Summary:**
- **DBMS** ā Software to manage databases; solves redundancy, inconsistency & security issues of file systems
- **Relational Model** ā Organises data into tables (relations) of rows (tuples) and columns (attributes)
- **Degree** ā Number of columns | **Cardinality** ā Number of rows
- **Primary Key** ā Uniquely identifies each row | **Foreign Key** ā Links to another table's Primary Key
- **View** ā Virtual table computed from a SELECT query; stores no data of its own
- **MySQL** ā Popular open-source RDBMS, created 1995, now owned by Oracle
- **SQL** ā The language used to define, manipulate, query, and control data in MySQL (and other RDBMS)
**SQL Classification ā Quick Table:**
| Category | Commands |
| :--- | :--- |
| DDL | CREATE, ALTER, DROP, TRUNCATE |
| DML | INSERT, UPDATE, DELETE |
| DQL | SELECT |
| DCL | GRANT, REVOKE |
| TCL | COMMIT, ROLLBACK, SAVEPOINT |
---
## šÆ 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.**
| Feature | Degree | Cardinality |
| :--- | :--- | :--- |
| **Meaning** | Number of attributes (columns) | Number of tuples (rows) |
| **Changes when** | Columns are added/removed (ALTER) | Rows are inserted/deleted |
| **Example** | Student(RollNo, Name, Marks) ā Degree = 3 | 50 students in the table ā Cardinality = 50 |
---
### 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.
You know **what** a database is ā now let's go deeper! This chapter answers the BIG questions: **What is a DBMS for? How does the relational model organise data? How does MySQL actually work under the hood?** Master this chapter and you'll understand the backbone of every app you use! š
> [!TIP]
> **How to use these notes:** This chapter covers **DBMS fundamentals**, the **Relational Model**, and **MySQL basics**. Board Exam Tips appear throughout ā don't skip them! Focus especially on **Sections 9.3, 9.4, and 9.8** ā examiner favourites every year!
---
## 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!
::: grid
::: card šļø | Database | Organised collection of related data | Student records, library catalogue, bank accounts
::: card āļø | DBMS | Software to create, manage and query databases | MySQL, Oracle, PostgreSQL
::: card š | Relational Model | Organises data into tables (relations) linked by common values | Most widely used database model today
::: card š» | SQL | The language used to talk to relational databases | Structured Query Language
:::
---
## 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:**
```mermaid
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:**
| Problem in File System | How DBMS Fixes It |
| :--- | :--- |
| **Data Redundancy** | Centralised storage ā data stored once, referenced everywhere |
| **Data Inconsistency** | Single source of truth ā update once, reflects everywhere |
| **Poor Security** | User-level permissions, authentication, role-based access |
| **No Concurrent Access** | Built-in concurrency control ā multiple users safely at once |
| **Data Isolation** | Unified schema ā all data follows one consistent structure |
| **No Integrity Constraints** | PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK constraints enforce valid data |
::: grid
::: card ā
| Data Independence | Applications don't need to know how data is physically stored | Change storage without rewriting programs
::: card š | Security & Access Control | Different users get different permission levels | Admin can edit; student can only view marks
::: card š¤ | Concurrent Access | Many users can use the database simultaneously, safely | Hundreds of students checking results at once
::: card š”ļø | Backup & Recovery | Built-in tools to recover data after crashes | Automatic backups, transaction logs
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the purpose of DBMS? / Why do we need a DBMS instead of file systems?" ā **2-3 mark** question asked very often!
> Core answer: A DBMS centralises data to **eliminate redundancy**, **maintain consistency**, **enforce security and integrity constraints**, and **support concurrent multi-user access** ā none of which is reliably possible with plain file-based storage.
---
## 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.
```mermaid
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:**
| RollNo | Name | Class | Marks |
| :--- | :--- | :--- | :--- |
| 1 | Priya Sharma | 12A | 91 |
| 2 | Arjun Kumar | 12B | 88 |
| 3 | Sita Roy | 12A | 95 |
::: grid
::: card š | Relation (Table) | The entire table ā a set of related tuples | The "Student" table itself
::: card ā”ļø | Tuple (Row) | One single record/entry in the table | The row for "Priya Sharma"
::: card ā¬ļø | Attribute (Column) | A single property/field of the relation | "Marks" column
::: card š¢ | Domain | The set of allowed/valid values for an attribute | Marks domain = 0 to 100
:::
> [!NOTE]
> **Why "Relational"? š§ **
> The word "Relational" does NOT mean tables are related to each other (though they often are!). It comes from the mathematical term **"relation"**, which is simply the formal name for a table ā a set of tuples (rows). Tables being *linked* via keys is a separate, additional feature of the model!
**Properties of a Relation (Important Rules!):**
| Property | Rule |
| :--- | :--- |
| **Atomic Values** | Each cell holds a single, indivisible value (no lists inside a cell) |
| **Unique Column Names** | No two columns in a relation can have the same name |
| **Row Order Doesn't Matter** | Rows can be in any order; the data is still the same relation |
| **Column Order Doesn't Matter** | Columns can be rearranged without changing meaning |
| **No Duplicate Rows** | Every tuple in a relation must be unique |
> [!IMPORTANT]
> **Board Exam Tip**
> "Define Relation, Tuple, and Attribute with examples." ā **3-mark** question, asked nearly every year!
> Answer: A **Relation** is a table consisting of rows and columns. A **Tuple** is a single row of the relation, representing one record. An **Attribute** is a single column of the relation, representing one property/field of the data.
---
## 9.4 š The Relational Model Terminology
Beyond Relation, Tuple, and Attribute, the relational model has its own precise vocabulary that examiners love to test directly!
**Core Terminology ā Quick Reference:**
| Term | Meaning | Example |
| :--- | :--- | :--- |
| **Degree** | Number of attributes (columns) in a relation | Student table with 4 columns ā Degree = 4 |
| **Cardinality** | Number of tuples (rows) in a relation | Student table with 3 rows ā Cardinality = 3 |
| **Relation Schema** | The structure/blueprint of a relation (name + attributes) | `Student(RollNo, Name, Class, Marks)` |
| **Relation Instance** | The actual data present in a relation at a given moment | The current set of rows in the table |
| **Candidate Key** | An attribute (or set) that can uniquely identify each tuple | RollNo, Admission Number |
| **Primary Key** | The candidate key chosen to uniquely identify tuples | RollNo (chosen as the Primary Key) |
| **Alternate Key** | Candidate keys NOT chosen as the Primary Key | Admission Number (if RollNo is PK) |
| **Foreign Key** | An attribute in one table that refers to the Primary Key of another table | `ClassID` in Student table referring to Class table |
```mermaid
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
```
> [!TIP]
> **Memory Trick: Degree vs Cardinality! šµ**
> Think **D**egree = **D**own the columns (how many fields).
> Think **C**ardinality = **C**ount the rows (how many records).
> If it helps, remember: **"Cardinality Counts rows, Degree Defines columns"**
---
### 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.
```mermaid
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?**
::: grid
::: card š | Security | Hide sensitive columns; show only what's needed | Hide salary, show only name & department
::: card šÆ | Simplicity | Hide complex joins behind a simple table-like name | Users query the view, not the messy joins
::: card š§© | Logical Data Independence | Underlying table structure can change without breaking the view's users | Insulates apps from schema changes
::: card ā»ļø | Reusability | Write a complex query once, reuse it like a table forever | No need to repeat long JOIN queries
:::
**Key Facts about Views:**
| Property | Detail |
| :--- | :--- |
| **Stores data?** | ā No ā always computed fresh from base tables |
| **Created using** | `CREATE VIEW view_name AS SELECT ...` |
| **Can be queried like** | A normal table ā `SELECT * FROM view_name` |
| **Updatable?** | Only simple views (based on a single table, no aggregate functions) |
| **Dropped using** | `DROP VIEW view_name` |
> [!NOTE]
> **View = A Window, Not a Photocopy! šŖ**
> A View is like looking through a **window** at the base table ā you see a live, current picture of the data. It's NOT like taking a **photocopy** (which would freeze the data at that moment). Change the base table, and the view's output changes instantly too!
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a View in SQL? Why is it used?" ā **2-mark** question.
> Answer: A **View** is a **virtual table** derived from one or more base tables using a SELECT query. It does not store data physically but is computed dynamically when accessed. Views are used for **security** (hiding columns), **simplicity** (hiding complex joins), and **data independence**.
---
### 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.
```mermaid
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:**
| Component | Role |
| :--- | :--- |
| **Tables (Relations)** | Store the actual organised data |
| **Rows (Tuples)** | Individual records within each table |
| **Columns (Attributes)** | Properties describing each record |
| **Primary Keys** | Uniquely identify rows within their own table |
| **Foreign Keys** | Link rows in one table to rows in another table |
| **Constraints** | Rules (NOT NULL, UNIQUE, CHECK) ensuring data validity |
| **Schema** | The overall blueprint describing all tables and their relationships |
::: grid
::: card š | Primary Key Rule | Must be unique and NOT NULL for every row | RollNo can never repeat or be blank
::: card š | Foreign Key Rule | Must match an existing Primary Key value in the parent table (or be NULL) | ClassID in Student must exist in Class table
::: card š§± | Referential Integrity | The rule that Foreign Keys must always point to valid existing data | Prevents "orphan" records
:::
> [!WARNING]
> **Common Mistake**
> Students often think a database is just "one big table with everything in it." In reality, **good relational database design splits data into multiple smaller, linked tables** (this process is called **Normalisation** ā covered in detail later) to avoid redundancy and keep data consistent.
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the structure of a relational database?" ā **3-mark** question.
> Answer: A relational database consists of **multiple tables (relations)**, each made up of **rows (tuples)** and **columns (attributes)**. Tables are linked using **Primary Keys** and **Foreign Keys**, and **constraints** ensure data validity. This structure is collectively described by the database's **schema**.
---
## 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)!
```mermaid
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:**
| Fact | Detail |
| :--- | :--- |
| **Released** | 1995 |
| **Creators** | Michael "Monty" Widenius, David Axmark, Allan Larsson |
| **Name Origin** | "My" ā daughter of co-founder Monty Widenius; "SQL" ā Structured Query Language |
| **Current Owner** | Oracle Corporation (since 2010) |
| **License Type** | Open-source (GPL) with paid commercial editions also available |
| **Written In** | C and C++ |
> [!NOTE]
> **Fun Fact! šµ**
> MySQL is named after co-founder Monty Widenius's daughter, **My**! Combine that with **SQL** (the language it uses), and you get **MySQL**. A cute, personal origin story for one of the world's biggest pieces of software infrastructure!
---
## 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.
```mermaid
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:**
::: grid
::: card š„ļø | MySQL Server (mysqld) | The core engine that stores data and processes all queries | Runs continuously in the background
::: card š» | MySQL Client | Software/tool used to connect to and interact with the server | MySQL Workbench, CLI, phpMyAdmin
::: card šļø | Storage Engine | The underlying mechanism that actually reads/writes data to disk | InnoDB (most common), MyISAM
::: card š | User Management | Controls who can connect and what they're allowed to do | GRANT and REVOKE privileges
:::
**Key Features of MySQL:**
| Feature | Benefit |
| :--- | :--- |
| **Open Source** | Free to use, modify, and distribute |
| **Cross-Platform** | Runs on Windows, Linux, macOS |
| **Multi-User Support** | Many clients can connect and query simultaneously |
| **Scalability** | Handles everything from tiny apps to massive websites |
| **High Performance** | Optimised query engine, indexing support |
| **Security** | User authentication, privilege-based access control |
| **ACID Compliance (InnoDB)** | Ensures reliable transactions even during crashes |
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the Client-Server architecture of MySQL?" ā **2-mark** question.
> Answer: MySQL follows a **Client-Server model** where the **MySQL Server** stores and manages all data and processes queries, while **Client programs** (command line, applications, tools like MySQL Workbench) connect to the server, send SQL queries, and receive results back.
---
## 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:**
```mermaid
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:**
| Platform / Tool | Method |
| :--- | :--- |
| **Windows (Command Line)** | Open Command Prompt ā `mysql -u root -p` |
| **XAMPP / WAMP** | Open Control Panel ā Click "Start" next to MySQL |
| **Linux** | `sudo service mysql start` then `mysql -u root -p` |
| **MySQL Workbench** | Open the app ā Double-click the saved connection |
**Basic Login Command:**
```
mysql -u root -p
```
| Part | Meaning |
| :--- | :--- |
| `mysql` | The MySQL client program being launched |
| `-u root` | Login as the user named `root` (default admin account) |
| `-p` | Prompt for a password before connecting |
::: grid
::: card ā
| Successful Login | Server running + correct credentials ā `mysql>` prompt appears | Ready to type SQL commands
::: card ā | Connection Refused | Server isn't running, or wrong host/port | Start the MySQL service first
::: card ā | Access Denied | Wrong username or password | Double-check credentials and try again
:::
> [!NOTE]
> **The `mysql>` Prompt is Your Command Centre! š§ **
> Once you see `mysql>`, you are inside the MySQL client, connected to the server, and ready to type SQL statements directly. Every statement you type here must end with a semicolon `;` to execute!
---
## 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!
```mermaid
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.
::: grid
::: card šļø | Data Definition | Define and modify the structure of database objects | CREATE, ALTER, DROP
::: card āļø | Data Manipulation | Insert, update, delete, and retrieve actual data | INSERT, UPDATE, DELETE, SELECT
::: card š | Data Control | Manage user permissions and access rights | GRANT, REVOKE
::: card š | Transaction Control | Manage groups of operations as a single safe unit | COMMIT, ROLLBACK, SAVEPOINT
:::
**SQL's Processing Capabilities ā Detailed:**
| Capability | What It Does | Example |
| :--- | :--- | :--- |
| **Define Data** | Create/modify database structures | `CREATE TABLE`, `ALTER TABLE` |
| **Retrieve Data** | Query and fetch data matching conditions | `SELECT ... WHERE ...` |
| **Manipulate Data** | Insert, update, or delete records | `INSERT INTO`, `UPDATE`, `DELETE` |
| **Control Access** | Grant or revoke permissions to/from users | `GRANT SELECT ON Student TO user1` |
| **Ensure Integrity** | Enforce rules so invalid data can't be entered | `PRIMARY KEY`, `NOT NULL`, `CHECK` |
| **Manage Transactions** | Group multiple statements as one all-or-nothing unit | `COMMIT`, `ROLLBACK` |
> [!IMPORTANT]
> **Board Exam Tip**
> "What are the processing capabilities of SQL?" ā **3-mark** question.
> Answer: SQL can **define** database structures (DDL), **manipulate** data within them (DML), **control** user access (DCL), and **manage transactions** (TCL) ā making it a complete language for working with relational databases, not just a query tool.
---
### 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.
```mermaid
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:**
| Command | Purpose | Example |
| :--- | :--- | :--- |
| **CREATE** | Build a new table, database, view, or index | `CREATE TABLE Student (RollNo INT, Name VARCHAR(30));` |
| **ALTER** | Change an existing table's structure | `ALTER TABLE Student ADD Marks INT;` |
| **DROP** | Permanently delete a table/database/view entirely | `DROP TABLE Student;` |
| **TRUNCATE** | Delete all rows instantly, but keep the table structure | `TRUNCATE TABLE Student;` |
::: grid
::: card ā ļø | DROP vs TRUNCATE vs DELETE | All three remove data, but very differently! | See comparison table below
::: card š | DDL is Auto-Committed | DDL changes are saved permanently the moment they run ā ROLLBACK won't undo them | Be careful before running DROP!
:::
**DROP vs TRUNCATE vs DELETE ā Critical Comparison:**
| Feature | DROP | TRUNCATE | DELETE |
| :--- | :--- | :--- | :--- |
| **Category** | DDL | DDL | DML |
| **Removes** | Entire table structure + data | All rows, keeps structure | Specific rows (or all, with no WHERE) |
| **Can use WHERE?** | ā No | ā No | ā
Yes |
| **Can Rollback?** | ā No (auto-committed) | ā No (auto-committed) | ā
Yes (if not committed) |
| **Speed** | Fast | Very Fast | Slower (row-by-row logging) |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between DROP, TRUNCATE, and DELETE." ā **2-3 mark** question asked very frequently!
> Core answer: **DROP** removes the entire table (structure + data) permanently. **TRUNCATE** removes all rows but keeps the empty table structure intact. **DELETE** removes rows selectively (using WHERE) and can be rolled back, unlike DROP/TRUNCATE which are auto-committed.
---
### 9.8.3 Classification of SQL Statements š
SQL commands are formally classified into **five categories**, based on what kind of task they perform.
```mermaid
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:**
| Category | Full Form | Purpose | Commands |
| :--- | :--- | :--- | :--- |
| **DDL** | Data Definition Language | Define/modify database structure | CREATE, ALTER, DROP, TRUNCATE |
| **DML** | Data Manipulation Language | Insert, update, delete data | INSERT, UPDATE, DELETE |
| **DQL** | Data Query Language | Retrieve/fetch data | SELECT |
| **DCL** | Data Control Language | Manage user access rights | GRANT, REVOKE |
| **TCL** | Transaction Control Language | Manage transactions | COMMIT, ROLLBACK, SAVEPOINT |
> [!NOTE]
> **Note on SELECT! š§ **
> Many textbooks (and CBSE itself, in older syllabi) club **SELECT** under DML rather than giving it a separate DQL category. Both classifications are accepted in CBSE answers ā just be consistent and mention SELECT's purpose (retrieving data) clearly!
> [!IMPORTANT]
> **Board Exam Tip**
> "Classify the following SQL statements: CREATE, INSERT, GRANT, COMMIT, SELECT, DROP." ā **Common 1-mark-per-item** question.
> Answer: **CREATE, DROP** ā DDL | **INSERT** ā DML | **GRANT** ā DCL | **COMMIT** ā TCL | **SELECT** ā DQL (or DML)
---
## ā ļø Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ā MySQL and SQL are the same thing | ā
MySQL is a DBMS *software*; SQL is the *language* used to interact with it (and other RDBMS too) |
| ā A View stores its own copy of data | ā
A View is virtual ā it computes results live from base tables every time it's queried |
| ā DELETE and TRUNCATE do the same thing | ā
DELETE can be selective (WHERE) and rolled back; TRUNCATE removes everything and is auto-committed |
| ā Degree refers to number of rows | ā
Degree = number of **columns**; Cardinality = number of **rows** |
| ā Database = one giant table | ā
A relational database is multiple linked tables, connected via Primary/Foreign Keys |
| ā Candidate Key and Primary Key are different concepts entirely | ā
Primary Key IS a Candidate Key ā just the one chosen to be the main identifier |
---
## š Quick Revision ā Exam Ready!
**Core Concepts ā One-Line Summary:**
- **DBMS** ā Software to manage databases; solves redundancy, inconsistency & security issues of file systems
- **Relational Model** ā Organises data into tables (relations) of rows (tuples) and columns (attributes)
- **Degree** ā Number of columns | **Cardinality** ā Number of rows
- **Primary Key** ā Uniquely identifies each row | **Foreign Key** ā Links to another table's Primary Key
- **View** ā Virtual table computed from a SELECT query; stores no data of its own
- **MySQL** ā Popular open-source RDBMS, created 1995, now owned by Oracle
- **SQL** ā The language used to define, manipulate, query, and control data in MySQL (and other RDBMS)
**SQL Classification ā Quick Table:**
| Category | Commands |
| :--- | :--- |
| DDL | CREATE, ALTER, DROP, TRUNCATE |
| DML | INSERT, UPDATE, DELETE |
| DQL | SELECT |
| DCL | GRANT, REVOKE |
| TCL | COMMIT, ROLLBACK, SAVEPOINT |
---
## šÆ 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.**
| Feature | Degree | Cardinality |
| :--- | :--- | :--- |
| **Meaning** | Number of attributes (columns) | Number of tuples (rows) |
| **Changes when** | Columns are added/removed (ALTER) | Rows are inserted/deleted |
| **Example** | Student(RollNo, Name, Marks) ā Degree = 3 | 50 students in the table ā Cardinality = 50 |
---
### 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.