The main idea of the chapter is to give the definition of the interaction between logical and physical stages of designing and also targets and tasks of the physical DB designing stage, justification of choosing the destination DBMS, acquaintance with “standard” rules that determine DBMS being a part of really relational systems. The chapter contains brief characteristic of SQL language and description of main instruments of providing relational data integrity.
During logical designing the structure of DB was determined (that is a set of values, attributes and links). Even though logical model doesn’t depend on specific destination DBMS it was created considering data organization in the chosen model. There is a continuous feedback between logical and physical designing, as the decisions that were taken during the stage of physical projecting to improve the efficiency of the system, are able to influence the structure of logical data model [4, 6].
DB physical designing – is a process of creation and description the DB implementation on the secondary store unit with indication of structures and methods of saving that are applied to provide efficient data processing.
The main purpose of physical designing is to describe the method of logical project physical implementation. Speaking about relational data model that is the following:
1) creation of relational tables set and limitations for them on the base of information that is contained in global logical data model;
2) the definition of specific structures of data saving and access methods that provide optimal efficiency of the systems with DB;
3) designing of the instruments for protecting the system that is created.
In ideal case the stage of logical designing of big systems should be separated from the stage of physical designing. There are several reasons for that:
1) they are connected to totally different aspects of the system: what to do and how to do;
2) they are run in different periods of time as we have to understand what has to be done before we decide how to do this;
3) absolutely different skills are necessary and they are usually implemented by different people.
Getting down to the DB physical designing, first of all it is necessary to choose specific target DBMS. Here are the target stages of its selection:
Let’s examine the choice of a product for learning relational DB organization.
The first stage in this case comes to the point that choice will be realized between DBMS that support relational data presentation model and client-server architecture. This is because about 80 % of DBMS in the worlds use relational DB, and client-server architecture proved in practice its vital capacity and efficiency.
The website doesn’t have a purpose to study the organization of DB on the base of some software product or to examine specific DBMS realization. Our purpose is to give general methodology and approach. On this base reader has to form his own skills of DB designing. Thus, chosen software product has to be the easiest and clearest for examination. According to the point of view of the authors, the most suitable are Mysql and Interbase.
On the third stage human factor appeared. Due to the fact that authors of the book program in the environment designed by Borland company for the begging of work over the book Interbase was the most acceptable choice among the products that were defined on the second stage. So, forth stage is finished, as the selection of DBMS was already explained.
It is necessary to admit that to implement practical projects on different stages of DBMS selection different factors can arise. Sometimes it is impossible to formalize them definitely. For example, the presence of licenses for the implementation of a specific DBMS, experience of designers in implementation of the projects on the base of specific DBMS, particularity of concrete project, budget that is provided and etc.
There are hundreds of relational DBMS on the market. There are some of them: Firebird [7]; Interbase [10, 14, 15]; Microsoft Access [16]; Microsoft SQL Server [17]; MySQL [18]; Oracle [19]; Visual FoxPro [20] and etc. Unfortunately, some of them, frankly speaking, don’t correspond to the definition of relational model. Several suppliers of DBMS that are based on the network or hierarchic data model implement only some features of relational systems in their products. Still they declare that their products belong to relational. Worried by the matter of fact that potential and content of relational method is disfigured, Edgar Codd in 1985 proposed 12 rules that give the definition to relational systems (or better is to say 13 if to consider fundamental rule 0). These rules create some kind of standard that can be applied to determine if DBMS really belongs to relational systems or not.
During many years rules that were proposed by Codd were criticized by interested persons and experts. Some determined it as just theoretical exercises. Others declared that their products already meet most of these requirements if not all of them. This discussion helped users and unions of DBMS designers to understand better the most important particularities of really relational DBMS. To underline the importance of these rules, they were divided into five functional groups [4].
Speaking in general, rules 0 and 12 is a «litmus paper» that gives us the possibility to determine if system belongs to relational DBMS. If the system doesn’t meet these requirements it must not be considered as a relational one.
Rule 0 – fundamental rule. Any system that is positioned as relational DBMS must be able to manage DB by means of its relational functions only.
This rule means that DBMS must not apply any non-relational operations to run such processes as data definition and manipulation.
Rule 12 – rule of alternative rout prohibition. If relational system has language of a low level (with serial digit operation) it can not be applied for canceling or bypass of rules and limitations of integrity, that were composed by relational system of higher level (with possibility of several strings processing).
This rule guarantees that all attempts to get access to the DB are managed by DBMS in such way that integrity of the DB can not be broken without the knowledge of user or administrator.. However, this doesn’t exclude the possibilities to apply languages of low level with an interface of sequential processing.
Fundamental structural concept of relational model is a relation. Codd affirms that relational DBMS has to support operations with several structure elements: relations, domains, primary and foreign keys. For each relation of DB primary key has to be defined.
Rule 1 – presentation of the information. All information in relational DB is presented in explicit mode on logical level and only as values in the tables.
According to this rule all information including metadata in the system catalog should be stored as relations and managed by means of the same functions that are used for data processing. “Logical level” is mentioned in this rule and it means that such physical constructions as indexes should not be presented in the model and user must not evidently refer to them in the data access operations even in cases when they do exist.
Rule 6 – renewal of representation. All representations that are theoretically updated should be updated in this system.
This rule has attitude to the representations of virtual table only. More detailed concept of updating that is accepted for SQL language will be examined later. This rule says that if representation is theoretically updated DBMS should be able to implement such updating. In fact none of existing systems support this requirement because terms of identification for all theoretically updated representations are still not determined.
Codd proposed two rules of data integrity support. Data integrity support is an important criterion that can be used to estimate of the system’s fitness for resolving practical problems. When DBMS by itself, and not only some of its application programs, supports a lot of limitations of integrity, it ensures data quality guarantee.
Rule 3 – systematic nulls processing (NULL). Nulls (that are preset by determinant NULL), that are values different from an empty row or idle characters row, null or other specific value are supported for systematic representation of missing or unacceptable information independently from the data type.
Rule 10 – independence of integrity limiting. Specific for the present relational DBMS limitations of integrity should be defined on sublanguage of relational data and saved in system catalogue instead of application programs.
Codd underlines that information about data integrity limitation establishing must be saved in system catalogue instead of encapsulation in separate application programs or users’ interfaces. Saving of integrity limitations in the system catalogue provides a significant advantage of centralized control and operating.
Ideal relational DBMS must support 18 data control functions that determine completeness of query language (term “query” includes operations of insert, updating and deletion). Rules of data manipulation determine method of application 18 data control functions. Strictly keeping these rules helps to isolate user and application programs from physical and logical methods of implementation data manipulation methods.
Rule 2 – guaranteed access. Logical access on the base of table names combination, primary key value and column name should be guaranteed for each data element (its atomic value) of relational DB.
Rule 4 – dynamic interactive catalog composed according to the rules of relation model. DB description should be presented on a logic level in the same way as ordinary data. It permits authorized users to make a call to this description using the same relational language that is used for data access.
This rule means that there can be only one language dedicated to handle metadata and normal data. DBMS must use only one logical structure for system information saving management – relation.
Rule 5 – comprehensive data sublanguage. Relational system can support several languages and different operating conditions with stations (for example blank fill method - fill-in-the-blanks). However there should be at least one language with operators that permit to express the following constructions: 1) data definition; 2) representation definition; 3) data handling instructions (interactive access, program access) 4)integrity limitations; 5) users authorization; 6) transactions management (start up, commitment and rollback).
It is necessary to know that new ISO standard for SQL language provides implementation of all this functions so that any language that supports this standard will automatically meet this requirement.
Rule 7 – high-level operations of input, updating and deletion. The capacity to update basic or derived relation (that is representation) as unique operand should have attitude not only to data access procedure but to procedures of data input, updating and deletion also.
Codd sets three rules of data independency from application programs that use this data. Strictly keeping these rules guarantees that users and designers will be protected from necessity to realize total modifications in the application program with each DB reorganization on low level.
Rule 8 – physical independence from data. Application programs and means of terminals managing should remain logically unchanged during implementation of any modifications of data saving or access mode.
Rule 9 – logical independence from data. Application programs and means of terminals managing should remain logically unchanged during any adjustments in the basic table if the information remains unchanged. Theoretically, these adjustments should not influence application software.
Rule 11 – independence from data distribution. Sublanguage of data handling in the relational DBMS should permit application programs and queries to remain logically unchanged independently from the mode of data saving: whether it is physical, centralized or distributed mode.
Independence from data distribution means that application program that implements access to DBMS on a separate computer should continue operating without any modifications when data in the networks will be transferred from one compute to anther. In other words for the end user should have an illusion that data has single source on one computer and systems bears responsibility for mediums movement and retrieval. Pay attention that it is not mentioned that relational DBMS certainly should support operation with distributed DB. It just means that language of queries should remain unchanged when work with distributed data is implemented in some DBMS.
SQL language is first and up till now unique standard language of work with DB that is rather wide-spread [4, 6 – 8]. There is one more standard language of work with DB - Network Data-base Language (NDL). It is based on CODASYL network model but it is far from being applied in all projects. Mostly all biggest DBMS designers nowadays create their own products using SQL language or SQL-interface.
In ideal case any language of DB operation should provide the following possibilities to user:
1) to create DB and tables with complete description of their structure;
2) to carry out simple and complicated queries, implement conversion of raw data;
3) to carry out simple and complicated queries, implement conversion of raw data;
4) to meet requirements of accepted standard that will permit to use the same command syntax and structure during the conversion of DBMS.
Also DB operation language has to resolve abovementioned tasks with minimal efforts from the side of user, and structure and command syntax should be rather simple and understandable. SQL language meets mostly all these requirements.
As an example of SQL language can be presented languages with the focus on transformation. Or the language dedicated for the operations with tables to modify input data into necessary output form. SQL language has two basic components:
1) DDL language (Data Definition Language), dedicated for definition of DB structure and data access managing;
2) DML language (Data Manipulation Language), dedicated for data selection and updating.
SQL language includes not only instructions for data definition and handling. There are absolutely no commands for computing. In other words there are no commands IF ... THEN ... ELSE, GO TO, DO ... WHILE and etc., that are dedicated for managing the computational process. Such tasks should be solved by means of programming languages or in interactive mode as a result of user’s actions. Due to such incompleteness of computational process organization SQL language can be applied in two ways. First way foresees interactive operation that includes input of separate SQL-operators by means of terminals. It is carried out by user. Another way is an application of SQL-operators to the program written in procedural language.
SQL language is more or less easy for learning.
For the moment when the site was created, there is an international standard ISO/IEC 9075 (1 – 4, 9 – 11, 13, 14):2008 for SQL language existing. Nominally this is standard language of relational DB definition and handling. However we should mention that DBMS designers get off its standards or support standards that were created much earlier. That is why it is necessary to apply corresponding technical documentation of the chosen DBMS designer for DB physical implementation. All examples and physical implementation of «LIBRARY» DB are worked out in Interbase DBMS, Embarcadero.
SQL-operator is composed of reserved words and also words that are determined by user [4, 6]. Reserved words is a constant part of SQL language and have fixed values. They should be recorded exactly as it was determined and can not be divided for wrapping. Words that were defined by user he determines by himself (according to first syntactical rules). These are names of different DB objects: tables, columns, presentations, indexes and etc. In the SQL-operator words are located according to defined syntactical rules. Even though it is not mentioned in the standard, a lot of SQL language dialects require putting some symbol at the end of operator that means the end of the text. Usually it is «semi».
Most of SQL-operators are not sensitive to register. It means that any letters can be used – both lower- and uppercase letters. One important conformance exception is symbol literal data that should not differ from correspondent values that concur in DB. Foe example, if there is a name value ‘IVANOV’ in the DB and in search terms is defined symbol literal ‘Ivanov’ will not be found.
As SQL language has free format, separate SQL-operators and their sequences will be more legible in case of using back-offs and aligning. It is recommended to keep the following rules.
To define the format of SQL-operators we will keep the following configuration.
In practice for the definition of DB structure DDL-operators are used. For the relations data filling and access DML-operators are used.
On the stage of choosing DB management system we stopped on Interbase DBMS [10, 14, 15]. It was mentioned that we don’t plan to study all particularities of work with the software product. Those, who are interested in details of designing and managing DB directly by means of this product, can get acquainted with documentation that is supplied by the designer.
Interbase DBMS permits to create DB in interactive mode (sequentially carrying out SQL-operators) or in SCRIPT-file interpreting mode. Practical part of the course is built on creation of SCRIPT-file and its sequent filling with instructions. Recommended sequence of SCRIPT-file instructions we are going to study during practical part of the course. Here we will present general overview of SQL-operators that create DB file and its structure that correspond to logical model on the hard disk.
To create DB file (or files) on hard disk operator CREATE DATABASE is used. It permits to:
Domains are created by CREATE DOMAIN operator, where it is necessary to give a unique name for each domain. It gives the possibility to:
To create tables and keys CREATE TABLE operator is used. Table must have a unique name. Within the table names of columns should not repeat. Thanks to this operator you can:
The order of creating tables in DB is important. First of all must be created those tables that have no foreign keys. After that should be created tables that have values of foreign keys connected to primary key of already existing tables. As an example we will present the order of working out tables «POSITION». «READERS», «TYPES OF TELEPHONES», «TELEPHONES» (fig. 3.4). It is possible to change the order creating tables «READERS» and «TYPES OF TELEPHONES». However «READERS» must be always created after table «POSITION», and «TELEPHONES» – after tables «TYPES OF TELEPHONES» and «READERS». Otherwise DBMS will generate an error.
DB «LIBRARY» structure that is received after the run of CREATE TABLE operators illustrates the connection between logical and physical models (appendix B). In appendix B letter Р is used to indicate primary keys, letter F is applied for foreign keys. Designation of primary and foreign keys corresponds to designation of relations links that are mentioned in appendix A. Arrows indicate that values of foreign keys in child relations must mandatory refer to primary keys values of parent relations.
NULL determinant indicates that attribute value at the present moment is unknown or inadmissible for this cortege.
NULL determinant should be understood as logical value «unknown». In other words this value or is not included in the definitional domain of some cortege or there is no determined value. Key word NULL is a way of incomplete or abnormal data processing. However determinant NULL should not be understood as zero numerical value or filled with blanks text line. Zeroes and blanks are values, while key word NULL is meant to indicate absence of any value. Some authors use term «value NULL», but in fact NULL determinant is not a value, but just indicates its absence. That is why it is not recommended to use term «value NULL».
Application of NULL determinant can cause problems on the stage of implementation. Difficulties can arise because relational model is based on calculation of predicates of the first degree and has two-digit or Boolean logic that means only two values are admissible: true or false. Application of NULL determinant means that it is necessary to work with logic of higher level, for example ternary or four-digit logic (Codd, 1986, 1987, 1990).
Application of NULL determinant for relation model is a topic for discussion. Codd (1990) examined NULL value as a component of this model, but other experts consider it to be not correct. They affirm that the problem of missing information is not quite clear jet. There is no adequate solution and thus including NULL determinant to relation model is premature (Date, 1995). It is necessary to admit that not all relational models support work with NULL determinant. But this determinant is present in Interbase DBMS.
First of all integrity is provided by primary (candidate) keys of basic or parent relations [2, 4, 6]. Here parent relation is defined as relation that corresponds to some subject (concept) of technological process on the logical model. For example, relation «POSITION» is a parent one for relation «READERS» (fig. 3.4). Relation «READERS» is a parent one for relation where primary keys refer to the value of «READERS» relation candidate keys.
Entity integrity in the basic relation can be provided because no attribute of the primary key can contain NULL determinant as a value.
To concede that there is NULL determinant in some part of candidate key is the same that to state that not all its attributes are necessary for unique identification of corteges. This statement contradicts the definition of candidate key.
Relational data integrity is provided by foreign keys in child relations. Child relations are relations with foreign keys that have values that refer to candidate keys values in parent relations.
Reference integrity is provided if the child relation foreign key value refers to existing value of candidate key of parent relation or is determined by NULL determinant.
The possibility of NULL determinant presence among attributes values that are used as foreign key is determined with demands of users. For example, if user considers that information about position is not important in the column «Position code» of relation «READER» there can be either NULL determinant or primary key value that has a position in attribute “Cod” of relation «POSITION». If user considers that information about position is significant then in the column «Position code» can not appear NULL determinant.
INSERT operator permits to add new row to the table with name that you indicate. It gives the possibility to calculate table columns that in the new raw will contain values different from values accepted on default. If on the domain level or in the CREATE TABLE operator values on default are not determined, then in the cells of new raw that belongs to the column outside the list, NULL determinant will be mentioned. Quantity of columns and add-on values that are calculated in the operator must concur. Even if one value will be outside the definitional domain, the INSERT operator will be rejected and DBMS will send the appropriate notification.
The order of new rows to the table is random. It happens in such way because it is impossible to add the row with foreign key value that refers to candidate key value in the parent table to the child table, until the row with some values of candidate key is not append to parent table. To eliminate possible errors you should add new rows to the table keeping order of their creation in the DB by CREATE TABLE operators.
For the determination of unique value of the primary key Interbase DBMS applies mechanism of generators. For each attribute, that is foreseen to be used as primary key, generator is determined. This is implemented by CREATE GENERATOR. There you can determine step that will split previous and new values of candidate key. Operator
SET GENERATOR {Ім'я} TO {Початкове значення}
permits to determine value that is the beginning of generator’s work. In other DBMS mechanism of generators is a part of CREATE TABLE operator, where it is possible to indicate the incremental data type for the column of the table.
UPDATE operator changes values in the rows of the table which name must be indicated. It permits to change the value in any quantity of the table columns. The same as for INSERT operator, when the new value goes out the definitional domain DBMS generates a notification about the error and the action of UPDATE operator will be cancelled.
Quantity of the table rows where the UPDATE operator will work is determined by search terms. In most cases definitional domain of the primary or candidate keys of the table are indicated in the search terms. Data is updated in those rows where the logical search clause is true. If you do not determine the search terms for the rows that must be updated all data in all rows will be changed.
Deletion of the row from the table is implemented by DELETE operator. The search terms are carried out the same as for UPDATE operators. If search terms are not determined, all rows of the table will be deleted.
This mechanism, as one of methods to provide data reference integrity, is implemented by CREATE TABLE operator. It gives the possibility to designer to determine the operation that must be carried out by the system while running operators UPDATE or DELETE that try to delete or update the value of candidate key of the parent table that is referred to by one or several rows of child table.
SQL language foresees four possible reactions either for updating or for deletion of candidate key value from parent table when it is referred by foreign key values from child table rows. Version of the operation must be indicated separately for operator UPDATE or DELETE when foreign key is determined in the child table. Operations are indicated after key phrases ON UPDATE or ON DELETE. They are determined according to requirements of users that were received on the stage of DB logical designing.
If we indicate word CASCADE after ON UPDATE then cascading update of the foreign key values in the rows of child table when UPDATE operator changes correspondent candidate key in the parent table. If we put CASCADE after ON DELETE then the execution of DELETE operator, that deletes row with value of candidate key in the parent table, will cause deletion of child table rows where foreign key value concurs with value of the candidate key.
SET NULL phrase that is put after ON UPDATE or ON DELETE tells the system that after deletion or changing candidate key value in the parent table it is necessary to put NULL determinant in all rows of child table where values of foreign keys refers to deleted or modified candidate key value.
Next possible answer of DBMS on the deletion of candidate key value from parent table is putting to all foreign key attributes values on default. For this it is necessary to put SET DEFAULT after phrase ON DELETE. We have to remind that for each attribute it is possible to indicate values that are inserted on default or during initialization of domains, or directly in the CREATE TABLE operator.
NO ACTION – is a proposition that is put after ON UPDATE or ON DELETE determines forth version of DBMS reaction on the modification of the candidate key value in the row of parent table or deletion of such row. In such case system generates notification about error as it ruins data integrity.
For example, the attempt to delete the row from the table «POSITION» (fig.3.4.) can cause the situation when value of foreign key will refer to the value of primary key that doesn’t exist. However deletion of row from the table «POSITION» will be held without any problem if there will be no reference from the table «READERS». Then data integrity is not ruined.
If there is no phrase ON UPDATE or ON DELETE in the definition of foreign key in CREATE TABLE operator that describes structure of child table, mechanism of reference integrity support in case of candidate key deletion or updating in the parent table is carried out the same as in case of NO ACTION phrase indication.
Content of «LIBRARY» DB that will be used for examination of data sample methods is presented in appendix B.
The success of DB physical implementation directly depends on results of logical modeling. Choosing destination relational DB you should remember rules that determine its membership of a really relational system. This will help to apply first and unique standard of language that gives possibility to determine and handle relational DB, – SQL language.
© 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.