E-r model, E-R analysis Introduction

1 Introduction

1. 1 effects of the ER model

The example of building houses illustrates the role of architects, builders and raw material merchants, emphasizes the role of database planners, database operators and database management systems, and points out that ER model is a database overall planning method based on relational model.

1. 2 concept of the ER model

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

ER comes from the English word " entity " and " contact ", It is mainly used to express and design the drawing of these two legends. Designed by Chinese-American Chen Pingshan. Codd 's paradigm approach is not suitable for global design, but there are other conceptual model design methods.

1. Design method of 3 ER model

From the user needs of research, combined with the case of the information system.

1) survey user needs, get a few basic requirements, such as " Find and modify students ' basic information "," Find and modify student performance information " .

2) Identify the data content in the user's needs, ignore the functional content, and re-investigate to determine the meaning of the data, such as what is the student's basic information. Finally, the following data are obtained: School number, name, gender, birthday, height, course name, grade. To determine if the data is qualified, simply determine if they can be subdivided again (guideline 1). These last-obtained data are called attributes.

3) Put all the information into a relationship, although the function is feasible, but the redundancy is too large, easy to bring the risk of inconsistency.

4) Use the " split " method to subdivide the relationship according to the semantic linkage.

" student", name, gender, birthday, height " five attributes identify the " students " semantics, and create a new relationship, Number as the primary key.

Identify The course name attribute as a " lesson " semantics, create a new relationship, and add the course number primary key. At the same time, it embodies the change meaning of information construction.

Description the score attribute cannot establish a score entity because it cannot exist independently (guideline 2). Conversely, it is linked to existing relationships.

Note the score attribute cannot fall into any of the above two relationships because the Item property is inflexible one (guideline 3) for each entity in the above entity collection .

Emphasizing that the " score " attribute is the attribute of the student's and course's contact, the two entity primary key is taken over to create a new relationship and the " score " property into the relationship.

Finally get the relationship as:

Student (school number, name, gender, birthday, height)

Course (Course number, course name)

Elective (school number, course number, grade)

2 rendering of er diagrams

Combined with the case study information system, the modeling method is described and the corresponding ER diagram is depicted.

Describes the representations of entities, attributes (including primary attributes), and connections.

Two main aspects of ER model design are emphasized: one is to express the requirement through er , and the other is to transform er into a relational model. ER diagram is a theoretical method which is summed up from practice, which can be used as a means to understand and express user's data needs, and also to form the basis of how to generate relational model.

Combined with the film company case Description of the ER diagram drawing.

A method of converting 3 ER diagrams to relational models

The correspondence relation between ER diagram and generative relation model is explained from the case of student information system, and the method is:

1) An entity directly corresponds to a relationship, all attributes of the entity become the attributes of the relationship, and the main attribute of the entity becomes the primary attribute of the relationship

2) A connection directly corresponds to a relationship, which first associates all the entity master attributes associated with the contact to form its own main property, and adds its own properties.

Emphasis on the relationship between entity and relation name naming problems, such as the use of Chinese characters, contact relations to express semantics.

Convert the movie company case ER diagram to a relational model (attributes added) as described above:

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

Stars (stid,name,address)

Studios (sdid,name,address)

Moviestar (Mid,stid,salary)

Moviestudio (Mid,sdid)

Note that the Moviestudio relationship in the above-mentioned practice can be incorporated into the movies, noting that the reason is that the link is 1:n.

Combining the questions of students and classes again illustrates the use of this merger, and explains how the 1:n relationship generates a relational model by adding a primary attribute of the entity with a contact value of 1 after the entity with the contact value is more than.

Describe the three types of contacts and emphasize the coverage relationship. Explains that many-to-many connections are created because they are split into two one-to-many, and a one-to-many connection is a special case of many-to-multiple connections, but merging is easier.

The ER diagram is converted to one of the class exercises of the relational model (attributes added by itself):

The result is:

Student (school number, name, gender)

Course (Course number, course name, teacher number)

Teacher (teacher number, teacher's name)

Reference Books (ISBN, title, course number)

Student Courses (School number, course number, results)

Description one-to-one contact the conversion method is to add a master attribute for another entity after any of the associated entities. Emphasize the need to combine query requirements and other functions to determine the choice.

ER diagram conversion to a relational model of classroom Practice II (attributes added by itself):

The result is:

Student (school number, name, gender)

Course (Course number, course name)

Teacher (teacher number, teacher's name)

Reference Books (ISBN, title)

Course Teacher Reference (course number, teacher number, ISBN)

Student Courses (School number, course number, results)

Student Management (School number, class committee number)

(Note: If the student management is 1:n, then the student relationship should be added after the "Class commission" can be)

4 Introduction to Erwin use

This paper introduces the advantages of common database modeling tools such as Erwin, such as visio,powerdesigner,rational, simple drawing and convenient conversion.

4. 1 Installation Registration

4. 2 The drawing of ER, note the generation of the contact entity (Associate entity)

4. 3 The use of forward engineering, that is, from the logical model to the physical model

4. 4 Use of reverse engineering, i.e. from physical model to logical model

5 Planning method of ER diagram

Classify the resulting attributes into entities or contacts using a few simple guidelines previously described.

There are two main criteria to follow:

1) A property cannot be a newly established entity if it cannot have the meaning of independent existence.

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

Explain the design method with the contract case, such as:

The corresponding ER diagram structure can be described as:

Contract (contract number, order date, Vendor name, vendor address)

Goods (cargo number, cargo classification)

Contract Goods (contract number, cargo number, order quantity)

In addition, there is a way to treat a vendor as an entity, and the corresponding ER diagram structure can be described as:

Contract (contract number, order date, factory name)

Goods (cargo number, cargo classification)

Manufacturer (vendor, vendor, vendor)

Contract Goods (contract number, cargo number, order quantity)

Comparison of the two methods:

1) The first method exists redundancy (the same vendor has the same address), but the entity is small, the processing is simple, and later the canonical structure can be obtained by using the Paradigm analysis method. Therefore, the entity can be less.

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 are many-to-many relationships with the goods, which significantly increases the complexity of processing. In fact, it is possible to consider removing the relationship between the vendor and the goods (which can be inferred from the rest of the relationship, but other relationships cannot be omitted).

