Database Management System

A librarian who finds one book among thousands in seconds, a school office that prints every student's marksheet in an afternoon, and a bank that knows your balance the moment you pay with Fonepay all rely on the same idea: data kept in an organised, searchable form. This unit explains what a database is, how a Database Management System (DBMS) looks after it, and how to build and use one in MySQL with SQL commands. Every query in this chapter was run on MySQL 8.4, and the table printed under it is exactly what MySQL returned.

2.1 Introduction to database: data, information, database and DBMS

Databases work quietly behind almost everything you do online.

Data and information

The marks 80, 85 and 90 are data. "Binay's average in the three terminal exams is 85, so he improved by 10 marks" is information: the data has been processed (averaged and compared) and now means something.

Database

Before computers, records were kept on paper: registers, files in cabinets and card catalogues. This traditional file system works for a few hundred records, but searching, sorting, copying and keeping it correct all depend on human effort.

Database Management System (DBMS)

Examples of DBMS software: MySQL (open source, used by most websites), Oracle Database (commercial, large enterprises), Microsoft SQL Server, PostgreSQL (open source, advanced features), SQLite (tiny, built into phones and apps) and MongoDB (a NoSQL system for unstructured data).

Recent trends. Cloud databases run on the Internet and grow with your needs (Google Cloud SQL, Amazon RDS). NoSQL databases such as MongoDB and Cassandra store data that does not fit neat tables. In-memory databases such as Redis and SAP HANA keep data in RAM for very fast answers. Distributed databases spread data over many servers so that one failure does not stop the service.

Features of a DBMS. Data storage in tables; quick data retrieval with simple commands; data manipulation (add, change, delete); data integrity (rules such as "marks must be 0 to 100"); control of redundancy (each fact stored once); security (who may see or change what); and backup and recovery.

A spreadsheet such as Excel is fine for one person's small list, but it cannot stop two rows having the same roll number, does not link tables safely, slows down with hundreds of thousands of rows, and is awkward for many users at once. A DBMS does all of these.

2.2 Data types in MySQL: numeric, string and date/time types

Choosing the right type keeps wrong data out and saves space: a date column rejects "hello", and arithmetic works on numbers but not on names.

Numeric types

String (character) types

Date and time types (written in quotes, in this exact order)

BOOLEAN stores TRUE or FALSE. In MySQL it is really TINYINT(1): TRUE is stored as 1 and FALSE as 0.

ERROR 1264 (22003): Out of range value for column 'phone_int' at row 1

ERROR 1406 (22001): Data too long for column 'code_char' at row 1

ERROR 1292 (22007): Incorrect date value: '2007  ' for column 'joined' at row 1

2.3 Table: field, record and table design

Key points about tables, fields and records:

• A table holds one kind of entity; its columns are fields and its rows are records. The table is the primary building block of a database.
• Every field has a name (such as dob) and a data type (such as DATE); every value in that field must fit the type.
• Each record describes one entity completely and is usually identified by a unique ID, the primary key.
• Tables can be linked to other tables through keys (section 2.4).

Designing a table

A well-designed table answers questions correctly for years. Follow these steps:

1. Decide the entity the table is about (students, books, sales).
2. List the fields, one fact per field: separate name, class and district rather than one long "details" field.
3. Choose a data type and size for each field.
4. Choose the primary key, a field whose value is different for every record (roll number, book number).
5. Add constraints such as NOT NULL for fields that must always be filled.
6. Do not store what can be calculated. Store the date of birth, not the age (the age changes every year); store marks, not the total.

CREATE TABLE books (
    book_no   INT PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    author    VARCHAR(60),
    price     DECIMAL(7, 2),
    copies    SMALLINT,
    bought_on DATE
);

2.4 Keys and relationships

In a database, keys are fields (or groups of fields) that identify records uniquely and link tables together.

