CLASS : XII I.T.

(802)

UNIT – 1
Database Concepts – RDBMS Tools

Database
The collection of interrelated data is known as database. A database system is basically a
computer based record keeping system. In other words collection of tables is known as database.

DBMS
It stands for Data Base Management System. It is application software which allows us to create a
database, to modify a database, to delete a database etc. It supports single user. It stores data as
file. Example : Foxpro, Window Registry etc.

Advantages :
(i). It is user friendly.
(ii). It reduces data redundancy (duplication of data).
(iii). It controls data inconsistency (mismatch of data).
(iv). It facilitates sharing of data.
(v). It enforces standards.
(vi). It ensures security of data.
(vii). It maintains data integrity.

RDBMS
It stands for Relational Database Management System. It stores data in tabular form. It allows to
work on multiple tables at a time. It supports multiple users. Example : MySQL, MS SQL Server,
Oracle etc.

Types of Users of DBMS

1. End User : It is the user who uses the database for querying, modifying and generating
reports as per the requirements. This user is not concerned with about the working and
designing of the database.
2. DBA (Data Base Administrator) : This user is responsible for administrating database and
DBMS such as authoring access, monitoring, technical support etc.
3. Application Programmer : This user writes the programs to interact with database.
4. System Analyst : This user determines the requirements of end users and then develops
specifications to meet the requirements.

Relational Model Terminology

These are the following important terms used in relational model :
1. Relation : Here table is known as relation. A table is a collection of rows and columns.
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 1 of 12
CLASS : XII I.T. (802)

2. Domain : All possible values in each column are called domain.

3. Tuple : A row of a relation is known as tuple. In other words, record is known as tuple.
4. Attribute : A column (field) of a relation is known as attribute.
5. Degree : The number of attributes (columns) in a relation (table) is known as degree.
6. Cardinality : The number of tuples (records) in a relation (table) is known as cardinality.
7. View : It is a virtual table.
8. Primary Key : It is a set of one or more attributes which uniquely identifies the tuples in the
relation.
9. Candidate Key : If we apply primary key on more than one attributes jointly then primary
key is known as candidate key.
10. Alternate Key : A candidate key which is not primary key, is known as alternate key.
11. Foreign Key : A non-key whose values are derived from the primary key of some other
relation, is known as foreign key in its current table.

Introduction to MySQL
It is an open source RDBMS (Relational Database Management System) which is available free
on [Link]. It was invented by Michael Widenius (a.k.a. Monty) in Sweden. It has been
named after Monty’s daughter My. The logo of MySQL is dolphin and its name is Sakila. It uses
SQL (Structured Query Language) command to work in it. Here data are stored in a table. It
provides a secure environment to store, maintain, and accessing data. It follows client/server
architecture.

How to start MySQL

Start → All Programs → MySQL → MySQL Server → MySQL Command Line Client
Or
Type “MySQL – u root – p” at command prompt.

SQL
It stands for Structured Query Language. It is a set of commands which is recognized by all
RDBMS. It is a database language which allows us to create and operate on relational databases.
SQL statement always end with a semicolon ( ; ).

Classification of SQL Commands/Statements

SQL is mainly classified into the following categories :
1. DDL : It stands for Data Definition Language. This type of command allows us to define
structure of database such as creating, modifying and deleting database. DDL commands
are :
 CREATE DATABASE
 CREATE TABLE
 ALTER TABLE
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 2 of 12
CLASS : XII I.T. (802)

 DROP TABLE
 DROP DATABASE
 TRUNCATE
 RENAME
2. DML : It stands for data manipulation language. This type of command is used to insert,
update, retrieve and delete records in a table. DML commands are :
 INSERT INTO
 SELECT
 UPDATE
 DELETE
3. DCL : It stands for Data Control Language. It is used to grant and remove permission from
any database user. DCL commands are :
 GRANT
 REVOKE
4. TCL : It stands for Transaction Control Language. This type of command allows us to
perform or manage and control the transactions. TCL commands are :
 BEGIN TRANSACTION
 COMMIT
 ROLLBACK
 SAVEPOINT

