Mon, Sep 13, 1999
------------------------

- Homework #2 due

- Converting E/R to "R"

- Entity Sets get their own relation
- Relationships get their own relation
	- include key attributes of both entity sets

- To form the key for a relation arising from an entity set
	- include key attributes of the entity set

- To form the key for a relation arising from a relationship: 
	- Many-Many: keys of both entity sets
	- Many-One: keys from the "many" set
	- One-One: keys from either one will do

- Weak Sets
	- do not need to form a relation for the double diamonds (why?)

- Inheritance
	- do not model subclasses
	- use a composite design with NULL values

- NULL means one of the following
	- inapplicable
	- not available
	- "I dunno what you're talking about!"

- No. of relations =  # Entity Sets 
	 	    + # Relationships
		    - # Subclasses
		    - # Double Diamond Relationships		

- Normalization: Process of making relations better

Wed, Sep 15, 1999
------------------------

- Review of "R"

- 2-person Cake Problem

- Normalization uses a theory of FDs

- FDs: Statements of the form 

	"If I know A_1, A_2,..., A_m, 
	I can determine B_1, B_2, ..., B_n"

- Written as A_1 A_2 ... -> B_1 B_2 ...

- Simplest examples of FDs: key constraints

- Other sources of FDs: notes written on the margin
	
- Combining 
	- on left: allowed
	- on right: allowed

- Splitting
	- on left: not allowed
	- on right: allowed

- Trivial FDs
	- when the B's are a subset of the A's

- Nontrivial FDs
	- when at least one of the B's is not among the A's

- Completely nontrivial FDs
	- when none of the B's are among the A's

- A set of attributes {A_1, A_2, ..., A_n} is a key for relation R iff
	- {A_1, A_2, ..., A_n} functionally determine every other
          attribute of R
	- no proper subset of {A_1, A_2, ..., A_n} can functionally
	  determine every other attribute of R (i.e., a key is minimal)

- If only first condition is satisfied
	- superkey

- Different authors use different notations
	- superkey is called key and key is called "candidate key"

- We will use the notation that superkey contains (potentially) extra attributes
  and that the key is minimal

- For Student(id,name,age), the key is {id}
  the superkeys are {id},{id,age},{id,name},{id,name,age}

- Closure of a set of attributes is all the information (attributes) that
  can be functionally derived from the set. Closure of a set A is
  denoted by A+ (where the + is in the superscript)

- B is contained in A+ if A->B holds

- Connection between closure and superkeys:
	- A set of attributes is a superkey for R iff the closure
          of that set is R

- Connection between closure and key:
	- A set of attributes is a key for R iff
		-  the closure of that set is R and
		-  no subset of that set exists whose
		   closure is R

- What is the use of closure?
	- If we would like to find if a certain FD A->B holds
	- compute the closure of A
	- see if B is in it! :-) 

- Not-true statements
	- If A->B, then B->A
	- If AB->C and A->C then B->C
	- If AB->C, then A->C or B->C

- Given the relation R(A,B,C,D,E) with AB->C, BC->AD, D->E, CF->B
	- question: what is the closure of {A,B}
	- answer: {A,B,C,D,E}, no F

- In general, to determine the FDs that follow from a given set of FDs
  requires an exponential-time algorithm (due to the number of subsets
  whose closure has to be determined)
	
Fri, Sep 17, 1999
------------------------

- 3 Person Cake Problem

- Review of FDs and Keys

- Introduction to BCNF
	- A relation R is in BCNF iff for every FD X->Y,
          X is a superkey
	
- Basic Idea:
	- Find an FD that is in violation of the BCNF condition
	- split (breakup) according to an FD
	- recurse, if necessary

- Can stop when we reach 2-columns 
	- Every 2-column relation is in BCNF
	- proof given in class

- Improper decompositions lead to "lossy joins"

- Started on 3NF: Bookings and Movies Example