In a students table, roll and a unique symbol_no (SEE symbol number) are both candidate keys. If roll is chosen as the primary key, symbol_no becomes an alternate key. name is not a candidate key, because two students can share a name. A super key is any set of fields that is unique, even if it contains extra fields (such as roll + name); every candidate key is a minimal super key.

Types of relationship

A relationship is a logical link between two tables through a common field (a key). It lets data stored in separate tables be used together without repeating it.

One-to-many is by far the most common relationship. A one-to-one link is used to split rarely-used or private details (medical records) into a separate table.

2.5 Introduction to MySQL

MySQL is a free, open-source relational DBMS that uses SQL (Structured Query Language) to define, store and query data. It was first released in 1995 by the Swedish company MySQL AB, which was bought by Sun Microsystems in 2008; Oracle Corporation took it over with Sun in 2010 and maintains it today. Most websites, school and office systems and many mobile apps keep their data in MySQL because it is fast, reliable and free.

Features of MySQL

• Open source and free (Community Edition), and runs on Windows, Linux and macOS.
• Simple language: SQL commands read almost like English.
• Data definition, retrieval and manipulation: create tables, query them and change their data.
• Multi-user: many users and programs can work at the same time.
• Security: user accounts, passwords and permissions.
• Fast and scalable: from a school lab to websites with millions of users.
• Works with modern applications: PHP, Python, Java and web frameworks.

Installing MySQL 8 on Windows

The textbook's steps use MySQL Installer, a Windows tool that existed only for MySQL 8.0. MySQL 8.0 reached its end of life in April 2026; the series to install now is MySQL 8.4 LTS (long-term support, maintained until 2032), which installs from an ordinary MSI file and is set up with MySQL Configurator. Workbench is downloaded separately.

1. Open dev.mysql.com/downloads/mysql, choose version 8.4 LTS and operating system Microsoft Windows, and download the Windows (x86, 64-bit), MSI Installer. Click "No thanks, just start my download".
2. Run the downloaded .msi file and choose the Typical setup. At the end keep Run MySQL Configurator ticked and click Finish.
3. In MySQL Configurator, keep the data folder, then on Type and Networking choose Development Computer and leave the port as 3306.
4. On Accounts and Roles type a strong root password twice and remember it: you will need it every time you log in as root.
5. On Windows Service keep "Configure MySQL Server as a Windows Service" (service name MySQL84) and "Start the MySQL Server at System Startup".
6. Accept the defaults for file permissions and sample databases, then on Apply Configuration click Execute. When every step shows a green tick, click Finish.
7. Download MySQL Workbench from dev.mysql.com/downloads/workbench, install it, and open it.

Working in MySQL Workbench

MySQL Workbench is the graphical tool for writing SQL and seeing the results. On its home screen, double-click the tile Local instance MySQL84, type the root password, and the SQL editor opens.

1Navigator — the SCHEMAS list shows each database; expand one to see its tables and columns2Toolbar — the lightning bolt runs the whole script (Ctrl + Shift + Enter); the bolt with a cursor runs only the statement under the cursor (Ctrl + Enter)3Query tab — the SQL editor where you type statements4Result grid — the rows returned by a SELECT5Output panel — one line per statement: green tick for success, red cross with the error message for failure6SQL Additions — built-in help showing the syntax of the statement you are typing

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

SQL and queries

SQL keywords are not case-sensitive (select works like SELECT), but writing keywords in capitals makes queries easier to read. Every statement ends with a semicolon, and text and date values go in single quotes.

Constraints

Constraints keep data accurate, stop duplicate or impossible entries, enforce the links between tables and record the organisation's rules inside the database itself.

2.6 Data Definition Language (DDL): CREATE, ALTER, DROP and TRUNCATE

DDL statements build and change the structure of the database: databases, tables, columns and constraints. They do not work on individual rows. In MySQL every DDL statement is committed at once — it cannot be undone with ROLLBACK — so think before you run DROP or TRUNCATE.

