Main menu

EN | RU | UK

To Home Page

APPENDIX C. Adding information to the database "LIBRARY"

Helping & Consulting
Helping&Consulting
Contact me on
On top
Appendix C
Purpose

Purpose: to reveal the peculiarities of adding information to a relational database without violating the integrity of entities, referential integrity and corporate integrity constraints.

C.1. Sequence of filling tables

The designer must understand the sequence of entering information in the tables before creating the physical model of a relational database. Strictly speaking, it coincides the order of creating database tables. Follow this rule and you won't be wrong. But do not forget that for the complex databases can have a lot of options for the sequences of creating tables and adding information into them. You will need to choose the optimal variant for this.

The mechanisms for entity integrity, referential integrity, and corporate integrity constraints are defined at the physical modeling stage of a relational database. They impose a number of necessary restrictions on entering, updating and deleting information in the database tables. These restrictions determine the sequence of adding information in the tables. If everything was done correctly, then users will have no chance of bypassing these mechanisms.

Adding information in the educational database "LIBRARY" is a necessary stage of learning. The order of entering information into its tables coincides with the sequence of their creation. This approach will allow you to correctly enter information into the database even in the absence of several necessary restrictions. You will be asked to add them yourself during the practical lessons.

C.2. Table PhoneTypes ("PHONE TYPES")

The information input into the table PhoneTypes is restricted by entity integrity and corporate integrity constraints. The primary key Code is responsible for the entity integrity. This key was defined into the column Code. Corporate integrity constraints are enforced by the domains AllCode and AllNameType ( see the table PhoneTypes in the physical model database "LIBRARY").

We'll start physics database modeling by describing two types of mechanisms that provide unique values for primary keys. The first type is more common and understandable. It auto-increments a value in the key column of type INTEGER in a statement CREATE TABLE. The second type is implemented in DBMS Interbase. It defines a unique key value using a generator. It must be created using the statement CREATE GENERATOR. We did this for the primary key of the table PhoneTypes:

       CREATE GENERATOR PhoneTypesCode;

You need to set its initial value now. The DBMS Interbase uses the operator SET GENERATOR for this. Generators are usually setting to an initial value of zero. The statement defining the initial value for the generator PhoneTypesCode looks like this:

       SET GENERATOR PhoneTypesCode TO 

Both mechanisms generate a unique primary key value in the statement INSERT when it adds a new row to the table. The auto-increment mechanism does not need specifying of a key column to add a new row. The generator mechanism requires the use of the function GEN_ID() in every statement INSERT for a key column.

The function GEN_ID() sets the step of changing the generator value. The step can be changed for each new line if it is necessary. The statements INSERT with the function GEN_ID() to add information to the PhoneTypes are presented below:

INSERT TABLE PhoneTypes

    (Code, Name)

VALUES

    (GEN_ID(PhoneTypesCode, 1), 'Home');

INSERT TABLE PhoneTypes

    (Code, Name)

VALUES

    (GEN_ID(PhoneTypesCode, 1), 'Office');

INSERT TABLE PhoneTypes

    (Code, Name)

VALUES

    (GEN_ID(PhoneTypesCode, 1), 'Cellphone');

INSERT TABLE PhoneTypes

    (Code, Name)

VALUES

    (GEN_ID(PhoneTypesCode, 1), 'Satellite');

Four rows were added to the table PhoneTypes. This is a result of the sequential operation of four statements INSERT (Appendix D). Each time the function GEN_ID() sets the step of increasing the Code value by 1. The generator PhoneTypesCode was used for this. The initial value was 0 for it. Therefore, the value in the column Code in the first row of the table PhoneTypes is 1 (Table C.1).

Table C.1

Information in the table PhoneTypes ("PHONE TYPES")
Code Name
1 Home
2 Office
3 Cellphone
4 Satellite

 

C.3. Table PassportData ("PASSPORT DATA")

