Chapter 5: Database System | Shubha Yatra Academy

Chapter 5
Database System

Page 1
 Chapter 5: Database System | Shubha Yatra Academy

5.1 Introduction to Data, Database, and DBMS

What is Data?
Data refers to raw facts, figures, or values that have no meaning on their own without context. Data is
essentially unprocessed information that, when organized and combined with context, becomes useful and
meaningful.
• Examples of Data: A number like 12345, a name like "John Doe", a date like 2024-12-16, a
temperature reading like 37°C
• Key Point: Data alone is meaningless. For example, "100" is just a number. But when we say
"100 marks in Math", it becomes meaningful information.

Remember
Data = Raw facts without context
Information = Data + Context + Meaning
Example: "25" is data. "Age: 25 years" is information.

What is a Database?
A database is a structured and organized collection of related data that is stored in a way that allows
for easy access, management, retrieval, and updating. It organizes data systematically so that users can
efficiently store and retrieve information when needed.
• Example: A customer database might store information like customer names, contact numbers,
email addresses, and purchase histories — all organized in tables.
• Example: A school database stores student names, roll numbers, grades, attendance records, and
fee details.
• Example: A hospital database stores patient records, doctor details, appointment schedules, and
billing information.

Feature Description
Organized Data is arranged in tables, rows, and columns for easy access.
Shared Multiple users can access the same database at the same time.
Integrated Data from different sources is combined into a single system.
Persistent Data remains stored even when the computer is turned off.
Secure Access to data can be restricted to authorized users only.

Page 2
 Chapter 5: Database System | Shubha Yatra Academy

What is a DBMS (Database Management System)?

A Database Management System (DBMS) is a software tool specifically designed to create, manage,
and manipulate databases. It acts as an interface between the user and the database, allowing users to
Create, Read, Update, and Delete (CRUD) data in an organized and secure manner.

The DBMS is responsible for:

• Maintaining data integrity (data is accurate and consistent)
• Handling queries (responding to user requests for data)
• Ensuring security (preventing unauthorized access)
• Managing concurrent access (multiple users working at the same time)
• Providing backup and recovery mechanisms

Popular DBMS Software Examples:

DBMS Software Type Common Use
Microsoft Access Desktop/Relational Small businesses, personal use, education
MySQL Server-based/Relational Web applications, online systems
Oracle Enterprise/Relational Large businesses, banking, government
PostgreSQL Open-source/Relational Complex applications, data warehousing
SQL Server Enterprise/Relational Corporate systems, Microsoft ecosystem
MongoDB NoSQL/Document Big data, real-time web applications

Functions of a DBMS
Function Description
Data Storage Organizes and stores data efficiently in a structured format using tables.
Executes commands to retrieve, insert, modify, or delete data based on user
Query Execution
input. Users can write SQL queries or use graphical tools.
Provides access control features like user accounts, passwords, and
Security
permissions to protect data from unauthorized access.
Ensures the data is accurate, consistent, and follows predefined rules
Data Integrity
(constraints) across the system.
Creates backup copies of the database and can restore data in case of system
Backup & Recovery
failure or data loss.
Manages simultaneous access by multiple users, ensuring data consistency
Concurrency Control
and preventing conflicts.
Changes to the database structure do not affect the applications that use the
Data Independence
data.

Page 3
 Chapter 5: Database System | Shubha Yatra Academy

Exam Tip
DBMS functions are frequently asked in exams. Remember: Storage, Query, Security, Integrity, Backup,
Concurrency, Independence.
MS Access is the DBMS tool covered in this syllabus.

5.2 Basic Concepts of Tables, Fields, Records, Relationships, and

Indexing

In a relational database (like Microsoft Access), data is organized into tables. Understanding the key
components of a table is essential for working with any database system.

Term Description Example

