Here is a summary of our in-class example of normalizing a database
for a portion of the university database.

Attributes:

S Semester  (e.g. Fall05)
C Course    (e.g. cs371)
B Book      (e.g. kifer)
R Room      (e.g. MDH1003)
T Time      (e.g. mwf3:00)
P Professor (e.g. mhg)
D Student   (e.g. alice)

Our integrity constraints were:

  -- The same course may have multiple offerings in a given semester.

  -- If there are two offerings of the same course in the same
     semester, they must use the same text.
  
  -- If there are two offerings of the same course in the same
     semester, they cannot be at the same time.

  -- A student cannot take more than one offering of the same course
     in the same semester (but may take the same course in multiple
     semesters)

  -- A professor teaches at most one course per semeseter

  -- There cannot be any room conflicts (e.g., two courses meeting in
     same room at same time in same semester)

Based on the above, we came up with the following original FDs:

  SDC->T            (no two offerings of identical course at same time)
  CS->B             (same book for all offerings)
  PST->C            (professor cannot teach two courses at identical time)
  PS->CTR           (professor cannot even teach two courses in same semester)
  SCT->RPB          (an offering of a course must have consistent room/professor/book)
  RTS->C            (No room conflicts)

Based on this, we compute a minimal cover as follows:

After step (1):

  SDC->T  
  CS->B   
  PST->C  
  PS->C
  PS->T 
  PS->R 
  SCT->R
  SCT->P
  SCT->B
  RTS->C  

After step (2):

  SDC->T  
  CS->B   
  PS[T]->C      [T is redundant]
  PS->C
  PS->T 
  PS->R 
  SCT->R
  SCT->P
  SC[T]->B      [T is redundant]
  RTS->C  

After step (3):

min cover              redundant
----------            -----------
  SDC->T  
  CS->B   
                        PS->C    (as PS->SRT->C)
                        PS->C
  PS->T 
  PS->R 
                        SCT->R   (as SCT->PS->R)
  SCT->P
                        SC->B    (already have CS->B)
  RTS->C  

-------------------------------------------------------

For 3NF Synthesis, we combing lefthand sides, to get PS->TR.  We have
five resulting tables, which we can interpret semantically as follows.

(SDCT;  SDC->T)      "Student Transcript View"
(CSB;   CS->B)       "Bookstore View"
(SCTP;  SCT->P)      "Teaching Assignments View"
(RTSC;  RTS->C)      "Room Scheduler"
(PSTR;  PS->TR)      "FacultyTimesheet"

Notice that SDCT is a superkey of the entire original relation, so
there was no need to add any other table in the synthesis process.

All of the above tables are in 3NF form relative to the original set
of FDs.   The closure of that original set would imply the following
true FDs for each:

SDCT: the only non-trivial FD is SDC->T  which is a key constraint

CSB:  the only non-trivial FD is CS->B  which is a key constraint

SCTP: FDs include SCT->P as well as PS->TC, both are key constraints.

RTSC: FDs include RTS->C as well as SCT->R.  Both are key constraints

PSTR: FDs include PS->TR and RTS->P, both of which are key constraints


So in fact, our schema is actually valid BCNF, yet guaranteed to be
both lossless and dependency-preserving via our 3NF synthesis.


=======================================================================

Though we did not originally consider the following when doing the
example in lecture, there should be an additional integrity constraint
to avoid a time conflict in an individual student's schedule.

Such a constraint would be described as
   SDT->C

Notice that is not entailed in the original FDs.   Furthermore, it is
not redundant and would be included as part of our computed minimal
cover.

Furthermore, by the 3NF synthesis algorithm, we would actually create
another table (SDTC; SDT->C) in our schema.   Oddly, by the described
snythesis algorithm, we would not combine this with the earlier
'student transcript view' (SDCT; SDC->T)  even though the attribute
set is the same.   Presumably it would be fine to actually combine
those two tables, including both FDs, as:
  (SDCT;  SDC->T,SDT->C)

Again, as both are key constraints, this is BCNF.