1. Three-level mode – Two-level mapping
In layman’s terms,
Internal mode is to see the database file, concerned about how to store data;
The conceptual schema is a table
The external mode is the view
2. Relational algebra
Cartesian product projection: select column selection: conditional filtering join: merge the same column, default equivalent join, that is, only retain the same line of the two
A projection is a selection of columns; Selection is filtering rows;
division
Division is something special. First R divided by S, you get A set of columns A that are in R, but not in S; Then select the rows from R that have the same value as the common columns of R and S, and these rows contain the value of the column set A, which is the result.
Third, function dependence
For example, the student number can determine the name of the student, say that the name depends on the student number.
1. The primary key of some functions is (Student ID + Course ID), so the field “Name” partly depends on student ID.
2. Transfer function dependency
Student id: dormitory fee 062201 A 900 062230 B 1200 062240 B 1200 Student ID: dormitory fee 062201 A 900 062230 B 1200 062240 B 1200 Student ID: dormitory fee 062201 A 900 062230 B 1200 Student ID: dormitory fee The transfer of fees depends on the student number.
Fourth, the value and use of standardization theory
Five, seek candidate keywords
Six, paradigm
Lifting paradigm can eliminate data redundancy, insert anomaly, update anomaly and delete anomaly. However, the higher the paradigm, the better. The higher the paradigm, the smaller the granularity of data, which may bring management and performance problems. Usually, paradigm three is enough.
Paradigms are incremental. In order to reach the high normal, the low normal must have been satisfied.
The criterion of a normal form can be judged if the condition is satisfied or the condition does not exist
1. First paradigm
2. Second normal Form
The credit portion depends on the primary key (only on the course number), so it does not fit the second normal form. The solution is to split the course out of the table, keeping only the course number. In fact, conversion to higher paradigms is usually split.
3. Third normal Form
There is only one primary key, there is no partial dependence, so it must be second normal form. But there are transitive dependencies in the table: the student number determines the department number, and the department number determines the name and address of the department, so it is not the third normal form. Solution: Break it up.
4. BC paradigm
There are no non-primary attributes in the diagram, which must fit the third normal form. But because T is not a candidate key in the listed dependency expression, it is not BC normal form. The candidate keywords in the figure are SJ and ST.
7. Pattern decomposition
Table splitting is actually schema splitting.
1. Keep function dependent decomposition
What function dependencies existed before decomposition, and they still exist after decomposition.
As shown in the figure, mode R(A,B,C) has A->B,B->C, A->C, decomposed into R1(A,B), R2(B,C), which is A preserve function dependent decomposition (A ->C can be derived from A->B,B->C transfer). But if you split into R1(A,B) and R3(A,C), then you don’t keep the function dependent decomposition, because B->C is gone.
2, nondestructive decomposition
1) Table method
Column as an attribute of the relational schema
Behavior decomposition model
A indicates that the decomposition schema has attributes in the original schema
When there are two rows in which a column is both a, the non-A column can be changed to B based on function dependencies. If I end up with a row
If it’s all A, it’s lossless.
2) Operations
This is suitable for cases where you decompose into two patterns.
The intersection and difference of these two modes are calculated respectively. If any difference is determined by intersection, it is lossless decomposition.
