APPENDIX B. Physical model of the database "LIBRARY"

The physical model of the database is created after the choice of the Database Management System (DBMS). Dear site visitors, it doesn't matter which relational database management system you will be using in your professional career. The course offered to your attention was initially aimed at studying the organization of relational databases. Therefore, the main criterion for choosing the DBMS Interbase was the minimum time to master it.

What to do if you have chosen another relational DBMS for learning the course? This will not change the essence of the physical modeling process. The database "LIBRARY" physical model will differ only in the data types of your DBMS. Here are the data types of Interbase DBMS. Find their analogs in your DBMS and make changes to the physical model. Please note that your DBMS may not support domains. The corresponding explanations are given in the paragraph B.3.

In the lecture, you can read general recommendations about the order of creating tables in relational databases. There is also an example of the order in which tables are created in the database "LIBRARY". It differs from the one given in the physical model (table B.1). Remember that there are many ways to achieve a goal. You choose them.

Table B.1

 

There is a similarity in the describing tables sequence in the logical and physical models of the database "LIBRARY". Both models can be divided into two parts. First, tables with information about readers and librarians are described. Then tables with information about books are described. The table with information on the process of accepting and giving out books is described last. However, the order of describing tables within the first and second parts is different for the logical and physical models.

The physical modeling of a relational database begins with its creation. You must use the CREATE DATABASE statement for this. It will have the following format to create the database "LIBRARY":

The operator places the database in the C:/DB/ directory. You must create it before executing the CREATE DATABASE statement. The database is created for a user with unlimited rights. Such a user is by default in any DBMS. We made it for an easy acquaintance with the relational databases organization. Our project is educational. DO NEVER THIS IN A REAL PROJECT.

The CREATE DATABASE statement will create a LIBRARY.GDB file in the C:/DB/ directory. You need to connect to the LIBRARY.GDB database to create physical model objects. Use the CONNECT operator for this:

Pay your attention again to the fact that the LIBRARY.GDB database is educational. We will work with it under the Interbase DBMS administrator name. The information of other users' access organization to relational databases is in the section "Database unauthorized access security".

This part of the relational database physical modeling is for those who have chosen a DBMS that supports domains. If your DBMS does not support them, then you should find out what you gave up.

Domains are defined based on standard data types. It is a user-defined data type from a programming point of view. Them are an important mechanism for maintaining the semantic integrity of a relational database. A domain gives the defined semantic meaning to a relationship attribute. This will avoid comparisons and other operations between data that have different semantic meaning.

The CREATE DOMAIN statement in the Interbase DBMS gives a wide range of opportunities for defining the relationship attributes properties. It allows you to assign a unique name to a standard data type. This is exactly what makes the semantic meaning of the relationship attribute unique. You can define a default value for a domain and specify a condition for checking it. This list can always be clarified in the DBMS documentation. It can be wider or narrower.

There is one more undeniable advantage of defining data types using domains. It is difficult to overestimate it. For example, all the attributes that store the surname, first name, or patronymic of the reader, librarian or author of the book in the database "LIBRARY" are defined on the FIO domain (table B.2). Here is the corresponding statement CREATE DOMAIN. Nine attributes were defined in three relationships on this domain. Thirty characters are allocated for each of these values. You can change their number or other properties of these attributes with a single statement ALTER DOMAIN. Such cases occur quite often during the relational databases maintenance.

Table B.2

 

The first part of the physical model, like the first part of the logical model of the database "LIBRARY" includes tables with information about readers and librarians. We start working on the database "LIBRARY" physical model by creating domains for the PhoneTypes table (table B.1).

Attributes of the "PHONE TYPES" relationship of the database "LIBRARY" logical model are defined in its physical model on the AllCode and AllNameType domains. Interbase 2020 DBMS uses the following operators to create them:

The argument NOT NULL eliminates the data absence in the any relationship attributes that are defined the domains AllCode and AllNameType in the physical model. Here you can see other possibilities of the statement CREATE DOMAIN in the DBMS Interbase.

