1. Flatten the Data Into the Table Below

Name:

Campus / Course / Sect. / Type / Title / Day / InstrID / FName / LName
Main / CIS 530 / A / F / Graphical User Interfaces / S / 121212 / Keith / Reichardt
CIS 540 / A / F / Data Communication / R / 232323 / Stephen / Longo
CIS 607 / A / SE / Computer Graphics / W / 343434 / Raymond / Kirsch
CIS 623 / A / C / N-Tier Architectures / T / 454545 / Samuel / Wiley
CIS 672 / A / SE / Advanced LAN / M / 565656 / Thomas / Pasquale
MBA 810 / A / F / Self-Assessment for Leadership / M / 676767 / David / Alexander
Bucks / CIS 615 / BA / SE / Project Management / R / 787878 / Joseph / Catanio
CIS 624 / BA / SE / Data Warehousing / T / 898989 / Michael / Redmond
CIS 630 / BA / C / Component Programming / R / 909090 / Jane / Turk
CIS 656 / BA / GE / E-Commerce / W / 010101 / Margaret / McCoey
MBA 810 / BA / F / Self-Assessment for Leadership / M / 131313 / James / Smither
MBA 810 / BB / F / Self-Assessment for Leadership / W / 131313 / James / Smither
Gywnedd / MBA 810 / GA / F / Self-Assessment for Leadership / T / 242424 / David / Seltzer

1.  Flatten the data into the table below.

Campus / Course / Sect. / Type / Title / Day / InstrID / FName / LName
Main / CIS 530 / A / F / Graphical User Interfaces / S / 121212 / Keith / Reichardt
Main / CIS 540 / A / F / Data Communication / R / 232323 / Stephen / Longo
Main / CIS 607 / A / SE / Computer Graphics / W / 343434 / Raymond / Kirsch
Main / CIS 623 / A / C / N-Tier Architectures / T / 454545 / Samuel / Wiley
Main / CIS 672 / A / SE / Advanced LAN / M / 565656 / Thomas / Pasquale
Main / MBA 810 / A / F / Self-Assessment for Leadership / M / 676767 / David / Alexander
Bucks / CIS 615 / BA / SE / Project Management / R / 787878 / Joseph / Catanio
Bucks / CIS 624 / BA / SE / Data Warehousing / T / 898989 / Michael / Redmond
Bucks / CIS 630 / BA / C / Component Programming / R / 909090 / Jane / Turk
Bucks / CIS 656 / BA / GE / E-Commerce / W / 010101 / Margaret / McCoey
Bucks / MBA 810 / BA / F / Self-Assessment for Leadership / M / 131313 / James / Smither
Bucks / MBA 810 / BB / F / Self-Assessment for Leadership / W / 131313 / James / Smither
Gywnedd / MBA 810 / GA / F / Self-Assessment for Leadership / T / 242424 / David / Seltzer

2.  Identify a primary key for the flattened table.

Course, Sect

3.  Identify any functional dependencies.

A.  Course, Sect ® Campus, Type, Title, Day, InstrID, FName, LName (primary key determines everything else)

B.  Course ® Title, Type (the course determines its title and the type of requirement it satisfies)

C.  InstrID ® FName, LName (the instructor’s ID determines his or her first and last names)

D.  Sect ® Campus

4.  Identify which of the above are partial dependences on the primary key.

B and D above are partial dependences on the primary key.

5.  Decompose (normalize) the table to 2NF (second normal form).

Course / Sect. / Day / InstrID / FName / LName
CIS 530 / A / S / 121212 / Keith / Reichardt
CIS 540 / A / R / 232323 / Stephen / Longo
CIS 607 / A / W / 343434 / Raymond / Kirsch
CIS 623 / A / T / 454545 / Samuel / Wiley
CIS 672 / A / M / 565656 / Thomas / Pasquale
MBA 810 / A / M / 676767 / David / Alexander
CIS 615 / BA / R / 787878 / Joseph / Catanio
CIS 624 / BA / T / 898989 / Michael / Redmond
CIS 630 / BA / R / 909090 / Jane / Turk
CIS 656 / BA / W / 010101 / Margaret / McCoey
MBA 810 / BA / M / 131313 / James / Smither
MBA 810 / BB / W / 131313 / James / Smither
MBA 810 / GA / T / 242424 / David / Seltzer
Campus / Sect.
Main / A
Bucks / BA
Bucks / BB
Gywnedd / GA
Course / Type / Title
CIS 530 / F / Graphical User Interfaces
CIS 540 / F / Data Communication
CIS 607 / SE / Computer Graphics
CIS 623 / C / N-Tier Architectures
CIS 672 / SE / Advanced LAN
MBA 810 / F / Self-Assessment for Leadership
CIS 615 / SE / Project Management
CIS 624 / SE / Data Warehousing
CIS 630 / C / Component Programming
CIS 656 / GE / E-Commerce

The instructor’s first and last names remain in the table at the Second Normal Form. That dependence is transitive – the primary key determines InstrID, which in turn determines the FName and LName. Thus at Third Normal Form the first table above is further decomposed into the following.

InstrID / FName / LName
121212 / Keith / Reichardt
232323 / Stephen / Longo
343434 / Raymond / Kirsch
454545 / Samuel / Wiley
565656 / Thomas / Pasquale
676767 / David / Alexander
787878 / Joseph / Catanio
898989 / Michael / Redmond
909090 / Jane / Turk
010101 / Margaret / McCoey
131313 / James / Smither
242424 / David / Seltzer
Course / Sect. / Day / InstrID
CIS 530 / A / S / 121212
CIS 540 / A / R / 232323
CIS 607 / A / W / 343434
CIS 623 / A / T / 454545
CIS 672 / A / M / 565656
MBA 810 / A / M / 676767
CIS 615 / BA / R / 787878
CIS 624 / BA / T / 898989
CIS 630 / BA / R / 909090
CIS 656 / BA / W / 010101
MBA 810 / BA / M / 131313
MBA 810 / BB / W / 131313
MBA 810 / GA / T / 242424