Database design and ER model-the principle of database system

Database System life cycle

The development of database application system is a software engineering, generally has the information collection, the organization, the processing, the extraction, the synthesis, the dissemination and so on function, but has own characteristic, therefore is called the database project .

Database application system from the beginning of planning, design, implementation, maintenance, and finally replaced by the new system to stop the use of the entire cycle, called the database system lifetime .

The lifetime of a database system can generally be divided into the following seven phases:

(1) Planning: The first step in the life cycle of the database system. The work to be done during the planning phase is to identify the goals and tasks that the system needs to achieve by understanding the actual needs of the user, and to determine the overall objectives of the database system.

The tasks to be done during the planning phase are:

A. Systematic investigation.

B. Feasibility analysis.

C. Determine the overall objectives of the database system and optimize the workflow of the application unit and develop the project development plan.

(2) Demand analysis: Accurate understanding and analysis of user needs.

The tasks required in the requirements analysis phase are:

A. Analyze user activity and generate business flowchart.

B. Determine the system scope and generate a system correlation diagram.

C. Analyze the data involved in user activity and generate a streaming diagram.

D. Analyze system data and generate a data dictionary.

(3) Conceptual design: Conceptual structure design is the most important stage of database design, and it forms a conceptual model independent of specific DBMS by synthesizing, summarizing and abstracting user's needs.

The main steps of conceptual design are done in three steps:

A. Perform data abstraction and design a local conceptual model.

B. Synthesize the local conceptual model into a global conceptual model.

C. Review.

(4) Logical design: Transforms the conceptual structure design into a data model supported by a DBMS.

(5) Physical design: Select a suitable physical structure for the logical structure model.

(6) Database implementation: Using DBMS provided by the database language and host language, according to the logical design and physical design results to establish a database, develop applications, and try to run.

(7) Database operation and maintenance: in the process of database system operation, collect the data of system operation to evaluate the system performance, and further adjust and modify the system.

Basic concepts of ER models

• ER model

ER model is a method and tool for people to know the objective world. The basic elements of an ER model are entities, contacts, and attributes. Er models are the use of entities and their relationships to describe things, a connection may involve multiple entities, it involves the number of entity sets is called the number of the contact, the ER model with the constraints of the contact type to limit the number of entities participating in the contact.

The design of the ER model is essentially a two-step process:

1. The entity type is designed first. (Do not involve "contact" at this time)
2. Re-design the contact type. (Consider the relationship between entities)

At design time, sometimes the line between "entity" and "contact" is blurred. The task of the Database Designer is to abstract the data in the real world and the connection between the data, and use "entity" and "contact" to express it.

• Conceptual design of database using ER model

The concept design of database using ER model is divided into three steps:

(1) Design the local ER model.

A. Determine the local structure scope.

B. Define the entity.

C. Define the contact.

B. Attribute assignment.

(2) Design the global ER model.

A. Determine the public entity type.

B. Merging of local ER models.

C. Conflict elimination.

(3) Optimization of the global ER model.

A. Merge entity types.

B. Eliminate redundant attributes.

C. Eliminate redundant linkages.

Basic concepts of relational models

A two-dimensional table structure is used to represent the entity set, and the data model of the relationship between entities is represented by the key code, called the relational model .

• Basic terminology for relational models

The basic terms are: field (attribute), field value (attribute value), record (tuple), two-dimensional table (tuple collection, relationship, or instance), and the expression in parentheses is the term in the relational model.

The number of attributes in a relationship is called the number of tuples , and the number of tuples is called cardinality .

key : Consists of one or several properties. (Note that the key is not necessarily a unique property.)

Hyper-Key : A set of properties that uniquely identifies a tuple in a relationship. (Note that a hyperlink is also an attribute set, not necessarily just a property)

candidate Key : A super-key that does not contain extra attributes.

PRIMARY KEY : The user is selected as a candidate key for the tuple identity.

