Distributed DBMS Quick Guide

https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm
Admin
Distributed DBMS Quick Guide is an educational resource that explains distributed database concepts,...
Tokens:17,265
Snippets:175
Trust Score:10
Update:7 months ago
Show doc for...
Tokens:
Raw
### Horizontal Fragmentation Example (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL code illustrates horizontal fragmentation by creating a new table 'COMP_STD' that includes all columns from the 'STUDENT' table but only for students enrolled in the 'Computer Science' course. This is useful for partitioning data based on specific criteria.

```sql
CREATE COMP_STD AS 
   SELECT * FROM STUDENT  
   WHERE COURSE = "Computer Science";
```

--------------------------------

### Vertical Fragmentation Example (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL code demonstrates vertical fragmentation by creating a new table 'STD_FEES' containing only the 'Regd_No' and 'Fees' columns from the 'STUDENT' table. This is useful for separating sensitive or frequently accessed data.

```sql
CREATE TABLE STD_FEES AS 
   SELECT Regd_No, Fees 
   FROM STUDENT;
```

--------------------------------

### Relational Algebra Combination of Projection and Selection

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Shows how to combine Projection and Selection operations in Relational Algebra to retrieve specific data. This example filters students by gender and course, then projects their names. It also illustrates an alternative approach using a rename operation for intermediate results.

```relational-algebra
$\pi_{Name}(\sigma_{Gender = \small "Female" AND \: Course = \small "BCA"}{(STUDENT)})$
```

```relational-algebra
FemaleBCAStudent \leftarrow \sigma_{Gender = \small "Female" AND \: Course = \small "BCA"} {(STUDENT)}
Result \leftarrow \pi_{Name}{(FemaleBCAStudent)}$
```

--------------------------------

### Relational Algebra Union Operation

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Demonstrates the Union operation in Relational Algebra, which combines the results of two operations, removing duplicate tuples. This example finds all students who are either in Semester 1 or are pursuing the BCA course.

```relational-algebra
Sem1Student \leftarrow \sigma_{Semester = 1}{(STUDENT)}
BCAStudent \leftarrow \sigma_{Course = \small "BCA"}{(STUDENT)}
Result \leftarrow Sem1Student \cup BCAStudent
```

--------------------------------

### SQL Referential Integrity Constraint Example

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL statement demonstrates how to enforce referential integrity by creating a STUDENT table with a foreign key referencing the HOSTEL table. The foreign key ensures that the S_HOSTEL value in the STUDENT table must either exist as a primary key in the HOSTEL table or be NULL.

```sql
CREATE TABLE STUDENT (
   S_ROLL INTEGER PRIMARY KEY,
   S_NAME VARCHAR2(25) NOT NULL,
   S_COURSE VARCHAR2(10),
   S_HOSTEL VARCHAR2(5) REFERENCES HOSTEL
);
```

--------------------------------

### SQL ALTER TABLE Statement to Add Column

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Modifies the structure of an existing table by adding a new column. This example adds an 'ADDRESS' column with a VARCHAR2 data type to the 'STUDENT' table.

```sql
ALTER TABLE STUDENT
ADD ( ADDRESS VARCHAR2(50) );
```

--------------------------------

### Defining Data Type Integrity Constraints (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL statement defines a 'HOSTEL' table with data type integrity constraints. It specifies that 'H_NO' must be a VARCHAR2 of 5 characters, cannot be NULL, and must start with 'H'. The 'CAPACITY' field is an INTEGER and must not exceed 150. These constraints ensure data validity at the point of entry.

```sql
CREATE TABLE HOSTEL (
   H_NO VARCHAR2(5) NOT NULL,
   H_NAME VARCHAR2(15),
   CAPACITY INTEGER,
   CHECK ( H_NO LIKE 'H%'),
   CHECK ( CAPACITY <= 150)
);
```

--------------------------------

### SQL to Relational Algebra: Nested Sub-queries for Employee Project Details

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Demonstrates the translation of a complex SQL query involving multiple nested sub-queries to retrieve project details for a specific employee. The query is decomposed into three sequential blocks.

```SQL
SELECT PID, STATUS FROM PROJECT 
WHERE PID = ( SELECT FROM WORKS  WHERE EMPID = ( SELECT EMPID FROM EMPLOYEE 
            WHERE NAME = 'ARUN KUMAR'));
```

```SQL
SELECT EMPID FROM EMPLOYEE WHERE NAME = 'ARUN KUMAR'; 
SELECT PID FROM WORKS WHERE EMPID = ArunEmpID; 
SELECT PID, STATUS FROM PROJECT WHERE PID = ArunPID;
```

--------------------------------

### Nested-loop Join Pseudocode

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Illustrates the conventional nested-loop approach for joining two tables, P and Q. It iterates through each tuple of P and compares it with each tuple of Q based on a joining attribute 'a'.

```pseudocode
For each tuple_p in P 
For each tuple_q in Q
If tuple_p.a = tuple_q.a Then 
   Concatenate tuple_p and tuple_q and append to Result 
End If 
Next tuple_q 
Next tuple-p 

```

--------------------------------

### Granting Database Roles and Privileges (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL snippet demonstrates how to create a role named 'ACCOUNTANT' and assign specific privileges (SELECT, INSERT, UPDATE, DELETE) on different tables (EMP_SAL, TENDER, EXPENSE) to this role. It then assigns the 'ACCOUNTANT' role to a user named 'ABC'. This is a common practice in DDBMS for managing user access efficiently.

