Chapter:DBMS

Chapter:DBMS

 

 What do you mean by DBMS and what are the advantages of DBMS over Traditional File system?

A DBMS (Database Management System) is a software system used to create, store, manage, and retrieve data from a database efficiently.

It provides an interface between users and the database, allowing easy access, modification, and control of data.

Example: MySQL, Microsoft Access

Advantages of DBMS over Traditional File System

A traditional file system stores data in separate files without proper organization or control. DBMS improves this in many ways:

1. Reduced Data Redundancy

  • DBMS avoids storing duplicate data, reducing storage space and inconsistency.

2. Data Consistency

  • Ensures that data remains accurate and updated across the database.

3. Data Sharing

  • Multiple users can access and share the same data easily.

4. Security of Data

  • Provides access control and user permissions to protect data.

5. Easy Data Access

  • Data can be quickly retrieved using queries.

6. Backup and Recovery

  • DBMS provides automatic backup and recovery in case of system failure.

7. Data Integrity

  • Maintains accuracy and correctness of data through rules and constraints.

8. Reduced Data Inconsistency

  • Changes made in one place are automatically updated everywhere. 

What is RDBMS? Define tables , field and records in the RDBMS.

An RDBMS (Relational Database Management System) is a type of database management system that stores data in the form of tables (relations) and allows relationships between these tables.

It is based on the relational model and uses keys to connect data between tables.

Examples: MySQL, Oracle Database, Microsoft Access

1. Table

A table is a collection of related data organized in rows and columns.

  • It represents an entity (e.g., Student, Employee)
  • Example: Student Table

Roll No

Name

Marks

1

Ram

80

2

Sita

90

2. Field

A field is a column in a table that represents a specific attribute of data.

  • It defines the type of information stored.
  • Example in Student table:
    • Roll No
    • Name
    • Marks

👉 Each field has a specific data type (number, text, date, etc.)

3. Record

A record is a row in a table that contains complete information about a single entry.

  • Example:
    • (1, Ram, 80) is one record
    • (2, Sita, 90) is another record

Summary

Term

Meaning

Table

Collection of related data in rows and columns

Field

A column representing an attribute

Record

A row containing complete data of one entity

 

Define SQL in DBMS? Write a query in SQL to retrive cust_id, cust_name, order_item and order_qty from the tables tbl_customer and tbl_order where cust _id is a common field in both tables.

SQL (Structured Query Language) is a standard language used in a DBMS (Database Management System) to create, store, retrieve, update, and delete data from databases.

It is used to communicate with relational databases such as MySQL, Oracle Database, and Microsoft SQL Server.

SQL Query (Join Example)

To retrieve cust_id, cust_name, order_item, and order_qty from tbl_customer and tbl_order where cust_id is common in both tables, we use an INNER JOIN.

SQL Query:

SELECT tbl_customer.cust_id,
       tbl_customer.cust_name,
       tbl_order.order_item,
       tbl_order.order_qty
FROM tbl_customer
INNER JOIN tbl_order
ON tbl_customer.cust_id = tbl_order.cust_id;

Explanation:

  • SELECT → chooses required columns
  • FROM tbl_customer → first table
  • INNER JOIN tbl_order → combines both tables
  • ON tbl_customer.cust_id = tbl_order.cust_id → matching common field 

What does it mean to format and validate field data in DBMS? Illustrate with suitable example.

Meaning of Formatting and Validating Field Data in DBMS

In a DBMS (Database Management System), formatting and validating field data are important processes used to ensure that data is correct, consistent, and properly structured before storing it in a database.

Example systems: MySQL, Microsoft Access

1. Formatting Field Data

Formatting means arranging or presenting data in a specific standard structure.

Purpose:

  • Makes data consistent and readable
  • Ensures uniform storage of information

Example:

  • Date format:
    • Incorrect: 20/6/26, June 20 2026
    • Correct (formatted): 2026-06-20 (YYYY-MM-DD)
  • Phone number format:
    • Incorrect: 981234567
    • Correct: +977-981234567

2. Validating Field Data

Validation means checking whether the data entered is correct, accurate, and follows rules before it is stored in the database.