CREATE

CREATE DATABASE school;
USE school;
CREATE TABLE students (
    roll     INT PRIMARY KEY,
    name     VARCHAR(40) NOT NULL,
    gender   CHAR(1),
    class    INT NOT NULL,
    district VARCHAR(20) DEFAULT 'Kathmandu',
    dob      DATE,
    CONSTRAINT chk_gender CHECK (gender IN ('M', 'F'))
);
DESCRIBE students;

USE selects the database that the following statements work on, and DESCRIBE (or DESC) shows the structure MySQL actually created:

SHOW DATABASES; lists the databases and SHOW TABLES; lists the tables of the current database.

ALTER

ALTER TABLE changes the structure of an existing table. All of these were run, in this order, on the table above:

DROP and TRUNCATE

CREATE TABLE notice (id INT, title VARCHAR(30));
INSERT INTO notice VALUES (1, 'Holiday'), (2, 'Exam routine');
TRUNCATE TABLE notice;
SELECT * FROM notice;

The SELECT now returns Empty set, but DESCRIBE notice; still shows both columns: TRUNCATE removed all the rows and kept the structure.

DROP TABLE notice;

Now SHOW TABLES; no longer lists notice: DROP removed the table and its data for ever. DROP DATABASE school; would remove the whole database.

2.7 Data Manipulation Language (DML): INSERT, SELECT, UPDATE and DELETE

DML statements work on the data inside tables without changing their structure. All the examples below use the students table built in §2.6 (with district widened to VARCHAR(30)) and follow one another in order, so each result is exactly what MySQL returned at that point.

INSERT — adding records

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
INSERT INTO table_name VALUES (value1, value2, ...);   -- a value for every column, in order

INSERT INTO students (roll, name, gender, class, district, dob)
VALUES (1, 'Aarosh Pokharel', 'M', 10, 'Kathmandu', '2010-03-12');

INSERT INTO students (roll, name, gender, class, district, dob) VALUES
(2, 'Subigya Nepal', 'F', 10, 'Lalitpur', '2010-07-25'),
(3, 'Binay Adhikari', 'M', 10, 'Kaski', '2009-11-02'),
(4, 'Nidhi Sharma', 'F', 9, 'Chitwan', '2011-01-19'),
(5, 'Sampada Bhattarai', 'F', 10, 'Kathmandu', '2010-05-30'),
(6, 'Aman Yadav', 'M', 9, 'Dhanusha', '2011-09-14'),
(7, 'Rajesh Tamang', 'M', 10, 'Makwanpur', NULL),
(8, 'Anjali Gurung', 'F', 9, 'Kaski', '2011-02-21'),
(9, 'Iman Shrestha', 'M', 9, 'Bhaktapur', '2011-08-03');

INSERT INTO students VALUES (10, 'Muna Rai', 'F', 10, 'Morang', '2010-04-27');
INSERT INTO students (roll, name, class) VALUES (11, 'Dikshya Pokhrel', 10);
SELECT * FROM students;

The second INSERT added eight rows in one statement. Dikshya's row shows the constraints at work: district received its DEFAULT 'Kathmandu', and the columns with no value and no default became NULL ("no value").

(a) ERROR 1062 (23000): Duplicate entry '3' for key 'students.PRIMARY'
(b) ERROR 1364 (HY000): Field 'class' doesn't have a default value
(c) ERROR 3819 (HY000): Check constraint 'chk_gender' is violated.

SELECT — retrieving data

SELECT column1, column2 FROM table_name
WHERE condition
ORDER BY column [ASC | DESC];

SELECT * returns every column; the answer comes back as a temporary table called the result set. Dikshya's practice row is removed first with DELETE FROM students WHERE roll = 11;, leaving ten students.

The WHERE clause keeps only the rows for which its condition is true. It can use the comparison operators =, <> (not equal), >, <, >=, <=, the logical operators AND, OR, NOT, and the special forms BETWEEN … AND …, IN (…) and IS NULL.

