Enhanced E-R Model and Business Rules

1. Enhanced E-R Model andBusiness Rules CS263 Lecture 4

2. Introduction The basic ER model was introduced in the mid 1970s Since then business relationships and business data have become more complex The term Enhanced Entity Relationship (EER) model refers to the extension of the original ER model

3. Supertype/Subtype relationships Most important new modelling construct in the EER model Allows us to model a general entity type (the supertype) and then subdivide it into several specialised entity types (called subtypes) Each subtype inherits from its supertype and may have special attributes of its own

4. Representing supertypes and subtypes • The supertype is connected with a line to a circle, which in turn is connected by a line to each subtype that has been defined (see Fig.) • The ‘U’ shaped symbol on each line connecting a subtype to the circle indicates that the subtype is a subset of the supertype, and also indicates the direction of the relationship • Attributes shared by all the entities are associated with the supertype, whilst attributes that are unique to a particular subtype are associated with that subtype

5. Basic notation for supertype/subtype relationships

6. An example: the EMPLOYEE supertype Suppose that an organisation has 3 basic types of employees: Hourly: Employee_Number, Employee_Name, Address, Date_Hired, Hourly_Rate Salaried: Employee_Number, Employee_Name, Address, Date_Hired, Annual_Salary, Stock_Option Contract consultants: Employee_Number, Employee_Name, Address, Date_Hired, Contract_Number, Billing_Rate

7. Employee supertype Many attributes the same across 3 types, so we could define a supertype called EMPLOYEE, with subtypes for HOURLY_EMPLOYEE, SALARIED_EMPLOYEE and CONSULTANT (see Fig)

8. Employee supertype with three subtypes All employee subtypes will have emp_no., name, address, and date-hired Each employee subtype will also have its own attributes

9. When to use supertype/subtype relations Should consider using subtypes when either (or both) of the following conditions are present: 1. There are attributes that apply to some (but not all) of the instances of an entity type 2. The instances of a subtype participate in a relationship unique to that subtype Both are true in the following Fig., where PATIENT has two subtypes: OUTPATIENT and RESIDENT PATIENT (the primary key is PATIENT_ID) All patients have an Admit_Date and a Patient_Name

10. Patient example Every patient is cared for by a RESPONSIBLE_PHYSICIAN who develops a treatment plan for the patient Each subtype also has unique attributes. Outpatients have a Checkback_Date, whilst residents have a Date_Discharged and a unique relationship that assigns each patient to a bed (this is a mandatory relationship, and each bed may or may not be assigned to a patient)

11. Supertype/subtype relationships of patients Both outpatients and resident patients are cared for by a responsible physician Only resident patients are assigned to a bed

12. Generalization and specialization • Generalization: = the process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP • Specialization = the process of defining one or more subtypes of the supertype, and forming supertype/subtype relationships. TOP-DOWN • Following Figs. Shows Generalisation in a situation where we have 3 different entity types: CAR, TRUCK and MOTORCYCLE • In second Fig. The more general entity type VEHICLE is shown

13. Generalisation MOTORCYCLE is not shown as it does not satisfy conditions for a subtype discussed earlier – the only attributes of MOTORCYCLE are those that are common to all vehicles, there are no attributes specific to motorcycles. Further, MOTORCYCLE does not have a relationship to another entity type

14. Example of generalization Three entity types: CAR, TRUCK, and MOTORCYCLE All these types of vehicles have common attributes

15. Generalization to VEHICLE supertype So we put the shared attributes in a supertype Note: no subtype for motorcycle, since it has no unique attributes

16. Specialisation In an example of specialisation, an entity type PART has identifier Part_No and other attributes Description, Unit_price, Location, Qty_On_Hand, Routing_Number and Supplier (a multivalued attribute as there may be more than one supplier) Some parts are internally Manufactured Parts whilst others are externally Purchased Parts (some parts are obtained from both sources – when the choice depends on factors such as manufacturing capacity, unit price of the parts etc.)

17. Specialisation Some attributes apply to all parts regardless of source, others such as Routing_Number depend on the source as they apply only to Manufactured Parts. This suggests that PART should be specialised by defining the subtypes MANUFACTURED_PART and PURCHASED_PART A new relationship ‘Supplies’ has been created between PURCHASED_PART and SUPPLIER that allows users to more easily associate purchased parts with their suppliers The attribute Unit_Price is now associated with the relationship ‘Supplies’ so that the price may vary between suppliers

