Er diagrams presentation

Er diagrams presentation

• 1.
  ER Diagrams
• 2.
  To be discussed:- Entity  Attribute  Types of Attribute  Relationship  ER Diagram Representation  Generalization  Specialization  Inheritance  Codd’s 12 Rule  Relational Data Model  ER Model to Relational Model
• 3.
  Entity  An entitycan be a real-world object, either animate or inanimate, that can be easily identifiable. Example : School Database  Teachers  Students  Classes  Courses offered Entity set  An entity set is a collection of similar types of entities. An entity set may contain entities with attribute sharing similar values.
• 4.
  Attributes Entities are representedby means of their properties, called attributes. All attributes have values. Example : Student :- Name, Class, Age Types of Attributes  Simple attribute − Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number is an atomic value of 10 digits.  Composite attribute − example, a student's complete name may have first_name and last_name.
• 5.
  Types of AttributesContinue…..  Derived attribute − example, average_salary in a department should not be saved directly in the database, instead it can be derived.  For another example, age can be derived from data_of_birth.  Single-value attribute − Single-value attributes contain single value. example −: Social_Security_Number.  Multi-value attribute − Multi-value attributes may contain more than one values.  Example: a person can have more than one phone number, email_address, etc.
• 6.
  Relationship  The associationamong entities is called relationship.  Ex: Employee works_at department  Ex: Student enrolls in a course  Relationship Set A set of relationships of similar type is called a relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes.
• 7.
  Mapping cardinalities  Cardinalitydefines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set.  One to one One to many Many to one Many to many
• 8.
  One to One Entityset A Entity set B
• 9.
  One to many Entityset A Entity set B
• 10.
  Many to one Entityset A Entity set B
• 11.
  Many to many Entityset A Entity set B
• 12.
  ER Diagram Representation Entity  Attributes Teacher Student Classes Studen t Nam e Roll Birth_Dat e
• 13.
  ER Diagram RepresentationContinue…  Composite Studen t Nam e Roll Birth_Dat e Last First
• 14.
  ER Diagram RepresentationContinue…  Multivalued Studen t Nam e Roll Birth_Dat e Last First Phone_No
• 15.
  ER Diagram RepresentationContinue…  Derived Studen t Nam e Roll Birth_Dat e Last First Phone_No Age
• 16.
  ER Diagram RepresentationContinue…  Relationship  Binary Relationship and Cardinality When two entities participate in a relationship then it is called Binary Relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.
• 17.
  ER Diagram RepresentationContinue…  One to One (1:1) 1 1 Example : Person – Passport Student – Roll-No Relationshi p EntityEntity
• 18.
  ER Diagram RepresentationContinue…  One to Many (1:N) 1 N Example : Mother- Children Student - Address Relationshi p EntityEntity
• 19.
  ER Diagram RepresentationContinue…  Many to One (N:1) N 1 Example : Students - Teacher Relationshi p EntityEntity
• 20.
  ER Diagram RepresentationContinue…  Many to One (M:N) M N Example : - Books - Authors Relationshi p EntityEntity
• 21.
  ER Diagram RepresentationContinue…  Participation Constraint Total participation Partial participation Relationshi p EntityEntity
• 22.
   The ERModel has the power of expressing database entities in a conceptual hierarchical manner. As the hierarchy goes up, it generalizes the view of entities, and as we go deep in the hierarchy, it gives us the detail of every entity included. Going up in this structure is called Generalization Reverse is called Specialization Generalization and Specialization
• 23.
  Generalization Pigeon Birds Peacoc kSparrow
• 24.
  Specialization Person Teache r Student IS A
• 25.
  Inheritance Person Nam e Gende r Birth_Dat e Teache r Student EMP_IDRoll IS A
• 26.
  Relational Data Model Relational data model is the primary data model, which is used widely around the world for data storage and processing.  Concepts  Touple  Relation Instance  Relation Schema  Relation key  Attribute domain
• 27.
  Key Constraints  Keyconstraints Primary Key Foreign Key Candidate Key Alternate Key
• 28.
  Key Constraints …. Key constraints force that −  in a relation with a key attribute, no two tuples can have identical values for key attributes.  a key attribute can not have NULL values.  Domain constraints  Every attribute is bound to have a specific range of values.  Example : Age can not be negative  Referential integrity constraints
• 29.
  Key Constraints …. Referential integrity constraints Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational Integrity Constraints. I If a relation refers to a key attribute of a different or same relation, then that key element must exist.
• 30.
  ER model toRelational Model  ER model  Entity  Attributes  Mapping Entity  Create table for each entity.  Entity's attributes should become fields of tables with their respective data types.  Declare primary key.
• 31.
  ER model toRelational Model  Mapping Relationship  Create table for a relationship.  Add the primary keys of all participating Entities as fields of table with their respective data types.  If relationship has any attribute, add each attribute as field of table.  Declare a primary key composing all the primary keys of participating entities.  Declare all foreign key constraints.
• 32.
  ER model toRelational Model  Mapping Weak entity set  Create table for weak entity set.  Add all its attributes to table as field.  Add the primary key of identifying entity set.  Declare all foreign key constraints.