foreign Key : the attribute corresponding to the primary key of a relationship appears in another relationship, at which point the primary key is the foreign key of the other relationship. If there are two relations S and SC, where s# is the primary key of the relationship S, s# also appears in the relationship SC, at this time s# is the foreign key of the relationship SC.

• Three types of integrity rules for relational models

1. Entity Integrity rules: requires that tuples in a relationship cannot have null values on the attributes that make up the primary key.
2. Referential integrity rules: A requirement cannot reference an entity that does not exist.
3. User-defined integrity rules: The system provides a mechanism for defining and verifying such integrity, as determined by the specific application environment.

Transformation rules of ER model to relational model

The result of the logical design of the relational database is the definition of a set of relational models, because of the inherent advantages of the relational model, the logical design transforms the result of the conceptual design (i.e. the global ER model) into the initial relational pattern set, makes the design process formalized, and the results can be verified.

The main component of ER model is entity type and contact type, and the conversion algorithm is how to convert entity type and contact type into relational pattern.

For entity types, you can convert each entity type to a relational pattern, the attribute of the entity is the attribute of the relationship pattern, and the entity identifier is the key of the relational pattern.

For the contact type, different processing is done according to different situations. The rules for conversion are:

1. If the relationship between entities is a one-to-two connection, place the key of a relation as a foreign key in another relationship.
2. If the relationship between entities is a one-to-many connection, the key of the relation on the "a" side is placed in the "many" side of the relationship as a foreign key.
3. If the relationship between entities is a many-to-many connection, the contact type is also individually converted to a relationship, which is called a cross-relationship. The key of the relationship is composed of the keys of the entity associated with the contact, and the properties of the relation become the attributes of the intersection.

ER Model Instance Analysis

Suppose to establish an enterprise database, the enterprise has a number of subordinate units, each unit has a number of employees, a staff member only in a unit, and a worker only work in a project, but a project has a lot of workers to work, there are a number of suppliers for various projects to supply different equipment. (This statement is a typical ternary contact type M:n:p)

The attributes of a unit are: unit name, telephone.

The attributes of employees are: Employee number, name, gender.

The properties of the device are: device number, device name, origin.

Vendor attributes are: Name, phone.

The properties of the project are: project name, location.

Please complete the following processing:

1. Design an ER diagram that meets the above requirements.
2. Convert the ER diagram to an equivalent relational pattern.
3. Based on your understanding, underline the primary key in each relationship.

(1) to meet the requirements of the ER diagram as follows (the entity attributes are drawn in an oval, here slightly ...) ）：

(2) After the transformation of the relationship mode and the primary key is as follows (red indicates the underlined primary key, purple indicates the foreign key with wavy lines, the dash means that the union is built, the 6th is the Union primary key and the other 3 relations of the foreign key):

1. Unit (unit name, telephone)
2. Employee (Employee number, name, gender, unit name, project name)
3. Project (project name, location)
4. Supplier (name, phone)
5. Equipment (device number, device name, origin)
6. Supply relationship (project name-Equipment number-supplier name, quantity) Note: the "Quantity" attribute here is based on test instructions.

Enhanced ER Model

• Weak entities and strong entities

One entity has a strong dependency on another entity, called a strong entity, and part or all of the entity's primary key is obtained from its strong entities. The entity is called a weak entity. Represented by a double-line rectangular box.

• Definitions of subclasses and super-classes

When an entity type on a lower level expresses a special case of an entity type at a higher layer, it is said that the entity type on the higher layer is a super-type, and the entity type on the lower layer is a subtype. Subclasses inherit all properties of the superclass.

TEST

1. Why does the tuple in the relationship have no sequencing? And no duplicate tuples are allowed?

(1) The relationship is the set of tuples, and the set does not consider the order of tuples. (2) Each tuple in the relationship must have a property identifier to differentiate each tuple, the attribute values are different, so there is no identical tuple, and from the point of view of the set theory, there will be no duplicate elements.

Database design and ER model-the principle of database system