DBMS Architecture, Data Independence, and Languages Notes

DBMS Architecture, Data Independence, Languages

Aim of the Session

  • To familiarize students with the basic concepts of:
    • Three-level architecture of DBMS
    • Data Independence
    • DBMS languages

Instructional Objectives

This session is designed to:

  • Explain the DBMS Three-Level Architecture.
  • Explain Data Independence and its different levels.
  • Introduce different types of DBMS languages.

Learning Outcomes

At the end of this session, students should be able to:

  • Understand the three-level architecture of DBMS.
  • Understand Data Independence and its different levels.
  • Recognize different types of DBMS languages.

DBMS – Three Level Architecture

  • A database management system that provides three levels of data is said to follow a three-level architecture.
    • External level (i.e., External Schema)
    • Conceptual level (i.e., Conceptual Schema)
    • Internal level (i.e., Internal Schema)

Data Independence

  • Data independence is the ability to modify the schema without affecting the programs and the application to be rewritten.

DBMS Languages

  • A DBMS has appropriate languages and interfaces to express database queries and updates.
  • Database languages can be used to read, store, and update the data in the database.

External Level

  • The external level is at the highest level of database abstraction. At this level, there will be many views defined for different users' requirements. A view will describe only a subset of the database.
  • Any number of user views may exist for a given global or sub schema.
  • For example, each student has a different view of the time table. The view of a student of BTech (CSE) is different from the view of the student of BTech(ECE). Thus, this level of abstraction is concerned with different categories of users.
  • Each external view is described by means of a schema called a sub schema or external schema.

Conceptual Level

  • At this level of database abstraction, all the database entities and the relationships among them are included. One conceptual view represents the entire database. This conceptual view is defined by the conceptual schema.
  • The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.
  • It describes all the records and relationships included in the conceptual view. There is only one conceptual schema per database. It includes features that specify the checks to relation data consistency and integrity.

Internal Level

  • It is the lowest level of abstraction closest to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods to be used by the database. The internal view is expressed by an internal schema.
  • The following aspects are considered at this level:
    • Storage allocation e.g., B-tree, hashing.
    • Access paths e.g., specification of primary and secondary keys, indexes etc.
    • Miscellaneous e.g., Data compression and encryption techniques, optimization of the internal structures.

Example for Each level

  • External Level
    • Sales Officer View 1: Item Name, Price
    • Inventory Controller View 2: Item Name, Stock
  • Conceptual Level
    • Item Number: Character (6)
    • Item Name: Character(30)
    • Price: Numeric(5,2)
    • Stock: Numeric(4)
  • Physical Level
    • Item #: Type Byte(6), offset = 0, Index = Ix, Length=50
    • Name: Type Byte(30), offset = 6
    • Price: Type Byte(8), offset = 36
    • Stock: Type Byte(4), offset = 44

Data Independence

  • Data independence is the ability to modify the schema without affecting the programs and the application to be rewritten.
  • Data is separated from the programs, so that the changes made to the data will not affect program execution and the application.
  • The main purpose of the three levels of data abstraction is to achieve data independence.
  • If the database changes and expands over time, it is very important that the changes in one level should not affect the data at other levels of the database. This would save time and cost required when changing the database.

Levels of Data Independence

  • There are two levels of data independence based on three levels of abstraction.
    • Physical Data Independence
    • Logical Data Independence
  • The three schema architecture can be used to explain the concept of data independence, which can be defined as the capacity to change the schema of one level without requiring modifications at another level.

Physical Data Independence

  • Physical data independence is the ability to modify the physical schema without making it necessary to rewrite application programs.
  • It is the capacity to change the internal schema without having to change the conceptual schema or external schema.

Logical Data Independence

  • Logical data independence is the ability to modify the conceptual schema without making it necessary to rewrite application programs.
  • It is the capacity to change the conceptual schema without having to change the external schema.