A collection of related data organized in rows
Customer Table, Product
Table (records) and columns (fields). Each table stores
Table, Orders Table
data about one specific subject or topic.
A single column in a table that represents a
Name, Age, Email, Phone,
Field (Column) specific attribute or data type. Each field has a
Address
name and a data type (text, number, date, etc.).
A single row in a table that contains all the data
John Doe, 25,
Record (Row) about one specific entity or item. Each record is
john@[Link]
unique.
A special field (or combination of fields) that
Customer_ID, Student_ID,
Primary Key uniquely identifies each record in a table. No two
Employee_ID
records can have the same primary key value.
A field in one table that links to the primary key of
Customer_ID in Orders table
Foreign Key another table. It creates a connection between two
linking to Customers table
related tables.

Example: Student Table in MS Access

Below is an example of how data looks in a database table:

Student_ID Name Age Class Email

101 Ram Sharma 18 12-A ram@[Link]
102 Sita Thapa 17 12-B sita@[Link]
103 Hari Gurung 18 12-A hari@[Link]
104 Gita Rai 16 11-C gita@[Link]

Page 4
 Chapter 5: Database System | Shubha Yatra Academy

In this table: Student_ID is the Primary Key, each row is a Record, and each column (Name, Age, etc.) is
a Field.

Relationships
A relationship is a link or connection between two or more tables based on a common field.
Relationships allow data in different tables to be connected, reducing duplication and improving data
organization.

Types of Relationships:

Relationship Type Description Example

A single record in Table A is linked to
Each person has one passport.
One-to-One (1:1) exactly one record in Table B, and vice
Person table ↔ Passport table.
versa.
A single record in Table A can be linked
One customer can place many
to multiple records in Table B, but each
One-to-Many (1:N) orders. Customer table →
record in B links to only one record in A.
Orders table.
This is the most common relationship.
Students enroll in many courses;
Multiple records in Table A can be linked
Many-to-Many each course has many students.
to multiple records in Table B. Usually
(M:N) Needs an Enrollment junction
handled using a junction (bridge) table.
table.

MS Access Tip
In MS Access, you create relationships using the Relationships window.
Go to Database Tools tab > Relationships to open this window.
Drag a field from one table and drop it on the matching field in another table.

Indexing
An index is a special data structure that speeds up the retrieval of data from a database table. It works
similar to an index in a textbook — instead of reading every page, you look up the index to find the exact
page quickly.

How Indexing Works:

• An index creates an ordered structure based on one or more fields.
• When you search for data, the DBMS uses the index to quickly locate the records instead of
scanning every row.
• Primary Key fields are automatically indexed in MS Access.
• You can also create indexes on other frequently searched fields like Name or Email.

Page 5
 Chapter 5: Database System | Shubha Yatra Academy

Advantages: Faster searches, faster sorting, improved query performance.

Disadvantages: Uses extra storage space, slows down data insertion/update operations (because the index
must be updated too).

Quick Comparison
Without Index: Database scans ALL records to find a match (slow for large tables).
With Index: Database jumps directly to matching records (fast lookup).
Think of it like a book: without an index, you flip through every page; with an index, you go directly to the
right page.

5.3 Introduction to Database Applications

Database applications are software tools that interact with databases for storing, managing, and
analyzing data. These applications make it easier to create, modify, and retrieve data, and they provide
user-friendly interfaces for interacting with the database.

Microsoft Access is one of the most popular desktop database applications. It is part of the Microsoft
Office suite and provides a graphical interface for creating and managing databases without needing to
write complex code.

Key Features of MS Access:

• Tables — Store data in rows and columns
• Queries — Search, filter, and analyze data
• Forms — User-friendly screens for data entry
• Reports — Formatted, printable summaries of data
• Macros & Modules — Automate repetitive tasks

Important
MS Access uses the .accdb file format (or older .mdb format).
It is a Relational Database Management System (RDBMS).
It supports SQL (Structured Query Language) for advanced queries.

Page 6
 Chapter 5: Database System | Shubha Yatra Academy

5.3.1 Data Types in MS Access

Every field in a database table must have a data type assigned to it. The data type determines what kind of
values can be stored in that field. Choosing the correct data type is important for data accuracy, storage
efficiency, and validation.

Data Type Description Example

