| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/uploads/20243/CST1600/41/16523677/Lab09/ |
Upload File : |
UNF:
studentgrades (StudentId*, StudentName, SP, Lab01, Lab02, Lab03, Lab04, Lab05, Lab06, Lab07, Lab08, Lab09, Total, Final, IP)
lettergrade (LetterGrade1, Percentage1, LetterGrade2, Percentage2, LetterGrade3, Percentage3, LetterGrade4, Percentage4, LetterGrade5, Percentage5, LetterGrade6, Percentage6, LetterGrade7, Percentage7, LetterGrade8, Percentage8, LetterGrade9, Percentage9)
possiblepoints (SP, Lab01, Lab02, Lab03, Lab04, Lab05, Lab06, Lab07, Lab08, Lab09, Total)
1NF:
1) The SP, Lab01-Lab09, Final and IP columns need to be separated from the StudentID and StudentName because if a student was deleted the grade information for the individual scores would be lost.
2) The StudentName field needs to be separated into StudentFirstName and StudentLastName to avoid having fields with multiple values in the same field.
3) The resulting studentgrades table should not have each assignment in a different column and it should have an assignment name column that could be tied back to the student
table.
3) The lettergrade table needs to have a unique identifier and it should be the letter grade because there could be multiple different percentage per letter grade. Also, there is no need to have numbers under the percentages, you could just have the letter grade and the percentage.
4) The assignment name are currently in both the possible points table and the studentgrades table. This table needs a unique ID for each assignment. Also, having the assignment names as columns would likely cause problems if assignments were added in the future including past data would have null values in those columns if future assignment were added.
The following entity tables would be:
student(StudentID*, StudentLastName, StudentFirstName)
studentgrades(StudentID*, AssignmentName@, StudentAssignPoints, StudentTotalPoints, Final, IP)
lettergrade(LetterGrade*, Percentage)
possiblepoints(AssignmentName*, AssignmentPoints, AssignmentsTotalPoints)
2NF:
1) The Final and IP fields in the studentgrades table have indirect dependencies on non-key attributes (StudentTotalPoints).
2) The StudentTotal, Final and IP fields are also calculated fields that are dependent on both the possiblepoints and lettergrade tables.
The following entity tables would be:
student(StudentID*, StudentLastName, StudentFirstName)
studentgrades(StudentID*, AssignmentName@, StudentAssignPoints)
studenttotals(StudentID*, StudentTotalPoints, Final, IP)
lettergrade(LetterGrade*, Percentage)
possiblepoints(AssignmentName*, AssignmentPoints, AssignmentsTotalPoints)
3NF:
1) The AssignmentsTotalPoints field in the possible points is based on non-key attributes (AssignmentPoints) and should be separated as well.
The following entity tables would be:
student(StudentID*, StudentLastName, StudentFirstName)
studentgrades(StudentID*, AssignmentName@, StudentAssignPoints)
studenttotals(StudentID*, StudentTotalPoints, Final, IP)
lettergrade(LetterGrade*, Percentage)
possiblepoints(AssignmentName*, AssignmentPoints)
possibleTotals(AssignmentsTotalPoints)
Three data rows:
student (StudentID*, StudentLastName, StudentFirstName)
84228748 Abbott Lael
44107438 Acosta Bruce
21479092 Fox Coby
studentgrades (StudentID*, AssignmentName@, StudentAssignPoints)
84228748 SP 4
84228748 Lab01 10
84228748 Lab02 5
84228748 Lab03 9
84228748 Lab04 ns
84228748 Lab05 3
84228748 Lab06 10
84228748 Lab07 ns
84228748 Lab08 ns
84228748 Lab09 ns
44107438 SP 5
44107438 Lab01 10
44107438 Lab02 5
44107438 Lab03 10
44107438 Lab04 10
44107438 Lab05 15
44107438 Lab06 10
44107438 Lab07 15
44107438 Lab08 15
44107438 Lab09 ns
21479092 SP 5
21479092 Lab01 10
21479092 Lab02 5
21479092 Lab03 10
21479092 Lab04 10
21479092 Lab05 15
21479092 Lab06 10
21479092 Lab07 ng
21479092 Lab08 ns
21479092 Lab09 ns
studenttotals (StudentID*, StudentTotalPoints, Final, IP)
84228748 41 F F
44107438 95 B- A
21479092 65 F A
lettergrade (LetterGrade*, Percentage)
A 93
A- 90
B+ 87
B 83
B- 80
C+ 77
C 73
C- 70
F 0
possiblepoints (AssignmentName*, AssignmentPoints)
SP 5
Lab01 10
Lab02 5
Lab03 10
Lab04 10
Lab05 15
Lab06 10
Lab07 15
Lab08 15
Lab09 15
possibleTotals (AssignmentsTotalPoints)
110