root@vm-learning
~
$
open
ch-3-1
UNIT 3 ▪ CHAPTER 1
10
Database Concepts & SQL Basics
Relational Model · Keys · Data Types · Constraints · DDL · DML · Simple SELECT
A database is an organised collection of related data stored electronically so that it can be easily accessed, updated and managed. A Database Management System (DBMS) is the software that lets you create, query and maintain a database.
Real-life analogy. A database is like a well-organised school register — rows of students, columns for roll-number, name, class, marks. A DBMS is the register-keeper: they know how to add a new student, update marks, find “all students who scored more than 80”, and make sure no two students get the same roll number.
10.1 Why do we need a Database?
Before databases became common, applications stored data in plain files (the file handling you did in Chapters 5–7). That works for small programs, but for a school, a bank, a hospital, or a railway-reservation system it creates serious problems.
10.1.1 File System vs DBMS
| Plain file system | DBMS | |
|---|---|---|
| Data duplication | Common — same record copied into many files | Controlled — stored once |
| Data consistency | Hard — copies go out of sync | Enforced by the DBMS |
| Querying | Must write custom code for every query | Ready-made query language (SQL) |
| Security / Access control | OS file permissions only | Fine-grained user privileges |
| Concurrent access | Risky — file locking issues | Safely handled by transactions |
| Backup & recovery | Manual | Built-in tools |
| Data integrity | Application’s job | Enforced by constraints |
10.1.2 Popular DBMS Products
| Type | Examples |
|---|---|
| Open-source | MySQL, MariaDB, PostgreSQL, SQLite |
| Commercial | Oracle, Microsoft SQL Server, IBM DB2 |
| Cloud-native | Amazon RDS, Google Cloud SQL, Azure SQL |
CBSE practicals use MySQL. Everything you learn here works in MariaDB, SQLite and — with minor syntax changes — in every other SQL database.
10.2 The Relational Data Model
In the relational model (proposed by E. F. Codd in 1970), data is organised into tables called relations. Each row is one record; each column is one attribute.
10.2.1 The essential terms — at a glance
Consider this small STUDENT table:
| roll | name | class | marks |
|---|---|---|---|
| 1 | Asha | 12 | 92 |
| 2 | Rahul | 12 | 78 |
| 3 | Riya | 12 | 85 |
| 4 | Ankit | 11 | 71 |
| Term | Meaning | In the sample table |
|---|---|---|
| Relation | The table itself | STUDENT |
| Attribute | A column of the table | roll, name, class, marks |
| Tuple (record, row) | A single row | (1, 'Asha', 12, 92) |
| Domain | The set of allowed values for an attribute | For marks: 0 – 100 |
| Degree | Number of attributes (columns) | 4 |
| Cardinality | Number of tuples (rows) | 4 |
| Schema | The structure — names and types of columns | STUDENT(roll int, name varchar, class int, marks int) |
Quick mnemonic — Degree goes Down the columns (just kidding — other way!). Degree = Database has how many attributes. Cardinality = Count of rows. Think “degrees on a thermometer are column-values; cards in a deck are rows.”
10.3 Keys in a Relation
A key is an attribute (or a combination) that helps identify tuples in a table or link one table to another. Keys are the backbone of data integrity.
10.3.1 Types of keys
| Key | What it is | Example in STUDENT |
|---|---|---|
| Candidate key | Any attribute (or combination) that can uniquely identify a tuple | roll, aadhaar_no, email — all possible candidates |
| Primary key | The one candidate key chosen to uniquely identify each tuple. Cannot be NULL, cannot repeat. | roll |
| Alternate key | Candidate key(s) not selected as the primary key | aadhaar_no, email |
| Composite key | A primary key made of two or more columns together | In a RESULT table: (roll, subject_code) |
| Foreign key | An attribute that refers to the primary key of another table — used to link tables | dept_id in EMPLOYEE referring to id in DEPARTMENT |
10.3.2 Primary key rules
- There is only one primary key per table.
- It must be unique — no duplicate values allowed.
- It cannot be NULL.
- Choose the simplest candidate key: numeric and short is best.
10.4 Introduction to SQL
SQL (Structured Query Language, pronounced “sequel”) is the standard language for talking to a relational database. A single SQL statement can create tables, insert data, update data, delete data and query data.
10.4.1 Categories of SQL Commands
| Category | Purpose | Commands |
|---|---|---|
| DDL — Data Definition Language | Define / modify structure of tables | CREATE, ALTER, DROP, TRUNCATE |
| DML — Data Manipulation Language | Work with the data inside tables | INSERT, UPDATE, DELETE, SELECT |
| DCL — Data Control Language | Permissions & access | GRANT, REVOKE |
| TCL — Transaction Control Language | Control of transactions | COMMIT, ROLLBACK, SAVEPOINT |
SQL keywords are not case-sensitive —
SELECT and select are equivalent. By convention we write keywords in UPPERCASE and names in lowercase so queries read cleanly.
10.5 SQL Data Types (CBSE subset)
| Type | Stores | Example |
|---|---|---|
CHAR(n) | Fixed-length string of exactly n characters (padded with spaces) | CHAR(10) for a code |
VARCHAR(n) | Variable-length string, up to n characters | VARCHAR(50) for a name |
INT / INTEGER | Whole number | roll INT |
FLOAT / DECIMAL(p,d) | Real number; DECIMAL(5,2) holds up to 5 digits with 2 after the decimal | salary DECIMAL(9,2) |
DATE | Date in YYYY-MM-DD format | dob DATE |
CHAR(10) stores exactly 10 characters — if you save "CBSE", it is padded to "CBSE ". Use VARCHAR unless the field has a truly fixed length (like a product code or a state code).
10.6 Constraints
A constraint is a rule the database enforces on a column. Any operation that would break the rule is automatically rejected — this protects data integrity.
| Constraint | Meaning |
|---|---|
NOT NULL | The column must contain a value (cannot be empty) |
UNIQUE | No two rows may have the same value in this column |
PRIMARY KEY | = NOT NULL + UNIQUE, one per table |
DEFAULT value | Used when no value is supplied on insert |
CHECK (condition) | Row is accepted only if the condition is true |
FOREIGN KEY | Value must exist as a primary key in another table |
10.7 DDL Commands — Defining the Structure
10.7.1 CREATE TABLE
CREATE TABLE student (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
class INT DEFAULT 12,
marks DECIMAL(5,2) CHECK (marks BETWEEN 0 AND 100),
email VARCHAR(60) UNIQUE
);
The line-by-line reading: create a table named student with five columns. roll is the primary key. name must be supplied. class defaults to 12 when omitted. marks must stay between 0 and 100. email must be unique.
10.7.2 ALTER TABLE — changing an existing table
-- add a new column
ALTER TABLE student ADD city VARCHAR(30);
-- change an existing column's type
ALTER TABLE student MODIFY name VARCHAR(60);
-- remove a column
ALTER TABLE student DROP COLUMN city;
-- rename a column (MySQL 8+)
ALTER TABLE student RENAME COLUMN marks TO total_marks;
10.7.3 DROP TABLE — deleting a table permanently
DROP TABLE student; -- table structure + all data gone
DROP removes the table and every row in it — there is no “undo.” Use with care.
10.7.4 TRUNCATE vs DROP vs DELETE
| Command | Category | Removes | Structure kept? |
|---|---|---|---|
DELETE | DML | Rows (possibly filtered by WHERE) | Yes |
TRUNCATE | DDL | All rows, faster than DELETE, cannot filter | Yes |
DROP | DDL | All rows + the table itself | No |
10.8 DML Commands — Working with the Data
10.8.1 INSERT INTO — add new rows
-- all columns, in the same order as CREATE TABLE
INSERT INTO student VALUES (1, 'Asha', 12, 92.00, 'asha@mail.com');
-- specific columns (safer — order-independent)
INSERT INTO student (roll, name, marks)
VALUES (2, 'Rahul', 78.50);
-- multiple rows at once
INSERT INTO student (roll, name, class, marks, email) VALUES
(3, 'Riya', 12, 85.00, 'riya@mail.com'),
(4, 'Ankit', 11, 71.25, 'ankit@mail.com');
Always list the column names in real projects — your query then keeps working even after
ALTER TABLE adds or reorders columns.
10.8.2 UPDATE — change existing rows
-- change the marks of roll no. 2
UPDATE student
SET marks = 88
WHERE roll = 2;
-- give a 5-mark grace to every class-11 student
UPDATE student
SET marks = marks + 5
WHERE class = 11;
Never forget the
WHERE in an UPDATE! Without it, every row in the table is updated. The same warning applies to DELETE.
10.8.3 DELETE — remove rows
-- remove one student
DELETE FROM student WHERE roll = 4;
-- remove every class-11 student
DELETE FROM student WHERE class = 11;
-- remove EVERY student (use TRUNCATE if that’s really what you want)
DELETE FROM student;
10.9 Simple SELECT — Reading the Data
SELECT is the command you will use most. Chapter 11 explores every clause in detail; the simple forms below are enough to get started.
10.9.1 Every column, every row
SELECT * FROM student;
10.9.2 Specific columns
SELECT name, marks FROM student;
10.9.3 Column aliases — AS
SELECT name AS student_name,
marks AS total_marks
FROM student;
10.9.4 Removing duplicates — DISTINCT
-- list the different classes present
SELECT DISTINCT class FROM student;
10.9.5 A simple expression in SELECT
SELECT name, marks, marks / 100 * 100 AS percentage
FROM student;
10.10 Worked Examples — Putting It All Together
10.10.1 Design a BOOK table for a library
CREATE TABLE book (
isbn VARCHAR(13) PRIMARY KEY,
title VARCHAR(120) NOT NULL,
author VARCHAR(80) NOT NULL,
price DECIMAL(7,2) CHECK (price > 0),
qty INT DEFAULT 0,
pub_date DATE
);
10.10.2 Populate it with a few rows
INSERT INTO book VALUES
('9780143441724', 'Wings of Fire', 'APJ Abdul Kalam', 299.00, 5, '1999-01-01'),
('9788172234980', 'Malgudi Days', 'R. K. Narayan', 250.00, 3, '1943-10-15'),
('9788172234998', 'The God of Small…', 'Arundhati Roy', 499.00, 2, '1997-04-04');
10.10.3 Show only titles and prices
SELECT title, price FROM book;
10.10.4 Update the quantity of a book
UPDATE book SET qty = qty + 10 WHERE isbn = '9788172234980';
10.10.5 Remove out-of-stock books
DELETE FROM book WHERE qty = 0;
10.11 Common Mistakes to Avoid
| # | Mistake | Fix |
|---|---|---|
| 1 | Forgetting WHERE in UPDATE / DELETE | Always double-check — without it the operation runs on every row |
| 2 | Using double-quotes for a string value | SQL strings go in single quotes — 'Asha', not "Asha" |
| 3 | Forgetting the ; at the end of a statement | Every MySQL statement ends with ; |
| 4 | Mixing up CHAR and VARCHAR | CHAR(n) = fixed, pads with spaces; VARCHAR(n) = up to n |
| 5 | Confusing DROP, TRUNCATE, DELETE | Drop removes the table; truncate empties it; delete removes (optionally filtered) rows |
| 6 | Two primary keys in one table | Only one primary key allowed — use UNIQUE for additional candidates |
| 7 | Inserting NULL into a NOT NULL column | Either supply a value or remove the NOT NULL constraint |
| 8 | Using reserved words as column names (e.g. order, class) | Rename the column or wrap it in back-ticks: `class` |
Quick-revision summary
- A database is organised data; a DBMS is the software that manages it. CBSE uses MySQL.
- Relational model terms: relation = table, tuple = row, attribute = column, domain = allowed values; degree = # columns, cardinality = # rows.
- Keys: candidate, primary (one chosen), alternate (others), composite (multi-column PK), foreign (references another table’s PK).
- SQL categories: DDL (CREATE, ALTER, DROP, TRUNCATE) · DML (INSERT, UPDATE, DELETE, SELECT) · DCL · TCL.
- Data types:
CHAR(n),VARCHAR(n),INT,FLOAT/DECIMAL(p,d),DATE. - Constraints:
NOT NULL,UNIQUE,PRIMARY KEY,DEFAULT,CHECK,FOREIGN KEY. CREATE TABLE,ALTER TABLE,DROP TABLEfor structure;INSERT,UPDATE,DELETEfor rows.- Always include
WHEREinUPDATE/DELETE, end each statement with;, wrap strings in single quotes.