The specification of any logical model relationship is implemented in the physical model using the statement CREATE TABLE. For the relationship "PHONE TYPES" in the DBMS Interbase 2020, it has the following format:

If the DBMS does not support domains, then the statement CREATE TABLE will have the following format:

Here you can see other possibilities of the statement CREATE TABLE in the DBMS Interbase. Find this operator in the DBMS documentation you have chosen for your professional activity. It will have a lot in common with the statement CREATE TABLE of the DBMS Interbase.

Please note that the table PhoneTypes completely corresponds to the relationship "PHONE TYPES" specification (table B.3).

Table B.3

 

The relationship "PASSPORT DATA" attributes are defined in the database "LIBRARY" physical model on the following domains: AllCode, AllSeries, AllNumber, AllPlace, AllSex, AllIssuePlace and AllNote. The domain AllCode was defined using the statement CREATE DOMAIN before the table PhoneTypes. The rest of the domains must be created.

All domains except AllSex and AllNote have an argument NOT NULL. It prohibits missing data in all columns of the table PassportData that are defined on the AllCode, AllSeries, AllNumber, AllPlace, and AllIssuePlace domains. We already wrote about this for the domains of the table PhoneTypes.

In fact, the domain AllSex also does not allow for an empty value. Its argument CHECK(VALUE IN ('M', 'F')) precisely indicates the set of allowed values. Thus, only the domain AllNote allows entering no values. Recall that the absence of a value in a relational DBMS table cell is denoted by the special determinant NULL.

The argument CHECK(VALUE >= 10) of the domain AllNumber defines the left end of its integer values range. The right end of the range is determined by the maximum value of the data type INTEGER of the Interbase DBMS. Usually, such things are determined by the Customer, based on the business processes specifics.

After defining all the required domains, we can create the table PasportData:

The attribute Code is the primary key of the relationship "PASSPORT DATA" in the database "LIBRARY" logical model. It is absent in the physical model of the table PasportData (table B.4). Instead, the triggers BfrInsInPasportData and BfrUpdInPasportData are defined in the database "LIBRARY". They ensure the Passport entity integrity by eliminating the addition of data about two different documents with the same values for the primary key Code and the compound potential key Series, Number. You will know the details after publishing the SCRIPT-file that implements the physical model of the database "LIBRARY" in the Interbase DBMS.

Table B.4

 

The relationship "LIBRARIANS" attributes are defined in the database "LIBRARY" physical model on the following domains: AllCode, AllClockNumber, FIO, AllPost, AllHomePhone and AllNote. The AllCode and AllNote domains were previously defined. We need to define the rest of the domains before creating the table Librarian.

The attributes NOT NULL and CHECK(VALUE >= 10) of the statement CREATE DOMAIN were previously described (see the relationships PhoneTypes and PasportData). Now all the domains of the relationship "LIBRARIANS" attributes are defined, and we can create the table Librarians.

The foreign key "Passport сode" is defined in the relationship "LIBRARIANS" specification (see the logical model). It is not found in the table Librarians (see the statement CREATE TABLE Librarians and table B.5). This is linked to the fact that the database "LIBRARY" referential integrity between the Librarians and PasportData tables is provided using triggers. This mechanism is more flexible than the primary and foreign key mechanism. Simultaneous operation of these two mechanisms is impossible. More details about this will be discussed after the publication of the practical lessons.

Table B.5

 

The relationship "READERS" attributes are defined in the database "LIBRARY" physical model on the following domains: AllCode, FIO, AllReaderCardNumber, AllJob, AllPost and AllNote. The domains AllCode, AllNote, AllPost, and FIO are previously defined before the tables PhoneTypes, PasportData, and Librarians. We must define the domains AllReaderCardNumber and AllJob before creating the table Readers.

The purpose of the arguments NOT NULL and CHECK(VALUE> = 10) is described earlier (see the tables PhoneTypes and PasportData). Recall that the source of such restrictions is the result of the business processes analysis.

