Unit 1: Database Concepts – RDBMS ToolRelational Data Models, Constraints & MySQL SQL Mastery
Authoritative textbook notes based on the official CBSE Class XII Student Handbook. Master relational schema definitions, superkeys, candidate keys, entity and referential integrity constraints, MySQL server configuration, DDL/DML queries, multi-table joins, GROUP BY, and solved board exercises.
Data, Information & The Need for Database Systems
In traditional file processing, each application stores its own independent data files (e.g., Sales maintains File1, while Payroll maintains File2). This siloed design creates three critical disadvantages:
Key Properties & Characteristics of DBMS
- Self-Describing Nature: A DBMS stores not only the data itself, but also complete description of data structures and constraints (metadata) in a data dictionary / catalogue.
- Program-Data Independence: Because data definitions are insulated in the catalogue, changing table schemas does not require rewriting the application programs that access the data.
- Multi-User Concurrency: Built-in concurrency control ensures multiple users can safely access and update data simultaneously without collision.
- Four User Categories: 1) End Users (query and report), 2) Database Administrator (DBA) (grants access, monitors hardware/software, recovery), 3) Application Programmers (write software with embedded SQL), and 4) System Analysts (gather end-user requirements and design schemas).
Relational Data Model: Terminology, Keys & Constraints
| Relational Term | SQL / Common Term | Formal Definition & Handbook Rules |
|---|---|---|
| Relation | Table | A two-dimensional tabular structure composed of rows and columns. Order of rows and columns is unimportant. |
| Tuple | Row / Record | A single horizontal instance representing real-world entity data. All values are atomic. No two tuples can be duplicate. |
| Attribute | Column / Field | A named vertical property defining the interpretation of values in that column. |
| Domain | Data Type & Permitted Set | The set of atomic, indivisible values from which an attribute draws its valid data. |
| Degree | Column Count | Total number of attributes in a relation. (e.g. 5 columns $\rightarrow$ Degree = 5). |
| Cardinality | Row Count | Total number of tuples currently stored in a relation. (e.g. 25 rows $\rightarrow$ Cardinality = 25). |
Hierarchy of Keys (From Superkey to Primary Key)
1. Superkey: Any attribute combination that uniquely identifies tuples. The set of all attributes in a relation is always a superkey.
2. Key (Minimal Superkey): A superkey from which no attribute can be removed without losing the uniqueness property.
3. Candidate Key: Any minimal superkey available in the relation. For a PERSON table, Aadhar_number, PAN, and Voter_ID are all candidate keys.
4. Primary Key: The chosen candidate key (usually the one with the fewest columns). Primary keys are underlined in relation schemas and can never contain NULL (Entity Integrity).
5. Foreign Key: An attribute in referencing relation $R_1$ that references the primary key of another relation $R_2$, enforcing Referential Integrity.
Complete MySQL SQL Syntax & Query Architecture
SQL is divided into DDL (defining structure) and DML (manipulating rows). Here are the standard SQL commands covered in the CBSE Class 12 handbook:
CREATE DATABASE School;
SHOW DATABASES;
USE School;
SHOW TABLES;
DROP DATABASE School;
CREATE TABLE Department (
Dept_ID INTEGER PRIMARY KEY,
Dept_Name VARCHAR(30) NOT NULL
);
CREATE TABLE Teacher (
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_No)
REFERENCES Department(Dept_ID)
ON DELETE SET NULL ON UPDATE CASCADE
);
ALTER TABLE Teacher ADD Age INTEGER;
ALTER TABLE Teacher DROP Age;
ALTER TABLE Teacher ALTER Salary SET DEFAULT 45000;
ALTER TABLE Teacher DROP FOREIGN KEY TEACHER_FK;
ALTER TABLE Teacher ADD CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID);
INSERT INTO Teacher VALUES (101, 'Shanaya', 'Batra', 'F', 50000, '1984-08-11', 1);
UPDATE Teacher SET Salary = Salary + 5000 WHERE Teacher_ID = 101;
DELETE FROM Teacher WHERE Teacher_ID = 101;
-- Pattern matching with % and _
SELECT First_Name FROM Teacher WHERE First_Name LIKE 'S%';
-- Equi-Join with Table Aliases
SELECT T.First_Name, T.Last_Name, D.Dept_Name
FROM Teacher AS T, Department AS D
WHERE T.Dept_No = D.Dept_ID;
-- GROUP BY with HAVING filter
SELECT Dept_No, COUNT(*) AS No_of_Teachers
FROM Teacher
GROUP BY Dept_No
HAVING COUNT(*) > 1;
Solved Textbook Exercises (Employee & Railway Tables)
Step-by-step query solutions for questions Q1 & Q2 on Pages 46–47 of the handbook.
Exercise Q1: Employee Table Solutions (Handbook Page 46)
Given Employee(Employee_ID, Employee_Name, Job_Title, Salary, Bonus, Age, Manager_ID):
(a) Create table: CREATE TABLE Employee (Employee_ID INT PRIMARY KEY, Employee_Name VARCHAR(30), Job_Title VARCHAR(20), Salary DECIMAL(10,2), Bonus DECIMAL(10,2), Age INT, Manager_ID INT, FOREIGN KEY (Manager_ID) REFERENCES Employee(Employee_ID));
(c) Delete ID 1217: DELETE FROM Employee WHERE Employee_ID = 1217;
(d) Update Amyra's salary: UPDATE Employee SET Salary = 40000 WHERE Employee_Name = 'Amyra';
(e) Prevent NULL in Age: ALTER TABLE Employee MODIFY Age INT NOT NULL;
(f) Salary > 20000: SELECT Employee_Name, Salary FROM Employee WHERE Salary > 20000;
(g) No bonus: SELECT * FROM Employee WHERE Bonus IS NULL;
(h) Ends with 'a': SELECT Employee_Name FROM Employee WHERE Employee_Name LIKE '%a';
(i) Manager_ID = 1201: SELECT Employee_Name, Job_Title FROM Employee WHERE Manager_ID = 1201;
(j) Managed by Amyra: SELECT E.Employee_Name, E.Job_Title FROM Employee E, Employee M WHERE E.Manager_ID = M.Employee_ID AND M.Employee_Name = 'Amyra';
(k) Age 26 to 30: SELECT Employee_Name, Job_Title FROM Employee WHERE Age BETWEEN 26 AND 30;
Exercise Q2: Railway Ticket Machine Solutions (Handbook Page 47)
Given Machine(Machine_ID, Station) and Sales(Machine_ID, Date, Tickets_Sold, Income):
(b) Ticket machines per station: SELECT Station, COUNT(*) AS Total_Machines FROM Machine GROUP BY Station;
(c) Daily income for New Delhi: SELECT S.Date, SUM(S.Income) AS Total_Daily_Income FROM Machine M, Sales S WHERE M.Machine_ID = S.Machine_ID AND M.Station = 'New Delhi' GROUP BY S.Date;
(d) Total tickets sold by machine 122: SELECT SUM(Tickets_Sold) AS Total_Sold FROM Sales WHERE Machine_ID = '122';
Official Unit 1 Practice Questions
Q1. What is the degree and cardinality of a table with 6 columns and 30 records?
Q2. Which of the following correctly describes a 'Key' in the Relational Data Model?
Q3. What does the Entity Integrity Constraint state?
Q4. In SQL, which clause is used to filter rows BEFORE grouping, and which clause filters groups AFTER grouping?
Q5. When creating a foreign key with 'ON DELETE CASCADE', what happens if a record in the parent (referenced) table is deleted?
Q6. Which wildcard character in the SQL 'LIKE' operator represents exactly one single character?
Q7. How should you correctly test if the column 'Last_Name' has no value assigned?
Q8. Which SQL command modifies the structure of an existing table (such as adding or dropping a column)?
Q9. What is the result of multiplying the number of rows of two tables without specifying a join condition (WHERE clause)?
Q10. Which built-in aggregate function counts all rows in a query result, including those with NULL values?
Unit 1 Database Concepts FAQs (AEO & Board Focus)
Master Class 12 MySQL & SQL Queries Live
Book a free 1-on-1 trial class with our CBSE expert mentors. Learn table normalization, nested subqueries, and practical exam problem solving.