APPENDIX A. Logical model of the database "LIBRARY"

Here are the results of logical modeling of the educational database "LIBRARY". A link diagram between relationships is essential for understanding the structure of a relational database. In it, relationships are represented only by key attributes that are used for relationships between them. The specification of relations allows you to get an idea of the entity properties that are involved in the accounting process of giving out and returning books in the library. The logical model is the starting point for the physical modeling of the “LIBRARY” DB. Its results are presented in Appendix B.

You should understand that the presented logical model of the “LIBRARY” DB is not perfect. The logic of the accounting process for giving out and returning the books in the library can be described in different ways. It depends on many factors. There is no sense to list them here. Try to make your own version of the model. Before that, decide on the purpose of the relational database, which you will create based on your logical model.

In our opinion, the learning process requires the following sequence of presentation of the logical model. The link diagram between relationships is given at the start (Fig. A.1). Although it is one of the main results of logical modeling. Then, the relationship descriptions which store information about the people who take part in the process are given sequentially. This is the first part of the logical model. The relationships with information about books are given in the second part. The relationship which connects both parts of the logical model is described last. It answers the question: who gave the book to whom and when, and when it was returned back to the library.

The link diagram represents the data structure of the "LIBRARY" database (fig. A.1). That's why, only key attributes of each relation that are involved in ensuring the referential integrity of the "LIBRARY" database are displayed into it. The rest of the attributes, including the keys, are described separately for each relationship. The list of all "LIBRARY" database relations you find at the very beginning of Appendix B.

Fig. A.1. Diagram of links between «LIBRARY» DB relationships

The practical value of the link diagram between relationships is difficult to overestimate. First, you will need it to correct the logical model, which is caused by changes in the logic of the business process. This happens quite often during the life cycle of a relational database. Secondly, this diagram allows to significantly simplify the design and optimization of SQL queries to retrieve information from the database. It is about using keys (indexes) to optimize the speed of SQL queries.

The description of the relationship “READERS” is included in the first part of the logical model. It is dedicated to organize a storage of information about librarians and readers. Together with the relationship "READERS", the first part of the logical model includes the relationships "LIBRARIES", "PASSPORT DATA", "TELEPHONES" and "TYPES OF PHONES".

The relationship contains a list of all readers who are registered in the LIBRARY database. As a result of logical modeling, the list of relationship attributes was obtained. It is listed in corresponding specification. The purpose of the attributes is clear from their names.

READERS(Code, Family name, Name, Patronymic, Reader card number, Passport code, Place of work, Position, Notes)

Primary key: Code

Candidate key: Reader card number

Candidate key: Passport code

Foreign key: Passport code

So, the following attributes of the relationship are candidate keys: “Code”, “Reader card number”, “Passport code”. Each of them individually can serve to ensure the entity integrity in the relationship "READER". However, the attribute "Code" was selected as the primary key. It was specially added to the relationship. Its format will remain unchanged throughout the entire of the database life cycle. It is defined by the database developer. This is important to ensure the referential integrity of the database. The format of the remaining candidate keys does not depend on the database developer.

The attribute “Passport code” is both a candidate and a foreign key. It ensures a 1: 1 (one to one) link between the relationship “READERS” and “PASSPORT DATA”. This means that one cortege in the relationship “READERS” always corresponds to one cortege in the relationship “PASSPORT DATA”.

The relationship "LIBRARIANS" is intended to store information about librarians. All of them are registered in the database “LIBRARY”. The list of its attributes is the result of logical modeling of the database “LIBRARY”. The names of the attributes have been chosen so that their purpose is clear.

LIBRARIANS(Code, Clock number, Family Name, Name, Patronymic, Passport code, Position, Home telephone, Notes)

Primary key: Code

Candidate key: Clock number

Candidate key: Passport code

Foreign key: Passport code

