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.

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 allows users to store, retrieve, update, and delete data, while also handling tasks like security, backups, and allowing multiple users to access data safely at once.

Examples of DBMS Software

• MySQL : a widely used, free and open-source DBMS
• Oracle Database : a powerful DBMS commonly used by large businesses
• Microsoft SQL Server : a DBMS developed by Microsoft, common in enterprise systems
• SQLite : a lightweight DBMS often used in mobile apps

2.3 Data Types (int, varchar, datetime, currency, etc.)

A data type defines what kind of value can be stored in a particular column of a database table, such as whether it holds numbers, text, or dates. Choosing the correct data type keeps data accurate and saves storage space.

Choosing the right data type matters for example, storing a phone number as int can cause problems if it starts with a 0, since numbers don’t keep leading zeros, so phone numbers are usually stored as varchar instead.

2.4 Tables, Rows, and Columns

Data in a database is organized into tables, which look similar to a spreadsheet, made up of rows and columns.

• Table : a structured collection of related data, such as a “Students” table storing information about every student
• Column (Field) : represents one specific type of data stored in the table, such as Name, Age, or Class. Each column has its own data type
• Row (Record) : represents one complete entry in the table, containing a value for every column, such as all the details for one single student

Example of a simple “Students” table:

Here, StudentID, Name, Age, and Class are columns, while each numbered line (Ram’s full details, Sita’s full details) is a row.

2.5 Keys: Primary Key, Foreign Key

Primary Key

A primary key is a column (or combination of columns) whose value uniquely identifies each row in a table. No two rows can share the same primary key value, and a primary key cannot be left empty (null). In the Students table above, StudentID would typically be the primary key, since no two students share the same ID.

Foreign Key

A foreign key is a column in one table that refers to the primary key of another table, used to create a link (relationship) between the two tables. For example, if you had a separate “Marks” table recording exam scores, it might include a StudentID column as a foreign key, referring back to the Students table, so you know exactly which student each mark belongs to.

Example: Marks table using StudentID as a foreign key

Primary and foreign keys together allow databases to store related information across multiple tables without repeating all the details every time, which is one of the core ideas behind organized (relational) databases.

2.6 Introduction to MySQL: Table, Queries, Reports

MySQL is one of the most widely used database management systems, free and open-source, used to create, manage, and query databases using a language called SQL (Structured Query Language).

Table

As explained above, a table is where MySQL actually stores structured data, organized into rows and columns.

Query

A query is a command written in SQL, sent to the database, asking it to perform an action such as retrieving specific data, adding new data, or updating existing data. Queries are how users and applications actually interact with a database.

Report

A report is a formatted, readable output generated from database data, often summarizing or organizing query results in a way that’s useful for humans to read, such as a monthly sales report or a class results summary. Reports are usually built on top of one or more queries.

2.7 DDL (CREATE, ALTER, DROP)

DDL stands for Data Definition Language the set of SQL commands used to define, create, or modify the actual structure of a database, such as creating tables or changing their columns (as opposed to working with the data inside them).

CREATE

Used to create a new database or table.

CREATE DATABASE school;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Class INT
);

This creates a database called school, and inside it, a table called Students with four columns, where StudentID is set as the primary key.

ALTER

Used to modify the structure of an existing table, such as adding, removing, or changing a column.

ALTER TABLE Students ADD Email VARCHAR(100);

This adds a new column called Email to the existing Students table.

DROP

Used to permanently delete an entire table or database, including all the data inside it. This action cannot be undone, so it should be used carefully.

DROP TABLE Students;

2.8 DML (SELECT, INSERT, UPDATE, DELETE)

DML stands for Data Manipulation Language the set of SQL commands used to work with the actual data stored inside tables, such as adding, retrieving, changing, or removing records.

INSERT

Used to add a new row (record) into a table.

INSERT INTO Students (StudentID, Name, Age, Class)
VALUES (1, "Ram Sharma", 15, 10);

SELECT

Used to retrieve data from a table.

SELECT * FROM Students;

