| 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/20245/CST1600/41/14842474/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:
● First I would move StudentID and StudentName into their own table called student. I would also split StudentName into FirstName and LastName. Once all the the data is transferred, StudentName can be deleted from the studentgrades table.
studentgrades (StudentID*, SP, Lab01, Lab02, Lab03, Lab04, Lab05, Lab06, Lab07, Lab08, Lab09, Total, Final, IP)
student (StudentId*, FirstName, LastName)
lettergrades (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)
● Next I would create a combine all the letter grades and percentages into 2 fields LetterGrade and Percentage. This eliminates repeating fields. I renamed the table to gradingscale to eliminate confusion between the field and table.
studentgrades (StudentID*@, SP, Lab01, Lab02, Lab03, Lab04, Lab05, Lab06, Lab07, Lab08, Lab09, Total, Final, IP)
student (StudentId*, FirstName, LastName)
gradingscale (Percentage*, LetterGrade)
possiblepoints (SP, Lab01, Lab02, Lab03, Lab04, Lab05, Lab06, Lab07, Lab08, Lab09, Total)
● Next I would create a combine all the assignments by making two fields, assignmentname and EarnedPoints. This eliminates repeating fields. I would also do the same for the possiblepoints table but the points field will be called PointsPossible. I also renamed the table to assignments.
studentgrades (StudentID*@, AssignmentName@, EarnedPoints, Total, Final, IP)
student (StudentId*, FirstName, LastName)
gradingscale (Percentage*, LetterGrade)
assignments (AssignmentName*, PointsPossible, Total)
● I would move total from assignments to its own table called possibletotal and rename Total to TotalPoints. This clarifies what is being stored and stops the data from being repeated over and over in the assignments table. I am also going to do the same for the student grades table. I will move it to a table called studenttotal and the Total field will be renamed to earnedtotal with the primary key being the studentID.
studentgrades (StudentID*@, AssignmentName@, EarnedPoints, Final, IP)
student (StudentId*, FirstName, LastName)
gradingscale (Percentage*, LetterGrade)
assignments (AssignmentName*, PointsPossible)
possibletotal (TotalPoints*)
studenttotal (StudentID*@, EarnedTotal)
● I am also going to make a new table called studentassignments which will hold StudentID*@, AssignmentName@, EarnedPoints. After the table is made and the data is transferred, AssignmentName and EarnedPoints can be deleted from the studentgrades table.
studentgrades (StudentID*@, Final, IP)
studentassignments (StudentID*@, AssignmentName*@, EarnedPoints)
student (StudentId*, FirstName, LastName)
gradingscale (Percentage*, LetterGrade)
assignments (AssignmentName*, PointsPossible)
possibletotal (TotalPoints*)
studenttotal (StudentID*@, EarnedTotal)
2NF:OK
3NF:OK
Three data rows:
student (StudentID*, FirstName, LastName)
84228748, Lael, Abbott
44107438, Bruce, Acosta
21479092, Coby, Fox
gradingscale (Percentage*, LetterGrade)
93, A
90, A-
87, B+
83, B
80, B-
77, C+
73, C
70, C-
0, F
assignments (AssignmentName*, PointsPossible)
SP, 5
Lab01, 10
Lab02, 5
Lab03, 10
Lab04, 10
Lab05, 15
Lab06, 10
Lab07, 15
Lab08, 15
Lab09, 15
possibletotal (TotalPoints*)
110
studentassignments (StudentID*@, AssignmentName*@, EarnedPoints)
84228748, SP, 4
84228748, Lab01, 10
84228748, Lab02, 5
studenttotal (StudentID*@, EarnedTotal)
84228748, 41
44107438, 95
21479092, 65
studentgrades (StudentID*@, Final, IP)
84228748, F F
44107438, B- A
21479092, F A