(Grade X) Chapter 2 : Database Management System
Data: Data are the raw facts and figure which are isolated and uninterrupted generally represented by letter and numbers. Data undergoes processing. E.g. 16, John, 10
Information: The collection of meaningful result obtained after processing raw data. They are generated after processing.
E.g. John is 16 years old and he reads in class 10.
Database:
The systematic and scientific collection of data that can access quickly whenever required is called database.
It is the organized way of collecting and keeping data and information.
E.g. Dictionary, telephone directory, marks ledger, attendance register.
DBMS:
DBMS stands for Database Management System which is the collection of software that is used to manage database i.e. storing, manipulating and retrieving data systematically and scientifically.
It is a software used to store, process and access data and information whenever required.
Example- i. MsAccess ii.Oracle iii. Foxpro iv.MsSql
v. MySql vi. Dbase vii. Delphi viii.Sybase ix. Foxbase x. IBM DB2
Advantages of DBMS
Prevents data redundancy
DBMS avoids storing the same data multiple times. It organizes data properly so duplication is reduced. This makes data more consistent and saves storage space.
Faster operation of data
DBMS allows quick access, updating, and retrieval of data. It uses efficient methods to process queries faster. This improves overall system performance.
Data security and privacy
DBMS protects data from unauthorized access. Only allowed users can view or change the data.
Recovery and backup
DBMS can recover data if there is a system failure. It regularly keeps backups of important data. This ensures that data is not lost permanently.
Data integrity
DBMS ensures that data remains accurate and consistent. It prevents incorrect or invalid data entry. Rules and constraints help maintain data quality.
Disadvantages of DBMS
Increases cost
DBMS software and hardware can be expensive. It also requires skilled people to manage it. This increases overall cost for organizations.
Database failure
If the database system fails, all data operations may stop. This can affect the entire organization. Recovery may take time and cause delays.
Frequent upgrade
DBMS needs regular updates to stay secure and efficient. Upgrading systems can be costly and time-consuming. It may also require training for users.
Highly complex
DBMS systems are complicated to design and manage. Users need proper training to use them effectively. Small mistakes can lead to big issues.
Huge size
DBMS requires a large amount of storage space. It stores lots of data, software, and logs. This makes the system heavy and resource-consuming.
Page number-50 Difference between Manual Database and Electronic Database
Differentiate between database and DBMS with examples.
Relational Database Management System (RDBMS)
RDBMS stands for Relational Database Management System. It is a type of database management system that stores data in the form of related tables (i.e., rows and columns). Each table (also called a relation) contains records (rows) and fields (columns), and relationships can be established between the tables using keys (primary and foreign).
Features of RDBMS:
Data is stored in tables.
Relationships between data are maintained.
Supports SQL (Structured Query Language).
Enforces data integrity and constraints.
Examples: Microsoft Access, MySQL, Oracle, SQL Server.
Differentiate between DBMS and RDBMS with examples.
Components of Database
Table
A table is where data is stored in a database. It is organized in rows and columns, similar to a spreadsheet. Each table stores information about one specific topic (like students or employees).
Column
Each column in a table is known as field which gives the smallest unit of information.
The field in the database is topic name or heading under which similar kind of information is stored.
Field is also known as attributes.
In the following database table of students, Symbol number, Name, Class and address is field.
|
Symbol number |
Name |
Class |
Address |
|
4786 |
Sworup Shrestha |
11 |
Kathmandu |
|
3256 |
Nabiska Shakya |
10 |
Lalitpur |
Row
Each row in a table is known as record which gives the detail information about person, place or any object.
Records are also known as tuples.
Each row represents a complete record of specific data item and holds different data within the same structure.
In the above database table of students 4786, Sworup, 10, Kathmandu, 3256, Nabiska Shakya, 10, Lalitpur is record.
Types of keys
Primary Key
A primary key is a column (or field) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it cannot be empty. For example, a student ID.
Foreign Key
A foreign key is a column that links one table to another table. It refers to the primary key of another table. This helps create relationships between tables.
Composite Key
A composite key is made of two or more columns used together to uniquely identify a row. It is used when a single column is not enough to ensure uniqueness. For example, a combination of student ID and course ID.
Primary Key
Example (Students Table)
|
StudentID (Primary Key) |
Name |
Age |
|
101 |
Ram |
20 |
|
102 |
Sita |
21 |
|
103 |
Hari |
19 |
Foreign Key
Example Students Table
(Parent Table)
|
StudentID (Primary Key) |
Name |
|
101 |
Ram |
|
102 |
Sita |
Courses Table
(Child Table)
Composite Key
Example
(Enrollment Table)
|
StudentID |
CourseID |
(Composite Key) |
|
101 |
C1 |
101 + C1 |
|
101 |
C2 |
101 + C2 |
|
102 |
C1 |
102 + C1 |
Primary Key (Example)
Table: Students
StudentID (Primary Key), Name, Age
Each student has a unique StudentID (like 101, 102). No two students can have the same ID.
• Foreign Key (Example)
Table 1: Students
StudentID (Primary Key), Name
Table 2: Courses
CourseID, StudentID (Foreign Key)
Here, StudentID in the Courses table refers to StudentID in the Students table. It connects students to their courses.
• Composite Key (Example)
Table: Enrollment
StudentID, CourseID (together form Composite Key)
A student can take many courses, and each course has many students. So, StudentID + CourseID together uniquely identify each record.
Data types
1. Numeric Data Types
Used for storing numbers.
2. Character (Text) Data Types
Used for storing letters, words, or text.
3. Date and Time Data Types
Used for storing dates and times.
4. Boolean Data Type
Used for storing true/false values.
Relationship
Database relationships are fundamental concepts in relational database design. They allow data to be stored efficiently, reduce redundancy, improve data integrity, and enable accurate retrieval of information. In a database, a relationship is a logical connection between two or more tables, typically based on a common field such as a key. These relationships make it possible to link data stored in separate tables and access them together in a meaningful and organized way. There are three types of relationship in database they are:
1. One-to-one ( 1 : 1 )
2. One-to-many ( 1 : M )
3. Many-to-many ( M : M )
1. One-to-One (1:1)
One Person → One Passport
One Student → One Library Card
One Employee → One ID Card
One Teacher → Many Students
One Customer → Many Orders
One Department → Many Employees
3. Many-to-One (M:1)
Many Students → One Teacher
Many Orders → One Customer
Many Employees → One Department
4. Many-to-Many (M:M)
Many Students ↔ Many Subjects
Many Doctors ↔ Many Patients
Many Authors ↔ Many Books
Types of Relationship
1. One-to-one relationship: In a one-to-one relationship, each record in one table is connected to only one record in another table. This type of relationship is not very common, but it is useful when we want to separate closely related information into two different tables.
Example: A person can have only one passport, and each passport is issued to only one person.
2. One-to-many relationship:
In a one-to-many relationship, a single record in one table is linked to multiple records in another table. However, each record in the second table is connected to only one record in the first table.
This is the most common type of relationship in databases. It is useful when one item is related to several items of another type.
Example: A teacher may teach many classes, but each class is taught by only one teacher.
Many-to-many relationship:
In a many-to-many relationship, multiple records in one table can be related to multiple records in another table. This type of relationship requires a third table, called a junction table, to link the records from both tables.
Example: A student can enroll in many courses, and each course can have many students.
Introduction to MySQL:
In today’s digital world, data is everywhere — from school records to online shopping and social media. To manage this data, powerful tools are needed. One of the most popular tools for working with data is MySQL, an open-source database management system used all over the world.
MySQL is a popular relational database management system (RDBMS) that uses SQL (Structured Query Language) to store, manage, and organize data. Originally developed by a Swedish company called MySQL AB, it is now maintained by Oracle Corporation. MySQL is widely used in web development, software applications, and data-driven systems due to its speed, reliability, and free availability. It allows users to interact with databases using SQL queries without needing to understand the full internal structure of the database. SQL provides statements for defining, manipulating, and controlling data. A query is a command sent to the database to retrieve, insert, update, or delete data. Today, MySQL is commonly used in websites, schools, banks, offices, and mobile applications. Learning its basics helps students understand how data is stored, searched, and managed in real-world systems.
Features of MySQL
i. Simple language
ii. Data retrieval
iii. Data manipulation
iv. Data definition
v. Multiple users
vi. Security features
vii. Used in modern applications
Table in MySQL
In MySQL, a table is a basic structure used to store data in a database. It organizes information into rows and columns, where each row represents a record and each column represents a field. Tables make it easy to manage and retrieve related data efficiently. All the data in a database is stored in one or more tables. You can create, modify, or delete tables using SQL commands.
Features of a table:
i. Stores data in rows and columns format.
ii. Each column has a specific data type (e.g., number, text, date).
iii. Each row in a table is called a record.
iv. Each column in a table is called a field.
v. Tables can be linked with each other using keys (like Primary Key and Foreign Key).
vi. Tables can be created, modified, or deleted using SQL queries.
Structured Query Language (SQL)
Simply, a query is a question to the database. SQL commands are the fundamental building blocks for communicating with a database management system (DBMS). Query is a request made to a database to retrieve, insert, update, or delete data. Queries are written using SQL (Structured Query Language), which was developed by IBM in the 1970s to manage data in relational databases. With the help of queries, users can interact with the database to get meaningful information based on specific conditions.
For example, if you want to find all students in Class 10 from your database, you can write a query to display only those records. Queries allow users to filter, sort, and search large amounts of data quickly and accurately. They also help in updating or removing data without manually searching each record. SQL queries can be simple or complex, depending on the need. Overall, queries are the main way users communicate with a database system to perform useful tasks.
SQL (Structured Query Language) pronounced as “See”-“quell” is made of three
sub languages DDL, DML and DCL.
Features of a Query
i. Retrieves specific data from tables.
ii. Adds, updates, or deletes records
iii. Filters and sorts data easily
iv. Useful for reports and analysis
Types of Queries
a) SELECT Query (Read Data)
Used to retrieve data from a table.
SELECT name FROM students;
Shows only names.
b) UPDATE Query (Modify Data)
Used to change existing data.
UPDATE students
SET age = 21
WHERE id = 1;
Changes age of student with ID 1.
c) DELETE Query (Remove Data)
Used to delete data from a table.
DELETE FROM students
WHERE id = 2;
Deletes student with ID 2.
SQL
SQL (Structured Query Language) is the language used to communicate with databases. It helps us to :
Create tables
Insert data
Update data
Delete data
DDL (Data Definition Language)
DDL is used to define or change database structure. Common DDL Commands are as follows:
CREATE → create table
ALTER → modify table
DROP → delete table
Example:
CREATE TABLE students (
id INT,
name VARCHAR(50)
);
DML (Data Manipulation Language)
DML is used to work with data inside tables. Common DML Commands are as follows:
SELECT
INSERT
UPDATE
DELETE
Example:
INSERT INTO students VALUES (1, 'Ram');
Difference between DDL and DML
SQL Constraints
(Page number69/70 --> table)
Constraints are rules that ensure correct and valid data.
Common Constraints:
NOT NULL → value cannot be empty
UNIQUE → no duplicate values
PRIMARY KEY → unique identifier
FOREIGN KEY → links tables
DEFAULT → sets default value
Page number 68 syntax and example
example-1
(a)Create a database named ‘student’
Ans: CREATE DATABASE student;
(b) Get inside the database.
Ans: USE student;
(c) Create a table named 'employee' with following fields.
Employee_id—int primary key not null auto increment
Employee_name--varchar(100)
Ans: CREATE TABLE employee (
employee_id int primary key not null auto_increment,
employee_name varchar(100)
);
(d) Delete the table.
Ans: DROP TABLE employee;
(e) Know whether that deleted table exists or not.
Ans: SHOW TABLES;
(f) Delete the database which you have created(student).
Ans: DROP DATABASE student;
(g) Know whether that deleted database exists or not.
Ans: SHOW DATABASES;
example-2
a. Create a database named ‘student’.
Ans: CREATE DATABASE student;
b. Create a table ‘student’ with following fields.
student_id--------------integer not null primary key auto increment
student_name---------varchar(100)
student_address-------varchar(100)
student_grade----------integer(int)
ANS; CREATE TABLE student (
student_id int not null primary key auto_increment,
student_name varchar(100),
student_address varchar(100),
student_grade int );
c. Insert any 6 records using ‘insert’ and ‘values’ command.
ANS: INSERT INTO student (student_name,student_address,student_grade) VALUES
('RAM SHRESTHA','KATHMANDU',12),
('RAJ SHARMA','BANEPA',12),
('SHYAM ADHIKARI','PATAN',12),
('ANJANA SHA','BOUDHA',11),
('SUBAM KARKI','DURBAR MARGA',12),
('ALINA LAMA ','BANASTHALI',10);
d. Display all the records of all fields.(Use select *)
ANS: SELECT * FROM STUDENT;
e. Display all the records of fields student_id and student_name.
Ans: SELECT student_id,student_name from student;
f. Display records of students whose name starts with letter ‘a’.
Ans: SELECT * from student where student_name like 'a%';
g. Display records of students whose name ends at letter ‘y’.
Ans: SELECT * from student where student_name like '%y';
h. Display all the records of students whose grade is 12. Use ‘where’ command.
Ans: SELECT * from student where student_grade=12;
i. Display all the records of students whose grade is 12 and who are from address “Kathmandu”. Use ‘and’ operator.
Ans: SELECT * from student where student_grade=12 and student_address='Kathmandu';
j. Update the student name with any other name who has id 1.
Ans: UPDATE student set student_name='Unnat Sapkota' where student_id=1;
k. Delete the record of student whose id is 3.
Ans: DELETE from student where student_id=3;
l. Display the records of students in sorted order using field ‘student_name’. Use ‘order by field name asc/desc’.
Ans: for ascending : SELECT * from student order by student_name asc;
for descending : SELECT * from student order by student_name desc;
m. Alter the table with following fields. Add one more field student_section---varchar(100). [Use alter and add command]
Ans: ALTER TABLE student add student_section varchar(100);
n. Change the size of field student_address--varchar(200) [Use alter and modify command]
Ans: ALTER TABLE student modify student_address varchar(200);
example-3 a. Create a database named ‘employees’.
Ans: CREATE DATABASE employees;
b. Create a table ‘employee’ with following fields.
emp_id--------------integer not null primary key auto increment
emp_name---------varchar(100)
emp_position-------varchar(100)
emp_salary----------float
Ans: CREATE TABLE employee (
emp_id int not null primary key auto_increment,
emp_name varchar(100),
emp_position varchar(100),
emp_salary float );
c. Insert any 6 records using ‘insert’ and ‘values’ command.
Ans: INSERT into employee (emp_name,emp_position,emp_salary) values
('amit ray','officer',43000),
('anjana sha','office assistant',30000),
('rajnis bhandari','security guard',25000),
('shubha karki','teacher',32000),
('kris sharma','cleaner',45000),
('sonam chaudary','plumber',15000);
d. Display all the records of all fields.(Use select *)
Ans: SELECT* from employee;
e. Display all the records of fields’ emp_id and emp_name.
Ans: SELECT emp_id,emp_name from employee;
f. Display records of employees whose name starts with letter ‘ab’.
Ans: SELECT * from employee where emp_name like 'ab%';
g. Display records of employees whose name ends at letter ‘y’ and starts from ‘b’.
Ans: SELECT * from employee where emp_name like 'b%y';
h. Display all the records of employees whose position is ‘engineer’.
Ans: SELECT * from employee where emp_position='engineer';
i. Display all the records of employees whose salary is in range 30000-45000.
Ans: SELECT * from employee where emp_salary>=30000 and emp_salary<=45000;
j. Update the employees name with any other name who has id 1.
Ans: UPDATE employee set emp_name='asia' where emp_id=1 ;
k. Delete the record of employees whose id is 3.
Ans: DELETE from employee where emp_id=3;
l. Display the records of employees in sorted order. Use ‘order by field name asc/desc’.
Ans: for ascending: SELECT * from employee order by emp_name asc;
for descending: SELECT * from employee order by emp_name desc;
m. Alter the table with following fields. Add one more field employees_address ---varchar(100). [insert after name field]
Ans: ALTER TABLE employee ADD COLUMN employees_address VARCHAR(100) AFTER emp_name;
n. Change the size of field emp _position--varchar(200) [Use alter and modify command]
Ans: alter table employee -> modify emp_position varchar(200);
o.Find the maximum and minimum salary in database.
Ans: for maximum salary: SELECT max(emp_salary) from employee;
for minimum salary: SELECT min(emp_salary) from employee;
p.Find the average salary distribution in database.
Ans: SELECT avg(emp_salary) from employee;