Adding information to the table PassportData has the following features. The statement CREATE TABLE for this table does not have a primary key defined for the column Code. However, it is necessary to ensure the uniqueness of the values in this column for the entity integrity "Passport" and the database "LIBRARY" referential integrity. The generator PassportDataCode will be created for this:

CREATE GENERATOR PassportDataCode;

SET GENERATOR PassportDataCode TO 

We discussed the principle of operation of generators earlier. Corporate integrity constraints on the PassportData table was described in the database "LIBRARY" physical model. The example of INSERT statements of adding the first two rows to the table PassportData are presented below.

INSERT TABLE PassportData

    (Code, Series, Number, Birthday, BirthPlace, Sex, IssuePlace, IssueDate)

VALUES

    (GEN_ID(PassportDataCode, 1), 'AA', 45003, '05/30/1930', 'Russia, Opochki city', 'F', 'Dniepropetrovsk', '01/12/1995');

INSERT TABLE PassportData

    (Code, Series, Number, Birthday, BirthPlace, Sex, IssuePlace, IssueDate)

VALUES

    (GEN_ID(PassportDataCode, 1), 'AA', 15700, '02/23/1930', 'Russia, Vladymir city', 'F', 'Zhitomir', '03/16/2000');

A total of 24 rows have been added to the table PasportData (Appendix D). All added information is shown in the Table C.2.

Table C.2

Information in the table PasportData ("PASSPORT DATA")
Code Series Number Birthday Birthplace Sex Issueplace Issuedate Note
1 AA 45003 30.05.1930 Russia, Opochki city F Dniepropetrovsk 12.01.1995 NULL
2 AA 15700 23.02.1930 Russia, Vladymir city F Zhitomir 16.03.2000 NULL
3 AB 87134 20.01.1963 Dniepropetrovsk region, Solenoe settlement F Dniepropetrovsk region, Solenoe settlement 10.01.1998 NULL
4 AE 12300 12.11.1960 Ukraine, Donetsk city M Donetsk 15.12.1991 NULL
5 AE 01067 19.07.1981 Ukraine, Dniepropetrovsk M Dniepropetrovsk 25.08.1997 NULL
6 AG 01568 14.09.1956 Kazakhstan, Pavlodar city M Kiev 24.05.1999 NULL
7 AZ 43188 13.11.1970 Dniepropetrovsk region, Dnieprodzerzhinsk city F Dniepropetrovsk region, Dnieprodzerzhinsk city 15.05.1998 NULL
8 AK 23490 05.01.1961 Russia, Samara city F Dniepropetrovsk 13.09.2000 NULL
9 AC 90843 10.10.1949 Moldova, Kishenev city F Dniepropetrovsk 13.12.1998 NULL
10 AY 90764 14.11.1950 Ukraine, Nikolaev city F Nikolaev city 11.11.1998 NULL
11 IK 10842 19.07.1949 Ukraine, Kirovograd city M Dniepropetrovsk 06.01.1998 NULL
12 IK 45190 18.07.1983 Dniepropetrovsk region, Petropavlovka settlement F Dniepropetrovsk region, Petropavlovka settlement 20.09.1999 NULL
13 AH 61327 01.10.1960 Russia , St. Petersburg F St. Petersburg 12.10.1976 NULL
14 AH 64277 23.12.1972 Ukraine, Lvov city M Lvov 06.01.1988 NULL
15 AK 89125 07.05.1980 Ukraine, Kiev city M Kiev 10.01.1998 NULL
16 AK 55706 07.04.1965 Donetsk F Donetsk 20.04.1982 NULL
17 AC 73271 05.07.1950 Crimea M Simferopol 23.08.1970 NULL
18 AG 45879 04.02.1961 Dniepropetrovsk F Dnieprodzerzhinsk 14.03.1980 NULL
19 AC 12548 08.04.1974 Truskavets F Prikarpatie 28.05.1989 NULL
20 AK 12578 11.11.1987 Donetsk, Kramatorsk F Kiev 26.01.2000 NULL
21 AC 55489 25.09.1981 Sumy F Charkov 06.11.1999 NULL
22 AY 45789 07.08.1972 Hungary F Ivano-Frankovsk 03.10.1988 NULL
23 AG 35126 18.03.1975 Odessa F Odessa 19.06.1993 NULL
24 AH 15625 19.06.1966 Dniepropetrovsk M Dniepropetrovsk, Petrikovka 12.08.1982 NULL

 