MySQL Data Types

These are the following important SQL elements :
1. Literals : Fixed data value is known as literals. It can be of character type or numeric type.
In MySQL, a text literal can have maximum length of 4000 bytes and a numeric literal can
be of maximum 53 digits of precision.
e.g. – ‘a’, ‘Kumar Gourab’, 305, 80.9 etc
2. Data Types : This is a concept which allows us to specify type of data entered in database.
MySQL has three categories of data types :
a. Numeric : It uses all standard ANSI numeric data types. MySQL supports the
following numeric data types :
i. BIGINT : It is used to store very large signed or unsigned integer value. Its
range for signed value is -9223372036854775808 to
+9223372036854775807. Its range for unsigned value is 0 to
18446744073709551615. It stores value up to 11 digits.
ii. INT : It is used to store large signed or unsigned integer value. Its range for
signed value is -2147483648 to +2147483647. Its range for unsigned value is
0 to 4294967295. It stores value up to 11 digits.
iii. MEDIUMINT : It is used to store medium signed or unsigned integer value. Its
range for signed value is -8388608 to + 8388607. Its range for unsigned value
is 0 to 16777215. It stores value up to 9 digits.
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 3 of 12
CLASS : XII I.T. (802)

iv. SMALLINT : It is used to store small signed or unsigned integer value. Its
range for signed value is -32768 to + 32767. Its range for unsigned value is 0
to 65535. It stores value up to 5 digits.
v. TINYINT : It is used to store very small signed or unsigned integer value. Its
range for signed value is -128 to + 127. Its range for unsigned value is 0 to
255. It stores value up to 4 digits.
vi. DECIMAL (L, D) : It is used to store signed fractional (float) value. It does not
store unsigned value. Here ‘L’ indicates the total number of digits including
decimals and ‘D’ indicates number of decimals. It is unpacked (unlimited)
floating point number. Here each decimal is of one byte.
vii. DOUBLE (L, D) : It is used to store signed fractional (float) value. It does not
store unsigned value. Here ‘L’ indicates the total number of digits including
decimals and ‘D’ indicates number of decimals. Its default value is (16, 4). It
can have maximum 53 places.
viii. FLOAT(L, D) : It is used to store signed fractional (float) value. It does not
store unsigned value. Here ‘L’ indicates the total number of digits including
decimals and ‘D’ indicates number of decimals. Its default value is (10, 2). It
can have maximum 24 places.
b. Date and Time : MySQL supports the following date and time data types :
i. DATE : It is used to store date in the format YYYY-MM-DD. Its range is
between 1000-01-01 to 9999-12-31.
ii. DATETIME : It is used to store date and time both together in the format
YYYY-MM-DD HH:MM:SS. Its range is between 1000-01-01 00:00:00 to
9999-12-31 15:30:00.
iii. TIMESTAMP : It is used to store date and time both together in the format
YYYYMMDDHHMMSS. It is similar to DATETIME. Only difference is that it
stores date and time without space or symbol.
iv. YEAR (N) : It is used to store a year in 2-digits or 4-digits format. If N is 2
then its range is from 1970 to 2069 (70-69). If N is 4 then its range is from
1901 to 2155 (70-69). The default value of N is 4.
v. TIME : It is used to store time in the format HH:MM:SS.
c. String : In MySQL, most data are stored in string format. These are the following
string data types :
i. CHAR (N) : It is used to store a fixed length string. Its range is from 1 to 255
characters in length. The default value of N is 1.
ii. VARCHAR (N) : It is used to store a variable length string. Its range is from 1
to 255 characters in length.
iii. BLOB : it stands for Binary Large Object. It is used to store the large amount
of binary data such as images or other types. It can store maximum 65535
characters. We do not specify length with this data type. Its string is case
sensitive during comparison.

Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 4 of 12

CLASS : XII I.T. (802)