SELECT name, dob FROM students WHERE class = 10;

SELECT roll, name FROM students WHERE gender = 'F' AND class = 10;

SELECT name, district FROM students WHERE district = 'Kaski' OR district = 'Chitwan';

SELECT name, dob FROM students WHERE dob BETWEEN '2010-01-01' AND '2010-12-31';

SELECT name, district FROM students WHERE district IN ('Kathmandu', 'Lalitpur', 'Bhaktapur');

SELECT name FROM students WHERE dob IS NULL;

ORDER BY, DISTINCT and LIMIT

ORDER BY sorts the result set: ASC (ascending, the default) or DESC (descending). With two columns, the second decides the order only among rows that tie on the first. DISTINCT removes repeated rows from the result, and LIMIT n keeps only the first n rows.

SELECT name, dob FROM students ORDER BY name;

SELECT name, class, dob FROM students ORDER BY class DESC, dob ASC;

SELECT name, dob FROM students WHERE dob IS NOT NULL ORDER BY dob LIMIT 3;

SELECT DISTINCT district FROM students;

LIKE — searching for patterns

LIKE compares text with a pattern that contains wildcard characters: % stands for any number of characters (including none) and _ (underscore) stands for exactly one character. The pattern must match the whole value. With MySQL's default settings LIKE ignores the difference between capital and small letters.

SELECT name, class FROM students WHERE name LIKE 'A%';

SELECT name FROM students WHERE name LIKE '%rai';

SELECT name FROM students WHERE name LIKE '%sh%';

SELECT name FROM students WHERE name LIKE '_a%';

SELECT name FROM students WHERE name LIKE '_man %';

UPDATE — changing existing data

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

UPDATE students SET district = 'Lalitpur' WHERE roll = 9;

UPDATE students SET class = class + 1 WHERE class = 9;

DELETE — removing records

DELETE FROM table_name WHERE condition;

DELETE FROM students WHERE roll = 11; removed Dikshya's row (1 row affected), DELETE FROM students WHERE name LIKE '%esh'; would remove every student whose name ends in "esh", and DELETE FROM students; would remove all rows while keeping the table's structure.

MySQL normally runs with autocommit switched on (SELECT @@autocommit; returns 1): each INSERT, UPDATE or DELETE is saved permanently the moment it runs. To be able to undo, start a transaction first:

START TRANSACTION;
DELETE FROM students WHERE class = 9;      -- Query OK, 4 rows affected
SELECT COUNT(*) FROM students;             -- 6
ROLLBACK;
SELECT COUNT(*) FROM students;             -- 10 again

ROLLBACK undid the delete; COMMIT would have made it permanent.

Reports

A report in MySQL is the organised presentation of data drawn from one or more tables by a query: a class list, a result summary, a sales total. MySQL itself prints reports as plain result tables; for designed, printable or graphical reports the data is passed to tools such as Power BI, Tableau or Looker Studio. Reports help people understand data, plan and decide.

The aggregate functions turn many rows into one value: COUNT(*) counts rows, SUM() adds, AVG() averages, MAX() and MIN() find the largest and smallest. GROUP BY makes one line per group, and HAVING filters groups.

To report marks, a second table holds each student's marks, with a composite primary key and a foreign key to students:

CREATE TABLE marks (
    roll    INT,
    subject VARCHAR(20),
    marks   INT CHECK (marks BETWEEN 0 AND 100),
    PRIMARY KEY (roll, subject),
    FOREIGN KEY (roll) REFERENCES students(roll)
);
INSERT INTO marks VALUES
(1, 'Computer', 88), (1, 'Maths', 76), (2, 'Computer', 92), (2, 'Maths', 81),
(3, 'Computer', 67), (3, 'Maths', 58), (5, 'Computer', 95), (5, 'Maths', 89),
(7, 'Computer', 54), (7, 'Maths', 47), (10, 'Computer', 79), (10, 'Maths', 85);