We can now create the table Readers:

The relationship "READERS" specification with the primary and foreign keys was defined in the database "LIBRARY" logical model. These keys are not defined in the Readers table physical model (table B.6). This is linked to the fact that it was decided to maintain the entities integrity in the table Readers using triggers at the stage of physical modeling. Triggers are also used to maintain the referential integrity between the table Readers and other tables in the database “LIBRARY”. More details on the use of triggers will be considered after the publication of practical lessons.

Table B.6

 

The table Phones completes the first part of the database "LIBRARY" physical model. It includes tables with information about readers and librarians. Recall that we started the first part of the physical model by creating the domains for the table PhoneTypes columns.

The relationship "PHONES" attributes are defined the domains AllCode and AllHomePhone in the database "LIBRARY" physical model. The domain AllCode was created before the table PhoneTypes. And the domain AllHomePhone we defined before the table Librarians. Now we can create the table Phones:

The relationship "PHONES" specification was defined with two foreign keys in the database "LIBRARY" logical model. These keys are not defined for the table Phones in the physical model (table B.7). Instead, triggers maintain referential integrity between the Readers and Phones, PhoneTypes and Phones tables in the database “LIBRARY”. You will learn more about this after the publication of the practical lessons.

Table B.7

 

The second part of the database "LIBRARY" physical model begins with the domains creation for the relationship "BOOKS" attributes. It includes tables with information about the books that are stored in the library.

The relationship "BOOKS" attributes are defined on the domains AllCode, BookName, AllDrawing, AllUDK, AllCipher, and AllNote. The domain AllCode was previously created before the table PhoneTypes. The domain AllNote was defined before creating the table PasportData. The rest of the domains must be created.

The argument NOT NULL disallows missing values (see the table PhoneTypes). The argument CHECK(VALUE >= 10) essence was described when creating the domain AllNumber before the table PasportData. It shouldn't surprise you that two domains with different names have the same properties. These domains have different semantic meanings.

All the objects required to create the table Books are defined. Let's create it using the statement CREATE TABLE:

The table Books completely corresponds to the relationship “BOOKS” specification in the database "LIBRARY" logical model (table B.8).

Table B.8

 

The relationship "BOOKS AUTHORS" continues the implementation of the second part of the database "LIBRARY" physical model. The relationship attributes are defined on just three domains: AllCode, FIO, and AllNote. They were all defined above. Therefore, we can immediately create the table BookAuthors using the statement CREATE TABLE:

The table BookAuthors exactly matches with the of the relationship "BOOKS AUTHORS" specification in the database "LIBRARY" logical model (table B.9).

Table B.9

 

The relationship "CO-AUTHORSHIP" attributes are defined on only one domain: AllCode. It was previously defined before the table PhoneTypes. Now, we can immediately add the table CoAuthorship to our physical model of the database "LIBRARY" using the statement CREATE TABLE:

Let's take a closer look at the argument FOREIGN KEY of the statement CREATE TABLE CoAuthorship. The argument syntax is structured so that you could clearly see the primary key and the table on which each foreign key of the table CoAuthorship is linking. The foreign key AuthorCode is related to the relationship BookAuthors primary key. The foreign key BookCode is bound to the relationship Books primary key. The arguments ON DELETE CASCADE and ON UPDATE CASCADE define the mechanism actions for maintaining the database "LIBRARY" referential integrity. Let's describe how it works using the example of the foreign key AuthorCode.

For the foreign key AuthorCode, the argument ON DELETE CASCADE means that deleting a row in the table BookAuthors will delete a set of rows in the table CoAuthorship. The values in the column AuthorCode for these rows are equal to the value of the primary key AllCode in the row that is removed from the table BookAuthors. The argument ON UPDATE CASCADE modifies the foreign key values in the column AuthorCode of the table CoAuthorship if the primary key value in the column AllCode of the table BookAuthors will be changed. For example, a 1-by-100 change in the column AllCode of the table BookAuthors will result in a 1-by-100 change in two rows of the table CoAuthorship at once (table C.8).

