The Entity Relationship Model

1. The Entity Relationship Model

2. The Entity Relationship Model • Database design • The ER model • Entity sets • Relationship sets • Weak entity sets • Subclasses • Aggregation • ER design issues

3. Goal of Database Design • Produce a set of tables that can store the data required by the enterprise • Database tables represent • Entities, and • Relationships between entities • A student database might contain • The entities student and course and • The relationship student takes course

4. Database Design • Experienced designers may be able to directly determine the tables for small applications • Many database applications model large and complex enterprises • In many cases no one person understands the complete requirements • A DB designer should discuss requirements with the users • Translate the requirements into a high level design • Confirm with the users that the design is correct • Translate the high level design into a database schema

5. Database Design

6. Design Overview • Requirements Analysis • Conceptual Database Design • Logical Database Design • Schema Refinement • Physical Database Design • Security Design

7. Requirements Analysis • A database is intended to model a real world enterprise • What data are to be stored in the database? • Linguistic aside: data is plural, datum is singular • What applications are required to work with the database? • Which are the most frequent, and the most important operations?

8. Conceptual Database Design • Use the Entity Relationship (ER) model to develop a high level description of the data • Identify the entities and relationships in the enterprise • Identify what information about these entities and relationships is to be stored in the database • Identify the integrity constraints (or business rules) that apply to the entities and relationships • Check with the client that the ER model that has been developed is correct

9. Logical Database Design • Determine which data model should be used to implement the database • Determine which DBMS to use • In most cases this means deciding which existing DBMS product to purchase or license • Map, or translate, the conceptual schema to a database schema of the chosen model • We will specify the conceptual schema by drawing an Entity Relationship diagram

10. Design • The designer has to decide how to represent the stuff to be modeled in the database • Items to be represented in a database are referred to as entities • Connections between items are referred to as relationships • e.g. A customer (entity) buys (relationship) a product (entity) • There are two major problems to be avoided • Redundancy – information should not be repeated • Incompleteness – it should be possible to record all the desired information

11. The Entity-Relationship Model people uses stuff

12. The Entity-Relationship Model • The most common abstract data model • The ER model has similarities to other modeling languages like UML • The major components of the ER Model are: • Entities such as people, cars, accounts, things, … • Attributes that describe the entities, e.g. name, age, amount, date, … • Relationships that connect the entities, e.g. customer owns account, student takes course • Constraints which restrict relationships, e.g. an account must be owned by a customer

13. Entity-Relationship Diagrams • Entity-relationship diagrams show the structure of a database graphically • Simple symbols: rectangles, diamonds, ovals and lines represent the components of the ER model • They are straightforward and easy to explain to users • There are many variations of ER diagrams • So don't expect the symbols in every ER diagram you see to be exactly the same! • Some common variations are discussed at the end of this presentation

14. And Attributes Entities

15. Entities and Entity Sets • An entity is a distinguishable object • That is, it can be distinguished from other object • e.g. customer Buffy Summers, or car Kit • May be concrete or abstract • An entity set is a collection of entities of the same type • e.g. all of the customers • Entity sets need not be disjoint • That is, two entity sets can contain the same entity • e.g. Buffy Summers could be an entity in both the Customer and Employee entity sets

16. Attributes • An entity is described by a set of attributes • Most of the data stored in a database are attribute values • If there is no value for an attribute it is given the null value • Some attributes may have their values derived from other attributes • Each attribute has a domain • A set of all possible attribute values • Attributes may be considered as functions that map an entity set to a domain

17. birthday last income first ER Diagram Entity Sets Attributes Customer Entity Set

18. street city number address Composite Attributes • Composite attributes are divided into sub-parts • e.g. address is composed of city, street and number • They group related attributes together • Composite attributes should be replaced with their sub-parts

19. Multivalued Attributes • A multi-valued attribute is a set of values • All of the same type • e.g. phone numbers • Multi-valued attributes should be replaced by an entity set

20. e_count Derived Attributes • The value of a derived attribute can be derived from other values • Belonging to related attributes or entities • e.g. employee_count for a department • Calculated by counting the number of employees in the department • Derived attributes do not need to be stored in the database • They can be calculated when required

21. Keys • Differences between entities in an entity set are expressed in terms of their attributes • That is, you cannot have two different entities that have the same values for each of their attributes • As they would represent the same real-world entity (but see weak entity sets later) • In contrast to OOP where every object has a unique object ID independent from the values of its variables • A key is a set of attributes whose values uniquely identify an entity in an entity set

22. Types of Keys • Superkey • Any set of attributes whose values uniquely identify an entity in an entity set • Candidate key • A minimal superkey, that is a superkey with no extraneous attributes • A relation can have more than one candidate key • Primary key • The candidate key designated by the database designer to refer to tuples (rows) in the relation (table) • Primary key attributes are underlined in an ERD

23. birthday last sin first income Primary Key Customer *assuming (unrealistically) that there are no two people with the same first name, last name and birthday Candidate Key 1: {first, last, birthday}* Candidate Key 2: {sin} Primary Key: {sin}

24. Selecting a Primary Key • A primary key must be a candidate key • i.e. a minimal superkey • The primary key should be chosen so that its attributes never (or very rarely) change • Including an address as part of a primary key is therefore not recommended • SINs make good primary keys • It is sometimes useful to generate a unique primary key for entities

25. Relationships

