Introduction to E-R model, E-R analysis and e-r Model Analysis

Introduction to E-R model, E-R analysis and e-r Model Analysis

1 Overview

1.1 Role of the ER Model

Taking the example of a building house, the role of architects, construction engineers, and raw material suppliers is illustrated, and the roles of database planners, database operators, and database management systems are emphasized, it is pointed out that the ER model is a relational model-based overall database planning method.

1.2 concepts of ER Model

The model is derived from the data conceptual model, indicating that the ER model is a conceptual model design method.

ER comes from the English word "entity" and "Link", indicating that it is mainly used to draw the two legends for expression and design. Designed by Chen Pingshan, a Chinese American. Codd's paradigm method is not suitable for global design. In addition, there are other conceptual model design methods.

1.3 ER Model Design Method

Starting from the user demand survey, combined with the case description of the student status information system.

1) Investigate user requirements and obtain basic requirements, such as "Searching and modifying basic student information" and "Searching and modifying Student Score information ".

2) identify the data content in user requirements, ignore functional content, and investigate again to determine the meaning of the data, such as what is the basic information of students. The following data is obtained: Student ID, name, gender, birthday, height, course name, and score. To determine whether the data is qualified, you only need to determine whether the data can be subdivided again (Criterion 1 ). The final data obtained is called an attribute.

3) Put all information into a link. Although the function is feasible, the redundancy is too large, which may lead to inconsistency.

4) use the "split" method to segment the relationship by Semantic Link.

The five attributes "student ID, name, gender, birthday, and height" are used to identify the meaning of "student" and create a new relationship. The student ID is used as the primary key.

The course name identifies the meaning of the course, creates a new link, and adds the primary key of the course number. It also shows the meaning of change in information construction.

The score attribute cannot establish a score entity because it cannot exist independently (Criterion 2 ). On the other hand, it is related to the existing relationship.

This indicates that the "score" attribute cannot be classified into any of the preceding two relationships, because the attribute value is not unique for each entity in the preceding entity set (Criterion 3 ).

Emphasize that the "score" attribute is the link between the student and the course, that is, the two entity primary keys will be taken to create a new relationship, and the "score" attribute will be placed in this relationship.

The final link is as follows:

Student (student ID, name, gender, birthday, height)

Course (course No., course name)

Electives (student ID, course number, score)

2. Draw an ERTU

Briefly describe the modeling method based on the student information system case and describe the corresponding erdiagram.

Description of entities, attributes (including primary attributes), and links.

Emphasize two major aspects of ER Model Design: one is to express the requirement through ER, and the other is to convert ER into a relational model. ERTU is a theoretical method summarized from practice. It can guide practice behavior again. It can be used as a means to understand and express user data requirements, and also constitute a basis for generating a relational model.

Describe the rendering of the ERTU Based on the film company case.

3. How to convert an erimage into a relational model

Explain the correspondence between the ERTU and the generated relationship model from the case study of the student status information system:

1) An object directly corresponds to a link. All attributes of an object become the attributes of the link, and the primary attributes of the object become the primary attributes of the link.

2) A link corresponds to a link. This link first combines all the object primary attributes associated with the link to form its own primary attributes and add its own attributes.

Name of the link corresponding to the object and the contact. For example, you do not need to use Chinese characters.

Follow these steps to convert the case ERTU of a movie company into a relational model (add the property yourself ):

Movies (mid, title, year, length, type)

Stars (stid, name, address)

Studios (sdid, name, address)

MovieStar (mid, stid, salary)

MovieStudio (mid, sdid)

It indicates that the MovieStudio relationship can be merged into Movies in the above practice, and the reason is that this relationship is 1: N.

Repeat the usage of the merger Based on the student and class questions, and explain how to generate a relational model using the 1: N link. The method is as follows: add the primary attribute of an object whose contact value is 1 after an object with multiple contact values.

Describes the three types of contact, and emphasizes the relationship covered. The reason why many-to-many connections are created is that they are split into two one-to-many connections, while one-to-many connections can be processed according to many-to-many connections, however, merging is simpler.

Convert the ERTU into one of the classroom exercises of the relational model (add attributes by yourself ):

Result:

Student (student ID, name, gender)

Course (course number, course name, instructor number)

Instructor (Instructor ID, instructor name)

Reference books (book number, title, course number)

Student Course (student ID, course number, score)

The one-to-one link conversion method is to add the primary attribute of another object after any associated object. When necessary, you must decide the Selection Based on query requirements and other functions.

The second class exercise for converting an ERTU into a relational model (add attributes by yourself ):

Result:

Student (student ID, name, gender)

Course (course number, course name)

Instructor (Instructor ID, instructor name)

Reference books (book number, book title)

Course instructor reference book (course No., instructor No., book no)

Student Course (student ID, course number, score)

Student Management (student ID, class committee number)

(Note: If the student management is, you should add the "class committee number" after the student relationship)

4 ERWin Introduction

This article introduces the advantages of common database modeling tools such as ERWin (Visio, PowerDesigner, and Rational), which are simple in drawing and easy in conversion.

4.1 install and register

4.2 ER draw, pay attention to the association Entity generation

4.3 Use of forward engineering, that is, converting from logical model to Physical Model

4.4 use of reverse engineering, that is, converting from physical model to logical model

5. Planning of the ERTU

Classify the obtained attributes into entities or links by using the previous simple principles.

There are two main rules:

1) attributes cannot become a new entity if they cannot exist independently.

2) attributes are classified into an object based on a unique value for each instance of the object.

Explain the design methods based on contract cases, such:

The erimage structure can be described as follows:

Contract (Contract No., order date, manufacturer name, vendor address)

Goods (goods No., goods classification)