iv. TEXT : It is used to store the large amount of strings. It can store maximum
65535 characters. We do not specify length with this data type. Its string is
not case sensitive during comparison.
v. TINYBLOB or TINYTEXT : It is used to store the very small string. It can
store maximum 255 characters. We do not specify length with this data type.
vi. MEDIUMBLOB or MEDIUMTEXT : It is used to store the very large string. It
can store maximum 16777215 characters. We do not specify length with this
data type.
vii. LONGBLOB or LONGTEXT : It is used to store the very-very large string. It
can store maximum 4294967295 characters. We do not specify length with
this data type.
viii. ENUM : An enumeration is a fancy term of list. It is used to create a list of
items from which the value must be selected.

Difference Between CHAR and VARCHAR

CHAR is used to store fixed length string but VARCHAR is used to store variable length string. In
CHAR, if a value is shorter than the given length then blank spaces are added. It means size will
be same for all values. But in VARCHAR, if a value is shorter than the given values then the size
is decreased.

NULL values
NULL value is a special character. It is neither a space nor a zero. If we leave any field empty then
NULL value is inserted automatically in the field. When we perform any arithmetic operations on
NULL value the result will be NULL value, such as if we add 5 to NULL value then result will be
NULL.

Comment
A comment is a message which is used only for documentation. In MySQL, multi line comment is
written by using the symbol /* ……………. */ and single line comment is written by two hyphens
followed by a space (- - ) or by using the symbol #.

Commands of MySQL
Commands of MySQL are terminated by semicolon (;). These are the following commands of
MySQL :
1. USE <database> : This command is used to open a database.
Syntax – USE <database>;
e.g. mysql> USE School;
Database changed
mysql>
Note :- show tables : This is used to display all tables.
e.g. mysql> show tables;
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 5 of 12
CLASS : XII I.T. (802)

2. CREATE TABLE : This command is used to create a table. A table must have at least one
column or field.
Syntax – CREATE TABLE <tablename> (<column name>, <datatype(<size>)>, <column
name>, <datatype(<size>)>, ……………..);
e.g. mysql> CREATE TABLE student(id int, sname varchar(15), roll int);
3. INSERT INTO : This command is used to insert record (tuple) in the table.
i. Syntax – INSERT INTO <table name> (<column name1>, <column name2>, …..)
VALUES (<value1>, <value2>, ………);
The above syntax is used to store value in selected fields.
e.g. mysql> INSERT INTO student (id, sname) VALUES(1001, ‘Kumar’);
ii. Syntax – INSERT INTO <table name> values( <value1>, <value2>, ……);
The above syntax is used to store values in all fields.
e.g. mysql> INSERT INTO student values(1001, ‘Kumar’, 1);
iii. Syntax - INSERT INTO <table name> (<column name1>, <column name2>, ……)
VALUES (<value1>, <value2> ………);
The above syntax is used to null values in the field.
e.g. mysql> INSERT INTO student(id, sname, roll) VALUES(1001, ‘Kumar’, null);
iv. Syntax – INSERT INTO <table name1> SELECT * FROM <table name2> WHERE
<condition>;
This command is used to insert or derived record from other table2 to table1.
e.g. INSERT INTO student SELECT * FROM marks WHERE roll>=1;
4. SELECT : This command is used to display records from the given table.
Syntax – SELECT <what-to-select> FROM <table name> WHERE <conditions>;
e.g.
i. SELECT * FROM student;
This command is used to display all records with all columns.
ii. SELECT * FROM student WHERE id = 1001;
This command will display only those records with all fields which id is equal to
1001.
iii. SELECT * FROM student WHERE sname = ‘Kumar’;
This command will display only those records with all fields which sname is equal to
Kumar.
iv. SELECT * FROM student WHERE dob = ‘1981-07-27’;
This command will display only those records with all fields which dob is equal to
1981-07-27.
v. SELECT * FROM student WHERE roll = 1 AND roll = 5;
This command will display only those records with all fields which roll is equal to 1
and 5.
vi. SELECT * FROM student WHERE roll = 1 or roll = 5;
This command will display only those records with all fields which roll is equal to 1 or
5.
vii. SELECT id, sname FROM student;
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 6 of 12
CLASS : XII I.T. (802)