Combining with the school information system case to explain the design method, such as:

The college has a number of different departments, each department of a number of departments, each teaching staff several teachers, some of which can take a number of graduate students, but each graduate student can only have one tutor, each department subordinate several classes, each class has many students, each student elective many courses, Each course can also be taken by multiple students.

The corresponding ER diagram structure can be described as:

College (college number, college name)

Department (department number, department name, college number)

Office (Department number, office name, line number)

Teacher (teacher number, teacher's name, title, office number)

Class (class number, class name, department number)

Student (school number, name, class number, education, tutor number)

Course (Course number, course name)

Student Course (School number, course number)

In addition, there is a way to treat professors and graduate students as entities individually, and the corresponding ER diagram structure can be described as:

College (college number, college name)

Department (department number, department name, college number)

Office (Department number, office name, line number)

Teacher (teacher number, teacher's name, office number)

Professor (Professor number)

Class (class number, class name, department number)

Student (school number, name, class number, education)

Postgraduate (postgraduate number, tutor number)

Course (Course number, course name)

Student Course (School number, course number)

Comparison of the two methods:

1) The first method entity is less, but the teacher entity's title, student entity's academic qualifications and the tutor number there are more empty values may, in the case of less empty value is a better choice.

2) The second method, although more entities, does not have the above null value problem. For such sub-class problems, pay attention to the corresponding processing method, that is, the subclass only has the necessary primary key information, all information can be directly from the parent class, so that the query is more simple.

6 sub-class issues

In the film company database to express the film's entity information, express the cartoon actor voice actors information

This is a sub-category, because cartoon movies are a kind of a seed in a movie.

Processing method One: treats the subclass as the contents of the parent class

The corresponding relational model for the above approach is:

Movies (Mid,title,iscartoon,sdid,did)

Studios (Sdid,name)

Dubbers (Did,name)

Description

1) can express query request

2) for non-cartoons, do more empty

3) Not applicable to more sub-categories, such as the introduction of Love (affectional film) outcome (finale) query requirements

The corresponding relational models are:

Movies (Mid,title,iscartoon,sdid,did, isaffectional, Finale)

Studios (Sdid,name)

Dubbers (Did,name)

You can see that most movies do and did information are empty

Processing mode Two: The subclass is expressed separately

The corresponding relational model is:

Movies (Mid,title,sdid)

Studios (Sdid,name)

Dubbers (Did,name)

Affectionalfilms (Mid,title,sdid,finale)

Cartoons (Mid,title,sdid,did)

Description

1) There are no more empty values.

2) Love films, cartoons and general movies are stored separately from common searches that do not differentiate between movie types (more table merging operations are required)

Processing method Three: The subclass is expressed separately, but the parent class has all the record information, the subclass only the primary key information and its own special information. This approach is generally the most effective.

The improved relational model is:

Movies (Mid,title,sdid)

Studios (Sdid,name)

Dubbers (Did,name)

Affectionalfilms (Mid,finale)

Cartoons (Mid,did)

Summarize:

The general steps for designing ER models are:

① Customer Survey, understand requirements, collect data

② segment data into non-sub-basic data items based on user needs

③ can put all the information (or attribute, field) into a relationship first, although the function is feasible, but the redundancy is too big, easy to bring the danger of inconsistency. Use the "split" method to subdivide a relationship by semantic linkage (entity-relationship). The entity is identified based on the original information obtained from the customer, and the attribute is placed in the entity in turn (the principle is that only the attribute is unique to the entity in order to attribute this property to the entity, otherwise see if it can be placed in another entity). Note that some properties are the properties of the contact, then the relationship between entities is established, and the connection is added to a property.

Transformation rules from the E-R model to the relational model:

① Create a table for each entity, the attribute of the entity becomes a field of the table, and a master attribute is set.

② all of the many-to-many links to a table, if it is a multi-to-one connection do not create a new table, after the contact value is more than the entity to add a contact value of 1 of the entity's primary key, if it is a one-off contact with the other entity will be added to the primary key.

Reference documents:

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 (Thunderbolt input address can)

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

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

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

E-r model, E-R analysis Introduction