Comprehensive Guide to DBMS Architecture, Data Models, and Abstraction Theory

Learning Resources and Course Overview

• Learning Management System (LMS) Resources: Several resources are available for practice and assessment including:
  • Practice courses.
  • Formative assessments (Live since the first of the month, though immediate completion may not be required).
  • Question banks.
  • Sample papers.
  • Practice quizzes $1$ to $3$.
• Technical Support and Communication:
  • If any components are missing from the LMS, students are instructed to mail the instructor or contact support.
  • Discussion forums are available for topical questions and should be attempted regularly.
  • Note: Technical glitches may occasionally hide the discussion forum at the end of examinations; students must be aware and report problems through the provided support mail.
• Class Participation: Attendance and active participation are mandatory. The instructor conducts periodic checks by asking questions about previous sessions.

Fundamental Concepts of DBMS

• Definition of DBMS (Database Management System):
  • It is a system software designed to manage, store, and retrieve data in an effective and structured form.
  • It consists of a collection of interlinked and interrelated data associated with a set of programs used to access that data.
  • It provides a framework for organizing data to ensure ease of access and management.

DBMS Architecture

• Definition: DBMS architecture describes how the database is designed, the structure of its storage, the organization of rows and columns, and acts as a blueprint for managing data.
• 1-Tier Architecture:
  • The database is directly available to the end-user.
  • There is no mediator between the user and the database.
  • The user interacts directly with the software (Client-Server).
  • Examples: Standalone applications, personal spreadsheets, Excel sheets, and direct SQL usage where the user puts in and saves data directly.
• 2-Tier Architecture:
  • Follows a classic Client-Server model.
  • Multiple clients communicate with a single server.
  • The server holds the database and is responsible for handling query processing and transaction management.
  • Access Protocol: Clients must send a request to the server. The server performs authentication and authorization; once the request is accepted, the server provides access to the database.
  • Responsibility: If a crash occurs or maintenance is needed, the server side is responsible.
  • Example: Banking management systems. A user might interact with application programs on their side, but critical actions like closing or deleting an account often require the main branch (server) to grant specific permission.
• 3-Tier Architecture:
  • Adds an intermediate layer (mediator) between the client and the server.
  • The client does not communicate with the database server directly.
  • Interaction Path: Client $\rightarrow$ Intermediate Layer (Mediator/Admin/DBA) $\rightarrow$ Database Server.
  • Purpose: Simplifies complexity for the user and increases security.
  • Examples:
    • Banking: Client $\rightarrow$ Bank Manager $\rightarrow$ Server.
    • E-commerce: User $\rightarrow$ Admin $\rightarrow$ Database/Transactions. The user provides data, but the admin confirms/accepts the transaction.
    • Corporate: Client $\rightarrow$ DBA (Database Administrator) $\rightarrow$ Database Server.

Data Models and Their Types

• Definition: A data model is a framework that defines the data structure and how data is organized and stored.
• Hierarchical Model:
  • Represents data in a tree-like structure.
  • Uses a Parent-Child relationship with a $1:N$ (one-to-many) relationship.
  • Features one ancestor (root) and multiple descendants (branches).
  • Examples: IBM IMS systems, airline flight systems where a main route branches into different regional sites.
• Network Model: Represents data in the form of a graph where child records can have multiple parents (many-to-many).
• Relational Model: Data is organized into tables (relations) with rows and columns. This is the basis for most modern SQL systems.
• Entity-Relationship (ER) Model:
  • Focuses on entities, attributes, and the relationships between them.
  • Entity: Represents a concept or object (equivalent to a table/column context).
  • Attribute: Specific properties or traits of an entity.
• Object-Oriented Model: Based on principles similar to programming languages like C++, Java, and Python. It treats data elements as objects that can access databases through defined methods.
• Semi-Structured Model: Involves data that does not reside in a relational database but has some organizational properties. Examples include HTML, XML, and JSON files.
• NoSQL Model: Uses documents rather than tables. It processes data using key-values or graph models without requiring traditional SQL queries or relational structures.

Data Abstraction

• Definition: The process of hiding complex internal details of the database system and showing only the essential parts to the user.
• Purpose:
  • Simplicity: Users (like general parents or non-technical end-users) do not need to understand code or complex structures to use a system like Amazon.
  • Security: Prevents unauthorized users from accessing or deleting critical source code or internal table structures.
  • Flexibility: Changes in the physical level do not affect the user experience.
• Levels of Abstraction:
  • Physical Level (Lowest Level): Describes how the data is actually stored in technical detail (minor information/storage specifics). The DBA and system designers are responsible for this level. End-users do not interact with this.
  • Logical Level (Middle Level): Describes what data is stored and the relationships between data entities (e.g., student entities, course entities, and enrollment numbers). Used by database designers and admins to manage the logical design.
  • View Level (Highest Level): The level at which different users interact with the system. It provides customized views of the database based on individual needs (e.g., different users seeing different parts of the same database).

Schema and Instances

• Schema:
  • Defines the skeletal structure and organization of the database.
  • Includes table names, column names, relationships, data types, and constraints.
  • It is generally fixed and rarely modified once the database design is finalized.
• Instance:
  • Represents the actual data stored in the database at a specific moment in time.
  • It is dynamic; data can be inserted, updated, or deleted.
  • Example: If a table "Student Details" has columns like Student Name and Roll Number, that is the Schema. If you add $20$ student records into that table, those records are the Instance.

Data Independence

• Definition: The ability to modify the database schema at one level without forcing a change at a higher level of schema.
• Physical Data Independence: The ability to modify the physical storage (where and how data is saved) without affecting the logical schema (the tables and relationships).
• Logical Data Independence: The ability to modify the logical schema (such as adding or removing columns) without affecting the external view or the application programs used by end-users.
• Benefits: Reduces maintenance costs and increases system scalability and flexibility.

Questions & Discussion

• Question: What is a DBMS?
• Response (Abhish/Mohit): DBMS stands for Database Management System. It is system software used to manage, store, and retrieve data effectively in a structured form. It is a collection of interrelated data and a set of programs to access that data.
• Question: Why is data abstraction important for security?
• Response: If end-users had access to the code or the view level allowed deletion of critical back-end components, the database would not be safe. Locking access ensures users only interact with permitted views.
• Question: What is the difference between Schema and Instances?
• Response: Schema is the structure (columns, types, constraints), while instances are the actual values or records (e.g., $20$ student records) stored at a specific time.