MS Access

MS Access is database management software that is designed by Microsoft. It is used in the process of managing and creating relational databases. In Class 9, students study higher concepts of databases like relationships, conditional queries and database design.

MS Access helps in managing a big amount of data in an efficient way.

What is a DBMS?

'DBMS' (database management system) refers to some software that facilitates managing, storing and organising information in a database. It serves as a manager to ensure that all the data are stored in an appropriate way that can be accessed in a short period of time and are safe.

Key Functions of DBMS:

• Data Storage: It is a data storage that stores large volumes of data in an organised form, commonly in tables.
• Data Retrieval: It allows you to quickly upload and access information through queries.
• Data Security: A DBMS helps to keep your data safe by preventing unauthorised access to information.
• Data Integrity: The integrity of data means accuracy and consistency.
• Multitasking: It enables several people to use the database and operate it simultaneously without problems.

Types of DBMS:

• Hierarchical DBMS: The information is arranged in tree form.
• Network DBMS: Data is stored in graph form.
• Relational DBMS (RDBMS): They store data in tables and connect them via keys (e.g., MySQL, MS Access).
• Object-Orientated DBMS: Data is stored in objects, the way it is used in programming.

Benefits of a DBMS:

• Easy Data Management: It simplifies the management and storage of huge amounts of data.
• Increased Rapidity in Data Access: It is possible to access data in a short time.
• Secure: It ensures your data is not accessed by unauthorised persons.
• Supports Multiple Users: The system can have multiple users at the same time.

DBMS is very common in organisations, schools, banks, and any other place where large amounts of data are required to be stored and processed efficiently.

What is a relational database?

The data is stored in a relational database in various tables, which are joined or linked to one another.

For example:

• One table stores student details.
• Another table stores marks.
• These tables are linked with a common field, such as roll number.

Relational databases minimise repetition of data and improve accuracy.

Advanced Table Design

How to create tables appropriately?

Important concepts include:

• Field Properties (Field Size, Format)
• Required Field
• Default Value
• Validation Rule
• AutoNumber Data Type

These characteristics are used to control data entry and minimise errors.

Relationships

Tables are related to each other.

Types of relationships:

One-to-One

Table A and Table B have one record in common each.

One-to-Many

There is one record in Table A that matches several records in Table B.

Example:

A student may have many subject marks.

Another concept is referential integrity that maintains data consistency in related tables.

Advanced Queries

Queries help in retrieving given data.

• Using criteria in queries
• Logical operators (AND, OR, NOT)
• Parameter Queries
• Calculated Fields
• Aggregate Functions (Sum, Count, Average)

Queries assist in analysing and filtering information.

Forms and Controls

The data is entered and shown in forms.

Advanced features include:

• Designing custom forms.
• Adding controls such as buttons.
• Better layout and appearance.
• Forms simplify interaction between databases.

Reports

Reports show data in a structured and printable format.

Students learn:

• Grouping data
• Sorting data
• Formatting reports
• Creating summary reports

Reports are helpful for official records and presentations.

Introduction to Macros

The automation of MS Access is performed with the help of a macro.

It helps:

• Perform repetitive duties fast
• Open forms automatically
• Display messages

Macros are time savers and more efficient.

Database Design

Good database design:

• Reduces data duplication
• Improves data accuracy
• Makes searching faster
• Keeps data organised

Before developing a database, proper planning is important.

QUIZ FOR MS ACCESS

1. MS Access is mainly used for:

A) Creating spreadsheets
B) Relational database management.
C) Designing webpages
D) Editing images

Answer: B) Relational database management.

2. What does DBMS stand for?

A) Data Backup Management Scan.
B) Database Management System.
C) Digital Binary Management System.
D) Data Browser Monitoring System.

Answer: B) Database Management System.

3. Which function of a DBMS is used to make data accurate and consistent?

A) Data Retrieval
B) Data Storage
C) Data Integrity
D) Data Formatting

Answer: C) Data Integrity

4. The primary data storage contents of a relational database are:

A) Trees
B) Graphs
C) Tables
D) Files

Answer: C) Tables

5. A common field like 'Roll Number' links two tables in a database. This concept represents the following:

A) Data Sorting
B) Relationships
C) Query Filtering
D) Report Formatting

Answer: B) Relationships

6. In database relationships, when a record in Table A links to a number of records in Table B, then it is referred to as:

A) One-to-One relationship
B) One-to-Many relationship
C) Many-to-One relationship
D) Network relationship

Answer: B) One to many relationship.

7. What is the database feature that will automatically assign a unique number to every record?

A) Default Value
B) Validation Rule
C) AutoNumber
D) Field Format

Answer: C) AutoNumber

8. What is the MS Access feature of getting specific information out of a database based on the conditions?

A) Forms
B) Queries
C) Reports
D) Tables

Answer: B) Queries

9. The user of the database would like to know the sum of marks of students by using SUM or the average marks. Which feature is used?

A) Aggregate Functions
B) Sorting
C) Validation Rule
D) Default Value

Answer: A) Aggregate Functions.

10. What MS Access capability is useful to automate the repetitive process, like opening forms and showing messages?

A) Queries
B) Macros
C) Reports
D) Tables

Answer: B) Macros