Listed below is a report used to list students, their semester performance, and
ID: 3689948 • Letter: L
Question
Listed below is a report used to list students, their semester performance, and their academic advisors. Normalize the columns to develop entities to be used in a relational database in 1NF, 2NF, and 3NF. Entities must indicate the attributes and primary keys. Show the derived entities and attributes in 1NF, 2NF, and 3NF.
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Course Grade
1076
Stan Musial
16
3.7
49
Brett Farve
CIS199
Computer Science1
4
B
MKT212
Mktg Management
3
A
ENG101
Compostition
3
B
CHM112
Chemstry 1
4
A
BUS101
Intro to Business
2
A
8859
Babe Ruth
10
3
49
Brett Farve
ENG101
Composition
3
A
MKT212
Mktg Management
3
C
CIS151
Computer Science1
4
A
6597
Al Kaline
14
2.2
23
Joe Montana
CIS151
Computer Science1
4
B
CHM112
Chemistry 1
3
C
ENG101
Composition
3
C
BUS101
Intro to Business
2
C
Student Number
Student Name
Total Credits
GPA
Advisor Number
Advisor Name
Course Number
Course Description
Course Credits
Course Grade
1076
Stan Musial
16
3.7
49
Brett Farve
CIS199
Computer Science1
4
B
MKT212
Mktg Management
3
A
ENG101
Compostition
3
B
CHM112
Chemstry 1
4
A
BUS101
Intro to Business
2
A
8859
Babe Ruth
10
3
49
Brett Farve
ENG101
Composition
3
A
MKT212
Mktg Management
3
C
CIS151
Computer Science1
4
A
6597
Al Kaline
14
2.2
23
Joe Montana
CIS151
Computer Science1
4
B
CHM112
Chemistry 1
3
C
ENG101
Composition
3
C
BUS101
Intro to Business
2
C
Explanation / Answer
Its basically a Student information database.....
For a student the Total Credits,GPA, Advisor details,Course details are dependent.
First Normal Form (1NF):
==================
As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.
In this Scenario,
Here, for a student multiple courses are there, hence the course details must be seperated (as below) to get atomic values as said in 1NF.
TABLE - STUDENT
KEYS- STUDENT ID(I.E. STUDENT NUMBER) IS PRIMARY KEY
ATTRIBUTES - STUDENT ID,STUDENT NAME,TOTAL CREDITS,GPA,ADVISOR NUMBER, ADVISOR NAME,COURSE ID
TABLE- STUDENT_COURSE
KEYS- STUDENT ID(I.E. STUDENT NUMBER) ,COURSE ID(I.E. COURSE NUMBER) IS CANDIDATE KEY
ATTRIBUTES - STUDENT ID, COURSE ID, COURSE DESCRIPTION, COURSE CREDITS, COURSE GRADE
Second Normal Form (2NF):
==================
A table is said to be in 2NF if both the following conditions hold:-
Table is in 1NF (First normal form)
No non-prime attribute is dependent on the proper subset of any candidate key of table.
An attribute that is not part of any candidate key is known as non-prime attribute.
In this Scenario,
Here for a student in STUDENT_COURSE table, the course credits and course grade depends totally on the candidate key(i.e., both student id,course id columns) but the course description is depending only on course id attribute so it must be seperated to achieve the rules in 2NF.
TABLE - STUDENT
KEYS- STUDENT ID(I.E. STUDENT NUMBER) IS PRIMARY KEY
ATTRIBUTES - STUDENT ID,STUDENT NAME,TOTAL CREDITS,GPA,ADVISOR NUMBER, ADVISOR NAME,COURSE ID
TABLE- COURSE
KEYS- COURSE ID(I.E. COURSE NUMBER) IS PRIMARY KEY
ATTRIBUTES - COURSE ID, COURSE DESCRIPTION
TABLE- STUDENT_COURSE
KEYS - STUDENT ID,COURSE ID IS CANDIDATE KEY
ATTRIBUTES - STUDENT ID, COURSE ID, COURSE CREDITS, COURSE GRADE
Third Normal Form (3NF):
=================
A table design is said to be in 3NF if both the following conditions hold:-
Table must be in 2NF
Transitive functional dependency of non-prime attribute on any super key should be removed.
In this Scenario,
Here for a Student an Advisor is present and the advisor name attribute is dependent on advisor number where this advisor number is attached to a student,so a transitive functional dependency exists hence we must seperate advisor name from student table to achieve 3NF.
TABLE - STUDENT
KEYS - STUDENT ID(I.E. STUDENT NUMBER),STUDENT NAME IS PRIMARY KEY
ATTRIBUTES - STUDENT ID,STUDENT NAME,TOTAL CREDITS,GPA,ADVISOR ID,COURSE ID
TABLE - ADVISOR
KEYS - ADVISOR ID (I.E. ADVISOR NUMBER) IS PRIMARY KEY
ATTRIBUTES - ADVISOR ID, ADVISOR NAME
TABLE- COURSE
KEYS - COURSE ID(I.E. COURSE NUMBER) IS PRIMARY KEY
ATTRIBUTES - COURSE ID, COURSE DESCRIPTION
TABLE- STUDENT_COURSE
KEYS - STUDENT ID,COURSE ID IS CANDIDATE KEY
ATTRIBUTES - STUDENT ID, COURSE ID, COURSE CREDITS, COURSE GRADE
... Finally after achieving the 1NF,2NF,3NF the student database holds STUDENT, COURSE, STUDENT_COURSE, ADVISOR tables.........
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.