| 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/20255/CST1600/41/16973452/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:
- studentgrades table:
- Student name is not atomic and needs to be split into first and last name.
- The instructions indicated that "You need not store ng - `not graded`or
ns - `not submitted` grades as they are calculated". Having all these
values in a row, violates not storing ns or ng assignments when only the
initial assignments are complete.
- lettergrade and possiblepoints table:
- While these tables could have a unique primary key created for their
current form, it is more useful to have granular primary keys so the data
is easier to merge and calculate values from.
Updated Tables:
student_information(StudentId*, FirstName, LastName)
student_scores(StudentId*@, AssignmentId*@, ScoredPoints)
student_grades(StudentId*@, Total, Final@letter_grade.LetterGradeID, IP@letter_grade.LetterGradeID)
letter_grade(LetterGradeID*, MinPercentage, MaxPercentage)
assignment_points(AssignmentId*, Points)
Note: The redundant possiblepoints.Total column was removed. See below
comment.
Description of Changes:
- studentgrades table:
- This table was split into 3 other tables. The reason for the split and
details of it are listed in the specific tables bullet point.
- student_information table:
- As a design decision, I chose to have slow changing student data
stored in a separate table so it is less likely to be mistakenly
changed when other faster changing fields are updated.
- StudentId is the primary key for this table.
- The StudentName column also moved to this table, and also was broken
into the FirstName and LastName columns so it would be atomic.
- student_score table:
- Reason for creation: created so uncompleted or un-scored student work
would not have to be explicitly saved in a table.
- Created primary key composed on StudentId and AssignmentId
- Both StudentId and AssignmentId are also foreign keys.
- The scores for each assignment are stored in the new column
"ScoredPoints".
- student_grades table:
- This contains faster changing student grade summary information.
Technically this table and the student_information table could be
combined. However, I wanted to separate faster changing data from
slower changing data.
- Its primary key is StudentId, which is also a foreign key.
- The calculated column Total is stored in this table (taken from
the original studentgrades table).
- The letter grade columns Final and IP are also stored in this table.
They are both foreign keys referring back to the
letter_grade.LetterGradeID column.
- lettergrade table:
- This table was replaced by the letter_grade table (note the underscore).
- This was done because having the primary key of the table identify a
specific letter grade is more useful than having one row identify all
letter grades.
- LetterGradeID is the primary key and is the letter that corresponds to
the associated percentage range.
- MinPercentage is the lower bound of the letter grade's percentage range.
- MaxPercentage is the upper bound of the letter grade's percentage range.
- Both MinPercentage and MaxPercentage were used so one row would be able
to calculate if a student's percentage is within its bounds.
- possiblepoints table:
- This table was transformed into 1 other table and had a redundant column
removed. The reason for this and its details are listed in the following
bullet points.
- assignment_points table:
- This table was created because it is more useful having the primary
key associated with assignment points be able to identify specific
assignment points rather than all assignments and their points.
- Having this form makes it easier to calculate the total assignment
points.
- AssignmentId is the primary key and also is the name of the
assignments.
- Points is the total points associated with an assignment.
- Removal of the Total column.
- The information contained in this column is redundant and can now be
replaced with the following query and joined to where it is needed.
SELECT SUM(Points) AS Total
FROM assignment_points
2NF:
OK
3NF:
OK
Three data rows:
student_information(StudentId*, FirstName, LastName)
84228748, Lael, Abbott
44107438, Bruce, Acosta
21479092, Coby, Fox
student_scores(StudentId*@, AssignmentId*@, ScoredPoints)
84228748, SP, 4
84228748, Lab01, 10
84228748, Lab02, 5
84228748, Lab03, 9
84228748, Lab05, 3
84228748, Lab06, 10
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
21479092, SP, 5
21479092, Lab01, 10
21479092, Lab02, 5
21479092, Lab03, 10
21479092, Lab04, 10
21479092, Lab05, 15
21479092, Lab06, 10
student_grades(StudentId*@, Total, Final@letter_grade.LetterGradeID, IP@letter_grade.LetterGradeID)
84228748, 41, F, F
44107438, 95, B-, A
21479092, 65, F, A
letter_grade(LetterGradeID*, MinPercentage, MaxPercentage)
A, 93, 100
A-, 90, 93
B+, 87, 90
B, 83, 87
B-, 80, 83
C+, 77, 80
C, 73, 77
C-, 70, 73
F, 0, 70
assignment_points(AssignmentId*, Points)
SP, 5
Lab01, 10
Lab02, 5
Lab03, 10
Lab04, 10
Lab05, 15
Lab06, 10
Lab07, 15
Lab08, 15
Lab09, 15