RDBMS Notes – Class 11 IT (802) | CBSE Exam Ready Notes

Get Class 11 IT RDBMS Notes with relational database concepts, MySQL, DDL & DML commands, SQL syntax, and practical examples. Fully aligned with CBSE syllabus.

Database Management System (DBMS)

• A Database Management System (DBMS) is software used to create and maintain databases on a computer.
• It helps users to organize, manage, and retrieve data efficiently.
• Some popular DBMS software include MySQL, PostgreSQL, Microsoft Access, Oracle, Microsoft SQL Server, DB2, and Sybase.
• DBMS allows users to store data in a structured manner so that it is easy to access and manage.

Benifits of DBMS

• DBMS helps in controlling data redundancy, which means avoiding unnecessary duplication of data.
• It reduces inconsistency by ensuring that the same data is not stored in multiple places with different values.
• Data can be shared among different users or systems.
• Security restrictions can be applied to protect sensitive data from unauthorized access.

Relational Database

• In a relational database, data is stored in the form of separate tables.
• These tables are connected or related to each other using common columns.
• Once relationships are established, data can be easily accessed and managed across multiple tables.
• A database that uses related tables to store data is called a Relational Database.

Relational Data Model Terminology

Domain

• A domain is the set of all possible values that a column can have.
• It defines the type of data that can be stored in a particular column.

Table (Relation)

• A table is used to store data in the form of rows and columns, similar to a spreadsheet.
• It represents data in a two-dimensional structure.
• Tables in a database are usually related to each other to improve efficiency and reduce errors.

Record (Row / Tuple)

• A record is a horizontal row in a table.
• Each record represents complete information about a single entity such as a person, place, or thing.

Field (Column / Attribute)

• A field is a vertical column in a table.
• Each column has a unique name and stores a specific type of data.
• All values in a column must be of the same type.

MySQL

• MySQL is an open-source Relational Database Management System.
• It is available free of cost and is widely used.

Keys in RDMBS

• A key is a column or a combination of columns used to identify rows in a table.
• Keys help in organizing and accessing data efficiently.

Primary Key

• A primary key is one or more columns that uniquely identify each record in a table.
• The value of a primary key must be unique for every record and cannot be repeated.
• Every table should have a primary key to ensure that each record can be uniquely identified.
• Examples of primary keys include Admission Number in a school, Patient Number in a hospital, Account Number in a bank, Employee ID, Item Code, and Flight Number.

Candidate Key

• A candidate key is a column or a group of columns that can be used as a primary key.
• There can be multiple candidate keys in a table.
• Out of all candidate keys, only one is selected as the primary key.

Alternate Key

• An alternate key is a candidate key that is not chosen as the primary key.
• For example, if both Roll Number and Admission Number can uniquely identify a record, and Admission Number is chosen as the primary key, then Roll Number becomes the alternate key.

Foreign Key

• A foreign key is a column in one table that refers to the primary key of another table.
• It is used to establish a relationship between two tables.
• For example, in an Employee table, the DEPTID column can act as a foreign key if it refers to the DEPTID column (primary key) in the Department table.
• The primary key and foreign key must have the same data type and size.

Introduction to MySQL

• MySQL is a relational database management system (RDBMS).
• MySQL was originally founded and developed in Sweden by David Axmark, Allan Larsson and Michael Widenius.
• They had worked together since the 1980s.

Characterstics of MySQL

• MySQL is released under an open-source license so it is customizable.
• It requires no cost or payment for its usage.
• MySQL has superior speed, is easy to use and is reliable.
• MySQL is a platform independent application which works on many operating systems like Windows, UNIX, LINUX etc.

How to Download MySQL

• MySQL can be downloaded from: https://www.mysql.com/downloads/
• Click on the “Download” button for the Community Server.
• Choose from the list of supported platforms (32-bit and 64-bit Windows, Linux, Solaris, Mac OS X, etc.).
• Select appropriate download link as per the operating system.
• Keep selecting the default options in subsequent windows.
• If configuration is successful:
  • Configuration file is created
  • MySQL server is installed and started
  • Security settings are applied
• During configuration, a password prompt will appear.
• The password should be entered and remembered.
• It is required each time to start MySQL.

Catergories of SQL Commands

Data Definition Language (DDL)

• DDL permits database tables to be created or deleted.
• It defines indices (keys), specifies links between tables, and imposes constraints.
• Examples:
  • CREATE DATABASE – creates a new database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table

Data Manipulation Language (DML)

