Database Concepts and Structured

Query Language (SQL)

Comprehensive Class 11 notes — detailed explanations, definitions, and examples.

Contents
1. Introduction to Database and DBMS

2. Advantages of DBMS over File-Based Systems

3. Data Models (Overview)

4. Relational Data Model

- Domain, Tuple, Relation, Attribute

- Keys: Candidate, Primary, Alternate, Composite, Foreign

5. Database Language Categories (DDL, DML, DCL, TCL, DQL)

6. Structured Query Language (SQL) — Overview

7. MySQL: Introduction and Basic Setup

8. Data Types in SQL (MySQL-centric)

9. Data Definition Language (DDL) Commands

10. Data Manipulation Language (DML) Commands

11. Data Query Language (DQL): SELECT and Clauses (WHERE, ORDER BY, GROUP BY,
HAVING)

12. Joins (INNER, LEFT, RIGHT, FULL) and Self-Join

13. Constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK)

14. Integrity Rules (Entity & Referential Integrity)

15. Normalization (1NF, 2NF, 3NF)

16. Transactions and ACID Properties

17. Indexes, Views, Stored Procedures (Intro)

18. Backup, Recovery & Security (Overview)

19. Examples and Practice SQL Queries

20. Frequently Asked Questions & Tips for Exam

1. Introduction to Database and DBMS

Database: A structured collection of related data stored together so it can be accessed,
managed, and updated efficiently. Examples: student records, bank accounts, inventory.

DBMS (Database Management System): Software that enables creation, management, and
use of databases. It provides an interface between users/applications and the data, ensuring
data storing, retrieving, updating, and administration. Examples: MySQL, PostgreSQL,
Oracle, SQL Server, SQLite.

2. Advantages of DBMS over File-Based Systems

- Data Redundancy Control: DBMS reduces duplicate data by centralizing storage.

- Data Consistency: Centralized control ensures consistent data values.

- Data Sharing: Multiple users and applications can access the same database.

- Data Integrity: Constraints and rules enforce valid data.

- Security: User accounts and privileges restrict access.

- Backup & Recovery: Built-in mechanisms to recover after failure.

- Concurrent Access: Provides transaction support and concurrency control.

- Data Independence: Logical and physical data independence allow schema changes
without affecting applications.

3. Data Models (Overview)

Data model: A collection of concepts for describing data, relationships, constraints and
operations.

Common data models:

- Hierarchical Model: Data organized as a tree of records.

- Network Model: Flexible graph structures with many-to-many relationships.

- Relational Model: Data represented in tables (relations). This is the most widely used
model.

- Object-Oriented Model: Data represented as objects; supports complex data types.

4. Relational Data Model
A relation is a table with rows and columns. It is based on mathematical relation concepts.

Key terms:

- Attribute: A named column in a table (e.g., RollNo, Name, Marks).

- Domain: The set of allowable values for an attribute (e.g., integer, date, varchar(50)).

- Tuple: A single row in a relation; an ordered set of values for each attribute.

- Relation: A table; defined by a name, attributes, and a set of tuples.

- Degree: Number of attributes (columns) in a relation.

- Cardinality: Number of tuples (rows) in a relation.

Keys:

- Candidate Key: An attribute or minimal set of attributes that can uniquely identify a tuple.

- Primary Key: A chosen candidate key for uniquely identifying tuples in a relation.

- Alternate Key: Candidate keys not chosen as primary key.

- Composite Key: A key made from two or more attributes combined to ensure uniqueness.

- Foreign Key: An attribute in one relation that refers to the primary key in another relation;
used to create relationships.

5. Database Language Categories

Database languages are classified as:

- DDL (Data Definition Language): Commands to define or modify structure (CREATE,

DROP, ALTER, TRUNCATE).

- DML (Data Manipulation Language): Commands to insert, update, delete data (INSERT,
UPDATE, DELETE).

- DQL (Data Query Language): SELECT statement to query data (often grouped with DML).

- DCL (Data Control Language): GRANT, REVOKE for permissions.

- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT to control

transactions.
6. Structured Query Language (SQL) — Overview
SQL is the standard language for relational database management systems (RDBMS). It is
declarative — you specify what you want, not how to get it. SQL has various components
(DDL, DML, DCL, TCL, DQL).

SQL is case-insensitive for keywords (SELECT = select), but identifiers or string values can
be case-sensitive depending on DBMS settings.

7. MySQL: Introduction and Basic Setup

MySQL is an open-source RDBMS widely used for web applications. It supports SQL and is
known for performance and ease of use.

Basic steps to start:

