A detailed explanation of soft exam afternoon--Database design

In the previous two blog, the small part of the soft examination in the afternoon test data Flow diagram design and UML diagram of the relevant knowledge points are elaborated in detail, today we continue to see the soft examination of the big question in the afternoon---database design, the design of the database we have been early contact, In the first computer room charge system when we are directly using someone else's script, did not think of the database there is what kind of problem, wait until the personal reconstruction room, we need to redesign the database, this time, is no longer silly Import Database script file So simple, we need to start from the needs analysis, Own design database, what three-paradigm, the primary foreign key Association this is where we need to be aware of, so to speak database design throughout our learning always, then today, the database appears in our soft test, she will be a kind of way to appear nie, is the white skirt swaying, or a seat on the floor long skirt, Small series today, a brief introduction to the database design of relevant knowledge points, first of all, we look at the following picture:

Next, the small series along with the above mind map of the context, one by one to explain the database design of relevant knowledge points, first we look at the first knowledge point:

Database design phase

The database design phase is divided into four stages, let's look at one of the following pictures:

Next, the small series of four stages in the end is what, what to do one by one of the introduction. First, let's take a look at the first stage, demand analysis. The so-called demand analysis is to collect and analyze the user's information needs and processing requirements of the system, to get the necessary analysis of the design system, to establish the system documentation, the objective of the requirement analysis is to understand the user's data requirements and processing requirements through investigation and research, And in accordance with a certain format to form the requirements of the specification, that is, the demand specification is the result of the requirements analysis stage, but also a basis and basis for future design, she includes the data involved in the database, the characteristics of the data, the use of frequency and data volume estimates, such as data names. Property. types, including data confidentiality requirements, database integrity constraints, the frequency of use, the size of data, and other issues, when designing large databases, these data information is usually used to manage the digital dictionary, which is the content of the requirements analysis.

Let's take a look at conceptual structure design: Conceptual structure design is the abstraction and synthesis of all the data and processing requirements provided by the requirements specification, according to a certain method, we construct the conceptual model that reacts to the data of user environment and the relation between data. That is, the ER model we usually talk about, so this conceptual model is not related to the specific DBMS, she is a real-world, user-friendly data model, in order to ensure that the conceptual data model involved in the correct and complete response to the user's data and the relationship between the Easy to carry out the various processing required, in the conceptual structure design stage can absorb the user to participate in the conceptual structure design, the design of the application of the view, that is, the data that each application sees and his structure, the local view, and then the partial view integration, That is, after the sub-ER diagram to the whole ER diagram merging process, so, the final result, the formation of conceptual design model, formed a conceptual design model, it is necessary to start the logical structure design.

then the logical structure of our design: according to the relevant rules, the ER model into a relational model, and then according to the relevant normalization theory, determine the relationship mode of the primary key, foreign keys, constraints and other such characteristics, so this conversion is to be able to a particular DBMS accepted by the logical model of a form, The result of the logical structure design phase is the data pattern written in the data definition language provided by DBMS, and the concrete method of logical design is related to the logical data model of DBMS, so another input is the characteristic of DBMS. The logical model should meet the requirements of database storage, consistency, and Operation of all aspects of the user.

The final is our physical design: The logical design phase to meet the user needs of the established logical model is physically implemented, his main content is based on the DBMS provide a variety of means of design data storage format and storage path, including file structure, index design process, that is, to design the database internal mode or storage mode, because the database of the internal mode of the database performance impact is relatively large, so should be based on the requirements of processing And the performance of the operating system hardware to design, so her input is based on hardware and operating system characteristics. After reading the design phase of the database, let's look at the ER model.

ER model

ER model, Entity-Contact Model (abbreviated as E-R model) it provides a user-oriented expression method that is not constrained by any DBMS and is widely used as a tool in database design for data modeling. The E-R data model has undergone many modifications and expansions since its inception. Let's look at a simple ER diagram, as shown below:

In our ER diagram, what is the meaning of each figure? NIE, the small part to introduce briefly, the ellipse represents the attribute. Rectangular means the entity, diamond means contact, the number indicates the type of contact, such as warehouse and parts of the relationship is inventory, the warehouse can be stored in a number of parts, parts can be placed in multiple warehouses to store, so their relationship is many-to-many relationship. In general, in the conceptual structure of the database design, the first to design the local ER diagram of each subsystem, the left is a procurement management ER diagram, the middle is the ER diagram of inventory management, the right is personnel Management ER diagram, first design The local ER diagram, and then merge.
Then to design the local ER diagram of each subsystem, can be divided into the following steps:
First, determine the extent of the detail view, and then identify each entity in that section, as well as the relationships between entities, the last assigned entity, and the attributes between the entity contacts. When the local ER diagram of each subsystem is well designed, we need to do is to merge the ER diagram of the subsystem to make it a total ER diagram, called the integration of the view, this integration has two ways, one way, multiple local ER diagram integration, the first step is gradually integrated, first two integration, and then integration. No matter in that way, due to the various subsystems in the application of the problems faced by different, and in a large system, usually not only a designer, so different subsystems by different designers design, resulting in the various parts of the ER diagram there must be a lot of inconsistencies between the problems, We refer to this inconsistency as a conflict, so when merging sub-er graphs, it is not easy to divide the ER graphs of each part together. Instead, the inconsistencies between subsystems must be eliminated in order to form a unified conceptual model that can be understood and accepted by all users throughout the system. About the theoretical knowledge of database design, small part of the temporary introduction to here, next, we take a look at the past years of soft test real problem, see how we use theoretical knowledge in the actual use of the NIE?