• Query and update commands form the DML part of SQL.
• Examples:
  • SELECT – extracts data from a table
  • UPDATE – updates data in a table
  • DELETE – deletes data from a table
  • INSERT INTO – inserts new data into a table

MySQL Data Types

• Data types indicate the type of data stored in a table column.
• CHAR(size)
  • Fixed-length string (1 to 255 characters)
  • Right-padded with spaces
  • Values enclosed in single or double quotes
• VARCHAR(size)
  • Variable-length string (1 to 255 characters)
  • Values enclosed in single or double quotes
• DECIMAL(size, d)
  • Stores numbers with or without fractional part
  • Size specifies total digits
  • d specifies digits after decimal
• INT or INTEGER
  • Stores integer values
  • Width up to 11
• DATE
  • Stores date including day, month and year
• TIME
  • Stores time in format HH:MM:SS

Creating a Database

• Before creating a table, a database must be created.
• Command used: CREATE DATABASE
• Syntax:
  CREATE DATABASE <database name>;
• Example:
  mysql> CREATE DATABASE School;

Using a Database

• Syntax:
  USE <databasename>;
• Example:
  mysql> USE School;

Viewing the Current Database

• To see the current database, SELECT command is used.
• Syntax:
  SELECT DATABASE();
• The database name (e.g., School) will be displayed.

Creating a Table

• After creating a database, tables are created using CREATE TABLE statement.
• Syntax:
  CREATE TABLE <TableName>(<ColumnName1> <Data Type1>, 
  <ColumnName2> <Data Type2>, … , <ColumnNameN> <Data TypeN>);
• Example:
  mysql> CREATE TABLE Learner 
  (RollNo INTEGER, Name VARCHAR(25));

Constraints in SQL

• Constraints are rules to ensure validity of data.

Types of SQL Constraints

• Primary Key
  • Sets column(s) as primary key
  • NULLs and duplicate values are not accepted
• NOT NULL
  • NULL values are not accepted
• FOREIGN KEY
  • Data accepted only if value exists in related table
• UNIQUE
  • Duplicate values are not accepted

Primary Key in Table – Example

Defining primary key at column level:

CREATE TABLE Shoes 
(Code CHAR(4) PRIMARY KEY, 
Name VARCHAR(20), 
type VARCHAR(10), 
size INT(2), 
cost DECIMAL(6,2), 
margin DECIMAL(4,2), 
Qty INT(4));

Defining primary key at table level:

CREATE TABLE Shoes 
(Code CHAR(4), Name VARCHAR(20), type VARCHAR(10), size INT(2), 
cost DECIMAL(6,2), margin DECIMAL(4,2), Qty INT(4), 
PRIMARY KEY (Code));

Composite Primary Key

CREATE TABLE bills 
(Order_Num INT(4), cust_code VARCHAR(4), 
bill_Date DATE, Bill_Amt DECIMAL(8,2), 
PRIMARY KEY(Order_Num, cust_code));

Not Null Constraint

• Used when NULL values should not be accepted.

Example:

CREATE TABLE Shoes 
(Code CHAR(4) PRIMARY KEY, Name VARCHAR(20), type VARCHAR(10), 
size INT(2) NOT NULL, cost DECIMAL(6,2), 
margin DECIMAL(4,2), Qty INT(4));

Viewing Table in Database

• Syntax:
  SHOW TABLES;
• Displays list of tables in current database.

Viewing Structure of Table

• Syntax:
  DESCRIBE <table name>;
  OR
  DESC <table name>;

Modifying Table Structure – Alter Table

• ALTER TABLE is used to:
  • Add column
  • Remove column
  • Modify column
  • Add or remove constraints
• Syntax:
  ALTER TABLE <table_name> ADD/DROP <column_name> [datatype];
  
  ALTER TABLE <table> MODIFY <column> <new_definition>;

Adding column:

ALTER TABLE Student ADD Games VARCHAR(20);

Modifying column:

ALTER TABLE Student MODIFY games INTEGER;

Deleting column:

ALTER TABLE Student DROP Games;

Adding/Modifying Primary Key

• Add primary key:
  ALTER TABLE Shoe ADD PRIMARY KEY(code);
• Drop primary key:
  ALTER TABLE Shoes DROP PRIMARY KEY;
• Add new primary key:
  ALTER TABLE Shoes ADD PRIMARY KEY (Name, Size);

Not Null using Alter

ALTER TABLE bills MODIFY bill_date DATE NOT NULL;

Deleting Table Structure – Drop Table

• DROP TABLE is used to remove table completely.
• Syntax:
  DROP TABLE <tablename>;