Stores alphanumeric characters (letters,
Short Text numbers, and symbols). Maximum 255 "John Doe", "KTM-001"
characters.
Stores large amounts of text. Can hold up to Detailed descriptions,
Long Text (Memo)
65,535 characters. comments, notes
Stores numeric values — either integer or
Number 123, 45.67, -10
decimal. Used for calculations.
Date/Time Stores dates and times in various formats. 2024-12-16, 10:30 AM
Stores monetary values with a fixed decimal
Currency format. Prevents rounding errors in financial $500.00, Rs. 1,500.00
calculations.
Automatically generates a unique sequential
AutoNumber number for each new record. Often used as the 1, 2, 3, 4, ...
Primary Key.
Stores only two values: True/False, Yes/No,
Yes/No (Boolean) True, False
or On/Off.
Stores embedded objects like images,
OLE Object A photo, a PDF document
documents, spreadsheets, or audio/video files.
Stores web addresses (URLs), email
Hyperlink [Link]
addresses, or links to files.
Allows attaching multiple files (images, Multiple photos attached to
Attachment
documents) to a single record. a product record
Creates a dropdown list that lets users choose Category dropdown:
Lookup Wizard
a value from another table or a predefined list. Electronics, Clothing, Food

Choosing the Right Data Type

Use Short Text for names, codes, and labels (not for numbers used in calculations).
Use Number for values you want to calculate (age, quantity, marks).
Use Currency for money values to avoid rounding errors.
Use AutoNumber for automatically generating unique IDs.
Use Yes/No for fields with only two possible answers.

Page 7
 Chapter 5: Database System | Shubha Yatra Academy

5.3.2 Creating, Modifying, and Deleting Tables in MS Access

Creating a Table
Tables are the foundation of any database. In MS Access, you can create tables in two ways:

Method 1: Datasheet View (Quick)

1. Open MS Access and create a new blank database (or open an existing one).
2. A default table (Table1) will appear in Datasheet View.
3. Click on "Click to Add" to add new fields (columns).
4. Choose the data type for each field.
5. Rename fields by double-clicking the column header.
6. Save the table with a meaningful name (e.g., "Students", "Products").

Method 2: Design View (Recommended for detailed control)

1. Go to Create tab > Table Design.
2. In the Design View, enter the Field Name for each column.
3. Select the appropriate Data Type for each field (Short Text, Number, Date/Time, etc.).
4. Add a Description (optional) to explain the purpose of each field.
5. Set the Primary Key: Right-click on the field that will uniquely identify each record and select
"Primary Key".
6. Set Field Properties at the bottom panel (Field Size, Format, Required, Default Value, etc.).
7. Save the table with a descriptive name.

Best Practices for Table Design

Always set a Primary Key — it ensures each record is unique.
Use meaningful field names (e.g., "FirstName" instead of "F1").
Choose the most appropriate data type for each field.
Keep tables focused on one subject (e.g., separate Customers and Orders into different tables).

Modifying a Table
You can modify an existing table structure to add, change, or remove fields:

1. Open the table in Design View (right-click the table name > Design View).
2. To add a new field: Click on an empty row and type the new field name and select its data type.
3. To change a data type: Click on the Data Type column of the field you want to modify and
select a new type.
4. To rename a field: Click on the Field Name and edit it directly.
5. To change field size: Adjust the Field Size property in the Field Properties panel at the bottom.
6. To delete a field: Right-click on the field row and select "Delete Rows".
7. Save the changes (Ctrl + S).

Page 8
 Chapter 5: Database System | Shubha Yatra Academy

Deleting a Table
If a table is no longer needed, you can remove it from the database:

1. In the Navigation Pane (left sidebar), find the table you want to delete.
2. Right-click on the table name.
3. Select "Delete" from the context menu.
4. Confirm the deletion when prompted.

Warning
Deleting a table permanently removes all its data and structure.
If other tables have relationships with the deleted table, those relationships will also be broken.
Always create a backup before deleting important tables.

Page 9
 Chapter 5: Database System | Shubha Yatra Academy

5.3.3 Establishing Relationships Among Tables

Relationships connect tables together based on common fields. This is a core feature of relational
databases like MS Access. By establishing relationships, you can:
• Avoid data duplication (store data in one place and reference it from other tables)
• Ensure data consistency (changes in one table are reflected in related tables)
• Create powerful queries that combine data from multiple tables

