Database Design
DATABASE DESIGN
Limitation of Files System
1. To access database user should know about the physical details of the data.
2. Operating system fails to control concurrency when database is large.
3. Once got access user can access the whole data of the file system.
Database Design Goals
1. 0% redundancy
2. Loss-less join
3. Dependency preservation
According to Code
No two records of the table should be equal.
Difference between Primary Key and Alternative Key
1. At most one primary key allowed for any relation but more than one alternative keys are allowed.
2. Null values are not allowed in primary key.Null values are allowed in alternative key.
3. Primary keys used to design primary key,unique keys are used to design alternative keys.
Primary Key : Unique + not null
Super Key : Set of attribute used to differentiate all the tuples of the relation or super set of candidate key.
Example : Let R be the relational schema with n-attribute , R( A1,A2,.......An).
Number of super keys possible
(a) With only candidate key A1
(b) With only candidate key A1 , A2
(c) With only candidate key A1A2, A3A4
FUNCTIONAL DEPENDENCY
Let R be the relational schema and x,y be the non-empty set of attributes. t1, t2 are any tuples of relation then x→y ( y functionally determined by x ) if t1.x = t2.x then t1.y = t2.y
Trivial Functional dependency
If x⊇y then x→y is trivial dependency
Example:-
Sid→Sid
Sid Same→Sid
Non-trivial Functional Dependency
If x ⋂ y =Φ and if x→y satisfy functional dependency definition.
Example:-
Cid → Cname
Armstrong's Axioms
1. Reflexive : If x⊇y then x→y
2. Transitivity : If x→y and y→z then x→z
3. Argumentation : If x→y then xz→yz
4. Splitting : If x→yz then x→y then x→z
5. Union : If x→y and x→z then x→yz
6. Pseudo transitivity : If x→y, yw→z then xw→z
Attribute Closure (X+)
Set of all attributes functionally determined by X.
Example :-
R(ABCD) { A→B , A→C , C→D }
(A)+ = { ABCD }
Functional Dependency Set closure (F+)
Set of all trivial and non-trivial FD's derived from given FD set F. x→y is logically applied is given FD set F only if x+ should determine y.
Let R be the relational schema decomposed into R1 and R2. Given decomposition is loss-less only if
1. R1 ∪ R2 = R
2. R1 ⋂ R2 ≠ Õ“
3. R1 ⋂ R2 →R1 or R1 ⋂ R2 →R2
Example:-
R(ABCDE)
{ A→BC , CD→ E, B→D , E→A }
D = { ABC , ACDE } → Loss-less.
PROPERTIES OF DECOMPOSITION
Loss-less Join Decomposition
Because of decomposition there should not be generation of any additional tuples.
i.e R ≡ R1⋈ R2
Dependency Preserving Decomposition
Because of decomposition there should be any loss of any dependency . Let R be the relational schema with functional dependency set F is decomposed into R1,R2,....Rn with FD sets F1,F2,.....Fn
respectively . In general F1∪F2 ∪F3......Fn can be ⊆ F.
If F1 ∪ F2........∪ Fn ≡ F then decomposition is preserving dependency.
Limitation of Files System
1. To access database user should know about the physical details of the data.
2. Operating system fails to control concurrency when database is large.
3. Once got access user can access the whole data of the file system.
Database Design Goals
1. 0% redundancy
2. Loss-less join
3. Dependency preservation
According to Code
No two records of the table should be equal.
Difference between Primary Key and Alternative Key
1. At most one primary key allowed for any relation but more than one alternative keys are allowed.
2. Null values are not allowed in primary key.Null values are allowed in alternative key.
3. Primary keys used to design primary key,unique keys are used to design alternative keys.
Primary Key : Unique + not null
Super Key : Set of attribute used to differentiate all the tuples of the relation or super set of candidate key.
Example : Let R be the relational schema with n-attribute , R( A1,A2,.......An).
Number of super keys possible
(a) With only candidate key A1
(b) With only candidate key A1 , A2
(c) With only candidate key A1A2, A3A4
FUNCTIONAL DEPENDENCY
Let R be the relational schema and x,y be the non-empty set of attributes. t1, t2 are any tuples of relation then x→y ( y functionally determined by x ) if t1.x = t2.x then t1.y = t2.y
Trivial Functional dependency
If x⊇y then x→y is trivial dependency
Example:-
Sid→Sid
Sid Same→Sid
Non-trivial Functional Dependency
If x ⋂ y =Φ and if x→y satisfy functional dependency definition.
Example:-
Cid → Cname
Armstrong's Axioms
1. Reflexive : If x⊇y then x→y
2. Transitivity : If x→y and y→z then x→z
3. Argumentation : If x→y then xz→yz
4. Splitting : If x→yz then x→y then x→z
5. Union : If x→y and x→z then x→yz
6. Pseudo transitivity : If x→y, yw→z then xw→z
Attribute Closure (X+)
Set of all attributes functionally determined by X.
Example :-
R(ABCD) { A→B , A→C , C→D }
(A)+ = { ABCD }
Functional Dependency Set closure (F+)
Set of all trivial and non-trivial FD's derived from given FD set F. x→y is logically applied is given FD set F only if x+ should determine y.
Let R be the relational schema decomposed into R1 and R2. Given decomposition is loss-less only if
1. R1 ∪ R2 = R
2. R1 ⋂ R2 ≠ Õ“
3. R1 ⋂ R2 →R1 or R1 ⋂ R2 →R2
Example:-
R(ABCDE)
{ A→BC , CD→ E, B→D , E→A }
D = { ABC , ACDE } → Loss-less.
PROPERTIES OF DECOMPOSITION
Loss-less Join Decomposition
Because of decomposition there should not be generation of any additional tuples.
i.e R ≡ R1⋈ R2
Dependency Preserving Decomposition
Because of decomposition there should be any loss of any dependency . Let R be the relational schema with functional dependency set F is decomposed into R1,R2,....Rn with FD sets F1,F2,.....Fn
respectively . In general F1∪F2 ∪F3......Fn can be ⊆ F.
If F1 ∪ F2........∪ Fn ≡ F then decomposition is preserving dependency.
No comments