relational database, summary

relational database, a simple understanding of the "row-table-Library" form of the two-dimensional database, the use of relational model to organize data, in short, the two-dimensional table between the field may be a relationship between the formation of a large-scale data network; Of course, a separate relational table can also be used as a simple library.

The biggest advantage of relational databases over other types of databases is the consistency of data (transactional processing), other things such as the premise of normalization, the small cost of updating data (the same fields are basically only one place), the ability to make complex queries such as joins, and so on.

Relational database also has the corresponding drawbacks, such as a large number of reading and writing are concentrated in a database, it will seriously affect the efficiency of the database, so most of the users of relational database use the form of master-slave database to alleviate the load problem, the main library is responsible for writing, from the library responsible for reading. However, this may cause problems such as inconsistent or conflicting data updates. To avoid this problem, the request for each table needs to be assigned to the appropriate primary database for processing. If the large database is segmented to achieve parallel operation, then join processing is not possible. To speed up the query, the relational database is typically indexed and the structure of the table changes when you need to add some necessary fields to the table. At this point, the table needs to be shared lock, this period of data additions and deletions will stall. If you need to take some time-consuming actions, such as creating an index on a table with a larger amount of data or altering its table structure, you need to pay special attention to the fact that data may not be updated over a long period of time. In addition, when the table structure is not fixed, that is, the use of relatively variable fields, the relational database is also more difficult to handle, the frequent updating of the table structure will greatly increase the workload. And if the query conditions are relatively simple, it is not necessary to use a relational database.

Three-paradigm of relational database:

• First paradigm: each attribute (field) is an atomic term, indivisible

The indivisibility here means that it must be segmented in the case of severability, which is judged in the context of the application, when the attribute is a document, although the document has paragraph marks, it should not be split.

Example:

--any field in a database table is a single attribute and cannot be dividedCreate TableAA (IDint, Nameagevarchar( -)) InsertAaValues(1,"' --Woman"') --did not reach the first paradigmCreate TableAA (IDint, Name Varcahr (Ten), ageChar(2)) InsertAaValues(1,"' -"',"'Woman"') --reach the first paradigm

• Second paradigm: Each secondary attribute (field) to which a full function depends on a candidate key, or a primary key

The key word is "full dependency", relative to "partial dependency" or "local dependency", if a candidate key or primary key consists of two attributes, the non-primary attribute cannot be dependent on only one or part of the property.

Example:

Create TableSCI (snoint( +), CNOint( +), Gradeint( +), creditint( +),Primary Keysno,cno) Course (CNO)1---1 Credits (credit)Student (SNO) n---N Course (CNO)Students+Course----Score (grade)Scisno CNO Grade credit1    1    -      the2    1     -      the3   1     -      the.   .      .   ..   .     .    ..   .     . ./*As a result, the credit has been repeatedly stored, data redundancy for a course credit, you need to repeat the new course, as Sno and CNO together as the primary key, when joining the new course, you must have a candidate for the class, such as a student's course, The student's other curriculum information is also removed at the same time. In short, this design is not very good, non-keyword attribute credit only function relies on CNO, that is, the credit part relies on the combination of keywords (SNO,CNO) instead of fully dependent solution into two relational patterns SC1 (SNO,CNO, Grade), C2 (Cno,credit). The new relationship consists of two relational patterns, which are linked by the outer keyword CNO in the SC1, and then natural joins are needed to restore the original relationship*/

• Third paradigm: All non-primary attributes (fields) do not have transitive dependencies on any candidate keywords

The key word is transitive dependency, which is transitive if the non-primary attribute relies on the primary key through another non-primary property. Example:

-- Example S1 (Sno,sname,dno,   Dname, location)          study number name    / * keyword sno determines individual properties. Because it is a single keyword, there is no partial dependency on the problem, certainly is 2NF. However, there must be a lot of redundancy in this relationship, and some of the properties of the student dno,dname,location will be stored, inserted, deleted and modified in a similar case.  */*  Reason: There is a transitive dependency in the relationship. That is Sno 1->1 DNO. While the DNO 1->n SNO does not exist, and the DNO location exists, so the keyword SNO to the location function is determined by the transfer dependency SNO-location implementation. In other words, Sno does not directly determine the location of the non-primary attribute.  * ///////* Address: No transitive dependencies can be left in each relational pattern. Workaround: Divided into two relations s (Sno,sname,dno), D (dno,dname,location) Note: The relationship S cannot have no outside keyword DNO. Otherwise two relationships lose contact 

　 Related terms:

• Super key: An attribute set that uniquely identifies a tuple in a relationship is called a hyper-key of a relational pattern
• Candidate key (candidate Key): A super-key that does not contain extra attributes is called a candidate key
• Primary key (primary key): A candidate key that the user selected as a tuple ID is called the primary key
• Main attribute (Prime Attribute): The attribute in the candidate key is called the primary attribute
• Non-primary property (Non-key Attribute): A property that is not contained in any candidate key is called a non-primary property.

relational database, summary