Module/Week 5 Homework Assignment
Chapter 7 Homework
PART 1: (1 point)
Module/Week 5 Homework Assignment
Chapter 7 Homework
PART 1: (1 point)
Given the following table, identify the functional dependencies:
StdSSN
StdCity
StdClass
OfferNo
OffTerm
OffYear
EnrGrade
CourseNo
CrsDesc
S1
SEATTLE
JUN
01
SPRING
2009
3.5
C1
DB
S1
SEATTLE
JUN
02
FALL
2009
3.3
C2
VB
S2
DENVER
JUN
03
SPRING
2010
3.1
C3
OO
S2
DENVER
JUN
02
FALL
2009
3.4
C2
VB
StdSSN- StdCity, StdClass
OfferNo – OffTerm, OffYear, CourseNo, CrsDesc
CourseNo -CrsDesc
StdSSN, OfferNo- EnrGrad
PART 2: (1 point)
In the table provided below, fill in the following data such that it is completely un-normalized. That is, it is not even in 1NF.
222-33-44444 John Doe Web Design, Database, Intro to MIS, C# Programming
555-66-7777 Jane Doe Prin. Of Accounting, Auditing I, Statistical Analysis
SSN
StudentName
Courses
222-33-44444
Jane Doe
Web Design Database Intro to Mis C# Programming
555-666-7777
John Doe
Prin. OF Accounting Auditing I Statistical Analysis
PART 3: (1 point)
Using your un-normalized table above, fill in the table below with the data in 1NF.
SSN
StudentName
Courses
PART 4:
Given the following table and functional dependencies, construct tables below that will satisfy 2NF and identify the primary key of each new table. Note that the primary key of this table is StdSSN + OfferNo.
StdSSN
StdCity
StdClass
OfferNo
OffTerm
OffYear
EnrGrade
CourseNo
CrsDesc
S1
SEATTLE
JUN
01
SPRING
2009
3.5
C1
DB
S1
SEATTLE
JUN
02
FALL
2009
3.3
C2
VB
S2
DENVER
JUN
03
SPRING
2010
3.1
C3
OO
S2
DENVER
JUN
02
FALL
2009
3.4
C2
VB
Functional dependencies:
StdSSN à StdCity, StdClass
OfferNo à OffTerm, OffYear, CourseNo, CrsDesc
StdSSN, OfferNo à EnrGrade
(1/2 point)
Table 1
Primary Key: _______________________________
(1/2 point)
Table 2
Primary Key: _______________________________
(1/2 point)
Table 3
Primary Key: _______________________________
PART 5: (1/2 point)
Now, assume an additional functional dependency:
CourseNo à CrsDesc
What table above needs to be modified to accommodate this new functional dependency?
_______________________________
PART 6: (1/2 point)
Given this new functional dependency, create tables below that will put this table in 3NF.
Table 4
Primary Key: _______________________________
Table 5
Primary Key: _______________________________
Given the following table, identify the functional dependencies:
StdSSN
StdCity
StdClass
OfferNo
OffTerm
OffYear
EnrGrade
CourseNo
CrsDesc
S1
SEATTLE
JUN
01
SPRING
2009
3.5
C1
DB
S1
SEATTLE
JUN
02
FALL
2009
3.3
C2
VB
S2
DENVER
JUN
03
SPRING
2010
3.1
C3
OO
S2
DENVER
JUN
02
FALL
2009
3.4
C2
VB
StdSSN- StdCity, StdClass
OfferNo – OffTerm, OffYear, CourseNo, CrsDesc
CourseNo -CrsDesc
StdSSN, OfferNo- EnrGrad
PART 2: (1 point)
In the table provided below, fill in the following data such that it is completely un-normalized. That is, it is not even in 1NF.
222-33-44444 John Doe Web Design, Database, Intro to MIS, C# Programming
555-66-7777 Jane Doe Prin. Of Accounting, Auditing I, Statistical Analysis
SSN
StudentName
Courses
222-33-44444
Jane Doe
Web Design Database Intro to Mis C# Programming
555-666-7777
John Doe
Prin. OF Accounting Auditing I Statistical Analysis
PART 3: (1 point)
Using your un-normalized table above, fill in the table below with the data in 1NF.
SSN
StudentName
Courses
PART 4:
Given the following table and functional dependencies, construct tables below that will satisfy 2NF and identify the primary key of each new table. Note that the primary key of this table is StdSSN + OfferNo.
StdSSN
StdCity
StdClass
OfferNo
OffTerm
OffYear
EnrGrade
CourseNo
CrsDesc
S1
SEATTLE
JUN
01
SPRING
2009
3.5
C1
DB
S1
SEATTLE
JUN
02
FALL
2009
3.3
C2
VB
S2
DENVER
JUN
03
SPRING
2010
3.1
C3
OO
S2
DENVER
JUN
02
FALL
2009
3.4
C2
VB
Functional dependencies:
StdSSN à StdCity, StdClass
OfferNo à OffTerm, OffYear, CourseNo, CrsDesc
StdSSN, OfferNo à EnrGrade
(1/2 point)
Table 1
Primary Key: _______________________________
(1/2 point)
Table 2
Primary Key: _______________________________
(1/2 point)
Table 3
Primary Key: _______________________________
PART 5: (1/2 point)
Now, assume an additional functional dependency:
CourseNo à CrsDesc
What table above needs to be modified to accommodate this new functional dependency?
_______________________________
PART 6: (1/2 point)
Given this new functional dependency, create tables below that will put this table in 3NF.
Table 4
Primary Key: _______________________________
Table 5
Primary Key: _______________________________