Database System Concepts Chapter7 Relational Database Design by kxi15611

VIEWS: 19 PAGES: 86

									  Database
   System
  Concepts
                                        Database System Concepts
Relational                          Chapter 7: Relational Database Design
Design

Functional
Dependencies

Normal Forms

Other Design                                                                a
                                           Departamento de Engenharia Inform´tica
Issues                                                               e
                                                 Instituto Superior T´cnico
Some
Examples
                                                           1st Semester
                                                            2009/2010


               Slides (fortemente) baseados nos slides oficiais do livro
               “Database System Concepts” ( c Silberschatz, Korth
               and Sudarshan) e nos slides do prof. Pedro Sousa
               ( c Pedro Sousa)
               Outline

  Database
   System      1   Relational Design
  Concepts

               2   Functional Dependencies
Relational           Definition
Design
                     Properties
Functional
Dependencies
               3   Normal Forms
Normal Forms

Other Design
                     1st Normal Form
Issues               2nd Normal Form
Some
Examples
                     3rd Normal Form
                     Boyce-Codd Normal Form
                     Schema Decomposition
                     Other Normal Forms
               4   Other Design Issues
               5   Some Examples
               Outline

  Database
   System
  Concepts


               1   Relational Design
Relational
Design

Functional
Dependencies
               2   Functional Dependencies
Normal Forms

Other Design
Issues
               3   Normal Forms
Some
Examples
               4   Other Design Issues

               5   Some Examples
               What is a “Good” Schema?

  Database
   System
  Concepts



Relational
Design

Functional
Dependencies   Suppose that, instead of borrower and loan we have a single
Normal Forms   relation:
Other Design
Issues
                        loan(customer name, loan number , amount)
Some
Examples
               What is a “Good” Schema?

  Database
   System
  Concepts     Suppose that, instead of borrower and loan we have a single
               relation:
Relational
Design
                        loan(customer name, loan number , amount)
Functional
Dependencies

Normal Forms   Result would be possible repetition of information
Other Design
Issues
                           customer id    loan number     amount
Some
Examples                        ...             ...          ...
                              Jones           L-100        10 000
                              Smith           L-100        10 000
                             Lindsay          L-100        10 000
                                ...             ...          ...
               Data Redundancy

  Database
   System
  Concepts



Relational
Design            Data redundancy has implications in the coherence and
Functional        completeness of the data
Dependencies
                  The following problems (or anomalies) are well known:
Normal Forms
                      Insertion: occurs when independent facts cannot be
Other Design
Issues                inserted as independent data in the database
Some                  Deletion: occurs when, by deleting an item, the deletion of
Examples
                      other independent items also occurs
                      Update: occurs when updating an item implies the
                      alteration of other independent items
               An Example

  Database
   System
  Concepts



Relational
                  Take the following relation
Design

Functional
Dependencies
                         order (order id, item id, quantity , unit price)
Normal Forms
                  Anomalies occur when:
Other Design
Issues

Some
Examples
               An Example

  Database
   System
  Concepts



Relational
                  Take the following relation
Design

Functional
Dependencies
                         order (order id, item id, quantity , unit price)
Normal Forms
                  Anomalies occur when:
Other Design
Issues                Insertion: we can only store the price of an item if there
Some                  are orders for the item
Examples
               An Example

  Database
   System
  Concepts



Relational
                  Take the following relation
Design

Functional
Dependencies
                         order (order id, item id, quantity , unit price)
Normal Forms
                  Anomalies occur when:
Other Design
Issues                Insertion: we can only store the price of an item if there
Some                  are orders for the item
Examples
                      Deletion: if we delete an order, we loose the price
                      information of the items
               An Example

  Database
   System
  Concepts



Relational
                  Take the following relation
Design

Functional
Dependencies
                         order (order id, item id, quantity , unit price)
Normal Forms
                  Anomalies occur when:
Other Design
Issues                Insertion: we can only store the price of an item if there
Some                  are orders for the item
Examples
                      Deletion: if we delete an order, we loose the price
                      information of the items
                      Update: if we change the price of an item, we need to
                      update all the orders containing the item
               A Theory for “Good” Relational Design

  Database
   System
  Concepts



Relational
Design
               We need a formal method to:
Functional         Decide whether a particular relation R is in “good” form.
Dependencies

Normal Forms
                   In the case that a relation R is not in good form,
Other Design
                   decompose it into a set of relations {R1 , R2 , . . . , Rn } such
Issues             that
Some                    each relation is in good form
Examples
                        the decomposition has no loss of information
                   Our theory is based on functional dependencies
               Outline

  Database
   System
  Concepts

               1   Relational Design
Relational
Design

Functional
               2   Functional Dependencies
Dependencies
Definition
                     Definition
Properties           Properties
Normal Forms

Other Design
Issues         3   Normal Forms
Some
Examples
               4   Other Design Issues

               5   Some Examples
               An Example Revisited

  Database
   System
  Concepts

                   The problem with the relation
Relational
Design

Functional
                          order (order id, item id, quantity , unit price)
Dependencies
Definition
Properties
                   as to do with dependency relations between its attributes
