680 likes | 773 Views
Temple University – CIS Dept. CIS616– Principles of Database Systems. V. Megalooikonomou E-R Diagrams (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU). Overview. Concepts Entities Relationships Attributes Mapping constraints: cardinalities Keys
E N D
Temple University – CIS Dept.CIS616– Principles of Database Systems V. Megalooikonomou E-R Diagrams (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)
Overview • Concepts • Entities • Relationships • Attributes • Mapping constraints: cardinalities • Keys • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables
The Tools Entities (‘entity sets’) N M Relationships (‘rel. sets’) and mapping constraints P attributes
Entity Sets • A database can be modeled as: • a collection of entities + • relationships among entities • An entity is an object that exists and is distinguishable from other objects • Example: specific person, company, event • Entities have attributes • Example: people have names and addresses • An entity set is a set of entities of the same type that share the same properties • Example: set of all persons, companies, holidays
Attributes • An entity is represented by a set of attributes, that is, descriptive properties that all members of an entity set have • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite (e.g., name (first,middle,last)) attributes • Single-valued and multi-valued attributes (e.g., multi-valued attribute: phone-numbers, dependents, etc • Derived attributes • Can be computed from other attributes (e.g., age, given date of birth) E.g., customer = (customer-id, customer-name, customer-street, customer-city) loan = (loan-number, amount)
Relationship Sets • A relationship is an association among several entities Example:HayesdepositorA-102customer entity relationship set account entity • A relationship set is a mathematical relation among n 2 entities, each taken from entity sets {(e1, e2, … en) | e1 E1, e2 E2, …, en En}where (e1, e2, …, en) is a relationship (entity sets E1, E2,…, En participate in the relationship) • Example: (Hayes, A-102) depositor
More examples … Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per course nouns -> entity sets verbs -> relationships
... name STUDENT ssn INSTRUCTOR issn
... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn what about different sections of course (with different instructors)?
ssn STUDENT c-id s-id SECTION COURSE but: s-id is not unique... issn INSTRUCTOR
ssn STUDENT N c-id takes M s-id SECTION COURSE issn INSTRUCTOR
STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR
Mapping Constraints: Cardinalities • 1 to 1 (example?) • 1 to N • N to M
Mapping Cardinalities One to one One to many Note: Some elements in A and B may not be mapped to any elements in the other set
Mapping Cardinalities Many to one Many to many Note: Some elements in A and B may not be mapped to any elements in the other set
STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR Example
E-R Diagrams • Rectangles represent entity sets • Diamonds represent relationship sets • Lines link attributes to entity sets and entity sets to relationship sets • Ellipses represent attributes • Double ellipses represent multivalued attributes • Dashed ellipses denote derived attributes • Underline indicates primary key attributes (will study later)
More details • ‘weak’ entities: if they need to borrow a unique id from a ‘strong entity - DOUBLE box. • ‘c-id’ + ‘s-id’: unique id for SECTION • discriminator (e.g., ‘s-id’) c-id 1 N s-id has SECTION COURSE
More details • Entity sets of a relationship need not be distinct • self-relationships – example ?
manages 1 EMPLOYEE N More details • Entity sets of a relationship need not be distinct • self-relationships – example ? • Usually different “roles” are indicated by labeling the lines that connect diamonds to rectangles
Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • E.g.: One-to-one relationship: • A customer is associated with at most one loan via the relationship borrower • A loan is associated with at most one customer via borrower
More details • Binary relationships • 3-way and k-way relationships? N M EMPLOYEE TOOL uses P PROJECT
More details - attributes • superkey: a set of one or more attributes whose values uniquely determine each entity (e.g., (ssn, address) ) • candidate key: a minimal super key (e.g., ssn; employee#) • primary key: a cand. key, chosen by DBA • multivalued or set-valued attributes (e.g., ‘dependents’ for EMPLOYEE) • derived attributes (e.g., 15% tip)
Participation of an Entity Set in a Relationship Set More details: • Totalparticipation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g. participation of loan in borrower is total • every loan must have a customer associated to it via borrower • Partial participation: some entities may not participate in any relationship in the relationship set • E.g. participation of customer in borrower is partial
Alternative Notation for Cardinality Limits More details: • Cardinality limits (min and max) can also express participation constraints (min=1 implies total participation of an entity set to a relationship set, max=1 implies that an entity participates in at most one relationship, max=* implies no limit).
Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables
e.g., students: part time (#credit-hours) and full time (major) Extended ER features: Specialization name STUDENT ssn IS-A major FT-STUDENT PT-STUDENT #credits
Specialization • Top-down design process; 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) • Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked
Generalization • … opposite to specialization • 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 inversions of each other; they are represented in an E-R diagram in the same way; we use the terms interchangeably • could have many levels of an IS-A hierarchy • attribute inheritance
Specialization and generalization (Cont.) • Can have multiple specializations of an entity set based on different features • E.g. permanent-employee vs. temporary-employee, in addition to officer 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
Design Constraints on Specialization/Generalization • The 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 • The constraint on whether or not entities may belong to more than one lower-level entity sets 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
Design Constraints on a Specialization/Generalization (Cont.) • Completenessconstraint -- 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
treats a relationship as an ‘abstract’ entity allows relationships between relationships rarely used Aggregation
Binary vs. Non-Binary Relationships • Some relationships that appear to be non-binary may be better represented using binary relationships • E.g., a ternary relationship parents, relating a child to his/her father and mother, is best replaced by two binary relationships, father and mother • Using two binary relationships allows partial information (e.g., only mother being known) • But there are some relationships that are naturally non-binary • E.g., works-on
Converting Non-Binary Relationships to Binary Form • In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set • Replace R between entity sets A, B and Cby an entity set E, and three relationship sets: 1. RA, relating E and A 2.RB, relating E and B 3. RC, relating E and C • Create a special identifying attribute for E • Add any attributes of R to E • For each relationship (ai , bi , ci) in R, create 1. a new entity eiin the entity set E 2. add (ei , ai ) to RA 3. add (ei , bi) to RB 4. add (ei , ci ) to RC
E-R Design Decisions • Decisions regarding: • The use of an attribute or entity set to represent an object • Whether a real-world concept is best expressed by an entity set or a relationship set • Use of a ternary relationship versus a pair of binary ones • Use of a strong or weak entity set • Use of specialization/generalization (that contributes to modularity in the design) • Use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure
Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables
STUDENT N c-id grade takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR
Representing strong entities just list the attributes, and underline the primary key, eg. STUDENT(ssn, name, address)
Representing Multivalued attributes Eg., EMPLOYEE with many dependents: • introduce a new table, with (ssn, dependent-name)
Representing relationships • get primary keys of all involved entities • primary key - depends on cardinality • 1 to 1: use either key, e.g., EMPLOYEE( ssn, empno, name, ...) • 1 to N: use the key of the ‘N’ part, e.g., TEACHES( issn, c-id, s-id) • N to M: use both keys, e.g., TAKES( ssn, c-id, s-id, grade)
Representing relationships • 1 to N: no need for separate table - eg., SECTION( issn, room-num, c-id, s-id) instead of SECTION1(c-id, s-id, room-num) TEACHES(issn, c-id, s-id) • for rel. between strong and corresponding weak entity, no need for table, at all!
Representing Generalization/Specialization Two solutions: - one table for each or - no table for super-entity (pros and cons?)
Representing Generalization/Specialization E.g., STUDENT(ssn, name, address) PT-STUDENT( FT-STUDENT(
Representing Generalization/Specialization E.g., STUDENT(ssn, name, address) PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major
Representing Generalization/Specialization no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major