Contract goods (Contract No., goods No., order quantity)

In addition, there is also a way to regard the vendor as an entity, and the corresponding ergraph structure can be described:

Contract (Contract No., order date, vendor No)

Goods (goods No., goods classification)

Vendor (vendor ID, vendor name, vendor address)

Contract goods (Contract No., goods No., order quantity)

Comparison of the two methods:

1) there is redundancy in the first method (the same vendor has the same address), but there are few entities and the processing is simple. In the future, the standard structure can be obtained using the paradigm analysis method. Therefore, fewer entities are allowed.

2) The second method needs to examine the relationship between the vendor entity and the contract and the goods, and find that there is a one-to-many relationship with the contract, but there is a many-to-many relationship with the goods, this significantly increases the processing complexity. In fact, we can consider removing the relationship between the manufacturer and the goods (we can infer from the remaining relationship, but other relationships cannot be omitted ).

Explain the design method based on the case study of the School Information System, for example:

The school has many different departments. Each department has several teaching and research sections, and each Teaching and Research Section has several teachers. Some professors can bring several graduate students, but each graduate student can only have one tutor; each department has several classes. Each class has many students. Each student takes many courses and can be selected by multiple students.

The erimage structure can be described as follows:

School (school No., school name)

Department (Department No., Department name, school No)

Teaching and Research Section (Teaching and Research Section No., Teaching and Research Section name, Department No)

Instructor (Instructor No., instructor name, title, Teaching and Research Section No)

Class (class number, class name, Department number)

Student (student ID, name, class number, education level, tutor number)

Course (course number, course name)

Student Course (student ID, course number)

In addition, there is also a way to separate professors and graduate students as entities, and the corresponding ergraphic structure can be described:

School (school No., school name)

Department (Department No., Department name, school No)

Teaching and Research Section (Teaching and Research Section No., Teaching and Research Section name, Department No)

Instructor (Instructor No., instructor name, Teaching and Research Section No)

Professor (Professor number)

Class (class number, class name, Department number)

Student (student ID, name, class number, education level)

Graduate Student (graduate student number, tutor number)

Course (course number, course name)

Student Course (student ID, course number)

Comparison of the two methods:

1) The first method has fewer entities, but there are many null values in the title of the teacher entity, the educational qualifications of the student entity, and the tutor number, it is a good choice when there are few null values.

2) Although there are many entities in the second method, there is no such null value problem. For this type of subclass problem, pay attention to the corresponding processing method, that is, only the required primary key information is left in the subclass, all information can be obtained directly from the parent class, so that the query is easier and easier.

 

6 subclass Problems

Express the entity information of a movie in the movie company database, and express the information of the leading character dubbing actors of a cartoon film.

This is a subclass problem, because cartoon movies are a subclass of movies.

Method 1: Treat the Child class as the content of the parent class.

The relational model corresponding to the above practice is:

Movies (mid, title, isCartoon, sdid, did)

Studios (sdid, name)

Dubbers (did, name)

Note:

1) can express query requirements

2) For non-cartoon films, did is mostly empty

3) it is not applicable to scenarios with many sub-categories, such as the finale query requirements for the introduction of affectional film.

The corresponding link model is:

Movies (mid, title, isCartoon, sdid, did, isAffectional, finale)

Studios (sdid, name)

Dubbers (did, name)

The did and did information of most movies are empty.

Method 2: Separate sub-classes

The corresponding link model is:

Movies (mid, title, sdid)

Studios (sdid, name)

Dubbers (did, name)

AffectionalFilms (mid, title, sdid, finale)

Cartoons (mid, title, sdid, did)

Note:

1) there are not many null values

2) separated storage of love films, cartoons, and general movies is not convenient for common searches that do not distinguish movie types (more table merge operations are required)

Method 3: the Child class is expressed separately, but the parent class has all the record information. The Child class only has the primary key information and its own special information. This method is generally the most effective.

The improved link model is as follows:

Movies (mid, title, sdid)

Studios (sdid, name)

Dubbers (did, name)

AffectionalFilms (mid, finale)

Cartoons (mid, did)

 

 

Summary:

General steps for ER Model Design:

① Customer survey, understanding requirements, and collecting data

② Subdivide data into basic data items that cannot be further divided based on user needs

③ You can put all the information (or attributes and fields) into a link. Although the function is feasible, the redundancy is too large, which may lead to inconsistency. Use the split method to break down a link by Semantic Link (entity-link. Identifies an entity based on the original information obtained from the customer, put the attribute into the object in sequence (the principle is that only the property is uniquely identified for the object can be attributed to the object, otherwise you can see whether it can be placed in other entities ). Note that some attributes are the contact attributes. In this case, you must establish the contact between entities and add an attribute to the contact.

Transform rules from E-R model to relational model:

① When a table is created for each object, the attribute of the object changes to a field of the table. A primary attribute is required.

② All the many-to-many connections should also be converted into one table. If there is a multi-to-one connection, do not create a new table, add the primary key of the object whose contact value is 1 after an object with multiple contact values. If the contact value is one-to-one, add the primary key of another object to the object.

References:

1. http://www.njcie.com/bbs/dispbbs.asp? Boardid = 19 & Id = 412 & page = 8

2. http://www.njcie.com/bbs/dispbbs.asp? Boardid = 19 & Id = 1163 & page = 2

3. http://www.njcie.com/bbs/dispbbs.asp? Boardid = 19 & Id = 1166 & page = 2

4. http://www.njcie.com/bbs/dispbbs.asp? Boardid = 19 & Id = 1173

Video (enter the address of thunder)

Http://www.njcie.com/DB/files/41.wmv

Http://www.njcie.com/DB/files/42.wmv

Http://www.njcie.com/DB/files/43.wmv