Normal Forms           Through the order id we know the item id, quantity and
Other Design           unit price
Issues
                       But we need only the item id to know the unit price
Some
Examples           We say that:
                       the order id determines the item id, quantity and
                       unit price
                       the item id determines the unit price
               Functional Dependencies

  Database
   System
  Concepts
                   Let R be a relation schema where

                                        α ⊆ R and β ⊆ R
Relational
Design

Functional         The functional dependency
Dependencies
Definition
Properties                                    α→β
Normal Forms

Other Design       holds on R if and only if, for any legal relations r (R), for
Issues

Some
                   any tuples t1 and t2 of r
Examples

                                  t1 [α] = t2 [α] ⇒ t1[β] = t2[β]

                   In other words
                             α determines β if, for every value of α there is
                             one and only one value of β
               Functional Dependencies (cont.)

  Database
   System
  Concepts

               Example
Relational
Design                      A   B
Functional
Dependencies
                            1   4      Does A → B hold?
Definition                   1   5
Properties                             Does B → A hold?
Normal Forms                3   7
Other Design
Issues

Some
Examples
               Functional Dependencies (cont.)

  Database
   System
  Concepts

               Example
Relational
Design                           A   B
Functional
Dependencies
                                 1   4         Does A → B hold?
Definition                        1   5
Properties                                     Does B → A hold?
Normal Forms                     3   7
Other Design
Issues

Some
                   Functional dependencies are determined from the
Examples           semantics of the context we are trying to model
                         They can not be determined from a given set of tuples
                         From a set of tuple we can only verify that a given
                         dependency does not hold
               Functional Dependencies and Keys

  Database
   System           A functional dependency is a generalization of the notion
  Concepts
                    of a key
                          We say that K is a superkey of r (R) if K → R
Relational
Design
                          K is a candidate key for r (R) if and only if
                               K → R and
Functional
Dependencies                   for no α ⊂ K , α → R
Definition
Properties          Functional dependencies allow us to express constraints
Normal Forms
                    that cannot be expressed using superkeys.
Other Design
Issues
               Example
Some
Examples       Consider the relation loan(customer id, loan number , amount). We expect
               this functional dependency to hold:

                                        loan number → amount

               but would not expect the following to hold:

                                      amount → customer name
               Properties of Functional Dependencies

  Database
   System
  Concepts
                   Given a set F set of functional dependencies, there are
                   certain other functional dependencies that are logically
                   implied by F
Relational
Design                  For example: If A → B and B → C , we can infer that
Functional              A→C
Dependencies
Definition          The set of all functional dependencies logically implied by
Properties
                   F is the closure of F
Normal Forms

Other Design
                        We denote the closure of F by F +
Issues
                   We can find all of F + by applying
Some
Examples
               Armstrong’s Axioms
                   Reflexivity: if β ⊆ α, then α → β
                   Augmentation: if α → β, then γα → γβ
                   Transitivity: if α → β, and β → γ, then α → γ
               Procedure for Computing F +

  Database
   System
  Concepts



Relational
Design
               F+ = F
Functional
               repeat
Dependencies
Definition
                 for each functional dependency f in F +
Properties         apply reflexivity and augmentation rules on f
Normal Forms
                   add the resulting functional dependencies to F +
Other Design
Issues           for each pair of functional dependencies f1 and f2 in F +
Some               if f1 and f2 can be combined using transitivity
Examples
                      then add the resulting functional dependency to F +
                 until F + does not change any further
               An Example

  Database
   System
  Concepts
                  Consider the relation R(A, B, C , G , H, I ) and the set

                                        F ={A→B
Relational
Design
                                                A→C
Functional
Dependencies                                  CG → H
Definition
Properties                                    CG → I
Normal Forms

Other Design
                                               B→H }
Issues

Some              Some members of F + are
Examples
                       A → H by transitivity from A → B and B → H
                       AG → I by augmenting A → C with G , to get AG → CG
                       and then transitivity with CG → I
                       CG → HI by augmenting CG → I to infer CG → CGI ,
                       and augmenting of CG → H to infer CGI → HI , and then
                       transitivity
               Derived Rules

  Database
   System
  Concepts



Relational         We can further simplify manual computation of F + by
Design
                   using the following additional rules:
Functional
Dependencies           Self-reflexivity: α → α holds
Definition
Properties
                       Union: If α → β holds and α → γ holds, then α → βγ
Normal Forms
                       holds
Other Design
                       Decomposition: If α → βγ holds, then α → β holds and
Issues                 α → γ holds
Some                   Pseudotransitivity: If α → β holds and γβ → δ holds, then
Examples
                       αγ → δ holds
                   The above rules can be inferred from Armstrong’s axioms
               Closure of Attribute Sets

  Database
   System
  Concepts
               Definition
Relational
               Given a set of attributes α we define the closure of α under F
Design
               (denoted by α+ ) as the set of attributes that are functionally
Functional
Dependencies   determined by α under F
Definition
Properties

Normal Forms       Algorithm to compute α+ :
Other Design
Issues             result = α;
Some               while (changes to result) do
Examples
                      for each β → γ in F do
                      begin
                        if β ⊆ result then result = result ∪ γ
                      end
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties

Normal Forms

Other Design
Issues

Some
Examples
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties
                 1   result = AG
Normal Forms

Other Design
Issues

Some
Examples
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties
                 1   result = AG
Normal Forms     2   result = ABCG (A → C and A → B)
Other Design
Issues

Some
Examples
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties
                 1   result = AG
Normal Forms     2   result = ABCG (A → C and A → B)
Other Design
Issues           3   result = ABCGH (CG → H and CG ⊆ AGBC )
Some
Examples
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties
                 1   result = AG
Normal Forms     2   result = ABCG (A → C and A → B)
Other Design
Issues           3   result = ABCGH (CG → H and CG ⊆ AGBC )
Some
Examples
                 4   result = ABCGHI (CG → I and CG ⊆ AGBCH)
               An Example

  Database
   System
  Concepts

               Consider the relation R(A, B, C , G , H, I ) and the set
Relational     F = {A → B, A → C , CG → H, CG → I , B → H}
Design

Functional
Dependencies
               Compute (AG )+ :
Definition
Properties
                 1   result = AG
Normal Forms     2   result = ABCG (A → C and A → B)
Other Design
Issues           3   result = ABCGH (CG → H and CG ⊆ AGBC )
Some
Examples
                 4   result = ABCGHI (CG → I and CG ⊆ AGBCH)


                                        AG → ABCGHI
               Closure and Keys

  Database
   System
  Concepts

               The following questions are equivalent
Relational
Design             Is AG a superkey?
Functional
Dependencies
                   Does AG → R?
Definition
Properties         Is (AG )+ ⊇ R?
Normal Forms
               The following questions are equivalent:
Other Design
Issues             Is AG a candidate key?
Some
Examples           Is any subset of AG a superkey?
                   Does A → R or G → R?
                   Is A+ ⊇ R or G + ⊇ R?
                  Outline

  Database
   System
  Concepts        1   Relational Design

Relational
Design
                  2   Functional Dependencies
Functional
Dependencies
                  3   Normal Forms
Normal Forms
1st Normal Form         1st Normal Form
2nd Normal
Form                    2nd Normal Form
3rd Normal
Form
Boyce-Codd
                        3rd Normal Form
Normal Form
Schema
                        Boyce-Codd Normal Form
Decomposition
Other Normal            Schema Decomposition
Forms

Other Design
                        Other Normal Forms
Issues

Some
Examples          4   Other Design Issues
                  Normal Forms

  Database
   System
  Concepts        1st Normal Form
                  Regards the structure of each attribute in a relation
Relational
Design

Functional
Dependencies

Normal Forms
1st Normal Form
2nd Normal
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Normal Forms

  Database
   System
  Concepts        1st Normal Form
                  Regards the structure of each attribute in a relation
Relational
Design
                     2nd Normal Form
Functional
Dependencies         Regards the dependencies between key and non-key
Normal Forms         attributes
1st Normal Form
2nd Normal
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Normal Forms

  Database
   System
  Concepts        1st Normal Form
                  Regards the structure of each attribute in a relation
Relational
Design
                     2nd Normal Form
Functional
Dependencies         Regards the dependencies between key and non-key
Normal Forms         attributes
1st Normal Form
2nd Normal
Form                       3rd Normal Form
3rd Normal
Form
Boyce-Codd                 Regards inter-dependencies between non-key
Normal Form
Schema
Decomposition
                           attributes
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Normal Forms

  Database
   System
  Concepts        1st Normal Form
                  Regards the structure of each attribute in a relation
Relational
Design
                     2nd Normal Form
Functional
Dependencies         Regards the dependencies between key and non-key
Normal Forms         attributes
1st Normal Form
2nd Normal
Form                       3rd Normal Form
3rd Normal
Form
Boyce-Codd                 Regards inter-dependencies between non-key
Normal Form
Schema
Decomposition
                           attributes
Other Normal
Forms                             Boyce-Codd Normal Form
Other Design
Issues                            Regards inter-dependencies
Some                              between key attributes
Examples
                  1st Normal Form

  Database
   System
  Concepts        Definition
                  A relational schema R is in 1st normal form if the domains of
Relational
Design
                  all attributes of R are atomic
Functional
Dependencies           Domain is atomic if its elements are considered to be
Normal Forms
1st Normal Form
                       indivisible units
2nd Normal
Form                   Atomicity is actually a property of how the elements of the
3rd Normal
Form                   domain are used
Boyce-Codd
Normal Form                 Example: Suppose that students are given roll numbers
Schema
Decomposition               which are strings of the form CS0012 or EE1127
Other Normal
Forms                       If the first two characters are extracted to find the
Other Design                department, the domain of roll numbers is not atomic
Issues
                            Doing so is a bad idea: leads to encoding of information in
Some
Examples                    application program rather than in the database
                  Note: we will assume all relations are in first normal form.
                  Problems of the 1st Normal Form

  Database
   System
  Concepts



Relational
Design                Consider the relation
Functional
Dependencies
                              works(person id, project, budget, time spent)
Normal Forms
1st Normal Form
2nd Normal
Form
                      where
