Unit 2.3 EERD.pdf

The Relational Database The Extended Entity-Relationship Model CSC105 Modern Database Design The Extended Entity-Relationship Model ❖ As the complexity of the data structure being modelled has increased and as application software requirements have become more stringent, there has been an in...

The Relational Database The Extended Entity-Relationship Model CSC105 Modern Database Design The Extended Entity-Relationship Model ❖ As the complexity of the data structure being modelled has increased and as application software requirements have become more stringent, there has been an increasing need to capture more information in the data model. ❖ The extended entity-relationship model (EERM) is sometimes referred to as the enhanced entity relationship model. Entity Supertypes (Superclass) and Subtypes (Subclass) ❖ Because most employees possess a wide range of skills and special qualifications, data modellers must find various ways to group employees based on employees' characteristics. ❖ For instance, a retail company could group employees as salaried and hourly employees, while a university could group employees as faculty, staff and administrators. ❖ The grouping of employees to create various types of employees provides two essential benefits: ❖ It avoids unnecessary nulls in the employees’ attributes when some have characteristics other employees do not share. ❖ It enables a particular employee type to participate in relationships that are unique to that employee type. Entity Supertypes and Subtypes - Illustration ❖ The aviation business employs pilots, mechanics, secretaries, accountants, database managers, and many other employees. ❖ The figure given in the next slide illustrates how pilots share specific characteristics with other employees, such as last name (EMP_LNAME) and hire date (EMP_HIRE_DATE) ❖ On the other hand, many pilots’ characteristics are not shared by other employees, such as flight hour restrictions, flight checks and periodic training. ❖ Therefore, if all employee’s characteristics and special qualifications were stored in a single EMPLOYEE entity, you would have a lot of null values, or you would have to make a lot of needless dummy entries. Entity Supertypes and Subtypes - Illustration ❖ In this case, special pilot characteristics such as EMP_LICENSE, EMP_RATINGS and EMP_MED_TYPE will generate null values for employees who are not pilots. ❖ In addition, pilots participate in some relationships that are unique to their qualifications. ❖ For example, not all employees can fly aeroplanes; ONLY EMPLOYEEs who are pilots can participate in the “employee flies aeroplane” relationship. Entity Supertypes and Subtypes - Illustration ❖ Based of preceding discussion, you would correctly deduce the PILOT entity to store only those attributes that are unique to pilots, and that the EMPLOYEE stores attributes that are common to all employees. ❖ Based on that hierarchy, you can conclude that PILOT is a subtype of EMPLOYEE, and the EMPLOYEE is the supertype of PILOT. ❖ Entity supertype Generic entity type that is related to one or more entity subtype Contains common characteristics ❖ Entity subtypes Contains unique characteristics of each entity subtype When to use subtypes and supertypes? Two criteria help the designer determine when to use ❖ subtype and supertype: ❖ There must be different, identifiable kinds or types of entities in the user’s environment. ❖ The different kinds or types of instances should have one more attributes that are unique to that kind or type. ❖ In the preceding example, because pilots meet both criteria of being identifiable kinds of employees and having unique attributes that other employees do not possess, it is appropriate to create PILOT as a subtype of EMPLOYEE. Employee supertype with three subtypes Specialization ❖ Specialisation is the top-down approach to defining a set of subclass of an entity type. ❖ It depicts the arrangement of higher-level entity superclass and lower-level entity subclass. ❖ Specialisation is based on grouping unique characteristics and relationships of the subclass. ❖ The set of the subclass is defined based on some distinguishing characteristics of the entities in the superclass. For instance, the set of SECRETARY, ENGINEER, and TECHNICIAN is a specialisation of the EMPLOYEE superclass that distinguishes among employee entities based on the job type. Generalisation ❖ Generalization is the bottom-up approach to identifying a higher-level, more generic entity superclass from a lower-level entity subclass. ❖ Generalization is based on grouping common attributes and relationships of the subclass. ❖ For instance, consider entity types CAR and TRUCK as shown in Figure 12. Since they can have common attributes, they can be generalized into the entity type VEHICLE. Subclass Discriminator ❖ A subclass discriminator is an attribute in the superclass entity that determines to which subclass the superclass occurrence is related. ❖ For example, if the EMPLOYEE entity type has an attribute Job_type, as shown in Figure 13, we can specify the condition of membership in the SECRETARY subclass by the condition (Job_type = ‘Secretary’). ❖ This condition is a constraint specifying that exactly those entities of the EMPLOYEE entity type whose attribute value for Job_type is ‘Secretary’ belong to the subclass. Disjoint and Overlapping Constraints An entity superclass can have a disjoint or overlapping entity subclass. In disjoint, an entity can be a member of at most one of the subclasses of the specialisation. Disjoint is denoted by placing d in the circle, as shown in Figure 15. For instance, in Figure 15, an employee cannot belong to more than one subclass at a time; that is, an employee cannot be Secretary and Technician at the same time. That is why d is written inside the small circle. In overlapping, an entity may be a member of more than one subclass of the specialisation. Overlapping is denoted by placing o in the circle, as shown in Figure 16. For instance, in Figure 16, a part may be both purchased and manufactured at the same time. That is why o is written inside the small circle (Figure 14). THANK YOU