Let's pay attention to the key attributes: "Code", "Clock number", "Passport code". They are all candidate keys. The attribute "Clock number" is used for identification into the organization. In our case, this is a library. The attribute "Passport code" identifies a library employee by his passport data. This is a requirement of the country's legislation. However, during logical design, the attribute “Code” was selected as the primary key. It does not play any role in the logic of the book accounting process. It is specially added to the relationship "LIBRARIANS". If this is not done, then the referential integrity of the database “LIBRARY” will depend on the format of the candidate key values (for example, "Clock number"). If we leave this dependency, then changing the format of the primary key value leads to the need to create referential integrity mechanisms for the old and new variants of its values. We have already encountered this problem for the relationship “READERS”.

The attribute “Passport Code” is both a candidate and a foreign key. It serves to enforce referential integrity between the relationship “LIBRARIANS” and “PASSPORT DATA”. Any cortege in the relationship "LIBRARIANS" always matches only one cortege in the relationship "PASSPORT DATA". Such a link between relationships is called 1:1 (one to one).

The between the relationship "READERS" and "LIBRARIANS" has much in common. First, in both these relationships, a record is kept of people who must have an identity card. Secondly, both groups of people are involved in the process giving out and receiving of books in the library. Thirdly, one and the same person can be both as a reader and as a librarian in one time. So, no one prohibits of library workers read books from its collections. For such a case, it is necessary to have both the reader attributes ("Reader card number", "Passport code", "Place of work", "Position", "Notes") and the librarian attributes ("Clock number" "Passport code", "Position", "Home telephone", "Notes"). Moreover, the values of the attributes "Passport Code" and "Position" in the relationship "READERS" and "LIBRARIANS" for such a person must be the same.

Let's return to the logical model imperfection of the database "LIBRARY". We have already discussed about that. And now we would like to draw your attention to the fact that the relationship "READERS" and "LIBRARIANS" the contain attributes "Family name", "Name" and "Patronymic". For a person who is both a librarian and a reader, the values of these attributes should be the same in both relationships. There is a simple way to make it. In our case it makes sense to define these attributes into the relationship "PASSPORT DATA". And then it is necessary them to remove from the relationship "READERS" and "LIBRARIANS". This improvement gives the possibility to enter values into attributes "Family name", "Name" and "Patronymic" in one place for every person. It excludes doubling this information. Additionally, it decreases the size of the database "LIBRARY". And what improvements for the logical model of the database "LIBRARY" can you offer?

The passport data of all people who was registred in the database "LIBRARY" was stored in this relationship. Other information of these people you can find in the relationship "READERS" and "LIBRARIANES". With each of them, the relationship "PASSPORT DATA" has a 1:1 (one to one) link. Skip to a description of this relationship. There are corresponding explanations there.

PASSPORT DATA(Code, Passport series, Passport no., Date of birth, Place of birth, Sex, Place of passport issue, Data of passport issue, Notes)

Primary key: Code

Candidate key: Passport series, Passport no.

Let's consider the key attributes of the relationship "PASSPORT DATA". The relationship has no foreign keys. There is a composite candidate key. Pay attention that for the first time in the logical model of the database "LIBRARY" we are dealing with a composite candidate key. It includes itself the attribute "Passport series" and "Passport no.". We can identify the passport of any person by the values of these two attributes. We are talking about Ukraine. In other countries, the ID card may have different information. Therefore, the special key attribute “Code” has been added to the relationship. It selected as a primary key. It gives an opportunity to get away from any legislation dependence in supporting referential integrity of database "LIBRARY".

Do not forget that the database "LIBRARY" was created learning of an organization of a relational database. And that's why, you were offered two approaches to store information about people's contacts in that database. It doesn't matter what kind of contacts. These might be phone numbers, postal addresses, e-mails and many others. For database "LIBRARY" We have chosen the type of contacts most understandable for a wide audience: phones.