3rd Normal
Form                                       project → budget
Boyce-Codd
Normal Form
Schema
Decomposition
                      It is in 1NF, however it still has the insertion, update and
Other Normal
Forms
                      deletion anomalies
Other Design
Issues

Some
Examples
                  2nd Normal Form

  Database
   System
                      Some useful definitions:
  Concepts                 An attribute A is completely dependent on a set of
                           attributes α if α → A and no subset of α determines A
Relational                 We say an attribute is a key attribute is it belongs to a
Design
                           candidate key
Functional
Dependencies
                  Definition
Normal Forms
1st Normal Form
2nd Normal
                  A relational schema R is in 2nd normal form if it is in 1NF and
Form
3rd Normal
                  every non-key attribute is completely dependent on the
Form
Boyce-Codd        candidate keys
Normal Form
Schema
Decomposition
Other Normal
Forms
                      Every 1NF relation with only non-composite candidate
Other Design
                      keys are also 2NF
Issues
                      Nothing is said regarding inter-dependencies between key
Some
Examples              attributes
                      Nothing is said regarding inter-dependencies between
                      non-key attributes
                  An Example

  Database
   System
  Concepts
                     Consider the relation
Relational
Design                                       R(A, B, C , D)
Functional
Dependencies
                     where A → D (note that AB → C and AB → D are
Normal Forms
1st Normal Form      implied)
2nd Normal
Form
3rd Normal
                     Is R in 2NF?
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  An Example

  Database
   System
  Concepts
                     Consider the relation
Relational
Design                                       R(A, B, C , D)
Functional
Dependencies
                     where A → D (note that AB → C and AB → D are
Normal Forms
1st Normal Form      implied)
2nd Normal
Form
3rd Normal
                     Is R in 2NF?
Form
Boyce-Codd
Normal Form
Schema               Decomposing R yields
Decomposition
Other Normal
Forms

Other Design
                                     R1(A, B, C ) and R2(A, D)
Issues

Some                 Both relations are now in 2NF.
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design

Functional
                                exam(student, course, teacher , grade)
Dependencies

Normal Forms          where course → teacher
1st Normal Form
2nd Normal
Form
                      Is R in 2NF?
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design

Functional
                                exam(student, course, teacher , grade)
Dependencies

Normal Forms          where course → teacher
1st Normal Form
2nd Normal
Form
                      Is R in 2NF?
3rd Normal
Form
Boyce-Codd
Normal Form           Decomposing exam yields
Schema
Decomposition
Other Normal
Forms                 exam(student, course, grade) and course(course, teacher )
Other Design
Issues
                      Both relations are now in 2NF.
Some
Examples
                  Yet Another Example

  Database
   System
  Concepts



Relational            Consider the relation
Design

Functional
Dependencies
                             order (order id, item id, quantity , unit price)
Normal Forms
1st Normal Form       where item id → unit price
2nd Normal
Form
3rd Normal
                      Is R in 2NF?
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Yet Another Example

  Database
   System
  Concepts



Relational            Consider the relation
Design

Functional
Dependencies
                             order (order id, item id, quantity , unit price)
Normal Forms
1st Normal Form       where item id → unit price
2nd Normal
Form
3rd Normal
                      Is R in 2NF?
Form
Boyce-Codd
Normal Form
Schema                                        Yes it is!
Decomposition
Other Normal
Forms
                                              And yet...
Other Design
Issues

Some
Examples
                  Problems of the 2nd Normal Form

  Database
   System
  Concepts



Relational
Design                2NF does not guarantee that there is no redundancy
Functional                For instance, consider updating the unit price of an item in
Dependencies
                          relation
Normal Forms
1st Normal Form
2nd Normal                       order (order id, item id, quantity , unit price)
Form
3rd Normal
Form
Boyce-Codd
                          where item id → unit price
Normal Form
Schema
Decomposition
                      Redundancy arises because of dependencies between
Other Normal
Forms                 non-key attributes
Other Design
Issues

Some
Examples
                  Eliminating the Anomalies

  Database
   System
  Concepts



Relational
                      To eliminate the anomalies, we need to eliminate the
Design                dependencies between non-key attributes
Functional
Dependencies
                          I.e. item id → unit price
Normal Forms          We can do this by splitting the relation into
1st Normal Form
2nd Normal
Form
3rd Normal
                                   order (order id, item id, quantity )
Form
Boyce-Codd
Normal Form
                                   item(item id, unit price)
Schema
Decomposition
Other Normal
Forms
                      The dependencies still hold, but now there are no
Other Design          dependencies between non-key attributes
Issues

Some
Examples
                  3rd Normal Form

  Database
   System
  Concepts



Relational
Design
                  Definition
Functional
Dependencies
                  A relational schema R is in 3rd normal form if it is in 2NF and
Normal Forms
                  there are no dependencies between non-key attributes
1st Normal Form
2nd Normal
Form
3rd Normal            All relations that have only one non-key attribute and are
Form
Boyce-Codd
Normal Form
                      2NF are also 3NF
Schema
Decomposition         Nothing is said regarding inter-dependencies between key
Other Normal
Forms                 attributes
Other Design
Issues

