Database Management System

2.1 Definition, Importance, and Application of Database

What Is a Database?

A database is an organized collection of related data, stored electronically so it can be easily accessed, managed, and updated. Instead of keeping information scattered across paper files or separate documents, a database keeps everything structured and searchable in one place.

Importance of a Database

• Stores large amounts of data in an organized, structured way
• Makes it fast and easy to search, sort, and retrieve specific information
• Reduces duplicate or repeated data through proper structuring
• Allows multiple users to access and update data at the same time
• Keeps data more secure, since access can be restricted and controlled

Applications of Databases

• Schools: storing student records, grades, and attendance
• Banks: storing account details, balances, and transaction history
• Hospitals: storing patient records and appointment schedules
• Online shopping: storing product listings, prices, and customer orders
• Social media: storing user profiles, posts, and connections between users

2.2 Data, Information, Database, DBMS

Data: raw, unprocessed facts and figures that have no meaning on their own, such as a list of numbers or names. For example, “15”, “Ram”, and “Class 10” are just data by themselves.

Information: data that has been processed, organized, or given context so it becomes meaningful and useful. For example, “Ram is 15 years old and studies in Class 10” is information because it connects the raw data into something understandable.

Difference Between Data and Information

Database: an organized collection of related data, stored in a structured way so it can be efficiently accessed and managed, as explained in the previous section.

DBMS (Database Management System)

Software used to create, manage, and interact with databases. A DBMS lets users store, retrieve, update, and delete data while also handling security, backups, and allowing multiple users to access data safely at once. E.g MySQL, Oracle Database, Microsoft SQL Server, SQLite

Features of DBMS

1. Less redundancy. Data like a student’s address is stored once, not in the library, exam, and fee files separately.
2. Consistent data. Update the address once, and it’s correct everywhere.
3. Data sharing. Many users can work on the same database at the same time.
4. Security. The administrator decides who can view or edit what. A teacher can read marks, but only the exam officer can change them.
5. Integrity. Rules keep bad data out, like marks only between 0 and 100 or no repeated roll numbers.
6. Backup and recovery. After a crash or power cut, the DBMS restores the data to its last correct state.
7. Data independence. You can change how data is stored, such as adding a column, without rewriting every program.
8. Fast queries. With SQL, “show students who scored above 80” takes seconds.
9. Concurrency control. If two people edit the same record at once, one update won’t overwrite the other, like two people booking the last bus seat.
10. Transactions (ACID). A set of steps either finishes fully or not at all. If the deposit fails, the withdrawal is cancelled too.

Relational Database Management System (RDBMS)

Relational Database Management System is a type of DBMS that stores data in tables made up of rows and columns. Each table is linked to others through common fields, which makes it easy to organize and manage related information. MySQL is a popular example of an RDBMS.  RDBMS is widely used today in websites, apps, banking systems, schools, and businesses to handle large amounts of data in an organized and secure way.

2.3 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.

2.4 Table

spreadsheet. Each table covers one subject, such as students, books, or products. Columns are the fields (name, age, price), and each row is one complete record. Tables are the basic building block of a database.

Here are the key features of a table:

1. Rows and columns. Data sits in a grid. Columns are fields, rows are records.
2. One subject per table. A student table holds only student details.
3. Unique records. A primary key, like a roll number, identifies each row.
4. Fixed data types. Each column accepts one kind of data, such as text, number, or date.
5. Easy to search and sort. You can find, filter, or arrange records quickly.
6. Linked to other tables. Tables connect through keys, like a student table linked to a results table.

A field is also known as a column and contains a single type of data under a specific category. It is a part of a database table that holds one piece of information about an entity.

For example, in a student database, fields might include Name, Roll Number, Class, and Date of Birth. Each of these fields stores a specific type of data for all students in the database.

In this table:

a. Student_ID, Name, Class, and DateOfBirth are the Fields.
b. Each field holds a specific type of data: Student ID holds numbers, Name holds text, Age holds numbers, and Date of Birth holds dates.

Records

A record, also called a row, is the complete set of data about one item in a table. It holds a value for every field (column), like one student, one employee, or one product.

Example: In a student table, each record stores one student’s Name, Roll Number, Class, and Date of Birth.

In this table:

a. The whole row (503, Deepa Khadka, 9, 2011-11-05) is one record.
b. It gives the complete information about one student, Deepa Khadka.

2.5 Keys: Primary Key and Foreign Key

In a database, keys are special fields (columns) used to uniquely identify data and establish links between tables. They help to keep the data organized, accurate, and connected.

1. Primary Key

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. This cannot be duplicated and cannot be empty (NULL).

           Features of Primary Key

