🏗️ Three Levels of Database Architecture (ANSI/SPARC Model)

The ANSI/SPARC model is a standardized framework that divides a database system into three distinct levels: External Level (View Level), Conceptual Level, and Internal Level (Physical Level). This architecture ensures better management, abstraction, and separation between layers of a database system.

1️⃣ External Level (View Level)

Overview:
The external level is the closest to the users. It represents how users or applications interact with the database, offering different views of the data.

Key Characteristics:

Customizes data for different user groups.
Provides multiple views of the same data to different users.
Data is presented in formats based on the user’s needs.

Example:
A Sales Manager may view data related to product sales, while a Finance Manager may see financial records and invoices. Both interact with the same data, but their views are tailored to their roles.

2️⃣ Conceptual Level

Overview:
The conceptual level describes the logical structure of the entire database. It includes information on data types, relationships, and constraints, independent of how the data is physically stored.

Key Characteristics:

Defines what data is stored and its logical relationships.
Abstracts the physical implementation of data.
Provides data independence, meaning physical changes do not affect the logical structure.

Example:
In a university database, the conceptual schema defines tables for students, courses, and enrollments, showing relationships between them without specifying how or where the data is physically stored.

3️⃣ Internal Level (Physical Level)

Overview:
The internal level is the lowest level of abstraction in the database system. It focuses on the physical storage of data, including how data is stored on disk, indexing, and optimizing data access.

Key Characteristics:

Defines the physical storage structure and data access methods (e.g., indexing, hashing).
Deals with file organization and disk block management.
Optimizes performance and storage efficiency.

Example:
In the university database, student records might be stored as disk files, with indexes created on student IDs to enable fast access.

🔗 Inter-Relationship Between Data at the Three Levels

The three levels of database architecture are interconnected, with each level serving a distinct purpose while relying on the others:

External to Conceptual: The external level provides customized views of the data to users, which are derived from the conceptual level. Changes in the external views do not affect the conceptual model.
Conceptual to Internal: The conceptual level defines the logical structure of the database, while the internal level deals with physical storage. Changes in physical storage do not impact the conceptual schema.
External to Internal: The external views are built upon the conceptual schema, which is then implemented physically at the internal level. The internal level ensures efficient data access and storage.

Conclusion

The three-level architecture (External, Conceptual, and Internal) provides a clear separation of concerns in a database system:

External level: Focuses on user-specific views.
Conceptual level: Deals with the logical structure of the data.
Internal level: Addresses physical storage and access paths.

This separation promotes data independence, making the system more flexible and easier to maintain. Changes in one level do not affect the other levels, allowing for more efficient management and scalability.

📑 Schema and Subschema in Databases

The term Schema refers to the overall structure (organization) of all data-items, including their record types, stored in a database. The schema defines the structure of the database, which is crucial before the actual setup begins.

What is Schema?

A Schema is essentially the blueprint of the database. It lists all the fields (data-items) to be stored in the database and defines their attributes such as name, type, and size. The first step when setting up a database is to define its schema.

In simpler terms, the schema is the skeleton that organizes the data in a database, showing how data will be structured and related.

What is Subschema?

A Subschema refers to the application programmer’s view of the data-items and record types that the user will work with. It is a subset of the schema and represents how data is organized from the perspective of an application or user.

When defining a schema for a database, a good practice is to start by listing the fields to be included in the database and then determine the name, type, and size of each field. This process forms the basis of defining the schema.

Data Independence

The ability to modify a schema definition at one level without affecting the schema at the next higher level is known as Data Independence.

Two Levels of Data Independence

Physical Data Independence: This refers to the ability to modify the schema at the physical level without affecting the schema at the conceptual level. In simpler terms, changes made to the storage or file organization do not affect the application programs using the data.
Logical Data Independence: This refers to the ability to modify the conceptual schema without causing any changes in the schema at the view level. It means changes in how data is logically structured do not require changes in the user interfaces or application programs.

Note:

Modifications at the physical level are occasionally necessary to improve the performance of the system.
It is more difficult to achieve logical data independence than physical data independence because application programs are heavily dependent on the logical structure of the database.

Database Models

📚 Database Models Overview

A Database Model refers to a set of tools and methodologies for describing data, data relationships, data semantics, and consistency constraints. These models help in defining how data is stored, retrieved, and manipulated within a database system. There are three primary types of database models:

Conceptual Database Models
Physical Database Models
Logical Database Models

1️⃣ Conceptual Database Models

Overview: Conceptual models describe data at the conceptual and view levels. They define the logical structure of the data and the relationships between entities, but they do not specify how the data is physically stored.

Purpose: These models focus on what data is stored and how it is logically related. They are more abstract and focus on the structure of the data without worrying about physical storage.

Example: In a university database, the conceptual model might define entities such as Student, Course, and Professor, and relationships like a student enrolling in a course and a professor teaching a course.