- Install MySQL server (or use XAMPP/WAMP for bundled Apache+MySQL).

- Use MySQL client (mysql CLI) or graphical clients (MySQL Workbench, phpMyAdmin).

- Connect as root or a user with privileges, then create databases and tables.

8. Data Types in SQL (MySQL-centric)

Common categories:

- Numeric Types: INT, SMALLINT, BIGINT, DECIMAL(p,s), FLOAT, DOUBLE.

- Character/String Types: CHAR(n) (fixed length), VARCHAR(n) (variable), TEXT (large

text).

- Date & Time Types: DATE, TIME, DATETIME, TIMESTAMP, YEAR.

- Binary Types: BLOB, BINARY, VARBINARY.

- Boolean Type: BOOLEAN or TINYINT(1) in MySQL.

9. Data Definition Language (DDL) Commands

CREATE DATABASE database_name;

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

...
);

Example:

CREATE DATABASE School;

USE School;

CREATE TABLE Student (

RollNo INT PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

DOB DATE,

Marks INT

);

Other DDL commands: DROP TABLE table_name; DROP DATABASE database_name; ALTER
TABLE to add/drop/modify columns; TRUNCATE TABLE to remove all records quickly.

10. Data Manipulation Language (DML) Commands

INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

Example: INSERT INTO Student (RollNo, Name, DOB, Marks) VALUES (1, 'Asha', '2006-05-
12', 88);

UPDATE table_name SET column = value WHERE condition;

Example: UPDATE Student SET Marks = 90 WHERE RollNo = 1;

DELETE FROM table_name WHERE condition;

Example: DELETE FROM Student WHERE RollNo = 1;

Be careful: Omitting WHERE will affect all rows.

11. Data Query Language (DQL): SELECT and Clauses

SELECT column_list FROM table_name WHERE condition;

Basic SELECT examples:

SELECT * FROM Student; -- retrieves all columns & rows

SELECT Name, Marks FROM Student WHERE Marks >= 75;

Clauses:

- WHERE: Filter rows using conditions (>, <, =, <>, BETWEEN, IN, LIKE).

- ORDER BY: Sort rows (ASC default, DESC for descending).

- GROUP BY: Aggregate rows sharing the same values; used with aggregate functions
(COUNT, SUM, AVG, MIN, MAX).

- HAVING: Filter groups (used with GROUP BY).

- LIMIT: Restrict number of rows returned (MySQL-specific).

Aggregate functions examples:

SELECT COUNT(*) FROM Student;

SELECT AVG(Marks) FROM Student WHERE Marks IS NOT NULL;

SELECT Class, MAX(Marks) FROM Student GROUP BY Class;

12. Joins
Joins combine rows from two or more tables based on related columns.

- INNER JOIN: Returns rows when there is a match in both tables.

Example: SELECT [Link], [Link] FROM Student s INNER JOIN Course c ON

[Link] = [Link];

- LEFT (OUTER) JOIN: Returns all rows from left table and matched rows from right table;
NULL where no match.

- RIGHT (OUTER) JOIN: Returns all rows from right table and matched rows from left table.

- FULL OUTER JOIN: Returns rows when there is a match in one of the tables (MySQL
doesn't support FULL directly; use UNION of LEFT and RIGHT joins).

- SELF JOIN: A table joined with itself. Useful for hierarchical data (manager-employee).

13. Constraints
Constraints enforce rules at table level:

- NOT NULL: Column must have a value.

- UNIQUE: All values in column must be distinct.

- PRIMARY KEY: Unique identifier for table; implies NOT NULL and UNIQUE.
- FOREIGN KEY: Ensures referential integrity between two tables.

- CHECK: Ensures values meet a Boolean condition (supported in newer MySQL versions).

Example of foreign key:

CREATE TABLE Enrollment (

EnrollID INT PRIMARY KEY,

RollNo INT,

CourseID INT,

FOREIGN KEY (RollNo) REFERENCES Student(RollNo),

FOREIGN KEY (CourseID) REFERENCES Course(CourseID)

);

14. Integrity Rules

Entity Integrity: Primary key must not be NULL; ensures each tuple can be uniquely
identified.

Referential Integrity: Foreign key values must either be NULL or match a primary key value
in parent table; prevents orphan records.

15. Normalization
Normalization is the process of organizing data to reduce redundancy and improve
integrity. Common normal forms:

- First Normal Form (1NF): Eliminate repeating groups; each field contains atomic
(indivisible) values; each record unique.

- Second Normal Form (2NF): In 1NF and every non-prime attribute fully functionally
depends on the primary key (no partial dependency). Applies to tables with composite keys.

- Third Normal Form (3NF): In 2NF and no transitive dependency (non-prime attributes
depend only on key).

Short example: A table storing student courses and marks might be split into Student,
Course, and Enrollment tables to eliminate redundancy.
16. Transactions and ACID Properties
Transaction: A logical unit of database operations that must either fully succeed or fully fail.
Transactions ensure data integrity with multiple operations.

ACID properties:

- Atomicity: All steps in a transaction succeed or none do (all or nothing).

- Consistency: Transaction transforms database from one valid state to another while
preserving rules/constraints.

- Isolation: Concurrent transactions do not interfere with each other; results are as if
transactions ran sequentially.

- Durability: Once a transaction is committed, changes persist even after system failure.

Transaction control commands: COMMIT; ROLLBACK; SAVEPOINT savepoint_name; SET

TRANSACTION;

17. Indexes, Views, Stored Procedures (Intro)

Index: A data structure that improves speed of data retrieval operations on a table at the
cost of additional storage and slower writes.

CREATE INDEX idx_name ON Student(Name);

View: A virtual table based on result-set of a SELECT query. It does not store data physically
(unless materialized).

CREATE VIEW TopStudents AS SELECT Name, Marks FROM Student WHERE Marks >= 90;

Stored Procedures & Functions: Precompiled SQL code stored in DBMS for reuse. They can
accept parameters and encapsulate logic.

18. Backup, Recovery & Security (Overview)

Backup: Copy of data to restore after loss. Types: full, incremental, differential.

Recovery: Process of restoring database to a consistent state after failure.

Security: Authentication (user accounts, passwords) and authorization (GRANT/REVOKE

privileges).

Example: GRANT SELECT, INSERT ON [Link] TO 'teacher'@'localhost';

19. Examples and Practice SQL Queries
Sample schema and queries:

CREATE DATABASE School; USE School;

CREATE TABLE Student (RollNo INT PRIMARY KEY, Name VARCHAR(50), Class
VARCHAR(10), DOB DATE, Marks INT);

CREATE TABLE Course (CourseID INT PRIMARY KEY, CourseName VARCHAR(50));

CREATE TABLE Enrollment (EnrollID INT PRIMARY KEY, RollNo INT, CourseID INT,
FOREIGN KEY (RollNo) REFERENCES Student(RollNo), FOREIGN KEY (CourseID)
REFERENCES Course(CourseID));

Insert sample data:

INSERT INTO Student (RollNo, Name, Class, DOB, Marks) VALUES (1, 'Asha', '11A', '2006-
05-12', 88);

INSERT INTO Student (RollNo, Name, Class, DOB, Marks) VALUES (2, 'Rohan', '11A', '2006-
07-20', 76);

INSERT INTO Course (CourseID, CourseName) VALUES (101, 'Mathematics');

INSERT INTO Enrollment (EnrollID, RollNo, CourseID) VALUES (1001, 1, 101);

Queries:

1) SELECT * FROM Student;

2) SELECT Name FROM Student WHERE Marks > 80;

3) SELECT Class, AVG(Marks) FROM Student GROUP BY Class;

4) SELECT [Link], [Link] FROM Student s INNER JOIN Enrollment e ON [Link] =