Some
Examples
                  An Example

  Database
   System
  Concepts
                     Consider the relation

Relational
Design
                                             R(A, B, C )
Functional
Dependencies         where B → C
Normal Forms
1st Normal Form
                     Is R in 3NF?
2nd Normal
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  An Example

  Database
   System
  Concepts
                     Consider the relation

Relational
Design
                                             R(A, B, C )
Functional
Dependencies         where B → C
Normal Forms
1st Normal Form
                     Is R in 3NF?
2nd Normal
Form
3rd Normal           Decomposing R yields
Form
Boyce-Codd
Normal Form
Schema
Decomposition                         R1(B, C ), where B → C
Other Normal
Forms
                                      R2(A, B), where A → B
Other Design
Issues

Some
                     Both relations are now in 3NF, i.e., there are no
Examples             dependencies between non-key attributes
                  Another Example

  Database
   System             Consider the relation
  Concepts


                        employee(id, name, department number ,
Relational
Design                                            section number , manager )
Functional
Dependencies
                      where department number → section number and
Normal Forms
1st Normal Form       section number → manager
2nd Normal
Form
3rd Normal
                      Is employee in 3NF?
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Another Example

  Database
   System             Consider the relation
  Concepts


                        employee(id, name, department number ,
Relational
Design                                               section number , manager )
Functional
Dependencies
                      where department number → section number and
Normal Forms
1st Normal Form       section number → manager
2nd Normal
Form
3rd Normal
                      Is employee in 3NF?
Form
Boyce-Codd
Normal Form
Schema
                      Decomposing employee yields
Decomposition
Other Normal
Forms                     employee(id, name, department number )
Other Design
Issues                    department(department number , section number )
Some
Examples                  section(section number , manager )

                      All relations are now in 3NF
                  Yet Another Example

  Database
   System
  Concepts
                      Consider the relation
Relational
Design                works(employee id, department id, budget, time, project id)
Functional
Dependencies
                      where project id → budget
Normal Forms
1st Normal Form
2nd Normal
                      Is works in 3NF?
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Yet Another Example

  Database
   System
  Concepts
                      Consider the relation
Relational
Design                works(employee id, department id, budget, time, project id)
Functional
Dependencies
                      where project id → budget
Normal Forms
1st Normal Form
2nd Normal
                      Is works in 3NF?
Form
3rd Normal
Form
Boyce-Codd
                      Decomposing works yields
Normal Form
Schema
Decomposition
Other Normal
                         works(employee id, department id, time, project id)
Forms

Other Design             project(project id, budget)
Issues

Some
Examples
                      Both relations are now in 3NF
                  A Final Example

  Database
   System
  Concepts
                      Consider the relation
Relational
Design                          uses(project, director , item, quantity )
Functional
Dependencies          where
Normal Forms
1st Normal Form                         project, item → quantity
2nd Normal
Form
3rd Normal
                                               project → director
Form
Boyce-Codd
Normal Form
                                              director → project
Schema
Decomposition                          director , item → quantity
Other Normal
Forms

Other Design          In which normal form is R?
Issues

Some
Examples
                  A Final Example

  Database
   System
  Concepts
                      Consider the relation
Relational
Design                          uses(project, director , item, quantity )
Functional
Dependencies          where
Normal Forms
1st Normal Form                         project, item → quantity
2nd Normal
Form
3rd Normal
                                               project → director
Form
Boyce-Codd
Normal Form
                                              director → project
Schema
Decomposition                          director , item → quantity
Other Normal
Forms

Other Design          In which normal form is R?
Issues

Some
Examples
                      R is in 3NF, however...
                  Problems with the 3rd Normal Form

  Database
   System
  Concepts

                      The relation
Relational
Design
                                uses(project, director , item, quantity )
Functional
Dependencies

Normal Forms          still allows some anomalies
1st Normal Form
2nd Normal
                          The same project director is stored several times
Form
3rd Normal
                          The director is stored only when items are used
Form
Boyce-Codd
                          The project is stored only when the director is known
Normal Form
Schema
                          Changing the project’s director may imply changing several
Decomposition
Other Normal
                          tuples
Forms

Other Design          This anomalies arise from the fact that there are
Issues
                      dependencies between key attributes (director and project)
Some
Examples
                  Boyce-Codd Normal Form

  Database
   System
  Concepts        Definition
                  A relational schema R is in Boyce-Codd Normal Form if it is
Relational
Design
                  3NF and every determinant is a superkey
Functional
Dependencies
                      In other words, for every dependency α → β in R + , either
Normal Forms
1st Normal Form       it is a trivial dependency (i.e., β ⊆ α), or α is a superkey
2nd Normal
Form                  In BCNF
3rd Normal
Form
Boyce-Codd
                           There are no dependencies between non-key attributes
Normal Form
Schema
                           (3NF)
Decomposition
Other Normal
                           There are no dependencies between subsets of key
Forms
                           attributes
Other Design
Issues                BCNF is different from 3NF only when
Some                       There is more than one candidate key
Examples
                           Candidate keys are composed of several attributes
                  An Example

  Database
   System
  Concepts
                     Consider the relation

