Creating a table structure, Filling tables - Databases

Creating a Table Structure

The table structure is created through a program written in the nested SQL language. It can be entered into the computer in two ways.

1. Initially, write the entire program (script file) in any of the text editors. When you save the file, you must specify the sql extension. Then the program can be entered using the WISQL utility (Run on ISQL Script).

This method is convenient when there is a repetitive sequence of SQL statements and when using well-functioning files. When they are formed, great care is needed, because in the presence of errors in the program, the first method actually reduces to the second.

2. Here you can use the WISQL utility or the SQL Exiorer utility.

When calling WISQL, it's useful to fix the Russification & quot ;. To do this, in the Session/Advanced Setting in the Character Set On window, you must specify WINI251.

The SQL language is used to create the table structure and fill them in.

Install the network connection of the database with WISQL via the File/Connect to Database element of its main menu. In the window that opens, we set the path to the database, username and password.

In the appeared splash screen in the top window of SQL Statement it is necessary to type the SQL-statement. So, to create the table Pravila we type

CREATE TABLE Pravila (

Nomprav integer Not Null,

FIO varchar (15),

Stephen varchar (5),

Otkrytie varchar (5),

Znak_ball varchar (5),

Sr_ball numeric! 15.2),

Znak_Stag varchar (5),

Stag integer,

Dolgnost varchar (10),

Objasnenia varchar (120)

);

When the set is complete, click the Run button.

Set Goes in the ISQL Output window and in the absence of errors is performed. If there are any errors, a message is displayed, indicating the line and the character in the set where the error occurred. After fixing it, you should press the Run button again.

The described procedure is tedious, and therefore in WISQL the buttons Previous and Next can return the required operator from the bottom window to the upper one. After its correction, it is entered by pressing the Run button.

You can confirm the end of the program by selecting File/Commit Work from the main menu, and canceling the execution - Filc/Rollback Work.

After the completion of work with WISQL, detachment from the database is performed by selecting File/Disconnect from Database. Selecting the Exit button means quitting WISQL.

It should be noted that embedded SQL has weak debugging tools and an inconvenient interface. However, the high speed of programs when working in a network mode makes us reconcile with this disadvantage.

To control the operation of WISQL, it is possible to use the menu.

When you click the View/Metadata Information menu item, the View Information screen saver is displayed. In it, you can select the type and name of the object, as a result of which the program of the object in the embedded SQL will be displayed in the ISQL Output window.

If the object name is not specified, a list of objects of this type is displayed.

Selecting the Extract/SQL menu item Metadata for Table and the table name will output data about it, which can be saved in a txt file.

Clicking on the Extract/SQL Metadata for Database will cause the metadata about the database to be issued as a program in the embedded SQL language. It can be copied to the clipboard and then saved, for example, in the Word editor.

An example of such a SQL program is given in Appendix 3.

Filling Tables

Filling can be done in WISQL or in SQL Explorer.

In the first case, a line-by-line data insertion is performed using the INSERT statement. The correction of the data can be carried out by the UPDATE and DELETE statements, the results obtained are controlled by the SELECT statement. However, this method is tedious.

It is more convenient to use the SQL Explorer utility, which also allows you to create a table structure.

After opening SQL Explorer, a two-screen saver appears on the screen. In the left window, select the Databases tab and the required alias. Clicking + to the left of it reveals a list of database object types. Clicking + the selected object type opens a list of names for this type of objects. The choice of the name causes the appearance in the right window of three (for tables - four) bookmarks.

Definition tab defines the metadata of a specific object, Text tab allows you to create SQL-operator of a particular object. Selecting the Data tab (for tables) allows for viewing of, filling and updating data in the selected table.

To enter new data, click + in the navigator and sequentially enter records. When you move to a subsequent record, the previous one is automatically saved. To save the last record, you must press the Post or Refresh button of the navigator.

Creating the program code of any object is performed when you select the Enter SQL tab. At the top of the right window, a SQL statement is typed and a button with a lightning image is pressed. If the operator is typed correctly, the result of the operator is displayed at the bottom of the right window (otherwise an error message is displayed). After successful execution of the statement, you should update the utility with information about the database using the View/Refresh menu item.

The operation of the database and the server in the remote version is controlled by the Interbase Server Manager utility.

B. USER INTERFACE. In client-server mode, the server side of the user interface is generally poorly developed. It displays the parameters common to all clients. There are usually few such parameters.

The main emphasis in the development of the user interface is transferred to the client interface.

When building the interface, we take into account the experience gained in the work [19], where the user interface is built on

