Saturday, September 26, 2026

Unit 2 - Database Management System

 

Unit 2 - Database Management System

2.1 Definition, importance and application of database

 

Database

A database is a systematic way of storing, organizing, and managing data so that it can be accessed, updated, and used easily and efficiently. It is an organized collection of related information stored in a computer system.

 

In our daily lives, we use databases in many situations where information needs to be stored and retrieved. For example, schools maintain student records such as names, attendance, exam marks, and addresses. Similarly, mobile phones store contacts, messages, and call history. All these are examples of databases where information is arranged properly for quick access whenever needed.

 

A database helps to store a large amount of information in an organized manner without confusion. It allows users to search, add, modify, and delete data easily. Because of these features, databases are widely used in hospitals, banks, online shopping websites, offices, schools, and many other organizations.

 

A database can be compared to a digital cupboard that keeps information properly arranged, secure, and easy to find whenever required.

 

In today’s digital world, databases are an essential part of most computer systems. When we book flight tickets, log into social media accounts, watch videos on streaming platforms, or make online transactions, databases work behind the scenes to store and manage information.

 

Understanding how databases work is the first step toward learning how modern technology stores, processes, and handles information efficiently.

 

Importance of Database:

  1. Stores and organizes large amounts of data systematically.
  2. Provides fast access and retrieval of required information.
  3. Ensures data security and maintains data accuracy.
  4. Allows easy updating, sharing, and management of data.
  5. Reduces data duplication and saves storage space.
  6. Provides backup and recovery facilities.
  7. Helps in decision-making and supports modern applications.

 

Applications of Database

Databases are used in different fields to store, organize, manage, and retrieve information efficiently.

1.     Education: Stores student records, attendance, marks, teacher details, and course information.

2.     Banking: Manages customer accounts, transactions, loan records, balances, and online banking services.

3.     Healthcare: Stores patient records, medical history, reports, appointments, and treatment details.

4.     Online Shopping (E-commerce): Maintains product details, prices, customer information, orders, payments, and reviews.

5.     Social Media: Manages user profiles, posts, comments, messages, likes, and connections.

6.     Government Systems: Maintains citizen records, identification details, tax records, and public service information.

7.     Cloud Services: Stores online data and allows users to access information from anywhere through the Internet.

 

Why might a school want to store student records in a database instead of using paper files or simple Excel sheets?

A school database can store large amounts of student information safely, allow quick searching and updating, reduce errors, and provide easy access to records whenever required.

2.2 Data, Information, Database, DBMS

1. Data

Data is a collection of raw facts, figures, or information that may not have a clear meaning by itself. It can be in the form of numbers, words, images, symbols, or other forms.

Examples of Data: Name of a student , Age , Mobile number , Examination marks , Address

When data is organized and processed, it becomes information. Data is the basic input used by computers to store, process, and perform different tasks.

 

2. Information

Information is the meaningful result obtained after processing, organizing, or analyzing data. It helps users understand facts and make decisions.

Example:

  • Data: 80, 85, 90
  • Information: The student scored an average of 85 marks.

 

3. Database

A database is an organized collection of related data that can be easily accessed, managed, and updated. It stores information in a structured form, usually in tables, making data easy to find and use.

 

Example:
A school database stores student names, grades, attendance, addresses, and other academic records.

 

Features of Database

  1. Organized data storage by storing information in a systematic and structured way for easy management.
  2. Easy data retrieval by allowing users to quickly search and access required information.
  3. Data updating and modification by enabling users to add, edit, or delete records whenever required.
  4. Data security by protecting stored information from unauthorized access and misuse.
  5. Data integrity by ensuring that stored data remains accurate, complete, and consistent.
  6. Reduced data redundancy by minimizing unnecessary duplication of data and saving storage space.
  7. Data sharing by allowing multiple users to access and use information efficiently.
  8. Backup and recovery by providing facilities to restore data during accidental loss or system failure.

 

Traditional File System vs Computerized Database

 

Traditional File System

A traditional file system refers to a system where data is stored and managed manually, often using physical storage methods like paper records or file cabinets.

 

Computerized Database System

A computerized database uses digital technology to store, retrieve, and manage data efficiently. Modern organizations use computerized databases because they are faster, more secure, and easier to manage.

 

Database Management System (DBMS)

A Database Management System (DBMS) is an application software used to store, manage, organize, and control data efficiently. It allows users to create, retrieve, update, and delete data in a structured way.

A DBMS acts as an interface between users and the database, ensuring data security, accuracy, integrity, and easy access.

 

 

 

 

Examples of DBMS Software

  1. MySQL: Open-source and widely used in web applications
  2. Oracle Database: Powerful commercial DBMS used in large enterprises
  3. Microsoft SQL Server: A popular DBMS from Microsoft, used in business environments.
  4. PostgreSQL: An open-source DBMS known for its advanced features and reliability.
  5. MongoDB: A NoSQL database used for handling large volumes of unstructured data.
  6. SQLite: Lightweight and used in mobile apps and embedded systems.

 

Recent Trends and Technologies in DBMS

1. Cloud Database

Cloud databases are databases hosted on cloud platforms instead of local servers. They provide scalability, flexibility, and cost-effective data storage, allowing users to access data from anywhere through the Internet.

2. NoSQL Database

NoSQL databases are non-relational databases designed to handle large amounts of unstructured and semi-structured data. They are widely used in big data applications.
Examples: MongoDB, Cassandra.

3. In-Memory Database

In-memory databases store data in the main memory (RAM) instead of traditional storage disks. This allows faster data retrieval and processing.
Examples: Redis, HANA.

4. Distributed Database

Distributed databases store data across multiple servers or different locations. They improve system performance, availability, and fault tolerance by allowing data to be accessed from different locations.

 

Features of DBMS (Database Management System)

1. Data Storage

A DBMS stores data in a structured form, usually in tables consisting of rows and columns. Each piece of information is stored at a specific location, making data easy to organize and manage.

2. Data Retrieval

A DBMS allows users to search and retrieve required data quickly using commands or queries without manually checking large amounts of information.

3. Data Manipulation

A DBMS supports operations such as adding, updating, and deleting data according to the user's requirements.

4. Data Integrity

A DBMS ensures that data remains accurate, consistent, and reliable by applying rules and restrictions.
Example: It prevents entering an invalid phone number format or duplicate records.

5. Data Redundancy Control

A DBMS reduces unnecessary duplication of data by organizing information properly and using techniques like normalization, which improves storage efficiency.

6. Data Security

A DBMS controls user access by allowing only authorized users to view or modify data. It protects sensitive information from unauthorized access.

7. Data Backup and Recovery

A DBMS provides backup and recovery facilities to protect data from accidental loss, system failure, or other technical problems.

 

Why is it necessary to use a DBMS rather than a simple spreadsheet or text file for storing large amounts of data?

A DBMS is necessary for storing large amounts of data because it provides better organization, security, and efficient data management compared to simple spreadsheets or text files.

Reasons:

  1. Efficient Data Management:
    A DBMS can store, organize, and manage large volumes of data systematically.
  2. Fast Data Retrieval:
    It allows users to quickly search and access required information using queries.
  3. Data Security:
    A DBMS provides access control and security features to protect sensitive data.
  4. Reduces Data Duplication:
    It minimizes repeated storage of the same data and maintains consistency.
  5. Easy Data Updating:
    Users can easily add, modify, and delete records without affecting other data.
  6. Multiple User Access:
    A DBMS allows many users to access and work with the same database simultaneously.
  7. Backup and Recovery:
    It provides facilities to back up and restore data in case of loss or system failure.

Conclusion:
A DBMS is more suitable than spreadsheets or text files for large amounts of data because it provides faster access, better security, accurate management, and reliable data handling.

 

Benefits of DBMS

  1. Easy Data Storage and Retrieval:
    A DBMS allows users to store data systematically and quickly retrieve required information whenever needed.
  2. Reduces Data Redundancy:
    A DBMS minimizes unnecessary duplication of data by organizing information efficiently.
  3. Ensures Data Consistency and Accuracy:
    A DBMS maintains correct, reliable, and consistent data throughout the system.
  4. Allows Multiple User Access:
    A DBMS enables multiple users to access and share the same database securely at the same time.
  5. Supports Data Backup and Recovery:
    A DBMS provides backup facilities and helps recover data in case of accidental loss or system failure.

