Database Management System

This chapter introduces the Database Management System (DBMS), which deals with how data is stored, organized, and managed digitally instead of manually. It explains the meaning of data, information, and DBMS, along with the concepts of tables, keys, and relationships used in relational databases. The chapter also introduces MySQL, a popular open-source RDBMS, including its data types, DDL and DML commands, operators, and clauses used to create, modify, and manage data.

For SEE, students should be able to define key terms, write and explain SQL commands (especially CREATE, INSERT, SELECT, UPDATE, DELETE), identify primary and foreign keys, differentiate between DDL and DML, and solve simple output-based SQL query questions.

Definition: A database is an organized collection of related data that can be easily accessed, managed, and updated. Information such as names, phone numbers, marks, prices, and addresses, when collected and stored in an organized way, forms a database. A database is not just a pile of data; it is arranged carefully to make searching, adding, deleting, and updating data quick and easy. Examples include a school attendance record, a library book list, or a mobile contact list.

Data refers to raw facts or figures that have no clear meaning on their own, such as 80, Nepal, or 25/03/2025. Information is meaningful and organized data — for example, 'Rina scored 80 marks in Math' is information because we understand what it means.

DBMS (Database Management System) is software that helps to create, manage, and use databases. It helps to store, add, delete, update, and search for data, and also keeps data safe from unauthorized users. Some popular DBMS are MySQL, Oracle, Microsoft Access, and MongoDB.

Common DBMS technologies include Hierarchical DBMS, Network DBMS, Relational DBMS (RDBMS), Object-Oriented DBMS, Open Source DBMS, Distributed DBMS, Cloud DBMS, and In-Memory DBMS, each suited to different data storage and management needs. In this chapter, RDBMS technology (MySQL) is studied.

RDBMS is a relational database system that stores data in tables consisting of rows and columns. Each table represents an entity, and relationships can be established between tables using keys. RDBMS supports SQL commands to manage, update, and retrieve data efficiently while ensuring data integrity and security.

MySQL is an open-source relational database management system (RDBMS) that helps to store, organize, and manage data using Structured Query Language (SQL). A data type defines the kind of data that can be stored in a field of a database, such as numbers, text, or dates.

Note: In VARCHAR(50), up to 50 characters can be stored. In CHAR(5), space is added if data is less than 5 characters. DECIMAL is better than FLOAT for money values as it keeps exact value. BOOLEAN in MySQL is actually TINYINT(1), storing 1 or 0. BLOB is used when files need to be saved directly inside the database.

Table: A table is a part of a database where data is stored in an organized way using rows and columns. Each table stores data about one particular topic. For example, a table named Student can store information about students such as their names, ages, and addresses.

Column: A column is a vertical part of a table that stores one type of information. It is also called an attribute or field. For example, in the Student table above, ID stores identification numbers, Name stores names, Age stores ages, and City stores city names.

Field: A field is the smallest unit of data in a table — a single data value stored where a row and column meet. For example, the value 'Ram' in the Name column of the first row is a field.

Field Name: A field name is the name of a column in a table, telling what kind of data is stored in it. In the Student table, ID, Name, Age, and City are field names.

Row: A row is a horizontal part of a table that stores one complete set of information, also called a record or tuple. In the Student table, Row 1 stores Ram's details, Row 2 stores Sita's details, and Row 3 stores Hari's details.

In a database, keys are special columns that help organize, identify, and connect data. The three important types are Primary Key, Foreign Key, and Composite Key.

A Primary Key is a column or set of columns in a table that uniquely identifies each row in the table. No two rows can have the same primary key value, and it cannot be empty.

Here, ID is the Primary Key because each student has a unique ID, no two students share the same ID, and it is not empty.

A Foreign Key is a column in one table that connects to the primary key of another table. It is used to create a relationship between two tables, and its value must match a value from the primary key of the other table.

Here, Mark_ID is the Primary Key of the Marks table, and Student_ID is a Foreign Key that connects to the ID column in the Student table.

A composite key is a combination of two or more columns used together to uniquely identify a record in a table. It is used when a single column is not enough to make a record unique.

Here, Student_ID and Course_ID together uniquely identify each record.

In DBMS, a relationship defines how data in one table is connected to data in another table. It is usually established using primary keys and foreign keys, which help maintain data integrity and allow data to be linked efficiently across multiple tables.

In a one-to-one relationship, each record in one table is related to only one record in another table. For example, each person has only one passport, and each passport belongs to only one person.

