J-1

1
Principles of Relational Design

• Chapter 12

2
Things can go wrong!

• All the relational designs we've seen so far have
  been pretty good
• fairly small, intuitive examples
• E/R model not far away
• A carelessly designed schema can lead to big
  problems
• How do we evaluate a schema?
• How do we design a good one?

3
Plan of Attack

• Study some informal principles (12.1)
• Functional dependencies an important type of
  semantic constraint
• define and illustrate (12.2)
• use to define "normal forms" 2NF, 3NF, and BCNF
  (12.3-12.5)
• Decomposition algorithms (13.1)
• Multivalued dependencies and 4NF (13.2)
• Join dependencies and 5NF (13.3)

4
Informal Guidelines by Elmasri and Navathe

• Design a relation so that it is easy to explain
  its meaning.
• Design so that no insertion, deletion, or
  modification anomalies can occur.
• Avoid attributes whose values can be null.
• Design so that reasonable joins do not produce
  spurious tuples.

5
The "Universal Relation" Approach

• Assume we have identified all the individual
  pieces of data (attributes) of the problem.
• The database design problem group the attributes
  into relations.
• The informal guidelines are one way of evaluating
  the result.
• The theory of functional dependencies and normal
  forms gives a more precise way.

6
Functional dependency defined

• Let X and Y be attributes
• X? Y means that Y is a function of X. I.e., if
  you know the value of X, there's only one
  possible value of Y. We say that "Y is
  functionally dependent on X" or "X determines Y."
• Note X? Y does not imply Y? X !

7
Examples

• If you know the SSN, there's only one possible
  name (is the reverse true?)
• SSN?NAME
• If you know the department number, you know the
  department name
• DNO ? DNAME

8
Dependencies between sets of attributes

• Given today's date and the date of birth, the age
  and the years until 65 are determined.
• TD, DB ? AGE, YTO65
• If you know the file pathname, you can determine
  its size, owner, and date of last modification.
• FP ?SZ, O, MDT
• Normally, when writing X ? Y, we assume that X
  and Y are sets of attributes.

9
Facts about FD

• FDs are purely semantic in nature
• FDs are facts about the abstract relation, not
  just about a particular relation instance
• They must hold for all possible legal instances
  of a relation
• All attributes of a relation are functionally
  dependent upon its key!
• In fact, we can formally define keys in terms of
  FDs.

10
Inference Rules for FDs

• Given a set of FDs, it may be possible to deduce
  others by purely syntactic means.
• Example Given that A?B,C, it follows that
  A?B also (and that A ?C)

11
Armstrong's inference rules

• Armstrong's rules (provable directly from the
  definitions)
• IR1. Reflexive rule if X?Y, then X?Y
• IR2. Augmentation rule if X?Y then XZ?YZ
• IR3. Transitive rule if X?Y and Y?Z then X?Z
• If you understand the idea of FD, these should
  make sense, even if you can't prove them formally.

12
Some other rules

• Provable from IR1-3
• IR4. Decomposition if X?YZ then X?Y
• IR5. Union (additive) rule if X?Y and X?Z then
  X?YZ
• IR6. Pseudotransitivity if X?Y and WY?Z then
  WX?Z
• These should also make sense. Try them out with
  actual attributes!

13
Closures

• X is the "closure" of X the set of all
  attributes functionally determined by X (given a
  set of FDs)

14
Nomalization

• Take a relation schema
• Test it against a normalization criterion
• If it passes, fine!
• Maybe test again with a higher criterion
• If it fails, decompose into smaller relations
• Each of them will pass the test
• Each can then be tested with a higher criterion

15
1st Normal Form

• We've already seen!
• It's the concept that all attribute values have
  to be atomic
• This is now taken for granted in the relational
  model
• but is being questioned again in the object model
• E/R attributes don't have to be atomic

16
Key Terminology

• Superkey attribute set with unique value
• Key minimal superkey (no attribute can be
  removed)
• May be more than one such "candidate key."
• One is designated the "primary key"
• "Prime" attribute occurs in some key
• "Non-prime" occurs in no key

17
Full and partial dependency

• X?Y is a "full functional dependency" if no
  attribute can be removed from X and there still
  be the dependency.
• X?Y is a "partial dependency" if some attribute
  can be removed from X and the dependency is still
  there.

18
2NF

• A relation schema is in Second Normal Form (2NF)
  if every non-prime attribute in it is fully
  functionally dependent on the primary key of the
  relation.
• more generally FFD on any key...

19
3NF

• X?Y is a transitive dependency if there is a set
  of attributes Z that is not a subset of any key
  of R, and both X?Z and Z?Y hold.
• A relation schema R is in Third Normal Form (3NF)
  if it is in 2NF and no nonprime attribute of R is
  transitively dependent on the primary key.

20
3NF (General Definition)

• A relation is in Third Normal Form (3NF) if,
  whenever X?A holds, either X is a superkey, or A
  is a prime attribute.
• Informally everything depends on the key or is
  in the key.
• Despite the thorny technical definitions that
  lead up to it, 3NF is intuitive and not hard to
  achieve. Aim for it in all designs unless you
  have strong reasons otherwise.

21
Boyce-Codd Normal Form (BCNF)

• R is in BFNC if whenever X?A holds, then X is a
  superkey.
• Slightly stronger than 3NF.
• Example R(A,B,C) with A,B?C, C?A
• 3NF but not BCNF
• Aim for BCNF and settle for 3NF