UNIT 1.2_CH7_ER DIAGRAM Database management system

It tell us about ER Diagrams

1. Dr. P.N.Jayanthi Asst. Prof. Dept. of ECE PNJ_ECE Slide 2- 1 Entity Relation Ship model UNIT 1 _CHAPTER 7 5 TH Sem Professional Core Elective-I Course Code: 21EC55B4

2. Entity Relational Model: Relational Model Concepts, Relational Model Constraints and Relational Database Schemas. Entity types, sets, Relationship, attributes. Case Study discussions for ER Diagrams. Introduction to Mongo DB. PNJ_ECE 2

3. Example COMPANY Database Requirements of the Company (oversimplified for illustrative purposes) The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. Each department controls a number of PROJECTs . Each project has a name, number and is located at a single location. PNJ_ECE Chapter 3- 3

4. Example COMPANY Database (Cont.) We store each EMPLOYEE’s social security number, address, salary, and birth date. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, birth date, and relationship to employee. PNJ_ECE Chapter 3- 4

5. Database State for COMPANY

6. Entity Relationship Model Concepts PNJ_ECE 6

7. Entity The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) or it may be an object with a conceptual existence (for instance, a company, a job, or a university course). PNJ_ECE 7

8. Attributes Attributes Attributes are properties used to describe an entity. For example an EMPLOYEE entity may have a Name, SSN, Address, BirthDate A specific entity will have a value for each of its attributes. For example a specific employee entity may have Name='John Smith', SSN='123456789', Address ='731, Fondren , Houston, TX', Sex='M', BirthDate ='09-JAN-55‘ Each attribute has a value set (or data type) associated with it – e.g. integer, float.. etc PNJ_ECE Chapter 3- 8

9. Types of Attributes (1) Simple Each entity has a single atomic value for the attribute. For example, SSN or Adhar number or voter id . Composite The attribute may be composed of several components. For example, Address (Apt#, House#, Street, City, State, ZipCode , Country) or Name ( FirstName , MiddleName , LastName ). Composition may form a hierarchy where some components are themselves composite. Multi-valued An entity may have multiple values for that attribute. For example, Color of a CAR or Previous Degrees of a STUDENT. Denoted as {Color} or { PreviousDegrees }. PNJ_ECE Chapter 3- 9

10. Types of Attributes (2) Complex Attributes In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels although this is rare. For example, Previous Degrees of a STUDENT is a composite multi-valued attribute denoted by {Previous Degrees (College, Year, Degree, Field)}. Null Values A particular entity may not have an applicable value for an attribute. Eg : Apartment number, college degree Null can also be used if we do not know Home phone number Two types of null 1) not known--- DOB 2) missing --- height of a person PNJ_ECE Chapter 3- 10

11. Entity Types and Key Attributes Entities with the same basic attributes are grouped or typed into an entity type. For example, the EMPLOYEE entity type or the PROJECT entity type. An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type. For example, SSN of EMPLOYEE. A key attribute may be composite. For example, VehicleTagNumber is a key of the CAR entity type with components (Number, State). An entity type may have more than one key. For example, the CAR entity type may have two keys: VehicleIdentificationNumber (popularly called VIN) and VehicleTagNumber (Number, State), also known as license_plate number. PNJ_ECE Chapter 3- 11

12. weak entity Entity types that do not have key attributes of their own are called weak entity types. Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination with one of their attribute values. We call this other entity type the identifying or owner entity type and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type. Suppose that a DEPENDENT entity is identified by the dependent’s first name and birth date, and the specific EMPLOYEE that the dependent is related to. DEPENDENT is a weak entity type with EMPLOYEE as its identifying entity type via the identifying relationship type DEPENDENT_OF PNJ_ECE 12

13. Relationship Types ,Sets, Roles Whenever an attribute of one entity refers to another entity type, some relationship exists. Eg . The attribute Manager of DEPARTMENT refers to an EMPLOYEE who manages the department. As the design is refined , the attributes get converted into relationships between entity types. A relationship type among many entity types, defines a set of relationship set. PNJ_ECE Chapter 3- 13

