Lecture 6. Designing relational databases

When designing a database, two main problems are solved:

• How to map domain objects to abstract data model objects so that this mapping does not contradict the semantics of the domain and be as good as possible (efficient, convenient, etc.)? Often this problem is called the problem of logical database design.
• How to ensure the efficiency of database queries, i.e. How, keeping in mind the features of a specific DBMS, should the data be located in external memory, what additional structures (for example, indexes) should be required, etc.? This problem is called the problem of physical database design.

In the case of relational databases, it is difficult to imagine any common recipes for physical design. Here too much depends on the used DBMS. For example, when working with Ingres DBMS, you can choose one of the proposed ways of physically organizing relationships; when working with System R, you should first think about clustering of relationships and the required set of indices, etc. Therefore, we limit ourselves to the logical design of relational databases, which are essential when using any relational database.

Moreover, we will not touch on a very important aspect of design - the definition of integrity constraints (with the exception of the primary key constraint). The fact is that when using DBMS with developed integrity constraints (for example, SQL-oriented systems) it is difficult to suggest any general approach to defining integrity constraints. These restrictions can have a very general view, and their formulation is still related to the field of art rather than engineering skill. The most that is suggested about this in the literature is an automatic consistency check of a set of integrity constraints.

So let's assume that the problem of designing a relational database is to make informed decisions about

• What kind of relationship should the database consist of and
• what attributes should this relationship have.