Data Modeling Using the Entity. Relationship Model

1. Data Modeling Using the Entity. Relationship Model •

2. Introduction • Once all the requirements have been collected and analyzed, • the next step is to create a conceptual schema for the database, using a high-level conceptual data model. • This step is called conceptual design.

3. Introduction • The conceptual schema is a concise description of the data requirements of the users and • includes detailed descriptions of the entity types, relationships, and constraints; • these are expressed using the concepts provided by the high-level data model. • Because these concepts do not include implementation details, they are usually easier to understand and can be used to communicate with nontechnical users.

4. Introduction • The high-level conceptual schema can also be used as a reference to ensure that • all users' data requirements are met and that the requirements do not conflict. • This approach enables the database designers to concentrate on specifying the properties of the data, • without being concerned with storage details. Consequently, • it is easier for them to come up with a good conceptual database design

5. ER modeling process is independent of the development platform (or software) • Enables unambiguous, accurate communication of understanding of the data resource in an abstract level • It is used for communications between database designer & users during systemanalysis & design process

6. Top-down Approach • identify data entities • determine attributes of the entities • determine the nature of the relationships

7. Top-down Approach – usually results in a data model that is well organized • but details can be easily overlooked.

8. Bottom-up Approach • gather information on data used by the organization by... • group into entities of which these data are attributes • determine the nature of the relationships –

9. Bottom-up Approach insures that no important data is overlooked but overall organization may not be so apparent.

10. E-R Modeling • E/R model consists of: – Entity type – Attribute type – Relationship type

11. Entity Type • A collection of objects (or a concept) that is identified by the enterprise as having an independent existence & share common properties (characteristics).

12. Entity Type • Most entities are recognizable business concepts, either concrete or abstract, about which various data are stored. • .

13. Entity Type – In our usage, the term ‘entity’ will be synonymous with the terms ‘entity type’ or ‘entity class’. – it refers to the generalization of occurrences

14. Entity Occurrence or an instance • An object or concept that is uniquely identifiable. • Entity occurrence: Mr Mufungulwa is a lecturer at the Copperbelt University • Entity type: Lecturer

15. Weak & Strong Entity • Weak Entity Type • An entity type that is existence-dependent on some other entity type. • Children of an employee in a company DB.

16. Strong Entity Type • An entity type that is not existence-dependent on some other entity type. • An employee entity type in a company DB.

17. Attributes • An attribute is a piece of information at the atomic level - that is it cannot be subdivided into meaningful component pieces • Property of an entity that is of interest to the organization

18. Attributes • We are usually only interested in a subset of an entity’s attributes which is directly related to the application • it is a good practice to have for each attribute a brief description

19. Attributes • Registration Number: “The unique identifier assigned by Driver & Vehicle Licensing Agency to a vehicle driven on the public roads in Zambia.”

20. Attributes • Attributes take on particular values in occurrences • E.g. VehicleID of Car • Each entity will have one (or more) attribute that distinguishes it from all other entities, called an Identifier or a Primary Key

21. Attributes • Where two or more attributes comprise the identifier it is called a composite identifier • E.g. Patient_Id (Patient_Name, Date_Of_Birth) • An entity type may have more than one key (alternative keys) • E.g. Car Registration No., Vehicle Identification No

22. Types of Attributes • Simple: • Each entity has a single atomic value for the attribute, for example SSN, CourseNo. • Composite: • The attribute may be composed of several components

23. Types of Attributes • Multivalued: • An entity may have multiple values for that attribute; for example Color of a Car or PreviousDegrees of a Student • Derived: • The domain value of attribute can be determined from one or more other attributes.

24. Nested attribute • In general, composite and multiple-valued attributes may be nested arbitrarily to any number of levels • although this is rare.

25. Relationship • Relationship = An association among entities • STUDENT Taught by LECTURER

26. Unary or Recursive Relationships • A relationship where the same entity participates more than once in a different roles. • PERSON Married to PERSON • Staff Supervises Staff

27. Participation & Structural Constraints • Two types: • cardinality and participation constraints. • Cardinality Constraints (Ratio) • Determines the number of possible relationships for each participating entity.

28. Participation & Structural Constraints • The number of allowed instances of entity B that can (or must) be associated with each instance entity A. • Most common degree for relationships is binary with cardinality ratios of one-to-one (1:1), one-to-many (1:M) or many-to-many (M:N). •

29. Participation Constraints • is about the importance of the instances’ participation in a specific relationship. • If it is applied to every instance of an entity, then it is called a total or a mandatory participation • (it is a must for each instance belongs to that entity type).

30. Participation Constraints • If only parts of the instances participate in a relationship or in other words, an instance may or may not participate in that relationship, then this is called a partial or optional participation.

31. ER-diagram Chen Notation

34. AN EXAMPLE DATABASE ApPLICATION • Lets consider an example database application, called COMPANY, • it serves to illustrate the basic ER model concepts and their use in schema design. • We first list the data requirements for the database here, and then create its conceptual schema step by step as • we introduce the modeling concepts of the ER model

35. Problems with ER Models • Problems may arise when designing a conceptual data model called connection traps. • Often due to a misinterpretation of the meaning of certain relationships. • Two main types of connection traps are called fan traps and chasm traps.

36. Fan Trap • – When a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous.

37. SCHOOL RESPONSIBLE FOR HAS COURSE TEACHING STAFF

38. Fan Trap • From the above model we can tell which school is responsible for which course, but we cannot determine which lecturer teaches a particular course.

39. Chasm Trap • – When a model suggests the existence of a relationship between entity types, but the pathway between certain entity occurrences does not exist.

40. TEACHING STAFF HAS SUPERVISE DEPARTMENT STUDENT

41. Chasm Trap • The problems arise when we want to know which students are registered to which department • Since the supervision relationship is optional at both ends we cannot guarantee that each student has a supervisor and • hence we cannot determine the department for a student who has no supervisor.

42. The Enhanced Entity-Relationship Model • Since 1980s , new demanding applications emerged and hence new requirements . • Basic concepts of ERM are not sufficient to represent the requirements of the newer, more complex applications.

43. EERM • Response is development of additional ‘semantic’ modelling concepts. • Semantic concepts are incorporated into the original ER model and is called the Enhanced Entity-Relationship (EER) model.

44. EERM • EERM also useful to represent many real world objects that can be classified naturally into hierarchies. • Additional concepts of EER model includes Specialization / Generalization, & categorisation.

45. Concept of Specialization /Generalization • Superclass – it is an entity type – it is at a high level (Generalisation) – It includes distinct subclasses that require to be represented in a data model. – it is also known as a Supertype

46. Subclass • – it is an entity type • – it is at a lower level (Specialisation) • – it is a subset of a supertype • – it has a distinct role • –

47. Subclass • It is also known as a subtype • – the relationship with a superclass represents an “IS-A” relationship • – it shares common attributes or relationships distinct from other subsets

48. Subclass • An entity in a subclass may possess subclass specific attributes, as well as those associated with the superclass.

49. Generalization process • – The process of minimising the differences between entities by identifying their common features.

50. Super class/ Subclass participation Constraints • – Disjoint Mandatory • – Disjoint Optional • – Overlapping Optional • – Overlapping Mandatory