let's say you're using a program called Tally to help you with your business. You don't need to know how Tally does all the math and accounting behind the scenes, you just need to know how to use it to make invoices and record payments. This is like abstraction, where you're only focusing on the important things you need to know to use Tally, and not worrying about the rest.

Another example is when you're driving a car, you don't need to know how the engine, transmission, and brakes work in order to drive the car. You just need to know how to use the gas pedal, steering wheel, and brakes to drive it. This is like abstraction too, where you're only focusing on the important things you need to know to drive the car, and not worrying about the rest.

Let's say we have an e-commerce website called "MegaMart" that sells a wide variety of products. The website has different teams such as the logistics team, customer service team, and many more. However, not all teams have access to all the data stored on the website.

For example, the logistics team only has access to customer addresses for delivery purposes. They do not have access to customer comments or feedback. This is because the comments and feedback are used by the customer service team to improve the overall customer experience and should not be accessed by the logistics team.

Similarly, the customer service team only has access to customer comments, feedback and phone numbers for customer support. They do not have access to customer addresses as that is the responsibility of the logistics team.

By implementing a DBMS (Database Management System) abstraction, the website can control access to the data and ensure that the correct teams have access to the information they need to perform their specific tasks. This improves the security and efficiency of the website, as well as protects the privacy of the customers.

The major purpose of DBMS is to provide users with an abstract view of the data. That is, the system hides certain details of how the data is stored and maintained.

To simplify user interaction with the system, abstraction is applied through several levels of abstraction.

The main objective of three level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once in the database.

The three levels of the architecture are: external level, conceptual level, and internal level.

ℹ️ Physical Level/ Internal Level:

The physical level, also known as the internal level, is the lowest level of abstraction in a database management system (DBMS). It describes how the data is actually stored on the physical storage media, such as hard drives or solid-state drives.

At this level, the DBMS uses low-level data structures, such as files, blocks, and pages, to organize and store the data. These data structures are used to optimize the performance of the database, such as by reducing disk I/O and minimizing the amount of data that needs to be read or written.

For example, in a database for a retail store, at the physical level, the data is stored in a specific format, like binary or binary large object (BLOB) format. The DBMS uses specific data structures such as B-tree, Hash tree, etc to store the data in a way that allows for efficient access and retrieval. This can include storage allocation, data compression, and encryption to protect the data.

The goal of the physical level is to define algorithms and techniques that allow efficient access to data, while minimizing the use of resources such as disk I/O and memory. This is accomplished by using techniques such as indexing, caching, and data partitioning, which can greatly improve the performance of the database.

ℹ️ Logical level / Conceptual level:

eCommerce website's database:

This table represents information about orders made on the eCommerce website. Each row in the table represents a single order and contains information about that order such as the Order ID, Customer ID, Product ID, Quantity and Price.

The Order ID is a unique identifier for each order, the Customer ID and Product ID are the foreign keys that link to the customers and products table respectively. Quantity and Price are the quantity of the product purchased and the total price of the order

The table also describes relationships between the data. For example, the Customer ID and Product ID columns have a one-to-many relationship with the customers and products table respectively, meaning that a single customer can have multiple orders and a single product can be included in multiple orders.

Users interacting with this table at the logical level don't need to know about the physical level. They can simply look at the table and understand the relationships between the different pieces of information. However, the person responsible for maintaining the database (the DBA) would need to know about the physical level in order to ensure that the table is stored and organized efficiently on a computer or server.

ℹ️ External level / View level:

The customer can see products, prices, and reviews, but cannot see the inventory levels or financial information. The administrator can see all the information in the database, including inventory levels and financial information.

For example, a customer logs into the eCommerce site and sees a list of products, their prices, and customer reviews. They can also add items to their cart and complete a purchase. This is the customer's view of the database.

On the other hand, an administrator logs into the site and sees the same list of products, but also has access to the inventory levels and financial information. They can also add, remove or update products, shipping details, and payment methods. This is the administrator's view of the database.

In this way, the database is separated into different schemas, or views, to simplify the user's interaction with the system and provide a security mechanism to prevent unauthorized access to certain parts of the database.

A data model is a conceptual tool that is used to describe the design of a database at a logical level. It provides a way to organize and structure data, as well as define relationships, semantics, and consistency constraints.

Database languages are used to interact with a database and perform different types of operations on it. There are two main types of database languages: Data definition language (DDL) and Data manipulation language (DML).

• DDL is used to specify the database schema. It is used to create, alter, and delete database objects such as tables, indexes, and views. For example, in SQL, the CREATE TABLE statement is used to create a new table, and the ALTER TABLE statement is used to modify an existing table.

• DML is used to express database queries and updates. It is used to insert, update, and delete data in the database. DML also includes a query language, which is used to retrieve information from the database. For example, in SQL, the SELECT statement is used to retrieve data from a table, the INSERT statement is used to insert new data into a table, and the UPDATE statement is used to modify existing data in a table.

Both DDL and DML are present in most DB languages, like SQL. For example, in SQL, the CREATE TABLE statement is used to create a new table, which is a DDL statement, and the SELECT statement is used to retrieve data from a table, which is a DML statement.