based on one form of Delphi. In this regard, the program code has become more complicated due to the organization of access to control elements, the introduction of additional buttons during the transition from groups of menu items to other menu items, the constant modification of the SQL operator in Query components.

When using multiple forms, the Query 'components can be assigned to specific displayable table variants, the transfer of SQL statements to the SQL properties of the Query component. This requires additional code to interact with the forms, but it is much simpler than the eliminated program code.

In general, you can use nine Delphi forms for the client and one for the server.

When you go to stored procedures, the application algorithm of the client part is greatly simplified. For tables Frames & quot ;, Working & quot ;, Applicants & quot ;, Accepted & quot ;, Accepted SQL statements are almost the same.

In this regard, it is better to use four Delphi forms for the client and one for the server. Then maybe this distribution tables.

Server

Form5, where the components Edit1 and Edit2 are located for fixing the current cycle and the total number of cycles.

Customer

Forml (main) - menu and table Staff schedule .

Form2 - table Frames and its components.

Form3 - table Explanations & quot ;.

Form4 - the Rules table.

Forms Delphi Form2 - Form4 - modal. When they are closed, we can switch the available elements of the main menu.

In the Forml main menu, select the Home , Show, Edit

For elements of the main menu, we first introduce the following division.

Home

Open

Sign out

Show

Staffing

Frames

Applicants

Working

________________

Accepted

________________

Accepted

________________

Dismissed

Edit

Start

________________

Running rules

________________

Quantitative result

Explanations Edit rules

Changing the result

________________

Accept all

________________

Continue

Note. When using multiple forms, you need to establish the necessary connections between them. To do this, in the unii module, after the implementation header, the line is written

uses unit1, .... uniti, ... unitm;

Without establishing relationships, forms will not interact.

Q. ALGORITHM OF APPENDIX. To create an algorithm, we use templates [19]. A template means a completed in the target relation and a repeating program part of a procedure or function. A template can be constructed from elementary and combined components.

VI1. Elementary components. The simplest (elementary) components are SQL statements: SELECT, update (UPDATE, DELETE, INSERT), destroy (DROP) and build (CREATE) tables (TABLE).

It should be noted the possibility of two ways to implement them.

1. Using the TQuery SQL Property:

select * from kadry

This method is used in a multi-window (multi-window) interface when the Query object of the form is " for a particular table and the type of request does not change during the entire session.

2. Creation of the formed request placed in the program code. Then the previous example will look like

Query1.Close;

Queryl.SQL.Clear;

Query1.SQL.Add ('select * from kadry');

Query1.Open;

This method is used for a single-window version of the interface or when the query type changes frequently in the Queryl object.

From the point of view of writing SQL-query, the difference is not large, therefore we illustrate the further procedure of template formation using SQL-properties.

VI1.1. The select statement is used in numerous variants, defined by the following basic characteristics:

a) using parameters (two classes - normal and with parameters);

b) the use of aggregation functions (normal, aggregated);

c) the ability to edit the table data after calling the table on the screen (normal, RequestLive);

d) using one or more source tables.

The following options are of interest for the projected BR.

1. A simple selection (we will call the option selecti):

select * from kadry

2. Selection with options (select2), in which we highlight the cases:

• with one table (select21):

select dolgnos, count (dolgnos) as fakty from kadry

