| 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 : /nginx/html/uploads/20243/CST1600/41/15510774/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: Violations: - StudentName should be split into into two fileds - StudentFirstName and StudentLastName - Each table needs to have a primary key - LetterGradeN repeats (should be two fields - one containing the percentage and the other containing the letter grade corresponding to the percentage) -LabN repeats (should be a separate lab/assignment table) Create Fields/Tables: studentGrade (StudentId*, StudentFirstName, StudentLastName, assignmentId*@, recievedPoints, recievedTotal, Final, IP) letterGrade (LetterGrade*, Percentage) assignment (assignmentId*, assignmentTitle, assignmentPoints) NOTE: I removed the "Total" in the possiblepoints table because it was calculated, and I thought it wouldn't be needed in the database since it was the same for each student. Why: - StudentFirstName and StudentLastName are now following 1NF - I removed/revised the possiblepoints table and renamed it assignment to hold all the labs - I combined all labs and other assignments in the assignment table - I added the assignment table so that the similar labs would not repeat in the studentgrades table and possiblepoints table - I made an assignmentId field and made it the primary key of the assignment table so that the title could change without causing errors - I made LetterGrade the primary key of the "lettergrade" table because it is unique (there will never be two A's with different percentages) Rename: - I renamed studentgrades to studentGrade - I renamed lettergrades to letterGrade - I renamed studentGrade.total as recievedPoints for more clarification 2NF: Violations: - studentFirstName and studentLastName isn't dependent on the assignmentId - recievedPoints, Final, and IP grades are directly related to each student, but not to each individual assignment Create Fields/Tables student (studentId*, studentFirstName, studentLastName) studentGrade (studentId*@, assignmentId*@, receivedPoints) letterGrade (letterGrade*, percentage) assignment (assignmentId*, assignmentTitle, assignmentPoints) studentFinalGrade (studentId*@, recievedTotal, Final, IP) Why: - I created a separate "student" table to hold student information not directly related to an individual assignment - I created a "studentFinalGrade" table to hold calculated information including the recievedTotal (sum of all recieved points), final (letter grade corresponding to the percentage of recievedTotal / the sum of the assignmentPoints field), and IP (letter grade corresponding to the percentage of recievedTotal / sum of the assignmentPoints of only the graded assignments) Rename: N/A 3NF: OK Three data rows: studentId* studentFirstName studentLastName 84228748 Lael Abbott 44107438 Bruce Acosta 21479092 Coby Fox studentId*@ assignmentId*@ receivedPoints 84228748 1 4 84228748 2 10 84228748 3 5 letterGrade* percentage A 93 A- 90 B+ 87 B 83 B- 80 C+ 77 C 73 C- 70 F 0 assignmentId* assignmentTitle assignmentPoints 1 Student Profile 5 2 Lab01 10 3 Lab02 5 4 Lab03 10 5 Lab04 10 6 Lab05 15 7 Lab06 10 8 Lab07 15 9 Lab08 15 10 Lab09 15 studentId*@ recievedTotal Final IP 84228748 41 F F 44107438 95 B- A 21479092 65 F A