ER model of database, function dependence, lossless decomposition, relational algebra __html

The basic concept of ER model and how to draw E-R diagram;

Entity: An objective existence can distinguish between things, can also be abstract events. Such as: a football match.

The entity is represented by a rectangle in the ER diagram

Attributes: An entity has many attributes, each of which becomes an attribute. The range of each attribute can be an integral type, a real number, and so on.

Properties are represented by ellipses.

Contact (Relationship):

1 Contact: If each entity in the entity set E1 can only be associated with one entity in the entity set E2, and vice versa, then the entity set E1 the connection to E2 as a one-to-one link to 1:1

N Contact: More than a pair, recorded as 1:n.

M:N Contact: Many-to-many contact, recorded as M:n

To draw an ER diagram:

Find the entity, contact, and finally add the attribute.

function Dependencies:

Definition: Set R (U) is the relational pattern on the attribute set U. X,y is a subset of U. If there is no possibility that two tuples have equal property values on X in any of the possible relationships R,r of R (U), and the values of the properties on Y are unequal, the X function is said to determine that Y or Y functions depend on X for x→y.

First understand the concept of relational schemas and attribute sets. Attribute sets can be understood as N records, with primary keys and attributes in each record.

X,y is a subset of u, that is, X,y is the two columns of data.

For any possible relationship with R (U) R:r can understand the relationship between a primary key and a field property, or a relationship between two unrelated properties. This relationship is not certain.

The last sentence means that it is impossible to have two tuples in x attribute columns equal in x, and equal in the Y property column.

Example: A primary key x can determine the following column of property y, you can say that the X function determines that y or Y functions depend on X

Two-column non-primary attribute x,y,x cannot determine Y, which is called the X function is not sure Y, then the inertia system is not dependent.

Full function dependencies:

Definition: In R (U), if x→y, and for any of X's true subsets X ' does not depend on Y, then the y is called a complete function dependency on X. Remember as X→fy

That is, X is a collection, and the entire set can determine Y and one of the true subsets of X ' cannot be sure that Y is the full function dependency.

Partial function dependencies:

If x→y, but the Y incomplete function relies on x, then y is called "x partial function dependency" as X→py

Transfer function Dependencies:

In R (U), if x→y, (Y is not included in X), and y does not rely on the x,y function to rely on Z, then Z is called X-transitive function dependent.

non-destructive decomposition:

In this simple can be understood as can be restored to the lossless decomposition, if you can not restore, it is a lossy decomposition.

Then, judge whether it is a lossless decomposition, then judge whether it can be restored. Here is an example to explain.

First, the elements in behavior R are listed as R1-R5.

The first step: R1,r2 ... There is no corresponding ABCDE, if there is the use of A1,A2 to express

The second step: see A→C, see AC two columns, a1→b13, so AE column a1→b13 the original b53 to B13 (with small for the standard change)

Step three: Look at the b→c, similar to the second step.

......

The following: De→c is the same.

Another method:

If R is decomposed into a function-dependent set satisfied by P={r1,r2},f R, the sufficient and necessary conditions for the decomposition p to be lossless are:

The r1∩r2→ (R1-R2) or r1∩r2→ (R2-R1) ps:-operation is made up of the public properties of R1 and R1 that are removed from R2.

Relational algebra:

Five basic operations: and, poor, Cartesian product, selection and projection. In addition, there are besides.

Relational algebra concept: is an abstract query language, is an algebraic symbol, where the query by attaching specific operators to the relationship represented. It includes a collection that operates on a table. (Relational algebra is actually an operation on relationships, and this kind of relational computing is a query language)

1. and (Union,∪): The Union of R∪s is all the collection of records in R that have, or s inside, or two of the interior and exterior surfaces.

2. Difference (difference,-): A collection that calculates the difference between two tables. R-s is a collection of records in R that are not inside S.

3. Cartesian product (product,x): A Cartesian product that computes two relationships. Make r a table with a K1 element, and make s a table with K2. RxS is a collection of all K1+k2 Meta Records, the first K1 elements from a record in R, and then K2 an element from a record in S.

4. Projection (project, represented by symbol π): Select a few columns from a table,

5. Select (SELECT, sign σ): Select N Row records from one table

6. Intersection (intersection, represented by Symbolic ∩): Calculates the theoretical intersection of two table sets. Gives the table R and S,r∩s are sets of records in both R and S.

7. Connection (join): The two tables are preceded by a Cartesian product, selected according to the same attributes, and then removed with a projection. That is the connection.

8. In addition to (division): There are two relationships R (X,y) and relations S (Z), where X,y,z is a collection of attributes. Suppose Y and Z have the same number of attributes, and the corresponding properties are from the same domain. The quotient relation of relation R (x,y) ÷s (z) is a subset of relation R projecting on the attribute X, and the Cartesian product of the subset and S (z) must contain R (x,y). Recorded as R÷s.

R A subset of the projections on attribute x: A subset of the X attribute, which may be a few rows of data, meaning that the original corresponding data in this subset must be the same as in the Z property. That is, in addition to the relationship.