Dec 1998 DB212: Database Management Systems (Question 2)

(a)	Give three reasons why normalization is carried out.	[3]
	to give an efficient design (performane-wise) to reduce the amount of data stored to ensure design is free from anomalies (update, insertion, deletion) to comply with relational database rules to reveal relationships between entities to allow simple retrieval and simple maintenance, and to remove need for restructuring Any 3, 1 mark	[3]
(b)	What is denormalization? Give two situations where it might be considered, and explain carefully why it might be useful in each case.	[7]
	Denormalization: the (deliberate) implementation of a database in a form which does not obey the normalization rules. Possible situations for denormalization: when two entities are in a 1-1 relationship. Even if one entity is optional, it may be sensible to store the two entities together in a table. if there is a many-to-many relationship with non-key attributes. In order to extract data from the two entities, in the normalised form it would be necessary to join three tables, but it might be better to combine columns from one of the entities into the table representing the relationship: this will avoid the need for a join in many queries. reference data: this data in a one-many relationship, in the entity on the 'one' side, where the entity does not participate in any other relationships. In this case, two entities could be merged, especially when there are very few instances of the entity on the 'many' side for each entity instance on the 'one' side Any 2 out of these 3: 1 mark for identifying the situation, 2 more for explanation	[1] [6]
(c)	Apply the normalization rules to the following data structures, which show the data to be recorded in a database of student records. All of your intermediate steps (UNF, INF, 2NF, 3NF) should be shown in detail.. student record = student number + student name + contact phone number + major + {course number + course title + instructor name + instructor location + grade}	[10]
	UNF (StuNo, StuName, PhoneNo, Major, {CourseNo, CourseTitle, InsName InsLoc, Grade})	[1]
	INF STUDENT = (StuNo, StuName, PhoneNo, Major) GRADE = (StudNo, CourseNo, CourseTitle, InsName, InsLoc, Grade)	[2]
	2NF STUDENT = (StudNo, StuName, PhoneNo, Major) GRADE = (StudNo, CourseNo, Grade) COURSE = (CourseNo, CourseTitle, InsName, InsLoc)	[3]
	3NF STUDENT = (StudNo, StuName, PhoneNo, Major) GRADE = (StudNo, CourseNo, Grade) COURSE = (CourseNo, CourseTitle, InsName) INSTRUCTOR = (InsName, InsLoc) Consequential error: candidates should only be penalised once for a mistake: e.g., a 2NF that is correctly derived from an incorrect 1NF should be given credit. Markers should therefore be looking for understanding: have candidates removed repeating groups (for 1NF), removed partial functional dependencies (for 2NF), removed transitive dependencies (for 3NF)?	[4]