1. Uniquely identifies each record
2. Cannot contain duplicate values
3. Cannot be NULL
4. Only one primary key is allowed per table

Example:

Here, StudentID is the primary key because it uniquely identifies each student.

2. Foreign Key

A Foreign Key is a field in one table that refers to the primary key in another table. It creates a relationship between two tables and helps ensure that the linked data is valid. A foreign key is also known as a linking pin between two tables.

The above diagram shows the relationship between a Bank and a Car. In the Car table, FinancedBy is a foreign key (FK) that connects to the BankID (Primary Key) in the Bank table.

Features of Foreign Key

1. Creates a relationship between tables.
2. Refersto the primary key of another table.
3. Enforces relationships.
4. Ensures data integrity.
5. Consists of one or more columns.

Example:

Table: Students

Table: Library

• In the Library table, StudentID is a Foreign Key that refers to the StudentID in the Students table.
• This ensures that only valid student IDs can be entered in the Library table.

3. Composite Key

A Composite Key is made by combining two or more fields to uniquely identify a record. This is used when a single field alone cannot ensure uniqueness.

Example:

1. Here, neither StudentID nor Subject alone can uniquely identify a row.
2. But the combination of StudentID + Subject can, making it a Composite Key.

Types of Relationship

A relationship is a link between two tables, usually made through a common field such as a key. It lets you store data in separate tables and still pull it together when you need it. It also cuts down on repeated data and keeps records accurate.

There are three types:

1. One-to-one (1: 1)
2. One-to-many (1: M)
3. Many-to-many (M: M)

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

Each record in one table matches only one record in the other table. It is not common, but it helps when you want to split closely related details into two tables.

Example: A person has only one passport, and each passport belongs to only one person.

2. One-to-many (1: M)

One record in the first table can match many records in the second table, but each record in the second table matches only one record in the first. This is the most common type.

Example: A teacher can teach many classes, but each class has only one teacher.

3. Many-to-many (M: M)

Many records in one table can match many records in the other. This needs a third table, called a junction table, to connect the two.

Example: A student can enroll in many courses, and each course can have many students.

2.6 Introduction to MySQL

MySQL is a free, open-source relational database management system. It uses SQL (Structured Query Language) to store, manage, and organize data. It was first developed by a Swedish company called MySQL AB and is now maintained by Oracle Corporation. It is popular in web development, software applications, and data-driven systems because it is fast, reliable, and free to use.

Features of MySQL

1. Free and open source. Anyone can download and use it.
2. Fast and reliable. Handles large data quickly.
3. Easy to use. Simple SQL commands, plus tools like phpMyAdmin.
4. Cross-platform. Runs on Windows, Linux, and macOS.
5. Secure. Passwords and user privileges control access.
6. Scalable. Works for small projects and large websites.
7. Multi-user. Many users can work at the same time.

2.6.1 Table in MySQL

A table is the basic structure MySQL uses to store data. It arranges information in rows and columns, where each row is a record and each column is a field. All the data in a database lives in one or more tables, and you can create, modify, or delete them using SQL commands.

Features of a table:

i. Stores data in rows and columns.
ii. Each column has a specific data type (number, text, date).
iii. Each row is called a record.
iv. Each column is called a field.
v. Tables can be linked using keys (Primary Key and Foreign Key).
vi. Tables can be created, modified, or deleted using SQL queries.

Relational Table

A relational table is a table in a relational database that represents one type of entity, like students, teachers, or courses. What makes it relational is that it can be connected to other tables using a Primary Key and a Foreign Key. Each row is a unique record, and the links between tables reduce repeated data.

Example:

i. A students table with student details.
ii. A courses table with course details.
iii. A student_courses table that links each student to the courses they enrol in.

2.6.2 Structured Query Language (SQL)

A query is a question or request sent to a database to retrieve, insert, update, or delete data. Queries are written in SQL (Structured Query Language), which IBM developed in the 1970s for managing data in relational databases. SQL commands are the basic building blocks for communicating with a DBMS.

For example, to find all students in Class 10, you write a query that shows only those records. Queries let you filter, sort, and search large amounts of data quickly, and update or delete records without checking each one by hand.

SQL is pronounced “see-quell” and has 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.

MySQL Constraints 

Constraints are rules applied to table columns to control what data can be stored. They keep data valid, consistent, and accurate by blocking duplicates, missing values, and incorrect entries.

Types of constraints:

Importance of constraints:

• Keep data accurate and consistent.
• Prevent duplicate or incorrect entries.
• Build relationships between tables.
• Enforce rules like “marks must be between 0 and 100.”

2.7 Data Definition Language (DDL)

