ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

database system and database concepts slides

•
•
Mozamel Jawad
Mozamel Jawad

database system and database concepts slides

1 of 23
Download to read offline
Outline Database Design using E-R Model 1.Converting Entities into Relations. 2.Converting Composite Attributes. 3.Converting Multi Valued Attributes. 4.Converting Weak Entities. 5.Converting Binary Relationship. 6.Converting Unary Relationships. 7.Converting ternary Relationships. 8.Converting Supertype/Subtype Relationships. 1 By Habibi
Database Design using E-R Model ï‚— E-R Model represents different things as Entities. ï‚— The connection among different entities are represented by Relationships. ï‚— These entities and relationships can be transformed into Relational Model. ï‚— This model can be used to design the database using relational model. 2 By Habibi
Converting Entities into Relations ï‚— The process of converting entities into relation is very simple. ï‚— In this process the name of entity becomes the name of Relation and attributes of entity becomes the fields of relation. ï‚— The following are some vocabulary that are commonly used. 3 By Habibi E-R Model Relational Model Database Traditional Programmer Entity Relation Table File Entity Instance Tuple Row Record Attribute Attribute Column Field Identifier Key Key Key/Link Vocabulary
Cont.…  Following example explains the process of converting an entity into relation. Employee Address DOB Name Emp_ID Emp_ID Name Address DOB Employee Relation 4 By Habibi
Converting Composite Attributes ï‚— If an entity contains composite attribute, each part of the attribute is represented by a separate field in the relation. ï‚— The following E-R model contains a composite attribute Address, different parts of this attributes are used as fields in the relation. Employee Emp_ID DOB Street City Country Address Name Emp_ID Name DOB Country City Street Employee Relation 5 By Habibi
Converting Multi Valued Attributes  The attribute is represented in a separate relation if an entity contains multi valued attribute.  The following E-R model contains a multi valued attribute Skill, this attribute is represented in a second relation. Emp_ID Name DOB Country City Street Employee Name Emp_ID Skill Address Emp_ID Skill • The second Relation Skill is using Emp_ID and Skill as composite key. • It may contain many rows. • Each row will contain a single skill of the employee. 6 By Habibi
Converting Weak Entities ï‚— A weak entity does not exist independently. It depends on the existence of another entity known as identifying owner. ï‚— When entities are converted into relations, first of all a relation for the identifying owner is created. ï‚— A separate relation is created for each weak entity and attributes of weak entity become the fields of the relation. ï‚— The primary key of identifying relation is used as foreign key in the weak entity relation. 7 By Habibi
Cont.… ERD: PersonID Name ChildName PersonID DOB Person and Child Relation Person Child has PersonID Name childName DOB 8 By Habibi
Converting Binary Relationship 1.Binary: One to Many Relationship:  One to many relationship of E-R Model is represented in relations by performing the following two steps: 1. Create a relation for each of the two types participating in relationship. 2. Include the primary key of the entity on one side of the relationship as foreign key in the relation that is on the many side of the relationship. 9 By Habibi
Cont.… ERD: Customer Order places CustID Name orderdate OrderID CustID Name OrderID Orederdate CustID Relations in binary one to many relationship 10 By Habibi
2.Binary: Many to Many Relationship:  Many to many relationship of E-R model is represented in relations by performing the following steps.  Create two relations A and B for each of the two entity types participating in the relationship.  Create another relation C that contains primary keys of relations A and B as foreign key.  These attributes become the primary of the relation C. 11 By Habibi
Cont.… ERD: StdID Name StdID SubID SubID Description Relations in binary Many to Many relationship Student Stu-Sub Subject Student Subject Studies StdID Name Description SubID 12 By Habibi
3. Binary: One to One Relationship:  One to One Relationship of E-R Model is represented in relations by performing the following two steps: 1. Create a relation for each of the two entity types participating in the relationship. 2. Include the primary key of the relation as a foreign key in the second relation. 13 By Habibi
Cont.… ERD: ChID Name Email DepID DepName ChID Chairman Departments Controls chID Name Email DepName DepID Relations in Binary One to One Relationships 14 By Habibi
Unary Relationships  Unary relationship exist between the instances of same entity types.  It is also known as recursive relationship.  Unary One to Many relationship of E-R Model is represented in relations by performing the following two steps: 1-Create a relation to represent the entity type. 2-Add another field as foreign key in the same relation that references the primary key of the relation.  The foreign key must have the same domain as the primary key. 15 By Habibi
Cont.… ERD: EmpID Name Address ManagerID Employee Manages Name Address EmpID Relation in Unary one to many relationship 16 By Habibi
Ternary Relationships  Ternary relationship exist between the instances of three entity types.  Ternary relationship of E-R model is represented in relations by performing the following two steps. 1. Create relation for each entity type participating in the relationship. 2. Create an associative relation to present the link between three entities.  The primary key of associative relation consist of primary keys of three entities in the relationship.  The attributes of associative entity type become attributes of the new relation. 17 By Habibi
Cont.… ERD: Patient Doctor Treatment PatientID Name Result Address Date treatmentID Details DocID Name Patient treatmen t 18 By Habibi
Cont.… Relations in Ternary Relationship: PatientID Name Address DocID Name PatientID DocID TreatmentID Date Result TreatmentID Details Patient Doctor Patient Treatment Treatment 19 By Habibi
Supertype/ Subtype Relationships  Supertype/ Subtype relationship of E-R Model is represented in relations by performing the following steps:  Create a separate relation for Supertype and for each of subtypes.  The relation for Supertype consist of the attributes which are common in all members, assign a primary key to the Supertype relation.  The relation for each subtype consist of the attributes, which are unique to that particular subtype, the primary key of the subtype will be similar to the primary key of the Supertype. 20 By Habibi
Cont.… ERD: Person Employee Student PerID Salary EmpID Class Roll No Email Name 21 By Habibi
Cont.…  Relations in Supertype/Subtype Relationship: PerID Name Email StudentID Roll No Class EmpID Salary Person Student Employee 22 By Habibi
Any Questions & Suggestions 23 By Habibi