18. Example of specialization Entity type PART Applies only to purchased parts Only applies to manufactured parts

19. Specialization to MANUFACTURED PART and PURCHASED PART Note: multivalued attribute was replaced by a relationship to another entity Created 2 subtypes

20. Completeness constraints • Whether an instance of a supertype must also be a member of at least one subtype. Has two possible rules: • 1:Total Specialization Rule: Yes (double line) In following Fig. A PATIENT must either be an OUTPATIENT or a RESIDENT PATIENT. Total specialisation is indicated by the double line extending from the PATIENT identity type to the circle

21. Examples of completeness constraints A patient must be either an outpatient or a resident patient Total specialization rule

22. Completeness constraints • Partial Specialization Rule: No (single line) An entity instance of the supertype is allowed not to belong to any subtype. • In the following Fig. If a VEHICLE is a car it will appear as an instance of CAR, and if a truck as an instance of TRUCK. • However, if the vehicle is a motorcycle it cannot appear as an instance of any subtype. This example of partial specialisation is specified by the single line from the VEHICLE supertype to the circle

23. Partial specialization rule A vehicle could be a car, a truck, or neither

24. Disjointness constraint • Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes. Has two possible rules: • Disjoint Rule: An instance of the supertype can be only ONE of the subtypes. Following Fig. shows that at any one time a PATIENT must be either an outpatient or a resident patient but cannot be both – specified by the letter ‘d’ • The subclass of a patient may change over time, but at any given time a patient is of only one type

25. Examples of disjointness constraints A patient can either be outpatient or resident, but not both Disjoint rule

26. Disjointness constraint • Overlap Rule: An instance of the supertype can simultaneously be a member of more than one of the subtypes. Some PARTs are both Manufactured and Purchased. An instance of PART is a particular Part Number (i.e. type of part) rather than the individual part itself (Part_No). Considering Part Number 4000, at a given time there may be 250 of this part to hand, of which 100 are manufactured and 150 are purchased. The overlap is specified by placing an ‘o’ in the circle • Double line suggests any part must be either a purchased part or a manufactured part, or it may simultaneously be both of these

27. A part may be both purchased and manufactured Overlap rule

28. Subtype discriminators • Attribute of the supertype whose values determine the target subtype(s) • Disjoint subtypes: a simple attribute with alternative values to indicate the possible subtypes. In the following Fig. A new attribute ‘Employee_Type’ has been added to the supertype to serve as a subtype discriminator. 3 values ‘H’ = Hourly, ‘S’ = Salaried, ‘C’ = Consultant. This is assigned the correct value when a new employee is added.

29. Subtype discriminators The expression “Employee_Type = “ (the LHS of a condition statement) is placed next to the line leading from the supertype to the circle, with the value of the attribute that selects the appropriate subtype placed adjacent to the line leading to that subtype

30. A simple attribute with different possible values indicating the subtype Subtype discriminator (disjoint rule)

31. Subtype discriminators • Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a boolean value to indicate whether or not the instance belongs to the associated subtype. • In the following Fig. a new attribute Part_Type has been added to PART. Part_Type is a composite attribute with components ‘Manufactured?’ and ‘Purchased?’ Each of these attributes is a boolean variable, and can be combined as: Manufactures only = YN, Purchased only = NY, Purchased and manufactured = YY

32. A composite attribute with sub-attributes indicating “yes” or “no” to determine whether it is of each subtype Subtype discriminator (overlap rule)

33. Defining supertype/subtype hierarchies It is possible for any of the subtypes in these examples to have other subtypes defined on it A supertype/subtype hierarchy is a hierarchical arrangement of supertypes and subtypes, where each subtype has only one supertype In modelling the Human resources in a University, the most general entity type would be PERSON (sometimes called the root) Relevant attributes are SSN (Social Security Number – Identifier), Name, Address, Gender and Date_Of_Birth

34. Defining supertype/subtype hierarchies Next we define all major subtypes of the root, here there are 3, EMPLOYEE, STUDENT and ALUMNUS (already graduated) A person may belong to more than one subtype (such as ALUMNUS and EMPLOYEE) so the overlap rule is used. Overlap allows for any overlap (3-way) so if certain combinations are not allowed a more refined supertype/subtype hierarchy would have to be developed to eliminate these