DDL is the part of SQL used to define and manage the structure of database objects such as tables, schemas, indexes, and views. It deals with how data is stored and organized, not with the data itself. DDL commands save their changes automatically (auto-commit), so they cannot be rolled back.

Common DDL commands:

1. CREATE query

CREATE makes new database objects. Before creating tables, you first create a database to hold them, then select it with the USE command.

Create a database:
CREATE DATABASE database_name;

Here, database_name is the name you want to give the new database.

Create a table:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
);

Here, column1, column2, and column3 are the table’s columns, and each datatype is the kind of data that column holds.

2. ALTER Query

The ALTER query in SQL is used to modify the structure of an existing table. It allows you to:

Add a new column

Syntax : 
ALTER TABLE  table_name
ADD  column_name  data_type;

To delete/drop a column

Syntax: 
ALTER TABLE  table_name
DROP COLUMN  column_name;

To modify of a column

Syntax: 
ALTER TABLE table_name
MODIFY COLUMN  column_name  new_data_type;

Rename a column

Syntax:
ALTER TABLE table_name
CHANGE old_column_name   new_column_name   data_type;

To remove constraints

Syntax:
ALTER TABLE  table_name
DROP CONSTRAINT  constraint_name;

3. DROP Query

The DROP query in SQL is used to permanently delete a database object such as a table, database, or view from the database system. Once dropped, the object and all its data cannot be recovered. Use the DROP command carefully!

Syntax: To drop a Database 
DROP DATABASE database_name;
Syntax: To drop a table 
DROP TABLE  table_name;

2.8 Data Manipulation Language (DML)

DML is the part of SQL used to work with the data stored in tables. It lets you insert, retrieve, update, and delete records. Unlike DDL, which deals with structure, DML works on the content of the table and doesn’t change its structure. DML changes can be rolled back and are not permanent until you use COMMIT.

 1. INSERT query

INSERT adds new records (rows) to a table. You can add one row or several at once by giving a value for each column.

Syntax 1: name the columns.

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Syntax 2: skip the column names.

INSERT INTO table_name
VALUES (value1, value2, value3);

SELECT Query

A SELECT query in SQL is used to retrieve data from a database table. It helps you view specific information you need, such as all rows, selected columns,

Syntax:  
SELECT  column(s)_name

3. UPDATE Query

An UPDATE query in SQL is used to modify existing records (rows) within a database table. Think of it as a way to change or correct information that’s already in your database. You can update specific columns in one or more rows based on certain conditions.

Syntax: 
UPDATE  table_name
SET  column1 = value1, column2 = value2, …
WHERE condition;

4. DELETE Query

A DELETE query in SQL is used to remove existing records (rows) from a database table. This functionality enables the removal of information that is deemed unnecessary or outdated. You can delete specific rows based on a condition or remove all rows from a table.

Syntax:
DELETE FROM  table_name
WHERE  condition;

WHERE and LIKE Clauses

Using the SELECT query, we can retrieve or extract data from the database based on specific criteria by using the WHERE and LIKE clauses.

Where Clauses

The WHERE clause in MySQL is used to filter records and retrieve only those rows that meet specific conditions. It helps in selecting data based on a given rule rather than displaying all records from a table. Without a WHERE clause, SQL queries would return the entire table.

Like Clauses

The LIKE clause is used in SQL to search for a specified pattern in a column. It is often used with the WHERE clause to filter records based on partial matches instead of exact values.

LIKE works with wildcard characters:

• % (percent sign): Represents zero, one, or multiple characters.
• _ (underscore): Represents a single character.

Wildcard characters in MySQL are special symbols used with the LIKE clause to represent unknown parts of a string. They help in searching for data when you don’t know the exact value but know a pattern.

2.8.5 Report 

A report is the organized result of a SQL query that pulls specific data from one or more tables and shows it in a readable form. It helps users understand data, analyze it, and make decisions.

MySQL can produce basic reports directly through queries, but it can’t design charts or printable layouts by itself. For those, you connect it to tools like Power BI, Tableau, or Looker Studio.

Types of reports you can make with queries:

i. Summary reports: show totals, like total sales or number of students.
ii. Detail reports: list full records, like all students with their marks.
iii. Grouped reports: group data by category, like class, department, or city.
iv. Filtered reports: show only records that meet a condition, like students above 80%.
v. Aggregate reports: use functions like SUM, AVG, and COUNT to summarize data.

Features of reports in MySQL:

i. Organized presentation of data
ii. Data filtering
iii. Summarization
iv. Combining data from multiple tables
v. Timely access
vi. Easy export

Computerkite

91 Posts Contributed

View all posts by Computerkite

Published July 2026. Last verified and updated: October 2026.