BLACK FRIDAY OFFER: Flat 50% Off with Free Self Learning Course | Use Coupon BLACKFRIDAY50

- SQL Server Blogs -

The Database as Part of the System Architecture



Introduction

The design of a database system is extraordinarily impacted by the basic PC framework on which it runs, specifically by such parts of PC architecture as network administration, parallelism, and distribution:

The system’s administration of PCs enables a few jobs to be executed on a server system, and a few tasks to be executed on client systems. This division of work has prompted client-server database systems.

Parallel processing inside a PC system permits database-framework exercises to speed up, enabling the quick reaction to transactions. It is the parallelism offered by the underlying computer system. The requirement for parallel query handling has prompted parallel database systems.

Read: What Is SQL Queries? List Of All SQL Queries With Examples

A distributed database is a database where not all storage elements are appended to a single processor. It might be kept in numerous PCs, situated in a common physical area; or might be scattered over a system of interconnected PCs.

If you have been constantly searching for “database system architecture” or “database system architecture in dbms”. You have come to the right place. In the following article, we will study the architecture of a database system, beginning with the customary centralized systems, decentralized, and finally distributed database systems. Let’s get started with the database system concepts and architecture.

Centralized and Client-Server Architectures

A centralized architecture infers the accessibility of a solitary or a couple of entities that have power over the whole Network.

Read: SQL Server Indexes-All You Need to Know

Client/server architecture is a computing model in which the server has, conveys and oversees a large portion of the resources and services to be consumed by clients.

Centralized Systems

This is a framework that utilizes client/server architecture where at least one customer node is legitimately associated with a central server. This is the most generally utilized kind of system in numerous associations where the customer sends a request to an organization server and gets the response.

Centralized Systems - Concepts of Database ArchitectureGet here for complete SQL server self learning module

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Decentralized Systems

In this framework, each hub makes its own decision. The final behavior of the framework is the total of the decisions of the individual hubs.

Concepts of Database Architecture - Decentralized system

Read: SQL Intersect Operator With Example

Distributed Systems

It is made out of different equipment and programming (on the whole called components) that speak with one another just by the exchange of messages. These parts are set inside a solitary network.

Distributed systems - Concepts of Database Architecture

Comparison between Centralized, Decentralized and Distributed system

Centralized Decentralized Distributed
A gathering of individuals or a corporate entity holds the entire control over the usefulness of the system The control lies with the end-user Here components are located on various networked PCs
All the data in one server for use High availability -Some nodes are always online Less time to get a response

Server System Architectures

Server systems Architecture can be divided into transaction servers and data servers.

Transaction Server

This is a particular kind of server that deals with the tasks of software-based transactions or transaction processing.

Read: Difference Between Clustered and Non-Clustered Index in the SQL

Data Server

This performs tasks, such as data analysis, storage, data manipulation, archiving, and other tasks using client/server architecture.

Data Server - Concepts of Database Architecture

Parallel Systems

This is a system where computation is done in parallel, on different simultaneously utilized processing units. They might be the core of a similar processor, various processors, or even single-core with emulated concurrent execution (time-sharing, cooperative or non-cooperative multitasking).

The Database as Part of the System Architecture - Parallel Systems

Parallel database System

This kind of architecture attempts to improve execution through parallelization of different tasks, for example, loading data, building records and assessing queries.

Read: SSRS Pie Chart - Having a Slice of the Pie

Speed Up vs. Scale-Up

Speed up

Scale Up

It is the effect of applying an expanding number of assets to a fixed measure of work to accomplish a corresponding in execution times 

It is the ability to help a similar exhibition level (response time) when both outstanding task at hand and assets increment proportionately

The ANSI/SPARC architecture

This architecture is divided into three levels

  • Internal
  • Conceptual
  • External

Internal Level

It is concerned with the way the data is physically stored.

External level

It is concerned with the way the data is viewed by individual users.

Read: How to Prevent SQL Injection Attacks?

Conceptual level

It is the link between the above two.

Conceptual level - Concepts of Database Architecture

Database Architecture

It uses programming dialects to structure a specific sort of utilization for associations or affiliations. It focuses on the arrangement, progression, utilization, and backing of Computer programs that store and compose data for associations, workplaces, and foundations. A database draftsman makes and completes applications to address the issues of customers.

The plan of a DBMS relies upon its architecture. It very well may be incorporated or decentralized on various leveled. The architecture of a DBMS can be viewed as either a single-tier or multi-tier. The levels are classified as follows: 

  1.   1-tier architecture
  2.   2-tier architecture
  3.   3-tier architecture
  4.   n-tier architecture 

The structure of a DBMS depends upon its design. It tends to be concentrated or decentralized or hierarchical. The design of a DBMS can be seen as either a single-tier or multi-tier. N-level engineering partitions the entire system into related however self-ruling n modules, which can be unreservedly adjusted, modified, changed, or supplanted. 

Read: How to Download and Install Microsoft SQL Server Data Tools?