35. Defining supertype/subtype hierarchies The next step is to evaluate whether any of the subtypes already defined qualify for further specialisation. Here employee has two subtypes, FACULTY and STAFF Because there may be types of employee other than Faculty and Staff, the partial specialisation rule is indicated. However, such an employee cannot be both Faculty and Staff at the same time, so the disjoint rule is indicated in the circle

36. Defining supertype/subtype hierarchies Two subtypes are defined for student: GRADUATE_STUDENT and UNDERGRADUATE STUDENT Notice that total specialisation and the disjoint rule are specified

37. Example of supertype/subtype hierarchy

38. Entity clustering • EER diagrams are difficult to read when there are too many entities and relationships • Solution: group entities and relationships into entity clusters • Entity cluster: set of one or more entity types and associated relationships grouped into a single abstract entity type • Because an entity cluster behaves like an entity type, entity clusters and entity types can be further grouped to form a higher-level entity cluster

39. Entity clustering Entity-clustering is a hierarchical decomposition of a macro-level view of the data model into finer and finer views, eventually resulting in the full, detailed data model. The first of the following Figs. Shows the completed data model with dashed lines drawn around possible entity clusters, and the second shows the final result of transforming this into an EER diagram of only entity clusters and relationships

40. Entity clustering Entity clusters are formed: 1. By abstracting a supertype and its subtypes (see CUSTOMER) 2. By combining directly related entity types and their relationships (SELLING_UNIT, ITEM, MATERIAL, MANUFACTURING) An entity cluster can also be formed by combining a strong entity and its associated weak entity types (not shown here)

41. Possible entity clusters for Pine Valley Furniture (PVF) Related groups of entities could become clusters

42. EER diagram of PVF entity clusters More readable, isn’t it?

43. Business rules • Statements that define or constrain some aspect of the business. We have already seen some (rules about relationships between entity types [cardinality], supertype/subtype relationships and definitions about attributes and entities). There are 3 main types of business rules: • 1. Derivation – rule derived from other knowledge • 2. Structural assertion – rule expressing static structure of the organisation • 3. Action assertion – rule expressing constraints/control of organizational actions • Following Fig. Is an EER diagram to describe business rules

44. EER depiction of business rules classification

45. Business rules The ER model in the following Fig. Contains 4 entity types: FACULTY, COURSE, SECTION and STUDENT. SECTION is a weak entity type, because it cannot exist without the COURSE entity type The identifying relationship for SECTION is ‘Is_Scheduled’ and the partial identifier is Section_ID (a composite attribute) Only selected attributes are shown for the various entity types to simplify the diagram

46. Stating a structural assertion Four examples are: 1. A course is a module of instruction in a particular subject area. This definition of the term course associates the two terms (module and subject area) 2. Student_Name is an attribute of student 3. A student may register for many sections, and a section may be registered for by many students – this fact states the participation of entity types in a relationship (Is_Registered)

47. Stating a structural assertion 4. A faculty member is an employee of the University. Although not shown in the following Fig., this fact designates a supertype/subtype relationship. The type of facts above are called base facts, they are fundamental and cannot be derived from other terms or facts

48. Derived facts A derived fact is a fact that is derived from business rules using an algorithm or inference A derived attribute is an example of a derived fact Two examples of derived facts follow: 1. Student_GPA (Grade Point Average) = Quality_Points/Total_Hours_Taken Where Quality_Points = sum [for all courses attempted] (Credit_Hours*Numerical_Grade) Student_GPA could be shown in the Fig. As a dashed oval connected to STUDENT

49. Derived facts 2. A student is taught by the faculty assigned to the sections for which the student is registered – this fact can be derived by following the Is_Registered relationship from STUDENT or SECTION and then the Is_Assigned relationship from SECTION to FACULTY

50. Stating an action assertion Action assertions deal with the dynamic aspects of the organisation and impose “must/must not” and “should/should not” constraints on handling data An action assertion is the property of some business rule (called the anchor object) for a data handling action (such as create, update, delete etc.) and it states how the other business rules (called corresponding objects) act on the anchor object