Database Management System (DBMS) | Computer Operator Loksewa Preparation

Computer Operator Loksewa

Database System

Complete notes for NTC / PSC Computer Operator preparation

Topics Covered

✦ Data & Database ✦ DBMS & Functions ✦ Types of Databases ✦ Tables, Fields, Records ✦ Relationships ✦ Data Types in MS Access ✦ MS Access Operations ✦ Queries, Forms, Reports

1. Data and Database

Data refers to raw facts, figures, or symbols that can be processed to generate useful information. It can be:

• Text — Names, Addresses
• Numbers — Phone Numbers, Prices
• Images — Photos, Graphs
• Audio/Video — Music, Movies

A Database is an organized collection of data stored and managed electronically. It allows easy storage, retrieval, updating, and deletion of data. Data is typically organized into tables consisting of rows and columns.

Data Organization

Stored in tables divided into rows and columns

Structured Data

Each column has a specific data type (integer, string, date)

Data Integrity

Ensures data is accurate, consistent, and error-free

2. Database Management System (DBMS)

A DBMS is a software application that facilitates the creation, management, and maintenance of databases. It provides an interface between the database and end-users, ensuring data integrity, security, and efficient storage management.

Type	Examples	Use Case
Relational (RDBMS)	MySQL, PostgreSQL, Oracle, SQL Server	Structured data with tables & relations
NoSQL DBMS	MongoDB, Cassandra, CouchDB	Large-scale, unstructured/flexible data

Functions of a DBMS

• Data Storage — Stores data using tables, indexes, and views
• Data Retrieval — Allows querying via SQL
• Data Update — Supports insertion, modification, and deletion
• Data Security — Controls access via authentication & authorization
• Data Integrity — Enforces constraints (primary/foreign keys)
• Concurrency Control — Manages simultaneous multi-user access
• Backup and Recovery — Ensures data can be restored after failure

3. Types of Databases

Type	Description	Example
Relational	Data in tables linked by primary/foreign keys	MySQL, Oracle
NoSQL	Handles large-scale unstructured data	MongoDB, Cassandra
Object-Oriented	Data stored as objects (like OOP)	ObjectDB
Hierarchical	Tree structure — each record has one parent	IBM IMS
Network	Graph structure with complex relationships	IDS

4. Components of a DBMS

Database Engine
Core software for storage, retrieval, modification

Database Schema
Blueprint defining data structure and relationships

Query Processor
Interprets and executes SQL queries

Data Dictionary
Stores metadata about tables and columns

Transaction Management
Ensures ACID properties and data integrity

User Interface
CLI or GUI for user interaction

5. Tables, Fields, and Records

Table

A collection of related data in rows and columns. Represents an entity (e.g., Customers, Orders).

Field (Column)

A single attribute of the entity. Has a specific data type (VARCHAR, INT, DATE, etc.).

Record (Row)

A single data entry holding values for all fields. Uniquely identified by the primary key.

Example — Customers table:

CustomerID (PK)	FirstName	LastName	Email	PhoneNumber
1	Rajan	Yadav	rajan@example.com	9845001234
2	Sita	Sharma	sita@example.com	9801234567

6. Relationships in a Database

Relationships link tables together to maintain data consistency. Defined using Primary Keys (PK) and Foreign Keys (FK).

Type	Description	Example
One-to-One (1:1)	Each record in A ↔ one record in B	Person ↔ Passport
One-to-Many (1:M)	One record in A → many records in B	Customer → Orders
Many-to-Many (M:M)	Many records in A ↔ many records in B (needs junction table)	Students ↔ Courses

Indexing

An index improves data retrieval speed at the cost of extra storage. Types include Primary Index, Secondary Index, Unique Index, and Composite Index.

7. Data Types in MS Access

Choosing the correct data type ensures efficient storage and data integrity.

Data Type	Purpose	Example
Short Text	Up to 255 characters	Names, Addresses
Long Text (Memo)	Up to 65,536 characters	Descriptions, paragraphs
Number	Numeric values for calculations	100, 250.75, -45
Date/Time	Dates and time values	12/25/2024, 10:30 AM
Currency	Monetary values (4 decimal places)	$100.50, Rs 5000.75
AutoNumber	Auto-generated unique ID (used as PK)	1, 2, 3, 4…
Yes/No	Boolean True/False values	Yes / No
OLE Object	Embedded files (images, PDFs, Word docs)	Company logo
Attachment	Multiple files per record (Access 2007+)	Employee photos
Hyperlink	Web addresses, email links, file paths	https://example.com
Calculated Field	Result of a formula (Access 2010+)	Total = Qty × Price
Lookup Wizard	Dropdown from another table or fixed list	Male / Female / Other

8. MS Access — Table Operations

Creating a Table (Design View)

1. Open MS Access → create or open a database
2. Click Table Design
3. Enter field names and select data types
4. Right-click a field → Set Primary Key
5. Press Ctrl + S and give the table a name

Example — Employees table design:

Field Name	Data Type	Description
EmployeeID	AutoNumber (PK)	Unique ID for each employee
FirstName	Short Text	Employee's first name
LastName	Short Text	Employee's last name
DOB	Date/Time	Date of birth
Salary	Currency	Employee's salary
IsActive	Yes/No	Active / Inactive status

9. Establishing Relationships in MS Access

1. Go to Database Tools → click Relationships
2. Click Show Table → add the required tables
3. Drag the Primary Key from one table to the Foreign Key in another
4. In the Edit Relationships dialog, select Enforce Referential Integrity
5. Click Create and save

Referential Integrity ensures that you cannot add an order for a non-existent customer, and prevents orphan records when a parent record is deleted.

10. Formatting and Validating Field Data

Common Validation Rules

Field	Validation Rule	Meaning
Age	>=18	Age must be 18 or older
Price	>0	Price must be greater than 0
Date of Birth	<=Date()	Must be past or current date
Phone	Like "98#########"	Must start with 98, 10 digits

11. Inserting, Modifying, and Deleting Data

INSERT

INSERT INTO Customers
  (CustomerID, Name, Email)
VALUES
  (1, 'Ram Bahadur',
   'ram@example.com');

UPDATE

UPDATE Customers
SET Email =
  'new@example.com'
WHERE CustomerID = 1;

DELETE

DELETE FROM Customers
WHERE CustomerID = 1;

12. Queries, Forms, and Reports in MS Access

Query

Used to retrieve and filter data from tables.

Create: Create → Query Design → select table → drag fields → set criteria → Run

SQL example:
SELECT Name, Price FROM Products WHERE Price > 1000;

Form

A user-friendly interface for entering and managing data.

Create: Create → Form → select table → switch to Form View → enter data → Save

Customize layout, fonts, and colors in Design View.

Report

Displays and prints formatted data.

Create: Create → Report Wizard → select data source → choose fields → set sort/group → Finish

Adjust headers and formatting in Design View.

13. Advantages of a DBMS

• Data Consistency — Constraints and validation rules keep data uniform
• Reduced Redundancy — Centralized storage minimizes duplication
• Improved Security — Authentication, authorization, and encryption
• Multi-user Support — Multiple users can access simultaneously without conflicts
• Backup & Recovery — Built-in tools restore data after failure
• Scalability — Can grow to accommodate larger data volumes

Quick Revision — Key Terms

DBMS — manages databases

Primary Key — unique row ID

Foreign Key — links to another table's PK

SQL — Structured Query Language

ACID — Atomicity, Consistency, Isolation, Durability

Referential Integrity — no orphan records

Index — speeds up data retrieval

AutoNumber — auto-generated PK field