Steps to Create a Relationship in MS Access:

1. Go to the Database Tools tab on the Ribbon.

2. Click Relationships.
3. The Relationships window will open. If it is your first time, the Show Table dialog box will
appear.
4. Add the tables you want to relate by selecting them and clicking Add.
5. Close the Show Table dialog.
6. Identify the common field (e.g., Customer_ID exists in both "Customers" and "Orders" tables).
7. Drag the primary key field from one table and drop it on the corresponding foreign key field in
the other table.
8. The Edit Relationships dialog box will appear. Here you can:
• Check "Enforce Referential Integrity" — prevents orphan records (e.g., an order without a
valid customer).
• Check "Cascade Update Related Fields" — if the primary key value changes, all related
foreign key values are updated automatically.
• Check "Cascade Delete Related Records" — if a record in the primary table is deleted, all
related records in the foreign table are also deleted.
9. Click Create to establish the relationship.
10. A line connecting the two tables will appear in the Relationships window, showing the
relationship type (1:1 or 1:∞).

Referential Integrity
Referential Integrity is a set of rules that ensures relationships between tables remain consistent.
When enforced, you cannot add a record in the foreign table that has no matching record in the primary table.
Example: You cannot add an order for Customer_ID = 999 if no customer with ID 999 exists.

Page 10
 Chapter 5: Database System | Shubha Yatra Academy

5.3.4 Formatting and Validating Field Data

Formatting
Formatting controls how data is displayed to the user. It does not change the actual stored data — it only
changes how it appears on screen or in printed reports.

Common Format Examples in MS Access:

Field Type Format Display Example

Date/Time Short Date 12/16/2024
Date/Time Long Date Monday, December 16, 2024
Date/Time Medium Time 10:30 AM
Number Fixed (2 decimals) 45.67
Number Percent 85%
Currency Currency $500.00 or Rs. 1,500.00
Yes/No Yes/No Yes or No
Yes/No True/False True or False
Yes/No Checkbox Checked/Unchecked

How to Set Format in MS Access: Open the table in Design View > Select the field > In the Field
Properties panel at the bottom, set the Format property.

Validating (Data Validation)

Data validation ensures that the data entered into a field meets specific criteria or rules before it is
saved. This prevents incorrect, incomplete, or inappropriate data from being stored in the database.

Validation Properties in MS Access:

Property Description & Example

A condition that the entered data must satisfy. Example: >=18 (only allows
Validation Rule
values 18 or above for an Age field).
The error message displayed when the validation rule is violated. Example:
Validation Text
"Age must be 18 or above."
Required If set to Yes, the field cannot be left blank. The user must enter a value.

Page 11
 Chapter 5: Database System | Shubha Yatra Academy

Property Description & Example

Controls the format of data input. Example: (999) 000-0000 for phone
Input Mask
numbers, forces the user to enter digits in the correct pattern.
Automatically fills a field with a predefined value if the user does not enter
Default Value
anything. Example: =Date() for today's date.

More Validation Rule Examples:

• >0 — Value must be greater than zero
• Between 1 And 100 — Value must be between 1 and 100
• <Date() — Date must be in the past (before today)
• "M" Or "F" — Only allows "M" or "F" (for gender field)
• Like "????" — Exactly 4 characters (? = any single character)

Formatting vs. Validation

Formatting = How data LOOKS (display only, does not restrict input).
Validation = What data is ALLOWED (restricts what users can enter).
Example: Formatting shows a date as 12/16/2024, but Validation checks if the date is in the correct range.

Page 12
 Chapter 5: Database System | Shubha Yatra Academy

5.3.5 Inserting, Modifying, and Deleting Data

Once tables are created, you need to add, edit, and remove data. In MS Access, data manipulation is done
primarily through the Datasheet View or through Forms.

Inserting Data (Adding New Records)

1. Open the table in Datasheet View (double-click the table name in the Navigation Pane).
2. The table will display existing records in a grid format.
3. Click on the empty row at the bottom of the table (marked with an asterisk *).
4. Type the data for each field in the new row.
5. Press Tab to move to the next field, or Enter to confirm and move to the next record.
6. MS Access automatically saves each record as you move to the next row.