Purpose:

  • Prevents incorrect or invalid data entry
  • Maintains data integrity

Example:

  • Age field:
    • Rule: Age must be between 0 and 120
    • Invalid: -5, 200
    • Valid: 25
  • Email field:
    • Must contain @ symbol
    • Invalid: ramgmail.com
    • Valid: ram@gmail.com

Example in Table Form

Name

Age

Email

Ram

25

ram@gmail.com

Validation Rules:

  • Age → numeric and between 0–120
  • Email → must include “@”

Formatting Rules:

  • Name → proper case (Ram, not RAM or ram)
  • Date → YYYY-MM-DD format

 

What are DDL and DML in SQL? Explain the most commonly used DDL and DML command used in SQL.

DDL and DML in SQL

In SQL (Structured Query Language) used in a DBMS like MySQL, commands are mainly divided into DDL (Data Definition Language) and DML (Data Manipulation Language).

1. DDL (Data Definition Language)

DDL consists of commands used to define, create, and modify the structure of database objects like tables, databases, and schemas.

Common DDL Commands:

a) CREATE

  • Used to create a new table or database.

Example:

CREATE TABLE Student (
    ID INT,
    Name VARCHAR(50),
    Marks INT
);

b) ALTER

  • Used to modify an existing table structure (add, delete, or modify columns).

Example:

ALTER TABLE Student ADD Age INT;

c) DROP

  • Used to delete a table or database permanently.

Example:

DROP TABLE Student;

d) TRUNCATE

  • Removes all records from a table but keeps the structure.

Example:

TRUNCATE TABLE Student;

2. DML (Data Manipulation Language)

DML consists of commands used to manage and manipulate data stored in tables.

Common DML Commands:

a) INSERT

  • Used to add new records into a table.

Example:

INSERT INTO Student VALUES (1, 'Ram', 85);

b) UPDATE

  • Used to modify existing records.

Example:

UPDATE Student
SET Marks = 90
WHERE ID = 1;

c) DELETE

  • Used to remove specific records from a table.

Example:

DELETE FROM Student
WHERE ID = 1;

d) SELECT (sometimes included in DML)

  • Used to retrieve data from a table.

Example:

SELECT * FROM Student;

 

What are the functions of queries, forms and report in the application software and DBMS while createing viewing, updating and deleting data via the user interface?

In an application software and DBMS such as Microsoft Access, queries, forms, and reports are important tools used to manage data through a user-friendly interface for creating, viewing, updating, and deleting data.

1. Functions of Queries

A query is used to retrieve and manipulate data from one or more tables.

Functions:

  • Create data view: Extract specific data from tables.
  • View data: Display filtered or selected records.
  • Update data: Modify multiple records using update queries.
  • Delete data: Remove unwanted records using delete queries.
  • Search data: Find specific information quickly.

Example:

  • Show students who scored more than 80 marks.

2. Functions of Forms

A form is a user interface used to enter, edit, and delete data easily.

Functions:

  • Create data: Insert new records into tables.
  • View data: Display records in a user-friendly format.
  • Update data: Edit existing records easily.
  • Delete data: Remove records through buttons or controls.
  • Improve usability: Makes data entry simple for users.

Example:

  • A student admission form used to enter student details.

3. Functions of Reports

A report is used to present data in a formatted and printable way.

Functions:

  • View summarized data: Displays organized information.
  • Print data: Produces hard copies of records.
  • Analyze data: Shows totals, averages, and grouped data.
  • Read-only output: Cannot directly modify data.

Example:

  • A mark sheet report showing student results.

List out three features of primary key. Write a query to increase the salary of the teacher by 15% for the following relation and display all the record
t_id    t_name    t_address    t_faculty    t_salary
101    suntali    Kathmandu    Computer    40,000
102    Hari    Pokhara    Electronics    30,000
103 Suvan    Biratnagar    Electrical    35,000
104    Harkey    Kathmandu    Computer    40,000

1. Features of Primary Key (3 marks)