The asterisk * means “all columns.” You can also select specific columns only:

SELECT Name, Class FROM Students;

UPDATE

Used to change existing data in a table.

UPDATE Students
SET Class = 10
WHERE StudentID = 1;

This changes the Class value to 10, but only for the row where StudentID equals 1. Without the WHERE clause, every row in the table would be updated, which is a common and serious mistake to avoid.

DELETE

Used to remove one or more rows from a table.

DELETE FROM Students WHERE StudentID = 1;

This deletes only the row where StudentID equals 1. Just like UPDATE, leaving out the WHERE clause would delete every row in the table.

Difference Between DDL and DML

Since DDL and DML are both used to work with a database but serve very different purposes, it’s important to understand exactly how they differ:

A simple way to remember it: DDL builds the shelves, DML puts things on them (and takes them off).

Practical Tasks

1. Download and install an open-source database application, such as MySQL.
2. Create a database:
   Copy

   Copy

   CREATE DATABASE school;

3. Create tables with various attributes and appropriate data types:
   Copy

   Copy

   CREATE TABLE Students (
       StudentID INT PRIMARY KEY,
       Name VARCHAR(50),
       Age INT,
       Class INT
   );

4. Implement a primary key in tables : as shown above, StudentID INT PRIMARY KEY sets StudentID as the table’s primary key.
5. Define relationships between tables using foreign keys:
   Copy

   Copy

   CREATE TABLE Marks (
       MarkID INT PRIMARY KEY,
       StudentID INT,
       Subject VARCHAR(50),
       Score INT,
       FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
   );

6. Modify a table using the ALTER command:
   Copy

   Copy

   ALTER TABLE Students ADD Email VARCHAR(100);

7. Insert appropriate data into tables:
   Copy

   Copy

   INSERT INTO Students (StudentID, Name, Age, Class)
   VALUES (1, "Ram Sharma", 15, 10);

8. Display all data using the SELECT statement:
   Copy

   Copy

   SELECT * FROM Students;

9. Display specified records using WHERE clause and LIKE (%, _):
   Copy

   Copy

   SELECT * FROM Students WHERE Class = 10;
   
   SELECT * FROM Students WHERE Name LIKE "R%";
   
   SELECT * FROM Students WHERE Name LIKE "_am%";

   The % symbol matches any number of characters (so "R%" finds names starting with R), while _ matches exactly one character (so "_am%" finds names with any single character followed by “am”).

10. Update and delete records from existing tables:
    Copy

    Copy

    UPDATE Students SET Class = 10 WHERE StudentID = 1;
    
    DELETE FROM Students WHERE StudentID = 1;

Important Questions

1. What is a database? Explain its importance with an example.
2. List any four real-world applications of databases.
3. Differentiate between data and information with examples.
4. What is a DBMS? Name any two examples of DBMS software.
5. What is a data type? Why is choosing the correct data type important?
6. Differentiate between int, varchar, and datetime data types, giving an example of each.
7. Define table, row, and column in the context of a database.
8. What is a primary key? Why must a primary key be unique?
9. What is a foreign key? How does it help connect two tables?
10. Differentiate between primary key and foreign key.
11. What is MySQL? Why is it widely used?
12. What is a query in a database?
13. What is DDL? List any two DDL commands with their use.
14. What is DML? List any two DML commands with their use.
15. Write a SQL command to create a table named “Books” with columns BookID, Title, and Price.
16. Write a SQL command to add a new column called “Author” to an existing table named “Books”.
17. Write a SQL command to insert a new record into a table named “Students”.
18. Write a SQL command to display all records from a table named “Students” where Class is 9.
19. What is the use of the WHERE clause in SQL? Give an example.
20. Explain the use of the % and _ wildcards in the LIKE clause with examples.
21. Write a SQL command to update the age of a student with StudentID 2 to 16.
22. Write a SQL command to delete a record from the “Students” table where StudentID is 3.
23. What could go wrong if the WHERE clause is left out of an UPDATE or DELETE statement? Explain with an example.
24. Differentiate between the DROP and DELETE commands.