Auto-Save
Unlike Word or Excel, MS Access automatically saves data as you enter it.
You do NOT need to press Ctrl+S to save records.
However, table structure changes (in Design View) need to be saved manually.

Modifying Data (Editing Existing Records)

To modify (edit) existing data in a table:

• Open the table in Datasheet View.

• Double-click on the cell you want to edit.
• Make the necessary changes.
• Press Tab or click on another cell to save the change.
• To undo a change, press Ctrl + Z immediately (before moving to another record).

Deleting Data (Removing Records)

To delete a record from a table:

• Open the table in Datasheet View.

• Click on the row selector (the gray box on the far left of the row) to select the entire record.
• Right-click and select "Delete Record", or press the Delete key on your keyboard.
• A confirmation dialog will appear. Click Yes to permanently delete the record.

Caution
Deleting a record is PERMANENT in MS Access. You cannot undo a deletion after moving to another
record.

Page 13
 Chapter 5: Database System | Shubha Yatra Academy

If Referential Integrity is enforced and Cascade Delete is enabled, deleting a record may also delete related
records in other tables.
Always double-check before confirming a deletion.

Page 14
 Chapter 5: Database System | Shubha Yatra Academy

5.3.6 Queries, Forms, and Reports in MS Access

1) Queries

Queries are used to retrieve, filter, sort, and analyze specific data from one or more tables based on
certain criteria. They are one of the most powerful features of a database.

Types of Queries in MS Access:

Query Type Description

The most common type. Retrieves data from one or more tables based on
Select Query
specific criteria. Does not modify data.
Performs actions on data: Make-Table (creates a new table from query
Action Query results), Update (modifies existing data), Append (adds new records), Delete
(removes records).
Prompts the user to enter a value (parameter) each time the query runs.
Parameter Query
Example: Enter a date range to filter results.
Displays summarized data (sum, count, average) in a spreadsheet-like format
Crosstab Query
with row and column headers.

Creating a Query
Method 1: Query Design View (Graphical)
a) Go to Create tab > Query Design.
b) The Show Table dialog appears. Add the table(s) you want to query, then close the dialog.
c) In the Query Design grid at the bottom, select the fields you want to include by double-
clicking them or dragging them down.
d) In the Criteria row, enter conditions to filter data (e.g., >500 for amounts greater than 500, or
"Kathmandu" for a city field).
e) In the Sort row, choose Ascending or Descending to sort results.
f) Click Run (!) on the Design tab to execute the query and see results.
g) Save the query with a meaningful name (e.g., "CustomersInKathmandu").

Method 2: SQL View (Advanced)

You can also write queries using SQL (Structured Query Language). Switch to SQL View from the
Design tab.

Example SQL Queries:

• SELECT * FROM Students; — Retrieves all records from the Students table

Page 15
 Chapter 5: Database System | Shubha Yatra Academy

• SELECT Name, Age FROM Students WHERE Age >= 18; — Gets names and ages of students
18 or older
• SELECT * FROM Orders WHERE Amount > 500 ORDER BY Amount DESC; — Orders
above 500, sorted by amount (highest first)

Modifying a Query
• Right-click the query name in the Navigation Pane > Design View.
• Change the fields, criteria, or sort order as needed.
• Run the query again to see updated results.
• Save the changes.

Deleting a Query
• Right-click the query name in the Navigation Pane > Delete.
• Confirm the deletion. (This only removes the query, NOT the data in the tables.)

Query Tip
Deleting a query does NOT delete any data. Queries are just saved questions/commands.
You can use wildcards in criteria: * (any characters), ? (single character), # (single digit).
Example criteria: Like "K*" finds all records starting with K.

Page 16
 Chapter 5: Database System | Shubha Yatra Academy

2) Forms

Forms provide a user-friendly graphical interface for entering, editing, and viewing data in a database.
Instead of working directly with the table grid, forms allow users to interact with one record at a time in a
visually organized layout.

Why Use Forms?

