Removing Redundant Attributes in Entity Sets

One of the critical steps in database design using the Entity-Relationship (E-R) model is ensuring the elimination of redundant attributes in entity sets. This process helps to optimize the design, avoid data duplication, and ensure data consistency.

What are Redundant Attributes?

Redundant attributes are attributes in an entity set that can be inferred or derived through existing relationship sets. Including such attributes unnecessarily increases complexity and storage requirements.

Examples of Redundant Attributes

Consider the following examples:

1. Instructor and Department

• The instructor entity set has attributes: ID, name, dept_name, salary.
• The department entity set has attributes: dept_name, building, budget.

Here, the attribute dept_name appears in both entity sets. Since dept_name is the primary key of the department entity set, it is redundant in the instructor entity set and should be removed. Instead, the relationship inst_dept should relate instructors to departments.

2. Section and Time Slot

• The section entity set has attributes: course_id, sec_id, semester, year, building, room_number, time_slot_id.
• The time_slot entity set has attributes: time_slot_id, {(day, start_time, end_time)}.

Here, the attribute time_slot_id is redundant in the section entity set, as it is the primary key of the time_slot entity set. The relationship sec_time_slot should relate sections with time slots instead.

3. Section and Classroom

• The section entity set has attributes: course_id, sec_id, semester, year, building, room_number.
• The classroom entity set has attributes: building, room_number, capacity.

Attributes building and room_number are redundant in the section entity set. These attributes should be removed, and the relationship sec_class should relate sections with classrooms.

How to Avoid Redundant Attributes?

To eliminate redundancy, follow these best practices:

• Identify Relationships: Use relationship sets to model associations between entities instead of including attributes directly in multiple entity sets.
• Normalize Entity Sets: Ensure that each entity set contains only those attributes that are directly relevant and unique to it.
• Review Relationships: Regularly review relationship sets and entity attributes for overlaps or redundancy.

Final E-R Design for the University Database

Below is a list of optimized entity sets and relationship sets for a university database:

Entity Sets

• classroom: (building, room_number, capacity)
• department: (dept_name, building, budget)
• course: (course_id, title, credits)
• instructor: (ID, name, salary)
• section: (course_id, sec_id, semester, year)
• student: (ID, name, tot_cred)
• time_slot: (time_slot_id, {(day, start_time, end_time)})

Relationship Sets

• inst_dept: Relating instructors with departments.
• stud_dept: Relating students with departments.
• teaches: Relating instructors with sections.
• takes: Relating students with sections (with descriptive attribute grade).
• course_dept: Relating courses with departments.
• sec_course: Relating sections with courses.
• sec_class: Relating sections with classrooms.
• sec_time_slot: Relating sections with time slots.
• advisor: Relating students with instructors.
• prereq: Relating courses with prerequisite courses.

Conclusion

Removing redundant attributes is an essential step in creating an efficient database design. By carefully analyzing entity sets and their relationships, you can optimize the database structure, reduce storage requirements, and improve data consistency. Mastering these techniques is crucial for success in competitive exams like GATE, UGC NET, and NIELIT.