14. Relationship Types ,Sets, Roles contd … A relationship relates two or more distinct entities with a specific meaning Eg . A relationship type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates each employee with the department for which the employee works. Relationships of the same type are grouped into a relationship type. Eg . the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate. In ER diagram, relationship types are displayed as diamond shaped boxes which are connected by straight lines to the participating entity types. The relationship name is displayed in the diamond shaped box. PNJ_ECE Chapter 3- 14

15. PNJ_ECE 15

16. Initial conceptual design of the company PNJ_ECE Chapter 3- 16

17. PNJ_ECE Chapter 3- 17

18. Relationship Types … Relationships Degree: Degree of a Relationship Type. The degree of a relationship type is the number of participating entity types. Both WORKS_FOR and WORKS_ON are binary relationships. Cardinality Ratios for Binary Relationships. The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in. PNJ_ECE Chapter 3- 18

19. Example relationship instances of the WORKS_FOR relationship between EMPLOYEE and DEPARTMENT PNJ_ECE 19

20. Relationship Types ,Sets, Roles contd… Role names and Recursive Relationships Each entity type that participate in a relationship type plays a particular role in the relationship. Eg . in WORK_FOR relationship type, EMPLOYEE palys role of the employee or worker and DEPARTMENT plays the role of employer Role names are not necessary in relationship types where all the participating entity types are distinct. However same entity type participates more than once in a relationship type in different roles, role name becomes essential-recursive relationship types. PNJ_ECE Chapter 3- 20

21. Relationships and Relationship Types (2) More than one relationship type can exist with the same participating entity types. For example, MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT, but with different meanings and different relationship instances. PNJ_ECE Chapter 3- 21

22. ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF PNJ_ECE Chapter 3- 22

23. Constraints on Relationships Constraints on Relationship Types ( Also known as ratio constraints ) Maximum Cardinality: max number of relationship (binary)instances that an entity can participate in. One-to-one (1:1) One-to-many (1:N) Many-to-one (N:1) Many-to-many(M:N) Minimum Cardinality (also called participation constraint or existence dependency constraints) Full or total(existence dependency) Partial PNJ_ECE Chapter 3- 23

24. Example relationship instances of the WORKS_FOR relationship between EMPLOYEE and DEPARTMENT PNJ_ECE 24

25. Max cardinality ratio: One to one(1:1) RELATIONSHIP PNJ_ECE Chapter 3- 25

26. Many-to-many (M:N) RELATIONSHIP PNJ_ECE Chapter 3- 26 e 1  e 2  e 3  e 4  e 5  e 6  e 7  r 1 r 2 r 3 r 4 r 5 r 6 r 7  p 1  p 2  p 3 r 9

27. Example relationship instances of the SUPPLY relationship between SUPPLIER , PROJECT AND PART PNJ_ECE 27

28. Recursive Relationships PNJ_ECE Chapter 3- 28

29. Recursive Relationship Type is: SUPERVISION (participation role names are shown) PNJ_ECE Chapter 3- 29

30. Weak Entity Type is: DEPENDENT Identifying Relationship is: DEPENDENTS_OF PNJ_ECE Chapter 3- 30

31. Participation constraints This constraints specifies the minimum number of relationship instances that each entity can participate in. Total: participation of every entity in the set of employee entity must be related to department entity via WORK_FOR. In ER diagram this is showed using double line. Partial: some or part of the set of employee entities are related to some department entity via MANAGES, but not necessarily all. In ER diagram this is showed using single line. PNJ_ECE Chapter 3- 31

32. Attributes of Relationship types A relationship type can have attributes; for example, Hours Per Week of WORKS_ON; its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT. Attributes of 1:1 relationship types can be migrated to any one of the participating entities. Attributes of 1:N or N:1 relationship types can be migrated only to the entity type on N side of relationship. Attributes of M:N relationship types can be be calculated by combination of participating entities. PNJ_ECE Chapter 3- 32

33. Attribute of a Relationship Type is: Hours of WORKS_ON PNJ_ECE Chapter 3- 33

34. Structural Constraints – one way to express semantics of relationships Structural constraints on relationships: Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N SHOWN BY PLACING APPROPRIATE NUMBER ON THE LINK. Participation constraint (on each participating entity type): total (called existence dependency ) or partial. SHOWN BY DOUBLE LINING THE LINK NOTE: These are easy to specify for Binary Relationship Types . PNJ_ECE Chapter 3- 34