• Easier and more intuitive for data entry than typing directly into a table grid.
• Can include labels, instructions, dropdown lists, and buttons to guide users.
• Restricts users from accidentally modifying table structure.
• Can show data from multiple related tables at once.

Creating a Form
Method 1: Quick Form (Auto-generated)
a) Select the table you want to base the form on (click it once in the Navigation Pane).
b) Go to Create tab > Form.
c) MS Access automatically creates a form with all the fields from the selected table.
d) Save the form with a descriptive name.

Method 2: Form Wizard

a) Go to Create tab > Form Wizard.
b) Select the table/query and choose which fields to include.
c) Choose a layout: Columnar (one record at a time), Tabular (multiple records), Datasheet, or
Justified.
d) Choose a style/theme for the form.
e) Give the form a name and click Finish.

Method 3: Form Design View (Full control)

• Go to Create tab > Form Design for a blank form.
• Manually add fields, labels, buttons, images, and other controls.
• Drag fields from the Field List pane to the form.
• Customize the layout, colors, fonts, and alignment.

Modifying a Form
• Open the form in Layout View (quick visual changes) or Design View (full control).
• Add, remove, or rearrange fields and controls.
• Change colors, fonts, sizes, and alignment.
• Add buttons, dropdown lists, or combo boxes.
• Save the changes.

Page 17
 Chapter 5: Database System | Shubha Yatra Academy

Deleting a Form
• Right-click the form name in the Navigation Pane > Delete.
• Confirm the deletion. (This only removes the form, NOT the underlying data.)

Page 18
 Chapter 5: Database System | Shubha Yatra Academy

3) Reports

Reports are used to present data in a formatted, printable, and professional-looking view. They are
designed for displaying, summarizing, and analyzing data for output purposes (printing, PDF export, or
screen display).

Key Features of Reports:

• Data is displayed in a read-only format (you cannot edit data through a report).
• Reports can include grouping, sorting, totals, subtotals, averages, and other calculations.
• You can add headers, footers, page numbers, logos, and custom formatting.
• Reports can be based on tables or queries.

Creating a Report
Method 1: Quick Report (Auto-generated)
• Select the table or query in the Navigation Pane.
• Go to Create tab > Report.
• MS Access automatically generates a report with all fields.
• Save and name the report.

Method 2: Report Wizard

• Go to Create tab > Report Wizard.
• Select the table/query and choose which fields to include.
• Set grouping levels (e.g., group orders by customer or by date).
• Set sort order (e.g., sort by amount in descending order).
• Choose a layout (Stepped, Block, Outline) and orientation (Portrait/Landscape).
• Give the report a name and click Finish.

Method 3: Report Design View

• Go to Create tab > Report Design for a blank report.
• Manually add fields, labels, calculated controls, and formatting.
• Organize sections: Report Header/Footer, Page Header/Footer, Detail section, and Group
Header/Footer.

Modifying a Report
• Open the report in Layout View or Design View.
• Change layout, grouping, sorting, or add/remove fields.
• Add calculated fields (e.g., =Sum([Amount]) for totals).
• Change colors, fonts, and formatting.
• Save the changes.

Page 19
 Chapter 5: Database System | Shubha Yatra Academy

Deleting a Report
• Right-click the report name in the Navigation Pane > Delete.
• Confirm the deletion. (Only the report is removed, not the underlying data.)

Quick Comparison: Queries vs. Forms vs. Reports

Feature Query Form

Purpose Retrieve and filter data Enter and edit data
Data Editing Some (via Action Queries) Yes (primary purpose)
User Interface Grid/table view Custom graphical layout
Output Filtered data results Interactive data entry screen
Based On Tables or other queries Tables or queries

Feature Report
Purpose Display and print formatted data summaries
Data Editing No (read-only)
User Interface Professional printable layout
Output Printed or exported documents (PDF, paper)
Based On Tables or queries

Final Exam Tip

Queries = Ask questions about your data (Search & Filter).
Forms = Enter and edit data easily (Input Interface).
Reports = Present and print data professionally (Output/Display).
All three can be created from the Create tab in MS Access.

Page 20