Analysis of typical examples

First, let's take a look at a November 2004 real topic, as follows:

The ER diagram looks like this:

We finished the topic, and then we looked at the first small question, as follows:

          We have just introduced the ER diagram into a relational model, in our diagram above, a total of two links, three entities, Each entity to be converted to a relational mode, there are three relationship patterns, two links to a relational model, the title requirements to four, the first to merge. So this time to determine the relationship between these entities, look at their relationships, we look at the topic is described as: "A customer can fill in a number of purchase orders on the same day, each purchase book can be filled with a variety of books, each book can be ordered multiple copies, bid the same book on the same sheet offerings can not appear many times, note , in order to simplify, regardless of the security of the credit card number leakage, etc. "through the description of my question, we can easily see that the relationship between the customer and the purchase order is a one-to-many connection, we look at books and orders, an order contains many books, according to the attempt, A book can appear in multiple orders, so the book and order contact is many to many, before we mentioned that too many links should be converted into a separate relationship mode, so PlaceOrder and orders are merged, so the four relationship pattern is book books (attribute six, Chikuma Bid Book number), the second customer, the attribute has four, the primary key customer number CID, the third is the order, so her property has the order number, the customer's number, and the date of completion brderdate, which is according to what we just said, a n than n relationship with another paragraph when the merger, Should include a section of the code to be added, so the property of this order has three, order number, customer number, fill date, a fourth orderlist, separate form a relationship mode, then will be associated with her each entity's code needs to be added in, So a total of four properties bid (number of books), order number, date of issue, quantity, primary key is a combination of book number and order number.
        External code: For books, there is no external code, because these properties are their own, the customer is also, the order has three attributes, CID is the external code of orders, because she is the customer's primary key, not the primary key of the order, So she is a foreign code, looking at OrderList, we know in orderlist his primary key is a combination of bid and ordernum, but either one of them is not her primary key, so each is her foreign key. We then look at the second question, as follows:

From the description of the topic we know that there are four customer attributes, so the two empty fill is definitely not a property, that would be what NIE, is the constraint, then in this topic what constraints NIE, see the topic, the first request Cardnum this value is unique, here does not reflect, the problem is only given is not empty, We use the constraint unique, so the first empty fill is unique cardnun, so that the value of Cardnum is unique, and a constraint is what NE, we know that when creating a table, we need to specify the table's primary key, which does not indicate that the table's primary key is the CID , here only the non-empty, but is not NULL is not the primary key, so the second blank is primary key (CID), so about filling in the SQL statement, when the table and view the situation, we first see whether his attributes are complete, if complete, the rest is the constraint. Then we look at the third question:

We don't want to look at the SQL statement first, so we can not see the fame, we must first figure out how to do this query, such as test instructions, the need to inquire about all orders for 123 456 books of the users to order other books, we could be divided into two steps to proceed, We first identify those users ordered the bid 123 345 of the book, the second step to check these users ordered other books, so this is used in the query nested, in the nested inside the first, and then query the second, So the fourth empty should be c.bid, fifth empty c.ordernum, in orderlist this relationship mode, she is a n than N of the relationship between the conversion, so she has the book number and order number, so the book number equals 123--456, The order number equals the order number of the relationship mode, so 4 and 5 are completed, the 4th is C, and the 5th empty is c.ordernum. So 3 filled in is not.

Small Series of words: The blog, the small part of the main explanation of the afternoon soft test data design of the relevant knowledge points, respectively, from the database design stage, er model has a typical example analysis of three aspects of the database design is a simple explanation, in the soft test, usually will examine us, Er diagram of the contact between the entities, write the relationship model, find out the main foreign key; the problems in the soft examinations include the type of connection between entities in a given ER diagram, the addition of attributes in the relational schema, the identification of the primary foreign key of the relational schema, and the SQL statement. At the same time, in determining the primary foreign key, we can draw conclusions according to Common sense, and the question description, to answer such questions need to read the topic carefully.

A detailed explanation of soft exam afternoon--Database design