Database Management and Programing Article

  Relational database design is the time to comply with certain rules.    In particular, the database design paradigm is a brief introduction 1 NF (the first paradigm), 2 NF (second paradigm), 3 NF (third paradigm) and BCNF, while the fourth and fifth Paradigm Paradigm stay to a later date Introduced.    In a time when you design the database, can meet these paradigm, you are the master database design. 

  The first paradigm (1 NF): R models in each of the specific relations r, if the value of each property are no longer at the smallest unit of data, then R is the first paradigm of the relationship.    Example: If employees, names, phone numbers to form a table (one person may have an office phone and a home telephone number) 1 NF has become a standard three ways: 
  First, repeat the name and memory of workers.    Thus, the keyword is the only phone number. 
  The second is for the workers, keyword, phone number, divided into units and residential telephone call the two attributes of the three workers for keywords, but mandatory each record there can be only one phone number. 
  These three methods, a way to the most desirable, according to the actual situation selected after two cases. 

  The second paradigm (2 NF): If the models R (U, F) of all non-main attributes are totally dependent on any one candidate keyword, then relations between the R is the second paradigm. 
  Example: Elective relations SCI (SNO, CNO, GRADE, CREDIT) for the school, which SNO, CNO, for the courses, GRADEGE for the results, CREDIT for the credits.    From the above conditions, the keyword for the combination of keywords (SNO, CNO) 
  In the application of the use of these models have the following questions: 
  a. data redundancy, assuming the same class of students from 40 elective, repeat 40 times on the credits. 
  b. Update anomaly, if a certain course of adjustment credits, the corresponding value of the yuan to update CREDIT group, may be different from the same class credits. 
  c. insert anomalies, such as the planned open courses, because no one elective, no school, keyword, only some people, such as elective courses and credits to be deposited. 
  d. remove anomalies, if students have graduated today from the database to delete the current elective record.    Some students have not yet elective courses, this course and credit records can not be saved. 
  Reason: non-keyword attributes CREDIT only function dependent on the CNO, which is dependent on some combination of keywords CREDIT (SNO, CNO) and not rely entirely on. 
  Solution: divided into two models SC1 (SNO, CNO, GRADE), C2 (CNO, CREDIT).    Relations, including two new models, among them through the SC1, keyword CNO phase and need time to conduct natural connection, restore the original relationship 

  The third paradigm (3 NF): If the models R (U, F) of all non-main attribute of any candidate keyword convey trust does not exist, then relations between the R is the third paradigm. 
  Example: If S1 (SNO, SNAME, DNO, DNAME, LOCATION), respectively on behalf of all attributes, 
  Name, the host of the department name, department address. 
  Keyword SNO decision each attribute.    Because it is a single keyword, not rely on the part of the problem is certainly 2 NF.    But relations certainly have a great deal of redundancy, the students of the few properties DNO, DNAME, LOCATION will repeat storage, insert, delete and modify, and will have a similar case of the situation. 
  Reason: relations depend on the existence of transmission.    That is, SNO -> DNO.    And DNO -> SNO does not exist, DNO -> LOCATION, Liao SNO therefore key to LOCATION function through the transfer decision is dependent on SNO -> LOCATION achieved.    In other words, SNO does not directly attribute the main decision-LOCATION. 
  Head solution: each of the models can not have a pass-dependent. 
  Solution: relations between the two S (SNO, SNAME, DNO), D (DNO, DNAME, LOCATION) 
  Note: S relations can not do in the keyword DNO.    Otherwise relations between the two lost contact. 

  BCNF: If the models R (U, F) all of the properties (including the main attributes and non-property owners) do not rely on the transmission of any candidate in the R keyword, said relations between the R is BCNF.    Or models R, if every decision factors include keywords (instead of being covered by keywords), the relationship between the RCNF model. 
  Example: Accessories management models WPE (WNO, PNO, ENO, QNT), respectively Table warehouse, accessories, trade unions, the number.    Have the following conditions 
  a. a warehouse a number of trade unions. 
  b. only one trade union work in a warehouse. 
  c. each warehouse models of a person in charge of accessories, but a person can manage several accessories. 
  d. the same type of accessories can be placed at several warehouses. 
  Analysis: The above in the PNO not sure QNT, by the combination of attributes (WNO, PNO) to decide, rely on the existing function (WNO, PNO) -> ENO.    As each of a warehouse accessories by hand, while a person can manage several parts, so there are combination of attributes (WNO, PNO) to determine the responsible person, (WNO, PNO) -> ENO.    Only because of a trade union work in a warehouse, ENO -> WNO.    As each warehouse of a person in charge of accessories, and only a trade union work in a warehouse, (ENO, PNO) -> QNT. 
  Keywords to find the candidate, because (WNO, PNO) -> QNT, (WNO, PNO) -> ENO, therefore (WNO, PNO) can decide the yuan Group, is a candidate keywords.    According to ENO-> WNO, (ENO, PNO) -> QNT, it (ENO, PNO) can also decide the yuan Group, another candidate for the keywords.    Attribute ENO, WNO, PNO are the main attributes, only one non-main attribute QNT.    It any one candidate keywords are completely dependent on the function, and is directly dependent on, the relationship model is 3 NF. 
  Analysis of the main attributes.    Because the ENO-> WNO, the main attribute ENO WNO is the determining factor, but it is not a keyword, just part of the combination of keywords.    This caused the main attributes of another candidate WNO keyword (ENO, PNO) part of dependence, because (ENO, PNO) -> ENO in turn, but not to set up, and the P-> WNO, they (ENO, PNO) -> WNO Also rely on the transmission. 
  Although there is no non-main attribute of the candidate key Liao rely on the transmission, but there is the main attribute of the candidate keyword rely on the transmission, will also have the same trouble.    If a new warehouse employees assigned to work, but temporarily in the attachment phase, the absence of an independent responsible for the management of certain parts of the task.    As part of the lack of keywords can not be inserted into the PNO and the relationship to.    Also, no matter into a personal accessory to a charge of security, then remove parts of the trade union will be deleted. 
  The solution: divided into management EP (ENO, PNO, QNT), keyword (ENO, PNO) work EW (ENO, WNO) their keywords is ENO 
  Disadvantages: decomposed rely on to maintain the function of the poor.    Such cases, because of decomposition, functional dependency (WNO, PNO) -> ENO lost, and therefore has damaged the original semantics.    Did not reflect a parts warehouse for each person in charge.    Likely to be one or two parts from two or more people to manage.    Therefore, after the decomposition of the relationship between the model of the integrity of the lower bound. 

  Broken down into more than a relationship, to make meaningful decomposition, the minimum requirement is not lost after the decomposition of the original information.    This information includes not only the data itself, but also a function of the data that rely on the interaction between the constraints.    Decomposition goal is to achieve a higher degree of standardization, but decomposition at the same time we must consider two issues: non-destructive connection and maintain functional dependency.    Sometimes often impossible to achieve both lossless connection, and totally dependent on maintaining function.    Need to be weighed against the need. 

  1NF until BCNF between the paradigm of the four following: 
  BCNF includes 3 NF contains 2 NF contains 1 NF 

  Summary: 
  Head: standardized purpose is to enable a more rational structure, elimination of abnormal storage, data redundancy as possible, to facilitate the insertion, delete and update the principles: to comply with a single concept of "one of the" principle that a pattern describes the relationship between the inter-entity or entities Of a link.    Norms is the essence of the concept of a single. 
  Method: projection models broken down into two or more models. 
  Requirements: After the decomposition of the relationship between the mode of collection and the original models should be "equivalent", that is a natural link between the original can be resumed without loss of information, and to maintain reasonable contact attribute. 

  Note: a guitar models that decomposition of different models can be set, that is not the only method of decomposition.    Minimum requirements must be redundant after the decomposition of the original database to database all the information as the prerequisite to achieve.    The fundamental goal is to save storage space and avoid data inconsistencies and improve the operational efficiency of the relationship, at the same time meet the application requirements.    In fact, do not always require all models have not reached BCNF.    Sometimes deliberately keep some redundancy may be more convenient data query.    Especially for those who update frequency is not high, very high frequency for the database system more so. 

  In the relational database, in addition to function rely on the outside there are more value-dependent, relying on the connectivity problem, which made the fourth paradigm, the fifth paradigm, such as a higher level of standardization requirements.    Here, after the return. 

  Dear friends, after you read what feelings, in fact, any theoretical basis for a database of books will say these things, taking into account the many netizens is halfway monk, to do database.    Special to find a copy of the special one copy, you have any questions, do not ask me, their own database to find a relationship between the theory of looking at it, perhaps, you go a long way.    Said that the above is the basis for the theory of things, let us think about, you do when the database design has been considered above the compliance paradigm, it has not done well in the database design time, think about Compared with the above mentioned, in the end is in violation of several paradigm? » 
  I have seen the database design, very few people do very much in line with the above paradigm, generally speaking, the first paradigm we can observe, in full compliance with the second and third paradigm of the very few people, the people must comply with Design is a master database, BCNF less chance of emerging paradigm, but also would undermine the integrity, you can not do when considering the design of it, of course, the flip-flop by ORACLE in resolving its shortcomings.    After doing our common design, we also hope that comply with the above paradigm. 

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.