Challenges of DBMS

  1. Requires Technical Knowledge:
    Managing and operating a DBMS requires technical skills and knowledge about database systems.
  2. High Initial Setup Cost:
    Setting up a DBMS may require expensive hardware, software, and skilled professionals.
  3. Data Security Risks:
    If a DBMS is not properly managed and protected, unauthorized users may access sensitive data.
  4. Requires Regular Maintenance:
    Complex database systems need continuous monitoring, updating, and maintenance.
  5. Performance Issues with Large Data:
    A DBMS may become slower when handling extremely large amounts of data without proper optimization.

 

 

Relational Database Management System (RDBMS)

A Relational Database Management System (RDBMS) is a type of DBMS that stores data in the form of tables consisting of rows and columns. Each table contains related information, and different tables are connected through common fields (keys) to organize and manage data efficiently.

RDBMS follows the relational model, where data relationships are maintained between different tables, making data storage, retrieval, and management easier.

 

Examples of RDBMS

  • MySQL: A popular open-source RDBMS widely used in websites and applications.
  • Oracle Database: A powerful commercial RDBMS used in large enterprises.
  • PostgreSQL: An open-source RDBMS known for advanced features and reliability.
  • Microsoft SQL Server: A widely used RDBMS for business applications.

 

Applications of RDBMS

  • Websites and Applications: Stores user accounts, content, and transaction details.
  • Banking Systems: Manages customer accounts, transactions, and financial records.
  • Schools: Stores student information, attendance, and examination results.
  • Businesses: Manages employee records, sales, inventory, and customer information.

 

Features of RDBMS

  1. Stores Data in Tables:
    Data is organized into tables containing rows (records) and columns (attributes).
  2. Uses Relationships Between Tables:
    Tables are connected through common fields, allowing related data to be accessed easily.
  3. Maintains Data Integrity:
    Ensures that data remains accurate, consistent, and reliable.
  4. Provides Data Security:
    Controls user access and protects important information.
  5. Supports Data Manipulation:
    Allows users to insert, update, delete, and retrieve data using SQL commands.
  6. Handles Large Amounts of Data:
    Efficiently manages large databases used by organizations.

Database

DBMS (Database Management System)

A database is an organized collection of related data stored in a structured form.

A DBMS is application software used to create, store, manage, and control databases.

It stores data in the form of tables, records, and fields.

It provides tools and commands to manage data stored in the database.

It is used for storing information.

It is used for storing, retrieving, updating, and securing information.

A database itself does not control data access or security.

A DBMS provides data security and access control features.

It cannot perform operations on data without additional software.

It allows users to insert, modify, delete, and retrieve data easily.

Example: Student database, bank customer database.

Example: MySQL, Oracle, Microsoft SQL Server, PostgreSQL.

 

 

 

 

 

 

DBMS

RDBMS

DBMS (Database Management System) is software used to store, manage, and retrieve data.

RDBMS (Relational Database Management System) is an advanced type of DBMS that stores data in related tables.

It may store data in files or simple structures.

It stores data in tables consisting of rows and columns.

Relationships between data may not be maintained.

Relationships between tables are maintained using keys.

It is suitable for small amounts of data and simple applications.

It is suitable for handling large amounts of data and complex applications.

It provides basic data security and management features.

It provides better security, data integrity, and consistency.

Data redundancy may be higher.

Data redundancy is reduced through normalization.

Supports single-user or limited user access.

Supports multiple users accessing data simultaneously.

Example: MS Access, dBase.

Example: MySQL, Oracle, PostgreSQL, Microsoft SQL Server.

 

Data

Information

Data is a collection of raw facts and figures.

Information is processed and organized data with meaning.

Data is unprocessed and may not have a clear meaning.

Information is meaningful and useful for understanding and decision-making.

It can be in the form of numbers, text, symbols, images, etc.

It is arranged and analyzed data that provides useful results.

Data is the basic input used for processing.

Information is the final output obtained after processing data.

Data alone cannot help in making decisions.

Information helps users make decisions and take actions.

Example: 80, 85, 90

Example: The student scored an average of 85 marks.

 

Traditional File System

Computerized Database

Data is stored and managed manually using paper files, registers, or file cabinets.

Data is stored and managed electronically using computer systems.

Data retrieval is slow and requires manual searching.

Data retrieval is fast and can be done easily using queries.

Data duplication is common and may cause inconsistency.

Reduces data redundancy and maintains data consistency.

Data security is limited and difficult to maintain.

Provides better security through authentication and access control.

Updating and modifying data is difficult and time-consuming.

Data can be easily updated, modified, and deleted.

Requires more physical storage space.

Requires less physical storage space and can store large amounts of data.

Data sharing among multiple users is difficult.

Allows multiple users to access and share data simultaneously.

Backup and recovery of data is difficult.

Provides easy backup and recovery facilities.

Example: Paper-based student records and office files.

Example: School management system, banking system, and online databases.

 

 

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

 

Data Type

 

A data type defines the type of data that can be stored in a particular column of a database table. It tells the database what kind of value can be entered, such as numbers, text, dates, or true/false values.

 

Choosing the correct data type helps to store data accurately, reduce storage space, and improve database performance.

 

Examples:

  • Age and ID numbers use numeric data types like INT.
  • Names and addresses use text data types like VARCHAR.
  • Birth dates and event times use date/time data types like DATE and DATETIME.

 

Types of SQL Data Types

 

1. Numeric Data Types

 

Numeric data types are used for storing numbers.

 

Data Type

Description

INT

Stores whole numbers without decimal points. Example: 10, 25, 100

DECIMAL(p,s)

Stores exact numbers with decimal values. Example: 95.50

FLOAT

Stores approximate decimal numbers with less precision.

SMALLINT

Stores smaller range of whole numbers.

BIGINT

Stores very large whole numbers.

 

Examples of Use:

  • Student ID → INT
  • Salary → DECIMAL
  • Population → BIGINT

 

2. Character (Text) Data Types

 

Character data types are used to store letters, words, and text information.

 

Data Type

Description

CHAR(n)

Stores fixed-length text with exactly n characters.

VARCHAR(n)

Stores variable-length text up to n characters.

TEXT

Stores large amounts of text such as paragraphs or descriptions.

 

Examples of Use:

  • Name → VARCHAR
  • Gender code → CHAR
  • Address or description → TEXT

 

 

 

 

 

3. Date and Time Data Types

 

Date and time data types are used to store dates and time-related information.

 

Data Type

Description

DATE

Stores only the date. Example: 2047-07-06

TIME

Stores only the time. Example: 14:30:00

DATETIME

Stores both date and time together.

TIMESTAMP

Records date and time automatically, often used for tracking events.

 

 

Examples of Use:

  • Date of birth → DATE
  • Class time → TIME
  • Online transaction time → DATETIME/TIMESTAMP

 

4. Boolean Data Type

 

Boolean data type is used to store logical values.

 

Data Type

Description

BOOLEAN

Stores either TRUE or FALSE values.

 

Examples:

  • Student status → TRUE/FALSE
  • Account active status → TRUE/FALSE

 

Importance of Choosing Correct Data Types

  1. Ensures data is stored in the correct format.
  2. Prevents invalid data entry.
  3. Saves storage space.
  4. Improves database performance.
  5. Maintains accuracy and consistency of data.

 

1. What might go wrong if we try to store a phone number as a date?
Answer: The database may store the phone number in the wrong format, causing incorrect data and difficulty in searching or using the information.

 

2. Why is it useful to separate numbers from text when designing a database?
Answer: Separating numbers and text helps the database process, search, calculate, and manage data more accurately and efficiently.

 

2.4 Tables, Rows and Columns

 

1. Table

A table is a structured way of storing data in a database using rows and columns. It is similar to a spreadsheet where data is organized clearly and systematically.

Each table represents a specific type of information, such as students, books, employees, or products. The columns define the type of data, while the rows contain individual records.

Tables help organize data clearly, making it easy to search, sort, and manage large amounts of information efficiently.

A table is considered the primary building block of a database because it helps store and manage information efficiently.

 

