MS Access a DBMS

At the core of computing lies the distinction between raw facts and meaningful knowledge:

• Data (উপাত্ত / डेटा): Data consists of raw, unorganized, unanalyzed facts, figures, characters, or symbols gathered from observations, measurements, or experiments. On its own, raw data lacks contextual meaning, intent, or decision-making value. For example, the isolated values 95, 88, 76, 'Priya', 8 are mere data points. Without contextual labels, one cannot tell whether 95 represents marks, a room number, or a speed limit.
• Information (তথ্য / सूचना): Information is data that has been systematically processed, organized, structured, and contextualized to deliver actionable meaning, clarity, and utility. When the raw numbers above are processed into: "Priya, a student of Class 8, secured 95 in Mathematics, 88 in Science, and 76 in English, achieving an overall Rank 1 with an 'A+' grade", it transforms into valuable information (a student Report Card).

Before modern databases were invented, organizations relied on physical paper ledgers and subsequently on flat-file systems (such as text files, CSV files, or separate spreadsheets stored on individual computers). In a flat-file system, each department created its own independent data files and wrote custom computer programs to read and process them.

As organizations grew, flat-file systems revealed fatal structural flaws that caused severe operational bottlenecks:

1. Data Redundancy (ডেটা রিডান্ড্যান্সি / डेटा रिडंडेंसी): Data redundancy refers to the unnecessary duplication of the same data across multiple files. For instance, in a school, a student's home address and phone number might be stored separately in the Admissions Office file, the Library register, the Accounts fee file, and the Sports department file. This wasted expensive storage space.
2. Data Inconsistency (ডেটা অসঙ্গতি / डेटा असंगति): Inconsistency is a direct, dangerous consequence of redundancy. If a student moves to a new residence and updates their address only with the class teacher, the Admissions file reflects the new address while the Library and Accounts files retain the old address. When different files contain conflicting, contradictory copies of the same entity's data, data integrity is shattered.
3. Difficulty in Accessing Data (তথ্য অনুসন্ধানে জটিলতা): Traditional file systems lack flexible query capabilities. If the school principal suddenly requests a list of all Class 8 students who scored above 90% in Computer Science and live in Kolkata, someone must either manually search through thousands of paper ledger entries or hire a programmer to write an entirely new application program just to extract that specific record set.
4. Data Isolation and Program-Data Dependence: In flat files, data files and application programs are tightly coupled. If the structure of a file is modified (e.g., expanding a phone number field from 10 digits to 12 digits), every single software program written to read that file crashes unless re-coded and recompiled.
5. Lack of Security and Access Control: In a file system, it is extremely difficult to grant selective access permissions. Either a user has access to the entire file or to nothing. A teacher entering exam marks might inadvertently view sensitive salary records or medical histories stored within the same file directory.
6. Concurrent Access Anomalies (একযোগে ব্যবহারের ত্রুটি): When multiple users attempt to read and modify the same file simultaneously across a network, write collisions occur. If two clerks attempt to update the same student's fee balance at the exact same second, one clerk's changes will overwrite and erase the other's, corrupting the financial ledger.
7. Lack of Data Integrity Constraints: File systems have no built-in validation mechanisms. A file will easily accept negative numbers for age (e.g., -15), alphabets for phone numbers, or duplicate roll numbers unless elaborate custom code is manually written for every single data-entry form.

A Database (ডেটাবেস / डेटाबेस) is an organized, structured collection of logically related data stored electronically in a computer system so that it can be easily accessed, managed, modified, updated, and retrieved with high efficiency.

Unlike an unorganized folder of random documents, a database enforces logical relationships between data items—such as connecting a student to their exam marks, library books, and attendance records.

A Database Management System (DBMS) is specialized system software that acts as an intelligent intermediary between end-users, software applications, and the physical database files stored on disk. The DBMS provides tools and interfaces to define data structures, insert new records, update existing records, execute search queries, and enforce security policies.

Different DBMS systems cater to different computing scales—from personal desktop projects to massive enterprise cloud architectures:

