Class 10 IT 402 Database Management System Notes

Obtain complete and simple-to-grasp notes of Database Management System (DBMS) for Class 10 IT Code 402, as per the current CBSE syllabus. The notes discuss all major topics such as database concepts, tables, records, fields, database types, DBMS vs RDBMS, primary keys, data types, and basic operations like insert, update, delete, and query. Best for board exam preparation, these notes will enable students to have a firm grip on DBMS and score better in both theory and practical exams. Download the PDF and begin preparing sensibly today!

Unit 3 – Database Management System

Data Base Management System

Data refers to raw facts and figures that have no meaning by themselves.
Examples: names, numbers, dates, marks, prices, etc.

Base means a collection or storehouse.
So, a database is a structured collection of data stored in an organized way.

Management means controlling, organizing, and handling data efficiently.
In DBMS, management refers to how the software organizes, stores, retrieves, and protects data.

System is a set of components that work together to perform a task.
In DBMS, the system includes software, tools, and processes that work together to manage the database.

Database Management System

A Database Management System (DBMS) is a software package containing computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop and manage databases for various applications.

A database is an integrated collection of data records, files, and other related objects. A DBMS enables multiple user applications to access the same database concurrently while ensuring data security and consistency.

Well-known DBMS software includes Oracle, IBM DB2, Microsoft SQL Server, Microsoft Access, PostgreSQL, MySQL, FoxPro, and SQLite.

A database model is basically used by the following users:

1. Developers: design and develop a database.
2. Administrators: keep track on database and check authorization to provide access to the users. (Admin) / DBA
3. End Users: are the group of people who really use the database program. For example, in a school, teachers and students are the end users as they use the database every day.

File Extensions:

• MS Access file – .accdb (Access Database)
• Open Office Base file: .odb (OpenOffice Database)

Different Element of a Table in Database

• Columns are referred as fields. A field describes a specific property of a record, that is why a field is also known as an attribute.
• Rows are referred as records. A single record is also known as tuple.
• A set of characters that represents a valid value is known as data or data value or data item.

Different types of Database Management System

Following are the different types of DBMS:

1. Relational Database Management System (MySQL)
2. Hierarchical Database Management System
3. Network Database Management System
4. Object-Oriented Database Management System

Microsoft Office vs Apache OpenOffice / Libre Office Features / Steps

Types of Data in Database

Data can be organized int two types:

1. Flat File
   A flat file is a simple data storage format in which data is stored in a single table consisting of rows and columns. It is suitable for storing a small amount of data and does not support relationships between tables. Example: MS Excel
2. Relational Database
   A relational database stores data using multiple tables that are connected through defined relationships (keys). It is used to store large amounts of data and supports efficient access and querying using Structured Query Language (SQL). Examples: MS Access, MySQL

Advantages / Features of Database

1. Reduces Data Redundancy: Data redundancy means storing the same data again and again. A database reduces redundancy by keeping information only once in a structured way. This saves storage and keeps data clean and organized.
2. Reduces Data Inconsistency: Inconsistency happens when the same information appears differently in different places. A database stores data in one place, so everyone sees the same and correct information, avoiding confusion or mistakes.
3. Increases Data Integrity: Data integrity means keeping data accurate, complete, and reliable. A database ensures that the information stored is correct and follows rules, so the data remains trustworthy.
4. Sharing of Data: A database allows multiple users to view and use the same data at the same time without affecting each other. This helps teams and departments work together easily.
5. Sharing of Resources: In a database system, different users can share the same tools, storage, and programs. This means there is no need to install separate software for every user, saving cost and effort.
6. Data Security: A database provides strong security features like passwords, permissions, and user roles. Only authorized people can access certain data, ensuring protection from misuse or unauthorized access.
7. Privacy: Privacy means keeping personal or sensitive information safe. A database allows control over who can see what data, ensuring that private information is only visible to the right people.
8. Backup & Recovery: A database automatically keeps backup copies of data. If data is lost due to a mistake or system failure, it can be quickly restored, making the system safe and reliable.

Database Server

A database server is responsible for storing, managing, and retrieving data from one or more databases. It handles requests from client computers or applications that want to access, update, or modify the data.

RDBMS (Relational Database Management System)

It is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in relational database is organized into tables. In a relational database each row is defined in the table is a record with a unique ID called the key.

Types of Keys in Database

1. Primary Key – A Primary Key is a field (column) that uniquely identifies each record in a table.
No two records can have the same primary key, and it cannot be empty (NULL).

Example: Student Table

StudentID	Name	Class
101	Riya	10
102	Aman	9

Here, StudentID is the Primary Key because each student has a unique ID.