Example: Student Table

Student_ID

Name

Class

Date of Birth

101

Aarosh Pokharel

10

2010-03-12

102

Subigya Nepal

10

2011-07-25

103

Aarambha Gharti

9

2013-11-10

 

Features of Table

  1. Rows and Columns:
    Data is arranged in horizontal rows and vertical columns for easy organization.
  2. Unique Rows:
    Each row stores one complete record and is usually identified by a unique ID called a primary key.
  3. Defined Schema:
    Each column has a specific data type such as text, number, or date.
  4. Organized Data:
    Tables keep data neat, clear, and easy to understand.
  5. Easy Search and Update:
    Users can quickly search, add, modify, or delete records.
  6. Relationships:
    Tables can be connected with other tables using keys to manage related information.

 

2. Field (Column)

A field, also known as a column, is a part of a database table that stores a specific type of information about an entity.

It is a part of a database table that holds one piece of information about an entity

Each field contains data of a particular type, such as text, numbers, or dates.

Example:

In a student database:

  • Student_ID
  • Name
  • Class
  • Date of Birth

are fields of the table.

 

Key Points About Fields

  1. A field represents a vertical column in a database table.
  2. Each field stores a specific type of data for every record.
  3. The field name describes the type of information stored, such as Name, Age, or Date of Birth.
  4. The data type of a field determines the kind of data it can store, such as text, numbers, or dates.

 

 

3. Record (Row)

A record, also known as a row, represents a complete set of information about one entity in a database table.

Each record contains values for all fields of a table. It represents one complete entry, such as one student, employee, or product.

 

Example: Student Record

S_ID

Name

Class

Date of Birth

101

Binay Adhikari

10

2010

In this table:

  • The entire row containing 101, Binay Adhikari, 10, 2010 is one record.
  • It contains complete information about one student.

 

Key Points About Records

  1. A record represents all information about one entity, such as a student, employee, or product.
  2. Each record is made up of multiple fields, and each field stores a specific type of data.
  3. Records are arranged horizontally in a table, where each row contains complete information about one entry.

 

Field

Record

A field is a column in a database table that stores a specific type of data.

A record is a row in a database table that stores complete information about one entity.

It represents a single category or attribute of data.

It represents a complete entry containing multiple fields.

Each field stores the same type of information for all records.

Each record contains values for all fields in the table.

Fields are arranged vertically in a table.

Records are arranged horizontally in a table.

Example: Name, Roll Number, Class, Date of Birth are fields.

Example: All information of one student (101, Binay Adhikari, 10, 2010) is a record.

 

 

2.5 Keys: Primary Key, Foreign Key

 

Introduction to Keys

In a database, a key is a special field (column) used to identify records uniquely and establish relationships between tables. Keys help keep data organized, accurate, and connected.

The main types of keys are:

  1. Primary Key
  2. Foreign Key
  3. Composite Key

 

1. Primary Key

A Primary Key is a field or combination of fields that uniquely identifies each record in a table. The value of a primary key must be unique and cannot be empty (NULL).

 

Example: Student Table

StudentID

Name

Class

Age

101

Biwash

10

15

102

Bidhya

10

14

103

Biraj

9

14

Here, StudentID is the primary key because each student has a unique ID.

 

Features of Primary Key

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

 

2. Foreign Key

A Foreign Key is a field in one table that refers to the Primary Key of another table. It is used to create relationships between two tables and maintain data integrity.

A foreign key acts as a linking pin between two tables.

 

Example:

Students Table

StudentID

Name

Class

201

Nidhi

10

202

Sampada

9

 

Library Table

BookID

BookTitle

StudentID

B_01

Nepali History

201

B_02

Database Basics

201

B_03

Learn Python

202

Here, StudentID in the Library table is a Foreign Key because it refers to the StudentID (Primary Key) in the Students table.

Features of Foreign Key

  1. Creates relationships between tables.
  2. Refers to the primary key of another table.
  3. Maintains data integrity between related tables.
  4. Prevents invalid data entry.
  5. Can contain duplicate values and NULL values (depending on database rules).

3. Composite Key

A Composite Key is a key created by combining two or more fields to uniquely identify a record in a table.

It is used when a single field alone cannot uniquely identify a record.

Example:

StudentID

Subject

Marks

301

Nepali

85

301

Social

92

302

Computer

95

303

Social

78

Here:

  • StudentID alone cannot uniquely identify a record.
  • Subject alone cannot uniquely identify a record.
  • The combination of StudentID + Subject uniquely identifies each record.

Therefore, StudentID + Subject = Composite Key

Primary Key

Foreign Key

A primary key is a field that uniquely identifies each record in a table.

A foreign key is a field that refers to the primary key of another table.

It is used to identify records within the same table.

It is used to create relationships between two different tables.

It cannot contain duplicate values.

It can contain duplicate values.

It cannot contain NULL values.

It may contain NULL values depending on database rules.

Only one primary key is allowed in a table.

A table can have multiple foreign keys.

It is created in the main (parent) table.

It is created in the related (child) table.

Example: StudentID in the Student table.

Example: StudentID in the Library table referring to the Student table.

 

Primary Key

Foreign Key

Composite Key

A primary key is a field that uniquely identifies each record in a table.

A foreign key is a field that refers to the primary key of another table.

A composite key is a combination of two or more fields used to uniquely identify a record.

It is used to identify records within the same table.

It is used to create relationships between two tables.

It is used when a single field cannot uniquely identify a record.

It cannot contain duplicate values.

It can contain duplicate values.

The combined values of fields must be unique.

It cannot contain NULL values.

It may contain NULL values depending on database rules.

Individual fields may contain repeated values, but the combination must be unique.

Only one primary key is allowed in a table.

A table can have multiple foreign keys.

A table can have only one composite key.

Example: StudentID in the Student table.

Example: StudentID in the Library table referring to the Student table.

Example: StudentID + Subject in a Marks table.

a. Which of the following can be a primary key?

i. Student Name     ii. Date of Birth     ✅ iii. Student ID     iv. Class Name

b. What is the purpose of a foreign key?

i. To store numbers only     ii. To uniquely identify each record     ✅ iii. To link one table with another     iv. To create duplicate values

c. Which key is formed by combining two or more fields?

i. Foreign Key     ii. Primary Key     ✅ iii. Composite Key     iv. Super Key

 

Types of Database Relationships

A database relationship is a logical connection between two or more tables in a database. Relationships are created using common fields, usually keys, to connect related data stored in different tables.

 

Database relationships help to:

  • Organize data efficiently.
  • Reduce data redundancy.
  • Maintain data accuracy and integrity.
  • Retrieve related information easily.

 

There are three main types of relationships in a database:

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

 

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

A one-to-one relationship is a relationship where one record in one table is connected to only one record in another table, and vice versa.

This type of relationship is less common and is used when closely related information is separated into different tables.

Example:

A person can have only one passport, and each passport belongs to only one person.

Person Table (1) → Passport Table (1)

Features

  • One record matches with only one record.
  • Used to separate important or sensitive information.
  • Maintains better data organization.

 

2. One-to-Many (1:M) Relationship

A one-to-many relationship exists when one record in one table is related to multiple records in another table, but each record in the second table is connected to only one record in the first table.

It is the most common type of database relationship.

Example:

A teacher can teach many classes, but each class is taught by only one teacher.

Teacher Table (1) → Classes Table (Many)

Features

  • One record can have multiple related records.
  • Multiple records cannot belong to multiple parent records.
  • Commonly uses a foreign key.

 

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

A many-to-many relationship exists when multiple records in one table are related to multiple records in another table.

This relationship requires a third table called a junction table (bridge table) to connect the two tables.

Example:

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

Students Table (Many) ↔ Courses Table (Many)

Features

  • Multiple records can relate to multiple records.
  • Requires an intermediate/junction table.
  • Helps manage complex relationships.

 

One-to-One (1:1)

One-to-Many (1:M)

Many-to-Many (M:M)

One record in one table is connected to only one record in another table.

One record in one table is connected to many records in another table.

Many records in one table are connected to many records in another table.

Each record has only one matching record in the related table.

Each record in the second table is connected to only one record in the first table.

Each record in both tables can have multiple related records.

It is less common in database design.

It is the most commonly used relationship in databases.

It is used when complex relationships exist between tables.

It usually does not require an additional table.

It uses a foreign key in the many-side table.

It requires a third table called a junction table.

Example: A person has one passport.

Example: A teacher teaches many classes.

Example: A student enrolls in many courses, and each course has many students.

Person (1) → Passport (1)

Teacher (1) → Classes (M)

Students (M) ↔ Courses (M)

 

2.6 Introduction to MySQL: table, queries, reports

 

MySQL

MySQL is a popular open-source Relational Database Management System (RDBMS) that uses SQL (Structured Query Language) to store, manage, organize, and retrieve data efficiently.

 

It was originally developed by a Swedish company called MySQL AB and is currently maintained by Oracle Corporation. MySQL is widely used in web development, software applications, and data-driven systems because of its speed, reliability, security, 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

 

MySQL is commonly used in websites, schools, banks, offices, and mobile applications.

 

Features of MySQL

  1. Simple Language:
    Uses SQL commands that are easy to learn and understand for managing databases.
  2. Data Retrieval:
    Allows users to quickly search and retrieve required information from databases.
  3. Data Manipulation:
    Supports adding, updating, and deleting data using SQL commands.
  4. Data Definition:
    Allows users to create, modify, and delete database structures such as tables.
  5. Multiple Users:
    Allows multiple users to access and work with the database at the same time.
  6. Security Features:
    Provides authentication and access control to protect data from unauthorized access.
  7. Used in Modern Applications:
    Widely used in websites, software applications, banking systems, and mobile applications.

 

 

 

Advantages of MySQL

  1. Open Source:
    Available freely and can be used without high licensing costs.
  2. Fast Performance:
    Processes database operations quickly and efficiently.
  3. Reliable:
    Provides stable and dependable data management.
  4. Scalable:
    Can handle small as well as large databases.
  5. Secure:
    Provides features to protect important information.

 

Advantages of MySQL

  1. Open Source: Freely available and reduces software cost.
  2. Fast Performance: Processes database operations quickly.
  3. Easy to Use: Uses simple SQL commands for database management.
  4. High Security: Protects data through authentication and access control.
  5. Reliable and Stable: Provides secure and dependable data management.
  6. Multi-User Support: Allows multiple users to access data simultaneously.
  7. Scalable: Handles both small and large amounts of data efficiently.

 

Download and Install MySQL (Windows)

 

Steps to Download MySQL

Step 1: Visit Official MySQL Download Page

Visit the official MySQL website:
https://www.mysql.com/downloads/

Step 2: Select MySQL Community Downloads

Click on MySQL Community (GPL) Downloads.

Step 3: Select MySQL Installer for Windows

Choose MySQL Installer for Windows from the available options.

Step 4: Download MySQL Installer

Select the required version and operating system, then download the Windows MSI Installer.

Step 5: Start Download

Click on "No thanks, just start my download" to begin downloading the installer file.

 

Steps to Install and Configure MySQL

Step 6: Run Installer

Locate the downloaded installer file and double-click it to start the installation process.

Step 7: Choose Setup Type

Select "Full" under Setup Type to install:

  • MySQL Server
  • MySQL Workbench
  • Documentation
  • Examples

Then click Next.

Step 8: Install Products

Click Execute to install all selected products. After successful installation, click Next.

Step 9: Configure MySQL Server

Under Type and Networking, select Development Computer as the configuration type and click Next.

Step 10: Authentication Method

Select the default authentication method and click Next.

Step 11: Set Root Password

Create a strong password for the root account. This password is required to access MySQL.

Step 12: Configure Windows Service

Keep the default Windows Service settings and click Next.

Step 13: Apply Configuration

Allow the installer to update permissions and click Execute to apply configuration changes.

Test the connection to ensure that MySQL Server is working properly.

Step 14: Finish Installation

After successful configuration, click Finish.

 

Using MySQL Workbench

MySQL Workbench is a graphical user interface (GUI) tool used to manage MySQL databases easily.

Steps:

  1. Open MySQL Workbench.
  2. Select Local Instance MySQL80 connection.
  3. Enter the root password.
  4. Click OK.
  5. You are now connected to MySQL Server and can create databases and tables.

 

Verify MySQL Installation

After installation, verify whether MySQL is installed correctly.

Step 1: Open Command Prompt

Open Command Prompt (Windows) or Terminal.

Step 2: Check MySQL Version

Type: mysql --version

If MySQL is installed correctly, it displays the installed version.

 

Step 3: Login to MySQL

Type: mysql -u root -p

Enter the root password.

If you see:

mysql>

it means MySQL is running successfully.

 

Exit MySQL

Use: exit;  or quit;

 

2.6.1 Table in MySQL

 

Table

In MySQL, a table is a basic structure used to store and organize data in a database. It stores information in the form of rows and columns, where each row represents a record and each column represents a field.

All data in a MySQL database is stored in one or more tables. Tables help users to organize, manage, search, and retrieve related information efficiently.

Tables can be created, modified, and deleted using SQL commands.

Example: Student Table

Student_ID

Name

Class

Age

101

Binay

10

15

102

Nidhi

9

14

In this table:

  • Student_ID, Name, Class, Age are fields (columns).
  • Each complete row represents a record.

Features of a Table in MySQL

  1. Stores Data in Rows and Columns:
    Tables organize information in a structured format using rows and columns.
  2. Specific Data Type for Each Column:
    Each column has a defined data type such as number, text, or date.
  3. Rows Represent Records:
    Each row in a table contains complete information about one item or entity.
  4. Columns Represent Fields:
    Each column stores a specific type of information about the records.
  5. Supports Relationships Between Tables:
    Tables can be connected using keys such as Primary Key and Foreign Key.
  6. Can Be Managed Using SQL Commands:
    Tables can be created, modified, or deleted using SQL queries.

Relational Table

A relational table is a table in a relational database where data is organized into rows and columns. Each table represents a specific type of entity, such as students, teachers, courses, or products.

A table becomes relational when it can be connected with other tables using keys such as Primary Key and Foreign Key. These relationships help organize related information, reduce data duplication, and improve data management efficiency.

In a relational table:

  • Each row represents a unique record.
  • Each column represents a specific field or attribute.

Example of Relational Tables

1. Students Table

Student_ID

Name

Class

101

Binay

10

102

Nidhi

9

Student_ID is the Primary Key.

2. Courses Table

Course_ID

Course_Name

C01

Computer Science

C02

Mathematics

Course_ID is the Primary Key.

3. Student_Courses Table

Student_ID

Course_ID

101

C01

101

C02

102

C01

Student_ID and Course_ID act as Foreign Keys to connect students and courses.

Features of Relational Table

  1. Organized Data Storage:
    Stores data in rows and columns in a structured format.
  2. Unique Records:
    Each row represents a unique record in the table.
  3. Specific Fields:
    Each column stores a particular type of information.
  4. Uses Keys:
    Uses Primary Keys and Foreign Keys to connect related tables.
  5. Reduces Data Redundancy:
    Avoids unnecessary duplication of data by separating information into related tables.
  6. Easy Data Management:
    Allows users to search, update, and manage related information efficiently.

 

2.6.2 Structured Query Language (SQL)

 

Definition of Query

A query is a request or command given to a database to perform a specific operation on data. It is used to retrieve, insert, update, or delete data from a database.

SQL queries are written using SQL (Structured Query Language), which is used to communicate with a Database Management System (DBMS).

A query works like a question to the database. It helps users obtain meaningful information based on specific conditions without manually searching through records.

A query is a question to the database. SQL commands are the fundamental building blocks for communicating with a database management system (DBMS).

 

Example:

If we want to find all students studying in Class 10 from a student database, we can write a query to display only those records.

Queries allow users to:

  • Search large amounts of data quickly.
  • Filter and sort information.
  • Add new records.
  • Update existing records.
  • Delete unwanted data.

SQL queries can be simple or complex depending on the requirement.

 

SQL (Structured Query Language)

SQL, pronounced as “See-quell”, is a language used to communicate with relational databases. It provides commands to define, manipulate, and control data.

 

