SQL Server Database Learning Note-conceptual data model

The conceptual Data Model (conceptual) is also known as the information model. It is the abstraction of objective things and their connections, the modeling of the information world, the first abstraction of the real world to the information world, and a powerful tool for database designers to design the database. The concept data model is free from the specific technical problems of computer system and database management systems, and focuses on analyzing the relationship between data and data, which is irrelevant to the specific database management system. The conceptual data model must be transformed into a logical data model to be implemented in a database management system. It emphasizes its semantic expression ability, that is, it can be more convenient and direct expression of the various semantics in the application just. This kind of model concept is simple, clear, easy to be understood by the user, it is the language of communication between the Database Designer and the user.

Pter Pin-shan Chen in 1976, the Entity-link method, referred to as the E-R model (entity-relationship models), is the most well-known representation of a conceptual model. This method uses the E-r diagram to describe the conceptual model of the real world. The E-R model can clearly express the semantics of the object being described, graphically describe the data and the relationship between them, simple, easy to understand, and easily converted into relational data models.

In order to adapt to the new application requirements, the extended entity Contact Model (Extend entity-relationship model) is proposed on the basis of the basic E-R model, which is referred to as "EER models". This model can represent more semantics, expand the concept of sub-type, and provide the mailbox tool for object-oriented database design.

1. Basic concepts of the conceptual model

Entities (Entity)

An objective and mutually differentiated transaction is called an entity. Entities can be specific people, things, and objects, or they can be abstract concepts or linkages. For example, a student is an entity.

Properties (Attribute)

Some of the characteristics of an entity are called attributes, each of which is called an attribute of an entity. The property must exist relative to the entity. For example, a student entity can be composed of attributes such as learning, name, gender, year of birth, affiliation, year of entry (20140505, Zhang Shan, Female, 1992, computer Department, 2014), which combine to characterize a particular student.

Code (key)

A property set that uniquely identifies an entity is called a code. For example, the student number is the code of the students entity.

Domains (Domain)

The value range of the property is called the domain of the property. For example, the field of the study number is a 6-bit integer, the domain of the name is a string collection, the age domain is an integer less than 28, and the gender domain is (male, female).

Entity type entity types are composed of entity names and attribute name collections to abstract and characterize the same class of entities. For example, the student (school number, name, gender, year of birth, affiliation) is a physical type.

Entities set (Entity set)

A collection of entities of the same type is called an entity set. For example, all students are an entity set.

Contact (Relationship)

The connection between things in the real world must be reflected in the conceptual model. There are generally two types of relations: one is the internal contact of the entity, usually refers to the relation between the attributes of the entity, and the other is the relation between the entities, usually refers to the relations between the different entities.

2. Contact

The linkage between the two entities is called the two-yuan connection, which is a large number of existing links in the real world and can be divided into 3 categories.

(1) One-to-one (1:1) contact

Entity set A is only related to entity set B, and the same entity B is only related to entity A, which is recorded as 1:1. For example, if a school has only one principal, and a principal is in one school, there is one-to-two connection between the school and the headmaster.

(2) One-to-many or multiple-pair links

Entity set a corresponds to multiple entities in entity set B, and each entity in B corresponds to an entity in a, recorded as 1:n. And vice versa, remember as n:1. For example, there are several students in a profession, and each student only learns in one major, and there is a one-to-many connection between the majors and the students.

(3) Many-to-many (m:n) contact

Entity set a corresponds to multiple entities in entity set B, whereas each entity in B corresponds to more than one entity in a, which is recorded as M:n. For example, a teacher can have many students, a student can have more than one teacher, the teacher and students have many-to-many links.

In fact, a one-to-many connection is a special case of a multi-link, and a multi-contact case.

In general, more than 3 entity types also have a pair of one or one-to-many, many-to-many links, known as multi-link. For example, for courses, teachers and reference books 3 entities, if a course can be taught by several teachers, using a number of reference books, and each teacher teaches a course, each reference book is only for one course of use, then the link between the course and teaching, reference books is a pair of many.

There can also be a pair of one or one-to-many, many-to-many connections between entities within the same entity set, called a unary connection. For example, the employee entity set has a leading and led link, that is, a worker (cadre) leads several employees, and one employee is directly led by another employee. So it's a one-to-many connection.

3. How the conceptual model is represented

Conceptual models are represented in many ways, the most well-known and most commonly used notation being the E-r method, also known as the E-R model. It uses e-r diagrams to describe the conceptual model of the real world. The main components of the E-r diagram are entity type, attribute and relation.

(1) Entity type: A rectangle is used to indicate the name of the entity in the rectangle box.

(2) attribute: An ellipse is used to connect the entity to the attribute with a non-forward edge.

(3) Contact: With a diamond, the diamond box is indicated by the contact name, and the non-edge of the diamond with the relevant entities are connected to the non-edge-side label on the type of contact (1:1,1:n or m:n). It is important to note that if a contact has attributes, these properties are also connected with the contact with the non-forward edge.

For example, there is a headmaster and several classes in the school, each class has several teachers and students, each teacher teaches many students, each student has the school number, the name, the gender, the class, the entrance time attribute.

It is necessary to note that there are two methods for drawing e-r graphs: Integration method and Separation method. The integration method is to draw all the entities of a system, the entity attributes, the entities and the entities together in a diagram, to form a complete e-r diagram. This drawing is suitable for describing a small database system, as shown in Figure 2-5 is the E-r diagram drawn by the integrated method. The separation method is to draw each entity and its attribute map separately, then draw the relationship between the entities, which is suitable for describing the large scale database system.

SQL Server Database Learning Note-conceptual data model