1 / 84

The Entity Relationship Model

The Entity Relationship Model. The Entity Relationship Model. Database design The ER model Entity sets Relationship sets Weak entity sets Subclasses Aggregation ER design issues. Goal of Database Design. Produce a set of tables that can store the data required by the enterprise

bart
Download Presentation

The Entity Relationship Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related