This command will display all records with id and sname fields only.
viii. SELECT id, sname FROM student where dob > ‘1981-07-27’;
This command will display only those records with id and sname fields which dob is
greater than 1981-07-27.
ix. SELECT DISTINCT sname FROM student;
This command will not display those records having duplicate value in sname.
x. SELECT ALL sname FROM student;
This command will display all values of sname.
xi. SELECT id, sname FROM student WHERE roll BETWEEN 10 AND 20;
This command will display only those records with id and sname field which has the
roll greater than 10 and less than 20.
xii. SELECT * FROM student WHERE sname IN (‘Kumar’, ‘Gourab’, ‘Mihir’);
This command will display only those records which has the values Kumar or
Gourab or Mihir in sname filed.
xiii. SELECT * FROM student WHERE sname NOT IN (‘Kumar’, ‘Gourab’, ‘Mihir’);
This command will display all records except the values Kumar or Gourab or Mihir in
sname filed.
xiv. SELECT sname FROM student WHERE sname LIKE ‘%r’;
This command will display only those records having ends with r in sname field.
xv. SELECT sname FROM student WHERE sname LIKE ‘Ku%’;
This command will display only those records having starting with Ku letter in sname
field.
xvi. SELECT id, sname FROM student WHERE roll IS NULL;
This command will displays only those records with id and sname fields which has
null value in roll field.
xvii. SELECT * FROM student ORDER BY sname;
This command will display all records with all columns in ascending order according
to the value of sname field.
xviii. SELECT <column name> AS <column name>, ….. FROM <table name>;
e.g. SELECT sname AS student’s Name FROM student;
This command will display all records with column name Student’s Name instead of
sname.
5. DESCRIBE or DESC : This command is used to display structure of given table.
Synrax – DESC <table name>;
e.g. mysql> DESC student;
6. UPDATE : This command is used to update or modify the record(s) of given table name.
Syntax – UPDATE <table name> SET <field name> = <value>,………where <condition>;
i. e.g. UPDATE student SET sname = ‘Raj’ where roll = 1;
It will update only those records which roll is equal to 1.
ii. e.g. UPDATE student SET class = ‘X’;
It will update all records of given table.
7. DELETE : This command is used to delete the record(s) from given table name.
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 7 of 12
CLASS : XII I.T. (802)

Syntax – DELETE FROM <table name> WHERE <condition>;

e.g. DELETE FROM student;
This command will delete all records from student table.
e.g. DELETE FROM student WHERE roll = 1;
This command will delete only those record which roll is equal to 1.
8. ALTER : This command is used to change definition of given table name. It is used to add
a new column, to delete a column, to redefine a column in the table. It has the following
syntax :
i. Syntax – ALTER TABLE <table name> ADD <columnname datatype>;
This command is used to add new column in existing table.
e.g. ALTER TABLE student ADD (marks int);
ii. Syntax – ALTER TABLE <>
iii. Syntax – ALTER TABLE <table name> MODIFY <columnname newdatatype>
[FIRST | AFTER];
The above command is used to modify given column name of existing table.
e.g. ALTER TABLE student MODIFY (sname varchar(20));
iv. Syntax – ALTER TABLE <table name> CHANGE <old column name> <new column
name datatype>;
This command is used to change the name of given column of existing table.
e.g. ALTER TABLE student CHANGE (sname studname varchar(20));
v. Syntax – ALTER TABLE <table name> ADD PRIMARY KEY(column name);
This command is used to add constraint Primary Key in the column of existing table.
e.g. ALTER TABLE student ADD PRIMARY KEY(id);
vi. Syntax – ALTER TABLE <table name> DROP PRIMARY KEY;
This command is used to delete primary key from the given table name.
e.g. ALTER TABLE student DROP PRIMARY KEY;
9. DROP : This command is used to delete a table or column from database.
i. Syntax – DROP TABLE <table name>;
This command is used to delete the given table name from database.
e.g. ALTER TABLE student;
ii. Syntax – DROP TABLE IF EXISTS <table name>;
This command checks the existence of given table first and it deletes from
the database.
e.g. ALTER TABLE IF EXISTS student;
iii. Syntax – ALTER TABLE <table name> DROP <column name>;
This command is used to delete column(s) from given table.
e.g. ALTER TABLE student DROP name;
e.g. ALTER TABLE student DROP name, DROP roll;
SQL Constraints
A constraint is a condition (restriction) or rules on a field or set of fields during. It ensures accuracy
and reliability of the data in the table. These are following constraints of MySQL:
1. NOT NULL – If we do not want to allow NULL value in a column.
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 8 of 12
CLASS : XII I.T. (802)

