CSC 1035

1

• CSC 1035
• Supplement for Chapter 2
• Functional Dependencies

2
Definition and Notation

• Suppose X and Y are sets of attributes (column
  names)
• Check whether this statement is true or not
• For every set of values in the X columns, there
  can be at most one set of values in the Y
  columns
• If thats true, then say X determines Y
• And write X -gt Y

3
An example of some FDs

• Suppose the attribute names are
• stuId, firstName, lastName, major, credits,
  classNumber, grade, schedule, room, facId, name,
  rank, department
• And suppose the semantics are the same as in
  University.mdb
• On the next pages, lets analyze those attributes
  for FDs.

4
Examples from University.mdb

• This statement is true
• For every set of values of stuId, classNumber,
  there can be at most one grade.
• So is this one
• For every value of stuId, there can be at most
  one value of firstName and lastName.
• This one isnt true
• For every value of classNumber, there can be at
  most one value of stuId.

5
Examples, continued

• Phrasing the examples from the previous slide in
  technical database language
• stuId, classNumber -gt grade
• stuID -gt firstName, lastName
• classNumber does NOT determine stuId

6
Heres a complete list of FDs

• stuId -gt firstName, lastName, major, credits
• stuId, classNumber -gt grade
• classNumber -gt room, schedule, facId
• facId -gt name, rank, department

7
Setting up the tables in the database

• Each of the FDs in the last slide should lead to
  its own table.
• Within each table, the left-hand part of the FD
  becomes the primary key.
• So rather than a single table with all 13
  attributes, we get four tables, each with fewer
  attributes.
• The big table would have had redundancies.

8
Normalization

• The process of decomposing a big table into
  smaller ones, in order to avoid redundancies and
  anomalies, is called normalization.
• An indication of a table thats not normalized
  would be if you can spot a FD X-gtY where X isnt
  a key for that table.