where (status = 'pa6o-L)

And (vremja between 1 And: vremja)

group by dolgnos

ParamByName (Vremja '). Value: = StrTolnt (Edit1.Text);

• with two tables (select22):

select K.vremja, K.Familia,

K.Uch_stepen, K.Otkrytie, K.Sr_ball,

K.Stag, K.dolgnos,

P.Objasnenia from kadry K, pravila P

where vremja =; vremja And

P.nomprav = K.nomprav

Vremja ') .Value: = StrTolnt (Edit1.Text)

3. Sampling with calculations and aggregation functions (select3):

select dolgnos, count (dolgnos) as fakty from kadry

where status = 'works'

group by dolgnos

4. Sampling with parameters and the possibility of changing the called data in the dialog:

RequestLive: = T rue;

select * from kadry

where (status = 'npm-iMM')

and {vremja =: vremja)

ParamByName (Vremja '). Value: = StrTolnt (Edit1 Text);

VI1.2. The update operator with rattling varieties:

1. With the parameters update1, which includes two subclasses:

• the name of the field being changed is not included in the conditions (update11):

Update kadry

set nomprav =: nomprav,

dolgnos =: dolgnos

where (uch_stepen =: uch_stepen)

And (otkrytie =: otkrytie)

And (Sr_ball & lt;: Sr_ball)

And jstag & gt; =: Stag)

And (status = 'npeTeH')

And (vremja =: vremja)

ParamByName ('nomprav'). Value: = Query2.FieldByName ('nomprav'). Value;

• the name of the field to be changed is included in the condition (update 12), while the variable uses the old _ prefix

Update kadry

set status =: status

where (status =: old_status)

And (vremja =: vremja)

ParamByName ('status'). Value: = 'accepted';

2. With the parameters, calculations and use of the cycle (update2):

Update stat_rasp

set fakty =: fakty,

vacanc = plany-fakty

where (dolgnos =: dolgnos)

And (vremja =: vremja)

ParamByName ('Vremja'). Value: = StrTolnt (Edit1.Text);

3. With Datasource parameters, loop and reference to parameters (update3):

update stat_rasp

set plany =: plany, fakty =: fakty,

vacanc =: vacanc, prinimaem =: prinimaem,

nedobor =: nedobor

where vremja =: vremja and dolgnos-: dolgnos

Datasource: = DataSource2;

VI1.3. Insert statement 1. Normal (insert 1):

insert into kadry

values ​​(TleTpOB ',' yes', ...);

ВП1.4. The delete operator:

1. Normal (delete 1):

delete from pravila

2. With parameters:

delete from kadry

where vremja =: vremja and dolgnos = 'refuse'

ParamByName ('Vremja'). Value: = StrTolnt (Editl.Text);

ΒΠ1.5. Drop drop operator of the table (drop1)

drop table pravila

VI1.6. Create table creation (crcatel) statement described earlier.

VP2. Complex components using at least two elementary components.

VI2.1. The inserti component (inserti + selecti):

insert into kadry

select * from kadryjsh

VP2.2. UpdateId component (update 1 + select3):

update stat_rasp

set plany =: plany, fakty =: fakty,

vacanc =: vacanc, prinimaem =: prinimaem,

where (vremja =: vremja) and

dolgnos IN

(select dolgnos, count (dolgnos) as fakty

from kadry

where (status = 'works') and

(vremja between 1 and: vremja)

group by dolgnos)

ParamByName ('Vremja'). Value: = StrTolnt (Edit1.Text);

VBR. Based on these components, the templates shown in Table 1 are formed. 1.1 and tied to user actions - pressing menu items (see Figure 15.2.).

When constructing an application algorithm, the templates are associated with the corresponding menu items and tested. Single-type templates can simply be copied.

We note one interesting circumstance. Formation of program templates (Table 15.4) is actually implemented in all DBMS, but in different versions.

In the Access DBMS, the components are queries, in which intermediate results are stored. In the Paradox DBMS (within Delphi), the components are associated with the Query components that store the intermediate data. In the InterBase DBMS, the containers for templates are stored procedures, in which the necessary components are sequentially located. Intermediate results are stored in the stored procedure's INTO program as parameters.

The scheme of interaction between the server and the client in terms of the application algorithm is shown in Fig. 15.5.

Table 15.4

Software templates for the application algorithm

User Response

Template

Start

A-copy

Delete 1

Inserti

or

B-copy

Selecti

Update3

or

B-Copy

Update}

or

G-conning

Drop1

Create1

Insert1

Staff Schedule

Select3

Update2

Select2l

Frames

Select2l

Working

Select2l

Applicants

Select2l

Running rules

Select1

Update11

Update11

Accepted

Select2l

Quantitative result

Select3

Update2

Select2l

Explanation

Select22

Accept all

Delete2

Updatel2

Accepted

Select2l

Continue

Update 12

B-copy

Selecti

Selecti

Dismissed

Select4

Select1

Changing the result

Select4

Edit Rules

Select2l Select4 Update12

Generators and triggers are not used in this implementation.

The fragment-sample program of the algorithm of the application is given in Appendix 3. It is allocated the server part (in the nested SQL language) and the client part (the interface language SQL and Object Pascal with 15 forms).

The client part is quite simple. It is largely a user interface program, since almost all select-queries are "hidden" in the SQL properties of the Query component.

Server-client interaction

Fig. 15.5. Server and client interaction

The program code for the server (the code of stored procedures) corresponds to the program templates considered in Table. 15.4.

The client code also consists primarily of the user interface code and the application algorithm code. The latter is represented by operators of type select21, in which the names of tables are replaced by the names of stored procedures.

We emphasize once again that both the client and the server for the local version are implemented on one main computer.

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)