• Controlled Data Redundancy: Data is consolidated into a centralized structure. Instead of replicating address fields across five files, it is stored once in a master student table and referenced elsewhere, dramatically reducing duplicate data.
• Ensured Data Consistency: Because data is stored centrally, an update made to a student's address takes effect instantly across all departments, preventing conflicting records.
• Data Sharing Across Applications: Authorized users from admissions, library, and finance can concurrently share and query the same centralized database safely.
• Enforcement of Data Standards: The Database Administrator (DBA) can enforce uniform data standards—such as standardized date formats (YYYY-MM-DD), phone number character limits, and naming conventions.
• Enforcement of Data Integrity: The DBMS automatically rejects invalid data through integrity constraints. If a user tries to enter a student age of 250 or leave a mandatory Primary Key blank, the DBMS blocks the transaction.
• Robust Security and Privacy Controls: Granular role-based security ensures that students can only view their own marks, teachers can enter grades, and only financial accountants can view payment ledgers.
• Automated Backup and Crash Recovery: In the event of a sudden power outage, hardware failure, or software crash, modern DBMS engines use transaction logs to restore the database to its last consistent state, guaranteeing zero data loss.
• Program-Data Independence: The logical structure of the database can be modified (such as adding a new column) without breaking existing software applications that query the database.

In 1970, British computer scientist Dr. Edgar F. Codd (E.F. Codd), working at IBM, published a seminal research paper titled "A Relational Model of Data for Large Shared Data Banks". Prior to Codd's breakthrough, databases organized data using complex hierarchical tree structures or network graphs that required programmers to navigate intricate pointer paths.

Dr. Codd proposed a revolutionary, elegant concept: represent all data as simple, two-dimensional mathematical tables called 'Relations'. A database based on this relational model is known as a Relational Database Management System (RDBMS).

To master RDBMS concepts, students must understand the foundational building blocks:

• Table / Relation (সারণি / সম্পর্ক): A two-dimensional grid consisting of rows and columns designed to store data about a specific entity (e.g., Student, Book, Employee, Product). Every relation in a database has a unique name.
• Record / Tuple / Row (রেকর্ড বা টাপল): A horizontal row in a table representing a single, complete, unified instance of an entity. For example, a single row in a 'Students' table contains all details belonging to one student: [101, 'Ananya Sen', 14, 'A+']. In formal database theory, a row is called a Tuple.
• Field / Attribute / Column (ফিল্ড বা অ্যাট্রিবিউট): A vertical column in a table representing a specific characteristic, property, or category of data stored for every entity. For example, Roll_No, Student_Name, Date_of_Birth, and Marks are attributes.
• Domain (ডোমেন / डोमेन): The set or pool of permissible, valid atomic values from which an attribute can draw its actual values. For example, the domain for an attribute Exam_Grade might be strictly restricted to the set {'A', 'B', 'C', 'D', 'F'}. Any grade outside this domain is rejected by the DBMS.

In board examinations, questions frequently test the definitions and calculations of Degree and Cardinality:

Let us examine a practical table named STUDENTS:

Analysis of STUDENTS Table:

• The table has 5 columns (Roll_No, Student_Name, Class, City, Marks). Therefore, Degree = 5.
• The table has 4 rows of student data. Therefore, Cardinality = 4.
• Exam Scenario: If 2 new students are admitted and enrolled (adding 2 rows), the Degree remains 5 while Cardinality becomes 4 + 2 = 6. If a new column Grade is added, Degree becomes 5 + 1 = 6 while Cardinality remains 6.

In real life, two students in the same classroom might share the exact same first and last name (e.g., two boys named 'Rahul Sharma'). If an administrator attempts to record marks or issue a disciplinary notice using only the name, confusion and errors will result. In a relational database, Keys (কি বা চাবি / कुंजियाँ) are essential attributes that guarantee every record can be uniquely distinguished and that relationships between separate tables remain robust and uncorrupted.

A Primary Key (প্রাইমারি কি / प्राइमरी की) is an attribute or minimal set of attributes that uniquely and unambiguously identifies each record (tuple) in a table.

Non-Negotiable Characteristics of a Primary Key:

1. Uniqueness (অনন্যতা): No two rows in the table can possess identical Primary Key values. Every value must be globally unique within that relation.
2. Not Null Constraint (অশূন্যতা): A Primary Key value can NEVER be empty, undefined, or NULL. Because it serves as the identifier of an entity, a missing Primary Key means the entity cannot be identified. This rule is called the Entity Integrity Constraint.
3. Permanence / Immutability: A Primary Key value should rarely, if ever, change over time. Examples of ideal primary keys include Student_ID, Admission_Number, Aadhaar_Number, Employee_ID, and ISBN for books.

In many real-world database tables, several attributes might independently possess the quality of uniqueness:

• Candidate Key (ক্যান্ডিডেট কি / कैंडिडेट की): All attributes or combinations of attributes in a table that are eligible and qualified to act as a Primary Key (possessing both uniqueness and minimality) are called Candidate Keys. For example, in a School Admissions table, both Admission_No and Aadhaar_Card_No are Candidate Keys.
• Alternate Key (অল্টারনেট কি / अल्टरनेट की): Out of all eligible Candidate Keys, database designers select exactly one to serve as the Primary Key. All the remaining Candidate Keys that were not chosen as the Primary Key are designated as Alternate Keys (or Secondary Keys).

