Selection of auxiliary entities - Databases: design

Extraction of auxiliary entities

The previously discussed entity Product Category contains the object attribute Subcategory & quot ;, which corresponds to a similar object of the domain. This was revealed in the analysis of the subject area. However, in order to apply the rule of normalization of this entity, it is necessary to determine the relationships between the attributes (Table 4.11).

The

Defining Attribute Relationships by Entity Product Category

Category IDF

Category name

1: 1

Subcategory

W

1: LH

-

IDF

categories

Description

categories

Subcategory

Typically, within a key entity, attributes of simple types will be linked one-to-one (1: 1), confirming their correctness of reflection in the entity in question. For other attributes, the primary key connection that characterizes each particular instance of the entity ( Product Category ) is of primary importance. Considering this attribute, the developer indirectly talks about the whole object, with the whole set of its main attributes (Table 4.12).

Table 4.12

Substantiation of entity attribute relationships

Considering the relationship between the attributes Category name and Subcategory since the "Category name" attribute is essentially a symbolic reflection of the IDF ID attribute of the category & quot ;, and the presence between them (IDF category and Category name ) links one to one says That the links between the IDF - Subcategory and Category name - Subcategory have an identical meaning.

Apply the normalization rule for the Sub-category (Figure 4.27), represented by a multi-valued dependence on the IDF attribute, we see that the subcategory should be represented by a new entity whose attributive composition is defined in the framework of business process modeling when selecting the "Subcategory" object and the connection from the entity " ; Product Category .

Q Product Category

Q Subcategory of goods

$ & pound; IDF categories: SERIAL

H-o & lt;

IDF subcategory: SERIAL

fV Category name: VARCHAR (150) [AK]

Subcategory: VARCHAR (150) [AK]

o-IDF category: INTEGER [FK]

Fig. 4.27. Normalization result by the attribute Subcategory

As in the case of the entity Product Category & quot ;, in essence Product Subcategory there is an identifying attribute Name of the subcategory represented by the character data type, which indicates that it is not appropriate to use it as a primary key (Table 4.13). In the light of this, a surrogate primary key is created IDF subcategories & quot ;, and the attribute Subcategory name will be specified as an alternative key.

Table 4.13

Description of the attributes of the entity Product Subcategory

No.

п/п

Entity

Attribute

Type

Data

Algorithm

Default

Limitation

NULL

1

І Іодка -

categories

products

Category name

With (150)

Description of the entity Product subcategory is considered only by attributes directly related to the object (Table 4.14). Therefore, the description table contains only one attribute Name of the subcategory .

Table 4.14

Description of the entity keys Product Subcategory

No.

п/п

Attribute

Key Type

Type

Data

Unique

nost

Optional

with

1

IDF subcategories

Primary

C (+1)

-

Surrogate

2

The name of the subcategory

Alternative

With (150)

Index

-

3

Category IDF

External

C

-

-

Since the appearance of the entity Product Subcategory is caused by the application of a certain rule of normalization, which led to the emergence of a non-identifying one-to-many relationship, the creation of a foreign key IDF category in essence Product subcategory and deleting the Sub-category from the entity Product Category & quot ;. As a result, in essence, the Product Subcategory there are three keys, as shown in the key description table.

Special attention should be paid to the foreign key attribute IDF category & quot ;. Unlike the similar attribute in the entity Product Category

this attribute is represented by another type of data - integer. This is explained by the fact that, by becoming a foreign key, the attribute loses the surrogate auto-fill key property and obtains properties of the usual attribute, including the data type, which must match the original one (integer with auto fill by the rule of arithmetic progression). As a result, the foreign key has received the appropriate data type.

Explore the Products Considering the function "Account of goods directory", the developer defines the entity "Goods". (Figure 4.28).

Fig. 4.28. The result of modeling the key entity Products

The

This entity contains one object attribute, the "Subcategory", meaning that there are subcategories in each category, but, unlike the previous variant, with the direction of the link to the identifying attribute (Table 4.15).