In a one-to-many relationship, a single record in one table can be linked to many records in another table. For example, one teacher teaches many students, but each student has one teacher.

In a many-to-many relationship, records in one table can relate to multiple records in another table, and vice versa. For example, each student can join many courses, and each course can have many students.

MySQL is a relational database management system that helps to store, manage, and organize data properly. It is free, open-source software widely used in websites, applications, and business systems. MySQL uses SQL (Structured Query Language) to create tables, store data, search data, and manage records.

A table is an object in MySQL where data is stored in rows and columns; each column has a name and data type, and each row stores one complete record. Tables are created using SQL commands. CREATE TABLE Student ( ID INT, Name VARCHAR(50), Age INT, City VARCHAR(30) );

This command creates a table named Student with four columns: ID, Name, Age, and City.

A query is a command given to the MySQL database to work with data — to add, search, update, or delete information. SELECT * FROM Student WHERE City = 'Butwal';

This query searches the table and gives the details of students from Butwal city.

A report is a final output of data collected from one or more tables, shown in a clear and arranged way. Reports are made using queries and tools, not directly created by SQL commands alone. SELECT * FROM Student WHERE Age > 14;

This command shows a list of students above 14 years in a report-like format.

Constraints are rules applied to table columns to maintain the accuracy and reliability of data. They ensure that only valid and consistent data is entered into the database.

A command is an instruction written in SQL to perform specific tasks in a database, such as creating tables, inserting data, viewing records, updating existing records, or removing data and table structures. The two main types of commands are DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL commands are used to define, create, manage, and delete the database structure itself, including tables, columns, data types, and indexes. Common DDL commands are CREATE, ALTER, DROP, TRUNCATE, and RENAME.

The CREATE command is used to make a new database or a new table. Syntax: CREATE DATABASE database_name; Example: CREATE DATABASE SchoolDB;

CREATE TABLE Student ( ID INT PRIMARY KEY, Name VARCHAR(50), Age INT NOT NULL, City VARCHAR(50) );

This command creates a new table named Student with the given columns and constraints.

The ALTER command is used to modify the structure of an existing table — to add new columns, delete existing columns, change a column's data type, or rename a column or table.

Adding a column: ALTER TABLE Student ADD Grade VARCHAR(10);

Deleting a column: ALTER TABLE Student DROP COLUMN City;

Changing a column's data type: ALTER TABLE Student MODIFY COLUMN Age VARCHAR(5);

Renaming a column: ALTER TABLE Student CHANGE COLUMN Name FullName VARCHAR(50);

The DROP command is used to permanently delete an entire database or table from the system. Once dropped, all data and structure are removed forever, so it must be used carefully. Syntax: DROP TABLE table_name; DROP DATABASE database_name; Example: DROP TABLE Student; DROP DATABASE SchoolDB;

DML commands are used to manage the data inside database tables — to add, view, modify, or delete records without affecting the table structure. Common DML commands are INSERT, SELECT, UPDATE, and DELETE.

The INSERT command adds new data (records) into a table, similar to adding a new row in a spreadsheet. Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); Example: INSERT INTO Student (ID, Name, Age, City) VALUES (1, 'Ram', 18, 'Kathmandu');

The SELECT command retrieves and displays data from one or more tables. It can select all columns or specific columns, and can filter using WHERE or sort using ORDER BY. SELECT * FROM table_name; SELECT column1, column2 FROM table_name;

WHERE Clause: filters records based on a condition. Only rows satisfying the condition are shown. SELECT Name, Age FROM Student WHERE Age > 17;

ORDER BY Clause: sorts the result in ascending (ASC) or descending (DESC) order. SELECT Name, Age FROM Student ORDER BY Age DESC;

LIKE Clause: searches for a pattern in a column using wildcards % (any number of characters) and _ (a single character). SELECT Name FROM Student WHERE Name LIKE 'A%';

The UPDATE command modifies existing records in a table based on a given condition. The SET keyword assigns new values, and the WHERE clause decides which records to update. If WHERE is skipped, all records are updated. Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Example: UPDATE Student SET City = 'Bhaktapur' WHERE Name = 'Sita';

This updates Sita's city to Bhaktapur while leaving other records unchanged.

The DELETE command removes one or more existing records from a table based on a condition. The table structure remains unchanged; only the data is removed. If the WHERE clause is omitted, all records in the table will be deleted, so it must be used carefully. Syntax: DELETE FROM table_name WHERE condition;

Example: DELETE FROM Employee WHERE Name = 'Hari';

This removes the row containing Hari from the Employee table.