Recent Post

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.

No comments