Consider a school advising system in which a faculty advisor can advise many stu
ID: 3866452 • Letter: C
Question
Consider a school advising system in which a faculty advisor can advise many students, each of whom can register for one or many courses. The following is an example of an un-normalized STUDENT table for three students.
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Grade
1034
Linda
47
3.60
59
Smith
CSC101
Computer Science I
4
B
MKT211
Marketing Management
3
A
ENG101
English Composition
3
B
CHM111
General Chemistry I
4
A
BUS101
Introduction to Business
2
A
3397
Sam
29
3.00
59
Smith
ENG101
English Composition
3
A
MKT211
Marketing Management
3
B
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CSC101
Computer Science I
4
B
CHM111
General Chemistry I
4
A
ENG101
English Composition
3
B
BUS101
Introduction to Business
2
B
Perform the normalization process to convert the above un-normalized table to:
1. First normal form (1NF).
2. Second normal form (2NF).
3. Third normal form (3NF).
Please show all your work. Please show & explain (via a well-written essay response with a minimum of 500 words) each step along the way and identify primary keys, if any, in each table in each step. Please explain your work as you make the progression from 1NF to 3NF. I would like for you to explain everything you’re doing as you move from First Normal Form to Third Normal Form.
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Grade
1034
Linda
47
3.60
59
Smith
CSC101
Computer Science I
4
B
MKT211
Marketing Management
3
A
ENG101
English Composition
3
B
CHM111
General Chemistry I
4
A
BUS101
Introduction to Business
2
A
3397
Sam
29
3.00
59
Smith
ENG101
English Composition
3
A
MKT211
Marketing Management
3
B
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CSC101
Computer Science I
4
B
CHM111
General Chemistry I
4
A
ENG101
English Composition
3
B
BUS101
Introduction to Business
2
B
Explanation / Answer
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Grade
1034
Linda
47
3.60
59
Smith
CSC101
Computer Science I
4
B
1034
Linda
47
3.60
59
Smith
MKT211
Marketing Management
3
A
1034
Linda
47
3.60
59
Smith
ENG101
English Composition
3
B
1034
Linda
47
3.60
59
Smith
CHM111
General Chemistry I
4
A
1034
Linda
47
3.60
59
Smith
BUS101
Introduction to Business
2
A
3397
Sam
29
3.00
59
Smith
ENG101
English Composition
3
A
3397
Sam
29
3.00
59
Smith
MKT211
Marketing Management
3
B
3397
Sam
29
3.00
59
Smith
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CHM111
General Chemistry I
4
A
4070
Kelly
14
3.20
23
Jones
ENG101
English Composition
3
B
4070
Kelly
14
3.20
23
Jones
BUS101
Introduction to Business
2
B
Convert the table from un normalized form to 1NF .
The above table contains the following structure
Structure of STUDENT table
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number, Advisor Name, Course Number, Course Description ,Course Credits ,Grade)
The fields StudentNam, Total Credits, GPA, Advisor Name, and Advisor Number related to a student number who will take advice from the Advisor
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number, Advisor Name)
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
1034
Linda
47
3.6
59
Smith
1034
Linda
47
3.6
59
Smith
1034
Linda
47
3.6
59
Smith
1034
Linda
47
3.6
59
Smith
1034
Linda
47
3.6
59
Smith
3397
Sam
29
3
59
Smith
3397
Sam
29
3
59
Smith
3397
Sam
29
3
59
Smith
4070
Kelly
14
3.2
23
Jones
4070
Kelly
14
3.2
23
Jones
4070
Kelly
14
3.2
23
Jones
4070
Kelly
14
3.2
23
Jones
The course Description depends on course number. And number of credits related to course number
COURSE (Course Number, Course Description, Course Credits)
Delete the repeating groups from the COURSE table for course number, course description and course credits
Course Number
Course Description
Course Credits
BUS101
Introduction to Business
2
MKT211
Marketing Management
3
CHM111
General Chemistry I
4
The above table contains no duplicate values and all on key attributes depends on primary key attribute, Course Number (2NF)
No transitive(a->b, b->c then a->c) function dependency in between three attributes(3NF)
Grade depends on student number and grade related to student number
Grade (Student Number, CourseNumber, Grade)
COURSE (Course Number, Course Description, Course Credits)
Student Number
Course Number
Grade
1034
CSC101
B
1034
MKT211
A
1034
ENG101
B
1034
CHM111
A
1034
BUS101
A
3397
ENG101
A
3397
MKT211
B
3397
CSC101
B
4070
CSC101
B
4070
CHM111
A
4070
ENG101
B
4070
BUS101
B
The above table contains no duplicate values and all on key attributes depends on primary key attribute, (Student Number + Course Number) (2NF)
No transitive (a->b, b->c then a->c) function dependency in between three attributes(3NF)
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number, Advisor Name)
The STUDENT table contains duplicate values for Advisor name and Advisor Number
Divide the table STUDENT as follows
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number)
Student Number
Student Name
Total Credits
GPA
Advisor Number
1034
Linda
47
3.6
59
3397
Sam
29
3
59
4070
Kelly
14
3.2
23
ADVISOR (Advisor Number, Advisor Name)
Advisor Number
Advisor Name
59
Smith
23
Jones
The total number of tables from normalized form to 3NF as follows
Un- normalized structure of STUDENT table
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number, Advisor Name, Course Number, Course Description ,Course Credits ,Grade)
TO 3NF tables
STUDENT (Student Number, Student Name, Total Credits, GPA, Advisor Number)
ADVISOR (Advisor Number, Advisor Name)
Grade (Student Number, CourseNumber, Grade)
COURSE (Course Number, Course Description, Course Credits)
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Grade
1034
Linda
47
3.60
59
Smith
CSC101
Computer Science I
4
B
1034
Linda
47
3.60
59
Smith
MKT211
Marketing Management
3
A
1034
Linda
47
3.60
59
Smith
ENG101
English Composition
3
B
1034
Linda
47
3.60
59
Smith
CHM111
General Chemistry I
4
A
1034
Linda
47
3.60
59
Smith
BUS101
Introduction to Business
2
A
3397
Sam
29
3.00
59
Smith
ENG101
English Composition
3
A
3397
Sam
29
3.00
59
Smith
MKT211
Marketing Management
3
B
3397
Sam
29
3.00
59
Smith
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CSC101
Computer Science I
4
B
4070
Kelly
14
3.20
23
Jones
CHM111
General Chemistry I
4
A
4070
Kelly
14
3.20
23
Jones
ENG101
English Composition
3
B
4070
Kelly
14
3.20
23
Jones
BUS101
Introduction to Business
2
B