More Related Content

database system and database concepts slides

  • 1. Outline Database Design using E-R Model 1.Converting Entities into Relations. 2.Converting Composite Attributes. 3.Converting Multi Valued Attributes. 4.Converting Weak Entities. 5.Converting Binary Relationship. 6.Converting Unary Relationships. 7.Converting ternary Relationships. 8.Converting Supertype/Subtype Relationships. 1 By Habibi
  • 2. Database Design using E-R Model ï‚— E-R Model represents different things as Entities. ï‚— The connection among different entities are represented by Relationships. ï‚— These entities and relationships can be transformed into Relational Model. ï‚— This model can be used to design the database using relational model. 2 By Habibi
  • 3. Converting Entities into Relations ï‚— The process of converting entities into relation is very simple. ï‚— In this process the name of entity becomes the name of Relation and attributes of entity becomes the fields of relation. ï‚— The following are some vocabulary that are commonly used. 3 By Habibi E-R Model Relational Model Database Traditional Programmer Entity Relation Table File Entity Instance Tuple Row Record Attribute Attribute Column Field Identifier Key Key Key/Link Vocabulary
  • 4. Cont.…  Following example explains the process of converting an entity into relation. Employee Address DOB Name Emp_ID Emp_ID Name Address DOB Employee Relation 4 By Habibi
  • 5. Converting Composite Attributes ï‚— If an entity contains composite attribute, each part of the attribute is represented by a separate field in the relation. ï‚— The following E-R model contains a composite attribute Address, different parts of this attributes are used as fields in the relation. Employee Emp_ID DOB Street City Country Address Name Emp_ID Name DOB Country City Street Employee Relation 5 By Habibi
  • 6. Converting Multi Valued Attributes ï‚— The attribute is represented in a separate relation if an entity contains multi valued attribute. ï‚— The following E-R model contains a multi valued attribute Skill, this attribute is represented in a second relation. Emp_ID Name DOB Country City Street Employee Name Emp_ID Skill Address Emp_ID Skill • The second Relation Skill is using Emp_ID and Skill as composite key. • It may contain many rows. • Each row will contain a single skill of the employee. 6 By Habibi
  • 7. Converting Weak Entities ï‚— A weak entity does not exist independently. It depends on the existence of another entity known as identifying owner. ï‚— When entities are converted into relations, first of all a relation for the identifying owner is created. ï‚— A separate relation is created for each weak entity and attributes of weak entity become the fields of the relation. ï‚— The primary key of identifying relation is used as foreign key in the weak entity relation. 7 By Habibi
  • 8. Cont.… ERD: PersonID Name ChildName PersonID DOB Person and Child Relation Person Child has PersonID Name childName DOB 8 By Habibi
  • 9. Converting Binary Relationship 1.Binary: One to Many Relationship:  One to many relationship of E-R Model is represented in relations by performing the following two steps: 1. Create a relation for each of the two types participating in relationship. 2. Include the primary key of the entity on one side of the relationship as foreign key in the relation that is on the many side of the relationship. 9 By Habibi
  • 10. Cont.… ERD: Customer Order places CustID Name orderdate OrderID CustID Name OrderID Orederdate CustID Relations in binary one to many relationship 10 By Habibi
  • 11. 2.Binary: Many to Many Relationship:  Many to many relationship of E-R model is represented in relations by performing the following steps.  Create two relations A and B for each of the two entity types participating in the relationship.  Create another relation C that contains primary keys of relations A and B as foreign key.  These attributes become the primary of the relation C. 11 By Habibi
  • 12. Cont.… ERD: StdID Name StdID SubID SubID Description Relations in binary Many to Many relationship Student Stu-Sub Subject Student Subject Studies StdID Name Description SubID 12 By Habibi
  • 13. 3. Binary: One to One Relationship:  One to One Relationship of E-R Model is represented in relations by performing the following two steps: 1. Create a relation for each of the two entity types participating in the relationship. 2. Include the primary key of the relation as a foreign key in the second relation. 13 By Habibi
  • 14. Cont.… ERD: ChID Name Email DepID DepName ChID Chairman Departments Controls chID Name Email DepName DepID Relations in Binary One to One Relationships 14 By Habibi
  • 15. Unary Relationships  Unary relationship exist between the instances of same entity types.  It is also known as recursive relationship.  Unary One to Many relationship of E-R Model is represented in relations by performing the following two steps: 1-Create a relation to represent the entity type. 2-Add another field as foreign key in the same relation that references the primary key of the relation.  The foreign key must have the same domain as the primary key. 15 By Habibi
  • 16. Cont.… ERD: EmpID Name Address ManagerID Employee Manages Name Address EmpID Relation in Unary one to many relationship 16 By Habibi
  • 17. Ternary Relationships ï‚— Ternary relationship exist between the instances of three entity types. ï‚— Ternary relationship of E-R model is represented in relations by performing the following two steps. 1. Create relation for each entity type participating in the relationship. 2. Create an associative relation to present the link between three entities.  The primary key of associative relation consist of primary keys of three entities in the relationship.  The attributes of associative entity type become attributes of the new relation. 17 By Habibi
  • 19. Cont.… Relations in Ternary Relationship: PatientID Name Address DocID Name PatientID DocID TreatmentID Date Result TreatmentID Details Patient Doctor Patient Treatment Treatment 19 By Habibi
  • 20. Supertype/ Subtype Relationships ï‚— Supertype/ Subtype relationship of E-R Model is represented in relations by performing the following steps:  Create a separate relation for Supertype and for each of subtypes.  The relation for Supertype consist of the attributes which are common in all members, assign a primary key to the Supertype relation.  The relation for each subtype consist of the attributes, which are unique to that particular subtype, the primary key of the subtype will be similar to the primary key of the Supertype. 20 By Habibi
  • 22. Cont.…  Relations in Supertype/Subtype Relationship: PerID Name Email StudentID Roll No Class EmpID Salary Person Student Employee 22 By Habibi
  • 23. Any Questions & Suggestions 23 By Habibi