APPENDIX C. Adding information to the database "LIBRARY"

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.

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:

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:

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:

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

 

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:

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.

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

 

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.

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.

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

 

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:

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:

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

 

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:

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

 

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:

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.

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

 

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:

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:

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

 

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.

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

 

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:

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

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

 

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:

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:

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

 

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:

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:

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