It remains to add that the table CoAuthorship fully complies with the relationship “CO-AUTHORSHIP” specification in the database "LIBRARY" logical model (table B.10).

Table B.10

 

The relationships "BOOKS FUNDS TYPES" and "PHONE TYPES" in the database "LIBRARY" logical model have the same specification. We have already created the table PhoneTypes ("PHONE TYPES") in the physical model. Its key attribute Code is defined on the domain AllCode. We will do the same in the table BookFunds ("BOOKS FUNDS TYPES"). The difference is that the attribute Name of the table PhoneTypes is defined on the domain AllNameType and it in the table BookFunds will be defined on the domain AllName:

The character data type was selected for the domain. The maximum characters number in a table cell is defined in the parentheses. The argument NOT NULL makes mandatory entering a value in all column AllName cells.

The relationship "BOOKS FUNDS TYPES" will correspond to the table BookFunds in the database "LIBRARY" physical model. All domains for its creation are defined. Let's add the table BookFunds to the physical model:

You can see that the table BookFunds completely complies with the relationship "BOOKS FUNDS TYPES" specification in the database "LIBRARY" logical model (table B.11).

Table B.11

 

The relationship "BOOKS INVENTORY NUMBERS" completes the second part of the database "LIBRARY" physical model. Its attributes are defined on domains: AllCode, AllInventoryNumber and AllCost. The domain AllCode was previously defined before the table PhoneTypes. Let's create the domains AllInventoryNumber and AllCost:

Both domains are defined on standard data types. The INTEGER data type is for integer values. The FLOAT data type is for floating point values. The argument NOT NULL is used for both domains. It prohibits leaving the cells of the table columns without value. The essence of the argument CHECK(VALUE >= 10) for the domain AllInventoryNumber was described when creating the domain AllNumber before the table PasportData. It prohibits entering values less than or equal to 10. The source of such restrictions is usually the result of the business processes analysis. That must be agreeing with the customer.

Now we can add the table BookInventoryNumbers to the database "LIBRARY" physical model:

The arguments ON DELETE CASCADE and ON UPDATE CASCADE maintain the referential integrity of a relational database by cascade deleting and updating foreign key values. Its work is described earlier for the table CoAuthorship.

We can only add that the table BookInventoryNumbers completely corresponds with the relationship "BOOKS INVENTORY NUMBERS" specification in the database "LIBRARY" logical model (table B.12).

Table B.12

 

Let us remind you that we have conditionally divided the logical and physical models of the database “LIBRARY” into two parts. The first part describes relationships and tables with information about people. The second part contains information about books. The relationship "BOOKS REGISTRATION" (logical model) or the table BookGiveOutRecord (physical model) connects them into one whole. It is here that librarians keep track of book lending to readers.

Only one domain is used to define the relationship "BOOKS REGISTRATION" attributes: AllCode. We created it before the table PhoneTypes. Several relationship attributes are defined on the standard data type: DATE. Now we can finish the database "LIBRARY" physical modeling by creating the table BookGiveOutRecord using the statement CREATE TABLE:

There is one inconsistency between the table BookGiveOutRecord and the relationship "BOOKS REGISTRATION" in the database "LIBRARY" logical model. The foreign key ReaderCode is not defined in the table. This is because the decision was made to maintain referential integrity between the tables Readers and BookGiveOutRecord using triggers. We wanted to show how different referential integrity mechanisms can be implemented in the same table.

Foreign key definitions for maintaining referential integrity were described earlier for the tables BookInventoryNumbers and CoAuthorship. See the description of the table CoAuthorship for more information about the arguments ON DELETE CASCADE and ON UPDATE CASCADE. In other respects, the table BookGiveOutRecord corresponds to the relationship "BOOKS REGISTRATION" specification in the database "LIBRARY" logical model (table B.13).

Table B.13