Question

Describe, using the table1, the problems that may occur when insertions deletions and modifications are made.

The Computer department of a large university has decided to provide students
with a document that lists the various types of programming skills they have
picked up on the course and the level they have reached in each. The table
below shows the attributes that need to be stored about each student along with
some sample entries.

The Skill_Id is a number that is unique for each skill, but the skill name is not.

The Student_Email is also unique for each student.

Students taking computing modules may come from outside courses such as
Business. This information along with the course director responsible is also
recorded. There is a single course director for each course, but a member of staff
can be course director for several courses.

Each programming skill that a student has been tested on is recorded with the
date that the test took place and the skill level reached. The test may be
repeated, but only the latest result is to be stored. Thus if Jones retakes the
Prolog test then only the date and level attributes would be updated.

le 1:Students_skill_table Student_Email Name Address Course Course_Director Skill_ld Skill_Name Skill_Date Skill_Level D96abc Jones 6 Old Manor Paul 23 Prolog 2/4/17 6 Computing Computing D96abc Jones 6 Old Manor Paul 23 Java 1/3/17 5 D96abd Stewart 4 Coach Rd Business Thompson 33 Prolog 3/5/17 8 D96xyz Rodgers 1 New Road Business Thompson 16 Pascal 1/3/17 4

Answer 1

1. Insertion anomalies When a new student is joined but yet to take skill test, we can't skip inserting skill details as those columns does not allow null values So we are unable to enter student details alone When a new course has been introduced but student not yet joined , we can't insert course details alone as student details and skill details does not allow null values When we need to add a course director alone but not yet assigned any course, as course details student details and skill details does not allow null values When we need to introduce new skills we have to insert new skill details alone but we cant insert as course details student details and skill details does not allow null values as no student got assigned to this new skill 2.Update anomalies • When we need to update the course director, we need to replace all the other records with the new director name which had the old director name who dealt earlier with that course. In such case skill details of previous under previous director will lose its consistency • Same anomalies apply when course or skill updated 3. Deletion anomalies When we delete student the skill details will be lost and the course details also lost When we delete a course, the course director details student details, skill test details of earlier dates will also be lost unnecessarily When we need to delete a specific skill, we will lose corresponding student, course, course director details unnecessarily

What is normalization upto 3 NF • A table is said to be in 1NF if it has single valued columns • A table is said to be in 2NF if it is in 1NF and every non key attribute is fully functionally dependent on the whole of the primary key and not on part of the primary key • A table is said to be in 3NF if it is in 2NF and does not have transitive dependencies which means non key attribute should not depend on another non key attribute which will be a candidate key but not a primary key or not even part of the composite primary key If the tables are not upto 3NF, the tables will have insertion , update and deletion anomalies Why this anomalies happened in the given table “Students_Skills_Table" is well explained using the below functional dependency diagram to get a clear understanding detailing that the given table is not normalized even in 2NF as it has partial dependencies.

Full Dependency Student Email Name Address Course Course_Director Skill Id Skill_Name Skill_Date Skill_Level Partial Dependency Partial Dependency Partial Dependency

Given table normalized to 3NF Course Course ID Course Director Director ID Course_Director Course_ID (FK) Student Student Email Name Address Course_ID (FK) Skill Skill Test Skill Id Skill_Name Course_ID (FK) Skill_Date Skill_Level Skill_ld (FK) Student_Email (FK)