Data Management Class Notes for 04/04/2014

Logical Database Design

• How good are our tables ?

  • Can they store all the information we want ?

  • Can they store all the constraints ?

  • Are there anomalies ?

  
  
  
  
  

  Example 1:

  
• Problems:

  • 1) Redundant information.

  • 2) Cannot capture the constraints appropriately: e.g. A grade of 1 would result in a salary of 90 but there's no person with a grade of one, so how do we store this information ? (dependency between salary can be assumed as given)

    • a- Make up a fake person ??

  
  
  

  • Possible solution to problem 2: Store information into different tables:

    

    Splitting tables into multiple ones: Decomposition

  • Done via a natural join: R = S T
    Create table R as
    Select distinct Name, SSN, DoB, Grade, Salaray
    From S,T
    Where s.grade = t.grade
    Lossless join decomposition (lossless in the sense that we didn’t loose any information)will decompose R into S and T

    • This Does not always work

  

  • Any two rows that have the same grade should have the same salary.
    
    Decomposition is one way of normalizing a database.
    
    Definition Normalization: Reorganizing a database to remove anomalies (“to put it into a normal form”)
    
    There exist several normal forms:
    1st normal form
    2nd normal form
    3rd normal form (most common one)
    4th normal form
    3rd BC

  

  1st normal form → fixed number of columns

  

  Functional Dependencies:
  P,Q
  If P functionally determines Q
  P → Q
  
  
  Business Rules (functional dependencies):

  • 1. Students can have only 1 birth year
    S→B
  • 2. 1 teacher must charge the same fee to every student
    T→F
  • 3. A teacher can teach only one course
    T→C
  • 4. A student can take any specific course from one teacher only
    S,C→T
  
  Given these dependencies, we should be able to find out what should be the primary key. S, T are a possible primary key of the table.
  
  
  BF → BF
  ST → STBFC → ST are a possible primary key
  SC → SCTFB → SC are a possible primary key
  

  Partial Dependency: From part of the PK to not in the PK (S→B)
  Transitive Dependency: From a column not in PK to a column not in the PK (T→F)
  Into Key Dependency: From not in PK to a column in PK (T→C)
  From the Kull Key Dependency: CS→T
  
  We could split like this

  

  Like this we got rid of one of our anomalies.
  
  Next split:

  

  And we got rid of the second anomality
  
  1st normal form means:
  No partial dependencies
  
  2nd normal form means:
  No transitive dependencies.
  
  Next split

  

  Now all anomalies are removed
  
  If two tables have the same primary key, we can combine them:

  

  1st normal form and all dependencies are from FULL KEY.
  Boyce-Codd Normal Form (BCNF)