C.4. Table Librarians ("LIBRARIANS")

The table Librarians entity integrity is ensured by the primary key on the column Code. The generator LibrariansCode is taking the responsibility for the uniqueness values of this key.

CREATE GENERATOR LibrariansCode;

SET GENERATOR LibrariansCode TO 

Generators are described for the table PhoneTypes. The corporate integrity constraints of the table Librarians are enforced by domains. Their complete list is given in the database "LIBRARY" physical model (Appendix B).

Referential integrity mechanisms are not implemented for the table Librarians. This is done on purpose. Do not forget that the database "LIBRARY" is designed for teaching. If you consider yourself an expert, then try adding them to the script-file yourself. Start with foreign keys. But it will be most useful to do this using triggers and stored procedures. It remains to add that the script-file implements such mechanisms for the table, which is very similar to the table Librarians. Try to answer the question: which table is this?

The statements INSERT to add the first two rows to the table Librarians are shown below.

INSERT TABLE Librarians

    (Code, ClockNumber, FamilyName, Name, Patronymic, PassportCode, Post, HomePhone)

VALUES

    (GEN_ID(LibrariansCode, 1), 28, 'Ivanova', 'Elena', 'Vladimirovna', 2, 'Librarian', '52‑XX‑75');

INSERT TABLE Librarians

    (Code, ClockNumber, FamilyName, Name, Patronymic, PassportCode, Post, HomePhone)

VALUES

    (GEN_ID(LibrariansCode, 1), 12, 'Nikolaenko', 'Lubov', 'Nikolaevna', 10, 'Librarian', '46‑XX‑19');

A total of 12 rows have been added to the table Librarians (Appendix D). These are shown in the Table C.3 below.

Table C.3

Information in the table Librarians ("LIBRARIANS")
Code ClockNumber FamilyName Name Patronymic PasportCode Post HomePhone Note
1 28 Ivanova Elena Vladimirovna 2 Librarian 52-XX-75 NULL
2 12 Nikolaenko Lubov Nikolaevna 10 Librarian 46-XX-19 NULL
3 187 Inozemtseva Ivanna Modestovna 9 Senior librarian 775-XX-00 NULL
4 83 Malzeva Diana Petrovna 12 Librarian 29-XX-15 NULL
5 10 Sizranzeva Tatyana Igorevna 3 Librarian 370-XX-22 NULL
6 100 Stavka Lilia Ivanovna 7 Librarian 22-XX-01 NULL
7 50 Leshenko Alla Fedorovna 13 Librarian 722-XX-36 NULL
8 36 Sira Lidia Ivanovna 19 Librarian 254-XX-02 NULL
9 45 Prokhina Tamara Lvovna 21 Librarian 63-XX-01 NULL
10 78 Samilenko Viktoria Igorevna 20 Librarian 125-XX-80 NULL
11 69 Stepanova Aleksandra Nikolaevna 16 Senior librarian 445-XX-65 NULL
12 17 Petrova Alina Sergeevna 23 Librarian 999-XX-05 NULL

 

C.5. Table Readers ("READERS")

In the educational database "LIBRARY" the table Readers has the richest instrument set of data manipulation (Appendix D). It is the basis of a more complex structural unit. It is the directory "Readers". We say of this notion for first time.

Let's give a general definition. A directory is one table or table set resulting from the normalization of the original relationship. Then the directory "Readers" is the set of tables obtained because of initial relationship "READERS" normalization (the lecture "The normalization of relationships during the DB designing", Fig. 3.1).

The directory "Readers" structure is clearly seen on the link diagram between the relationships of the database "LIBRARY". It includes set of tables. It is built based on the following relationships: "READERS", "PASSPORT DATA", "PHONES" and "PHONE TYPES". We will only talk about the table Readers ("READERS") now.

The column Code is not defined as the table Readers primary key. But the entity integrity in it and the database "LIBRARY" referential integrity is ensured by the uniqueness of the values in this column. The ReadersCode generator is responsible for this:

CREATE GENERATOR ReadersCode;

SET GENERATOR ReadersCode TO 

The generator mechanism operation is described above. It is used in statements INSERT to add new rows to the table Readers. An example of operators for adding the first two rows of the table is shown below:

INSERT TABLE Readers

    (Code, FamilyName, Name, Patronymic, ReaderCardNumber, PassportCode, Job, Post)

VALUES

    (GEN_ID(ReadersCode, 1), 'Ivanov', 'Petr', 'Ivanovich', 317, 4, 'NMU, CM dep.', 'Assistant');

INSERT TABLE Readers

    (Code, FamilyName, Name, Patronymic, ReaderCardNumber, PassportCode, Job, Post)

VALUES

    (GEN_ID(ReadersCode, 1), 'Fedorez', 'Irina', 'Olegovna', 28, 1, 'NMU, AEC', 'Front-door security');

A total of 12 rows have been added to the table Readers (Appendix D). You can see them in the Table C.4.

Table C.4

Information in the table Readers ("READERS")
Code FamilyName Name Patronymic ReaderCardNumber PasportCode Job Post Note
1 Ivanov Petr Ivanovich 317 4 NMU, CM dep. Assistant NULL
2 Fedorez Irina Olegovna 28 1 NMU, AEC Front-door security NULL
3 Ilin Ivan Petrovich 1345 11 NMU, physics dep. Associate professor NULL
4 Surenko Dmitry Pavlovich 543 6 NMU, geophysicist dep. Senior professor NULL
5 Korshunova Natalia Yurievna 128 8 NMU, geo-informatics dep. Assistant NULL
6 Nosenko Oleg Vladimirovitch 5672 5 NMU, ICC Engineer NULL
7 Brusov Vladimir Mikhajlovitch 485 24 NMU, geodesy dep. Laboratory assistant NULL
8 Kozirev Alexey Sergeevich 759 15 NMU, criminology dep. Professor NULL
9 Levchenko Julia Pavlovna 146 18 NMU, political theory dep. Head of department NULL
10 Svetlaya Tatyana Ivanovna 2021 22 NMU, translation dep. Senior professor NULL
11 Sheglov Petr Yevgenievich 997 14 NMU, power supply dep. Assistant NULL
12 Kirilenko Victor Alexandrovich 1010 17 NMU, electric drive dep. Deputy dean NULL

 

C.6. Table Phones ("PHONES")

The table Phones has a significant difference from all the tables that were discussed earlier. A composite candidate key was determined for it at the logical modeling stage. The uniqueness of the composite candidate key values ensures the entity integrity in the table Phones. This is the phone number of the reader (Appendix A).

The generators mechanism is not fit for maintaining the uniqueness of composite candidate or primary key values. Instead of it the triggers mechanism works here (Appendix D). Therefore, you will not see the functions GEN_ID() in the statements INSERT for adding new rows in the table Phones. See below adding of the first two rows:

INSERT TABLE Phones

    (ReaderCode, PhoneTypesCode, PhoneNumber)

VALUES

    (1, 1, '29-XX-15');

INSERT TABLE Phones

    (ReaderCode, PhoneTypesCode, PhoneNumber)

VALUES

    (1, 2, '98-XX-88');

A total of 25 lines have been added to table Phones (Appendix D). These are shown in the Table C.5 below.

Table C.5

Information in the table Phones ("PHONES")
ReaderCode PhoneTypeCode PhoneNumber
1 1 29-XX-15
1 2 98-XX-88
1 3 38053198XX87
2 3 47-XX-10
3 1 68-XX-09
4 1 370-XX-20
4 3 38097567XX54
5 1 744-XX-00
6 1 33-XX-35
6 3 38096231XX83
8 1 68-XX-58
8 2 47-XX-45
8 3 38063257XX88
9 1 144-XX-48
9 2 32-XX-02
9 3 38097555XX22
10 1 56-XX-01
10 2 89-XX-98
10 3 38053456XX52
11 1 789-XX-97
11 2 47-XX-96
11 3 38054022XX84
12 1 777-XX-45
12 2 41-XX-39
12 3 38067454XX21

 

C.7. Table Books ("BOOKS")

The entity integrity in the table Books is maintained by the primary key on the column Code. It is also maintaining for the database "LIBRARY" referential integrity. The generator BooksCode is providing for the uniqueness of values in the Code column:

CREATE GENERATOR BooksCode;

SET GENERATOR BooksCode TO 

The work of generators is described using the example of the table PhoneTypes. The statements INSERT for adding the first two records to the table Books are presented below.

INSERT TABLE Books

    (Code, Name, IssueYear, Drawing, BookPublishers, UDK, Cipher)

VALUES

    (GEN_ID(BooksCode, 1), 'Automation of industrial processes on concentration plant', '01/01/1985', 60000, "Nadra", '622.7', '622.7‑52 ⁄T');

INSERT TABLE Books

    (Code, Name, IssueYear, Drawing, BookPublishers, UDK, Cipher)

VALUES

    (GEN_ID(BooksCode, 1), 'Concentration and metallurgy processes automation problem solving', '01/01/1969', 200000, "Nauka", '622.7‑52', '622.7‑52(075) ⁄T');

A total of 14 rows were added to the table Books using statements INSERT in the script-file (Appendix D). See the result of their work below (Table C.6).

Table C.6

Information in the table Books ("BOOKS")
Code Name IssueYear Drawing BookPublishers UDK Cipher Note
1 Automation of industrial processes on concentration plant 01.01.1985 6000 "Nadra" NULL 622.7-52/T NULL
2 Concentration and metallurgy processes automation problem solving 01.01.1969 2000 "Nauka" NULL 622.7-52(075)/T NULL
3 Asymptotic methods of optimal management 01.01.1987 1000 "Avtomat" NULL 681.513.5:/A NULL
4 Optimum automotive systems synthesis 01.31.1984 5000 "Avtomat" NULL 681.513.5:/DO NULL
5 Stochastic systems optimization methods 01.01.1987 4500 "Matstat" NULL 681.513.5:/DO NULL
6 Automotive systems of ore-dressing treatment technological process management 31.01.1987 4000 "Avtomat" NULL 622.7-52/P NULL
7 C/C++ High-level language programming 11.01.2007 5500 "Piter" NULL 681.3.06(075) NULL
8 Computer networks. Conceptions, technologies, protocols 31.01.2006 6000 "Piter" NULL 004.72(075) NULL
9 Directory of differential equations with 1-st derivatives 31.01.2003 10000 "FIZMATLIT" NULL 517.9 NULL
10 Probability theory and mathematical statistics 31.01.2004 1000 "Piter" NULL 519.2 NULL
11 C#. High-level language programming 31.01.2009 1500 "Piter" NULL 004.43 NULL
12 Probability theory and mathematical statistics 31.01.2005 2000 "Vyshay shkola" NULL 519.2 NULL
13 Probability theory and mathematical statistics 31.01.2002 2500 "FIZMATLIT" NULL 519.2 NULL
14 Discret-batch methods of simple differential equations integration 31.01.1991 7000 "LIAN" NULL 517.9-37 NULL

 

C.8. Table BookAuthors ("BOOKS AUTHORS")

The primary key on the column Code is responsible for the entities integrity in the BookAuthors table and for the database "LIBRARY" referential integrity. The uniqueness of the primary key values is maintained by the generator BookAuthorsCode:

CREATE GENERATOR BookAuthorsCode;

SET GENERATOR BookAuthorsCode TO 

The generator mechanism work is described using the table PhoneTypes as an example. The statements INSERT for adding of the first two rows to the table BookAuthors are presented below:

INSERT TABLE BookAuthors

    (Code, FamilyName, Name, Patronymic, Birthday)

VALUES

    (GEN_ID(BookAuthorsCode, 1), 'Tikhonov', 'Oleg', 'Nikolaevich', '01/31/1940');

INSERT TABLE BookAuthors

    (Code, FamilyName, Name, Patronymic, Birthday)

VALUES

    (GEN_ID(BookAuthorsCode, 1), 'Akulenko', 'Leonid', 'Dmitrievich', '03/12/1934');

The BookAuthors table contains 16 rows. All of them are formed in the script-file by statements INSERT (Appendix D). The results of their work are shown in the Table C.7.

Table C.7

Information in the table BookAuthors ("BOOKS AUTHORS")
Code FamilyName Name Patronymic Birthday DeatheDay ShortBiography Note
1 Tikhonov Oleg Nikolaevich 12.07.1945 NULL NULL NULL
2 Akulenko Leonid Dmitrievich 26.11.1962 NULL NULL NULL
3 Prozuto Viktor Sergeevich 14.08.1950 NULL NULL NULL
4 Kolosov Gennadiy Yevgenievich 01.06.1950 NULL NULL NULL
5 Kozakov Igor Yeleseevich 18.09.1962 NULL NULL NULL
6 Pavlovskaya Tatyana Alexandrovna 03.08.1967 NULL NULL NULL
7 Zaytsev Valentin Fedorovich 03.07.1955 NULL NULL NULL
8 Polianin Andrey Dmitrievich 15.03.1939 NULL NULL NULL
9 Andronov Alexandr Mikhajlovitch 18.06.1969 NULL NULL NULL
10 Kopytov Evgeniy Alexandrovich 09.03.1950 NULL NULL NULL
11 Gringlaz Leonid Yakovlievich 11.07.1950 NULL NULL NULL
12 Bavrin Ivan Ivanovich 12.09.1949 NULL NULL NULL
13 Pugachev Vladymyr Semenovich 03.02.1958 NULL NULL NULL
14 Olifer Victor Grigorievich 07.04.1961 NULL NULL NULL
15 Olifer Natalia Alexandrovna 11.12.1965 NULL NULL NULL
16 Flegontov Alexandr Vladimirovitch 07.07.1963 NULL NULL NULL

 

C.9. Table CoAuthorship ("CO-AUTHORSHIP")

The database "LIBRARY" physical model was defined a composite primary key for the table CoAuthorship. It includes all its columns. As you already know, generators are not a suitable mechanism for maintaining the uniqueness of composite candidate or primary key values. This requires the use of triggers and stored procedures.

The script-file does not contain triggers and stored procedures to provide unique values for the potential key of the table CoAuthorship. This approach is possible. It is based on the internal mechanisms of the DBMS. They call an exception when trying to enter a non-unique value for the primary key. The statements INSERT for adding the first three rows to the table CoAuthorship are presented below.

INSERT TABLE CoAuthorship

    (BookCode, AuthorCode)

VALUES

    (1, 1);

INSERT TABLE CoAuthorship

    (BookCode, AuthorCode)

VALUES

    (2, 1);

INSERT TABLE CoAuthorship

    (BookCode, AuthorCode)

VALUES

    (3, 2);

The consistency of information adding in the table CoAuthorship have mattered. Let's consider it on the example of the third line. The values of the foreign keys BookCode and AuthorCode are equal respectively to 3 and 2 in this row. This means that the table Books must have a row with the primary key Code value equaling 3. Additionally, the BookAuthors table must have a row with the of the primary key Code value equaling is 2. Otherwise, the referential integrity rules of the database "LIBRARY" will generate an exceptional situation using the standard mechanisms of the DBMS Interbase. In this case, the third row will not be added to the table CoAuthorship.

A total of 19 lines are added in the script-file to the table CoAuthorship (Appendix D). A separate statement INSERT has been created to add each row. You can see the results of their work in the Table C.8.

Table C.8

Information in the table Сoаuthorship ("CO-AUTHORSHIP")
BookCode AuthorCode
1 1
2 1
3 2
4 4
5 5
6 3
7 6
8 14
8 15
9 7
9 8
10 9
10 10
10 11
11 6
12 12
13 13
14 16
14 7

 

C.10. Table BookFunds ("BOOKS FUNDS TYPES")

The entity integrity in the BookFunds table is ensured by the values uniqueness in the column Code. It is chosen as the primary key. The generator BookFundsCode provides unique key values:

CREATE GENERATOR BookFundsCode;

SET GENERATOR BookFundsCode TO 

The operation of the generators work is described above. The statements INSERT adding of rows to the table BookFundsCode are presented below:

INSERT TABLE BookFunds

    (Code, Name)

VALUES

    (GEN_ID(BookFundsCode, 1), 'STL');

INSERT TABLE BookFunds

    (Code, Name)

VALUES

    (GEN_ID(BookFundsCode, 1), 'Students');

There are only two rows in the table BookFundsCode. Therefore, you can see all the statements INSERT from the script-file that add information to it. The results of their work are shown in the Table C.9.

Table C.9

Information in the table BookFunds ("BOOKS FUNDS TYPES")
Code Name
1 STL
2 Students

 

C.11. Table BookInventoryNumbers ("BOOKS INVENTORY NUMBERS")

The entity integrity in the table BookInventoryNumbers is enforced by the primary key on the column Code. The generator BookInventoryNumbersCode creates unique values for this primary key:

CREATE GENERATOR BookInventoryNumbersCode;

SET GENERATOR BookInventoryNumbersCode TO 

The generators work mechanism is described above. It remains to give an example of statements INSERT that enter the first two rows into the table BookInventoryNumbers:

INSERT TABLE BookInventoryNumbers

    (Code, BookCode, FundCode, InventoryNumber, Cost)

VALUES

    (GEN_ID(BookInventoryNumbersCode, 1), 1, 1, 4567890, 15.56);

INSERT TABLE BookInventoryNumbers

    (Code, BookCode, FundCode, InventoryNumber, Cost)

VALUES

    (GEN_ID(BookInventoryNumbersCode, 1), 2, 1, 4510000, 22.33);

Let's look at the sequence of adding information to the second row of the table BookInventoryNumbers. The foreign key values are important here: BookCode = 2 and FundCode = 1. We will not be able to enter the second row in the table BookInventoryNumbers without the corresponding rows in the tables Books and BookFunds.

The primary key is defined on the column Code in both tables. They are referenced by the foreign keys of the table BookInventoryNumbers. Therefore, you first need to add a row in the table Books where Code = 2, and then add a row to the table FundCode where Code = 1. After that you can add a second row to the table BookInventoryNumbers.

The script-file adds 15 rows to the table BookInventoryNumbers. A statement separate INSERT has been created to add each row (Appendix D). You can see the results of their work in the Table B.10.

Table C.10

Information in the table BookInventoryNumbers ("BOOKS INVENTORY NUMBERS")
Code BookCode FundCode InventoryNumber Cost
1 1 1 4567890 15,56
2 2 1 4510000 22,33
3 3 1 4532477 34,01
4 4 1 4512890 12,99
5 5 2 4678532 56,78
6 6 2 4632112 10,10
7 7 2 7569832 73,50
8 7 2 5478956 45,10
9 8 2 2145876 59,25
10 9 1 5214786 36,05
11 10 1 5268933 74,20
12 11 2 7865890 21,32
13 12 1 6589321 36,69
14 13 1 7812639 48,13
15 14 1 7523690 27,99

 

C.12. Table BookGiveOutRecord ("BOOKS REGISTRATION")

The entity "Book inventory number" integrity is needed to maintain in the table BookGiveOutRecord. The primary key Code is responsible for this. The uniqueness of its values is provided by the generator BookGiveOutRecordCode:

CREATE GENERATOR BookGiveOutRecordCode;

SET GENERATOR BookGiveOutRecordCode TO 

How generators work is described earlier for the table PhoneTypes. The statements INSERT adding of the first two rows to the table BookGiveOutRecord are presented below:

INSERT TABLE 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 TABLE 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);

The information was added to the table BookGiveOutRecord last. Try to estimate the scale of preparation for it. Ten rows were entered into the table BookGiveOutRecord for fixing of ten cases of issuing books to readers. One hundred three rows were added into the ten tables before that.

Let's understand the order of adding information to the table BookGiveOutRecord using the first line as an example. We can see that the foreign keys values OutLibrianCode, InventoryCode, InLibrianCode and the ReaderCode column equal respectively 2, 3, 6 and 4. Therefore, we must perform several actions before entering the first line. Their sequence is arbitrary.

We'll start entering the first row into the table BookGiveOutRecord with adding information into the table BookInventoryNumbers. It is necessary to enter a row in which the primary key Code value is 6 into this table. It's the turn of entering information into the table Librarians. Two foreign keys of the table BookGiveOutRecord reference on primary key Code of the table Librarians. Therefore, we need to enter two rows into this table. The value of the primary key Code should be equal to 4 in the first row and its value in the other should be equal to 3. A row with the primary key Code value equal 2 should appear in the table Readers. The rules of database "LIBRARY" referential integrity will allow us to enter the first row to the table BookGiveOutRecord only after all these actions.

A separate statement INSERT for adding each row to the BookGiveOutRecord table was created (Appendix D). You can see the results of their work in the Table C.11.

Table C.11

Information in the table BookGiveOutRecord ("BOOKS REGISTRATION")
Code ReaderCode OutLlibrarianCode InventaryCode IssueDate ReturnDate FactreturnDate InLibrarianCode
1 2 4 6 11.09.2004 25.09.2004 24.09.2004 3
2 3 4 4 02.09.2004 16.09.2004 11.12.2004 3
3 6 4 3 02.09.2004 16.09.2004 16.09.2004 1
4 4 3 6 30.10.2004 13.11.2004 10.01.2005 6
5 7 10 7 10.11.2009 24.11.2009 24.11.2009 12
6 9 7 12 15.12.2009 29.12.2009 NULL NULL
7 11 8 10 06.02.2009 20.02.2009 19.02.2009 7
8 7 9 8 07.03.2009 21.03.2009 10.04.2009 10
9 9 8 12 05.02.2010 28.02.2010 NULL NULL
10 12 10 15 21.09.2010 05.10.2010 03.10.2010 9

 

Conclusions

1. Entity integrity and referential integrity of a relational database is maintained through unique primary key values in tables, even if they are not explicitly defined in statements INSERT.

2. In modern DBMS, the integer values uniqueness of non-composite primary and potential keys is maintained using either generators or the auto-incrementing property of the table column.

3. Triggers and stored procedures must be used to maintain the uniqueness of the composite candidate and primary key values.

4. The order of adding information in the tables always may coincide with the sequence of their creation. However, this is not always the optimal way to add information to relational database tables.

© Yaroslav Kuvaiev, 2005—2023.

All rights reserved.

All information placed on this Web site is designed for internal use only. Its reproduction or distribution in any form is prohibited without the written permission from Author.