Relational
Design
                                             R(A, B, C , D)
Functional
Dependencies         where AB → CD, BC → AD, and A → C
Normal Forms
1st Normal Form
                     Is R in BCNF?
2nd Normal
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  An Example

  Database
   System
  Concepts
                     Consider the relation

Relational
Design
                                             R(A, B, C , D)
Functional
Dependencies         where AB → CD, BC → AD, and A → C
Normal Forms
1st Normal Form
                     Is R in BCNF?
2nd Normal
Form
3rd Normal
Form
                     Decomposing R yields
Boyce-Codd
Normal Form
Schema
Decomposition                                R1(A, C )
Other Normal
Forms
                                             R2(A, B, D)
Other Design
Issues

Some
                     Both relations are now in BCNF
Examples                 Note that not all dependencies where preserved
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
                      What are the possible anomalies?
Boyce-Codd
Normal Form
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
                      What are the possible anomalies?
Boyce-Codd
Normal Form           What are the problems with the following decompositions?
Schema
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
                      What are the possible anomalies?
Boyce-Codd
Normal Form           What are the problems with the following decompositions?
Schema
Decomposition
Other Normal
Forms
                          class1(student, teacher ) and class2(course, teacher )
Other Design
Issues

Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
                      What are the possible anomalies?
Boyce-Codd
Normal Form           What are the problems with the following decompositions?
Schema
Decomposition
Other Normal
Forms
                          class1(student, teacher ) and class2(course, teacher )
Other Design
Issues
                          class1(course, student) and class2(course, teacher )
Some
Examples
                  Another Example

  Database
   System
  Concepts

                      Consider the relation
Relational
Design
                                    class(course, student, teacher )
Functional
Dependencies

Normal Forms
                      where teacher → course
1st Normal Form
2nd Normal            In which normal form is relation class?
Form
3rd Normal
Form
                      What are the possible anomalies?
Boyce-Codd
Normal Form           What are the problems with the following decompositions?
Schema
Decomposition
Other Normal
Forms
                          class1(student, teacher ) and class2(course, teacher )
Other Design
Issues
                          class1(course, student) and class2(course, teacher )
Some
                          class1(course, student) and class2(student, teacher )
Examples
                  Schema Decomposition

  Database
   System               When we decompose a schema to achieve normalization,
  Concepts
                        some loss of information may occur
Relational        Example
Design

Functional
Dependencies       1   Consider the relation
Normal Forms           r (A, B, C ), where A → B and
1st Normal Form        C →B
2nd Normal
Form                             A    B   C
3rd Normal
Form                             1    X   1
Boyce-Codd                       3    X   2
Normal Form                      2    Y   3
Schema                           4    Y   4
Decomposition
Other Normal
Forms

Other Design
Issues

Some
Examples
                  Schema Decomposition

  Database
   System               When we decompose a schema to achieve normalization,
  Concepts
                        some loss of information may occur
Relational        Example
Design

Functional
Dependencies       1   Consider the relation
Normal Forms           r (A, B, C ), where A → B and
1st Normal Form        C →B
2nd Normal
Form                              A   B   C
3rd Normal
Form                              1   X   1
Boyce-Codd                        3   X   2
Normal Form                       2   Y   3
Schema                            4   Y   4
Decomposition
Other Normal
Forms              2   We can decompose into to
Other Design           r1 (A, B) and r2 (B, C )
Issues
                              A   B       B   C
Some                          1   X       X   1
Examples                      3   X       X   2
                              2   Y       Y   3
                              4   Y       Y   4
                  Schema Decomposition

  Database
   System               When we decompose a schema to achieve normalization,
  Concepts
                        some loss of information may occur
Relational        Example
Design

Functional
Dependencies       1   Consider the relation
Normal Forms           r (A, B, C ), where A → B and   3   However, when we perform
1st Normal Form        C →B                                r1 ⋊ r2 , we get
                                                              ⋉
2nd Normal
Form
3rd Normal
                                  A   B   C                         A   B   C
                                  1   X   1
Form
Boyce-Codd                        3   X   2                         1   X   1
Normal Form                       2   Y   3                         1   X   2
Schema                            4   Y   4
Decomposition                                                       3   X   1
Other Normal
Forms              2   We can decompose into to                     3   X   2
Other Design           r1 (A, B) and r2 (B, C )                     2   Y   3
Issues
                              A   B       B   C                     2   Y   4
Some                          1   X       X   1                     4   Y   3
Examples                      3   X       X   2
                              2   Y       Y   3                     4   Y   4
                              4   Y       Y   4
                  Lossless-Join Decomposition

  Database
   System         Definition
  Concepts
                  Consider the relational schema R = (R1 , R2 ). A decomposition of r (R)
                  into R1 and R2 is lossless-join if
Relational
Design
                                              r = πR1 (r ) ⋊ πR2 (r )
                                                           ⋉
Functional
Dependencies

Normal Forms      Theorem (Heath’s Theorem)
1st Normal Form
2nd Normal        A decomposition of R into R1 and R2 is lossless-join if and only if at least
Form
3rd Normal
                  one of the following dependencies is in F + :