2. Composite Primary Key – It is a combination of two or more columns used together to uniquely identify a record.

Example: Marks Table

StudentID	Subject	Marks
101	Math	88
101	English	92

3. Foreign Key – A Foreign Key is a column in one table that refers to the Primary Key of another table. It creates a relationship between two tables.

Example: Student Table
Primary Key → StudentID
Fee Table

FeeID	StudentID	Amount

Here, StudentID in Fee Table is a Foreign Key referencing the StudentID in Student Table.

4. Alternate Key – A Candidate Key that is not chosen as the primary key is called an Alternate Key.

Example: Employee Table

EmpID	Email	Phone

• EmpID, Email, Phone — all are unique
• If EmpID is chosen as Primary Key, then Email and Phone become Alternate Keys.

5. Candidate Key – A Candidate Key is any column (or set of columns) that can uniquely identify a record. A table can have multiple candidate keys, but only one becomes the primary key.

Example: Employee Table

EmpID	Email	Phone

• EmpID, Email, Phone — all are unique. All three are Candidate Keys. One of them becomes the Primary Key, and the rest become Alternate Keys.

Data Types in MY SQL

INT – Whole numbers (e.g., 10, 500, -20)
FLOAT – Numbers with decimals (less precision)
DOUBLE – Numbers with decimals (more precision)
CHAR(n) – Fixed-length text
VARCHAR(n) – Variable-length text
TEXT – Large amount of text
DATE – Stores date only (YYYY-MM-DD)
TIME – Stores time only (HH:MM:SS)
YEAR – Stores year (YYYY)
DATETIME – Stores date + time

SQL (Structured Query Language)

SQL is a special computer language used in Relational Database Management Systems (RDBMS) to work with data. With SQL, users can write queries to store, retrieve, update, and delete data in a database. It helps in managing large amounts of information easily and efficiently.

The SQL commands are of two types (DDL & DML):

1. Data Definition Language (DDL) – [CREATE, ALTER, DROP, TRUNCATE]
2. Data Manipulation Language (DML) – [INSERT, UPDATE, DELETE, MERGE]
3. Data Query Language (DQL) – [SELECT]
4. Data Control Language (DCL) – [GRANT, REVOKE]
5. Transaction Control Language (TCL) – [COMMIT, ROLLBACK, SAVEPOINT]

Data Definition Language (DDL)

DDL is the part of SQL used to define, create, and modify the structure of a database. It deals with tables, indexes, views, and other database objects. The commands that fall under this category are listed as follows:

1. CREATE – It is used to create a new database or table.

CREATE DATABASE

SYNTAX: CREATE DATABASE <DATABASE_NAME>;
EXAMPLE: CREATE DATABASE myschool_db;

USE DATABASE – To create a table USE Database command is required.

SYNTAX: USE <DATABASE_NAME>;
EXAMPLE: USE myschool_db;

CREATE TABLE

SYNTAX: CREATE TABLE table_name(column_name1 data_type1, column_name2 data_type2, ........ ,........);
EXAMPLE: CREATE TABLE employee(e_id int(8), e_name char(30));

Que: Write a command and query to create the structure of a table named “student1”.

Roll_no	Class	Name	Marks

Ans: CREATE TABLE Student1 (Roll_No INT, Class VARCHAR(3), Name VARCHAR(30), Marks INT);

2. ALTER – It is used to modify the structure of an existing database or table.

TO ADD COLUMN (FIELD)

SYNTAX: ALTER TABLE <Table_Name> ADD COLUMN <Column_Name> <Data_Type>;
EXAMPLE: ALTER TABLE employee ADD COLUMN e_loc char(50);

TO REMOVE/DROP COLUMN (FIELD)

SYNTAX: ALTER TABLE <Table_Name> DROP COLUMN <Column_Name>;
EXAMPLE: ALTER TABLE employee DROP COLUMN e_loc;

TO RENAME EXISTING DATABASE OR TABLE

SYNTAX: ALTER TABLE <Table_Name> RENAME TO <New_Table_Name>;
EXAMPLE: ALTER TABLE employee RENAME TO customers;

3. DROP – Deletes an existing database or table.

TO DROP TABLE

SYNTAX: DROP TABLE <Table_Name>;
EXAMPLE: DROP TABLE employee;

TO DROP DATABASE

SYNTAX: DROP DATABASE <Database_Name>;
EXAMPLE: DROP DATABASE myschool_db;

4. TRUNCATE – Remove all table records including allocated table spaces, but not the table itself.

SYNTAX: TRUNCATE TABLE <Table_Name>;
EXAMPLE: TRUNCATE TABLE employee;

