Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

ER Relational Model

Loading...

Published in: Big Data & Hadoop
7,151 Views

It gives an insight into ER diagram and its details

Varsha D / Kolkata

13 years of teaching experience

Qualification: student

Teaches: Chemistry, IT & Computer Subjects, Physics, Biology, Computer Science, IT

Contact this Tutor
  1. Relational and ER Models
  2. Relational Model What is Relational Model?
  3. Objectives Relational Model: Structures, basics. Attributes, simple composite, single valued and multivalued attributes. Domains, Atomicity, Tuples. Instance and Schema. Keys, Superkey, Candidate key, Primary key, Foreign key, Unique key. ER Model: Entity, Relationship and Entity sets. Mapping Cardinalities. ER Diagram , Notation Guide. Specimen ER Diagram. Cardinality in ER Diagram. Weak Entity sets. Generalization and Specialization
  4. What is Relational Model? The relational model is today the primary data model for commercial data-processing applications. It has attained its primary position because of its simplicity, which eases the job of the programmer, as compared to earlier data models such as the network model or the hierarchical model. Is based on a collection of tables. Users of which can create , insert and modify tables, There are several languages for database programming. SQL, Oracle, etc.
  5. StruCture Of a Relational Model Consists of a collection of tables, each of which is assigned a unique name. These tables can be called relations. A row in a table represents a relationship among a set of values. Correspondence between the concept of table and the mathematical concept of relations. The following is one such example of a relation Uni.R011 08120000 08123455 08125678 Stud.Name Number.Obt Year Shankar Bill Masate 98 76 78 1st 2nd 2nd
  6. A few Basic Terms in A Relational Model Attributes: serve as names for the columns of the relation. Usually, an attribute describes the meaning of entries in the column as shown below. Attribute Uni.R011 08120000 08123455 08125678 Stud.Name Shankar Bill Masate Number.Obt 98 76 78 Year 1st 2nd 2nd
  7. Types of Attributes Simple attributes: Attributes which are not divided into subparts. Such as, an attribute account balance that holds only the current balance. Composite attributes: Attributes that are divided into subparts. Such as a attribute name that has subparts first name, and last name. Single valued attributes: The attributes that are supposed to have one single value only. Such as an attribute uni,roll is supposed to have ony one university roll per student. Multi valued attributes: An attribute that can have more than one values. such as,an attribute phone number, that can have more than one phone number.
  8. A few Basic Terms in A Relational Model Domains: For each attribute,its domain is the set of permitted values. e.g;ln the last relation,the domain of the attribute stud.name is all possible names of the students in the university. And not their height or weights etc. Mathematicians define a relation to be a subset of a Cartesian product of a list of domains.so, a table of n attributes must be a subset of X Dn-l X Dn
  9. A few Basic Terms in A Relational Model Atomicity:An attribute is said to be atomic if its domain is an indivisible set of permissible values. say,the domain of the set of integers are atomic. But,sets of integers are not. Tuple: The rows of a relation,has one component for each attribute of a relation. A tuple variable is a variable that stands for a tuple Tuple Uni.R011 08120000 08123455 08125678 Stud.Name Shankar Bill Masate Number.Obt 98 76 78 Year 1st 2nd 2nd
  10. Instance and Schema Schema: Schema is the overall Design of the Database Schema of a relation consists of attribute definitions name type / domain integrity constraints For example Student Schema = (uni. roll , number. obt, stud. name, year)
  11. Instance and Schema Instance:lnstance is the information stored in the Database at a particular moment The current values (relation instance) of a relation are specified by a table . An element t of r is a tuple, represented by a row in a table. Order of tuples is irrelevant (tuples may be stored in an arbitrary order) Tuples Uni.R011 08120000 08123455 08125678 Attribuutes Number.Obt Year Stud.Name Shankar Bill Masate 98 76 78 1st 2nd 2nd
  12. Let K c R Keys superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) by possible r " we mean a relation r that could exist in the enterprise deling. we are mo Example: customer_name, customer_street} and customer name are both superkeys of Customer, if no two customers can possibly have the same name In real life, an attribute such as customer id would be used instead of customer_name to uniquely identify customers, but we omit it to keep our examples small, and instead assume customer names are unique.
  13. Keys (Cont.) if K is minimal candidate key {customer _ name} is a candidate key for Customer, Example : since it is a superkey and no subset of it is a superkey. Primary key: a candidate key chosen as the principal means of identifying tuples within a relation Should choose an attribute whose value never, or very rarely, changes. E.g. email address is unique, but may change
  14. Keys (Cont.) Foreign Key: A relation schema may have an attribute that corresponds to the primary key of another relation. The attribute is called a foreign key E.g. customer_name and account_number attributes of depositor are foreign keys to customer and account respectively. Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation. The Unique key concept uniquely identifies each record Unique Key: in a database table. Similar to the Primary key,it provides guarantee for uniqueness for a column or set of columns. There can be many Unique key constraints per table, but only one Primary key constraint per table. And also permits null values.
  15. Schema branch branch—name branch—city assets Diagram account account—number branch—name balance loan loan—number branch—name amount depositor customer—name account—number customer customer—name customer—street customer—city borrower cus tomer—name loan—number
  16. Entity Relationship Model The entity-relationship (E-R) data model perceives the real world as consisting of basic objects, called entities, and relationships among these objects. An entity is a "thing" or "object" in the real world that is distinguishable from all other objects. For example, each person in an enterprise is an entity An entity set is a set of entities of the same type that share the same properties, or attributes. For example, Student result is an entity set that is a collection of student entities having the same set of attributes.
  17. Mapping Cardinalities Express the number of entities to which another entity can be associated via a relationship set. For a binary relationship set R between entity sets A and B the following can be the mapping Cardinalities. One to one. An entity in A is associated with at most one entity in B, and an B is associated with at most one entity in A One to many. An entity in A is associated with any number (zero or more) of An entity in B, can be associated with at most one entity in A. Many to one. An entity in A is associated with at most one entity in B. An entity in B, can be associated with any number (zero or more) of entities in A. Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A.
  18. Entity-Relationship Diagram E-R diagram can express the overall logical structure of a database graphically. E-R diagrams are simple and clear qualities that may well account in large part for the widespread use of the E-R model. Such a diagram consists of the following major components. (in the next slide)
  19. Entity-Relationship Diagram Rectangles, which represent entity sets Ellipses, which represent attributes Diamonds, which represent relationship sets Lines, which link attributes to entity sets and entity sets to relationship sets Double ellipses, which represent multivalued attributes Dashed ellipses, which denote derived attributes Double lines, which indicate total participation of an entity in a relationship set Double rectangles, which represent weak entity sets
  20. Notation Guide O ENTITY TYPE WEAK ENTITY TYPE O RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE
  21. Notation Guide ATTRIBUTE O KEY ATTRIBUTE MULTIVALUED ATTRIBUTE O DERIVED ATTRIBUTE COMPOSITE ATTRIBUTE
  22. ER Diagram Basics ET Jilt)/ qty Store Keeps sname Locations Relationship manager Attributes pname descrip
  23. Entity Set Weak Entity Set Relationship Set Identifying Relationship Set for VVeak Entity Set Primary Key Attribute Multivalued Attribute Derived Attribute Total Participation of Entity Set in Relationship Discriminating Attribute of YVeak Entit Set
  24. name E Many to Many Relationship One to One Relationship Role Indicator Total Generalization E disjoint Many to One Relationship Cardinality Limits ISA (Specialization or Generalization) Disjoint Generalization
  25. Entity set E with attributes Al, A2, A3 and primary key Al Many to Many Relationship One to One Relationship Many to One Relationship 1 1 1
  26. A Specimen ER Diagram
  27. Cardinality In ER Diagram A directed line from the relationship set borrower to the entity set loan specifies that borrower is either a one-to-one or many-to-one relationship set, from customer to loan. An undirected line from the relationship set borrower to the entity set loan specifies that borrower is either a many-to-many or one-to-many relationship set from customer to loan. A double line from any entity set to another denotes total participation,ie each record from the entity set is related to at least one entity set in the other
  28. Cardinality In customer-name customer-id ER Diagram customer-street customer-city customer customer-name customer-id customer-str customer-city customer customer-name customer-id customer-stre customer-city customer loan-number borrower (a) loan-number borrower (b) loan-number borrower (c) amount loan amount loan amount loan a. One to many. b.many to one. c.one to one.
  29. Cardinality in ER Dlagram customer-name customer-id customer-street loan-number customer-city amount 1..1 borrower custonter loan The digit on the left of any line from a entity set to a relationship denotes the minimum ,and,the right side denotes the maximum participation of that set in the relation. For example,the 0... * in the line from customer to borrower indicates that a customer can have a minimum of zero and a max of unlimited number of loans.
  30. Weak Entity sets An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. Weak entities can be mapped to the relational model by: Map each weak entity E that depends on a strong entity F to a new relation R Relation R contains all the attributes in E and the primary key of F The primary key for R is the primary key of E and the primary key of For example,a set containing 'issue number' ,'issue date' and 'book name since there is no primary key in this set a primary key from another table say stud.id of the stud borrowing the book would serve as a part of its primary key,That is stud.id and issue no would serve as its primary key.
  31. Weak Entity Sets For example,a set containing 'issue number' ,'issue date' and 'book name' since there is no primary key in this set a primary key from another table say stud.id of the stud borrowing the book would serve as a part of its primary key, That is stud.id and issue no would serve as its primary key. Lname Fnam e Name ELIPLOYEE DEPENDENTS-OF Name Relationship DEPENDENT
  32. Specialization Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set. These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. Depicted by a triangle component labeled ISA (E.g. customer IS a" person) a lower-level entity set inherits Attribute inheritance all the attributes and relationship participation of the higher- level entity set to which it is linked.
  33. Specialization name Exam street person ISA city salary officer office-number employee ISA teller station-number credit-rating customer secretary hours-worked hours-worked
  34. Generalization A bottom-up design process combine a number of entity sets that share the same features into a higher-level entity set. Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way. The terms specialization and generalization are used interchangeably.
  35. Specialization and Generalization (Contd.) Can have multiple specializations of an entity set based on different features. E.g. permanent-employee vs. temporary-employee, in addition to o icer vs. secretary vs. teller Each particular employee would be a member of one of permanent-employee or temporary-employee, and also a member of one of officer, secretary, or teller The ISA relationship also referred to as superclass - subclass relationship
  36. Design Constraints on a Specialization/GeneraIization Constraint on which entities can be members of a given lower-level entity set. condition-defined E.g. all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person. user-defined Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. Disjoint an entity can belong to only one lower-level entity set Noted in E-R diagram by writing disjoint next to the ISA triangle Overlapping an entity can belong to more than one lower-level entity set
  37. Design Constraints on a Specialization/GeneraIization (Contd.) Completeness constraint specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization. total . an entity must belong to one of the lower-level entity sets partial. an entity need not belong to one of the lower-level entity sets

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related PPTs

Upload PPTs

If you have your own PowerPoint Presentations which you think can benefit others, please upload on LearnPick. For each approved PPT you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > PowerPoint Slides > ER Relational Model