Complete Advanced in Database Computer II
Complete Advanced in Database Computer II
DATABASE COMPUTER
BY
This book is designed according to the current syllabus and exam papers so that all
students can pass their exams more easily. “Study smart, pass easy — this book is made for
you.”
Page 1 of 30
A database is an organized collection of related data stored in a structured manner
to allow easy access, retrieval, updating, and management of information.
Key Points
Examples
2. Importance of Databases
b) Fast Retrieval
c) Data Sharing
d) Security
Page 2 of 30
3. Real-Life Applications of Databases
Application Purpose
Library Systems Manage books and borrowing
Banking Systems Manage customer accounts
Hospital Systems Store patient records
Flight Reservation Systems Manage bookings
Inventory Systems Track stock levels
4. Hierarchy of Data
📘 Hierarchy of Data in a
Database Database
Character The tiniest unit of
data. Example: A single letter
like A, or a number like 5.
Page 3 of 30
Record A group of related fields
about one person or thing.
Example: For a student:
• Student file
• Teacher file Courses file Fees file 👉 All these files
together form the school database.
Page 4 of 30
Example
• Character → A
• Field → Student Name
• Record → Complete student information
• File → Student records
• Database → University database
Entity
Examples
• Student
• Employee
• Customer
Attribute
Examples
• Student Name
• Age
• Address
Page 5 of 30
🔑 What is a Key?
• A key is a special field (column) in a table.
• It is used to identify records uniquely or to connect tables together.
• Without keys, data could get mixed up or duplicated.
📌 Types of Keys
1. Primary Key
o A field that uniquely identifies each record in a table. o No two
records can have the same primary key.
o Example:
In a Student table, the Student ID is the primary key.
Even if two students have the same name, their IDs are different.
2. Foreign Key o A field that links one table to another. o It is
the “connection point” between related tables. o Example:
In a Student table, you may have a Department ID.
That Department ID is a foreign key because it connects to the
Department table (where each department has its own record).
Easy Analogy
• Primary Key = Your National ID number → it’s unique to you.
• Foreign Key = Your school ID card showing your department → it connects you to the
department you belong to
• Separate files
• Separate programs
• No centralized control
A file-based system is an old way of storing data where each application keeps its
own separate files. There is no central database, and each program manages its
own data independent
Page 6 of 30
🧠 Simple Example
a) Data Redundancy
b) Data Inconsistency
d) Data Dependence
e) Limited Flexibility
8. Database Approach
Page 7 of 30
Advantages
• Reduced redundancy
In a file-based system, the same data (like a student’s name) might be stored
in many files. In a database, data is stored once and shared by all programs
— this avoids duplication
• Better consistency
Because data is stored in one central place, any change (like updating a
student’s address) automatically appears everywhere. This keeps all
information accurate and consistent.
• Improved security
Databases allow controlled access — only authorized users can view or edit
certain data. This protects sensitive information like patient or banking
record
Many users or applications can access the same database at the same time.
For example, different departments in a school can all use the same student
database
• Centralized management
A DBMS is software that helps you create, manage, and control databases.
Page 8 of 30
It acts as a bridge between the user and the database, making it easy to store, find,
and update information.
⚙️ Functions of a DBMS
Create Databases Allows users to design and set up new databases with tables,
fields, and relationships.
Store Data Saves data safely in organized tables for easy access later.
🔍 Retrieve Data Lets users search and get specific information quickly using
queries.
Control User Access Manages who can view or change data — protecting
sensitive information.
Examples of DBMS
• Oracle
• MySQL
• SQL Server
• PostgreSQL
• MongoDB
• Microsoft Access
🧩 Database vs Database Management System (DBMS)
DBMS (Database
Aspect Database
Management System)
A collection of related data Software used to create, manage,
💾 Meaning
stored together for easy access. and control databases.
Page 9 of 30
Stores information in tables and Controls how data is stored,
📚 Purpose
files. retrieved, and updated.
Important Terms
Table
A row in a table.
Field
A column in a table.
Relationship
Page 10 of 30
Connection between tables using keys.
Definition
Features
• User-friendly
• Supports forms and reports
• Stores large amounts of data
• Suitable for small and medium organizations
Store the actual data in rows and columns. Each row is a record, and
Tables each column is a field. Example: a Student Table with Name, ID, and Age.
Used for entering and displaying data easily. They act like user-friendly
Forms windows for data input instead of typing directly into tables.
Object Function / Explanation
Used to search, filter, and analyze data. You can ask questions like
Queries
“Show all students with GPA above 3.0.”
Used for printing and presenting information neatly. They summarize
Reports data for meetings or official documents.
Page 11 of 30
Macros Automate tasks — for example, opening a form or running a query with
one click. They save time and reduce errors.
Contain VBA (Visual Basic for Applications) code used for advanced
Modules programming and custom automation.
In Simple Words
b) Forms
c) Queries
f) Modules
Contain VBA code.
Page 12 of 30
Components of MS Access Interface
Component Function / Explanation
The toolbar at the top of the window that contains commands grouped under
🎀 Ribbon tabs such as Home, Create, External Data, and Database Tools. It helps you
perform tasks quickly.
Located on the left side of the screen. It displays all database objects (Tables,
📂 Navigation
Forms, Queries, Reports, Macros, Modules) so you can open or manage them
Pane
easily.
🪟 Object The main working area where the selected object (like a table or form) is
Window displayed and edited.
⚡
A small toolbar above the Ribbon that provides shortcuts to common
Quick Access
commands like Save, Undo, and Redo. You can customize it to add your
Toolbar
favorite tools.
Special tabs that appear only when certain objects are open or selected (for
Contextual example, Table Tools or Form Design). They provide commands specific to
Tabs
that object.
In Simple Words
• Easy to use
• Fast database development
• Good Microsoft Office integration
• Supports multi-user access
• Cost effective
Page 13 of 30
Advantages of Microsoft Access
Advantage Explanation
Access has a friendly interface with menus, wizards, and
🪟 Easy to Use drag-and-drop tools, making it simple even for beginners
to create and manage databases.
You can quickly design tables, forms, queries, and reports
⚡ Fast Database
without writing complex code — ideal for class projects
Development
and small organizations.
Works smoothly with other Microsoft programs like
🔗 Good Microsoft
Excel, Word, and Outlook for importing, exporting, and
Office Integration
reporting data.
👥 Supports Several users can work on the same database at the same
Multi-User Access time, making teamwork easier.
It’s affordable compared to large enterprise database
💰 Cost-Effective systems like Oracle or SQL Server, yet powerful enough
for small and medium organizations.
In Simple Words
Microsoft Access helps users build and manage databases quickly and
cheaply, with tools that are easy to learn and work well with other Microsoft
Office applications.
💻 MS Access vs MS Excel
Feature Microsoft Access Microsoft Excel
Handles large datasets efficiently Best for small datasets and quick
Data Size
using tables and relationships. calculations.
Page 14 of 30
👥
Supports multi-user access —
Mainly single-user focused —
User Access one person edits a file at a time.
several people can work on the
same database at once.
Uses relational tables that can
Uses worksheets — each sheet is
Structure link data between different tables
independent and not relational.
(e.g., Students ↔ Departments).
Provides strong data validation,
⚙️ Data Provides formulas, charts, and
queries, and forms for structured pivot tables for analysis.
Control input.
Ideal for managing records Best
Ideal for calculations, budgets,
Use
— analyzing
🗂️ Purpose — storing, organizing, and linking calculations
In Simple Words
Page 15 of 30
• Access is like a filing system — it stores and connects data. Excel is like
a calculator — it computes and analyzes data.
1. Define a database.
2. Explain advantages of databases.
3. Differentiate between database and DBMS.
4. Explain hierarchy of data.
5. Discuss limitations of file-based systems.
6. Define entity, attribute, and key.
7. Explain functions of DBMS.
8. What is an RDBMS?
9. Explain objects in MS Access.
[Link] Access and Excel.
Database
DBMS
Table
Query
Report
Primary Key
Foreign Key
• Memorize definitions.
• Understand comparisons.
• Practice writing short notes.
• Revise examples carefully.
• Study tables, queries, forms, and reports. Practice essay questions
regularly.
These tables are used to test your understanding of relationships between tables
(Foreign Keys and Primary Keys).
From CAP 121 (2016–2017 & 2017–2018 Papers)
Describe a database.
• Provide four reasons why organizations adopt databases instead of the
traditional file approach.
• Draw and explain the hierarchy of data levels (Character → Field →
Record → File → Database).
• Name the four objects that exist in Microsoft Access databases.
• State the importance of relationships in MS Access.
• Describe the use of an attribute and a key in Microsoft Access.
• State the ribbon used to import, export, and collect data in Access.
• Compare and contrast data types used in Excel and Access.
• Explain the difference between a foreign key and a record.
• Describe two types of queries in Microsoft Access.
• Draw and illustrate the traditional vs. database approach for generating
reports.
Page 18 of 30
Q5
a) How is a field different from a record in Microsoft Access?
Table
Field Record
Example: LastName (holds one Example: A full student row with ID, data
type) Name, Course, etc.
b) What is the use of Data Models in DBMS? Data models provide a conceptual
framework to:
Page 19 of 30
• Increased complexity — developers must understand the physical data
layout
Q6
i) Data Redundancy
Data redundancy refers to the duplication of the same data in multiple locations
or files. In file systems, redundancy is common because the same data may be
stored in separate files for different applications. DBMS minimizes redundancy
through centralized data storage and normalization.
ii) Distributed Systems
A distributed system is a collection of independent computers that appear to the
user as a single coherent system. In DBMS, a Distributed Database stores data
across multiple physical locations but manages it as one logical database,
improving reliability, scalability, and local access speed.
iii) Attribute
An attribute is a characteristic or property of an entity. In relational database
terms, it corresponds to a column/field in a table. For example, for a Student
entity, attributes could be StudentID, Name, DateOfBirth, and Email.
iv) Primary Key
A primary key is a unique identifier for each record in a table. It must contain
unique values and cannot be NULL. It ensures each row can be uniquely
identified and is used to establish relationships with other tables (via foreign keys).
Page 20 of 30
In file systems, data is duplicated across multiple files
1. Reduced Data (e.g., same customer details in sales and billing files).
Redundancy DBMS stores data once in a central repository, eliminating
unnecessary duplication and saving storage space.
Page 22 of 30
retrieve, and update data, and is typically controlled by a Database Management
System (DBMS).
Page 23 of 30
3. Hierarchy of data levels:
scss
Database
↑
└── File (Table)
↑
└── Record (Row/Tuple)
↑
└── Field (Column/Attribute)
↑
└── Character (Smallest unit)
Explanation:
Page 24 of 30
5. Importance of relationships in MS Access:
Relationships link tables together using Primary Keys and Foreign Keys to:
Page 25 of 30
General number Number (multiple sub-types: Integer,
Numbers
format Long, Single, Double, Decimal)
Auto-
Not available AutoNumber (unique sequential IDs) increment
Key difference: Access is strictly typed — each field must have a fixed data type
with validation rules. Excel is loosely typed — cells can hold any format and can
be changed freely.
Page 26 of 30
Used to enforce relationships and maintain Represents one complete
referential integrity instance of an entity
Query
Description Example
Type
Database Approach:
sql
User Request → Query (SQL) → DBMS → Access Tables → Generate Report
• Changes to data structure don't break reports (if views/queries are used
properly)
• ✅ Fast, flexible, and maintainable
Visual Illustration:
sql
Traditional: Database:
+----------+ +----------+
| User | | User |
+----+-----+ +----+-----+
| |
v v
+----------+ +----------+
| Program1 | | Query |
| Program2 | vs. | + Report |
| Program3 | | Designer |
+----+-----+ +----+-----+
| |
v v
+----------+ +----------+
| File1 | | DBMS |
| File2 | | (Tables) |
| File3 | +----------+
+----------+
Page 28 of 30
TEST YOURSELF
Describe any two types of queries in Microsoft Access.
1. Differentiate between a foreign key and a record in databases.
2. Explain the significance of the functionality represented by keys in
databases.
3. Draw and illustrate the traditional approach vs. database approach for
generating reports.
4. Describe a database structure that would suit multiple files in MS Access.
5. State the name of the ribbon used to import, export, and collect data in
Access.
APA 7th Edition References
1. Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearso
n Education.
2. Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Addison-Wesley.
3. Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGra
w-Hill.
4. Lings, B. J. (1986). Database management. Data Processing, 28(1), 51–58.
5. Berg, J. L. (1989). Database management. Computer Standards & Interfaces, 9(1), 81–86.
6. Patcharaprutipakorn, T., Thongaraim, D., & Thatreenaranon, N. (2017). Case study: Datab
ase management for solid waste management services. International Journal of Future Co
mputer and Communication, 6(3), 133–137.
7. Naimisha, U., & Ashritha, A. (2019). Janaushadhi database management system. Internati
onal Journal of Trend in Scientific Research and Development, 3(5), 611–617.
8. Balakrishna, B. (2019). Serverless databases are the future of database management. Journ
al of Scientific and Engineering Research, 6(1), 277–282.
9. Ingle, P. S., Sawale, S. M., Parihar, V. D., Deshmukh, A. R., & Cheke, A. A. (2020). A co
mprehensive review on database management. International Journal of Creative Research
Thoughts (IJCRT), 8(5), 2320–2882.
10. Springer. (2021). Database Management System: Foundations and Practices. Springer Na
ture
Page 29 of 30
BY
Page 30 of 30