Entity-Relationship Modelling

1. Entity-Relationship Modelling • Entity Sets • Relationship Sets • Design Issues • Mapping Constraints • Keys • E-R Diagram A data model is a collection of tools for describing data, data relationships, data semantics, and consistency constraints. • An Entity-Relationship (E-R) Model is a data model.

2. Entity Sets • The entity-relationship (E-R) data model perceives the real world as a collection of entities and relationships. • ER model is good for describing a database in logical level. • An entity consists of a set of attributes. • Examples: entity people have attributes person-id (e.g. HKID nos.), names, and addresses. • An entity has a subset of attributes whose values uniquely identify the entity. • Example: people (identified by person-ids), loans (identified by loan-ids) • Question: Any example of using two or more attributes to identify an entity? • An entity set is a set of entities of the same type that share the same properties. • Example: The set of all persons who are customers at a given bank (can be defined as an entity set called customer) • Why do they belong to the same type? • ‘coz each of them has an account number at the given bank • Another example: The set of all employees of a bank (entity set: employee)

3. Entity Sets customer and loan customer-id customer- customer- customer- loan- amount name street city number entity set entity set

4. Attributes e.g. customer =(customer-id, customer-name, customer-street, customer-city) loan = (loan-number, amount) • An entity is represented by a set of attributes. These attributes are commonly shared by all members in an entity set. • Domain – the set of permitted values for each attribute. Any examples? • E.g. attribute loan-number might be the set of all strings of the form “L-n” where n is a positive integer. • Attribute types • Simple and composite attributes. • E.g. composite attribute name: first-name, middle-name, and last-name • Single-valued and multi-valued attributes • E.g. multivalued attribute: phone-numbers • Derived attributes • Can be computed from other attributes • E.g. age, given date of birth

5. Composite Attributes

6. Relationship Sets • A relationship is an association among two or more entities (it is a mathematical relation among n 2 entities) • A relationship set is a subset of U = {(e1, e2, … en) | e1  E1, e2  E2, …, en  En}where • E1, E2, …, En are entity sets. • (e1, e2, …, en) is a relationship • Question: What’s the cardinality of U? |E1|  |E2|  …  |En| Example:HayesdepositorA-102customer entity relationship set account entity • (Hayes, A-102) is a relationship in the relationship set depositor

7. Relationship Set borrower

8. Attributes of a Relationship • A relationship may also have attributes (called descriptive attributes). • E.g. the depositor relationship set between entity sets customer and account may have the attribute access-date

9. Degree of a Relationship Set • The number of entity sets that associate with in each relationship in a relationship set. • A relationship that involves two entity sets is binary (or degree two). • Most relationship sets of database system are binary in practice • Relationship sets may involve more than two entity sets. • Example: A bank employee may have different job at different branch • Ternary relationship set among employee, job and branch • Relationship set: works-on  employee  job  branch • Jones (employee) is a manager (job) at Perryridge (branch). • Jones (employee) is an auditor (job) at Downtown (branch). • Smith (employee) is a teller (job) at Downtown (branch).

10. Mapping Cardinalities • Express the number of entities to which another entity can be associated via a relationship set. • For a binary relationship set the mapping cardinality must be one of the following types: • One to one • One to many • Many to one • Many to many • Note: Some elements in A and B may not be mapped to any elements in the other set • Any example? One to many One to one • E.g. A customer may have no account in the bank but only has a loan record.

11. Mapping Cardinalities Many to one Many to many

12. Mapping Cardinalities affect ER Design • Refer to the relationship set depositor (which has a descriptive attribute access-date) on slide 8. • Can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer • I.e., the relationship from customer to account is one to many • Question: Can we do something similar for a relationship of many to one?