Basic tutorial 3: advanced database model representation

The process of data modeling and implementation can be roughly described as follows: Thinking-> advanced design-> relational database model-> relational DBMS. If you use the powerdesigner tool, this process can be described as creating CDM> converting to PDM> generating DBMS implementation. This section focuses on the presentation of the advanced design and the method of switching to the relational database mode.

For how to use the powerdesigner tool, see here: http://www.cnblogs.com/yxonline/category/89658.html

For an introduction to business modeling, see here: http://www.cnblogs.com/DBFocus/archive/2011/10/12/2208580.html

The earliest method is the erdiagram, and the recent mean is to use UML. Either method must be determined before conversion to the mode.

4.1 e/R Model

4.1.1 Basic Concepts

E/R diagram is a symbol used to describe the database mode.

Entity: An abstract object. A collection of similar entities forms an entity set. For student tables, each student is an entity, and the student set is an entity set.

Attributes: attributes of an object, such as the student's name and gender.

Contact: the connection between two or more entity sets. Such as the relationship between students and classrooms.

Object contact graph: a graph that describes object sets, attributes, and links. A rectangle represents an object set, an elliptical shape represents an attribute, and a diamond represents a contact.

This example introduces the core concepts of the E/R diagram.

4.1.2 diversity of Contacts

1: n relationship if any entity in entity set E has a relationship with multiple entities in entity set F, one-to-multiple relationship exists between E and F, A producer can produce multiple movies.

Relationship if E and F are 1: N and N: 1, there is a 1-to-1 relationship between E and F.

M: N relational entity set E entity and F entity can be associated with any number of objects. For example, a star can appear in multiple movies, and a movie is played by multiple stars.

In the E/R diagram, → represents the diversity of the relationship. If the relationship between E and F is as many as 1, the arrow points to F. A movie is produced by a producer without joint production. For a relationship, both ends of the relationship point to the corresponding entity. It is worth noting that arrows indicate at most one, not necessarily exist.

In the same link, an object may appear multiple times. At this time, draw two lines, but mark the role of each line to show the difference.

4.1.3 multiple contacts

In addition to binary associations, there are more complex multi-dimensional associations, which can still be represented by diamond. In addition, the contact can have its own attributes to indicate information related to this association.

Some data models require that the connection be binary, such as UML. E/R does not limit the number of associated duplicates, but it is necessary to know how to perform this conversion. Define the connected object set as the object set of multi-link tuples. For each object set of the original multi-channel contact, multiple-to-one contact is introduced from the connected object. Because the connected entity set assumes multiple roles, each role is a contact.

4.1.4 subclass in E/R Model

Similar to object-oriented objects, some entity sets may contain public entities, but each of them has some different entities, which can be extracted as public entity sets. The ISA relationship can also be used here. In the figure, we use triangles. One side of the triangle is connected to the subclass, and the angle relative to the edge is connected to the parent class.

4.1.5 key representation

The keys of an object set have the same concept as those of a link, and are not duplicated. In the E/R diagram, the key attributes are underlined. When an object has multiple keys, it is only marked under the primary key. There is no way to represent multiple keys.

4.1.6 expression of reference integrity

F indicates that the F object must exist in batches with arrows.

4.1.7 weak entity set

If the key of entity set E is composed of parts or all attributes of another entity set. There are two types of entity set sources.

First, the entity of E is a part of the entity in set F. If you only consider that the decoration of the entity name is not unique, you need to consider the name of the entity to be determined. For example, for TV Series Episode information e, each TV series may be numbered from episode 1st to episode N, but other TV series also have such names. In order to name a unique residence information, you must consider the name of the TV series to which the series belongs and its own name.

Second, the connected entity set described above-as a method to eliminate multiple connections. These object sets usually do not have their own attributes. Their keys consist of the keys of the entities they connect.

A double rectangle is used to represent a weak entity set, and a double ring is used to represent a many-to-one link. This helps provide a key for a weak entity set. Underline the attributes that provide keys for weak object sets.

Weak entity set E is composed of several key attributes of its own, from E to the multiple-to-G connection of other entity sets. These many-to-one connections are called the support links of E, and the entity set arriving from E is called the support entity set.

4.2 From E/R diagram to link Design

4.2.1 Design Principles

Faithfulness: The design should be faithful to the specific requirements of practical applications. For example, the teaching relationship between the course and the instructor may be in the form of N: 1 or N: M, it depends on whether in reality multiple mentors are allowed to teach this course to record.

