Assignment 2 The figure below shows a table called GRADE REPORT for a university
ID: 3701898 • Letter: A
Question
Assignment 2 The figure below shows a table called GRADE REPORT for a university StudentIDStudent Major ID Instructor Instructor Name ress tle Name 16830045 Brooks IS IS 350 Database Mgt ? 104 IS 465 nalysis B317 329107 Baker104 Philips IS 350 Database Mgt ? 104 t 201 Fund Acctg Milier H 310 Mkgt 300 Intro Mktg Bennett B212 Assume that students can take multiple courses and has only one major; also assume that one course is taught by one professor. 1) Describe deletion, update, and insertion problems for this table 2) In what normal form is this table? 3) State the functional dependencies in this table. 4) Then decompose this table into 3NF.Explanation / Answer
1)
deletion anomaly ---- If Baker left the university, all the records of courses in which he was enrolled also get deleted. Now if course Acct 201 has been enrolled by only one student Baker, all the information about the course will also get deleted .
updation anomaly --- If the campus address of Baker needs to be changed , all the 3 rows need to be updated. If all the rows are not modified , inconsistency in database exists.
insertion anomaly ---- If a new course needs to be inserted , but there is no student enrolled in it yet. The CourseID cannot be left NULL.
2)
The table is in First Normal Form as there are all atomic attributes .
3)
Functional Dependencies:
StudentID -> Student_Name,Campus_Address,Major,Grade
CourseID ->Course_Title
Instructor_Name -> Instructor_Office
4) 3NF
Student(StudentID, Student_Name,Campus_Address,Major,Grade)
Course(CourseID,Course_Title)
Instructor(Instructor_Name, Instructor_Office)
underlined are primary keys
Do ask if any doubt. Please upvote.