• Example:
• DROP TABLE Orders;

Insert Records in Table – Insert Command

Insert Into <Tablename>(Column Names) Values (Value1, Value2, Value3, …);

Example:

Insert for Selected Columns:

Insert Into Student(Rno, Gender, Lname, Fname) 
Values(8, ‘F’, ‘Shanu’, ‘Deepakshi’);

Insert for All Columns

INSERT INTO STUDENT 
VALUES(1, ‘ABHISHEK’, ‘NARULA’, ‘M’, ‘1998-10-05’, 98);

Select Command

• Used to view data from a table
• Returns result set from one or more tables
• Syntax:
  SELECT <column name> FROM <table name>;

Display Single Column

SELECT rno FROM student;

Display Multiple Columns

SELECT rno, gender, fname FROM student;

Display All Columns

SELECT * FROM student;

Distinct Keyword

• Used to remove duplicate values
• Example:
  SELECT DISTINCT marks FROM student;

Where Clause

• Used to filter records based on condition
• Fetches only records satisfying condition
• Syntax:
  SELECT <column names> FROM <table name> 
  WHERE <condition>;
• Example:
  SELECT fname, marks FROM student 
  WHERE marks > 90;

Arithmetic Operators

• Used with numeric values to perform calculations
• Operators:
  • + (addition)
  • – (subtraction)
  • * (multiplication)
  • / (division)
  • % (modulus)
• Example:
  SELECT fname, marks + 10 FROM student 
  WHERE marks > 90;

Relational Operators

• Compare two values and give result as true or false
• Examples:
  > SELECT name FROM employee WHERE city = ‘Jaipur’;
  > SELECT empname FROM employee WHERE esal < 50000.00;
  > SELECT empname FROM employee WHERE edept <> ‘Sales’;

Logical Operators

SQL supports following set of logical operaators: AND, OR, NOT

AND – returns true if both conditions are true.

Example:
SELECT fname FROM student 
WHERE gender = ‘f’ AND marks > 90;

OR: returns true if any one condition is true.

Example:
SELECT Coachname, game FROM Sports 
WHERE game = ‘football’ OR game = ‘hockey’;

NOT: returns the result that is opposite to the given condition.

Example:
SELECT empname, esal FROM employee 
WHERE NOT(esal < 50000.00);

BETWEEN Operator

Used to retrieve records based on given range of values on a column.

Example:
SELECT fname, lname, marks 
FROM student 
WHERE marks BETWEEN 92 AND 95;

Handling Null Values –

IS operator is used to match NULL values in given expression. and IS NOT is used for values not equal to NULL.

Example: IS operator
SELECT empname, zone FROM employee 
WHERE zone IS NULL;

Example: IS NOT operator

SELECT empname, zone FROM employee 
WHERE zone IS NOT NULL;

Alias (As) Name to Columns

TO give alias name to column As operator is used.

Example:
SELECT rno AS ‘Roll Number’, fname AS ‘First Name’, marks 
FROM student;

IN Operator

Used to retrieve records which match a certain set of values.

Example:
SELECT fname, marks FROM student 
WHERE marks IN (88, 92, 95);

LIKE Operator

Used to retrieve recods based on string pattern matching. following symbols (wildcards) used for making patterns:

• % → any number of characters
• _ → single character

Examples:

> SELECT * FROM student WHERE fname LIKE ‘A%’;
> SELECT * FROM student WHERE fname LIKE ‘%K’;
> SELECT * FROM student WHERE fname LIKE ‘_____’;

Order By

Used to display resultant records in sorted order.

Syntax:
SELECT <columns> FROM <table> 
[WHERE condition] 
ORDER BY <column> [DESC];

Example:

SELECT fname, lname, marks FROM student 
ORDER BY marks;

SELECT fname, lname FROM student 
ORDER BY fname DESC;

SELECT fname, lname, marks FROM student 
ORDER BY marks ASC, fname DESC;

SELECT fname, lname, marks FROM student 
WHERE marks > 90 
ORDER BY marks DESC, fname ASC;

By default the records are displayed in ascending order of the column name.

Update Command

Used to modify data in table

Syntax:
UPDATE <table name> 
SET <column name> = <value>, … 
[WHERE condition];

Example:
Update Employee
Set salary = salary + salary*0.01
Where ecode = ‘E002’;

Delete Command

Used to remove records from table.

Syntax:
DELETE FROM <table name> 
[WHERE condition];

Example:
Delete From Student;

All records are removed and table becomes empty