35. Naming of schema construct Entity names –upper case letters ,singular names(nouns) Relationship types - upper case letters ,verbs. Binary relationship –L to R, top to bottom or bottom to top. PNJ_ECE Chapter 3- 35

36. Alternative (min, max) notation for relationship structural constraints: Involves associating a pair of integer numbers(min,max) with each participating entity type E in a relationship type R specifies min =0 implies partial participation and min >0 implies total participation Must have 0  minmax, min0 and max 1 Specifies that each entity e in E participates in at least min and at most max relationship instances in R at any point of time. Derived from the knowledge of mini-world constraints PNJ_ECE Chapter 3- 36

37. Examples: A department has exactly one manager and an employee can manage at most one department. Specify (0,1) for participation of EMPLOYEE in MANAGES Specify (1,1) for participation of DEPARTMENT in MANAGES An employee can work for exactly one department but a department can have any number of employees . Specify (1,1) for participation of EMPLOYEE in WORKS_FOR Specify (1,N) for participation of DEPARTMENT in WORKS_FOR PNJ_ECE Chapter 3- 37

38. PNJ_ECE Chapter 3- 38 The (min,max) notation relationship constraints Employee Department Manages (1,1) (0,1) Employee Department Works-for (1,N) (1,1)

39. COMPANY ER Schema Diagram using (min, max) notation PNJ_ECE Chapter 3- 39

40. The company conceptual schema in UML class diagram PNJ_ECE Chapter 3- 40

41. Entity-class Relationship types-associations Relationship instances-links. Relationship attributes-link attributes Relationship constraints-multiplicities PNJ_ECE Chapter 3- 41

42. Relationships of Higher Degree Relationship types of degree 2 are called binary Relationship types of degree 3 are called ternary and of degree n are called n-ary In general ,a relationship type R of degree n will have n- edges in an ER diagram, one connecting R to each participating entity type In general, an n-ary relationship is not equivalent to n binary relationships PNJ_ECE Chapter 3- 42

43. Ternary relationship SUPPLY is a set of relationship instances(s,j,p) PNJ_ECE Chapter 3- 43

44. Three binary relationship types. Instances (s,p) for can_supply relationship. Instance (j,p) for uses relationship Instance (s,j) for supplies relationship. These relationship instances does not imply an instance (s,j,p) which is ternary relationship PNJ_ECE Chapter 3- 44

45. Ternary relationship. Relationship instance(i,s,c). (i ,s,c) should not exist in OFFERS unless each binary instances exist in its respective relationship type In general, three binary relationship cannot replace a ternary relationship. Depending on cardinality ratio, The schema designer must analyze. PNJ_ECE Chapter 3- 45

46. Constraints on ternary relationship There are 2 constraints: 1.cardinality ratio Constraint for a particular project-part combination, then only one supplier will be used. we use 1 on supplier participation and M,N on project, part participat ion Instance ( j,p ) can appear at most once in a relationship set, ie ., each ( j,p ) uniquely identifies a single supplier. ( j,p ) combination acts as a key, which uniquely determines relationship instance( s,j,p ) in the relationship set. PNJ_ECE Chapter 3- 46

47. PNJ_ECE Chapter 3- 47 Some of the Currently Available Automated Database Design Tools COMPANY TOOL FUNCTIONALITY Embarcadero Technologies ER Studio Database Modeling in ER and IDEF1X DB Artisan Database administration and space and security management Oracle Developer 2000 and Designer 2000 Database modeling, application development Popkin Software System Architect 2001 Data modeling, object modeling, process modeling, structured analysis/design Platinum Technology Platinum Enterprice Modeling Suite: Erwin, BPWin, Paradigm Plus Data, process, and business component modeling Persistence Inc. Pwertier Mapping from O-O to relational model Rational Rational Rose Modeling in UML and application generation in C++ and JAVA Rogue Ware RW Metro Mapping from O-O to relational model Resolution Ltd. Xcase Conceptual modeling up to code maintenance Sybase Enterprise Application Suite Data modeling, business logic modeling Visio Visio Enterprise Data modeling, design and reengineering Visual Basic and Visual C++