Data Manipulation Language (DML)

DML is the part of SQL used to add, change, or remove data inside the database tables. It helps users manage the actual records stored in the database. The commands that fall under this category are listed as follows:

1. INSERT – The insert command is used to add one or more records to a table. There are two methods to use the INSERT command.

Method 1: 
SYNTAX: INSERT INTO <Table_Name> (Field 1, Field 2,….) VALUES (Value 1, Value 2,….);
EXAMPLE: INSERT INTO employee (‘e_id’, ‘e_name’,) VALUES (1, “Mukesh”);

Method 2: 
SYNTAX: INSERT INTO <Table_Name> VALUES (Value 1, Value 2,….);
EXAMPLE: INSERT INTO employee VALUES (1, “Mukesh”);

2. SELECT – It is used to retrieves or fetch the data from the table.

TO FETCH ENTIRE RECORD OF TABLE [* means ALL records]

SYNTAX: SELECT * FROM <Table_Name>;
EXAMPLE: SELECT * FROM employee;

TO FETCH RECORD OF SELECTED FIELDS FROM TABLE

SYNTAX: SELECT <Field1, Field2,…> FROM <Table_Name>;
EXAMPLE: SELECT e_name, e_id FROM <Table_Name>;

Que: Write a command and queries to create the records of a table named ‘Students’ with the following fields.

Roll_No	Name	Class	Marks
101	Ajay	X	765
107	Suraj	IX	865
109	Simran	X	766
103	Aman	IX	821
104	Naresh	IX	1

a. Write SQL commands to create the above given table. Set appropriate datatypes for the fields.
b. Write a query to display the details of all the students studying in class X.

Ans: (a)
CREATE TABLE Students (Roll_No INT NOT NULL, Name VARCHAR(50), Class CHAR(10), Marks INT);
INSERT INTO Students VALUES (101,“Ajay”,“X”,765);
INSERT INTO Students VALUES (107,“Suraj”,“IX”,865);
INSERT INTO Students VALUES (109,“Simran”,“X”,766);
INSERT INTO Students VALUES (103,“Aman”,“IX”,821);
INSERT INTO Students VALUES (104,“Naresh”,“IX”,1);
(b)
SELECT * FROM Students WHERE Class=“X”;

SELECT Command Clauses

• WHERE clause specifies a criteria about the rows to be retrieved.
• ORDER BY clause specifies an order (ascending/descending) in which the rows (records) are to be retrieved.

To retrieve all records:

SELECT * FROM <Table_Name>;

To retrieved records from specific fields:

SELECT <Field1, Field2,…> FROM <Table_Name>;

By using WHERE clause:

SELECT * FROM <Table_Name> WHERE <condition>;
SELECT Field1, Field2,… FROM <Table_Name> WHERE <condition>;

By using ORDER BY clause:

By default Ascending order
SELECT * FROM <Table_Name> ORDER BY <Field_Name>;
in Descending order
SELECT * FROM <Table_Name> ORDER BY <Field_Name> DESC;

3. UPDATE – Sometimes, you need to modify the existing records in the table. The UPDATE command of the SQL can be used for this purpose.

SYNTAX: UPDATE <Table_Name> SET <Column1=Value1, Column2=Value2,…> WHERE <condition>;
EXAMPLE: UPDATE employee SET Last_Name=‘Sharma’ WHERE E_ID=101;

4. DELETE – It is used to remove the existing records from a table.

SYNTAX: DELETE FROM <Table_Name> WHERE <condition>;
EXAMPLE: DELETE FROM employee WHERE E_ID=101;

Important: CREATE TABLE BY ASSIGNING PRIMARY KEY

EXAMPLE: CREATE TABLE Employee1 (Emp_ID INT NOT NULL PRIMARY KEY, ENAME VARCHAR(20), AGE INT(5));

MYSQL Functions

MySQL has many built-in functions. Some of them are as: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )

COUNT( ) – The COUNT() function returns the number of rows that matches a specified criterion.

SELECT COUNT(*) FROM <table_name> WHERE <condition>;
SELECT COUNT(<column_name>) FROM <table_name> WHERE <condition>;

SUM( ) – The SUM() function returns the total sum of a numeric column.

SELECT SUM(<column_name>) FROM <table_name> WHERE <condition>;

AVG( ) – The AVG() function returns the average value of a numeric column.

SELECT AVG(<column_name>) FROM <table_name> WHERE <condition>;

MAX( ) – The MAX() function returns the largest value of the selected column.

SELECT MAX(<column_name>) FROM <table_name> WHERE <condition>;

