1 of 39

Advanced Database Design & Tuning

  • With Examples, Diagrams & MCQs

2 of 39

Schema Refinement

  • Organizing relations to minimize redundancy
  • Improves consistency

3 of 39

Redundancy Example

  • Employee(EmpID, Dept, Manager)
  • Manager repeated → redundancy

4 of 39

Anomalies

  • Insertion: Cannot add data
  • Deletion: Loss of info
  • Update: Inconsistency

5 of 39

Functional Dependencies

  • X → Y (X determines Y)

6 of 39

FD Example

  • RollNo → Name
  • Dept → HOD

7 of 39

Armstrong’s Axioms

  • Reflexivity, Augmentation, Transitivity

8 of 39

Closure Example

  • Find (A)+ where A→B, B→C ⇒ A+ = {A,B,C}

9 of 39

Normalization

  • Process to remove redundancy

10 of 39

1NF

  • Atomic values only

11 of 39

1NF Example

  • Multi-valued phone → separate rows

12 of 39

2NF

  • No partial dependency

13 of 39

2NF Example

  • Remove dependency on part of composite key

14 of 39

3NF

  • No transitive dependency

15 of 39

3NF Example

  • A→B, B→C ⇒ remove B

16 of 39

BCNF

  • Every determinant is a superkey

17 of 39

Decomposition

  • Split table into smaller tables

18 of 39

Lossless Join

  • Join gives original relation

19 of 39

Dependency Preservation

  • FDs maintained after split

20 of 39

MVD

  • A →→ B (independent multi-values)

21 of 39

Join Dependency

  • Complex dependency type

22 of 39

Physical DB Design

  • Focus on storage and access

23 of 39

Indexing

  • Speeds up data retrieval

24 of 39

Index Example

  • CREATE INDEX idx ON Student(Name)

25 of 39

Clustered Index

  • Data physically sorted

26 of 39

Non-Clustered Index

  • Separate index structure

27 of 39

Index-only Plan

  • Query satisfied using only index

28 of 39

Database Tuning

  • Improving performance

29 of 39

Query Optimization

  • Rewrite queries, avoid nested queries

30 of 39

Materialized Views

  • Precomputed results

31 of 39

Denormalization

  • Add redundancy for speed

32 of 39

Concurrency

  • Locks, Deadlocks, Transactions

33 of 39

Query Optimization Example

  • Before: SELECT *
  • After: SELECT specific columns

34 of 39

MCQ 1

  • Which normal form removes transitive dependency?
  • a)1NF b)2NF c)3NF d)BCNF

35 of 39

MCQ 2

  • FD means:
  • a)Field Data b)Functional Dependency c)Final Data d)None

36 of 39

MCQ 3

  • Lossless join ensures:
  • a)No data loss b)Fast query c)Less storage d)None

37 of 39

MCQ 4

  • Clustered index:
  • a)Logical b)Physical c)Temporary d)None

38 of 39

MCQ Answers

  • 1-c
  • 2-b
  • 3-a
  • 4-b

39 of 39

Summary

  • Good schema + Proper indexing + Tuning = High performance DB