[Link] INNER JOIN Course c ON [Link] = [Link];

5) UPDATE Student SET Marks = Marks + 2 WHERE Class = '11A';

6) DELETE FROM Student WHERE RollNo = 2; -- careful!

20. Frequently Asked Questions & Exam Tips

- Learn the syntax and common SQL commands with examples.

- Practice writing queries, especially JOINs and GROUP BY with HAVING.

- Understand normalization steps with small examples.

- Remember that PRIMARY KEY implies NOT NULL and UNIQUE.

- When asked to write SQL, include the CREATE TABLE statement with appropriate data
types and constraints.

- For practicals, memorize how to use MySQL CLIENT commands: CREATE DATABASE, USE,
SHOW TABLES, DESCRIBE table_name;

Practice Questions (with space to answer)

1. Define DBMS and list three advantages over file-based systems.

2. Explain the relational model and define tuple, attribute, and domain.

3. Write SQL statements to create a 'Library' database with tables Book(BookID, Title,
Author, Price) and Member(MemberID, Name, JoinDate).

4. Insert 3 sample rows into Book table.

5. Write queries to: a) List all books by a specific author, b) Find average price of books, c)
Delete a book by BookID.

6. Explain 1NF, 2NF and 3NF with examples.

7. What are ACID properties? Explain with an example.

Prepared by: ChatGPT — Detailed Class 11 notes on Database Concepts & SQL.