Avoid redundancy: each task is expressed only once. redundancy can cause exceptions.

Simplicity: do not add more components to the design unless necessary. Although the added components are theoretically correct, being redundant in practical applications will lead to complexity and error-prone.

Select the correct link: the object set can be associated in different ways, but you must consider using the most appropriate way to express the relationship between entities. For the relationship between the production contract in the figure above, whether an additional connection is required between the film and movie stars depends on the actual situation. If some movie stars do not have information about the movie stars in the contract, an additional relationship is required: stars play movies.

Select the property or entity set/contact: can we remove this relationship from the previous relationship of the film producer, but directly use the name and address of the producer as the property of the film? The answer to this question is no. Doing so will lead to an exception in updating and deleting. However, it is appropriate to write a name into a movie without an address. To solve this problem, entity set E can put all attributes of E into another entity as attributes without link association under the following conditions:

1). All e-related relationships must have arrows pointing to E, that is, e must be one of multiple-to-one connections.

2). If e has multiple attributes, all attributes constitute the unique key of E.

3). No contact contains e multiple times.

4.2.2 conversion from E. R to link

For any non-weak entity set, you can create a relationship with the same name and the same attribute set.

To convert a link to a link with the same name, add the attributes of this link. For each object set involved by R, add their key attributes as part of the link.

1). Process link combinations

For the multi-to-one relationship between entity set E and F, if direct conversion is performed, two entity relationships are generated, plus one link. Because it is a multi-to-one relationship, the key of the one-to-one relationship can determine the unique value of the attribute. Therefore, the three relationships can be merged into two relationships. One of these links is converted from the f object, and the other includes all the attributes of E, the key attribute of F, and any attribute of R.

For example, for the above movies and producers, movies and relationships can be combinedMovie(Title,Year, Length, gnera, studionname), the producer is convertedStudio(Studionname, Address)

This is true even when there are multiple many-to-one links from E to other entity sets. This improves the query efficiency.

2) Processing weak entity sets

If W is a weak entity set, the mode after W is converted into a link is composed of all attributes of W and all attributes associated with W, support for each connection W, that is, the many-to-one connection from W to E, must contain all key attributes of E.

You do not need to construct a link for the supported connections connected to W.

For example, the relationship between the description of the above series and the TV series isSerialdesc(Serialno,Serialname
DESC ),Serial(Serialname, Director ).

3) process the subclass Structure

There are three policies that can convert an ISA layered entity set to a link.

First, follow the E/R viewpoint. Creates a link for any object set in the hierarchy. It contains the root key attributes and its own attributes. For example, the inheritance relationships for movies can be converted as follows:Movie(Title,Year, Length, genre ),Cartoons(Title,Year, DESC ). The ISA contact cannot be created as a link.

Second, consider an object as an object of a single class. For each child entity, create a relationship that contains all attributes of all entity sets in the Child tree. For example, the above Inheritance needs to be converted into two relationships, each of which contains all their attributes,Movie(Title,Year, Length, genre ),MovieCartoons(Title,Year, Length, genre, DESC)

Third, use null values. Create a relationship that contains the attributes of all object sets in the hierarchy. Each object is represented by a single tuple. If the object has no attributes, the corresponding component of this tuple is set to null. Here we only need one table:Movie(Title,Year, Length, genre, DESC), which contains all attributes.

The above three methods have their respective advantages and disadvantages. The main points are listed below.

First, because queries involving multiple links are expensive, people are more willing to search for all the attributes required by a link. The null value method has good performance at this point.

Second, sometimes to avoid repetition. Object-oriented method. Each link only contains its own attributes, occupying as little space as possible. In the null value method, a large number of blank fields will waste a lot of space.

4.3 UML

In UML, entities and links can both represent the relationships between classes. After understanding E/R, it is easier to understand UML.

Key representation: In the class diagram, a primary key is added after the attribute that represents the key, indicating that it is the primary key.

Description: a straight line between two classes indicates Association. The associated names are written online below. You can add M .. n at both ends of the line to declare the degree and reference relationship. If the contact has its own properties, it is represented by the association class. If the link has a many-to-one relationship, you can use the clustering and combination attributes on the connection, and the online endpoint is represented by a diamond.

Self-Association: if the two ends of the Association connect to the same class, it is called self-Association. To differentiate roles in an association, write a name for the two sides of the association.

E/R subclass adopts the standard oo representation in UML.

4.4 UML diagram-to-Link Design

The key point is the conversion from E/R to link, which is not repeated here.