studentID name advisor dept deptName courseName grade 123 Jones Rose CIS CompSys
ID: 3613521 • Letter: S
Question
studentID
name
advisor
dept
deptName
courseName
grade
123
Jones
Rose
CIS
CompSys
CISY451
A
123
Jones
Rose
CIS
CompSys
CISY450
B
123
Jones
Rose
CIS
CompSys
CISY551
B
321
Smith
Chao
CIS
CompSys
CISY451
C
321
Smith
Chao
CIS
CompSys
CISY450
B
999
Abel
Peterson
BBIT
BussIT
BBIT450
B
studentID
name
advisor
dept
deptName
courseName
grade
123
Jones
Rose
CIS
CompSys
CISY451
A
123
Jones
Rose
CIS
CompSys
CISY450
B
123
Jones
Rose
CIS
CompSys
CISY551
B
321
Smith
Chao
CIS
CompSys
CISY451
C
321
Smith
Chao
CIS
CompSys
CISY450
B
999
Abel
Peterson
BBIT
BussIT
BBIT450
B
Explanation / Answer
Normalizing databases generally consists of removing redundancies,so that upon an insert, update, delete, or addition, you do nothave data anomalies that give you inconsistent results. Here, wehave the redundancy of the name, advisor, department, anddepartment name. These items do not have a functional dependency oncourseName and grade because they are not used to determine them.Further, it is very likely that the advisor has a functionaldependency with dept and department name--when do you have anadvisor that advises multiple majors? At any rate, in Second Normal Form, a 1NF table is in 2NF if andonly if none of its non-prime (non-key) attributes are functionally dependent on a part(proper subset) of a candidate key (wikipedia). In Third Normal Form, it's in 3NF if all of its non-prime (non-key)attributes do not have a transitive dependency (i.e. A ->B,B->C) So the functional dependancies are: Advisor -> Dept Dept -> DeptName StudentID -> Name StudentID, courseName -> grade So first, let's put the advisors into another table. Things in boldare a primary key. Things in italics are a foreign key. Advisor Dept Rose CIS Chao CIS Peterson BBIT Now we need to make one that tells you what the department namesare. This is to deal with the transitive dependency of Advisor -> Dept, Dept -> DeptName. Dept DeptName CIS CompSys BBIT BussIT We also need a table that puts the studentID and the name together,as StudentID -> name. StudentID Name 123 Jones 321 Smith 999 Abel And the main table now looks like StudentID courseName grade 123 CISY451 A 123 CISY450 B 123 CISY551 B 321 CISY451 C 321 CISY450 B 999 BBIT450 B We now have no transitive dependencies on our non-prime attributes(3NF) and all non-prime attributes are now dependent on the wholekey in a table. (2NF).
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.