DBMS Architecture

  • The DBMS design depends upon its architecture.
  • The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers, and other components that are connected with networks.
  • The client/server architecture consists of many PCs and a workstation which are connected via the network.
  • DBMS architecture depends upon how users are connected to the database to get their request done.

Types of DBMS Architecture

  • 1-tier Architecture
  • 2-tier Architecture
  • 3-tier Architecture
1-Tier Architecture
  • In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and use it.
  • Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
  • The 1-Tier architecture is used for the development of the local application, where programmers can directly communicate with the database for the quick response.
2-Tier Architecture
  • The 2-Tier architecture is the same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, APIs like ODBC, and JDBC are used.
  • The user interfaces and application programs are run on the client-side.
  • The server side is responsible for providing the functionalities like query processing and transaction management.
  • To communicate with the DBMS, the client-side application establishes a connection with the server side.
3-Tier Architecture
  • The 3-Tier architecture contains another layer between the client and server. In this architecture, the client can't directly communicate with the server.
  • The application on the client-end interacts with an application server which further communicates with the database system.
  • The end user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
  • The 3-Tier architecture is used in the case of large web applications.

DATABASE LANGUAGES IN DBMS

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)
  • Data Query Language (DQL)
Data Definition Language (DDL)
  • Consists of the SQL commands that can be used to define the database schema.
  • Deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
  • DDL is a set of SQL commands used to create, modify, and delete database structures but not data.
  • Examples: create, drop, alter, truncate
Data Manipulation Language (DML)
  • SQL commands that deal with the manipulation of data present in the database.
  • Includes most of the SQL statements.
  • Controls access to data and to the database.
  • Examples: insert, update, delete
Data Control Language (DCL)
  • Includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
    • GRANT: This command gives users access privileges to the database.
    • REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
Transaction Control Language (TCL)
  • Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete.
  • If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.
  • The following TCL commands are used to control the execution of a transaction:
    • COMMIT: Commits a Transaction.
    • ROLLBACK: Rollbacks a transaction in case of any error occurs.
    • SAVEPOINT: Sets a save point within a transaction.
Data Query Language (DQL)
  • A portion of a SQL statement that allows you to get and organize data from a database.
  • Use the SELECT command to extract data from a database in order to perform actions on it. It is the same as the projection operation in relational algebra.
  • The result of a select statement on a table or collection of tables is compiled into a new temporary table, which is subsequently displayed or received by a program, i.e., a front-end.

DBMS Languages and Their Commands

  • DDL
    • Create
    • Drop
    • Alter
    • Truncate
  • DML
    • Insert
    • Update
    • Delete
  • DCL
    • Grant
    • Revoke
  • TCL
    • Commit
    • Rollback
    • Savepoint
  • DQL
    • Select

TYPES OF DBMS

  • Object Based Databases
  • Hierarchical Databases
  • Network Databases
  • Relational Databases
Object based Databases
  • Object-based databases use concepts such as entities, attributes, and relationships.
Hierarchical Databases
  • Hierarchical Database model is one of the oldest database models. Information Management System (IMS) is based on this model
    *Example
    *Organization chart
Network Databases
  • The Network Databases represents data with a graph. The main difference of the network model from the hierarchical model, is its ability to handle many to many (N:N) relations
Relational Databases
  • Relational Databases stores data in the form of tables. This concept purposed by Dr. E.F. Codd, a researcher of IBM in the year 1960’s

Activities/ Case Studies/ Important Facts Related to the Session

  • Similar to a client-server architecture.
  • Faster access, Easier to maintain and can handle multiple users simultaneously.
  • Used when we wish to access DBMS via applications and APIs.
  • Has scalability and security issues because of the direct client-server connection.

Summary

  • The Database Management System (DBMS) architecture shows how data in the database is viewed by the users. It is not concerned about how the data are handled and processed by the DBMS.
  • It helps in implementation, design, and maintenance of a database to store and organize information for companies.
  • The concept of DBMS depends upon its architecture. The architecture can be designed as centralized, decentralized, or hierarchical.