The keys now protect the data. INSERT INTO marks VALUES (15, 'Computer', 70); fails with error 1452 ("a foreign key constraint fails") because there is no student 15; DELETE FROM students WHERE roll = 1; fails with error 1451 because marks still refer to roll 1; and INSERT INTO marks VALUES (4, 'Computer', 105); fails the CHECK (error 3819).

SELECT class, COUNT(*) AS students FROM students GROUP BY class;

SELECT subject, COUNT(*) AS entries, MAX(marks) AS highest, MIN(marks) AS lowest,
       ROUND(AVG(marks), 1) AS average
FROM marks GROUP BY subject;

SELECT roll, SUM(marks) AS total FROM marks GROUP BY roll ORDER BY total DESC;

SELECT district, COUNT(*) AS students FROM students
GROUP BY district HAVING COUNT(*) > 1;

SELECT s.name, SUM(m.marks) AS total, ROUND(AVG(m.marks), 1) AS average
FROM students s JOIN marks m ON s.roll = m.roll
GROUP BY s.roll, s.name ORDER BY total DESC;

Reports in MySQL are valued for their organised presentation, filtering of only the rows needed, summarisation with aggregate functions, combination of several tables, timely access to current data and easy export to spreadsheets and reporting tools.

CREATE DATABASE krishikranti;
USE krishikranti;
CREATE TABLE farmers (
    farmer_id   INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    phone       VARCHAR(10) UNIQUE,
    district    VARCHAR(30) NOT NULL,
    land_ropani DECIMAL(6, 2) CHECK (land_ropani > 0)
);
CREATE TABLE crops (
    crop_id   INT PRIMARY KEY,
    crop_name VARCHAR(30) NOT NULL UNIQUE,
    season    VARCHAR(10) CHECK (season IN ('Summer', 'Winter', 'Spring'))
);
CREATE TABLE tools (
    tool_id        INT PRIMARY KEY,
    farmer_id      INT NOT NULL,
    tool_name      VARCHAR(30) NOT NULL,
    tool_condition VARCHAR(10) DEFAULT 'Good',
    FOREIGN KEY (farmer_id) REFERENCES farmers(farmer_id)
);
CREATE TABLE sales (
    sale_id     INT PRIMARY KEY,
    farmer_id   INT NOT NULL,
    crop_id     INT NOT NULL,
    sale_date   DATE NOT NULL,
    quantity_kg DECIMAL(8, 2) CHECK (quantity_kg > 0),
    rate_per_kg DECIMAL(8, 2) CHECK (rate_per_kg > 0),
    FOREIGN KEY (farmer_id) REFERENCES farmers(farmer_id),
    FOREIGN KEY (crop_id) REFERENCES crops(crop_id)
);
CREATE TABLE farm_activities (
    activity_id   INT PRIMARY KEY,
    farmer_id     INT NOT NULL,
    crop_id       INT NOT NULL,
    activity      VARCHAR(30) NOT NULL,
    activity_date DATE NOT NULL,
    FOREIGN KEY (farmer_id) REFERENCES farmers(farmer_id),
    FOREIGN KEY (crop_id) REFERENCES crops(crop_id)
);

Textbook exercises: model answers

CREATE DATABASE CompanyData;
USE CompanyData;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName  VARCHAR(50),
    LastName   VARCHAR(50),
    Salary     DECIMAL(10, 2)
);

ALTER TABLE Employees ADD Email VARCHAR(100);

ALTER TABLE Employees ALTER Salary SET DEFAULT 25000;

RENAME TABLE Employees TO StaffMembers;

INSERT INTO StaffMembers (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'Sampada', 'Bhattrai', 30000.50);

SELECT * FROM StaffMembers;

SELECT FirstName, Salary FROM StaffMembers;

UPDATE StaffMembers SET Salary = Salary * 1.10;