In 1-tier architecture, the DBMS is the main substance where the client straightforwardly sits on the DBMS and utilizes it. Any progressions done here will be done on the DBMS itself. It doesn't give supportive apparatuses to end-clients. Database planners and programming engineers ordinarily need to use single-level design. 

If the design of DBMS is 2-tier, by then it must have an application through which the DBMS can be gotten to. Programming engineers utilize a 2-level plan where they get to the DBMS using an application. Here the application level is totally free of the database to the extent of action, structure, and programming.

3-tier design secludes its levels from each other subject to the multifaceted idea of the customers and how they use the data present in the database. It is the most extensively used design to structure a DBMS.

 N-tier architecture would include separating an application into three distinct levels.

Have you taken the demo class for Microsoft SQL server training yet? Hurry, It’s Free!

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

1-tier architecture

This is the simplest of Database Architecture where the Client, Server, Database all reside on the same machine. The simplest example is when we install SQL Server Database in one machine and execute queries in it.

1-tier architecture

2-tier architecture

This is a software architecture in which a presentation layer or interface runs on a customer, and a data layer or data structure gets put away on a server. Isolating these two parts into various areas represents two-tier architecture, rather than single-tier architecture. Different sorts of multi-tier architecture include extra layers in distributed software design.

2-tier architecture

Read: Different Types of SQL Keys: Example and Uses

3-tier architecture

Three-tier architecture is a client-server architecture in which the valuable procedure logic, data access, computer data storage, and user interface are made and kept up as independent modules on an autonomous platform. Three-tier architecture is a software design pattern and well-established software architecture.    

3-tier architecture

n-tier architecture

Multitier architecture or multilayered architecture or N-Tier architecture is a client-server architecture in which presentation, application processing, and data management functions are physically isolated. The broadest utilization of multi tier architecture is the three-tier architecture.

Read: How to Clear SQL Server Transaction Log File with DBCC Shrinkfile

What do we understand by a ‘Tier’?

A "tier" can likewise be alluded to as a "layer".

Three layers involved with the application to be specific - Presentation Layer, Business Layer, and Data Layer. Let us see each layer in detail:

Presentation Layer

It is generally called the Client layer. It is the highest layer of an application. This is the layer we see when we utilize an application. By using this layer we can get to the website pages or application structures on the off chance that it is a work area application. The primary value of this layer is to converse with the Application layer. This layer passes the data which is given by the customer with respect to console exercises, mouse snaps to the Application Layer. 

Read: Difference Between Clustered and Non-Clustered Index in the SQL

For instance, the login page of Gmail where an end client could see text boxes and buttons to enter client id, secret key and to tap on sign-in. 

In basic words, it is to view the application.

Application Layer

It is generally called Business Logic Layer which is generally called a sensible layer. As indicated by the Gmail login page model, when a customer taps on the login button, the Application layer works together with the Database layer and sends anticipated that information should the Presentation layer. It controls an application's helpfulness by performing nitty-gritty handling. This layer goes about as an arbiter between the Presentation and the Database layer. Complete business rationale will be written in this layer. 

Read: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

In essential words, it is to perform assignments on the application.

Data Layer

This layer stores data. The application layer speaks with the Database layer to recover the data. It contains techniques that associate the database and performs required activities e.g.: insert, update, delete and so forth.

In basic words, it is to share and recover the data.

Software Process Model

A software process model is a streamlined portrayal of a software process. Each model represents a procedure from a particular point of view.

Query Processing

It is the process of translating a query into low-level instructions in which the DBMS can easily work with.

Storage Management

This usually refers to the management of Computer data storage, which includes memory management.

Read: What Is The Difference Between Tables And Views In SQL?

Transactions, Concurrency Control, and Recovery

Transaction

Concurrency Control

Recovery

A transaction, in the context of a database, is a logical unit that is independently executed for data retrieval or updates

Concurrency control is the procedure in DBMS for managing simultaneous operations without conflicting with each other

Recovery is the process of restoring a database to the correct state in the event of a failure

So this was it, a complete guide to architecture of database system, with comprehension on key database system concepts and architecture. Hope this article has helped you understand the architecture of database system.

Signup for the SQL developer Training to learn the basics of database querying!

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Summary

In this write-up, we have attempted to explain detailed architecture of a database system by contacting all the potential parts of the database as a piece of the framework design. This post will give an intriguing diagram to an individual who needs to comprehend database system architecture and connection between database and system architecture. If you want to have powerful hold on database system architecture in dbms, take Microsoft SQL server training to have in-depth understanding of relational databases and complete architecture of a database system.

Read: Top 20 SSAS Interview Questions and Answers For Freshers, Experienced 

SQL Tutorial Overview


    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 04 Dec 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

14 days 14 Dec 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

9 days 09 Dec 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

11 days 11 Dec 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

4 days 04 Dec 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

5 days 05 Dec 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

4 days 04 Dec 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

4 days 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

0 day 30 Nov 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

5 days 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

-1 day 29 Nov 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

19 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews