Establishing relationships between tables - Databases

Establishing links between tables

To create links in WISQL, use the instructions of the form PRIMARY KEY (NG) - create the main key in the field NG; FOREIGN KEY (NG) REFERENCES GRUPPA (NG) - create a foreign key NG with connection to the main key NG of the GRUPPA table.

The structure of tables and connections of the developed RDB is shown in Fig. 14.15. Links and tables in the RDB are saved to the extent possible to transfer and convert information from the previously described database.

Creating stored procedures. The stored procedure in the InterBase DBMS can be used both in the application algorithm and, along with the triggers, in the actual databases for cascading deletion and updating.

The client part needs to pass parameters to the stored procedure. After the work is completed, the stored procedure passes the results to the client application. This approach also reduces the performance requirements of the user's computer running the RDB.

Here is an example of a stored procedure that removes key entries from the Kafedra table:

ALTER PROCEDURE KAFEDRA_DEL (DKKAF INTEGER)

AS

declare variable dsh_spec integer;

declare variable dng varchar (6);

declare variable dnzk varchar (10);

declare variable dkprikaz integer;

declare variable dkprep integer;

BEGIN

for select sh_spec from specialnost

where kkaf =: dkkaf

into: dsh_spec

do

begin

The schema of links of the database tables

Fig. 14.15. The link diagram of the database tables "The learning process"

for select ng from gruppa

where gruppa.sh_spec =: dsh_spec

into: dng

do

begin

for select nzk from student

where student.ng =: dng

into: dnzk

do

begin

delete from ipp where ipp.nzk =: dnzk;

delete from uspevaem where uspevaem.nzk =: dnzk;

delete from atestacia where atestacia.nzk =: dnzk;

delete from oplata where oplata.nzk =: dnzk;

delete from obshegit where obshegit.nzk =: dnzk;

for select kprikaz from prikaz

where prikaz.nzk =: dnzk

into: dkprikaz

do delete from vid_prikaz where vidjDrikaz.kprik-: dkprikaz;

delete from prikaz where prikaz.nzk =: dnzk;

end

delete from student where student.ng =: dng;

delete from izuchenie where izuchenie.ng =: dng;

delete from gruppa where gruppa.ng =: dng;

end

delete from specialnost where sh_spec =: dsh_spec;

end

for select kprep from prepod where kkaf =: dkkaf

into: dkprep

do begin

delete from izuchenie where kprep =: dkprep;

delete from uspevaem where kprep =: dkprep;

delete from atestacia where kprep =: dkprep;

end

delete from prepod where kkaf =: dkkaf;

delete from laba where laba.kkaf =: dkkaf;

delete from kafedra where kkaf =: dkkaf;

suspend;

END

This procedure KAFEDRADEL as a parameter gets the number of the department DKKAF, then first deletes the records in the subordinate tables and only then the key entry in the table KAFEDRA.

Many subordinate tables themselves have subordinate tables. Thus, before deleting data in them, it is necessary to delete data in their subordinate tables and only then - in the parent tables. Exit from the existing situation allows the nesting of SQL procedures supported in InterBase. Due to this, it is possible to implement a rather complicated cascade deletion procedure, which contains up to 16 attachments with the size of a similar procedure not exceeding 48 kilobytes.

In the database "Training process" there are several stored procedures that perform the following work:

• GruppaDel - a cascading delete in the Gruppa table and associated tables;

• KafedraDel - a cascading delete in the Kafedra table and associated tables;

• Predmet_Del - cascading delete in Predmet table and related tables;

• Predpr Del - cascade deletion in the Predpr table and associated tables.

• PrepodDel - a cascading delete in the Prepod table and associated tables;

• Prikaz_Del - cascading delete in the Prikaz table and related tables;

• SpecialnostDel - cascading delete in the Specialnost table and related tables;

• Student Del - cascading delete in the Student table and related tables.

Filling (loading tables with data from an existing database "Training process"), as in a centralized database, it is possible either directly in the table or in forms. The composition of the forms is described in the user interface.

In contrast to a centralized database in a given database, a part of the data is filled automatically by generators.

Creating a system of generators. In an explicit form, InterBase does not support fields like "auto-numbering". Instead, there is a mechanism in InterBase called "generators". The generator is a variable, the value of which can be obtained and increased by some value using the built-in function GEN_ID.

Typically, generators are used in triggers, and the trigger text can be:

CREATE TRIGGER TI_CLIENTS FOR CLIENTS

ACTIVE BEFORE

INSERT POSITION 0

AS

BEGIN IF (new.CLIENTJD IS NULL) THEN

CLIENTJD = GENJD (& lt; hmh of the generator & gt ;, 1);

END,

where 0 is the initial value; 1 - step increment in the current value of the generator.

The generator mechanism ensures that even with a concurrent (parallel) function call GEN_ID, each user will be given a unique value. The last value of the generator is always stored in the database, so the developer does not need to worry about recovering its maximum value after connecting to the database. Generators are variables of type integer (longint).

The program complex (DB) & "The learning process contains three generators designed to generate unique codes:

• Gen_kkaf - Chairs;

• Gen_kpreci - the subject;

• Gcn_Kprp is an enterprise.

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)