MIN( ) – The MIN() function returns the smallest value of the selected column.

SELECT MIN(<column_name>) FROM <table_name> WHERE <condition>;

Fetch numbers in a range using BETWEEN

SELECT * FROM Students WHERE RollNo BETWEEN 1 AND 50;
This query will display all student records where RollNo is from 1 to 50

SELECT * FROM Students WHERE Marks BETWEEN 50 AND 80;
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-03-31';
SELECT * FROM Products WHERE Price>= 100 AND Price<= 500;
SELECT * FROM Employees WHERE Age BETWEEN 25 AND 40;

SQL query using a calculated field

SELECT ProductID, ProductName, Quantity, Price,
Quantity * Price AS TotalAmount
FROM Orders;

Here: Quantity and Price are existing fields.
Quantity * Price is a calculated value.
AS TotalAmount gives the calculated field a name.

Wildcard Characters in SQL

• % – represent zero or more characters
  • Example: bl% finds bl, black, blue, and blob
• _ – Represents a single character
  • Example: h_t finds hot, hat, and hit
• [ ] – Represents any single character within the brackets
  • Example: h[oa]t finds hot and hat, not hit or hut
• ^ – Represents any character not in the brackets
  • Example: h[^oa]t finds hit, hut not hot and hat
• Hyphen (-) – Represents a range of characters
  • Example: c[a-d]t finds cat and cbt, cct, cdt

GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

GROUP BY Syntax:
SELECT FUNCTION(<Column Name>) FROM <table_name> WHERE <condition> GROUP BY <column_name>;

Example:
SELECT COUNT(CustomerID), country FROM customers GROUP BY country;

MS Access (Microsoft Access)

MS Access is a database management software developed by Microsoft. It allows users to create, store, manage, and retrieve data easily. It combines a graphical user interface with the powerful Microsoft database engine, making it suitable for small to medium-sized applications.

It is widely used in schools, offices, and small businesses because it is easy to use and does not require advanced technical knowledge.

With MS Access, users can create:

• Tables (to store data)
• Forms (to enter data) – Forms are user-friendly screens used to enter, edit, or view individual record or data in a database. They look like simple input boxes and make data entry easier and safer.
  Example: A form can have fields like Name, Class, Roll Number where you type student details.
• Queries (to search and filter data) – Queries are questions asked to the database to get specific information. They help us search, filter, and find only the data we need.
  Example: If you want to find all students who scored more than 80 marks, you create a query.
• Reports (to display data in a readable or printable format / consolidated data) – Reports are printed or display-ready outputs that present data in a neat and readable format. They are used to summarize and showcase information clearly.
  Example: A report can show a list of all students with their marks in a proper table format.

Different Views available in MS Access

In MS Access, different views are available to create, edit, and work with database objects like tables, forms, queries, and reports. Here are the main views are:

Datasheet View

Datasheet View shows the data in a table in the form of rows and columns, just like an Excel sheet.
You can use this view to enter, edit, and see data directly in the table. It is mainly used when you want to work with the actual data stored in the database.

Design View

Design View is used to create or change the structure of a table. In this view, you can set field names, data types, primary keys, and rules for the data. It does not show actual data; instead, it shows how the table is designed.

Types of Relationships in RDBMS

1. One-to-One (1:1) Relationship

Definition:
A one-to-one relationship occurs when one record in a table is related to only one record in another table, and vice versa.

• Table: Students

StudentID	Name
1	Rahul
2	Priya

• Table: StudentDetails

StudentID	Address
1	Delhi
2	Mumbai

Each student has exactly one address, and each address belongs to exactly one student.

2. One-to-Many (1:N) Relationship

A one-to-many relationship occurs when one record in a table is related to multiple records in another table, but those multiple records are related to only one record in the first table.

• Table: Teachers

TeacherID	Name
1	Mr. Sharma
2	Ms. Anjali

• Table: Students

TeacherID	StudentID	Name
1	1	Rahul
1	2	Priya
2	3	Aman

• Mr. Sharma teaches Rahul and Priya → 1 teacher → many students.
• Ms. Anjali teaches Aman → 1 teacher → 1 student.

3. Many-to-Many (M:N) Relationship

A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. This is usually implemented using a junction or bridge table.

• Table: Students

StudentID	Name
1	Rahul
2	Priya

• Table: Courses

CourseID	Address
101	Math
102	Science

• Table: StudentCourses (Junction or Bridge Table)

StudentID	CourseID
1	101
1	102
2	101

Explanation:

• Rahul is enrolled in Math and Science.
• Priya is enrolled in Math.
• Many students can take many courses → many-to-many relationship.