Home

Retrieve Database After Entering All Values


From NV

Where MaNV=$MaNV if #FOUND then

Update NV Set MAP = 12

Where MANV= „NV5‟;

Maybe you are interested!

else write (terminal, “Not found”)

Example 2.25: Consider the global employee relationship in example 2.23 with an application that updates the department code of an employee whose employee code and department code are entered from the terminal.

Retrieve Database After Entering All Values

Read(terminal, $MANV,$MAP) Select *

From NV

Where MANV=$MANV if #FOUND then

Update NV

Set MAP = $MAP

Where MANV=$MANV;

else write (terminal, “Not found”)

2) Throughout the position

The application programmer must deal explicitly with fragments.

Program structure

- Step 1: Get a parameter from the Pascal-like program

Read (terminal, $iv1,…, $ivn)

- Step 2: Access database

Select fn1 Into $ov1,…., fnn Into $ovn

From R i

Where

In there:

+ fn1,….,fnn are the names of the fields

+ R i is some i-th piece

- Step 3: Return the result as a parameter

If #FOUND then

Insert into R k ( fn1,…., fnn ) Values ​​( $ov1,….,$ovn ) Delete

From R i


Where

else write (terminal, “Not found”)

Example 2.26: Consider the global relation NV in example 2.23

Select MANV, HOTEN, SALARY, RENT

Into $MANV, $HOTEN, $LUONG, $THUE

From NV 1

Where MANV =‟NV5‟;

if #FOUND then begin

Select MAQL into $MAQL From NV 2

Where MANV =‟NV5‟;

Insert into NV3(MANV, HOTEN, MAP) Values ​​(„NV5‟, $HOTEN,12);

Insert into NV 4 (MANV, LUONG, THUE, MAQL) Values ​​('NV5', $SAL, $THUE, $MAQL);

Delete from NV 1 Where MANV ='NV5';

Delete from NV 2 Where MANV ='NV5';

End;

The first two select commands collect the required NV tuple from its containing fragments, NV1 and NV2 . The two insert commands rearrange this tuple in a different way and insert it into two other fragments, NV3 and NV4 . The two delete commands delete the old copy of this tuple from NV1 and NV2 . Note that the last four commands can be executed in parallel or in any order.

Example 2.27: Consider example 2.23 with an application to update the department code of an employee whose employee code and department code are entered from the terminal.

In this case, the application would be more complex, having to find which fragment contains the employee's tuple, read the employee's old MAP, determine the change to the MAP to change the new fragment to contain the tuple, and finally perform the necessary inserts and deletes. The necessary inserts and deletes are different for the first and last fragments containing the tuple. Therefore, the application would have to contain code to handle all possible solutions. This example shows how fragment transparency is useful to the application programmer in the case of application updates, but it also implicitly shows how complex it is for a DDBMS to provide this feature.


Therefore, the application must perform the following steps:

Step 1: Enter employee code and room code from the terminal

Step 2: Find the piece containing the employee whose employee code is the employee code entered. Step 3: Read the employee's old room code, determine the change in the room code to

change the new piece containing this set

Step 4: Execute the necessary add and remove commands

3) Transparent local mapping

The application deals explicitly with the position of the pieces and must update the replicas as well.

Example 2.28: Consider the global relation NV in Example 2.23. Suppose the fragments of the relation are placed in the following way:

NV 1 : place 1 and 2

NV 2 : places 3 and 4

NV 3 : places 5 and 6

NV 4 : places 7 and 8

Select MANV, HOTEN, SALARY, RENT

Into $MANV, $HOTEN, $LUONG, $THUE

From NV 1 at site 1 Where MANV ='NV5';

if #FOUND then begin

Select MAQL into $MAQL From NV 2 at site 3

Where MANV =‟NV5‟;

Insert into NV3(MANV, HOTEN, MAP) at site 5 Values ​​('NV5', $HOTEN,12);

Insert into NV3(MANV, HOTEN, MAP) at site 6 Values ​​('NV5', $HOTEN,12);