Form
Boyce-Codd
Normal Form                                      R1 ∩ R2 → R1
Schema
Decomposition                                    R1 ∩ R2 → R2
Other Normal
Forms

Other Design
Issues            Example
Some              In the previous example, the decomposition into r1 (A, B) and r2 (A, C ) is a
Examples
                  lossless-join decomposition, since {A, B} ∩ {A, C } → {A, B} (because
                  A → B).
                  Dependency Preservation

  Database
   System
  Concepts


                      Suppose we decompose r (R) into R1 , R2 , ..., Rn
Relational
Design

Functional
                  Definition
Dependencies
                  Let Fi be the set of dependencies in F + that include only
Normal Forms
1st Normal Form   attributes in Ri . A decomposition is dependency preserving if
2nd Normal
Form
3rd Normal
Form                                  (F1 , F2 , . . . , Fn )+ = F +
Boyce-Codd
Normal Form
Schema
Decomposition         If it is not, then checking updates for violation of
Other Normal
Forms                 functional dependencies may require computing joins,
Other Design
Issues
                      which is expensive.
Some
Examples
                  Dependency Preservation (cont.)

  Database
   System
  Concepts

                       There are some situations where
Relational                   BCNF is not dependency preserving, and
Design
                             efficient checking for FD violation on updates is important
Functional
Dependencies           That is why we need the 3rd Normal Form
Normal Forms                 Allows some redundancy (with resultant problems)
1st Normal Form
2nd Normal                   But functional dependencies can be checked on individual
Form
3rd Normal                   relations without computing a join.
Form
Boyce-Codd                   There is always a lossless-join, dependency-preserving
Normal Form
Schema
Decomposition
                             decomposition into 3NF
Other Normal
Forms
                  Note: It is always possible to decompose a relation into a set of relations
Other Design
Issues            that are in BCNF such that the decomposition is lossless, but it may not
Some              be possible to preserve dependencies.
Examples
                  Relational Design Goals

  Database
   System
  Concepts
                      Goal for a relational database design is:
                          BCNF
Relational
Design
                          Lossless join
Functional
                          Dependency preservation
Dependencies
                      If we cannot achieve this, we accept one of
Normal Forms
1st Normal Form
                          Lack of dependency preservation
2nd Normal                Redundancy due to use of 3NF
Form
3rd Normal
Form                  Interestingly, SQL does not provide a direct way of
Boyce-Codd
Normal Form           specifying functional dependencies other than superkeys
Schema
Decomposition
Other Normal
                          Can specify FDs using assertions, but they are expensive to
Forms
                          test
Other Design
Issues
                          Even if we had a dependency preserving decomposition,
Some
                          using SQL we would not be able to efficiently test a
Examples                  functional dependency whose left hand side is not a key
                  4th Normal Form

  Database
   System
  Concepts


                      Multivalued dependencies regard dependencies between
Relational
Design                the values present in tuples
Functional                For instance, in a relation teaches(course, teacher , book)
Dependencies
                          we can say that course multidetermines teacher and
Normal Forms
1st Normal Form
                          course multidetermines book (denoted by
2nd Normal
Form
                          course →→ teacher and course →→ book)
3rd Normal
Form
                          In this case, if we were to add a new book to the course,
Boyce-Codd
Normal Form
                          we would have to add a new tuple for each of the course
Schema
Decomposition
                          teachers
                      The 4th Normal Form requires that a database has no
Other Normal
Forms

Other Design
Issues
                      multivalued dependencies
Some
Examples
                  Other Normal Forms

  Database
   System
  Concepts

                      The 5th Normal Form (a.k.a. project-join normal form)
Relational            generalizes multivalued dependencies through the concept
Design

Functional
                      of join dependencies
Dependencies
                      The Domain-key Normal Form (DKNF) requires that the
Normal Forms
1st Normal Form
                      database contains no constraints other than domain
2nd Normal
Form                  constraints and key constraints
3rd Normal
Form
Boyce-Codd
                      The 6th Normal Form defined when extending the relational
