A relational database cannot contain repeating groups. We normalize a database to First Normal Form to get rid of a possible repeating group in the table. Also, the tables in the database must not have update anomalies. The step-by-step exercises in this assignment will allow you the opportunity to normalize a data table and design tables to avoid repeating groups and update anomalies.
Based on the two readings and your knowledge of the college environment, consider the Student table below. Then, on a Word document, complete the following exercises.
Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, (CourseNum, CourseDescription, Term, Grade))
Based on the table notation shown above, this table contains four repeating groups. Identify the four columns with repeating groups.
To get rid of repeating groups, let’s normalize the Student table to first normal form. We now have two normalized tables: Student and StudentCourse.
Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName)
StudentCourse (StudentNum, CourseNum, CourseDescription, Term, Grade)
The new primary key of the StudentCourse table is a combination of StudentNum and CourseNum column.
Based on the Student table above, multiple students can share the same advisor. Therefore, update anomalies can occur when there is a change in AdvisorName. We can solve the problem by creating a new table called Advisor table. List your Advisor table with its columns and primary key using shorthand notation.
Now, we have the following three tables.
Student (StudentNum, StudentName, NumCredits, AdvisorNum)
Advisor
StudentCourse (StudentNum, CourseNum, CourseDescription, Term, Grade)
In the StudentCourse table, multiple students can enroll the same course. Therefore, update anomalies can occur when there is a change in CourseDescription. Solve the problem by creating a new table called Course table. List your Course table with its columns and primary key using shorthand notation.
We have normalized the original Student table to avoid repeating groups and created new tables to solve the update anomalies. We now have a good design of our student database which consists of four tables below. All tables are normalized and do not have update anomalies.
Student (StudentNum, StudentName, NumCredits, AdvisorNum)
Advisor
StudentCourse (StudentNum, CourseNum, Term, Grade)
Course
List all four tables with columns and primary keys using shorthand notation. Use your answers from exercises 2 and 3 to complete this student database. Note that Student and StudentCourse tables should be listed as shown. Since Term and Grade columns are already in the StudentCourse table, they should not be in the Course table.
To understand how your work will be assessed, view the Scoring Rubric. Click for more options
Click on the assignment title link above to submit your completed assignment.
