ER MODEL.pptx

data model data relationship
data semantics . er model , relational model, basic er number
entity types
entity shape relationship

1. ER- MODEL & RELATIONAL MODEL Dr. Robin Mathur, LPU

2. DATA MODEL Slide 1- 2 A collection of tools for describing Data Data relationships Data semantics Data constraints

3. ER MODEL The ER model defines the conceptual view of a database. It works around real-world entities and the associations among them. At view level, the ER model is considered a good option for designing databases. Slide 1- 3

4.   E-R model stands for Entity Relationship model. ER Model is used to model the logical view of the system from data perspective which consists of these components: Entity, Entity Type, Entity Set. An Entity may be an object with a physical existence – a particular person, car, house, or employee – or it may be an object with a conceptual existence – a company, a job, or a university course. An Entity is an object of Entity Type and set of all entities is called as entity set. e.g.; E1 is an entity having Entity Type Student and set of all students is called Entity Set. ER MODEL

5. Relational data model The relational model used the basic concept of a relation or table. The columns or fields in the table identify the attributes such as name, age , etc A tuple or row contains all the data of a single instance of the table In the relational model, every tuple must have a unique identification or key based on the data. Relational model also includes concepts such as foreign keys, which are primary keys in one relation that re kept in another relation to allow for the joining of data. Slide 1- 5

6. Relational data model Relational Model was proposed by E.F. Codd to model data in the form of relations or tables. After designing the conceptual model of Database using ER diagram, we need to convert the conceptual model in the relational model which can be implemented using any RDMBS languages like Oracle SQL, MySQL etc.

7. Relational data model Slide 1- 7

8. Database Designing E-R Model Constructs Entity instance - person, place, object, event, concept (often corresponds to a row in a table) Attribute - property or characteristic of an entity type (often corresponds to a field in a table) Relationship instance – link between entities (corresponds to primary key-foreign key equivalencies in related tables) Relationship type – category of relationship…link between entity types

9. Sample E-R Diagram

10. Basic E-R Notation Entity symbols Relationship symbols Attribute symbols A special entity that is also a relationship

11. Basic E-R Notation

12. E-R Model Constructs: Entity Type Entity - person, place, object, event, concept

13. Example of a weak entity

14. E-R Model Constructs: Attributes Attribute: a property or characteristic of an entity type that is of interest to the organization Simple versus Composite Attribute a simple attribute cannot be broken down into smaller components, while a composite attribute can be broken down into component parts Single-Valued versus Multivalued Attribute Stored versus Derived Attributes A stored attribute is one whose values are stored in the database A derived attribute is one whose whose values can be calculated from related stored attributes

15. A composite attribute An attribute broken into component parts

16. Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed) Multivalued: an employee can have more than one skill Derived from date employed and current date

17. Attribute that is both multivalued and composite This is an example of time-stamping

18. E-R Model Constructs: Identifier or Key Identifier or Key an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type Simple Key versus Composite Key Candidate Key - an attribute that could be a key Criteria for Selecting Identifiers Will not change in value Will not be null No intelligent identifiers (containing e.g. locations or people that might change) Substitute new, simple keys for long, composite keys

19. Simple key attribute The key is underlined

20. Composite key attribute The key is composed of two subparts

21. Relationships Relationship type ( is a meaningful association between entity types; is modeled as the diamond and lines between entity types; can have attributes) vs. Instance Multiple Relationships more than one type of relationship between entities Degree of a relationship - number of entity types that participate in it Unary (or Recursive) Relationship Binary Relationship Ternary Relationship Cardinality of Relationships Many-to-Many and Associative Entities (combination of relationship and entity) All relationships involved are “many” Result has independent meaning One or more non-key attributes

22. Relationship type (Completes)

23. Degree of relationships One entity related to another of the same entity type Entities of two different types related to each other Entities of three different types related to each other

24. Cardinality of Relationships One – to – One Each entity in the relationship will have exactly one related entity One – to – Many An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity Many – to – Many Entities on both sides of the relationship can have many related entities on the other side

25. Cardinality

26. Unary relationships

27. Binary relationships

28. Ternary relationships Note: a relationship can have attributes of its own

29. Strong and weak entities Strong entity Weak entity Identifying relationship

30. Slide 1- 31

31. SOME MCQS …