A relational database avoids repeating large chunks of data by splitting information into multiple specialized tables and linking them together. The glue that connects these tables is the Foreign Key (ফরেন কি / फॉरेन की).

A Foreign Key is an attribute in one table (known as the referencing or child table) whose values are derived from and reference the Primary Key of another table (known as the referenced or parent table).

Referential Integrity Rule: The Foreign Key constraint ensures that a child table can never contain a value in its Foreign Key column that does not already exist as a valid Primary Key in the parent table. Furthermore, a parent record cannot be accidentally deleted if child records currently point to it, preventing 'orphan records' in the database.

Microsoft Access is a widely used relational database management software included in the Microsoft Office suite. It provides a visual graphical user interface (GUI) that allows beginners to create relational tables, establish relationships, design data-entry screens, and generate printable reports without writing complex code.

An MS Access database (.accdb file) is organized around four core structural components called Objects:

1. Tables (সারণি): The fundamental bedrock of the database. Tables store all raw structured data in two-dimensional grids of rows (records) and columns (fields). Without tables, no other database object can function.
2. Queries (অনুসন্ধান / क्वेरी): A query is a request or question posed to the database to search, filter, extract, sort, or perform calculations on specific records meeting user-defined criteria. For example, a query can instantly extract: "Display only the Names and Phone numbers of students who scored over 90% in Computer Science."
3. Forms (ফর্ম): A Form is a user-friendly, graphical window screen designed to make data entry, editing, and viewing intuitive and error-free. Instead of navigating intimidating raw tables with hundreds of columns, users interact with text boxes, drop-down menus, and buttons. Forms act as an interactive front-end.
4. Reports (প্রতিবেদন / रिपोर्ट): A Report is a formatted, styled, and organized presentation of database information designed specifically for printing, formal documentation, and business summaries (e.g., student Report Cards, monthly fee receipts, annual stock audits). Unlike forms, reports are static read-only outputs optimized for paper printing and PDF generation.

When creating fields in Design View, users must specify the exact Data Type to ensure memory optimization and prevent invalid inputs:

SQL (উচ্চারণ: সিক্যুয়েল / एसक्यूएल) is the standardized programming language used across the world to communicate with relational database management systems. Whether working on Oracle, MySQL, Microsoft SQL Server, or SQLite, SQL commands remain universal.

SQL commands are broadly divided into two major functional categories:

• Data Definition Language (DDL): Commands that define, alter, or destroy the structural schema of the database (e.g., creating tables or modifying column definitions). Common DDL commands include CREATE TABLE, ALTER TABLE, and DROP TABLE.
• Data Manipulation Language (DML): Commands used to inspect, retrieve, insert, modify, and delete the actual data records stored inside tables. The four core DML commands are SELECT, INSERT, UPDATE, and DELETE.

Let us master the four fundamental DML operations using a sample table Students(Roll_No, Name, Class, Marks, Grade):

1. The SELECT Statement (ডেটা অনুসন্ধান করা): Used to fetch and retrieve data from one or more tables.

   -- Example 1: Retrieve all columns for all students
   SELECT * FROM Students;
   
   -- Example 2: Retrieve only Name and Marks of students in Grade A
   SELECT Name, Marks FROM Students WHERE Grade = 'A';

2. The INSERT Statement (নতুন রেকর্ড যোগ করা): Inserts a new row of data into an existing table.

   -- Insert a new student record
   INSERT INTO Students (Roll_No, Name, Class, Marks, Grade)
   VALUES (105, 'Ritwik Das', 8, 92, 'A');

3. The UPDATE Statement (বিদ্যমান রেকর্ড পরিবর্তন করা): Modifies existing values in one or more records meeting a specified condition.

   -- Update marks and grade for student with Roll_No 102
   UPDATE Students
   SET Marks = 95, Grade = 'A+'
   WHERE Roll_No = 102;

   CRITICAL NOTE: Always include the WHERE clause! Omitting WHERE will update every single student's marks in the entire table!
4. The DELETE Statement (রেকর্ড মুছে ফেলা): Removes existing records from a table based on a condition.

   -- Delete a specific student who transferred to another school
   DELETE FROM Students
   WHERE Roll_No = 105;

   CRITICAL NOTE: Omitting the WHERE clause in DELETE will instantly erase all records in the table!