-
Notifications
You must be signed in to change notification settings - Fork 1
Database
Let R be a relation schema and let X and Y be non-empty sets of attributes in R. We say that an instance of R satisfies the FD: X -> Y if the following holds for every pair of tuples t1 and t2 in r:
If t1.X = t2.X, the t1.Y = t2.Y
| A | B | C | D |
|---|---|---|---|
| a1 | b1 | c1 | d1 |
| a1 | b1 | c1 | d2 |
| a1 | b2 | c2 | d1 |
| a2 | b1 | c3 | d1 |
AB -> C
The set of all FDs implied by a given set F of FDs is called the closure of F, denoted as F+.Armstrong's Axioms can be used to build F+.
- Reflexivity: If Y ⊆ X, then X -> Y.
- Augmentation: If X -> Y, then XZ -> YZ for any Z.
- Transitivity: If X -> Y and Y -> Z, then X -> Z.
Let R be a relation schema, F be the set of FDs given to hold over R, X be a subset of the attributes of R, and A an attribute of R. R is in BCNF if, for every FD X-->A in FD, one of the following statements is true:
- A ⊆ X; that is, it is trivial FD, or
- X is a superkey.
Consider the following relation. If we know that the relation is BCNF,
can the value of A in the second row be a?
| X | Y | A |
|---|---|---|
| x | y1 | a |
| x | y2 | ? |
Let R be a relation schema, F be the set of FDs given to hold over R, X be a subset of R, and A be an attribute of R. R is the 3NF if, for every FD X → A in F, one of the following statements is true:
- A ⊆ X; that is, it is trivial FD, or
- X is a superkey, or
- A is part of some key for R.
Suppose that a dependency X -> A cause a violation of 3NF. There are two cases:
- X is a proper subset of some key K. Partial dependency.
- X is not a proper subset of any key. Transitive dependency.
SBDC: Reserves(sid: integer, bid: integer, day: date, cardno: string), S -> C
SBDC is not 3NF. But if added with C -> S, CBD is also a key. Then SBDC is 3NF.
Using decomposition with ER-diagram usually leads to 3NF. BCNF is stronger than 3NF.
Association:
- 1 to 1: referring attribute on either side
- 1 to N: referring attribute on N side
- N to N: association table
SQL Server has isolation levels: READ UNCOMMITED, READ COMMITTEED (default), REPEATABLE READ, SNAPSHOT and SERIALIZABLE. Reference is https://msdn.microsoft.com/en-us/library/ms173763.aspx.
Oracle has isolation level: READ COMMITTED (default), SERIALIZABLE and Read-only Reference is http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm.
MySQL InnoDB has isolation levels: READ UNCOMMITED, READ COMMITTEED, REPEATABLE READ (default) and SERIALIZABLE. Reference is https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html.