Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
DBMS Articles
Page 2 of 27
MySQL PARTITION BY Clause
The PARTITION BY clause in MySQL groups rows into separate partitions (windows) for use with window functions like RANK(), DENSE_RANK(), LEAD(), and LAG(). It must always be used inside an OVER() clause. If omitted, the entire table is treated as a single partition. Syntax window_function(expression) OVER (PARTITION BY column_name [ORDER BY column_name] [frame_clause]) Example Consider a Hacker table ? CREATE TABLE Hacker ( h_id INT, h_name VARCHAR(50), challenge_id INT, score INT ); INSERT INTO Hacker ...
Read MoreMultiversion Timestamp Ordering
Multiversion Timestamp Ordering (MVTO) is a concurrency control technique that maintains multiple versions of each data item. Each version has a unique timestamp, and transactions access the appropriate version based on their own timestamp, preventing conflicts and deadlocks. Components Each version Xi of a data item maintains − Value − The data content of that version. Read_TS(Xi) − Largest timestamp of any transaction that successfully read Xi. Write_TS(Xi) − Timestamp of the transaction that created Xi. How MVTO Works ...
Read MoreMultiple Granularity Locking in DBMS
Multiple granularity locking provides different levels of locks for different database objects − from the entire database down to individual records. This maximizes concurrency while maintaining consistency. Granularity Hierarchy Database Area 1 Area 2 File 1 File 2 File 3 File 4 R1 ...
Read MoreMOSS Concurrency Control Protocol (Distributed Locking in Database)
The MOSS (Multi-Object Synchronized Scheduling) Concurrency Control Protocol manages concurrency in distributed database systems by handling hierarchical transactions with parent-child relationships and lock inheritance. Distributed Locking Types Optimistic locking − No locks acquired upfront; conflicts detected at commit time. Pessimistic locking − Locks acquired before accessing shared resources; conflicts prevented from the start. MOSS Protocol MOSS is a hierarchical locking protocol for nested transactions where subtransactions can inherit locks from ancestors ? ...
Read MoreModeling of UNION Types Using Categories
In DBMS, superclass/subclass relationships typically involve a single superclass. However, a union type or category represents a subclass that is a subset of the UNION of multiple distinct superclass entity types. Example: Vehicle Owner Category Consider a motor vehicle registration database with three entity types: PERSON, BANK, and COMPANY. A vehicle owner can be any one of these. We create a category called OWNER that represents the union of all three ? PERSON BANK COMPANY ...
Read MoreDifference between DBMS and RDBMS
A DBMS (Database Management System) is software used to create, update, delete, and maintain a database with controlled access to data. An RDBMS (Relational Database Management System) is an enhanced type of DBMS based on the relational model, storing data in tables with defined relationships. What is a DBMS? DBMS stores data in the form of files using navigational or hierarchical structures. There is no relationship between data elements, so it does not support distributed databases. DBMS is often used in small organizations to deal with small amounts of data handled by a single user. Examples include file ...
Read MoreMultiple Relation Queries and JOIN Ordering
Multiple relation queries involve joining several tables to produce a result set. The order in which JOIN operations are executed can significantly affect query performance. Query optimization determines the most efficient execution plan by evaluating different JOIN orderings. Challenges The query optimizer must determine the most efficient execution plan considering join selectivity, data size, distribution, and available access paths. With n tables, there are n! possible join orderings, making optimal selection a complex process. Table A 1M rows Table B ...
Read MoreMultiversion Concurrency Control Techniques
This article has a truncated Drawbacks section at the end. Let me fix that and clean up the SVGs: It is essential to maintain data consistency and prevent concurrency issues in database systems when multiple transactions access the same data simultaneously. Multiversion Concurrency Control (MVCC) techniques provide an efficient way to achieve this by maintaining multiple versions of data items. Concurrency Control Protocols Database systems provide concurrency control to ensure isolation among transactions, maintain consistency, and resolve conflicts arising from read−write and write−read operations. The main techniques used are − Two−phase locking protocol Timestamp ordering ...
Read MoreDifference between Database and a Blockchain
A database and a blockchain are both systems for storing and managing data, but they differ fundamentally in architecture, control, and data modification capabilities. A database uses centralized storage managed by administrators, while a blockchain uses decentralized, immutable storage distributed across a network. Database A database is a data structure comprised of tables and schemas to store user and system information. It provides SQL to create, read, update, and delete records. A DBMS (Database Management System) manages the database, and database administrators control access and modify sensitive data. A database follows a client-server model architecture. Blockchain ...
Read MoreDifferent Types of Database Users
Database users interact with data to update, read, and modify the given information daily. There are various types of database users and we will learn in detail about them. Database users can be divided into the following types − Naive users / Parametric users Sophisticated users End Users Application Programmer or Specialized users or Back-End Developer System Analyst Database Administrator (DBA) Temporary Users or Casual Users These users can access the database and recover the data using various applications. Let’s have a quick understanding of all the types in detail − End Users/Parametric Users These users access the ...
Read More