A Primary Key in a DBMS such as MySQL has the following main features:

  1. Uniqueness
    • Each value in a primary key column must be unique.
    • No two records can have the same primary key value.
  2. No Null Values
    • A primary key cannot contain NULL (empty) values.
    • Every record must have a valid key value.
  3. Identifies Records Uniquely
    • It is used to uniquely identify each row in a table.
    • It ensures that each record can be accessed individually.

2. SQL Query to Increase Salary by 15% and Display All Records

To increase the salary of all teachers by 15%, we use the UPDATE command and then display all records using SELECT.

Step 1: Update salary

UPDATE teacher
SET t_salary = t_salary + (t_salary * 15 / 100);

Step 2: Display all records

SELECT * FROM teacher;

Explanation:

  • t_salary + (t_salary * 15 / 100) increases salary by 15%
  • UPDATE modifies all rows in the table
  • SELECT * displays the updated table

Define Primary Key, Foreign key and diffferent datatype in database management system. Write a query in SQL to rerieve dr_id, dr_name, pat_id, pat_name from the table tbl_doctor and tbl_patient where dr_id is a common field in both table

1. Primary Key

A Primary Key is a field (or combination of fields) in a database table that uniquely identifies each record.

Features:

  • Must be unique
  • Cannot be NULL
  • Only one primary key per table

Example: dr_id in a doctor table.

2. Foreign Key

A Foreign Key is a field in one table that refers to the Primary Key of another table.

Features:

  • Creates relationship between two tables
  • Can have duplicate values
  • Helps maintain referential integrity

Example: dr_id in tbl_patient referring to tbl_doctor.

3. Data Types in DBMS

Data types define the type of data a field can store in a database such as MySQL.

Common Data Types:

  1. INT (Integer)
    • Stores whole numbers
    • Example: 1, 100, 500
  2. VARCHAR (Variable Character)
    • Stores text or string
    • Example: "Ram", "Kathmandu"
  3. CHAR
    • Stores fixed-length text
  4. DATE
    • Stores date values
    • Example: 2026-06-20
  5. FLOAT / DECIMAL
    • Stores decimal numbers
    • Example: 45.5, 99.99

4. SQL Query (Join Operation)

To retrieve dr_id, dr_name, pat_id, pat_name from tbl_doctor and tbl_patient where dr_id is common:

SELECT tbl_doctor.dr_id,
       tbl_doctor.dr_name,
       tbl_patient.pat_id,
       tbl_patient.pat_name
FROM tbl_doctor
INNER JOIN tbl_patient
ON tbl_doctor.dr_id = tbl_patient.dr_id;

Explanation:

  • INNER JOIN combines both tables
  • Matching is done using dr_id
  • Only matching records from both tables are displayed

Write notes on Data redudancy , consistency

Data Redundancy

Data redundancy refers to the unnecessary duplication of data in a database or data storage system.

Key Points:

  • The same data is stored in multiple places.
  • It often happens in poorly designed databases.
  • Example: A customer’s address stored in both Customer and Order tables.

Types of Redundancy:

  • Controlled redundancy: Intentional duplication for performance or backup.
  • Uncontrolled redundancy: Unintentional duplication due to poor design.

Advantages:

  • Can improve data availability and query speed (in some cases).
  • Useful in distributed systems for backup.

Disadvantages:

  • Wastes storage space.
  • Increases chances of data inconsistency.
  • Makes updates difficult (must change data in multiple places).

Data Consistency

Data consistency means that data remains accurate, correct, and the same across all databases or systems after any operation (insert, update, delete).

Key Points:

  • Ensures that all copies of data match each other.
  • Important in database management systems (DBMS).
  • Maintains data integrity during transactions.

Example:

If a student’s grade is updated in one table, it should be updated everywhere it is stored.

Types of Consistency:

  • Strong consistency: All users see the same data immediately after update.
  • Eventual consistency: Data becomes consistent after some time (common in distributed systems).

Importance:

  • Prevents contradictions in data.
  • Improves reliability of systems.
  • Essential for financial, banking, and critical applications.

Relationship Between Redundancy and Consistency

  • High redundancy increases the risk of inconsistency.
  • Good database design (normalization) reduces redundancy and improves consistency.
0 Komentar

Subscribe to: Posts (Atom)

Practical Questions