Both approaches are based on user requirements. If you have agreed with users that one phone number is enough to contact a person, then use the simplest option. The attribute "Home telephone" has been added in the relationship "LIBRARIANS" for that. If during the maintenance of the database it turned out that one contact is not enough for one person, then you need the second option. This variant presented for persons who was registered as readers in the database "LIBRARY". It was implemented in three relationships: "READERS", "PHONES" and "PHONE TYPES".

PHONES(Reader code, Telephone type code, Telephone number)

Candidate key: Reader code, Telephone number

Foreign key: Reader code

Foreign key: Telephone type code

The specification of the relationship "PHONES" is the result of the normalization relationships of the database "LIBRARY". More details about this will be written in the methodical recommendations for practical works "Refinement of the logical model and normalization of relations." The candidate key of the relationship "PHONES" consists of two attributes: "Reader code" and "Telephone number". There is a logical explanation for this.

Consider the attribute "Reader code". It cannot be used as a candidate key. Because we are considering an option when for one person it should be possible to specify more than one contact. In our case, these are phone numbers. That's why, several corteges might be added into the relationship "PHONES" with the same values of the attribute "Reader code". This does not match the definition of a candidate key.

Let's consider the attribute “Telephone number”. So, look like we've found a candidate key! Because we will not find two identical phone numbers in the world. But then answer the question: can two readers be from the same family? We consider they can. Both can register the telephone number that is installed in their house. This means that at least two corteges of the relationship "PHONES" can contain the same number. That's why the "phone number" attribute cannot be a candidate key.

Does it make sense to specify the same phone number for the same reader several times? We think the answer is obvious: no. After all this reasoning, it becomes obvious that the pair of values in the attributes "Reader code" and "Telephone number" can be a candidate key of the relationship "PHONES". Always be critical of your logical conclusions. This will help you become the best at what you do.

The relationship "PHONE TYPES" contains information about the types of readers' phones. It completes the first part of the database "LIBRARY" logical model. Recall that the first part contains information about all readers and librarians.

PHONE TYPES(Code, Name)

Primary key: Code

Candidate key: Name

The relationship has just two attributes. Each of them is a candidate key. The attribute "Code" was specially added in the relationship "PHONE TYPES". This technique was explained earlier. Attribute "Code" is necessary to ensure the independence of the referential integrity of the database from external factors. See relationships "READERS", "LIBRARIANS" and others. On the one hand, this approach complicates the relationship "PHONE TYPES" and the database "LIBRARY". However, on the other hand, it reduces the amount of memory that the database will occupy on disk. We can easily prove it.

Let us assume that twenty characters are allocated for the attribute "Name" of the relationship "PHONE TYPES". The number of bytes per character depends on the encoding of the set of characters in the alphabet. In the best case, one character is one byte. Therefore, a minimum of twenty bytes must be reserved for each "Phone Type" foreign key value in the relationship "PHONES".

Now let's talk about the length of the "Code" attribute value. Usually, the minimum length of integer value is one byte. Then the integer value of the foreign key "Telephone type code" will take up twenty times less disk space than its symbolic value. This is an example of the strong link between a logical and a physical model of a relational database.

The second part of the database "LIBRARY" logical model begins with the relationship "BOOKS" description here. That relationship stores a part of the information about all the books that are in the library funds. This can be seen of the link diagram between the database "LIBRARY" relationships (fig. A.1). Find the "BOOKS" relationship on it. Take a strong look at his connections to other relationships. After that, it will become clear to you that comprehensive information on each book is stored simultaneously in these five relationships: "BOOKS", "BOOKS AUTHORS", "CO-AUTHORSHIP", "BOOKS INVENTORY NUMBERS" and "BOOKS FUNDS TYPES". We will sort out the logic of making such a difficult decision gradually.

BOOKS(Code, Name, Issue year, Print run, Book publishers, Universal Decimal Classification, Cipher, Notes)

Primary key: Code

Candidate key: Name, Book publishers, Issue year