26. Relationship Sets • A relationship is an association between two or more entities • A relationship set is a set of relationships of the same type • Relationship sets may have descriptive attributes • Such attributes cannot be part of a relationship set’s primary key • Useful for properties that can’t be associated with either of the participating entity sets

27. More Formally … • An n-ary relationship set R is an association between n entity sets E1 … En • R is a subset of {(e1, …, en) | e1 E1, …, en En} • Binary relationships are the most common • i.e. relationships between two entities • Which can be the same entity! • Ternary relationships are not unusual • The role of an entity is the function that it plays in a relationship

28. salary works_on Project Employee start name sin budget pname cost ER Diagram Relationship Sets Note the “verb” name Descriptive Attribute the start date is the date that an employee started working on a project, it must therefore be an attribute of the relationship

29. roles manages sin salary f_name l_name And Another Relationship Set Employee manager subordinate

30. one-to-one one-to-many many-to-one many-to-many Mapping Cardinalities • There are different types of binary relationships • In terms of the number of relationships that the participating entities may be involved in • Mapping cardinalities are specified in an ER diagram by directed lines (arrows)

31. Mapping Cardinalities • Assume entity sets A and B • One-to-one • An entity in A associates with at most one entity in B, an entity in B associates with at most one entity in A • One-to-many (A to B) • An entity in A associates with any number of entities in B, an entity in B associates with at most one entity in A • Many-to-many • An entity in A associates with any number of entities in B, an entity in B associates with any number of entities in A

32. ER Diagram Cardinalities • Mapping cardinalities are shown in a relationship set by directed and undirected lines • Directed lines (arrows) point from an entity set to the relationship set • In a relationship R between entity sets A and B, an arrow from Ato Rindicates that • Entities in Acan involved in only one relationship in R • i.e. be related to at most one entity in entity set B

33. salary works_in many-to-one name address budget bname sin Branch Employee Mapping Cardinalities Example • An employee can work in only one branch • A branch can have many employees working in it A branch can have many employees but an employee can work in only one branch employee branch many one

34. Relationship Set Primary Keys • The attributes of a relationship set are • The attributes that comprise the primary keys of the participating entity sets and • Any descriptive attributes of the relationship set • The primary key of a relationship depends on the key constraints in the relationship • Many-to-many – all the non-descriptive attributes of the relationship set • One-to-many – the primary key for the many entity • One-to-one – the primary key of either entity

35. salary works_in budget bname name sin address Relationship Set Key Example • The attributes of works_in are sin and bname • They are not shown as part of the relationship set in the ER diagram because they can be determined from the entities • The relationship set has no descriptive attributes • The primary key of works_in is sin • {sin, bname} is a superkey but not a candidate key • Each employee can only work in one branch, therefore can appear only once in works_in Branch Employee

36. Participation Constraints • Indicate that each entity in an entity set must be involved in at least one relationship • Participation is said to be either total (there is a constraint) or partial (no constraint) • If there is no participation constraint all the entities may still be involved in the relationship • Total participation is indicated by a double line from the relationship to the entity • Or a thick line

37. birthdate owns Account Customer accNum type income sin f_name l_name balance Participation Example • Each account must be owned by at least one customer

38. represents Lawyers • A law firm’s lawyers are assigned to represent clients interests Client Lawyer

39. attends Kids • Children attend pre-school, assume that • A pre-school must have children • Children can only attend one pre-school Pre-school Child

40. works_for Wage Slaves • Employees work for companies • An employee must be employed by someone (or they wouldn’t be an employee) • People often have more than one job Company Employee

41. is_CEO The Man • Chief Executive Officer (CEOs) of a company • Assume that being a CEO is a full time position and • That a company can only have one CEO • And that one woman or man must be in charge • So no cooperatives or workers' collectives Company Employee

42. holds Banks • Bank accounts and branches • A branch can have many accounts • An account must be held at a single branch Account Branch

43. contains Whose Bedroom? • Rooms in a house • This one should be easy … • But: how do we identify a room? • What are its attributes and its primary key? Room House

44. Non Binary Relationships • A relationship is does not have to be binary but can include any number of entity sets • Ternary relationships are not uncommon • Specifying the the mapping cardinalities of non-binary relationships can be complicated • A key constraint indicates that an entity can participate in only one relationship • Just like with binary relationships

45. birthday hours title Role works tid lname sin gpa project tname fname Team Student Ternary Relationships in this example there are no cardinality constraints, implying that a student can have any number of roles on the same team a student has a role working in a team (like team leader, or techie or coffeeboy)

46. birthday hours title Role works tid lname fname gpa project tname sin Team Student Ternary Relationships The key constaint identifies that a student can only work on one team and in one role Is it possible to represent one student working on many teams but with only one role on each team?

47. Extended ER Model

48. Other ER Model Features • Weak Entity Sets • Where the entities cannot be uniquely identified without information from a related entity set • Subclasses • Class hierarchies of entities • Aggregation • Used to model relationships that exist between entities and relationships

49. Weak Entity Sets • A weak entity cannot be identified by its own attributes alone • A member of a weak entity set is identified by combining its partial key with the primary key of another entity set • The other entity set is referred to as the owner entity set • Weak entity sets are permitted only when • The owner and weak entity set participate in a one-to-many identifying relationship set and • The weak entity set must have total participation in the identifying relationship

50. name number street city sqFeet Contains Identifies the weak entity set Weak Entity Set Example Identifies the partial key Room contains House