SQL is divided into three main sub-languages:

  1. DDL (Data Definition Language):
    Used to create and modify database structures.
    Examples: CREATE, ALTER, DROP
  2. DML (Data Manipulation Language):
    Used to insert, update, delete, and retrieve data.
    Examples: INSERT, UPDATE, DELETE, SELECT
  3. DCL (Data Control Language):
    Used to control access and permissions in databases.
    Examples: GRANT, REVOKE

 

Features of Query

  1. Retrieves Specific Data:
    Allows users to search and display required information from tables.
  2. Adds, Updates, or Deletes Records:
    Helps users insert new data, modify existing records, or remove unwanted data.
  3. Filters and Sorts Data Easily:
    Allows users to display data according to specific conditions and arrange it properly.
  4. Useful for Reports and Analysis:
    Helps generate useful reports and analyze database information.

 

MySQL Constraints

 

MySQL Constraints are rules applied to the columns of a table to control the type and validity of data stored in a database. They help maintain data integrity by ensuring that only accurate, valid, and consistent data is entered into tables.

Constraints prevent problems such as duplicate values, missing data, and incorrect entries.

 

Types of MySQL Constraints and Their Functions

Constraint

Description

PRIMARY KEY

Ensures that each record has a unique value and does not allow duplicate or NULL values in the specified column.

FOREIGN KEY

Links one table with another by referring to the primary key of another table and maintains data consistency.

NOT NULL

Prevents a column from storing empty (NULL) values and ensures required data is entered.

UNIQUE

Ensures that all values in a column are different and prevents duplicate entries.

CHECK

Restricts the values that can be entered into a column based on specified conditions.

DEFAULT

Automatically assigns a predefined value to a column if no value is provided during data insertion.

 

Importance of MySQL Constraints

  1. Maintains Data Accuracy and Consistency:
    Ensures that stored data remains correct, reliable, and consistent.
  2. Prevents Duplicate and Incorrect Data Entries:
    Avoids repeated values and invalid information in database tables.
  3. Establishes Relationships Between Tables:
    Helps connect different tables using keys such as primary key and foreign key.
  4. Enforces Business Rules and Conditions:
    Ensures that data follows specific rules required by the organization.

 

2.7 Data Definition Language (DDL)

 

DDL (Data Definition Language) is a part of SQL (Structured Query Language) used to define, create, and manage the structure of database objects such as tables, databases, views, and indexes.

DDL commands are used to control how data is stored and organized in a database. They do not directly manage the data inside tables; instead, they modify the structure of database objects.

When a DDL command is executed, the changes are automatically saved (committed) to the database and usually cannot be reversed using rollback.

 

Common DDL Commands and Their Functions

Command

Description

CREATE

Creates new database objects such as databases, tables, views, or indexes.

ALTER

Modifies the structure of an existing table, such as adding, deleting, or changing columns.

DROP

Permanently deletes database objects such as tables or databases.

TRUNCATE

Removes all records from a table while keeping the table structure unchanged.

RENAME

Changes the name of an existing table or database object.

 

 

 

 

 

Importance of DDL

  1. Creates Database Structure:
    Helps create tables, databases, and other database objects.
  2. Modifies Database Objects:
    Allows changes to existing table structures according to requirements.
  3. Maintains Database Organization:
    Helps arrange and manage how data is stored.
  4. Removes Unnecessary Objects:
    Allows permanent deletion of unwanted database structures.

 

DDL Commands in SQL (CREATE, ALTER, DROP)

 

1. CREATE Query

The CREATE query is a DDL (Data Definition Language) command used to create new database objects such as databases, tables, views, and indexes.

Before creating tables, a database must be created first because tables are stored inside a database.

 

A. CREATE DATABASE Statement

The CREATE DATABASE command is used to create a new database.

 

Syntax:

CREATE DATABASE database_name;

 

Example:

CREATE DATABASE school;

 

Explanation:
This command creates a new database named school.

After creating a database, we select it using the USE command.

 

Syntax:

USE database_name;

 

Example:

USE school;

 

B. CREATE TABLE Statement

The CREATE TABLE command is used to create a new table inside a database.

 

Syntax:

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype

);

Where:

  • Column → Represents a field in the table.
  • Datatype → Defines the type of data stored in the column.

 

Example 1: Creating a Table

CREATE TABLE students (

ID INT,

Name VARCHAR(50),

Class INT,

DOB DATE

);

Explanation:

This command creates a table named students with four fields:

  • ID
  • Name
  • Class
  • DOB

 

Example 2: Adding Primary Key

CREATE TABLE students (

ID INT PRIMARY KEY,

Name VARCHAR(50),

Class INT,

DOB DATE

);

Explanation:

The PRIMARY KEY ensures that:

  • Each student has a unique ID.
  • ID cannot contain duplicate or NULL values.

 

Example 3: Adding NOT NULL Constraint

CREATE TABLE students (

ID INT PRIMARY KEY,

Name VARCHAR(50),

Class INT NOT NULL,

DOB DATE

);

Explanation:

The Class column is set as NOT NULL, meaning it must contain a value when inserting records.

 

2. ALTER Query

Definition

The ALTER query is a DDL command used to modify the structure of an existing table.

It allows users to add, delete, modify, or rename columns.

 

A. Add a New Column

Syntax:

ALTER TABLE table_name

ADD column_name data_type;

Example:

ALTER TABLE students

ADD Address VARCHAR(50);

 

B. Delete a Column

Syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

Example:

ALTER TABLE students

DROP COLUMN Address;

 

C. Modify a Column

Syntax:

ALTER TABLE table_name

MODIFY COLUMN column_name new_data_type;

Example:

ALTER TABLE students

MODIFY COLUMN Name VARCHAR(100);

 

D. Rename a Column

Syntax:

ALTER TABLE table_name

CHANGE old_column_name new_column_name data_type;

Example:

ALTER TABLE students

CHANGE Name Student_Name VARCHAR(50);

 

E. Remove Constraints

Syntax:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

 

3. DROP Query

Definition

The DROP query is a DDL command used to permanently delete database objects such as databases, tables, or views.

⚠️ Once a database object is dropped, its data cannot be recovered.

 

A. Drop Database

Syntax:

DROP DATABASE database_name;

Example:

DROP DATABASE school;

 

B. Drop Table

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE students;

 

 

2.8 Data Manipulation Language (DML)

DML (Data Manipulation Language) is a part of SQL used to manage and manipulate the data stored inside database tables.

 

DML commands allow users to:

  • Insert new records.
  • Retrieve existing data.
  • Update existing records.
  • Delete unwanted records.

 

Unlike DDL, which manages the structure of a database, DML focuses on the content (data) stored in tables.

DML operations can be rolled back, meaning changes can be undone before they are permanently saved using the COMMIT command.

 

Common DML Commands

Command

Function

INSERT

Adds new records into a table.

SELECT

Retrieves data from one or more tables.

UPDATE

Modifies existing records in a table.

DELETE

Removes records from a table.

 

1. INSERT Query

The INSERT query is used to add new records (rows) into a database table.

It allows users to insert a single record or multiple records at a time.

Syntax:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example:

INSERT INTO students(ID, Name, Class, DOB)

VALUES (1, 'Dikshya Pokhrel', 10, '2007-05-12');

Explanation:
This command adds a new student record into the students table.

 

2. SELECT Query

The SELECT query is used to retrieve or display data from database tables.

The output obtained after executing a SELECT query is called a result set.

Syntax:

SELECT column_name

FROM table_name;

Examples:

Display all records:

SELECT * FROM students;

Explanation:
Displays all columns and records from the students table.

 

Display specific column:

SELECT Name FROM students;

Explanation:
Displays only the Name column from the students table.

 

 

Display multiple columns:

SELECT Name, DOB FROM students;

Explanation:
Displays Name and Date of Birth information.

 

3. UPDATE Query

The UPDATE query is used to modify existing records in a database table.

It changes values of one or more columns based on a specified condition.

Syntax:

UPDATE table_name

SET column1 = value1

WHERE condition;

Example:

UPDATE students

SET Class = 11

WHERE ID = 5;

Explanation:
Updates the class of the student whose ID is 5.

 

4. DELETE Query

The DELETE query is used to remove existing records (rows) from a database table.

It can delete specific records using conditions or remove all records from a table.

Syntax:

DELETE FROM table_name

WHERE condition;

Example:

DELETE FROM students

WHERE Name = 'Muna';

Explanation:
Deletes the record of the student named Muna.

 

Delete All Records:

DELETE FROM students;

Explanation:
Removes all records from the table but keeps the table structure.

 

WHERE Clause

The WHERE clause is used to filter records and retrieve only those rows that satisfy a specific condition.

Syntax:

SELECT column_name

FROM table_name

WHERE condition;

Example:

SELECT Name, DOB

FROM students

WHERE Class = 10;

Explanation:
Displays the name and date of birth of students studying in Class 10.

 

 

 

 

LIKE Clause

The LIKE clause is used to search data based on a specific pattern. It is commonly used with the WHERE clause.

It helps find records when the exact value is unknown.

Wildcard Characters

Symbol

Meaning

%

Represents zero, one, or multiple characters.

_

Represents exactly one character.

 

Examples of LIKE Clause

1. Starts with a letter

SELECT Name, Class

FROM students

WHERE Name LIKE 'A%';

Explanation:
Displays students whose names start with A.

 

2. Single unknown character

UPDATE students

SET Class = 12

WHERE Name LIKE '_man';

Explanation:
Updates students whose names have four letters and end with "man", such as Aman.

 

3. Ends with specific letters

DELETE FROM students

WHERE Name LIKE '%esh';

Explanation:
Deletes students whose names end with "esh", such as Rajesh or Ramesh.

 

DDL (Data Definition Language)

DML (Data Manipulation Language)

DDL is used to define and manage the structure of a database.

DML is used to manage and manipulate the data stored inside database tables.

It works with database objects such as tables, databases, views, and indexes.

It works with records and values stored in tables.

It is used to create, modify, and delete database structures.

It is used to insert, retrieve, update, and delete data.

DDL commands automatically save changes permanently.

DML changes can be rolled back before using the COMMIT command.

It does not directly handle the data stored in tables.

It directly works with the content of tables.

Examples: CREATE, ALTER, DROP, TRUNCATE, RENAME

Examples: INSERT, SELECT, UPDATE, DELETE

Example: Creating a student table.

Example: Adding student records into the table.

 

 

2.8.5 Report in MySQL

 

A report in MySQL is an organized presentation of data retrieved from one or more database tables using SQL queries. It displays information in a meaningful and readable format to help users understand, analyze, plan, and make decisions.

 

MySQL itself does not directly create graphical or printed reports, but it provides structured data that can be used to generate reports using external reporting tools such as Power BI, Tableau, and Google Data Studio.

 

In simple terms, a report is the result of a query that extracts specific information from a database and presents it in a useful format.

 

Types of Reports Generated Using MySQL Queries

1. Summary Reports - Show total values like total sales, number of students, etc.

2. Detail Reports - Display detailed records (e.g., list of all students with marks).

3. Grouped Reports - Group data by categories like department, class, city, etc

4. Filtered Reports - Reports based on specific conditions (e.g., students above 80% marks).

5. Aggregate Reports - Use functions like SUM, AVG, COUNT to summarize data

 

Features of Reports in MySQL

  1. Organized Data Presentation:
    Presents data in a clear, structured, and meaningful format for easy understanding.
  2. Data Filtering:
    Allows users to display only required information based on specific conditions.
  3. Summarization:
    Helps summarize large amounts of data using functions like SUM(), AVG(), and COUNT().
  4. Multi-Table Combination:
    Combines data from multiple tables to create more meaningful reports.
  5. Timely Access:
    Provides quick access to updated information whenever required.
  6. Easy Export:
    Allows reports to be exported into different formats for sharing and further analysis.

 

 

Case Study: Design of Relational Database for KrishiKranti Mobile Farming Application

a. Introduction

Agriculture is one of the important sectors of Nepal, and many farmers require better tools to manage crops, farming activities, equipment, and sales information. With the growth of digital technology, mobile applications can help farmers maintain records and make better decisions using organized data.

KrishiKranti is a mobile farming application designed to support Nepali farmers by helping them manage crop details, farming tools, production activities, and sales records efficiently. The application uses a relational database system to store, organize, and retrieve farming-related information. A relational database stores information in tables and connects related data using keys such as primary keys and foreign keys. Wikipedia

 

b. Problem Statement

Many farmers still maintain farming records manually, which creates difficulties such as:

  • Difficulty in keeping records of crops and production.
  • Loss of important farming information.
  • Poor management of agricultural tools and resources.
  • Difficulty tracking sales and income.
  • Lack of organized data for planning future farming activities.

Therefore, a digital database system is required to store, manage, and analyze farming information efficiently.

 

c. Objectives

The main objectives of the KrishiKranti database system are:

  1. To create a digital record system for farmers and their farming activities.
  2. To store information about crops, planting dates, harvesting details, and production.
  3. To manage agricultural tools and equipment used by farmers.
  4. To maintain sales records of agricultural products.
  5. To provide quick access to farming information through SQL queries.
  6. To reduce data duplication and maintain accurate farming records.

d. SQL Integration

The KrishiKranti application uses MySQL/RDBMS to store and manage data.

Database Tables

1. Farmer Table

Farmer_ID

Name

Address

Phone

101

Ram Thapa

Chitwan

98XXXXXXXX

Primary Key: Farmer_ID

2. Crop Table

Crop_ID

Crop_Name

Season

Farmer_ID

C01

Rice

Summer

101

Primary Key: Crop_ID
Foreign Key: Farmer_ID

3. Tools Table

Tool_ID

Tool_Name

Quantity

Farmer_ID

T01

Tractor

1

101

Primary Key: Tool_ID
Foreign Key: Farmer_ID

4. Sales Table

Sale_ID

Crop_ID

Quantity

Price

S01

C01

500 kg

50000

Primary Key: Sale_ID
Foreign Key: Crop_ID

Example SQL Queries

Create Farmer Table

CREATE TABLE Farmer(

Farmer_ID INT PRIMARY KEY,

Name VARCHAR(50),

Address VARCHAR(50),

Phone VARCHAR(15)

);

Insert Farmer Data

INSERT INTO Farmer

VALUES(101,'Ram Thapa','Chitwan','9812345678');

Display Crop Information

SELECT * FROM Crop;

Update Sales Information

UPDATE Sales

SET Price=60000

WHERE Sale_ID='S01';

 

e. Expected Outcomes

After implementing the KrishiKranti database system, the expected outcomes are:

  1. Farmers can store and manage farming information digitally.
  2. Crop production records can be accessed easily.
  3. Tool management becomes more organized.
  4. Sales and income records can be tracked efficiently.
  5. Data accuracy and consistency can be maintained.
  6. Farmers can make better decisions using organized information.
  7. The system reduces paperwork and saves time.

 

f. Conclusion

The KrishiKranti mobile farming application database provides an effective solution for managing agricultural activities digitally. By using a relational database system, farmer information, crop details, tools, and sales records can be stored in an organized manner.

The use of SQL and database concepts such as tables, primary keys, and foreign keys helps maintain accurate and connected information. This system can support Nepali farmers in improving farm management and making data-based decisions.

 

Database Relationship Diagram (Concept)

Farmer

  |

  | 1:M

  |

Crop -------- Sales

  |

  |

Tools

Farmer → Crop: One farmer can manage many crops.
Crop → Sales: One crop can have many sales records.
Farmer → Tools: One farmer can own/manage many tools.

 

Project Work

Project Title: Database Design for “KrishiKranti” Farming App

a. Planning: Identify Entities

For designing the KrishiKranti Farming App database, the following main entities are identified:

  1. Farmer – Stores information about farmers using the application.
  2. Crop – Stores details about different crops cultivated by farmers.
  3. Tools – Stores information about farming equipment and tools.
  4. Sales – Stores records of agricultural product sales.
  5. FarmActivity – Stores farming activities such as planting, irrigation, harvesting, and fertilizer use.

Additional entities can include:

  • Market
  • Supplier
  • Fertilizer
  • Weather Information

The database design can be based on agricultural information and statistics available from Nepal’s agriculture-related government sources. Department of Agriculture

b. Database Design in MySQL

1. Create Database

CREATE DATABASE KrishiKranti;

 

