Data modeling using the Entity–Relationship(ER) model:

1. Data modeling using the Entity–Relationship(ER) model:

2. Entity – Relationship(ER) Model ER data model allows us to describe the data involved in a real world enterprise in terms of objects and their relationships and is used to develop an initial database. An entity is an object in the miniworld. An attribute of an entity can have a value from a value set (domain). Database design process can be divided into following steps :

4. Requirement collection and Analysis • Understanding what data is stored • What applications must be built on top • What operation are most frequent for performance requirements • User defined operation applied to database • Data flow diagram techniques for specifying functional requirement • 2. Creating conceptual schema • concise description of data requirements of user • Include detail descriptions of entity types , relationships,constraints • Enable DB to concentrate on specifying the properties of data,without concerned with storage detail. • 3. Logical design or data model mapping • Implementation of data model • Conceptual schema is transformed from high level data model into the implementation data model • 4. Physical design • Internal storage(structures ,access path , indexes , file organization) of database files are specified.

5. Entity-Relationship(ER) Model The ER model is a high-level conceptual data model. The ER model was introduced by Peter Chen in 1976, and is now the most widely used conceptual data model.

6. ER MODEL CONCEPT

7. TYPE OF ATTRIBUTES Simple or atomic : Attribute that are not divided.

8. Composite Attribute hierarchy Single – valued : Attribute which has single value. Eg Age

9. ENTITY TYPE AND KEY ATTRIBUTE

11. Displaying an Entity type

12. Entity type CAR with two key

13. Relationship Types , Sets and Instances Relationship When an attribute of one entity type refers to another entity type Represent references as relationships not attributes

14. Relationship • A relationship is an association among several entities. • Entity does not exits in isolation. • They always exist in relationship. • Degree of relationship • It is the number of participating entities. • A relationship of degree 2 is called binary and 3 is ternary. • Relationships usually have certain constraints that limit the possible combinations of entities that may participate in relationship instances. • Type of constraints • There are two types of relationship constraints • Cardinality ratio • Participation constraints

15. A relationship relates two or more distinct entities with a specific meaning. For example, EMPLOYEE John Smith works on the ProductX PROJECT or EMPLOYEE Franklin Wong manages the Research DEPARTMENT. Relationships of the same type are grouped or typed into a relationship type. For example, the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate. The degree of a relationship type is the number of participating entity types. Both MANAGES and WORKS_ON are binary relationships.

18. Cardinality Ratio It specifies the number of relationship instances that an entity can participate. Cardinality identifies the maximum number of instances of an entity that are related to one instance to another entity. Cardinality ratios for binary relationship types are : One-to –one(1:1) An entity in one table associated with at most one entity in other table. Eg: one person holds one job

19. One-to-many(1:N) An entity associated with any number of entities in other table but entity in other table can associate with at most one entity of first table. An employee can work for only one department but many employee can work in a department. Many to one An entity in table associated with at most one entity in other table.An entity in other table can be associated with any number of entities in first table. Many to many An entity in table can associated with any number of entities in another table and vice versa. Eg An employee can work for several projects and several employees can work on a project .

20. Participation It specifies whether the existence of an entity depends on its being related to another entity via relationship type. There are types : i)Total Participation Every entity in entity set must participate in relation. Ex: A company policy states that every emp must work for a department or an employee entity can exist only if it participates in relationship instances. Total participation is called existence dependency. Represented by double line ii) Partial Participation It means that some or part of the set is related. Ex : In emp & dept relationship – only one employee manages a depart not all. Represented by single line

21. Notations of E-R diagram

23. Example relationship instances of the WORKS_FOR relationship between EMPLOYEE and DEPARTMENT.

24. Attributes of Relationship Types Relationship types can also have attributes ,similar to those of entity types. Eg to record the number of hours per week that an employee works on a particular project . Entity – Relationship Diagram(ER Diagrams) Express the logical structure of a database graphically. It describes data as entities , relationships and attributes.

25. Weak Entity Types • A weak entity must participate in an identifying relationship type with an owner or identifying entity type • Entity types may not have any key attribute of their own. • Weak entity type are identified by being related to specific entities in combination with some of their attribute values. • Entities are identified by the combination of: • A partial key of the weak entity type • The particular entity they are related to in the identifying entity type. Example: Suppose that a DEPENDENT entity is identified by the dependent’s first name and birthdate, 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

26. Constraints on Relationships • Constraints on Relationship Types • ( Also known as ratio constraints ) • Maximum Cardinality • One-to-one (1:1) • One-to-many (1:N) or Many-to-one (N:1) • Many-to-many • Minimum Cardinality • (also called participation constraint or existence dependency constraints)

28. Structural Constraint Min, Max • A more complete specification of the structural constraint on a relationship type can be given by the integer pair (min, max), which means an entity must participate in at least min and at most max relationship instances.

29. Specified on each participation of an entity type E in a relationship type R • Specifies that each entity e in E participates in at least min and at most max relationship instances in R • Default(no constraint): min=0, max=n • Must have minmax, min0, max 1 • Derived from the knowledge of mini-world constraints 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 (0,n) for participation of DEPARTMENT in WORKS_FOR

31. Data Modeling Tools A number of popular tools that cover conceptual modeling and mapping into relational schema design. Examples: ERWin, S- Designer (Enterprise Application Suite), ER- Studio, etc. POSITIVES: serves as documentation of application requirements, easy user interface - mostly graphics editor support

32. Problems with Current Modeling Tools • DIAGRAMMING • Poor conceptual meaningful notation. • To avoid the problem of layout algorithms and aesthetics of diagrams, they prefer boxes and lines and do nothing more than represent (primary-foreign key) relationships among resulting tables.(a few exceptions) • METHODOLGY • lack of built-in methodology support. • poor tradeoff analysis or user-driven design preferences. • poor design verification and suggestions for improvement.