# 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.

*Unique + not null*

**Primary Key :***Set of attribute used to differentiate all the tuples of the relation or super set of candidate key.*

**Super Key :***Let R be the relational schema with n-attribute , R( A1,A2,.......An).*

**Example :**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