The Enhanced E-R Model and Business Rules Database Management Systems Fall 2009 Department of Computer Systems Enginee

1. The Enhanced E-R Model andBusiness RulesDatabase Management SystemsFall 2009 Department of Computer Systems Engineering, U.E.T. Peshawar Adapted from: Modern Database Management Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden

2. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Supertypes and Subtypes Subtype: A subgrouping of the entities in an entity type which has attributes that are distinct from those in other subgroupings Supertype: A generic entity type that has a relationship with one or more subtypes Inheritance: Subtype entities inherit values of all attributes of the supertype An instance of a subtype is also an instance of the supertype

3. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Motivation for entity subtypes and supertypes : Nulls Created by Unique Attributes [1]

4. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition So the grouping of employees to create various types of employees provides two important details: It avoids unnecessary nulls in the employee attributes when some employees have characteristics that are not shared by other employees. It enables a particular employee type to participate in relationships that are unique to that employee type. Motivation for entity subtypes and supertypes : Nulls Created by Unique Attributes

5. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

6. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Specialization hierarchy A specialization hierarchy (i.e. entity subtypes/supertypes) provides a means to: Support attribute inheritance (which enables an entity subtype to inherit the attributes and relationships of the supertype.) Define a special supertype attribute knows as the subtype discriminator. Define disjoint/overlapping constraints and complete/partial constraints.

7. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

8. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Relationships and Subtypes Relationships at the supertype level indicate that all subtypes will participate in the relationship The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level

9. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

10. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition 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

11. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

12. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

13. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

14. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

15. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Constraints in Supertype/ Completeness Constraint Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype Total Specialization Rule: Yes (double line) Partial Specialization Rule: No (single line)

16. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

17. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

18. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Constraints in Supertype/ Disjointness constraint Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes. Disjoint Rule: An instance of the supertype can be only ONE of the subtypes Overlap Rule: An instance of the supertype could be more than one of the subtypes

19. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

20. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

21. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Constraints in Supertype/ Subtype Discriminators Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s) Disjoint – a simple attribute with alternative values to indicate the possible subtypes 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

22. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

23. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

24. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

25. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Quiz # 2: Draw an EER diagram for the following problem: A nonprofit organization depends on a number of different types of its successful operation. The organization is interested in the following attributes for all of these persons: SSN, Name, Address, City/State/Zip, and Telephone. These types of persons are of greatest interest: employees, volunteers, and donors. Employees only have a Date_Hired attribute, and volunteers only have a Skill attribute. Donors only have a relationship (named Donates) with an Item entity type. A donor must have donated one or more item and an item may have no donors, or one or more donors. There are persons other than employees, volunteers, and donors who are of interest to the organization, so that a person need not belong to any of these three groups. On the other hand, at a given time a person may belong to tow or more of these groups (for example, employee and donor).

26. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Quiz # 2: solution

27. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Entity Clusters 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

28. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

29. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

30. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Business rules [2] A business rule is a: Term: The application of a single definition to a word or phrase Fact: An association between two or more terms Derivation: An attribute derived from other attributes Constraint: A condition that determines what values an attribute or relationship can or must have. In case of designing a database for a university, some examples of business rules are: "A student may register for a section of a course only if he or she has successfully completed the prerequisites"  "A faculty member teaches from 0 to a maximum of 3 courses per semester" "Students may not take more than 18 credits (except by special permission)

31. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition Three main types of business rules A derivation…statement derived from other knowledge in the business, such as mathematical or logical inference involving literals and facts. E.g., a derivation is the calculation of a pay check based on work hours minus deductions. A structural assertion…expresses some aspects of the static structure of the organization. The E-R diagram represents the structural assertion of data entities and attributes, and the relationships among entities. It is stated either as a term or a fact. E.g. “A course is module of instruction in a particular area”, “Student name is an attribute of student”. An action assertion…statement of constraints or control on the action of the organization. It deals with the dynamic aspect of the organization. E.g. “A student must have a GPA >2.2 to pass the course”. (Action assertions are implemented as triggers or stored procedures in databases in databases).

32. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

33. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition How Action Assertions are stated? Anchor Object – an object on which actions are limited Corresponding Objects – an object influencing the ability to perform an action on another business rule Action – creation, deletion, update, or read Example:

34. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

35. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

36. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

37. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition

38. Adapted from: Chapter 4 of Hoofer et al, “Modern Database Management”, 6th Edition REFERENCES: [1].Peter Rob, & Carlos Coronel. Database Systems: Design, Implementation, and Management, 7th Edition, Thomson Technology [2]. “Modeling Business Rules: What Data Models Do”, by David C. Hay, (http://www.tdan.com/view-articles/5174/)