Syntax – CREATE TABLE <table name> ( field name datatype NOT NULL);
e.g. CREATE TABLE marks (marksid number NOT NULL);
2. DEFAULT – This constraint is used to provide default value to a column
Syntax – CREATE TABLE <Table name>(field name, datatype DEFAULT <value>);
e.g. CREATE TABLE marks (marksid number NOT NULL, comp DEFAULT 50;
3. UNIQUE – This constraint is used to ensure that the values in a column are unique or
distinct. It can have null values.
Syntax – CREATE TABLE <table name> (field name datatype);
e.g. - Create TABLE student(marksid int unique);
4. CHECK – This constraints ensures that all values in a column satisfy the given condition.
Syntax – CREATE TABLE <Table Name>(column name datatype check(condiion));
e.g. CREATE TABLE Marks(phy int check(marksid > 0));
5. PRIMARY KEY : A primary key is a key which is used to uniquely identify each row in a
table. It cannot be left empty also. When we apply primary key on multiple fields then that is
known as COMPOSITE KEY.
Syntax – CREATE TABLE <table name>(column name datatype primary key);
e.g. CREATE TABLE student (studid int PRIMARY kEY)
6. FOREIGN KEY : In RDBMS, tables references one another through common fields and
ensure validity of references. Referential integrity is a system of rules that a DBMS use to
ensure the relationship between records in related tables are valid. Whenever two tables
are related by a common column, then the related columns in the parent table should be
declared a PRIMARY KEY or UNIQUE KEY and the related column in the child table.
Syntax – FOREIGN KEY <column name> REFERENCES <master table> (PRIMARY KEY
of master table);
e.g. create table orders(orderid int, customerid int, amount double, Primary
Key(order_id), FOREIGN KEY(customerid_id) REFERENCES cumtomer(SID));

Important Questions :
1. What is difference between DBMS and RDBMS?
Ans  These are the following differences between DBMS and RDBMS :
DBMS RDBMS
 It stands for Data Base  It stands for Relational Data Base
Management System. Management System.
 It stores data in the form of files.  It stores data in the form of table.
 It can handle small amount of data.  It can handle huge amount of data.
 It supports single user at a time.  It supports multiple users at a time.

Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 9 of 12

CLASS : XII I.T. (802)

Functions :
Function is a predefined formula which makes the calculation easily. There are two types of
functions :
1. Single Row Function :
2. Aggregate Function / Multiple Row Function: A function which works on multiple row is
called Group Function or Aggregate Function or Multiple Row Function. These
functions are :
(i). Max() : It returns maximum value of the given column name or expression based on
a column.
Eg.  Select Max(Phy) from Marks;
(ii). Min() : It returns minimum value of the given column name or expression based on a
column.
Eg.  Select Min(Phy) from Marks;
(iii). Avg() : It returns average value of the given column name or expression based on
a column.
Eg.  Select Avg(Phy) from Marks;
(iv). Sum () : It returns total value of the given column name or expression based on a
column.
Eg.  Select Sum(Phy) from Marks;
(v). Count() : It counts the number of values in a given column.
Eg.  (a). Select Count(*) from Tablename; - It counts total number of records in the
given tablename.
Eg.  (b). Select Count(DISTINCT sub) from class; - It counts different subject of
class table.
Eg.  (c). Select Count(Phy) from marks where Phy > 30; - It counts the record for
which physics is greater than 30.
Group By : This clause of MySQL is used to generates report based on some group of data.
These groups are based on some column values.
Eg.  Select type, sum(qty) from Shoes Group By type;
Output :- +--------+----------+
| type | SUM(qty) |
+--------+----------+
| Office | 1100 |
| School | 7180 |
| Sports | 1740 |
+--------+----------+
Having : This clause is used to generate output only for those groups which satisfy some
conditions. In other words, it is used to put some conditions on individual groups.
Eg.  SELECT type, sum(qty) FROM Shoes GROUP BY type HAVING Sum(qty) > 1500;
Output :- +--------+----------+
| type | SUM(qty) |
Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 10 of 12
CLASS : XII I.T. (802)

+--------+----------+
| School | 7180 |
| Sports | 1740 |
+--------+----------+
Displaying Data From Multiple Tables
(i). Cartesian Product Or Cross Join of Tables : It is a process of pairing up each row of one
table with each row of the other table. For example, if two tables contains 3 rows and 2 rows
respectively, then its Cartesian product will contain 3 X 2 = 6 rows.

(1,a)
1 a (2,a)
(3,a)
2 (1,b)
(2,b)
3 b (3,b)

Cartesian Product of Two Tables

Product Order_table
+------+-------------+ +----------+--------+----------+
| Code | Name | | Order_No | P_Code | Sup_Code |
+------+-------------+ +----------+--------+----------+
| P001 | Toothpaste | | 1 | P001 | S002 |
| P002 | Shampoo | | 2 | P002 | S002 |
| P003 | Conditioner | +----------+--------+----------+
+------+-------------+
Eg  SELECT * FROM order_table, product;
Output :-
+----------+--------+----------+------+-------------+
| Order_No | P_Code | Sup_Code | Code | Name |
+----------+--------+----------+------+-------------+
| 1 | P001 | S002 | P001 | Toothpaste |
| 2 | P002 | S002 | P001 | Toothpaste |
| 1 | P001 | S002 | P002 | Shampoo |
| 2 | P002 | S002 | P002 | Shampoo |
| 1 | P001 | S002 | P003 | Conditioner |
| 2 | P002 | S002 | P003 | Conditioner |
+----------+--------+----------+------+-------------+
(ii). Equi-Join of Two Tables : It is the process of extracting meaningful information from the
Cartesian product by placing some conditions in the statement. It generates output by matching
each value of a column in both tables.
Eg  SELECT * FROM order_table, product WHERE p_code = code;
Output :-

Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 11 of 12

CLASS : XII I.T. (802)

+----------+--------+----------+------+------------+
| Order_No | P_Code | Sup_Code | Code | Name |
+----------+--------+----------+------+------------+
| 1 | P001 | S002 | P001 | Toothpaste |
| 2 | P002 | S002 | P002 | Shampoo |
+----------+--------+----------+------+------------+

Transaction
A transaction is a unit of work (update, delete, insert etc) that must be done in logical order and
successfully as a group or not done at all. Each transaction has a beginning and an end. In
MySQL, we have the following commands for transaction :
(i). START TRANSACTION : This statement has no clause. It is used to start a new transaction.
Syntax – START TRANSACTION;
(ii). COMMIT : It is used to save all changes made to the database during the transaction. It ends
the transaction also.
Syntax – MySQL>COMMIT; OR MySQL>COMMIT WORK;
(iii). ROLLBACK : It is used to cancel the entire transaction.
Syntax – MySQL>ROLLBACK; OR MySQL> ROLLBACK WORK;
(iv). SAVEPOINT : It is used to define a marker in transaction. It is useful in rolling back the
transaction till marker.
Syntax – SAVEPOINT <name>;
e.g. SAVEPOINT n1;
e.g. ROLLBACK TO SAVEPOINT n1;

Kumar Gourab (DAV PS, CRRC, Medical Road, Gaya) Page 12 of 12