2️⃣ Physical Database Models

Overview: Physical models define how data is stored physically on storage devices, such as hard drives or SSDs. They focus on the organization of data, data retrieval methods, and storage efficiency.

Purpose: These models provide details on how data is stored, including file structures, indexes, and access paths. Physical models are essential for implementing the database at the system level.

Example: In a physical model, a table from the relational database might be stored in disk blocks with indexes created to speed up search queries.

3️⃣ Logical Database Models

Overview: Logical models describe data at the conceptual and view levels, focusing on the logical organization of data. They specify the database’s structure in a way that is independent of physical storage.

Purpose: Logical models bridge the gap between the conceptual and physical models. They provide a higher-level description of the database implementation and define how data should be logically organized and accessed.

Example: A relational model uses tables to represent entities and establishes relationships using foreign keys. A Customer table may have a foreign key to the Orders table to represent which customer made which orders.

🔚 Conclusion

The Conceptual, Physical, and Logical database models serve distinct roles in the database design process. The conceptual model focuses on what data is stored, the logical model defines how data is logically organized, and the physical model describes how data is stored on the system. These models work together to create an efficient and well-organized database system.

Database Models

A Database Model is a collection of tools used to describe the data, relationships, data semantics, and consistency constraints within a database.

Types of Database Models

Conceptual Database Models: These models describe data at the conceptual level and at the view level. They provide flexible structuring capabilities. One of the most widely used conceptual models is the Entity-Relationship (ER) Model. Conceptual models are often called object-based database models.
Physical Database Models: These models describe data at the lowest level and are used for implementing the database at the system level. Examples of physical models include the Unifying Model and Frame Memory Model.
Logical Database Models: These models describe data at both the conceptual and view levels. They specify the overall logical structure and offer a higher-level description of the implementation. Logical models are also known as record-based database models. Three widely accepted logical data models include:
- Relational Model
- Network Model
- Hierarchical Model

📊 Data Models and the Role of DBA

Hierarchical, Network, and Relational Data Models

Hierarchical Data Model

In the Hierarchical Data Model, relationships between records are represented in a parent-child structure, where each parent can have multiple children, but each child has only one parent. This model is best suited for representing data with a clear hierarchy.

Relationship between records is of the parent-child type.
Many-to-many relationships cannot be expressed in this model.
Simple and straightforward implementation, but not flexible for complex relationships.
Best used when there is a clear hierarchical character in the database.
Searching for a record can be difficult, as you need to go through the parent record to find the child.
Possible data inconsistency issues during updates or deletions.

Network Data Model

The Network Data Model uses pointers to represent relationships between records. In this model, each record can have multiple parent and child records, which makes it more flexible than the hierarchical model.

Relationships between records are expressed in the form of pointers or links.
Many-to-many relationships can be implemented in this model.
Record relationships are physically stored using pointers, making implementation more complex.
More suitable for representing many-to-many relationships.
Searching for records is easier, as multiple access paths are available.
No data inconsistency problems during updates or deletions as data is stored in only one place.

Relational Data Model

The Relational Data Model represents relationships using tables (relations) where each record is identified by a unique key. It is the most commonly used data model in modern database systems.

Relationships between records are represented by a relation (table) with keys for each record.
Many-to-many relationships can be easily implemented.
Relationship implementation is simple using keys or composite key fields.
Best suited for representing real-world objects and relationships.
Data is logically organized in rows and columns within tables, and does not maintain physical connections between records.
Searching for records is efficient using indexed key fields.
Data integrity is maintained using methods like normalization to ensure consistency.

Which Data Model to Use?

The choice of data model depends on the organization's goals and requirements:

The organization’s primary goals and requirements.
The volume of daily transactions that will be performed.
The estimated number of queries or inquiries that will be made.

Role of Database Administrator (DBA)

The Database Administrator (DBA) is responsible for the creation, modification, and maintenance of the database. This role is crucial for ensuring that the database runs smoothly and efficiently.

Primary Responsibilities of a DBA

Schema Definition: The DBA defines the original database schema by writing a set of definitions, which are then converted into tables stored in the data dictionary.
Storage Structure and Access Method Definition: The DBA specifies the storage structures and access methods for the database, ensuring optimal data retrieval.
Schema and Physical Organization Modification: The DBA modifies the schema and physical organization of the database as needed to meet the changing needs of the organization or to improve performance.
Granting of Authorization for Data Access: The DBA ensures that only authorized users have access to the database, and maintains user profiles and permissions.
Routine Maintenance: The DBA defines procedures for recovering data after failures and minimizes data loss due to human, natural, or hardware-related issues.
Maintaining Integrity: The DBA ensures that the integrity of the database is maintained through mechanisms like integrity constraints, which are checked during updates.