1. Limitations of File-Based Systems

File-based systems store data in individual files for each application. Limitations include:

1. Data Redundancy & Inconsistency

   • Same data may be stored in multiple files, causing duplication and inconsistencies.

2. Difficulty in Data Access

   • Accessing data requires writing custom programs. Complex queries are hard to implement.

3. Data Isolation

   • Data is scattered across files, making it difficult to access data across multiple files.

4. Integrity Problems

   • Hard to enforce data integrity (like unique IDs or constraints) in file-based systems.

5. Concurrent Access Issues

   • Multiple users cannot safely access or update files at the same time.

6. Security Issues

   • File-based systems have limited support for user authentication and access control.

2. How Database Approach Resolves Limitations

3. Physical Architecture of a DBMS

The physical architecture defines how data and DBMS components are organized and accessed:

Components:

1. Hardware

   • Servers, storage devices, CPU, memory where DBMS and data reside.

2. DBMS Software

   • Core system managing database operations:

     • Storage Manager: Manages data storage, buffering, and retrieval.

     • Query Processor: Parses and executes queries.

     • Transaction Manager: Ensures ACID properties.

3. Data

   • Stored in files or tables on storage devices.

   • Organized logically into tables, indexes, views.

4. Users / Applications

   • End Users: Use applications to interact with DBMS.

   • Application Programs: Connect via APIs (like JDBC) to perform operations.

Physical Architecture Diagram (Simplified)

[Users / Applications]
          |
    [Query Processor]
          |
   [Transaction Manager]
          |
     [Storage Manager]
          |
       [Data Files]

• Queries from users pass through the query processor → transaction manager ensures consistency → storage manager handles reading/writing to physical storage.

Summary:

• File-based systems have redundancy, inconsistency, poor security, and limited access.

• DBMS centralizes data, enforces constraints, supports concurrency, and provides easy access.

• Physical architecture includes users, DBMS software, and storage hardware interacting to manage data efficiently.

(i) Key Constraints

• A key is an attribute (or set of attributes) that uniquely identifies a tuple (row) in a relation (table).

• Constraint: No two tuples can have the same value for the key.

Example:
Table: Student(ID, Name, Age)

• Key: ID → ensures each student has a unique ID.

(ii) Domain Constraints

• A domain constraint specifies that values of an attribute must come from a predefined domain (type or set of allowed values).

Example:

• Age in Student table → must be an integer between 18 and 25.

• Gender → only Male or Female.

(iii) Candidate Key

• A candidate key is a minimal set of attributes that can uniquely identify a tuple.

• A relation can have multiple candidate keys; one is chosen as the primary key.

Example:
Table: Student(ID, RollNo, Email, Name)

• Candidate keys: ID, RollNo, Email → any of these can uniquely identify a student.

(iv) Select Operation (σ)

• Select (σ) returns tuples that satisfy a given condition.

• Notation: σ_condition(Relation)

Example:
Table: Student(ID, Name, Age)

• Query: σ_Age>20(Student) → Students with Age > 20

(v) Project Operation (π)

• Project (π) returns specific columns (attributes) from a relation.

• Notation: π_attributeList(Relation)

Example:
Table: Student(ID, Name, Age)

• Query: π_Name(Student) → Only names of students

(vi) Equijoin Operation

• Combines tuples from two relations based on equality of attributes.

Example:
Table1: Student(ID, Name)
Table2: Marks(ID, Score)

• Query: Join on ID → Student ⨝ Student.ID = Marks.ID Marks

(vii) Set Difference Operation (-)

• Returns tuples in one relation but not in another.

Example:
Table1: AllStudents(ID) → {101, 102, 103}
Table2: PassedStudents(ID) → {101, 102}

• AllStudents - PassedStudents → {103}

(viii) Referential Integrity Constraint

• Ensures foreign key values in one table exist as primary key values in another table.

• Prevents orphan records.

Example:
Table1: Student(ID, Name) → Primary Key: ID
Table2: Marks(StudentID, Score) → StudentID is foreign key referencing Student(ID)

• Marks.StudentID must exist in Student.ID.