Notice the key attributes of the "BOOKS" relationship. He has two possible keys. The candidate key "Code" was selected as the primary key. This approach is described for the relations "READERS", "LIBRARIANS", "PASSPORT DATA" and others, which are presented in the database "LIBRARY". The second candidate key is composite. It includes the attributes "Name", "Book publishers", and "Issue year". None of these attributes alone can uniquely identify a book. Even any pair of these attributes that was included in the candidate key does not guarantee the integrity of the entities in the "BOOKS" relationship. However, the values of all three attributes uniquely identify a tuple of relationships that stores information about a single book. These statements are based on input from people in the organization for which you are performing logical modeling of a relational database. Do not forget that the "LIBRARY" database is educational.

The relation stores information about the authors of the books. Let's consider its specification.

BOOKS AUTHORS(Code, Family name, Name, Patronymic, Date of birth, Date of death, Brief biography, Notes)

Primary key: Code

Pay attention to the key attribute "Code" of the relationship "BOOKS AUTHORS". In the relationships "READERS", "LIBRARIANS", "PASSPORT DATA" and "BOOKS" it was added for the independence of the database "LIBRARY" referential integrity from external factors. This attribute has been added in relationship "PHONE TYPES" to reduce the memory size occupied by the database "LIBRARY". The reason for adding the attribute "Code" to the relationship "BOOKS AUTHORS" does not match one of the above.

To understand it, try to find candidate keys of the relationship "BOOKS AUTHORS" yourself. You will see that no attribute of a relationship can uniquely identify the it cortege. The “Brief biography” and “Notes” attributes are problematic to use as part of a candidate key. These are text attributes. Their size will not allow identifying the relationship corteges within a reasonable amount of time. Additionally, the size of the special files that store the candidate key values will be too large.

Consider the remaining five attributes. First, let's analyze the group consisting of the attributes "Family name", "Name" and "Patronymic". All variants of values in these attributes of the relationship "BOOKS AUTHORS" of the database "LIBRARY" are unique. However, there are only sixteen corteges in this relationship (table C.4). This is not the case in real life. This is well known who tests software. Let's add the values of the attributes "Date of birth" and "Date of death" to them. Do you really think that a situation cannot emerge that two people with the same family names, names and patronymics cannot be born on the same day, live a long and happy life, and die on the same day? Believe us, that may be.

Consequently, in relationship "BOOKS AUTHORS" there are no attributes or their set that could form a candidate key. This is a reason to add the attribute "Code" as a candidate key and then choose it as a primary key.

The relationship links authors to the books they have written. It consists of only two attributes. Each of these attributes is a foreign key. And all the attributes of the relationship were included in the candidate key.8

CO-AUTHORSHIP(Book code, Author code)

Candidate key: Book code, Author code

Foreign key: Book code

Foreign key: Author code

Why is it so difficult to connect the relationships "BOOKS" and "BOOKS AUTHORS" with each other? It's all about eliminating the N:M (many-to-many) link between these relationships. One author can write several books. On the other hand, one book may have several authors. This means that several corteges in the relationship “BOOKS” can correspond to several corteges in the relationship “AUTHORS”. There are no mechanisms in the relational data model that could implement such a case.

However, any link N:M between relationships can be realized with two links 1:M and an intermediate relationship. The relationship "CO-AUTHORSHIP" has the structure of just such a relationship. It is intermediate between the relationships "BOOKS" and "AUTHORS". It consists of two attributes, each of which is a foreign key. The attribute "Book code" maintains the link 1:M between the relationships "BOOKS" and "AUTHORS". The attribute "Author code" maintains the link 1:M between the relationships "AUTHORS" and "CO-AUTHORSHIP". The candidate key for the relationship "CO-AUTHORSHIP" consists of all its attributes.

The relationship contains a list of the names of all library funds that include books. Its structure is the same as that of the relationship "PHONE TYPES". There you will find comprehensive information about the logic for creating such relationships in relational databases. Therefore, we provide only its specification.

