[Computer room cooperation]-database design

Objective:

Recently started the computer room cooperation, in which they are mainly involved in the design of the D layer, but also in the document design phase mainly involved in database design. Prior to the computer room reconfiguration for the database design did not follow the document to drive, so this time in writing documents I am very serious, so encountered a lot of details of the problem, such as the view, the stored procedure in the database design which part of the addition, as well as the database of the external mode of deep understanding!

Content:

Database design cycle, divided into planning, demand analysis, summary design, logical design, physical design, implementation, operation and maintenance. I have divided the design of the database into pre-preparation, mid-term design, and later implementation maintenance.

Cycle Division	Pre-preparation	Mid-term design	Post-operation Maintenance
	Planning, requirements Analysis documentation	Overview design, logic design, physical design	Implementation, operation and maintenance

Here I mainly talk about the medium-term design, the following is mainly my database data document layout:

One: The main problems encountered in database design is the relationship between structural design and external design , and then re-learn the principles of the database of the book, here to find the answer, structural design and external design relationship inseparable from the "three models of two images."

The conceptual model expresses the overall logical structure of the whole data, based on the user requirements, the data modeling, independent of the hardware and software, is the Database Designer and user communication tools

The logical model expresses the whole logical structure of the DB, from the database realization point of view, the data modeling, independent of the hardware, depends on the software, is the Database Designer and the Application Programmer communication Tool.

The external model is designed to accommodate different business units to design different external models, also known as user-level models, reflecting the user's view of using the database, which is a logical subset of the logical model. It allows databases and applications to interact by designing an out-of-design model . The outer mode is the database design tables, views, stored procedures, etc., in the face of different users, access to different.

The internal model is the physical model, is the lowest layer of the database abstraction, it mainly describes the way of storage, where the storage structure of the database should be the index structure, access methods through the primary index and secondary index. It is mainly related to the knowledge of the files in the operating system we are learning.

Two: The Entity Contact Model (ER model) of conceptual model design phase in structural design is a widely adopted conceptual model design method, for the basic elements in ER model are entity, contact and attribute. One of the most critical links in the mapping cardinality , the mapping cardinality reaction is the relationship between entities and entities, for example, the relationship between the two-dollar relationship is mainly 1:1,1:n,m:n.

Structural design conceptual model to logical model is mainly the process of ER diagram-to-relational model transformation, which is based on the transformation mechanism of mapping cardinality :

1. If the relationship is 1:1, then the attributes of any one of the relationship models in the relationship model of two entities are added to the properties of the key and contact types of the other relational model.

The relationship between the card and the student is 1:1, so you can use the number as a foreign key in the card's properties, or you can use the card number as a foreign key in the student table.

2. If the relationship is 1:n, then the N-end entity type is converted to the attribute of the entity type primary key and the contact type that are added to the 1 end of the relational schema.

The registration of the relationship between the card and the user is N:1, so the card has the user's primary key as the foreign key of the card.

Then two entities correspond to two relationship patterns

Card (card number, card balance, card type, card status, user ID)

User (user ID, user name, password)

3. If the relationship is m:n, then the contact type is also changed to the relational mode, whose attributes are the keys of the two entity types (as foreign keys) plus the properties of the contact type.

The relationship between the card and the recharge, because the relationship between the user and the card is m:n, so they in the transformation into a relational mode, the return card and recharge will become two relationship mode, then in the return card and recharge, users, cards, their two entities will be converted into four relationship mode, respectively,

User (user ID, user name, password)

Card (card number, card balance, card type, card status)

Return Card (card number, return card amount, return card date, return card time, return card user)

Recharge (card number, recharge amount, recharge date, recharge time, recharge user)

Generally speaking is 1:1 or 1:n relationship, they do not add the relationship between the pattern, two entities will correspond to two relationship patterns, only increased the main foreign key between the addition of relations; in m:n relationships, more of their contact entities are added as relational patterns. And the primary key in the contact entity is a collection of primary keys for two entities.

Summarize:

This is in the database design phase encountered a basic problem, found that a lot of problems are very basic, good learning basic knowledge is really so important, many of these things come from the principle of database system, found that the original learned before the book, the knowledge will be deepened. Not only this book, hope that the future study is so, after learning to look back, there will be unexpected surprises!

[Computer room cooperation]-database design