DDL's consistency constraints are checked every time a DB is updated. For example, a constraint could be that a certain column must contain a unique value for each row in a table.

DML's data manipulation involves retrieval, insertion, deletion and updating of information stored in DB. For example, in SQL, the SELECT statement is used to retrieve data from a table, the INSERT statement is used to insert new data into a table, the DELETE statement is used to delete data from a table, and the UPDATE statement is used to modify existing data in a table.

To summarise, database languages are used to interact with a database and perform different types of operations on it. DDL is used to specify the database schema, DML is used to express database queries and updates and it includes a query language which is used to retrieve information from the database.

In order for an application program to access a database, it needs to use a database management system (DBMS) API. An API (Application Programming Interface) is a set of routines, protocols, and tools for building software and applications.

For example, a banking system's module generating payrolls may need to access a database in order to retrieve employee information. The application program, which is written in a host language such as C/C++ or Java, would use the appropriate API to send data manipulation language (DML) statements (e.g., SELECT, INSERT, UPDATE, DELETE) to the database. The API would then take care of sending the statements to the DBMS, which would execute them and return the results to the application program.

Two common APIs used to access databases from application programs are Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC). ODBC is a Microsoft API that allows programs written in C/C++ to access a variety of databases, while JDBC is a Java API that allows Java programs to access databases. Both APIs provide a set of functions and methods that can be used to connect to a database, send SQL statements, and retrieve results.

A Database Administrator (DBA) is a person responsible for the overall management and performance of a database or a group of databases. They have central control over both the data and the programs that access the data. They are responsible for ensuring the availability, performance, and security of the database.

An example of a DBA in the real world could be someone who works for a large retail company. This person would be responsible for managing the company's databases that store information such as customer data, sales data, and inventory data. They would be responsible for tasks such as:

1. Schema Definition: creating and maintaining the structure of the databases, including table and field definitions.
2. Storage structure and access methods: determining how the data should be stored and optimized for quick retrieval.
3. Schema and physical organization modifications: making changes to the database structure as needed.
4. Authorization control: managing user access to the databases and ensuring that only authorized individuals have access to sensitive data.
5. Routine maintenance: performing regular tasks such as backups and security patches to ensure the integrity and availability of the data.
6. Any upgrades: Upgrading the database software as per the requirement and compatibility. This DBA would work closely with the company's IT department and other teams to ensure that the databases are running smoothly and that the data is accurate and accessible. They would also be responsible for troubleshooting any issues that may arise with the databases and ensuring that the data is protected from unauthorized access or breaches.

T1 Architecture: Imagine you have a small store, and you keep track of your inventory using a simple database on your computer. The database, the server, and the program you use to access the database all run on the same machine. This is an example of T1 architecture.

Single Tier DBMS Architecture is used whenever:

• The data isn't changed frequently.
• No multiple users are accessing the database system.
• We need a direct and simple way to modify or access the database for application development.

Highlights:

1- Simplest DBMS architecture.

2- All the components of DBMS, i.e., the server, database, and client, reside on a single system.

3- The user can directly access the database.

4- Used when data isn't changing frequently.

5- Suitable for programmers, database designers, and single-user access.

T2 Architecture: Now imagine your store is growing and you want to access your inventory database from multiple computers. You set up a separate server machine to run the database and allow other computers, or clients, to connect to it and access the data through a program. This is an example of T2 architecture.

The main advantages of having a two-tier architecture over a single tier are:

• Multiple users can use it at the same time. Hence, it can be used in an organization.
• It has high processing ability as the database functionality is handled by the server alone.
• Faster access to the database due to the direct connection and improved performance.
• Because of the two independent layers, it's easier to maintain.

*Note - The main disadvantages of Two-Tier DBMS Architecture are:

• Scalability - As the number of clients increases, the load on the server increases. Thereby declining the performance of the DBMS and, in turn, the client-side application.

• Security - The Direct connection between the client and server systems makes this architecture vulnerable to attacks.

Highlights:

1- Similar to a client-server architecture.

2- Faster access, Easier to maintain, and can handle multiple users simultaneously.

3-Used when we wish to access DBMS via applications and APIs.

4-Has scalability and security issues because of direct client-server connection.

T3 Architecture: As your store becomes even bigger, you decide to create a web application for customers to view and purchase items online. The web application runs on a separate server, called an application server, which communicates with the database server to retrieve and update data. The customers access the web application through their web browsers on their own computers, which act as the clients. This is an example of T3 architecture.

• Scalability - Since the database server isn't aware of any users beyond the application layer and the application layer implements load balancing, there can be as many clients as you want.
• Data Integrity - Data corruption and bad requests can be avoided because of the checks performed in the application layer on each client reques.
• Security - The removal of the direct connection between the client and server systems via abstraction reduces unauthorized access to the database.

*Note - In Three Tier DBMS Architecture, an additional layer (Application Layer) is added between the Client and the Server. This increases the number of layers present between the DBMS and the end-users, making the implementation of the DBMS structure complex and difficult to maintain.

Highlights:

1- Most widely used DBMS architecture.

2- Follows Client-Application-Server architecture.

3- Enhanced security, data integrity, and scalability.

4-Has complexity and maintenance issues because of the extra layer.