BOOKS FUNDS TYPES(Code, Name)

Primary key: Code

Candidate key: Name

This relationship completes the description of the second part of the logical model of the database "LIBRARY". It contains information about each exemplar of the book in the library.

BOOKS INVENTORY NUMBERS(Code, Book code, Fund code, Inventory number, Cost)

Primary key: Code

Candidate key: Inventory number

Foreign key: Book code

Foreign key: Fund code

Each copy of the book has its own unique inventory number. The attribute "Inventory number" contains its value. This is a relationship candidate key. However, the special attribute "Code" has been introduced into the relationship. It is chosen as the primary key to maintain referential integrity with the relationship "BOOKS REGISTRATION". This is done for the independence of the referential integrity of the database "LIBRARY" from external factors (see the relationships "READERS", "LIBRARIANS", "PASSPORT DATA" and "BOOKS").

The 1:M (one-to-many) link is maintained between the relationships "BOOK INVENTORY" and "BOOKS REGISTRATION". The relationship "BOOK INVENTORY" has the M:1 (many to one) relationship with the relationships "BOOKS" and "BOOKS FUNDS TYPES". This means that one book can have several copies in different library funds. The link diagram between relations of the DB "LIBRARY" displays this clearly (Fig A.1).

Two parts of the logical model merge into a single whole in the relationship "BOOKS REGISTRATION". The first is about people and the second is about books. This ends the logical model description of the educational database "LIBRARY".

BOOKS REGISTRATION(Code, Reader code, Code of librarian who gave out books, Code of book inventory number, Date of give out, Date of restitution, Actual data of restitution, Code of librarian who took back books)

Candidate key: Code of book inventory number, Reader code, Date of give out

Foreign key: Reader code

Foreign key: Code of librarian who gave out books

Foreign key: Code of librarian who took back books

Foreign key: Code of book inventory number

Let's start with a candidate key. It includes three attributes: "Code of book inventory number", "Reader code", "Date of give out". Why are they enough to identify a relationship cortege? Let's reason. One reader can take several books. This means that the same value of the reader code can be present in several corteges of the relationship. This is not sufficient to uniquely identify a relationship cortege. One book can be borrowed several times. This one-to-one case is like the case with the attribute "Reader code". Let's take a couple of attributes "Reader code" and "Book inventory number code". Have we found a candidate key? But who prevents the reader from taking the same copy of the book in the library several times? Especially if this book liked him.

Let's add the date of gave out of the book to these attributes. And let's immediately ask the question: can one and the same reader takes the same copy of a book from the library within 24 hours several times? Of course, it can! First, the reader took the book at once after the library was opened. Then, after reading it, his returned it in before lunch. Then he remembered that he needed to clarify something, and he again took this copy of the book before the library closed. So, the candidate key in the specification of the relationship "BOOKS REGISTRATION" is incorrect?

It all depends on the data type of the attribute "Date of give out" in the physical model of the database "LIBRARY". For the second time, we pay your attention to the strong connection between physical and logical modeling of a relational database. The first case was described for the relationship "PHONE TYPES".

The data type of the "Date of give out" attribute must record the moment of the book gave out with a precision of a second. This will allow the candidate key consisting of the attributes "Book inventory number", "Reader code", "Date of give out" to uniquely identify the cortege of the relationship "BOOKS REGISTRATION". By reasoning in this way, you can find more candidate keys to this relationship. They are not specifically indicated in its specification.

Consider the foreign keys of the relationship "BOOKS REGISTRATION". Each of them consists of just one attribute. Each defines a link of type M:1 (many to one) relationship "BOOKS REGISTRATION" with three other relationships of the database "LIBRARY": "READERS", "LIBRARIANS" and "BOOKS INVENTORY NUMBERS". Moreover, with the relationship "LIBRARIANS" there are two links M:1 (many to one). We need to know not only the librarian who gave out the book, but also the librarian who accepted it.

On this, the description of the logical model will be considered complete.