USE KrishiKranti;

Table 1: Farmer

CREATE TABLE Farmer(

    Farmer_ID INT PRIMARY KEY,

    Farmer_Name VARCHAR(50) NOT NULL,

    Phone VARCHAR(15) UNIQUE,

    Address VARCHAR(50) NOT NULL,

    Gender VARCHAR(10),

    Age INT CHECK(Age>=18)

);

Purpose

Stores personal details of farmers.

Table 2: Crop

CREATE TABLE Crop(

    Crop_ID INT PRIMARY KEY,

    Farmer_ID INT,

    Crop_Name VARCHAR(50) NOT NULL,

    Season VARCHAR(20),

    Area DECIMAL(5,2),

    Production INT,

    FOREIGN KEY(Farmer_ID) REFERENCES Farmer(Farmer_ID)

);

Purpose

Stores crop cultivation details and connects crops with farmers.

Table 3: Tools

CREATE TABLE Tools(

    Tool_ID INT PRIMARY KEY,

    Farmer_ID INT,

    Tool_Name VARCHAR(50) NOT NULL,

    Quantity INT DEFAULT 1,

    Condition_Status VARCHAR(20),

    FOREIGN KEY(Farmer_ID) REFERENCES Farmer(Farmer_ID)

);

Purpose

Stores information about farming equipment.

Table 4: Sales

CREATE TABLE Sales(

    Sale_ID INT PRIMARY KEY,

    Crop_ID INT,

    Quantity INT NOT NULL,

    Price DECIMAL(10,2),

    Sale_Date DATE DEFAULT(CURRENT_DATE),

    FOREIGN KEY(Crop_ID) REFERENCES Crop(Crop_ID)

);

Purpose

Stores crop selling information.

Table 5: FarmActivity

CREATE TABLE FarmActivity(

    Activity_ID INT PRIMARY KEY,

    Farmer_ID INT,

    Activity_Name VARCHAR(50),

    Activity_Date DATE,

    Description VARCHAR(100),

    FOREIGN KEY(Farmer_ID) REFERENCES Farmer(Farmer_ID)

);

Purpose

Stores daily farming activities.

Database Relationship

Farmer

   |

   | 1:M

   |

Crop -------- Sales

   |

  

Farmer

   |

   | 1:M

   |

Tools

 

Farmer

   |

   | 1:M

   |

FarmActivity

Normalization

The database follows normalization rules:

First Normal Form (1NF)

  • Each field contains a single value.
  • No repeating groups exist.

Second Normal Form (2NF)

  • All non-key attributes depend on the primary key.

Third Normal Form (3NF)

  • No unnecessary dependency between non-key attributes.

This reduces data duplication and improves database efficiency.

Sample Insert Query

Insert Farmer Data

INSERT INTO Farmer

VALUES

(1,'Ram Thapa','9800000000','Chitwan','Male',35);

Insert Crop Data

INSERT INTO Crop

VALUES

(101,1,'Rice','Summer',2.5,500);

Insert Tool Data

INSERT INTO Tools

VALUES

(201,1,'Tractor',1,'Good');

Insert Sales Data

INSERT INTO Sales

VALUES

(301,101,200,50000,'2026-01-10');

SQL Operations

Retrieve Farmer Information

SELECT * FROM Farmer;

Display Crops of Farmers

SELECT Farmer_Name,Crop_Name

FROM Farmer,Crop

WHERE Farmer.Farmer_ID=Crop.Farmer_ID;

Update Crop Production

UPDATE Crop

SET Production=600

WHERE Crop_ID=101;

Delete Old Sales Record

DELETE FROM Sales

WHERE Sale_ID=301;

Expected Outcomes

After implementing the KrishiKranti database:

  1. Farmers can maintain digital farming records.
  2. Crop production information can be stored and managed easily.
  3. Agricultural tools can be tracked efficiently.
  4. Sales and income records can be maintained.
  5. Data duplication is reduced through normalization.
  6. Farmers can access useful information quickly.
  7. The system supports better farm planning and decision-making.

Conclusion

The KrishiKranti Farming App database provides an organized solution for managing agricultural activities. By using MySQL and relational database concepts, farmer details, crops, tools, sales, and farm activities can be stored securely and efficiently.

The use of primary keys, foreign keys, constraints, and normalization ensures accurate and reliable data management. This database system can help Nepali farmers maintain digital records and improve agricultural productivity. Department of Agriculture

Note: For the project requirement of 25 records in each table, the above structure can be extended by inserting 25 sample records into each entity table.

 

Choose the Correct Answer (MCQ)

a. Which of the following is NOT a function of a DBMS?
i. Data storage     ii. Data retrieval     ✅ iii. Word processing     iv. Data manipulation

b. Which type of key uniquely identifies each record in a table?
i. Foreign Key     ii. Composite Key     ✅ iii. Primary Key     iv. Candidate Key

c. What is the primary purpose of a foreign key?
i. To store only text data.     ii. To uniquely identify each record in its own table.     ✅ iii. To establish and enforce relationships between tables.     iv. To combine multiple fields into one.

d. In a one-to-many relationship between teachers and classes, which statement is true?
i. Each teacher teaches only one class, and each class has only one teacher.     ✅ ii. Each teacher can teach multiple classes, and each class has only one teacher.     iii. Each teacher teaches only one class, and each class can have multiple teachers.     iv. Each teacher can teach multiple classes, and each class can have multiple teachers.

e. Which SQL command is used to add new rows to a table?
i. SELECT     ii. UPDATE     ✅ iii. INSERT     iv. DELETE

f. Which SQL command is used to modify existing data in a table?
i. SELECT     ✅ ii. UPDATE     iii. INSERT     iv. DELETE

g. Which SQL clause is used to specify conditions for data retrieval?
i. SELECT     ii. FROM     ✅ iii. WHERE     iv. ORDER BY

h. What does the SQL LIKE clause help you do?
i. Perform exact matches.     ✅ ii. Search for patterns in data.     iii. Sort the data.     iv. Define data types.

i. Which SQL data type is used for storing only date values (YYYY-MM-DD)?
i. TIME     ii. DATETIME     iii. TIMESTAMP     ✅ iv. DATE

j. Which SQL data type is suitable for storing names and addresses, allowing for variable length?
i. CHAR     ii. TEXT     iii. INT     ✅ iv. VARCHAR

k. What is the primary difference between CHAR(n) and VARCHAR(n) data types?
i. CHAR stores only numeric data, while VARCHAR stores text.     ✅ ii. CHAR has a fixed length, while VARCHAR has a variable length up to n characters.     iii. VARCHAR is used for dates, while CHAR is for text.     iv. There is no significant difference between them.

 

Short Answer Questions

a. Define data and information.

Data: Data is a collection of raw facts, figures, or values that do not have a clear meaning on their own.
Information: Information is processed and organized data that has meaning and is useful for decision-making.

b. What is a primary key?

A primary key is a field or combination of fields that uniquely identifies each record in a table. It cannot contain duplicate or NULL values.

c. What is the foreign key?

A foreign key is a field in one table that refers to the primary key of another table. It is used to create relationships between tables.

d. Define fields and rows.

Field: A field is a column in a database table that stores a specific type of data.
Row: A row is a record in a table that contains complete information about one entity.

e. Differentiate database and DBMS.

Database: A database is an organized collection of related data stored in a structured way.
DBMS: A DBMS is software used to create, manage, store, and retrieve data from databases.

f. Describe RDBMS.

RDBMS (Relational Database Management System) is a type of DBMS that stores data in tables made up of rows and columns. It connects related tables using keys such as primary keys and foreign keys.

g. Why is a DBMS considered more advantageous for managing large amounts of data compared to a simple spreadsheet?

A DBMS is more useful because it can store large amounts of data, provide faster searching, improve security, reduce duplication, and allow multiple users to access data simultaneously.

h. Describe the purpose of primary and foreign keys in a relational database.

A primary key uniquely identifies each record in a table, while a foreign key connects two tables and maintains relationships between related data.

i. Explain the concept of a one-to-many relationship and provide a real-world example.

A one-to-many relationship occurs when one record in a table is related to multiple records in another table.
Example: One customer can place many orders, but each order belongs to only one customer.

