ITM531CS524 Object Oriented Modeling and Design

1
ITM531/CS524Object Oriented Modeling and Design

• Module 3
• Relational Normalization

2
What is a good database design?

• People claim a good relational design is based on
  normalized relations.
• Dependency theory is the basis for normalization.
• Algorithmic technique available.

3
Relational Normalization

• Dependency Theory
• Decomposition Theory
• Normal Forms
• Normalization algorithms

4
Functional dependency

• A Functional dependency for relation R(X Y Z)
  exists if for each X value in R there is a unique
  Y value associated with it in R.

5
Functional dependency application

• Assignments ( EMP. PROJ, DEPT, D-MGR, ACCT,
  SAL)
• EMP, PROJ ? DEPT / D-MGR / ACCT / SAL / EMP
  / PROJ
• EMP ? DEPT / SAL
• DEPT ? D-MGR

6
Functional dependency application (cont.)

• Better Design using functional dependency
  information
• Assignments ( EMP. PROJ, DEPT, D-MGR, ACCT,
  SAL)
• Normalized tables
• ASSIGN (EMP. PROJ, ACCT)
• EMP (EMP, DEPT, SAL)
• DEPT (DEPT, D-MGR)

7
Functional Dependency Closure

• Given FDs A ? B B ? C
• Then A ? can be derived
• The CLOSURE of a set of FDs (F), Denoted (F), is
  set F plus all FDs derived from F.

8
Carlson / Arora Axioms

• Axiom 1
• IF Y ? X ? Ui for some Ri (Ui)
• Then X ? Y
• Axiom 2
• IF X ? Y and X ? Z,
• Then X ? YZ

9
Carlson / Arora Axioms (cont.)

• Axiom 3
• If X ? Y and Y ? Z,
• Then X ? Z
• Axiom 4
• If Z? X and Z? Y and
• XY ? Ui for every Ri(Ui),
• Then XY ? X and XY ? Y

10
Carlson / Arora Axioms (cont.)

• Axiom 5 (Data dependency)
• If Z ? X Y, then X ?? Y
• Axiom 6 (Multi-valued dependency)
• If Z1 ? X, Z2 ? X, , ZN ? X,
• Then X ?gt Z1, Z2, ZN

11
Using Carlson / Arora Axioms

• By giving the following relations
• DIVISION ( DIV, DIV-DIR, DIV-NAME)
• DEPT (DEPT, D-MGR, DIV)
• EMP (EMP, DEPT, SAL, BLDG, OFFICE)
• ASSIGNMENTS (EMP, PROJ, ACCT, DIV)
• PROJECT (PROJ, PROJ-NAME, DIV)
• BUDGET (DIV, PROJ, BUDGET)
• COMPUTERS (COMP-ID, EMP, YEAR-PURCHASED)
• BUILDINGS (BLDG, ADDRESS, DIV)
• PROJ-RESOURCES (RESOURCE, PROJ, COST)

12
Applying Carlson / Arora Axioms

• Applying AXIOM 1 3
• Axiom 1 EMP, PROJ ? EMP PROJ
• GIVEN EMP ? DEPT
• Axiom 3 EMP, PROJ ? DEPT
• GIVEN PROJ ? PROJ-NAME
• Axiom 3 EMP, PROJ ? PROJ-NAME
• Axiom 2 EMP, PROJ ? DEPT, PROJ-NAME

13
Apply Carlson / Arora Axioms (cont.)

• Applying AXIOM 4
• ABOVE EMP, PROJ ? DEPT PROJ
• Axiom 4 DEPT, PROJ ? DEPT PROJ
• GIVEN DEPT ? DIV
• Axiom 3 DEPT, PROJ ? DIV
• Axiom 2 DEPT, PROJ ?DIV, PROJ
• GIVEN DIV, PROJ ? BUDGET
• Axiom 3 DEPT, PROJ ? BUDGET

14
Apply Carlson / Arora Axiom (cont.)

• Applying AXIOM 5
• ABOVE EMP, PROJ ? EMP PROJ
• Axiom 5 EMP ?? PROJ
• ABOVE DEPT, PROJ ? DIV BUDGET
• Axiom 5 DIV ?? BUDGET

15
Apply Carlson / Arora Axiom (cont.)

• Appling Axiom 6
• GIVEN COMP-ID ? EMP ? DEPT ? DIV
• GIVEN RESOURCE ? PROJ ? DIV
• GIVEN BLDG ? DIV
• Axiom 6 DIV ? COMP-ID, REOURCE, BLDG

16
Dependency Class relationships
17
Algorithms relationships (cont.)