DELETE FROM StaffMembers WHERE EmployeeID = 103;

TRUNCATE TABLE StaffMembers;

Practice questions

Group A — Multiple choice (1 mark each)

1. Which of these is information rather than data? [1]a45, 67, 89bRam, Sita, Haric"The class average is 67"d2082-01-15
2. The best data type for a ten-digit mobile number is [1]aINTbSMALLINTcVARCHAR(10)dDATE
3. A field that uniquely identifies each record and can never be NULL is a [1]aforeign keybprimary keycalternate keydUNIQUE column
4. In a table marks(roll, subject, marks), the key (roll, subject) is a [1]aforeign keybcomposite keycalternate keydsuper table
5. A student can join many clubs and each club has many students. This relationship is [1]aone-to-onebone-to-manycmany-to-manydmany-to-one
6. Which command removes all the rows of a table but keeps its structure? [1]aDROP TABLEbTRUNCATE TABLEcALTER TABLEdCREATE TABLE
7. Which clause arranges the result set in order? [1]aWHEREbORDER BYcLIKEdDISTINCT
8. SELECT name FROM students WHERE name LIKE '_a%'; lists names whose [1]afirst letter is absecond letter is aclast letter is adonly letter is a
9. Which of these is a DML command? [1]aCREATEbALTERcUPDATEdDROP
10. The default port number of the MySQL server is [1]a80b443c3306d8080
11. Which constraint stops a mark of 105 from being stored? [1]aUNIQUEbDEFAULTcCHECKdNOT NULL
12. Which statement shows the structure (columns and types) of a table? [1]aSHOW studentsbDESCRIBE studentscSELECT * FROM studentsdUSE students

Group B — Short answer (2 marks each)

1. Define DBMS and write any two of its advantages. [2]
2. Differentiate between CHAR and VARCHAR with an example of each. [2]
3. What is a foreign key? Explain with an example. [2]
4. Write two differences between DELETE and TRUNCATE. [2]
5. Write an SQL statement to create a table teachers with fields tid (integer, primary key), tname (up to 40 characters, required), subject (up to 20 characters) and salary (decimal with 2 places). [2]
6. Using the students table of this chapter, write a query to show the name and district of Class 10 students in alphabetical order of name, and write its result. [2]
7. What does UPDATE marks SET marks = marks + 5 WHERE subject = 'Maths' AND marks < 50; do? How many rows does it change in the marks table of this chapter? [2]
8. What is a candidate key? Give an example. [2]

CREATE TABLE teachers (
    tid     INT PRIMARY KEY,
    tname   VARCHAR(40) NOT NULL,
    subject VARCHAR(20),
    salary  DECIMAL(9, 2)
);

SELECT name, district FROM students WHERE class = 10 ORDER BY name;

Group C — Long answer (4 marks each)

1. Define database and DBMS. Explain any three features of a DBMS. [1+3]
2. A shop keeps a table products(pid, pname, category, price, qty). Write SQL to(a) create the table with pid as primary key and price greater than 0,(b) insert the record (6, 'Speaker', 'Audio', 1500, 10),(c) show the name and price of products costing more than Rs 500, the most expensive first, and(d) delete the products that are out of stock. [4]
3. What is a key in a database? Explain primary key, foreign key and composite key with examples. [1+3]
4. Explain the three types of relationship with an example and a diagram of each. [4]

CREATE TABLE products (
    pid      INT PRIMARY KEY,
    pname    VARCHAR(40) NOT NULL,
    category VARCHAR(20),
    price    DECIMAL(8, 2) CHECK (price > 0),
    qty      INT DEFAULT 0
);
INSERT INTO products (pid, pname, category, price, qty)
VALUES (6, 'Speaker', 'Audio', 1500.00, 10);
SELECT pname, price FROM products WHERE price > 500 ORDER BY price DESC;
DELETE FROM products WHERE qty = 0;