Insert into NV 4 (MANV, LUONG, THUE, MAQL) at site 7 Values ​​('NV5', $SAL, $THUE, $MAQL);

Insert into NV 4 (MANV, LUONG, THUE, MAQL) at site 8 Values ​​('NV5', $SAL, $THUE, $MAQL);

Delete from NV 1 at site 1 Where MANV ='NV5';

Delete from NV 1 at site 2 Where MANV ='NV5';

Delete from NV 2 at site 3


Where MANV =‟NV5‟;

Delete from NV 2 at site 4 Where MANV ='NV5';

End;

The application is implemented as follows: The first two select commands contain an at clause to select where to access NV 1 and NV 2 ; the two insert and two delete commands are used to add new tuples to all replicas of the fragments containing these tuples and to remove old tuples from all replicas containing these tuples.

It is important to note that when updating data, we must ensure that the new data satisfies all integrity constraints in the database, including centralized and distributed databases. In a distributed database, updating data will result in moving data from one fragment to another only when the updated attribute is in the qualifier of the fragment whose data is updated and the new value must change the value of this predicate from true to false; the new fragments containing the moved data are those whose new value makes their qualifier true.

Also, in the above example, if we have the MAP MAQL functional dependency, then the above update program has not changed the MAQL value of employee with code „NV5‟ to the new value.

2.4. Basic operations to access distributed databases

In the examples of the previous sections, we always assumed that each basic database access operation (query) returned a single value attached to a single variable.

However, we want to use more powerful basic operations that return relations as results; in this case Pascal-like procedural programming and SQL-based operations replace parameters as single variables with files (parameter files).

To avoid the problem of describing types, we will use the following approach in our examples: parameters whose names end with the suffix _REL are considered to be of file type in Pascal-like programs and of relational type in SQL commands, assuming their structure has been declared.

4 ways to access a database with fragmentation transparency:

- Retrieve database for each value

- Retrieve database after entering all values

- Retrieve database before entering values

- Access database using common subexpression

Application program


Written in a PASCAL-like language with embedded SQL commands.

- Standard procedures:

Read ( terminal, ,…, ): Write input procedure (terminal, ,…, ) : export procedures In which:

+ terminal: is the file name (if input and output are done at the terminal, the terminal file name will be used)

+ iv1,…,ivn: is a list of input parameters

+ ov1,…,ovn: is a list of output parameters

- SQL command: defines a basic operation to access the database

+ Step 1: Get a parameter from the Pascal-like program

+ Step 2: Access database

+ Step 3: Return the result as a file

- How to declare input parameters: $variable_name

- How to declare output parameters: $file_name_ REL (List of news items)

Example 2.29: Consider the project management system of a software development company in Example 1.5. Suppose there is an application that shows the project code deployed by the employee whose employee code is entered from the terminal.

Example 2.30: Consider the business management system of a company in example 2.13. Suppose there are applications that provide the supplier name and address to which they supply an item whose item code is entered from the terminal.

2.4.1. Retrieve database with each value

The advantage of the program is that it gives quick results after each time you enter the data you want to search for.

Steps to perform retrieval: Repeat

Step 1: Get parameters from Pascal-like program

Read (terminal, $iv1,…,$ivn);

Step 2: Delete all sets from the file $REL_file_name Delete From $ REL_file_name ;

Step 3: Insert the found results into the file $file_name_ REL Insert into $file_name_ REL ( $ov1,…,$ovn) Values ​​( Select fn1, …, fnn

From R1, R2,…,Rn

Where ); In there:


+ fn1,…., fnn are the names of the fields

+ R1, R2,…, Rn are global relations

Step 4: For each value entered, the program scans the parameter file.

$REL_file_name and output the result to the terminal

Repeat

Read ($REL_file_name , $ ov1,…,$ovn) Write(terminal, ( $ov1,…,$ovn)

Until END – OF- $filename_ REL Until END –OF –terminal – input;

1) With example 2.29, the program is written as follows:

Repeat

Read (terminal, $MANV); Delete from $DA_REL;

Insert into $DA_REL ($MADA) Values(Select MADA

From HS

Where MANV=$MANV);