```sql
CREATE ROLE ACCOUNTANT;
GRANT SELECT, INSERT, UPDATE ON EMP_SAL TO ACCOUNTANT;
GRANT INSERT, UPDATE, DELETE ON TENDER TO ACCOUNTANT;
GRANT INSERT, SELECT ON EXPENSE TO ACCOUNTANT;
COMMIT;
GRANT ACCOUNTANT TO ABC;
COMMIT;
```

--------------------------------

### SQL CREATE TABLE Statement

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Defines the structure of a new table in a relational database, specifying column names, data types, and constraints like primary keys. This is the first step in creating a database relation.

```sql
CREATE TABLE STUDENT (
   ROLL INTEGER PRIMARY KEY,
   NAME VARCHAR2(25),
   YEAR INTEGER,
   STREAM VARCHAR2(10)
);
```

--------------------------------

### Synchronous Replication: Multi-Site Update (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL snippet illustrates how a transaction is expanded to update data across multiple replicas in different sites during synchronous replication. It involves spawning child transactions for each site.

```sql
Begin T1: 
   Begin T1A : 
      Update PROJECT Set PLocation = 'Mumbai' 
      Where PLocation = 'Bombay'; 
   End T1A;  
	
   Begin T2A : 
      Update PROJECT Set PLocation = 'Mumbai'
      Where PLocation = 'Bombay'; 
   End T2A; 	

End T1;
```

--------------------------------

### Synchronous Replication: Single Site Update (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL snippet demonstrates a transaction updating a data table in a single site. It's a basic update operation without considering replication.

```sql
Begin T1: 
   Update PROJECT Set PLocation = 'Mumbai' 
   Where PLocation = 'Bombay'; 
End T1;
```

--------------------------------

### Relational Algebra Projection Operation

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Demonstrates the Projection operation in Relational Algebra, which selects a subset of columns from a table. The syntax $\pi_{Column1, Column2}{(TableName)}$ is used to specify the desired columns and the table.

```relational-algebra
$\pi_{Name,Course}{(STUDENT)}$
```

--------------------------------

### SQL to Relational Algebra: Average Salary Sub-query

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Translates an SQL query with a nested sub-query to find employees earning less than the average salary into relational algebra. It breaks down the query into inner and outer blocks for processing.

```SQL
SELECT * FROM EMPLOYEE 
WHERE SALARY < ( SELECT AVERAGE(SALARY) FROM EMPLOYEE ) ;
```

```SQL
SELECT AVERAGE(SALARY)FROM EMPLOYEE ;
```

```SQL
SELECT * FROM EMPLOYEE WHERE SALARY < AvgSal;
```

--------------------------------

### Relational Algebra Selection Operation

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Illustrates the Selection operation in Relational Algebra, which filters rows based on a specified condition. The syntax $\sigma_{Condition}{(TableName)}$ is used to define the filtering criteria and the target table.

```relational-algebra
$\sigma_{Course} = {\small "BCA"}^{(STUDENT)}$
```

--------------------------------

### SQL SELECT Statement for Data Retrieval

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Retrieves specific data from one or more tables based on specified criteria. This query selects names of students from the 'STUDENT' table where the 'STREAM' is 'COMPUTER SCIENCE'.

```sql
SELECT NAME FROM STUDENT
WHERE STREAM = 'COMPUTER SCIENCE';
```

--------------------------------

### SQL INSERT INTO Statement

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Adds a new row (tuple) of data into an existing table. It requires specifying the table name and the values for each column, ensuring data conforms to the defined structure.

```sql
INSERT INTO STUDENT ( ROLL, NAME, YEAR, STREAM)
VALUES ( 1, 'ANKIT JHA', 1, 'COMPUTER SCIENCE');
```

--------------------------------

### Defining Entity Integrity Constraint (SQL)

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

This SQL statement defines a 'HOSTEL' table and designates 'H_NO' as the primary key, enforcing entity integrity. A primary key ensures that each tuple (row) in the table is uniquely identifiable and that the primary key field cannot contain NULL values. This is crucial for maintaining data consistency and enabling efficient data retrieval.

```sql
CREATE TABLE HOSTEL (
   H_NO VARCHAR2(5) PRIMARY KEY,
   H_NAME VARCHAR2(15),
   CAPACITY INTEGER
);
```

--------------------------------

### SQL DROP TABLE Statement

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Removes an entire table, including its structure and all data, from the database. This is a permanent operation.

```sql
DROP TABLE STUDENT;
```

--------------------------------

### SQL UPDATE Statement for Data Modification

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Modifies existing data within a table. This command changes the 'STREAM' from 'ELECTRONICS' to 'ELECTRONICS AND COMMUNICATIONS' for rows that match the condition.

```sql
UPDATE STUDENT
SET STREAM = 'ELECTRONICS AND COMMUNICATIONS'
WHERE STREAM = 'ELECTRONICS';
```

--------------------------------

### SQL DELETE Statement for Row Removal

Source: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_quick_guide.htm

Removes rows from a table that meet specified conditions. This command deletes all records of students who are in their 4th year.

```sql
DELETE FROM STUDENT
WHERE YEAR = 4;
```
Tokens:
Raw