Main menu

EN | RU | UK

To Home Page

APPENDIX B. Physical model of the database "LIBRARY"

On top
Appendix B
Purpose

Purpose: to show the strong relation between logical and physical modeling of a relational database.

B.1. About the physical model

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

The order of the tables description in the database "LIBRARY" physical model
Relationship name in the logical model Table name in the physical model
1 PHONE TYPES PhoneTypes
2 PASSPORT DATA PasportData
3 LIBRARIANS Librarians
4 READERS Readers
5 PHONES Phones
6 BOOKS Books
7 BOOKS AUTHORS BookAuthors
8 CO-AUTHORSHIP CoAuthorship
9 BOOKS FUNDS TYPES BookFunds
10 BOOKS INVENTORY NUMBERS BookInventoryNumbers
11 BOOKS REGISTRATION BookGiveOutRecord

 

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.

B.2. Database

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":

CREATE DATABASE "C:/DB/LIBRARY.GDB"

    USER "SYSDBA"

    PASSWORD "masterkey"

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:

CONNECT "C:/DB/LIBRARY.GDB"

    USER "SYSDBA"

    PASSWORD "masterkey"

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".

B.3. Domains

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

Domains of relationships attributes in the database "LIBRARY"
Attributes Relationship Domain description General requirements Domain names Data type
1 Primary and foreign keys of all relationships ALL Unique row numbers for all relationships (primary keys), foreign key values for all relationships Integer value AllCode INTEGER
2 Family names, names and patronymics of people READERS, LIBRARIANS, BOOKS AUTHORS Set of all surnames, names and patronymics of people Symbolic, 30 characters FIO CHAR(30)
3 Name PHONE TYPES Set of all names of phone types Symbolic, 20 characters AllNameType CHAR(20)
4 Passport series PASSPORT DATA Set of all passports series of readers and librarians Symbolic, 2 characters AllSeries CHAR(2)
5 Passport no. PASSPORT DATA Set of all numbers of passports of readers and librarians Integer value AllNumber INTEGER
6 Place of birth PASSPORT DATA Set of all descriptions of the birthplaces of readers and librarians Symbolic, 100 characters AllPlace CHAR(100)
7 Sex PASSPORT DATA Set of gender descriptions of readers and librarians Symbolic, 1 character AllSex CHAR(1)
8 Place of passport issue PASSPORT DATA Set of descriptions of places where passports are issued to readers and librarians Symbolic, 100 characters AllIssuePlace CHAR(100)
9 Notes READERS, LIBRARIANS, PASSPORT DATA, BOOKS, BOOKS AUTHORS Set of all unstructured notes Binary large object AllNote BLOB
10 Brief biography BOOKS AUTHORS Set of all short biographies of book authors Binary large object AllNote BLOB
11 Clock number LIBRARIANS Set of all personnel numbers for librarians Integer value AllClockNumber INTEGER
12 Position LIBRARIANS Set of all librarian positions Symbolic, 30 characters AllPost CHAR(30)
13 Home telephone, Telephone number LIBRARIANS, PHONES Set of all phones of librarians and readers Symbolic, 20 characters AllHomePhone CHAR(20)
14 Reader card number READERS Set of all readers card numbers Integer value AllReaderCardNumber INTEGER
15 Place of work READERS Set of descriptions of all of the readers' mainstream work Symbolic, 60 characters AllJob CHAR(60)
16 Name BOOKS Set of all book titles Symbolic, 200 characters BookName CHAR(200)
17 Book circulation BOOKS Set of all book circulations Integer value AllDrawing INTEGER
18 Universal Decimal Classifier BOOKS Set of all universal decimal classifiers for books Symbolic, 20 characters AllUDK CHAR(20)
19 Cipher BOOKS Set of all internal book ciphers in the library Symbolic, 30 characters AllCipher CHAR(30)
20 Name BOOKS FUNDS TYPES Set of all types of book funds Symbolic, 20 characters AllName CHAR(20)
21 Inventory number BOOKS INVENTORY NUMBERS Set of all inventory numbers of books Integer value AllInventoryNumber INTEGER
22 Cost BOOKS INVENTORY NUMBERS Set of all book values Floating point value AllCost FLOAT

 

B.4. Table PhoneTypes ("PHONE TYPES")

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:

CREATE DOMAIN AllCode AS INTEGER

    NOT NULL;

CREATE DOMAIN AllNameType AS CHAR(20)

    NOT NULL;

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:

CREATE TABLE PhoneTypes

    (Code AllCode,

    Name AllNameType,

    PRIMARY KEY (Code));

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

CREATE TABLE PhoneTypes

    (Code INTEGER,

    Name CHAR(20),

    PRIMARY KEY (Code));

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

Specification of the table PhoneTypes (“PHONE TYPES”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Name Name CHAR 20  

 

B.5. Table PassportData (“PASSPORT DATA”)

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.

CREATE DOMAIN AllSeries AS CHAR(2)

    NOT NULL;

CREATE DOMAIN AllNumber AS INTEGER

    CHECK(VALUE >= 10)

    NOT NULL;

CREATE DOMAIN AllPlace AS CHAR(100)

    NOT NULL;

CREATE DOMAIN AllSex AS CHAR(1)

    CHECK(VALUE IN ('М','Ж'));

CREATE DOMAIN AllIssuePlace AS CHAR(100)

    NOT NULL;

      CREATE DOMAIN AllNote AS BLOB;

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:

CREATE TABLE PasportData

    (Code AllCode,

    Series AllSeries,

    Number AllNumber,

    Birthday DATE NOT NULL,

    BirthPlace AllPlace,

    Sex AllSex,

    IssuePlace AllIssuePlace,

    IssueDate DATE,

    Note AllNote);

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

Specification of the table PassportData (“PASSPORT DATA”)
Headline Column name Type Length Key
1 Code Code INTEGER    
2 Passport series Series CHAR 2  
3 Passport no. Number INTEGER    
4 Date of birth Birthday DATE    
5 Place of birth BirthPlace CHAR 30  
6 Sex Sex CHAR 1  
7 Place of passport issue IssuePlace CHAR 100  
8 Data of passport issue IssueDate DATE    
9 Notes Note BLOB    

 

B.6. Table Librarians ("LIBRARIANS")

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.

CREATE DOMAIN AllClockNumber AS INTEGER

    CHECK(VALUE >= 10)

    NOT NULL;

CREATE DOMAIN FIO AS CHAR(30)

    NOT NULL;

CREATE DOMAIN AllPost AS CHAR(30)

    CHECK(VALUE IN ('М','Ж'));

CREATE DOMAIN AllHomePhone AS CHAR(20)

    NOT NULL;

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.

CREATE TABLE Librarians

    (Code AllCode,

    ClockNumber AllClockNumber,

    FamilyName FIO,

    Name FIO,

    Patronymic FIO,

    PasportCode AllCode,

    Post AllPost,

    HomePhone AllHomePhone,

    Note AllNote,

    PRIMARY KEY (Code));

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

Specification of the table Librarians (“LIBRARIANS”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Clock number ClockNumber INTEGER    
3 Family Name FamilyName CHAR 30  
4 Name Name CHAR 30  
5 Patronymic Patronymic CHAR 30  
6 Passport code PasportCode INTEGER    
7 Position Post CHAR 30  
8 Home telephone HomePhone CHAR 20  
9 Notes Note BLOB    

 

B.7. Table Readers (“READERS”)

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.

CREATE DOMAIN AllReaderCardNumber AS INTEGER

    CHECK(VALUE >= 10)

    NOT NULL;

CREATE DOMAIN AllJob AS CHAR(60)

    NOT NULL;

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:

CREATE TABLE Readers

    (Code AllCode,

    FamilyName FIO,

    Name FIO,

    Patronymic FIO,

    ReaderCardNumber AllReaderCardNumber,

    PasportCode AllCode,

    Job AllJob,

    Post AllPost,

    Note AllNote);

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

Specification of the table Readers (“READERS”)
Headline Column name Type Length Key
1 Code Code INTEGER    
2 Family name FamilyName CHAR 30  
3 Name Name CHAR 30  
4 Patronymic Patronymic CHAR 30  
5 Reader card number ReaderCardNumber INTEGER    
6 Passport code PasportCode INTEGER    
7 Place of work Job CHAR 60  
8 Position Post CHAR 30  
9 Notes Note BLOB    

 

B.8. Table Phones ("PHONES")

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:

CREATE TABLE Phones

    (ReaderCode AllCode,

    PhoneTypesCode AllCode,

    PhoneNumber AllHomePhone);

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

Specification of the table Phones (“PHONES”)
Headline Column name Type Length Key
1 Reader code ReaderCode INTEGER    
2 Telephone type code PhoneTypeCode INTEGER    
3 Telephone number PhoneNumber CHAR 20  

 

B.9. Table Books ("BOOKS")

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.

CREATE DOMAIN BookName AS CHAR(200)

    NOT NULL;

CREATE DOMAIN AllDrawing AS INTEGER

    CHECK(VALUE >= 10)

    NOT NULL;

CREATE DOMAIN AllUDK AS CHAR(20)

    NOT NULL;

CREATE DOMAIN AllCipher AS CHAR(30)

    NOT NULL;

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:

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

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

Table B.8

Specification of the table Books (“BOOKS”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Name Name CHAR 200  
3 Issue year IssueYear DATE    
4 Book circulation Drawing INTEGER    
5 Book publishers BookPublishers CHAR 60  
6 Universal Decimal Classifier UDC CHAR 20  
7 Cipher Cipher CHAR 30  
8 Notes Note BLOB    

 

B.10. Table BookAuthors (“BOOKS AUTHORS”)

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:

CREATE TABLE BookAuthors

    (Code AllCode,

    FamilyName FIO,

    Name FIO,

    Patronymic FIO,

    Birthday DATE NOT NULL,

    Deatheday DATE,

    ShortBiography AllNote,

    Note AllNote,

    PRIMARY KEY (Code));

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

Specification of the table BookAuthors (“BOOKS AUTHORS”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Family name FamilyName CHAR 30  
3 Name Name CHAR 30  
4 Patronymic Patronymic CHAR 30  
5 Date of birth Birthday DATE    
6 Date of death DeatheDay DATE    
7 Brief biography ShortBiography BLOB    
8 Notes Note BLOB    

 

B.11. Table CoAuthorship (“CO-AUTHORSHIP”)

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:

CREATE TABLE CoAuthorship

    (Code 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);

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

Specification of the table CoAuthorship (“CO-AUTHORSHIP”)
Headline Column name Type Length Key
1 Book code BookCode INTEGER   F
2 Author code AuthorCode INTEGER   F

 

B.12. Table BookFunds ("BOOKS FUNDS TYPES")

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:

CREATE DOMAIN AllName AS CHAR(20)

    NOT NULL;

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:

CREATE TABLE BookFunds

    (Code AllCode,

    Name AllName,

    PRIMARY KEY (Code));

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

Specification of the table BookFunds (“BOOKS FUNDS TYPES”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Name Name CHAR 20  

 

B.13. Table BookInventoryNumbers ("BOOKS INVENTORY NUMBERS")

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:

CREATE DOMAIN AllInventoryNumber AS INTEGER

    CHECK(VALUE >= 10)

    NOT NULL;

CREATE DOMAIN AllCost AS FLOAT

    NOT NULL;

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:

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

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

Specification of the table BookInventoryNumbers (“BOOKS INVENTORY NUMBERS”)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Book code BookCode INTEGER   F
3 Fund code FundCode INTEGER   F
4 Inventory number InventoryNumber INTEGER    
5 Cost Cost FLOAT    

 

B.14. Table BookGiveOutRecord “BOOKS REGISTRATION”

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:

CREATE TABLE BookGiveOutRecord

    (Code AllCode,

    ReaderCode AllCode,

    OutLibrarianCode AllCode,

    InventoryCode AllCode,

    IssueDate DATE NOT NULL,

    ReturnDate DATE NOT NULL,

    FactReturnDate DATE,

    InLibrarianCode AllCode,

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

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

Спецификация таблицы BookGiveOutRecord («УЧЕТ ВЫДАЧИ КНИГ»)
Headline Column name Type Length Key
1 Code Code INTEGER   P
2 Reader code ReaderCode INTEGER    
3 Librarian code who gave out books OutLibrarianCode INTEGER   F
4 Code of book inventory number InventoryCode INTEGER   F
5 Date of give out IssueDate DATE    
6 Date of restitution ReturnDate DATE    
7 Actual data of restitution FactReturnDate DATE    
8 Librarian code who took back books InLibrarianCode INTEGER   F

 

Conclusions

1. The order in which tables in the physical model are created may not match the order in which relationships are modeled in the logical model of a relational database.

2. The names of objects in the physical model must match or be similar to the names of objects in the logical model.

3. You should choose the simplest mechanisms for maintaining the entities integrity and the referential integrity of the relational database.

4. The use of more complex mechanisms for maintaining the entities integrity and the referential integrity of a relational database can only be justified by the lack of the ability to implement customer requirements using simpler mechanisms.

5. The process of creating a database "LIBRARY" physical model clearly proved the close connection between the logical and physical modeling of relational databases.

© Yaroslav Kuvaiev, 2005—2020.

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.