Implementing a distributed database, Building a table structure - Databases

Implementing a distributed database

When using client-server mode, such tasks arise for the server and clients.

For a server

1. Building the structure of a table system on the server, possibly with a system of queries (types) for populating tables, with the establishment of links between tables.

2. Filling tables with data.

3. Provide access through views, requests, and stored procedures.

For customers

1. Develop the necessary applications.

2. Construct appropriate user interfaces.

In this chapter, we'll look at a local version of the client-server mode. The features of the formation of a remote version with a single- and multilevel structure will be discussed in Ch. 15.

The database itself. The following work is required.

• Building tables.

• Establishing links between tables to implement referential integrity in the RDB.

• Using stored procedures to provide referential integrity.

• Creating a system of generators to automatically fill fields that require automatic numbering.

• Loading tables with data from an existing database Teaching process & quot ;.

Building a table structure

All operations on creating the structure of RDB tables in InterBase are performed using the Windows 1SQL utility (WISQL), supplied with the RDB server (Figure 14.14).

Windows ISQL Window

Fig. 14.14. Windows ISQL Window

Creating tables and other RDB objects is done in SQL.

The QBE language severely restricts the developer and allows him to create queries only on predefined templates that were created by the language's creators.

Here's an example of creating a gruppa table:

/* Table: GRUPPA, Owner: SYSDBA */

CREATE TABLE GRUPPA (NG VARCHAR (6) CHARACTER SET WIN 1251 NOT NULL,

NAZVANIE VARCHAR {6} CHARACTER SET WIN 1251,

OTD VARCHAR (20) CHARACTER SET WIN1251,

STAROSTA VARCHAR (20) CHARACTER SET WIN1251,

STAR_POLN VARCHAR (50) CHARACTER SET WIN 1251,

KURATOR VARCHAR (20) CHARACTER SET WIN 1251,

KURS VARCHAR (50) CHARACTER SET WIN 1251,

SH_SPEC INTEGER NOT NULL,

GOD_NABOR INTEGER,

STUDENTOV INTEGER,

DEVUSHEK INTEGER,

UNOSHEY INTEGER,

BESPLATN INTEGER,

CHAST INTEGER,

POLNPLATN INTEGER,

CONSTRAINT GRUPPRIMKEY1 PRIMARY KEY (NG));

The last line indicates that the NG field (group number) is the primary key. The instruction type SH_SPF.C INTEGER NOT NULL indicates that it is necessary to create a column sh_spec of type Integer with a mandatory non-zero value.

The total RDB has 18 tables:

• ArhivStud - an archive table containing information about students' progress, for the past semesters;

• Gruppa - list of all groups;

• IPP - a table that stores information about the students' passing of industrial practice;

• Izuchenie - a table with information about the studied subjects in groups;

• Kafedra - the list of chairs;

• Laba - list;

• Obshegit - a list of students living in hostels;

• Oplata - information about the payment by students of their studies;

• Predmet - the list of subjects taught;

• Predpr - the list of enterprises where students take practical training;

• Prepod - list of teachers;

• Prikaz - orders;

• Specialnost - a list of specialties;

• Student - list of students studying and information about them;

• TempUsp is an intermediate table needed to generate complex reports that can not be organized only using queries;

• Uspevaem - information about the surrender of the session by students;

• Vid_prikaz - types of orders.

After creating the tables, the programmer can extract the SQL commands from the RDB that he entered during the creation of the tables. Such information obtained from the RDB is called metadata. A text file containing metadata can be re-executed if necessary, which will allow to restore its structure in the event of a complete loss of the RDB without repetitive, tedious stuffing of all SQL commands. To obtain such information, you need to select the Metadata for DataBase from the Extract/SQL menu in the Windows ISQL utility, and then specify the name of the file in which you want to save the metadata. Listing of SQL programs developed by the RDB is given in Appendix 2.

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)