Purpose
Purpose: to get an idea about the objective, structure, and sequence of DDL and DML commands of the SQL language using the example of creating a database "LIBRARY".
D.1. Script-file in the Interbase DBMS
A script file – is a source file that contains script in any scripting language. This is the most general definition of script file we could find. Let's narrow this definition for our case: a script file is a sequence of DDL and DML commands of the SQL language for creating a physical database model.
In our opinion, the technique of using SQL scripts in Interbase is not different from other DBMS for creating a physical database model. In general, for the Interbase DBMS, it includes three consecutive steps:
- Create a script file using a text editor.
- Execute the file using isql in IBConsole.
- View execution results and commit changes to the database.
The Notepad application will be enough for creating and editing a script file in Windows OS. Use MS Word for this with caution. This application has hidden control characters. They may be the cause of atypical behavior of the script-file handler in the Interbase DBMS.
Experience shows that the script for creating a physical model of a relational database in a script file is most convenient to perform step by step. After each SQL statement or group of statements, you can see the result of their work. If it suits you, copy the SQL statements to a script file. This approach will allow you to get the script for restoring the initial structure of the database. In the future, you can supplement or change it. It remains to add that a high-quality logical database model ensures the success of its physical modeling.
The "LIBRARY" DB creating
The script file . will be used for the database “LIBRARY” creating. It contains 8 parts:
- Creating domains.
- Creating tables.
- Creating data views.
- Creating primary key value generators.
- Defining exceptional situations.
- Creating triggers and stored procedures.
- Creating table indexes.
- Adding information to tables.
A « COMMIT;» operator terminates the script file. It closes a transaction that was automatically opened just before the start of execution. It fixes all the changes that have been installed in the database "LIBRARY". All other statements in script file have detailing comments.
Let's take a closer look at the sequence of actions for creating the "LIBRARY" database in the "Interbase" DBMS.
- Read the information about installation, registration, and licensing information about using "Interbase" DBMS.
- Install "Interbase" DBMS on your device.
- Open IBConsole.
- Choose Menu Bar item Database in the IBConsole interface.
- Select Database Menu’s item Create database….
- Point a place and DB name in the «Create Database» dialog window. For example, C:\DB\LIBRARY.IB.
- Don’t change other parameters and tap the button «OK».
- Enter the password «masterkey» and tap the button «Connect» in the «Database Connect» dialog window.
- Select the node LIBRARY.IB in the Server Tree in the IBConsole.
- Choose the button
![[Button appearance]](en/ap04fig02)
Interactive SQL in the IBConsole Interface. - Copy the script-file from appendix D.3 to the Input Aria of the Interactive SQL dialog window.
- Mark the /* CREATING DOMAINS. phrase. It is in the first row of the appendix D.3.
- Using scrolling, find the COMMIT; statement. It is in the last row of the appendix D.3.
- Holding Shift, click the mouse’s left button to the right of the COMMIT; statement.
- Use Ctrl+C to copy the selection to the clipboard.
- Go to Input Aria of the Interactive SQL dialog window and press Ctrl+V.
- Select the item Transactions in the Menu Bar of Interactive SQL dialog window.
- Use the Options item in the Transactions Menu
- Set the Write Access Mode (item 3, fig. 1) in the Transaction Editor dialog window and tap the «ОК button.
- Tap the button
![[Button appearance]](en/ap04fig03)
Execute of Tool Bar in the Interactive SQL dialog window.
Fig. 1. The Transaction Editor dialog window
This script file was checked for errors in Interbase 2020. There are DDL and DML SQL commands in the script file. They will create all objects and add all data into DB LIBRARY.IB. For example, then you can see list of LIBRARY.IB tables and views in the Tables and Views left part of the Interactive SQL dialog window. Find the READERS table on this list. Tap it. You can see the columns list of this table. Compare it with READERS table description in the physical model.
You have a possible for data viewing in the READERS table. Run the simplest SQL from example 6.1. Just copy it into the Input Aria of the Interactive SQL dialog window. Then select button Execute in the Tool Bar. Results will be displayed in the Output Aria.
Have enjoy learning about relational database organization.
D.3. The "LIBRARY" DB script-file.
/*CREATING DOMAINS
Statements create domains defined in the physical database model. */
/* The domain is used to define a unique row number for all relations in the Library database It use for creating of the Code field in all Library DB tables that don't allow NULL value. */
CREATE DOMAIN AllCode AS INTEGER
NOT NULL;
/* The domain is used to define a librarian’s identifier, that get a book from a reader. This is the foreign key of BookGiveOutRecord table. It allows NULL value. When the reader takes the book in the library, we can’t before the librarian, that will take it. */
CREATE DOMAIN InLibrarianCode AS INTEGER;
/* The domain is used to define the sets of all surnames, first names and patronymics of people in the Library database. It use for creating of the FamilyName, Name, Patronymic fields in all Library DB tables. */
CREATE DOMAIN FIO AS CHAR(30)
NOT NULL;
/* The domain is intended to work with all additional information about people and their autobiographies in the “Library” database. These are the fields that named as Note or ShortBiography in all Library DB tables. */
CREATE DOMAIN AllNote AS BLOB;
/* The domain is intended to define the sets of all book titles in the “Library” database. This is the Name field in the Books table. */
CREATE DOMAIN BookName AS CHAR(200)
NOT NULL;
/* The domain is intended to define the sets of all circulations of books in the Library database. This is the Drawing field in the Books table. */
CREATE DOMAIN AllDrawing AS INTEGER
CHECK(VALUE >= 10)
NOT NULL;
/* The domain is intended to define the sets of all UDCs in the “Library” database. This is the UDK field in the Books table. */
CREATE DOMAIN AllUDK AS CHAR(20)
NOT NULL;
/* The domain is intended to define the sets of all ciphers of the books in the “Library” database. This is the Cipher field in the Books table. */
CREATE DOMAIN AllCipher AS CHAR(30)
NOT NULL;
/* The domain is intended to define the sets of all series of passports in the Library database. This is Series field in the PassportData table. */
CREATE DOMAIN AllSeries AS CHAR(2)
NOT NULL;
/* The domain is intended to define the sets of all passport numbers in the Library database. This is the Number field in the PassportData table. */
CREATE DOMAIN AllNumber AS INTEGER
CHECK(VALUE >= 10)
NOT NULL;
/* The domain is intended to define the sets of all birthplaces of the Library database. This is the BirthPlace field in the PasportData table. */
CREATE DOMAIN AllPlace AS CHAR(100)
NOT NULL;
/* The domain is intended to describe the gender of the Library database. This is the Sex field in the PassportData table. */
CREATE DOMAIN AllSex AS CHAR(100)
CHECK(VALUE IN ('F','M'));
/* The domain is intended to define the sets of all places where people, whose was registered in the Library database, got their passports. This is the IssuePlace field in the PassportData table. */
CREATE DOMAIN AllIssuePlace AS CHAR(100)
NOT NULL;
/* The domain is intended to define sets of all type collections of books in the Library database. This is the AllName field in the BookFunds table. */
CREATE DOMAIN AllName AS CHAR(20)
NOT NULL;
/* The domain is intended to define the sets of all phone types in the Library database. This is the AllNameType field in the PhoneTypes table. */
CREATE DOMAIN AllNameType AS CHAR(20)
NOT NULL;
/* The domain is intended to define the sets of all library card numbers in the Library database. This is the ReaderCardNumber field in the Readers table. */
CREATE DOMAIN AllReaderCardNumber AS INTEGER
CHECK(VALUE >= 10)
NOT NULL;
/* The domain is intended to define the sets of all personnel numbers of the Library database. This is the ClockNumber field in the Librarians table. */
CREATE DOMAIN AllClockNumber AS INTEGER
CHECK(VALUE >= 10)
NOT NULL;
/* The domain is intended to define the sets of all book inventory numbers in the Library database. This is the InventoryNumber field in the BookInventoryNumber table. */
CREATE DOMAIN AllInventoryNumber AS INTEGER
CHECK(VALUE >= 10)
NOT NULL;
/* The domain is intended to work with each book costs in the Library database. This is the Cost field in the BookInventoryNumber table. */
CREATE DOMAIN AllCost AS FLOAT
NOT NULL;
/* The domain is intended to define the sets of all places of the main work of the readers of the Library database. This is the Job field in the Readers table. */
CREATE DOMAIN AllJob AS CHAR(60)
NOT NULL;
/* The domain is intended to define all reader position sets in the Library database. This is the AllPost field in the Readers table. */
CREATE DOMAIN AllPost AS CHAR(30)
NOT NULL;
/* The domain is intended to define the sets of librarian home phones in the Library database. This is the HomePhone field in the Librarians table. */
CREATE DOMAIN AllHomePhone AS CHAR(20)
NOT NULL;
/* CREATING TABLES
Statements that create database tables .*/
/* First of all, we have to create tables that are referenced by foreign keys. The FOREIGN KEY clause in the CREATE TABLE statement create them. Let's look to the PassportData and the Readers tables. The PasportData table must be created before the Readers table. */
/* Creating table that will store information about passport data. */
CREATE TABLE PasportData
(Code AllCode,
Series AllSeries,
Number AllNumber,
Birthday DATE NOT NULL,
BirthPlace AllPlace,
Sex AllSex,
IssuePlace AllIssuePlace,
IssueDate DATE,
Note AllNote);
/* Creating table that will store information about readers. */
CREATE TABLE Readers
(Code AllCode,
FamilyName FIO,
Name FIO,
Patronymic FIO,
ReaderCardNumber AllReaderCardNumber,
PasportCode AllCode,
Job AllJob,
Post AllPost,
Note AllNote);
/* Creating table that will store information about librarians. */
CREATE TABLE Librarians
(Code AllCode,
ClockNumber AllClockNumber,
FamilyName FIO,
Name FIO,
Patronymic FIO,
PasportCode AllCode,
Post AllPost,
HomePhone AllHomePhone,
Note AllNote,
PRIMARY KEY (Code));
/* Creating table that will store information about book collection types. */
CREATE TABLE BookFunds
(Code AllCode,
Name AllName,
PRIMARY KEY (Code));
/*Создаем таблицу, в которой будут храниться сведения о типах телефонов*/
CREATE TABLE PhoneTypes
(Code AllCode,
Name AllNameType,
PRIMARY KEY (Code));
/* Creating table that will store information about the authors of the books. */
CREATE TABLE BookAuthors
(Code AllCode,
FamilyName FIO,
Name FIO,
Patronymic FIO,
Birthday DATE NOT NULL,
Deatheday DATE,
ShortBiography AllNote,
PRIMARY KEY (Code));
/* Creating table that will store information about the books that are in the library's collections. */
CREATE TABLE Books
(Code AllCode,
Name BookName,
IssueYear DATE NOT NULL,
Drawing AllDrawing,
BookPublishers CHAR(60),
UDK AllUDK,
Cipher AllCipher,
Note AllNote,
PRIMARY KEY (Code));
/* We create a table that give a possibility to point more than one author for one book, and more than one book for one author. In terms of relational algebra, this means that this table splits the many-to-many relationship between the BookAuthors and Books tables into two one-to-many relationships. The first relationship is between the BookAuthors and CoAuthorship tables, and the second relationship is between the Books and CoAuthorship tables. You can see it clearly on the ERP-diagram for LIBRARY DB. */
CREATE TABLE CoAuthorship
(BookCode AllCode,
AuthorCode AllCode,
PRIMARY KEY (BookCode, AuthorCode),
FOREIGN KEY (AuthorCode) REFERENCES BookAuthors
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (BookCode) REFERENCES Books
ON DELETE CASCADE
ON UPDATE CASCADE);
/* Creating table that will store information about phones. */
CREATE TABLE Phones
(ReaderCode AllCode,
PhoneTypesCode AllCode,
PhoneNumber AllHomePhone);
/* Creating table that will store information about the inventory numbers of books. */
CREATE TABLE BookInventoryNumbers
(Code AllCode,
BookCode AllCode,
FundCode AllCode,
InventoryNumber AllInventoryNumber,
Cost AllCost,
PRIMARY KEY (Code),
FOREIGN KEY (BookCode) REFERENCES Books
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (FundCode) REFERENCES BookFunds
ON DELETE CASCADE
ON UPDATE CASCADE);
/* Creating table that will store information about accounting for the issuance of books. */
/* There is an essential difference between foreign keys, that define the referential integrity between the BookGiveOutRecord table and the Librarians, BookInventoryNumbers tables. FOREIGN KEY (OutLibrarianCode) and FOREIGN KEY (InventoryCode) require the present of Librarians.Code and BookInventoryNumbers.Code primary key’s values. FOREIGN KEY (InLibrarianCode) doesn’t require mandatory present of the Librarians.Code primary key value. We don’t before know which librarian will take the book, when the reader returns it. We can’t define a librarian’s identity for a new row adding and we leave the NULL value. The InLibrarianCode domain allows this value entering. */
CREATE TABLE BookGiveOutRecord
(Code AllCode,
ReaderCode AllCode,
OutLibrarianCode AllCode,
InventoryCode AllCode,
IssueDate DATE NOT NULL,
ReturnDate DATE NOT NULL,
FactReturnDate DATE,
InLibrarianCode InLibrarianCode,
PRIMARY KEY (Code),
FOREIGN KEY (OutLibrarianCode) REFERENCES Librarians
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (InventoryCode) REFERENCES BookInventoryNumbers
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (InLibrarianCode) REFERENCES Librarians
ON DELETE CASCADE
ON UPDATE CASCADE);
/* CREATING DATA VIEWS
Statements that create data views. */
/* Creating data view for the BOOKS directory. */
CREATE VIEW Book
(InventoryNumber, BookName, IssueYear, Drawing, UDK, Cipher,
FamilyName, BookAuthorsName, Patronymic, Birthday,
Deatheday, FundName)
AS
SELECT BIN.InventoryNumber, B.Name, B.IssueYear, B.Drawing, B.UDK, B.Cipher,
BA.FamilyName, BA.Name, BA.Patronymic, BA.Birthday,
BA.Deatheday, BF.Name
FROM BookInventoryNumbers BIN, Books B, BookAuthors BA, BookFunds BF, CoAuthorship CA
WHERE BIN.BookCode = B.Code AND
BIN.FundCode = BF.Code AND
CA.AuthorCode = BA.Code AND
B.Code = CA.BookCode;
/* Creating data views for the directory READERS book. */
CREATE VIEW ReadersView
(ReaderCode, FamilyName, ReaderName, Patronymic, ReaderCardNumber,
Job, Post, Series, Number, BirthDay,
BirthPlace, Sex, IssuePlace, IssueDate)
AS
SELECT R.Code, R.FamilyName, R.Name, R.Patronymic, R.Job,
R.Post, R.ReaderCardNumber, PD.Series, PD.Number, PD.BirthDay,
PD.BirthPlace, PD.Sex, PD.IssuePlace, PD.IssueDate
FROM Readers R, PasportData PD
WHERE PD.Code = R.PasportCode;
CREATE VIEW PhonesView
(ReaderCode, TypeName, PhoneNumber, PhoneTypesCode)
AS
SELECT P.ReaderCode, P.PhoneNumber, PT.Name, PT.Code
FROM Phones P, PhoneTypes PT
WHERE P.PhoneTypesCode = PT.Code;
/* CREATING GENERATORS
Statements that create generators of primary key values. */
/* It is created a generator for the Code field of the BookAuthors table and set its initial value. */
CREATE GENERATOR BookAuthorsCode;
SET GENERATOR BookAuthorsCode TO 0;
/* It is created a generator for the Code field of the PassportData table and set its initial value. */
CREATE GENERATOR PasportDataCode;
SET GENERATOR PasportDataCode TO 0;
/* It is created a generator for the Code field of the Books table and set its initial value. */
CREATE GENERATOR BooksCode;
SET GENERATOR BooksCode TO 0;
/* It is created a generator for the Code field of the Readers table and set its initial value. */
CREATE GENERATOR ReadersCode;
SET GENERATOR ReadersCode TO 0;
/* It is created a generator for the Code field of the Librarians table and set its initial value. */
CREATE GENERATOR LibrariansCode;
SET GENERATOR LibrariansCode TO 0;
/* It is created a generator for the Code field of the BookInventoryNumbers table and set its initial value. */
CREATE GENERATOR BookInventoryNumbersCode;
SET GENERATOR BookInventoryNumbersCode TO 0;
/* It is created a generator for the Code field of the BookFunds table and set its initial value. */
CREATE GENERATOR BookFundsCode;
SET GENERATOR BookFundsCode TO 0;
/* It is created a generator for the Code field of the PhoneTypes table and set its initial value. */
CREATE GENERATOR PhoneTypesCode;
SET GENERATOR PhoneTypesCode TO 0;
/* It is created a generator for the Code field of the BookGiveOutRecord table and set its initial value. */
CREATE GENERATOR BookGiveOutRecordCode;
SET GENERATOR BookGiveOutRecordCode TO 0;
/* DEFINING EXCEPTIONS
Next these are statements that define exceptions for stored procedures and triggers. */
CREATE EXCEPTION ReaderCardNumberIsDuplicate
'There is a reader in the directory with this library card number!!!';
CREATE EXCEPTION ReaderPassportIsFound
'There is a reader in the reference book with this series and passport number!!';
CREATE EXCEPTION PsprtCdInReadersIsDuplicate
'Entering two same values of Readers.PasportCode potential key does not allow!!';
CREATE EXCEPTION PsprtCdInPasportDataNotFound
'Readers.PasportCode foreign key value misses in PasportData.Code primary key!!';
CREATE EXCEPTION SrsAndNmbrIsDuplicate
'The passport with this series and number is already registered in the DB!!!';
CREATE EXCEPTION PrKeyRdrPhnNbrIsDuplicate
'ReaderCode and PhoneNumber is Phones table primary key. Do not duplicate!!!';
CREATE EXCEPTION PrKeyInPasportDataIsDuplicate
'Entering two identical values of PassportData.Code primary key is not allowed!';
CREATE EXCEPTION ValOfFrKeyInLibrariansIsFound
'PasportData.Code value exists in Librarians.PasportCode. It was not deleted!!!';
CREATE EXCEPTION ValOfFrKeyInReadersIsFound
'PasportData.Code value exists in Readers.PasportCode. It was not deleted!!!';
CREATE EXCEPTION PrKeyInReadersDataIsDuplicate
'Entering two identical values of the primary key Readers.Code is not allowed!!';
CREATE EXCEPTION ValOfFrKeyInPhonesIsFound
'Readers.Code value exists in Phones.ReaderCode. The reader was not deleted!!!';
CREATE EXCEPTION ValOfFrKeyInBkGvOutRcrdIsFound
'Readers.Code val. exists in BookGiveOutRecord.ReaderCode The readr was not del';
CREATE EXCEPTION ReaderCodeIsNotFound
'The foreign key value Phones.ReaderCode is not found in Readers.Code!!!';
CREATE EXCEPTION PhoneTypeCodeIsNotFound
'The foreign key value Phones.PhoneTypeCode is not found in PhoneTypes.Code!!!';
/*СОЗДАНИЕ ТРИГГЕРОВ И ХРАНИМЫХ ПРОЦЕДУР
We implemented triggers and stored procedures ensuring the corporate restrictions only for the READERS directory. */
/* The default separator between statements in a script-file is semicolon. But we can’t use this separator between a trigger or stored procedure and the next statement. Therefore, we change the semicolon to a different separator. We will use it only after the last trigger statement or stored procedure. The statements will still be separated by the semicolon inside triggers and stored procedures. */
/* Next, we create the stored procedures and triggers. They provide the referential integrity for adding, changing, and deleting data from the Readers directory tables. They also give possibilities to work with the readers data. */
/************************** THE READERS DIRECTORY **********************************/
/* The procedure ensures the data integrity in the PassportData table if these data is adding or updating. */
CREATE PROCEDURE BfrInsUpdInPasportData
/* The procedure is called from the BfrInsInPasportData and BfrUpdtInPasportData triggers. The BfrInsInPasportData trigger is called before an INSERT statement. The BfrUpdtInPasportData trigger is called before an UPDATE statement.
Actually, these triggers replace the sentence PRIMARY KEY (Code) in the CREATE TABLE PasportData statement .
The triggers ensure the Passport entity integrity. First, they exclude adding two same values of primary key in Code field. Second, they prevent adding a couple of the same values in the Series and Number fields. These fields are a potential key of the PasportData table.
An exception is generated for both cases.
ATTENTION!!!
We can't use foreign keys in the Readers and Librarians tables after removing sentence PRIMARY KEY (Code) from the CREATE TABLE PasportData statement. We say about the sentences FOREIGN KEY (PasportCode) REFERENCES PasportData in the CREATE TABLE Readers and CREATE TABLE Librarians statements.
But the database needs this referential integrity for correct working with readers' and librarians' data. The support of this referential integrity is ensured by triggers and stored procedures. They are located and described below.
ATTENTION!!!
We can’t use triggers and keys for data integrity insuring between pair of relations at the same time. We have to begin with keys. This is the simplest way of data integrity enforcing. If you need more complex checking or actions, you will choose triggers.
VALUES THAT ARE ENTERED IN OTHER ATTRIBUTES ARE VALIDATED AT THE DOMAIN LEVEL. */
/* The sign of adding a new row to the table. */
/* This is a new value of passport identifier. And it’s a CODE primary key new value in the PasportData table. */
/* This is a new value of the passport series. This value is a first part of the potential key in the PasportData table. This key contains a passport series value and a passport number value */
/* This is a new value of the passport number. This value is a second part of the potential key in the PasportData table. This key contains a passport series value and a passport number value. */
/* This is an old value of passport identifier. And it’s a Code primary key old value in the PasportData table. */
/* This is an old value of the passport series. This value is a first part of the potential key in the PasportData table. */
/* This is an old value of the passport number. This value is a second part of the potential key in the PasportData table. */
/* We put in the variable the number of passports registered in the LIBRARY database, in which the primary key value matches the primary key value of the new passport. It was obtained by the procedure in the NewCode parameter. */
DECLARE VARIABLE CodeIsFound INTEGER;
/* We put into the variable the number of passports registered in the LIBRARY database, in which the series and number match the series and number of the new passport. The passport number value was obtained by the procedure in the NewNumber parameter. The passport series value was obtained by the procedure in the NewSeries parameter. */
DECLARE VARIABLE SrsAndNmbrIsFound INTEGER;
/* The process of checking values when adding a new record is different from the process of checking values when correcting data in an existing table row. The main difference is that when you add a new record, you need to check all its values, and when you correct data, you need to check only those values that have changed. This feature is implemented using an IF statements. The first condition is the same for every operator. If :DoInsert = 1, then the procedure was called from the BfrInsInPasportData trigger that fired before the new record was added. In this case, all check conditions will be executed. If :DoInsert = 0, then the procedure was called from the BfrUpdtInPasportData trigger. In this case checks are executed in each block only for values, that was changed. */
IF (:DoInsert = 1 OR :NewCode != :OldCode) THEN
BEGIN
/* We determine the number of values of the Code primary key by a counting method. */
SELECT COUNT(*)
FROM PasportData PD
WHERE PD.Code = :NewCode
INTO :CodeIsFound;
/* If the PasportData table already contains the value of the primary key Code, received by the procedure in the :NewCode parameter, then the PrKeyInPasportDataIsDuplicate exception is generated. */
IF (:CodeIsFound > 0) THEN
EXCEPTION PrKeyInPasportDataIsDuplicate;
IF (:DoInsert = 1 OR (:NewSeries != :OldSeries OR :NewNumber != :OldNumber)) THEN
BEGIN
/* We determine the number of pair of Series, Numbers values of the composite potential key by a counting method. */
SELECT COUNT(*)
FROM PasportData PD
WHERE PD.Series = :NewSeries AND
PD.Number = :NewNumber
INTO :SrsAndNmbrIsFound;
/* If the PasportData table already contains the pair of Series, Numbers values of the composite potential key, received by the procedure in the :NewSeries and :NewNumbers parameters, then the SrsAndNmbrIsDuplicate exception is generated. */
IF (:SrsAndNmbrIsFound > 0) THEN
EXCEPTION SrsAndNmbrIsDuplicate;
/* The trigger ensures data integrity when a new row is entered into the PassportData table. */
CREATE TRIGGER BfrInsInPasportData FOR PasportData
ACTIVE
BEFORE INSERT
AS
BEGIN
/* We call the procedure that ensures data integrity in the PassportData table when data is added or changed. */
EXECUTE PROCEDURE BfrInsUpdInPasportData(1,
New.Code,
New.Series,
New.Number,
New.Code,
New.Series,
New.Number);
/* The trigger ensures the integrity of the data when it is corrected in the PassportData table. */
CREATE TRIGGER BfrUpdInPasportData FOR PasportData
ACTIVE
BEFORE UPDATE
AS
BEGIN
/* The trigger ensures the integrity of the data when it is corrected in the PassportData table. */
EXECUTE PROCEDURE BfrInsUpdInPasportData(0,
New.Code,
New.Series,
New.Number,
Old.Code,
Old.Series,
Old.Number);
/* The procedure ensures the data integrity in the Readers table if these data are adding or updating. */
CREATE PROCEDURE BfrInsUpdInReaders
/* The procedure is called from the BfrInsInReaders and BfrUpdtInReaders triggers. The BfrInsInReaders trigger is called before an INSERT statement. The BfrUpdtInReaders trigger is called before an UPDATE statement.
Actually, these triggers replace the sentence PRIMARY KEY (CODE) in the CREATE TABLE Readers statement.
Triggers ensure the integrity of the "reader" entity. They exclude adding same values of the potential keys in Code, PassportCode and ReaderCardNumber column for two different readers.
Adding the existing value for any potential key generates an exception.
ATTENTION!!!
We have deprecated the PRIMARY KEY (Code) sentence in the CREATE TABLE Readers statement. In this case, referential integrity between the Phones, BookGiveOutRecord tables and the Readers table should have been maintained using triggers. The Phones.ReaderCode, BookGiveOutRecord.ReaderCode foreign keys have been stayed the same.
ATTENTION!!!
We can’t use triggers and keys for data integrity insuring between pair of relations at the same time. We have to begin with keys. This is the simplest way of data integrity enforcing. If you need more complex checking or actions, you will choose triggers.
VALUES THAT ARE ENTERED IN OTHER ATTRIBUTES ARE VALIDATED AT THE DOMAIN LEVEL. */
/* The sign of adding a new row to the table. */
/* This is a new value of reader’s identifier. This is a Code primary key new value in the Readers table. */
/* This is a new value of a reader’s card number. This is an old value of the ReaderCardNumber potential and foreign key in the Readers table. */
NewReaderCardNumber INTEGER,
/* This is a new value of reader's passport identifier. This is a new value of the PasportCode potential and foreign key in the Readers table. */
/* This is an old value of reader’s identifier. This is a Code primary key old value in the Readers table. */
/* This is an old value of reader’s card number. This is an old value of the ReaderCardNumber potential and foreign key in the Readers table. */
OldReaderCardNumber INTEGER,
/* This is a new value of Reader's passport identifier. This is a new value of the PasportCode potential and foreign key in the Readers table. */
/* This variable is used for saving of counting result Readers.Code primary key values, that it was obtained by the procedure in the NewCode parameter. Pay attention that in Readers.Code is saved identifiers of all readers whom registred in the LIBRARY database. Pay attention that in Readers.Code is saved identifiers of all readers who registred in the LIBRARY database. */
DECLARE VARIABLE CodeIsFound INTEGER;
/* This variable is used for saving of result counting Readers.ReaderCardNumber potential key values, that it was obtained by the procedure in the NewReaderCardNumber parameter. This identifier is used by librarians. */
DECLARE VARIABLE ReaderCardNbrIsFound INTEGER;
/* This variable is used for saving of result counting Readers.PasportCode potential key values, that it was obtained by the procedure in the NewPasportCode parameter. This is used also as a foreign key in the Readers table for relation with the PasportData table. */
DECLARE VARIABLE PasportCodeIsFound INTEGER;
/* This variable is used for saving of result counting Pasport.Code primary key values, that it was obtained by the procedure in the NewPasportCode parameter. This is used as the reader passport identifier. */
DECLARE VARIABLE PasportCodeIsFoundInPD INTEGER;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewCode != :OldCode) THEN
/* Using the counting method, we determine the number of a Code primary key new values in the Readers table. */
SELECT COUNT(*)
FROM Readers R
WHERE R.Code = :NewCode
INTO :CodeIsFound;
/* The registration attempt of two readers with the same :NewCode identifier value in the Readers.Code primary key generates the PrKeyInReadersDataIsDuplicate exception. */
IF (:CodeIsFound > 0) THEN
EXCEPTION PrKeyInReadersDataIsDuplicate;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewReaderCardNumber != :OldReaderCardNumber) THEN
/* Using the counting method, we determine the number of a new values of the ReaderCardNumber potential key in the Readers table. */
SELECT COUNT(*)
FROM Readers R
WHERE R.ReaderCardNumber = :NewReaderCardNumber
INTO :ReaderCardNbrIsFound;
/* The registration attempt of two readers with the same :NewReaderCardNumber identifier value in the Readers.ReaderCardNumber potential key generates the ReaderCardNumberIsDuplicate exception. */
IF (:ReaderCardNbrIsFound > 0) THEN
EXCEPTION ReaderCardNumberIsDuplicate;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewPasportCode != :OldPasportCode) THEN
/* Using the counting method, we determine the number of new values of the PasportCode potential and foregin key in the Readers table. */
SELECT COUNT(*)
FROM Readers R
WHERE R.PasportCode = :NewPasportCode
INTO :PasportCodeIsFound;
IF (:PasportCodeIsFound = 0) THEN
/* Using the counting method, we determine the number of values of the Code primary key in the PasportData table. */
SELECT COUNT(*)
FROM PasportData PD
WHERE PD.Code = :NewPasportCode
INTO :PasportCodeIsFoundInPD;
IF (:PasportCodeIsFoundInPD != 1) THEN
/* The value :NewPasportCode of the reader's passport ID was not found in the PasportData.Code primary key. Registering such a reader generates the PsprtCdInPasportDataNotFound exception. */
EXCEPTION PsprtCdInPasportDataNotFound;
/* The registration attempt of two readers with the same :NewPasportCode value of passport ID in the Readers.PasportCode potential key generates the PsprtCdInReadersIsDuplicate exception. */
EXCEPTION PsprtCdInReadersIsDuplicate;
/* The trigger enforces data integrity when a new row is entered into the Readers table. */
CREATE TRIGGER BfrInsInReaders FOR Readers
ACTIVE
BEFORE INSERT
AS
BEGIN
/* We call the procedure that ensures data integrity in the Readers table when data is added or changed. */
EXECUTE PROCEDURE BfrInsUpdInReaders(1,
New.Code,
New.ReaderCardNumber,
New.PasportCode,
New.Code,
New.ReaderCardNumber,
New.PasportCode);
/* The trigger ensures the integrity of the data when it is corrected in the Readers table. */
CREATE TRIGGER BfrUpdInReaders FOR Readers
ACTIVE
BEFORE UPDATE
AS
BEGIN
/* The trigger ensures the integrity of the data when it is corrected in the Readers table. */
EXECUTE PROCEDURE BfrInsUpdInReaders(0,
New.Code,
New.ReaderCardNumber,
New.PasportCode,
Old.Code,
Old.ReaderCardNumber,
Old.PasportCode);
/* The procedure ensures the data integrity in the Phones table if these data are adding or updating. */
CREATE PROCEDURE BfrInsUpdInPhones
/* The procedure is called from the BfrInsInPhonesand and BfrUpdInPhones triggers. The BfrInsInPhones trigger is called before an INSERT statement. The BfrUpdInPhones trigger is called before an UPDATE statement. These triggers ensure the integrity of the entity "Reader's phone number". They exclude adding two identical phone numbers for one reader. It means that can’t two the same pairs of the ReaderCode and PhoneNumber values in the Phones table. This pair of columns is a composite potential and primary key of the Phones table.
Adding the existing pair of values for primary key generates an exception.
The BfrInsInPhones, BfrUpdInPhones and BfrDltInReaders triggers provide referential integrity between the entities "Reader's phone number" ( the Phones relation) and "Reader" ( the Readers relation). Additionally, the BfrInsInPhones and BfrUpdInPhones triggers provide referential integrity between the entities "Reader's phone number type" ( the Phones relation) and "phone type" ( the PhoneTypes relation).
This makes it possible not to define the FOREIGN KEY (ReaderCode) REFERENCES Readers and the FOREIGN KEY (PhoneTypesCode) REFERENCES PhoneTypes in the CREATE TABLE statement.
ATTENTION!!!
Read the description of the BfrDltInReaders trigger. The trigger like it for the PhoneTypes table wasn’t defined. The referential integrity between the Phones table and the PhoneType table can’t be realizing in whole volume without it. Try to create such a trigger by yourself.
ATTENTION!!!
We can’t use triggers and keys for data integrity insuring between pair of relations at the same time. We have to begin with keys. This is the simplest way of data integrity enforcing. If you need more complex checking or actions you will choose triggers.
VALIDATION OF THE VALUE THAT IS INTRODUCED IN THE PhoneNumber ATTRIBUTE IS PERFORMED AT THE DOMAIN LEVEL. */
/* The sign of adding a new row to the table. */
/* This is a new value of reader’s identifier. This is a ReaderCode foreign keynew value in the Phones table. */
/* This is a new value of reader’s phone type identifier. This is a PhoneTypesCode foreign key new value in the Phones table. */
NewPhoneTypesCode INTEGER,
/* This is a new value of reader’s phone number. */
/* This is an old value reader’s identifier. This is a ReaderCode foreign key old value in the Phones table. */
/* This is an old value of reader’s phone type code. This is a PhoneTypesCode foreign key old value in the Phones table. */
OldPhoneTypesCode INTEGER,
/* This is an old value of reader’s phone number. */
/* The variable is used for saving of the Phones.ReaderCode foreign key value counting result. The counting executes among Readers.Code primary key values. The procedure receives the Phones.ReaderCode value in the NewReaderCode parameter. */
DECLARE VARIABLE ReaderCodeIsFound INTEGER;
/* The variable is used for saving of the Phones.PhoneTypesCode foreign key value counting result. The counting executes among the PhoneTypes.Code primary key values. The procedure receives the Phones.ReaderCode value in the NewPhoneTypesCode parameter. */
DECLARE VARIABLE PhoneTypeCodeIsFound INTEGER;
/* The variable is used for saving of a result counting of a pair ReaderCode and PhoneNumber values in the Phones table. They are a potential and primary key of the Phones relation. The procedure receives this pair of values through the NewReaderCode and NewPhoneNumber parameters. */
DECLARE VARIABLE PrKeyRdrPhnNbrIsDuplicate INTEGER;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewReaderCode != :OldReaderCode) THEN
/* Using the counting method, we determine the number of the Phones.ReaderCode foreign key new values among the Readers.Code primary key values. */
SELECT COUNT(*)
FROM Readers R
WHERE R.Code = :NewReaderCode
INTO :ReaderCodeIsFound;
/* The exception ReaderCodeIsNotFound will be generated when NewReaderCode reader identifier don’t find in the Readers table. */
IF (:ReaderCodeIsFound != 1) THEN
EXCEPTION ReaderCodeIsNotFound;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewPhoneTypesCode != :OldPhoneTypesCode) THEN
/* Using the counting method, we determine the number of the Phones.PhoneTypesCode foreign key new values among the PhoneTypes.Code primary key values. */
SELECT COUNT(*)
FROM PhoneTypes PT
WHERE PT.Code = :NewPhoneTypesCode
INTO :PhoneTypeCodeIsFound;
/* The exception PhoneTypeCodeIsNotFound will be generated if the :NewPhoneTypesCode value of the phone type identifier don’t find in the PhoneTypes table. */
IF (:PhoneTypeCodeIsFound != 1) THEN
EXCEPTION PhoneTypeCodeIsNotFound;
/* See the comment at the beginning of the procedure body BfrInsUpdInPasportData. */
IF (:DoInsert = 1 OR :NewReaderCode != :OldReaderCode OR :NewPhoneNumber != :OldPhoneNumber) THEN
/* Using the counting method, we determine the number of the new values of the composite potential and primary key that includes the ReaderCode, PhoneNumber columns. */
SELECT COUNT(*)
FROM Phones P
WHERE P.ReaderCode = :NewReaderCode AND
P.PhoneNumber = :NewPhoneNumber
INTO :PrKeyRdrPhnNbrIsDuplicate;
/* The registration attempt of same the two :NewPhoneNumber phone number for one :NewReaderCode reader identifier generates the exception PrKeyRdrPhnNbrIsDuplicate. */
IF (:PrKeyRdrPhnNbrIsDuplicate > 0) THEN
EXCEPTION PrKeyRdrPhnNbrIsDuplicate;
/* The trigger enforces the data integrity when a new row is entered into the Phones table. */
CREATE TRIGGER BfrInsInPhones FOR Phones
ACTIVE
BEFORE INSERT
AS
BEGIN
/* The procedure ensures the data integrity in the Phones table when they are added or changed. */
EXECUTE PROCEDURE BfrInsUpdInPhones(1,
New.ReaderCode,
New.PhoneTypesCode,
New.PhoneNumber,
New.ReaderCode,
New.PhoneTypesCode,
New.PhoneNumber);
/* The trigger ensures the integrity of the data when it is updated in the Phones table. */
CREATE TRIGGER BfrUpdInPhones FOR Phones
ACTIVE
BEFORE UPDATE
AS
BEGIN
/* The procedure ensures the data integrity in the Phones table when they are added or changed. */
EXECUTE PROCEDURE BfrInsUpdInReaders(0,
New.ReaderCode,
New.PhoneTypesCode,
New.PhoneNumber,
Old.ReaderCode,
Old.PhoneTypesCode,
Old.PhoneNumber);
/* The trigger executes a cascading update of the PasportCode passport identifier values in the Readers and Librarians tables. Every passport identifier value references to the PasportData.Code primary key value. */
CREATE TRIGGER AftUpdtInPasportData FOR PasportData
/* It gives us a possibility doesn’t define foreign keys with the cascade updating in the CREATE TABLE Readers and CREATE TABLE Librarians statements.
ATTENTION!!!
We can’t use triggers and key for a cascade updating at the same time. */
ACTIVE
AFTER UPDATE
AS
BEGIN
/* The PasportData.Code primary key value was changed. */
IF (NEW.Code != OLD.Code) THEN
/* This is a cascade update of the Readers.PasportCode foreign key value. */
UPDATE Readers
SET PasportCode = NEW.Code
WHERE PasportCode = OLD.Code;
/* This is a cascade update of the Librarians.PasportCode foreign key value. */
UPDATE Librarians
SET PasportCode = NEW.Code
WHERE PasportCode = OLD.Code;
/* The trigger executes the cascading update of the ReaderCode reader identifier values in the Phones table. */
CREATE TRIGGER AftUpdtInReaders FOR Readers
/* Every reader identifier value references to the Readers.Code primary key value. In this case the ReaderCode foreign key have to define without ON UPDATE CASCADE option in the CREATE TABLE Phones statement.
ATTENTION!!!
We can’t use triggers and keys for a cascade updating at the same time. */
ACTIVE
AFTER UPDATE
AS
BEGIN
/* The Readers.Code primary key value has been changed. */
IF (NEW.Code != OLD.Code) THEN
/* This is a cascade update of the Phones.ReaderCode foreign key value. */
UPDATE Phones
SET ReaderCode = NEW.Code
WHERE ReaderCode = OLD.Code;
/* The trigger ensures the data integrity between the Readers table and the Phones and BookGiveOutRecord tables. */
CREATE TRIGGER BfrDltInReaders FOR Readers
/* Two actions are executed one by one before a string deleting in the Readers table. First a Readers.Code primary key value searching is executed among the Phones.ReaderCode foreign key values. Second searching of same this primory key value is executing among the BookGiveOutRecord.ReaderCode foreign key values. If at least one of they are success, an exception will be generated.
This approach of data integrity enforcing is an equivalent of Phones.ReaderCode and BookGiveOutRecord.ReaderCode foreign keys defining without the ON DELETE CASCADE options. We can organize the cascade deleting in these triggers using next statements:
DELETE FROM Phones
WHERE ReaderCode = Old.Code
DELETE FROM BookGiveOutRecord
WHERE ReaderCode = Old.Code
We need to place them instead statements
EXCEPTION ValOfFrKeyInPhonesIsFound
EXCEPTION ValOfFrKeyInBkGvOutRcrdIsFound
ATTENTION!!!
We can’t use triggers and keys for a cascade deleting at the same time. */
/* The variable is used for result saving of the Readers.Code primary key value counting. The counting executes among the Phones.ReaderCode and BookGiveOutRecords.ReaderCode foreign key values. */
DECLARE VARIABLE ReaderCodeIsFound INTEGER;
/* Using the counting method, we determine the number of the Readers.Code primary key old values among the Phones.ReaderCode foreign key values. The Old.Code variable contains a Readers.Code value that present in a deleting Readers table row. */
SELECT COUNT(*)
FROM Phones P
WHERE P.ReaderCode = Old.Code
INTO :ReaderCodeIsFound;
/* The ValOfFrKeyInPhonesIsFound exception will be generated if old the value of the Readers.Code primary key was found among Phones.ReaderCode foreign key values. */
IF (:ReaderCodeIsFound > 0) THEN
EXCEPTION ValOfFrKeyInPhonesIsFound;
/* Using the counting method, we determine the number of the Readers.Code primary key old values among the BookGiveOutRecord.ReaderCode foreign key values. The Old.Code variable is a Readers.Code value that present in a deleting Readers table row. */
SELECT COUNT(*)
FROM BookGiveOutRecord BG
WHERE BG.ReaderCode = Old.Code
INTO :ReaderCodeIsFound;
/* The ValOfFrKeyInBkGvOutRcrdIsFound exception will be generated if the old value of Readers.Code primary key was found among the BookGiveOutRecord.ReaderCode foreign key values. */
IF (:ReaderCodeIsFound > 0) THEN
EXCEPTION ValOfFrKeyInBkGvOutRcrdIsFound;
/* The trigger ensures the data integrity between the PasportData table and the Readers and Librarians tables. */
CREATE TRIGGER BfrDltInPasportData FOR PasportData
/* Two actions are executed one by one before a string deleting in the PasportData table. The first searching of the PasportData.Code primary key value is executed among the Readers.PasportCode foreign key values. The second searching of this primary key value is executing among the Librarians.PasportCode foreign key values. If at least one of they are success, an exception will be generated.
This approach of data integrity enforcing is an equivalent of the Readers.PasportCode and the Librarians.PasportCode foreign keys defining without the ON DELETE CASCADE options. We can organize the cascade deleting in these triggers using next statements:
DELETE FROM Readers
WHERE PasportCode = Old.Code;
DELETE FROM Librarians
WHERE PasportCode = Old.Code;
We need to place them instead statements
EXCEPTION ValOfFrKeyInReadersIsFound;
EXCEPTION ValOfFrKeyInLibrariansIsFound;
ATTENTION!!!
We can’t use triggers and keys for a cascade deleting at the same time. */
/* The variable is used for the result saving of the PasportData.Code primary key value counting. The counting executes among the values of the Readers.PasportCode and Librarians.PasportCode foreign keys. */
DECLARE VARIABLE PasportCodeIsFound INTEGER;
/* Using the counting method, we determine the number of the PasportData.Code primary key old values among the Readers.PasportCode foreign key values. The Old.Code variable is a PasportData.Code value that present in a deleting PasportData table row. */
SELECT COUNT(*)
FROM Readers R
WHERE R.PasportCode = Old.Code
INTO :PasportCodeIsFound;
/* The exception ValOfFrKeyInReadersIsFound will be generated if the old value of the PasportData.Code primary key was found among the Readers.PasportCode foreign key values. */
IF (:PasportCodeIsFound > 0) THEN
EXCEPTION ValOfFrKeyInReadersIsFound;
/* Using the counting method, we determine the number of the PasportData.Code primary key old values among the Librarians.PasportCode foreign key values. the Old.Code variable includes the PasportData.Code value that presents in the deleting PasportData table row. */
SELECT COUNT(*)
FROM Librarians L
WHERE L.PasportCode = Old.Code
INTO :PasportCodeIsFound;
/* The ValOfFrKeyInLibrariansIsFound exception will be generated if the old value of the PasportData.Code primary key was found among the Librarians.PasportCode foreign key values. */
IF (:PasportCodeIsFound > 0) THEN
EXCEPTION ValOfFrKeyInLibrariansIsFound;
/* The procedure returns a number of full matching first name, last name, and patronymic among readers. It uses by operators for warning of such the matching in the Readers directory. This information is useful for making decision about adding or correcting of existing data about a reader in the directory. */
CREATE PROCEDURE SlcFNP_InReaders
/* Reader's last name */
/* Reader’s first name */
/* Reader's patronymic */
/* The variable is used for saving of the full matching counting result of the reader’s first name, second name and patronymic in the Readers table and returning it to a client application. */
/* The procedure returns the full matching counting result of the reader’s first name, second name and patronymic to a client application. */
SELECT COUNT(*)
FROM Readers R
WHERE R.FamilyName = :FamilyName AND
R.Name = :Name AND
R.Patronymic = :Patronymic
INTO :FNP_IsFound;
/* The procedure returns a quantity of phone numbers in the Phone table that doesn’t belong to the reader with passed identifier from a client application. An operator can use this information for working with the data of an existing or new reader. */
CREATE PROCEDURE SlcRP_InPhones
/* Reader’s phone number. */
/* Reader’s identifier. */
/* The variable is used for the quantity saving of phone numbers in the Phone table that doesn’t belong to the reader with passed an identifier from a client application. */
/* This is a counting result of quantity saving of a phone numbers in the Phone table that doesn’t belong to the reader with passed an identifier from a client application. The result is returned back to the client application. */
SELECT COUNT(*)
FROM Phones P
WHERE P.ReaderCode != :ReaderCode AND
P.PhoneNumber = :PhoneNumber
INTO :RP_IsFound;
/* This is an action procedure. It is called from a client application and adds information about a new reader in the Readers and PasportData tables. The procedure parameter values with the R_ prefix is added in the Readers table. And the procedure parameter values with the PD_ prefix is added in the PasportData table. */
CREATE PROCEDURE NewReader
/* Reader’s passport series */
/* Reader’s passport number */
/* Reader's birthday */
/* Reader's birthplace */
/* Gender of the reader */
/* Issue place of reader’s passport */
/* Issue date of the reader's passport */
/* Reader's last name */
/* Reader first name */
/* Reader's patronymic */
/* Reader’s card number */
R_ReaderCardNumber INTEGER,
/* Reader's place of work */
/* Reader’s position */
/* The variable is used for a saving result of an exact matching counting result of the combination PD_Series and PD_Number values in the PasportData table. */
DECLARE VARIABLE SrsAndNmbrIsFound INTEGER;
/* The passport identifier of the newly registered reader is placed in the variable. */
DECLARE VARIABLE R_PasportCode INTEGER;
/* Using the counting method, we determine an exact matching quantity pairs of the new reader’s passport series and number with the PasportData.Series and PasportData.Number values. */
SELECT COUNT(*)
FROM PasportData PD
WHERE PD.Series = :PD_Series AND
PD.Number = :PD_Number
INTO :SrsAndNmbrIsFound;
IF (:SrsAndNmbrIsFound = 0) THEN
/* If the data about the new reader’s passport doesn’t find in the Readers table then the INSERT statemen adds the data about the new reader to the PasportData table. */
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), :PD_Series, :PD_Number, :PD_Birthday, :PD_BirthPlace,
:PD_Sex, :PD_IssuePlace, :PD_IssueDate);
/* The data about the new reader’s passport finds in the Readers table.
ATTENTION!!!
The new reader can work as a librarian and his passport can have already registered in the PasportData table.
The statement updates the data of reader’s passport in the PasportData table. The PasportData.Series and PasportData.Number compound foreign key is used for this. */
UPDATE PasportData
SET Birthday = :PD_Birthday,
BirthPlace = :PD_BirthPlace,
Sex = :PD_Sex,
IssuePlace = :PD_IssuePlace,
IssueDate = :PD_IssueDate
WHERE Series = :PD_Series AND
Number = :PD_Number;
/* We get the new reader’s passport identifier. We need this for the data adding of the new reader in the Readers table. */
SELECT PD.Code
FROM PasportData PD
WHERE PD.Series = :PD_Series AND
PD.Number = :PD_Number
INTO :R_PasportCode;
/* The statement adds the data about the new reader to the Readers table. */
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), :R_FamilyName, :R_Name, :R_Patronymic, :R_ReaderCardNumber,
:R_PasportCode, :R_Job, :R_Post);
/* This is an action procedure. It is called from a client application and adds a new phone number of reader in the Phones table. */
CREATE PROCEDURE NewReaderPhone
/* Reader’s identifier */
/* Phone type identifier */
/* Reader’s phone number in international format */
/* The variable is used for saving of a :ReaderCode and :PhoneNumber pair values counting result in the Phones table. The pair of Phones.ReaderCode and Phones.PhoneNumber values is a compound potential and the primary key of the Phones table. */
DECLARE VARIABLE PrKeyIsDuplicate INTEGER;
SELECT COUNT(*)
FROM Phones P
WHERE P.ReaderCode = :ReaderCode AND
P.PhoneNumber = :PhoneNumber
INTO :PrKeyIsDuplicate;
/* Using the counting method, we determine an exact matching quantity of the :ReaderCode and :PhoneNumber values pair with the pairs of Phones.ReaderCode and Phones.PhoneNumber values in rows of the Phones table. */
IF (PrKeyIsDuplicate > 0) THEN
/* The reader's phone has been already registered in the Phones table. */
UPDATE Phones
SET PhoneTypesCode = :PhoneTypesCode
WHERE ReaderCode = :ReaderCode AND
PhoneNumber = :PhoneNumber;
/* The statement updates the data of an identifier of reader’s phone type. */
/* The reader’s phone isn’t registered in the Phones table. */
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(:ReaderCode, :PhoneTypesCode, :PhoneNumber);
/* The statement adds data about the Reader’s new phone in the Phones table. */
/* This is an action procedure. It updates reader’s data in the Readers table. It is called from a client application. The UpdDataInPasportData action procedure is used for reader’s passport data updating in the PasportData table. */
CREATE PROCEDURE UpdDataInReaders
/* In this parameter the procedure gets a number of one of the Readers table’s potential key: */
/* 1 - Code; */
/* 2 - ReaderCardNumber; */
/* 3 - PasportCode. */
/* The potential key value is used for the row searching with the reader’s data, those have been to update. */
/* Reader’s identifier */
/* Reader's last name */
/* Reader's first name */
/* Reader's patronymic */
/* Library card number */
ReaderCardNumber INTEGER,
/* Reader's passport identifier */
/* Reader's place of work */
/* Reader’s position */
IF (PotentialKey = 1) THEN
/* The Readers.Code potential and primary key value defines the row for reader’s data updating. */
UPDATE Readers
SET FamilyName = :FamilyName,
Name = :Name,
Patronymic = :Patronymic,
ReaderCardNumber = :ReaderCardNumber,
PasportCode = :PasportCode,
Job = :Job,
Post = :Post
WHERE Code = :Code;
/* The UPDATE statement updates all values in the row except the Readers.Code potential and primary key value. */
IF (PotentialKey = 2) THEN
/* The Readers.ReaderCardNumber potential key value defines the row for reader’s data updating. */
UPDATE Readers
SET FamilyName = :FamilyName,
Name = :Name,
Patronymic = :Patronymic,
ReaderCardNumber = :ReaderCardNumber,
PasportCode = :PasportCode,
Job = :Job,
Post = :Post
WHERE ReaderCardNumber = :ReaderCardNumber;
/* The statement updates all values in the row except the Readers.ReaderCardNumber potential key value. */
IF (PotentialKey = 3) THEN
/* The Readers.PasportCode potential key value defines the row for reader’s data updating. */
UPDATE Readers
SET FamilyName = :FamilyName,
Name = :Name,
Patronymic = :Patronymic,
ReaderCardNumber = :ReaderCardNumber,
PasportCode = :PasportCode,
Job = :Job,
Post = :Post
WHERE PasportCode = :PasportCode;
/* The UPDATE statement updates all values in the row except the Readers.PasportCode potential key value. */
/* This is an action procedure. It updates reader’s passport data in the PasportData table. It is called from a client application. The UpdDataInReaders action procedure is used for reader’s data updating in the Readers table. */
CREATE PROCEDURE UpdDataInPasportData
/* In this parameter the procedure gets a number of one of the PasportData table’s potential key: */
/* 1 - Code; */
/* 2 - Series, Number. */
/* The potential key value is used for the row searching with reader’s passport data, those have been to update. */
/* Passport identifier */
/* Passport series */
/* Passport number */
/* Reader's birthday */
/* Reader's birthplace */
/* Gender of the reader */
/* Place of issue of the passport to the reader */
/* Issue date of the reader's passport */
IF (PotentialKey = 1) THEN
/* The PasportData.Code potential and primary key value defines the row for reader’s passport data updating. */
UPDATE PasportData
SET Series = :Series,
Number = :Number,
Birthday = :Birthday,
BirthPlace = :BirthPlace,
Sex = :Sex,
IssuePlace = :IssuePlace,
IssueDate = :IssueDate
WHERE Code = :Code;
/* The UPDATE statement updates all values in the row except the PasportData.Code potential and primary key value. */
IF (PotentialKey = 2) THEN
/* The pair of PasportData.Series and PasportData.Number values defines the row for reader’s passport data updates. This pair of values is the PassportData table’s compound potential key. */
UPDATE PasportData
SET Series = :Series,
Number = :Number,
Birthday = :Birthday,
BirthPlace = :BirthPlace,
Sex = :Sex,
IssuePlace = :IssuePlace,
IssueDate = :IssueDate
WHERE Series = :Series AND
Number = :Number;
/* The UPDATE statement updates all values in the row except the PasportData.Series and PasportData.Number values. This pair of values is the PassportData table’s compound potential key. */
/* This is an action procedure. It updates a number and a type of the reader’s phone in the Phones table. It is called from a client application. */
CREATE PROCEDURE UpdDataInPhones
/* Reader’s identifier. */
/* Reader’s phone type identifier. */
/* Reader’s old phone number. */
/* Reader's new phone number. */
/* The UPDATE statement changes a reader’s old phone number by new. In the row with reader’s old phone number the pair of Phones.ReaderCode and Phones.PhoneNumber values of the Phone table’s compound potential and primary kay has to match with the pair of :ReaderCode and :PhoneNumber values. */
UPDATE Phones
SET ReaderCode = :ReaderCode,
PhoneTypesCode = :PhoneTypesCode,
PhoneNumber = :NewPhoneNumber
WHERE ReaderCode = :ReaderCode AND
PhoneNumber = :PhoneNumber;
/* This is an action procedure. It deletes all the reader’s data in Library database. The information is deleted in the BookGiveOutRecord, PasportData, Phones and Readers tables. The procedure is called from a client application. */
CREATE PROCEDURE DltReader
/* Reader’s identifier. */
/* The variable is used for a value saving of reader’s passport identifier. */
DECLARE VARIABLE PasportCode INTEGER;
/* The variable is used for a quantity saving of reader’s passport identifier in the Librarians table. */
DECLARE VARIABLE PasportCodeIsFound INTEGER;
/* The statement deletes the information about phones of the reader in the Phones table. */
DELETE FROM Phones
WHERE ReaderCode = :ReaderCode;
/* The statement deletes information in the BookGiveOutRecord table about the books that were given out to the reader. */
DELETE FROM BookGiveOutRecord
WHERE ReaderCode = :ReaderCode;
/* We get the reader’s passport identifier. It needs us for searching it in the Librarians table. */
SELECT R.PasportCode
FROM Readers R
WHERE R.Code = :ReaderCode
INTO :PasportCode;
/* The statement deletes information about the reader in the Readers table. */
DELETE FROM Readers
WHERE Code = :ReaderCode;
/* Using the counting method, we search the reader’s passport identifier in the Librarians table. If it found than the reader is a library employee. */
SELECT COUNT(*)
FROM Librarians L
WHERE L.PasportCode = :PasportCode
INTO :PasportCodeIsFound;
IF (:PasportCodeIsFound = 0) THEN
/* The reader’s passport identifier didn’t find in the Librarians table. So, the reader isn’t a library employee. */
DELETE FROM PasportData
WHERE Code = :PasportCode;
/* The statement deletes the reader’s passport data in the PasportData table. */
/********************* THE END OF THE DIRECTORY "READERS" **********************/
/* We return the separator between the statements of the script file, which is used by default. */
/* CREATING INDEXES.
The indexes need for an acceleration of searching information in the tables. General principles of their use were described in in the paragraph "Optimizing the structure of indexes". */
CREATE INDEX RdrCd
ON BookGiveOutRecord
(FactReturnDate);
CREATE INDEX RdrCdFctRtrnDt
ON BookGiveOutRecord
(ReaderCode, FactReturnDate);
CREATE INDEX CdPsprtCd
ON Readers
(Code, PasportCode);
CREATE INDEX CdBrthday
ON PasportData
(Code, Birthday);
CREATE INDEX CdUDK
ON Books
(Code, UDK);
CREATE INDEX CdBkCd
ON BookInventoryNumbers
(Code, BookCode);
CREATE INDEX FndCd
ON BookInventoryNumbers
(FundCode);
CREATE INDEX RdrCrdNmbr
ON Readers
(ReaderCardNumber);
CREATE INDEX PhnTpCd
ON Phones
(PhoneTypesCode);
/* These are INSERT statements that adds the information into database. */
/* We add 24 records to the PasportData table. */
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AA', 45003, '05/30/1930', 'Russia, Opochki city',
'F', 'Dniepropetrovsk', '01/12/1995');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AA', 15700, '02/23/1930', 'Russia, Vladymir city',
'F', 'г. Zhitomir', '03/16/2000');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AB', 87134, '01/20/1963', 'Dniepropetrovsk region, Solenoe settlement',
'F', 'Dniepropetrovsk region, Solenoe settlement', '01/10/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AE', 12300, '11/12/1960', 'Ukraine, Donetsk city',
'M', 'Donetsk', '12/15/1991');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AE', 01067, '07/19/1981', 'Ukraine, Dniepropetrovsk',
'M', 'Dniepropetrovsk', '08/25/1997');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AG', 01568, '09/14/1956', 'Kazakhstan, Pavlodar city',
'M', 'Kiev', '05/24/1999');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AZ', 43188, '11/13/1970', 'Dniepropetrovsk region, Dnieprodzerzhinsk city',
'F', 'Dniepropetrovsk region, Dnieprodzerzhinsk city', '05/15/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AK', 23490, '01/05/1961', 'Russia, Samara city',
'F', 'Dniepropetrovsk', '09/13/2000');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AC', 90843, '10/10/1949', 'Moldova,, Kishenev, city',
'F', 'Dniepropetrovsk', '12/13/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AY', 90764, '11/14/1950', 'Ukraine, Nikolaev city',
'F', 'Nikolaev city', '11/11/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'IK', 10842, '07/19/1949', 'Ukraine, Kirovograd city',
'M', 'Dniepropetrovsk', '01/06/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'IK', 45190, '07/18/1983', 'Dniepropetrovsk region, Petropavlovka settlement',
'F', 'Dniepropetrovsk region, Petropavlovka settlement', '09/20/1999');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AH', 61327, '10/1/1960', 'Russia , St. Petersburg',
'F', 'St. Petersburg', '10/12/1976');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AH', 64277, '12/23/1972', 'Ukraine, Lvov city',
'M', 'Lvov', '01/06/1988');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AK', 89125, '05/07/1980', 'Ukraine, Kiev city',
'M', 'Kiev', '01/10/1998');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AK', 55706, '04/07/1965', 'Donetsk',
'F', 'Donetsk', '04/20/1982');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AC', 73271, '07/05/1950', 'Crimea',
'M', 'Simferopol', '08/23/1970');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AG', 45879, '02/04/1961', 'Dniepropetrovskк',
'F', 'Dnieprodzerzhinsk', '03/14/1980');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AC', 12548, '04/08/1974', 'Truskavets',
'F', 'Prikarpatie', '05/28/1989');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AK', 12578, '11/11/1987', 'Donetsk, Kramatorsk',
'F', 'Kiev', '01/26/2000');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AC', 55489, '09/25/1981', 'Sumy',
'F', 'Charkov', '11/06/1999');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AY', 45789, '08/07/1972', 'Hungary',
'F', 'Ivano-Frankovsk', '10/03/1988');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AG', 35126, '03/18/1975', 'Odessa',
'F', 'Odessa', '06/19/1993');
INSERT INTO PasportData
(Code, Series, Number, Birthday, BirthPlace,
Sex, IssuePlace, IssueDate)
VALUES
(GEN_ID(PasportDataCode, 1), 'AH', 15625, '06/19/1966', 'Dniepropetrovsk',
'M', 'Dniepropetrovsk, Petrikovka', '08/12/1982');
/* We add 12 records to the Readers table. */
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Ivanov', 'Petr', 'Ivanovich', 317,
4, 'NMU, CM dep.', 'Assistant');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Fedorez', 'Irina', 'Olegovna', 28,
1, 'NMU, AEC', 'Front-door security');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Ilin', 'Ivan', 'Petrovich', 1345,
11, 'NMU, physics dep.', 'Associate professor');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Surenko', 'Dmitry', 'Pavlovich', 543,
6, 'NMU, geophysicist dep.', 'Senior professor');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Korshunova', 'Natalia', 'Yurievna', 128,
8, 'NMU, geo‑informatics dep.', 'Assistant');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Nosenko', 'Oleg', 'Vladimirovitch', 5672,
5, 'NMU, ICC', 'Engineer');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Brusov', 'Vladimir', 'Mikhajlovitch', 485,
24, 'NMU, geodesy dep.', 'Laboratory assistant');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Kozirev ', 'Alexey', 'Sergeevich', 759,
15, 'NMU, criminology dep.', 'Professor');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Levchenko', 'Julia', 'Pavlovna', 146,
18, 'NMU, political theory dep.', 'Head of department');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Svetlaya', 'Tatyana', 'Ivanovna', 2021,
22, 'NMU, translation dep.', 'Senior professor');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Sheglov', 'Petr', 'Yevgenievich', 997,
14, 'NMU, power supply dep.', 'Assistant');
INSERT INTO Readers
(Code, FamilyName, Name, Patronymic, ReaderCardNumber,
PasportCode, Job, Post)
VALUES
(GEN_ID(ReadersCode, 1), 'Kirilenko', 'Victor', 'Alexandrovich', 1010,
17, 'NMU, electric drive dep.', 'Deputy dean');
/* We add 12 records to the Librarians table. */
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 28, 'Ivanova', 'Elena', 'Vladimirovna',
2, 'Librarian', '52-XX-75');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 12, 'Nikolaenko', 'Lubov', 'Nikolaevna',
10, 'Librarian', '46-XX-19');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 187, 'Inozemtseva', 'Ivanna', 'Modestovna',
9, 'Senior librarian', '775-XX-00');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 83, 'Malzeva', 'Diana', 'Petrovna',
12, 'Librarian', '29-XX-15');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 10, 'Sizranzeva', 'Tatyana', 'Igorevna',
3, 'Librarian', '370-XX-22');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 100, 'Stavka', 'Lilia', 'Ivanovna',
7, 'Librarian', '22-XX-01');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 50, 'Leshenko', 'Alla', 'Fedorovna',
13, 'Librarian', '722-XX-36');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 36, 'Sira', 'Lidia', 'Ivanovna',
19, 'Librarian', '254-XX-02');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 45, 'Prokhina', 'Tamara', 'Lvovna',
21, 'Librarian', '63-XX-01');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 78, 'Samilenko', 'Viktoria', 'Igorevna',
20, 'Librarian', '125-XX-80');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 69, 'Stepanova', 'Aleksandra', 'Nikolaevna',
16, 'Senior librarian', '445-XX-65');
INSERT INTO Librarians
(Code, ClockNumber, FamilyName, Name, Patronymic,
PasportCode, Post, HomePhone)
VALUES
(GEN_ID(LibrariansCode, 1), 17, 'Petrova', 'Alina', 'Sergeevna',
23, 'Librarian', '999-XX-05');
/* We add 2 records to the BookFunds table. */
INSERT INTO BookFunds
(Code, Name)
VALUES
(GEN_ID(BookFundsCode, 1), 'STL');
INSERT INTO BookFunds
(Code, Name)
VALUES
(GEN_ID(BookFundsCode, 1), 'Students');
/* We add 16 records to the BookAuthors table. */
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Tikhonov', 'Oleg', 'Nikolaevich', '07/12/1945');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Akulenko', 'Leonid', 'Dmitrievich', '11/26/1962');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Prozuto', 'Viktor', 'Sergeevich', '08/14/1950');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Kolosov', 'Gennadiy', 'Yevgenievich', '06/01/1950');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Kozakov', 'Igor', 'Yeleseevich', '09/18/1962');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Pavlovskaya', 'Tatyana', 'Alexandrovna', '08/03/1967');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Zaytsev', 'Valentin', 'Fedorovich', '07/03/1955');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Polianin', 'Andrey', 'Dmitrievich', '03/15/1939');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Andronov', 'Alexandr', 'Mikhajlovitch', '06/18/1969');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Kopytov', 'Evgeniy', 'Alexandrovich', '03/09/1950');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Gringlaz', 'Leonid', 'Yakovlievich', '07/11/1950');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Bavrin', 'Ivan', 'Ivanovich', '09/12/1949');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Pugachev', 'Vladymyr', 'Semenovich', '02/03/1958');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Olifer', 'Victor', 'м', '04/07/1961');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Olifer', 'Natalia', 'Alexandrovna', '12/11/1965');
INSERT INTO BookAuthors
(Code, FamilyName, Name, Patronymic, Birthday)
VALUES
(GEN_ID(BookAuthorsCode, 1), 'Flegontov', 'Alexandr', 'Vladimirovitch', '07/07/1963');
/* We add 4 records to the PhoneTypes table. */
INSERT INTO PhoneTypes
(Code, Name)
VALUES
(GEN_ID(PhoneTypesCode, 1), 'Home');
INSERT INTO PhoneTypes
(Code, Name)
VALUES
(GEN_ID(PhoneTypesCode, 1), 'Office');
INSERT INTO PhoneTypes
(Code, Name)
VALUES
(GEN_ID(PhoneTypesCode, 1), 'Cellphone');
INSERT INTO PhoneTypes
(Code, Name)
VALUES
(GEN_ID(PhoneTypesCode, 1), 'Satellite');
/* We add 14 records to the Books table. */
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Automation of industrial processes on concentration plant',
'01/01/1985', 6000, '"Nadra"', '622.7', '622.7-52/T');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Concentration and metallurgy processes automation problem solving',
'01/01/1969', 2000, '"Nauka"', '622.7-52', '622.7-52(075)/T');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Asymptotic methods of optimal management',
'01/01/1987', 1000, '"Avtomat"', '681.513.5', '681.513.5:/A');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Optimum automotive systems synthesis',
'01/31/1984', 5000, '"Avtomat"', '681.513.5', '681.513.5:/DO');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Stochastic systems optimization methods',
'01/01/1987', 4500, '"Matstat"', '681.513.5', '681.513.5:/DO');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Automotive systems of ore‑dressing treatment technological process management',
'01/31/1987', 4000, '"Avtomat"', '622.7-52', '622.7-52/P');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'C/C++ High‑level language programming',
'01/11/2007', 5500, '"Piter"', '681.3.06', '681.3.06(075)');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Computer networks. Conceptions, technologies, protocols',
'01/31/2006', 6000, '"Piter"', '004.72', '004.72(075)');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Directory of differential equations with 1‑st derivatives',
'01/31/2003', 10000, '"FIZMATLIT"', '517.9', '517.9');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Probability theory and mathematical statistics',
'01/31/2004', 1000, '"Piter"', '519.2', '519.2');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'C#. High‑level language programming',
'01/31/2009', 1500, '"Piter"', '004.43', '004.43');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Probability theory and mathematical statistics',
'01/31/2005', 2000, '"Vyshay shkola"', '519.2', '519.2');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Probability theory and mathematical statistics',
'01/31/2002', 2500, '"FIZMATLIT"', '519.2', '519.2');
INSERT INTO Books
(Code,
Name,
IssueYear, Drawing, BookPublishers, UDK, Cipher)
VALUES
(GEN_ID(BooksCode, 1),
'Discret‑batch methods of simple differential equations integration',
'01/31/1991', 7000, '"LIAN"', '517.9', '517.9-37');
/* We add 19 records to the CoAuthorship table. */
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(1, 1);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(2, 1);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(3, 2);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(4, 4);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(5, 5);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(6, 3);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(7, 6);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(8, 14);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(8, 15);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(9, 7);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(9, 8);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(10, 9);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(10, 10);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(10, 11);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(11, 6);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(12, 12);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(13, 13);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(14, 16);
INSERT INTO CoAuthorship
(BookCode, AuthorCode)
VALUES
(14, 7);
/* We add 25 records to the Phones table. */
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(1, 1, '29-XX-15');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(1, 2, '98-XX-88');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(1, 3, '38053198XX87');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(2, 2, '47-XX-10');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(3, 1, '68-XX-09');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(4, 1, '370-XX-20');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(4, 3, '38097567XX54');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(5, 1, '744-XX-00');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(6, 1, '33-XX-35');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(6, 3, '38096231XX83');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(8, 1, '68-XX-58');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(8, 2, '47-XX-45');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(8, 3, '38063257XX88');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(9, 1, '144-XX-48');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(9, 2, '32-XX-02');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(9, 3, '38097555XX22');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(10, 1, '56-XX-01');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(10, 2, '89-XX-98');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(10, 3, '38053456XX52');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(11, 1, '789-XX-97');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(11, 2, '47-XX-96');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(11, 3, '38054022XX84');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(12, 1, '777-XX-45');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(12, 2, '41-XX-39');
INSERT INTO Phones
(ReaderCode, PhoneTypesCode, PhoneNumber)
VALUES
(12, 3, '38067454XX21');
/* We add 15 records to the BookInventoryNumbers table. */
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 1, 1, 4567890, 15.56);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 2, 1, 4510000, 22.33);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 3, 1, 4532477, 34.01);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 4, 1, 4512890, 12.99);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 5, 2, 4678532, 56.78);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 6, 2, 4632112, 10.10);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 7, 2, 7569832, 73.50);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 7, 2, 5478956, 45.10);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 8, 2, 2145876, 59.25);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 9, 1, 5214786, 36.05);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 10, 1, 5268933, 74.20);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 11, 2, 7865890, 21.32);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 12, 1, 6589321, 36.69);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 13, 1, 7812639, 48.13);
INSERT INTO BookInventoryNumbers
(Code, BookCode, FundCode, InventoryNumber, Cost)
VALUES
(GEN_ID(BookInventoryNumbersCode, 1), 14, 1, 7523690, 27.99);
/* We add 9 records to the BookGiveOutRecord table. */
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 2, 4, 6, '09/11/2004',
'09/25/2004', '09/24/2004', 3);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 3, 4, 4, '09/02/2004',
'09/16/2004', '12/11/2004', 3);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 6, 4, 3, '09/02/2004',
'09/16/2004', '09/16/2004', 1);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 4, 3, 6, '10/30/2004',
'11/13/2004', '01/10/2005', 6);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 7, 10, 7, '11/10/2009',
'11/24/2009', '11/24/2009', 12);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 9, 7, 12, '12/15/2009',
'12/29/2009', NULL, NULL);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 11, 8, 10, '02/06/2009',
'02/20/2009', '02/19/2009', 7);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 7, 9, 8, '03/07/2009',
'03/21/2009', '04/10/2009', 10);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 9, 8, 12, '02/05/2010',
'02/28/2010', NULL, NULL);
INSERT INTO BookGiveOutRecord
(Code, ReaderCode, OutLibrarianCode, InventoryCode, IssueDate,
ReturnDate, FactReturnDate, InLibrarianCode)
VALUES
(GEN_ID(BookGiveOutRecordCode, 1), 12, 10, 15, '09/21/2010',
'10/05/2010', '10/03/2010', 9);
/* The COMMIT statement closes the transaction. All changes in Library database are committed. */
1. The script-file structure has the big differences with the description sequence of the Library physical model. It relations with the convenience presentation of code sections. Each section includes statements for defining one-type objects. For example: domines, tables, views and other.
2. Creating indexes of tables didn’t reflect in the Library physical model because they use for shortening time for data select with SQL-queries.
3. Script-file is a transaction that needs to end by the COMMIT statement for fixing of all changing in Library database.