Normal Form
Schema                model to take into account the temporal dimension (
Decomposition
Other Normal
Forms                 Problem with these generalized constraints: are hard to
Other Design          reason with, and no set of sound and complete set of
Issues

Some
                      inference rules exists. Hence rarely used.
Examples
               Outline

  Database
   System
  Concepts


               1   Relational Design
Relational
Design

Functional
Dependencies
               2   Functional Dependencies
Normal Forms

Other Design
Issues
               3   Normal Forms
Some
Examples
               4   Other Design Issues

               5   Some Examples
               ER Model and Normalization

  Database
   System
  Concepts
                   When an E-R diagram is carefully designed, identifying all
Relational
                   entities correctly, the tables generated from the E-R
Design             diagram should not need further normalization
Functional
Dependencies       However, in a real (imperfect) design, there can be
Normal Forms
                   functional dependencies from non-key attributes of an
Other Design
                   entity to other attributes of the entity
Issues
                       Example: an employee entity with attributes
Some
Examples
                       department number and department address, and a
                       functional dependency
                       department number → department address
                       Good design would have made department an entity
                   Functional dependencies from non-key attributes of a
                   relationship set possible, but rare
               Other Design Issues

  Database
   System
  Concepts
                   Some aspects of database design are not caught by
                   normalization
Relational
Design             Examples of bad database design, to be avoided:
Functional             Instead of earnings(company id, year , amount), use
Dependencies
                       earnings 2000, earnings 2001, earnings 2002, etc., all on
Normal Forms           the schema (companyi d, earnings)
Other Design                Above are in BCNF, but make querying across years
Issues
                            difficult and needs new table each year
Some
Examples               Use company year (company id, earnings 2000,
                       earnings 2001, earnings 2002)
                            Also in BCNF, but also makes querying across years
                            difficult and requires new attribute each year
                            Is an example of a crosstab, where values for one attribute
                            become column name
                            Used in spreadsheets and in data analysis tools
               Denormalization for Performance

  Database
   System
  Concepts         May want to use non-normalized schema for performance
                   For example, displaying customer name along with
Relational         account number and balance requires join of account
Design

Functional
                   with depositor
Dependencies
                   Alternative 1: Use denormalized relation containing
Normal Forms       attributes of account as well as depositor with all above
Other Design
Issues
                   attributes
Some
                       faster lookup
Examples               extra space and extra execution time for updates
                       extra coding work for programmer and possibility of error
                       in extra code
                   Alternative 2: use a materialized view, defined as
                   account ⋊ depositor
                            ⋉
                       Benefits and drawbacks same as above, except no extra
                       coding work for programmer and avoids possible errors
               Modeling Temporal Data

  Database
   System
  Concepts
                   Temporal data have an association time interval during
                   which the data are valid
Relational
Design             A snapshot is the value of the data at a particular point in
Functional
Dependencies
                   time
Normal Forms       Adding a temporal component results in functional
Other Design       dependencies like
Issues

Some
Examples                 customer id → customer street, customer city

                   not to hold, because the address varies over time
                   A temporal functional dependency holds on schema R if
                   the corresponding functional dependency holds on all
                   snapshots for all legal instances r (R)
               Outline

  Database
   System
  Concepts


               1   Relational Design
Relational
Design

Functional
Dependencies
               2   Functional Dependencies
Normal Forms

Other Design
Issues
               3   Normal Forms
Some
Examples
               4   Other Design Issues

               5   Some Examples
               Example 1

  Database
   System
  Concepts
                   Consider the following E-R diagram
                                               item_id
                              price
Relational
Design

Functional                                                       order_number
                                        item
Dependencies

Normal Forms

Other Design                                                              date
                             quantity
Issues
                                                         order
Some
Examples

                                                                     client_name

                                                client_id



                   If we convert it to relational “as is”, in which NF will the
                   resulting schema be?
                   What anomalies can occur?
               Example 2

  Database
   System
  Concepts         Consider the following relation

Relational                               R(A, B, C , D)
Design

Functional
Dependencies
                   where the following dependencies hold
Normal Forms
                                                B→D
Other Design
Issues
                                                D→B
Some
Examples                                   ABD → C

                   What are the candidate keys?
                   In which normal form is R?
                   How can we decompose it into BCNF relations?
                   Are any dependencies lost?
               Example 3

  Database
   System
  Concepts

                   Consider the following relation
Relational
Design
                                         R(A, B, C , D)
Functional
Dependencies

Normal Forms       where the following dependencies hold
Other Design
Issues                                      A → BCD
Some
Examples                                    B → ACD
                                          CD → AB

                   What are the candidate keys?
                   In which normal form is R?
               Example 4

  Database
   System
  Concepts
                   Consider the following relation
Relational
Design                                  R(A, B, C , D, E )
Functional
Dependencies
                   where the following dependencies hold
Normal Forms

Other Design
Issues
                                          AB → CDE
Some                                        D→E
Examples

                   What are the candidate keys?
                   In which normal form is R?
                   If instead of AB → CDE we had that ABD → CE , in
                   which normal form would R be?
               Example 5

  Database
   System
  Concepts
                   Consider the following relation

Relational                               R(A, B, C , D)
Design

Functional
Dependencies       where the following dependencies hold
Normal Forms

Other Design                               AB → CD
Issues

Some
Examples
                   Indicate a functional dependency that would make R not
                   be in the 2NF.
                   Indicate a functional dependency that would make R be in
                   2NF, but not be in the 3NF.
                   Indicate a functional dependency that would make R be in
                   3NF, but not be in the BCNF.
               Example 6

  Database
   System
  Concepts
                   Consider the following relation
Relational
Design                                     R(A, B, C )
Functional
Dependencies
                   and its decomposition into
Normal Forms

Other Design
Issues                              R1(B, C ) and R2(A, C )
Some
Examples
                   Which functional dependencies should hold to assure that
                   this decomposition is lossless-join?
                   Assuming that this is a lossless-join decomposition, write
                   the relational algebra expression that can rebuild R from
                   R1 and R2.
  Database
   System
  Concepts



Relational
Design

Functional
Dependencies

Normal Forms

Other Design   End of Chapter 7
Issues

Some
Examples

								
To top