Database Architecture and Schema

Study Notes

There are three levels of Database Management System (DBMS) Architecture: External, Conceptual, and Internal.
External Level (View Level): This level reflects the user's perspective of the database, presenting only the relevant data needed for each user. For example, a student database can have different views for the accounting department (student name, ID, fee receipt) and the library department (student name, ID, books issued).
Conceptual Level (Logical Level): This level outlines the data stored within the database, including the relationships between different data elements. It describes all entities, their attributes, and their relationships.
Internal Level (Physical Level): This layer defines how the data is physically stored and organized within the computer system. It details the file structure, data storage methods, and access paths used to retrieve information.

A database schema acts as the blueprint or design plan for the database.
It encompasses the types of entities, their relationships and how they are expressed.
The schema can be categorized into two types:
- Physical Schema: Defines the physical level of the database.
- Logical Schema: Defines the conceptual level of the database.
A database instance represents a snapshot of the data stored in the database at a specific point in time. It reflects the current state of the database, including any inserted, deleted, or modified information.

Empty State: A newly created database without any data populated.
Initial State: The database is populated for the first time with initial data.
Current State: The database undergoes updates and modifications, leading to its current state.
Valid State: All structural constraints and rules are met, ensuring data integrity within the database.

The three-level architecture's primary objective is to ensure data independence, which means changes at a lower level should not affect the higher levels.
Logical Data Independence: The ability to modify the conceptual schema without altering the external schema. This allows for adding, removing, or changing entities, attributes, or relationships without disrupting user views or applications.
Physical Data Independence: The ability to modify the internal schema without altering the conceptual schema. This enables changes to the physical storage layout and organization without impacting the logical structure of data or existing application programs.

Menu-Based Interfaces: Offer a list of options for users to interact with the database.
Forms-Based Interfaces: Present forms for users to input data, or to retrieve specific information based on filled entries.
Graphical User Interfaces (GUIs): Provide a visual representation of the schema, allowing users to query data by manipulating diagrams.
Natural Language Interfaces: Interpret user requests expressed in natural language (like English) and retrieve the corresponding data.
Parametric User Interfaces: Designed for users with specific roles or activities requiring a limited set of operations.
DBA Interfaces: Provide privileged commands for database administrators to manage and control the database.

Data Definition Language (DDL): Used by database administrators and designers to define the database schema's structure and constraints.
Data Manipulation Language (DML): Used to manipulate and update data within the database, including inserting, deleting, and modifying information.