Database Concepts in MS Access

A database is an organized collection of related data that can be stored, searched, updated, and retrieved.

Example: A coaching institute may maintain a database containing student names, courses, mobile numbers, and fee details.

A Database Management System (DBMS) is software used to create and manage databases.

Examples include Microsoft Access, MySQL, Microsoft SQL Server, Oracle Database, and PostgreSQL.

Access is a relational database management application useful for many desktop and small-business database solutions.

A table stores related data in rows and columns. For example, a Students table can store details about students.

A record is one complete row in a table. It contains the values for one item or person.

Example: The row containing StudentID 101, Rahul, ADCA, and 10000 is one student record.

A field is a column in a table that stores one category of information.

Examples include StudentID, Name, Course, Mobile, DateOfBirth, and Fees.

Each field has a field name and a data type that determines what kind of value it can store.

• Record: A complete row containing information about one item.
• Field: A column containing one type of information.

In a Students table, Rahul's complete row is a record, while Name is a field.

A data type defines the kind of information a field can store.

• Short Text: Names, course names, or phone numbers.
• Long Text: Longer descriptions or notes.
• Number: Numeric values used in calculations.
• Date/Time: Dates and times.
• Currency: Money values such as fees.
• AutoNumber: A value Access generates automatically for each new record.
• Yes/No: A true/false or yes/no value.

The available field types can vary by Access version and table design.

A primary key uniquely identifies each record in a table.

For example, StudentID can identify a student even if two students have the same name.

A primary key cannot contain duplicate values or Null values.

A foreign key is a field in one table that refers to a key in another table, helping connect related information.

Example: A Payments table may contain StudentID to show which student made each payment.

A relationship connects related tables using matching fields.

For example, Students.StudentID can be related to Payments.StudentID.

Relationships help reduce unnecessary repetition and support consistent data.

• One-to-one: One record in a table relates to one record in another table.
• One-to-many: One record can relate to several records in another table.
• Many-to-many: Multiple records on each side can relate to multiple records on the other side, usually through a junction table.

Example: One student can have many payment records, which is a one-to-many relationship.

A query is used to find, filter, sort, calculate, or modify data according to specified conditions.

Example: A query can display students enrolled in the ADCA course or show students whose fees are pending.

A form provides a convenient interface for entering, viewing, and editing records.

Example: A Student Admission Form can contain fields for a student's name, course, mobile number, and admission date.

A report presents data in a structured format suitable for viewing or printing.

Example: A monthly fee report can show students, payment amounts, and the total fee collected.

• Tables: Store data.
• Queries: Find or process data.
• Forms: Provide an interface for working with records.
• Reports: Present information in a formatted layout.

A small institute database might contain these tables:

• Students: StudentID, Name, Mobile, CourseID
• Courses: CourseID, CourseName, CourseFee
• Payments: PaymentID, StudentID, Amount, PaymentDate

StudentID and CourseID can be used to connect related records between tables.

• To keep related information organized.
• To search and update information more easily.
• To reduce unnecessary duplication.
• To connect information through relationships.
• To create useful queries, forms, and reports.

A spreadsheet such as Excel is useful for calculations, charts, and flexible data analysis. A relational database such as Access is useful for managing structured records across related tables.

The best option depends on the amount of data, relationships, reporting needs, and how the information will be used.

• Give tables and fields clear, meaningful names.
• Choose suitable data types.
• Use primary keys to identify records.
• Set relationships where appropriate.
• Restrict access to private information.
• Make regular backups of important database files.

Task: Plan a simple database for a computer institute.

1. Write down the information you need to store about students.
2. Design a Students table with StudentID, Name, Mobile, and CourseID fields.
3. Design a Courses table with CourseID, CourseName, and CourseFee fields.
4. Choose a primary key for each table.
5. Identify how the Students and Courses tables can be related.
6. Suggest one query, one form, and one report for the database.

1. What is a database?
2. What is a DBMS?
3. What is the difference between a field and a record?
4. What is a data type?
5. What is the purpose of a primary key?
6. What is a foreign key?
7. What is a relationship between tables?
8. Name the four main objects in MS Access.
9. What is the purpose of a query?
10. Why are database backups important?