Table 4.15

Defining Relationships for the Entity "Products"

IDF

Item

Description

Item

1: 1

ІІІІna goods

1: AG

1: LH

The Iodate of the Rock

1: LH

1: LH

-

Goods IDF

Description

Item

Product Price

The Iodate of the Rock

The

The

In essence, "Products there are two attributes pertaining directly to the "Goods" object, one of which is the "Product Price", is associated with the identification attribute by a one-to-many relationship in the direction from the identification key (Table 4.16). As the price is a characteristic attribute of the object and has the data type "Currency"; (Money), derived from a numeric data type, which can be represented by a simple type, besides, by a set of values ​​existing in the domain, can not be represented as a classifier, because it is impossible to determine in advance all possible variants of values, then its it is not appropriate to consider separately from the "Goods" object.

The

Justifying the links between the attributes of the entity "Products"

Attributes

Justification

IDF

Item

Price

Item

( IDF Littl, A

1) 1: Champions League I The price of the goods) V category У 1 1 У

One unit of goods can be valued only by one price. But a specific price can characterize different goods

IDF

Item

Subcategory

(IDF | 1: L 1 (Subcategory)

V category У 1 ------------ 1 V У

Specifying a subcategory characterizes the grouping of goods and involves the placement of several different products within the same subcategory. Each item in the directory must belong to only one subcategory

The

The

It is pointless to consider the relationship between the price of a product and its subcategory, since this relationship is not direct, and the subcategory is not related to the price in any way and vice versa. Consideration of the links of the name of the goods with the price and subcategory is also not required, since it is uniquely linked to the surrogate key and is its symbolic reflection.

Applying the normalization rule to the IDF product bundle - Subcategory leads to the need to create an entity that corresponds to the Subcategory (Figure 4.29). Since an entity for this object has already been created, there is no need to create such an entity. This and the related entity Product Category we place it on the diagram for the function under consideration. The entity Product subcategory & quot ;, in accordance with the established communication type, is associated with the key entity, forming instead of the attribute "Subcategory foreign key IDF subcategory .

Exploring the object "Order". Within the Filling order with goods the customer must make the order for the ordered goods. When the function was considered, the entity "Order" was selected. with attributes (Figure 4.30), characterizing its content.

In this entity, two object attributes are distinguished: Client and "Items", which will be represented by auxiliary entities corresponding to the objects of the domain. The study of the principles of the work of the business process for filling the order makes it possible to understand the necessity of indicating the necessity of filling certain attributes with data.

The

Fig. 4.29. The result of the normalization of the entity "Products"

The

When working with an order, the client first creates the order itself, and then fills it with the goods, indicating the quantity. This enables the developer to determine that the attributes "Goods", "Quantity" and " and Cost may not be filled when creating an order, as shown in the structure of the entity.

Fig. 4.30. The attributive composition of the key entity "Order"

The

Viewing the Client (Figure 4.31), using the normalization rule, leads to the creation of the entity "Client" corresponding to the identical object.

Fig. 4.31. Selecting the entity Client

The

At this time, the consideration of the entity Client It is inappropriate, because the client's order type is not considered in the filling order function. Proceeding from this, consideration should be made in the framework of functions forming the order for processing and subsequent implementation of information needs. While we will consider this essence, considering that the functions, where it is considered, have not been investigated yet.

Now examine another object attribute - "Products". For its consideration, it is necessary to determine the types of links with other attributes, and first of all with the key attribute.

Between the Products and order ID Many-to-many relationships are established (Table 4.17), which indicates that these two attributes can not be used in one entity. This is explained by the fact that the attribute Items is, first, object-oriented and must be separated into a separate entity, and secondly, its key attribute "Product Name" is symbolic. In addition, an order identified by the Order Number attribute associated with the Order ID a one-to-one relationship, is also represented by a character data type. The presence of two symbolic attributes in one entity with a many-to-many relationship leads to the idea of ​​the need for normalization, which is realized through the rule considered earlier.

Table 4.17

Defining relationships between the attributes of the entity "Order"

IDF order

Order number

1: 1

Order date

1: L

1: LH

Customer

: To

1: L

KM

Products

KM

KM

KM

KM

Number

KM

KM

KM

KM

KM

Cost

KM

KM

KM

KM

km

KM

IDF

Order

Number

Order

Date

Order

Customer

Products

Number

Cost

The

The

However, before normalizing, it is necessary to determine whether there are in fact attributes that are related to the goods, order or their bundle. Since the order is uniquely associated with the client, the attribute Client is a direct order characteristic, as well as the order date and its number. This indicates that when you select the entity "Order" As an independent element of the model, the corresponding attributes must be put into it.

With respect to other attributes, the analysis of the subject area indicates that the quantity and value of the goods in the order are characteristics of not the product itself, but its records in the order. Therefore, these attributes must remain in essence, where the order and goods will be linked.

In the course of applying the rule of normalizing an entity with a link, many - to many - entities were identified as "Orders (Figure 4.32) in which attributes that uniquely characterize a particular order migrated - "Order ID", "Order number", "Order date" and " and the foreign key of the entity Client & quot ;. In this case, taking into account the fact that the ID IDF attribute of the order is the surrogate key associated with the Order Number & quot ;, attribute for the Order Number An alternative key was created with the rule of securing uniqueness through the index. The choice of the rule is explained by the fact that by the attribute Order number will not only respect the uniqueness of the value, but also ensure the search for the corresponding order, which is revealed at the stage of the domain analysis, when the developer finds out what information needs the user can have.

Fig. 4.32. The result of the normalization of the entity Order

The

The entity "Goods", which should also be highlighted during normalization, was previously formed and associated with the corresponding domain object. Therefore, this essence is simply placed in the model and associated with the original entity, making up the linked ID IDF order - goods IDF & quot ;, creating the ability to specify multiple item items within a single order. Original attribute Products from the entity Order you can delete, and the essence itself - rename the name "Order goods", thus correcting the correspondence of the name of the entity with its semantic content.

The remaining two attributes are the Quantity and Cost - are characteristics of the goods within the framework of a particular order, which was revealed at the stage of analysis of attribute relationships and understanding of the essence of these attributes. Therefore, these attributes must remain in the original entity, they have changed properties, namely, the property of the possibility of storing the value "NULL" has changed. Previously, it was determined that these attributes can store an empty value, according to such a conclusion based on the fact of placing attributes in a single entity with the order and its characteristics. Redefining the source entity to a functional entity-a bundle that allows you to not have relationship instances when you have a sales order and goods in the product directory, has formed the ability to implement the rule of admissibility of storing an empty value in the Amount and Cost & quot ;, which indicates that there is no need to strengthen this rule by preserving the corresponding property of these attributes. This was done by changing the rule of the possibility of storing an empty value to prohibit such an opportunity. This is also justified from the point of view of understanding the rules for working with attribute values ​​in conjunction with order goods. This rule is that when ordering a product, you must specify the quantity and calculate its cost.

Correcting attribute properties allowed the incorrect properties of the "Quantity" attribute, which was that the attribute allowed the storage of an empty value and, at the same time, it was required to set a default value if the user did not specify this value. As a result, it turned out that the user started working with the order,

ns indicating no ordered items, which was determined by the empty value for the "Goods" attribute, and in the "Quantity" attribute, the default value of "1", which was defined in the attribute property, will already be entered. Now, if the property of the possibility of storing an empty value changes, the situation with the absence of goods in the order and the simultaneous availability of its quantity will be impossible. The situation is similar with the value, which is calculated by the formula, but in the absence of the value of the quantity of goods, this will be impossible. Changing the rule of the possibility of storing an empty value allowed this situation to be resolved without violating the rules of working with data in accordance with the limitations of the domain.

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)