Database Design - Informatics for Economists

Database Design

Designing a database is a long, time-consuming and poorly formalized process, on which the viability and effectiveness of the projected database depend, its ability to develop. If you have poorly designed the database, this will lead to the need to constantly re-do it.

The result of the design should be the tables that represent the objects and their interrelationships.

Only normalized relationships are allowed in a relational model.

We introduce a number of definitions.

Definition 1. The ratio is in the first normal form (1NF) if and only if all the elements contained in it contain only atomic (indivisible) values.

This definition simply states that any normalized ratio is in 1NF.

The notion of functional dependency is the most important concept (inside a relationship).

Definition 2. If the ratio R, is specified, the Y attribute R functionally depends on the X attribute R if and only if each value of X in R is associated with exactly one value Y .

For example, the relation Goods ( Item Code, Name, Brand, Price) is given. Product Code is a key attribute. Each value of the attribute of the Item code corresponds exactly to the value of the attributes Name, Brand, Price. In other words, knowing the value of the Item attribute, you can unambiguously determine the values ​​of the remaining attributes of this product. Hence, the attributes Name, Brand, Price functionally depends on the attribute Item code. It is possible to represent the functional dependence using a diagram (Figure 10.4).

Functional Dependency Chart

Fig. 10.4. Function Dependency Chart

In the case of a composite key, the concept of functionally complete dependency is introduced.

Definition 3. The Y attribute is in the full functional dependency from the X, if it is functionally dependent on X and does not function functionally from any subset of the X attribute ( < strong> X - composite key).

For example, as shown in Fig. 10.5, the Quantity attribute is in the full functional dependency from the composite key Item code and Warehouse number.

Let's give another example (Figure 10.6).

Example of full functional dependency

Fig. 10.5. Example of a full functional dependency

A generalized example of a functional relationship

Fig. 10.6. A generalized example of a functional relationship

The Number attribute is in full functional dependence on the composite key Item code and Warehouse number. Attributes Name, Brand, Price are not fully functional depending on the composite key Item code and Warehouse number, since these attributes are functionally dependent on the part of the composite key, namely the attribute of the Item code.

In this case, when designing this ratio is divided into two (Figure 10.7).

Splitting a relation into two in the case of different functional dependencies

Fig. 10.7. Split the ratio into two in the case of different functional dependencies

Definition 4: The Y attribute is in the transitive dependency of the X, if it is functionally dependent on the Z, attribute and the Z attribute is functionally dependent on the X.

In Fig. 10.8 attribute The address of the company is in a transitive relationship with the attribute of the warehouse number.

In this case, when designing this ratio is divided into two (Figure 10.9).

Definition 5. The ratio R is in the second normal form (2NF), if it is in 1NF and each non-key attribute functionally depends from the primary key.

Transitive dependency example

Fig. 10.8. Example of transitive dependency

Splitting a relationship into two in the case of a transitive relationship

Fig. 10.9. Splitting a relationship into two in the case of a transitive dependency

Definition 6. The ratio R is in the third normal form (ZNF), if it is in 2NF, and any non-key attribute depends from the key is not transparent.

Definition 7. The ratio R is in the fourth normal form (4NF), if it is in the ZNF and each relationship tuple consists of the value a primary key that identifies an object, and a set of mutually independent arbitrary attribute values ​​that describe the object in some way.

4NF contains a very simple and generally available idea. The concept of 4NΦ can be formulated on the intuitive level as follows: "One fact is stored once."

The design process is the process of transforming forms, i.e. the process of bringing an arbitrary relation to an equivalent set of relations in the fourth normal form (4HΦ). The design process can be broken down into several steps.

Step 1. The arbitrary ratio is reduced to the first normal form (1NF).

Step 2. The ratio in 1NF is reduced to an equivalent set of relations in the second normal form (2NF).

Step 3. The ratio in the 2NF is reduced to an equivalent set of ratios in the third normal form (3NF).

Step 4. The ratio in the 3NF is reduced to an equivalent set of ratios in the fourth normal form (4NF).

In practice, the normalization process, as a rule, ends at the stage of bringing the ratio to the ZNF.

The most important problem to be solved when designing databases is the creation of such a structure that would ensure minimal duplication of information and simplify the procedures for processing and updating data.

The introduction of normalization of relations in the development of a database model ensures its operability. This does not at all mean that an abnormalized conceptual model will necessarily prove ineffective. Just an abnormalized model can cause some difficulties in the operation of the database and its modification.

Clarify the steps of normalization.

Step 1 consists in the creation of a two-dimensional table containing data elements.

The 2nd step of normalization is to allocate keys and attributes that depend on them. In order to bring the relation to 2HF, it is necessary to select groups of attributes that depend on the parts of the composite key. These groups can form separate relationships (tables). The separation from a relationship in 1NF of a relationship in which non-key attributes depend only on the key as a whole is called a reduction to 2NF.

Step 3 consists in separating the relationships in which the attributes are in a transitive dependency on the key in separate relationships.

The process of reducing the ratio of arbitrary form to 3NF is preceded by a large preliminary work on the creation of this primary or arbitrary relationship. This is no less important point in the design of the database.

Creating a database that would meet the current and prospective information needs of the enterprise, is associated with the need to design a conceptual model of the domain. The design of the conceptual model is based on the analysis of data processing tasks performed at this enterprise. The conceptual model includes descriptions of objects and their interrelationships. When designing a conceptual model, all efforts of the developer should be aimed at structuring the data and identifying the relationships between them.

Example 10.1 (database design).

It is required to develop a database for the sale of goods from warehouses by holding companies. The holding represents the union of four companies: Citilink, Dinikin, Elee, Lizarin. Each of these firms has several warehouses in Moscow, where the goods are stored. The range of goods is the same for the holding center. Any product can be stored in one or more warehouses; in each warehouse are stored various goods. Wholesale is carried out from warehouses. Each firm carries out sales only from its warehouses.

Solution

The study of the domain, the analysis of data, the establishment of relationships between data (the authors did not set out to consider this stage in detail) allowed to create a primary ratio (Figure 10.10). For the sake of clarity, not all fields are filled in the table.

Select the keys and the attributes that depend on them:

• Company name, Company address, Company phone number are functionally dependent on the Company code attribute;

• Attributes Store address, Warehouse phone, Company code (since each warehouse belongs to a particular firm) are functionally dependent on the attribute of the Warehouse number;

Primary relation for the warehouse implementation database

Fig. 10.10. Primary relationship for the warehouse implementation database

• Attributes Name, Brand, Price are functionally dependent on the attribute Item code;

• The quantity attribute (this is the quantity of the item stored in the given warehouse) is fully functional depending on the composite key Item code, Warehouse number;

• Attributes The number of goods sold (sold goods) and the discount are fully functional depending on the composite key Date of sale, Item code, Warehouse number.

Thus, five relations were formed from the primary relationship (Figure 10.11).

Relationships received from the primary, for the database selling goods from warehouses

Fig. 10.11. Relationships received from the primary, for the sale of goods from warehouses

Combination of zeros Product code, Warehouse number is defined in the Storage table. To avoid inconsistency of the data (you can not sell the product from the warehouse, if it is not stored there), create an ID field in the table Storage & quot ;, which will fix combination of the Product Code - Warehouse number, and we will use it in the "Sales" table.

Then we will finally have the following five relations (Figure 10.12). After that you can start creating the database "Sales". with MS Access.

Relationships for OBDs from Warehouses

Fig. 10.12. Relationships for the warehouse implementation database

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)