Repeat

Read ($DA_REL, $MADA) write(terminal, $MADA)

until END – OF- $DA_REL until END –OF –terminal – input;

For each employee with the code $MANV entered by the user, the projects that this employee is involved in are retrieved and placed in the parameter file $DA_REL. The inner loop scans this parameter file and displays the projects that this employee is involved in.

2) With example 2.30, the program is written as follows:

Repeat

Read (terminal, $MAMH); Delete from $NCC_REL;

Insert into $NCC_REL ($TENNCC, $DC) Values(Select TENNCC, DC

From MH, KD

Where MH.MANCC= KD.MANCC and MAMH = $MAMH);

Repeat

Read ($NCC_REL, $TENNCC, $DC)

write(terminal, $TENNCC, $DC) until END – OF- $NCC_REL


until END –OF –terminal – input;

For each supplier with the code $MAMH entered by the user, the suppliers that supply the item including the supplier name and address information will be retrieved and put into the parameter file $NCC_REL. The inner loop scans this parameter file and displays the suppliers that supply the selected item.

2.4.2. Retrieve database after entering all values

The application first collects all the entered information and then retrieves the database. Therefore, retrieving the data only once with a larger parameter file is more efficient than retrieving the database for each value. In this way, the number of retrieval operations in the distributed database is reduced, the same amount of data results are transmitted, but fewer control messages are used.

Programmers can control database access even when they work at the global level.

The choice between the two solutions above depends on the tuple results returned when joining the two relations and on the number of tuples used by the application. Obviously, a trade-off between response time and overall system overhead must be considered: if minimization of the amount of work is desired, the second solution is better; if fast response after each vendor code entry is desired, the first solution is better.

An application can access large collections if it is batched and less interactive; this trade-off between more batched and more online solutions is well known in traditional databases.

Steps to perform retrieval:

Step 1: Put all the information entered from the terminal into the file

$ REL_file_name

Repeat

Read (terminal, $iv1,…,$ivn);

Insert into $file_name1_ REL( $iv1,…,$ivn)

Values( $iv1,…,$ivn)

until END –OF –terminal – input;

Step 2: Find the information that satisfies the condition in the file $file_name1_ REL and put it in the file $file_name2_ REL

Insert into $file_name2_REL($ov1,…,$ovn)

Values(

Select $iv1,…,$ivn, fn1,…,fn2

From R, $file_name1_ REL

Where R.fni= $file_name1_ REL .$ivni);


Where the file $file_name1_REL is used as a relation

Step 3: For each value entered, the program scans the parameter file.

$file_name2_REL and output the result to the terminal

Repeat

Read ($file_name2_REL, $ov1,…,$ovn);

Write (terminal, ($ov1,…,$ovn)

Until END – OF- $PNUM_REL;

1) With example 2.29, the program is written as follows:

Repeat

Read (terminal, $MANV); Insert into $NV_REL($MANV) Values($MANV)

Until END –OF –terminal – input; Insert into $DA_REL($MADA, $MANV) Values(

Select MADA, $MANV From HS, $NV_REL

Where HS.MANV= $NV_REL.$MANV);

Repeat

Read ($DA_REL ($MADA, $MANV);

Write(terminal, ($MADA, $MANV) Until END – OF- $DA_REL;

First this application collects all the entered employee codes and puts them in the file.

$NV_REL and then retrieves the database and puts it in the parameter file $DA_REL. Next, the program scans the $DA_REL file and displays the projects that this employee participates in.

2) With example 2.30, the program is written as follows:

Repeat

Read (terminal, $MAMH); Insert into $MH_REL($MAMH) Values($MAMH)

Until END –OF –terminal – input;

Insert into $NCC_REL ($TENNCC, $DC) Values(Select TENNCC, DC

From MH, KD, $MH_REL

Where MH.MANCC= KD.MANCC and KD.MAMH =$MH_REL. $MAMH);

Repeat

Read ($NCC_REL, $TENNCC, $DC)

Comment


Agree Privacy Policy *