j. Briefly describe the roles of DDL and DML in SQL.

DDL (Data Definition Language) is used to create and modify database structures such as tables and databases.
DML (Data Manipulation Language) is used to insert, retrieve, update, and delete data stored in tables.

k. Explain the function of the WHERE clause in a SELECT statement.

The WHERE clause is used to filter records and retrieve only those rows that satisfy a specific condition.

Example:

SELECT * FROM students WHERE Class=10;

l. What are SQL constraints? Why are they important for maintaining data integrity?

SQL constraints are rules applied to table columns to control the type and validity of data entered. They help maintain data accuracy, consistency, and prevent invalid or duplicate data.

m. What is a composite key, and when might it be necessary to use one?

A composite key is a key formed by combining two or more fields to uniquely identify a record. It is used when a single field alone cannot uniquely identify a record.

Example: Student_ID + Subject in a marks table.

 

Long Answer Questions

a. Define DBMS with its advantages.

Definition of DBMS

A Database Management System (DBMS) is application software used to create, store, organize, manage, and retrieve data efficiently from a database. It acts as an interface between users and the database, allowing users to perform operations such as inserting, updating, deleting, and retrieving data.

A DBMS helps maintain data security, accuracy, consistency, and easy access.

Advantages of DBMS

  1. Organized Data Storage:
    DBMS stores data in a systematic and structured way, making it easier to manage and access.
  2. Fast Data Retrieval:
    Users can quickly search and retrieve required information from large amounts of data.
  3. Reduces Data Redundancy:
    It minimizes unnecessary duplication of data by managing information efficiently.
  4. Data Security:
    Provides authentication and access control to protect data from unauthorized users.
  5. Maintains Data Accuracy:
    Ensures data consistency and accuracy through rules and constraints.
  6. Data Sharing:
    Allows multiple users to access and share data simultaneously.
  7. Backup and Recovery:
    Provides facilities to recover data during system failures or accidental loss.

b. What is the importance of primary key in DB? List out its features.

Definition of Primary Key

A primary key is a field or combination of fields that uniquely identifies each record in a database table. It ensures that every record has a unique identity.

Importance of Primary Key

  1. Helps identify each record uniquely in a table.
  2. Prevents duplicate records in a database.
  3. Maintains data accuracy and integrity.
  4. Helps establish relationships between tables.
  5. Improves searching and retrieving data efficiently.

Features of Primary Key

  1. Unique Identification:
    Each record has a different primary key value.
  2. No Duplicate Values:
    Duplicate values are not allowed in the primary key column.
  3. No NULL Values:
    A primary key cannot contain empty values.
  4. Only One Primary Key:
    A table can have only one primary key.
  5. Used for Relationships:
    It helps connect tables using foreign keys.

c. Differentiate between DDL and DML.

DDL (Data Definition Language)

DML (Data Manipulation Language)

DDL is used to define and manage database structures.

DML is used to manage data stored inside tables.

It works with database objects like tables and databases.

It works with records and values stored in tables.

It creates, modifies, and deletes database structures.

It inserts, retrieves, updates, and deletes data.

Changes are automatically committed.

Changes can be rolled back before COMMIT.

It does not directly handle table data.

It directly handles the content of tables.

Examples: CREATE, ALTER, DROP, TRUNCATE.

Examples: INSERT, SELECT, UPDATE, DELETE.

d. Show difference between Table and Query.

Table

Query

A table is a structure used to store data in rows and columns.

A query is a command used to perform operations on database data.

It permanently stores records in a database.

It retrieves or modifies data temporarily based on requirements.

Rows represent records and columns represent fields.

It is written using SQL commands.

Used for organizing and storing information.

Used for searching, inserting, updating, or deleting information.

Example: Student table storing student details.

Example: SELECT * FROM Student;

e. What is the relationship in DBMS? Explain its types.

Definition of Relationship

A relationship in DBMS is a logical connection between two or more tables using common fields, usually primary keys and foreign keys. Relationships help organize data, reduce duplication, and improve data management.

Types of Relationships

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

In this relationship, one record in one table is connected to only one record in another table.

Example:
One person has one passport, and one passport belongs to one person.

2. One-to-Many (1:M) Relationship

In this relationship, one record in one table is related to many records in another table.

Example:
One customer can place many orders, but each order belongs to only one customer.

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

In this relationship, many records in one table are related to many records in another table. It requires a third table called a junction table.

Example:
Many students can enroll in many courses.

f. What is a Query? List out its importance in DBMS.

Definition of Query

A query is a request or command sent to a database to perform operations such as retrieving, inserting, updating, or deleting data.

Queries are written using SQL (Structured Query Language) and are the main way users communicate with a database.

Importance of Query in DBMS

  1. Data Retrieval:
    Helps users quickly find required information from databases.
  2. Data Modification:
    Allows users to add, update, and delete records.
  3. Data Filtering:
    Helps display only required data using conditions.
  4. Data Sorting:
    Allows arranging data in a specific order.
  5. Report Generation:
    Helps create meaningful reports from database information.
  6. Efficient Data Management:
    Makes handling large amounts of data easier and faster.

g. What is a report in the context of MySQL and what is its purpose?

Definition of Report

A report in MySQL is an organized presentation of data retrieved from one or more database tables using SQL queries.

MySQL provides structured data, which can be used to create reports using reporting tools such as Power BI, Tableau, and Google Data Studio.

Purpose of Reports

  1. To present data in a meaningful format.
  2. To analyze collected information.
  3. To support planning and decision-making.
  4. To summarize large amounts of data.
  5. To help users understand database information easily.

h. Describe report with its features.

Definition of Report

A report is a structured presentation of database information that displays extracted data in a readable and meaningful format. Reports are generated using SQL queries and help users analyze information effectively.

Features of Reports

  1. Organized Data Presentation:
    Displays information in a clear and structured format.
  2. Data Filtering:
    Shows only required information based on specific conditions.
  3. Summarization:
    Uses functions like SUM(), AVG(), and COUNT() to summarize data.
  4. Multi-Table Combination:
    Combines information from multiple tables to create useful reports.
  5. Timely Access:
    Provides updated information whenever required.
  6. Easy Export:
    Allows reports to be exported for sharing and further analysis.

 

 

 

 

Table

Query

Report

A table is a basic structure used to store data in a database.

A query is a command used to retrieve, insert, update, or delete data from a database.

A report is an organized presentation of data retrieved from a database.

It stores data permanently in rows and columns.

It performs operations on stored data according to user requirements.

It displays processed data in a meaningful and readable format.

Rows represent records and columns represent fields.

It is written using SQL commands.

It is usually created using query results.

Used for organizing and storing raw data.

Used for searching, filtering, and modifying data.

Used for analyzing data and supporting decision-making.

Data is entered and stored directly in tables.

Data is not permanently stored; it only displays the required results.

It presents existing data from tables or queries.

Example: Student table storing student details.

Example: SELECT * FROM Students WHERE Class=10;

Example: Student performance report showing marks and grades.

 

SQL Queries

a. Create a new database named CompanyData

CREATE DATABASE CompanyData;

b. Create a table named Employees

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Salary DECIMAL(10,2)

);

c. Add a new column Email to the Employees table

ALTER TABLE Employees

ADD Email VARCHAR(100);

d. Modify the Salary column to have a default value of 25000

ALTER TABLE Employees

MODIFY Salary DECIMAL(10,2) DEFAULT 25000;

e. Rename the Employees table to StaffMembers

RENAME TABLE Employees TO StaffMembers;

f. Insert a new record into the StaffMembers table

INSERT INTO StaffMembers(EmployeeID, FirstName, LastName, Salary)

VALUES (101, 'Sampada', 'Bhattrai', 30000.50);

g. Retrieve all columns and all rows from the table

SELECT * FROM StaffMembers;

h. Retrieve only FirstName and Salary columns

SELECT FirstName, Salary

FROM StaffMembers;

i. Increase the salary of all employees by 10%

UPDATE StaffMembers

SET Salary = Salary * 1.10;

j. Delete the employee with EmployeeID 103

DELETE FROM StaffMembers

WHERE EmployeeID = 103;

k. Delete all records but keep the table structure intact

TRUNCATE TABLE StaffMembers;

By on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)