The EntityRelationship Model CSCD 34 Data Management Systems

The Entity-Relationship Model CSCD 34 - Data Management Systems. A. Vaisman 1

Overview of Database Design v Requirements Analysis: Understand what data will be stored in the database, and the operations it will be subject to. v Conceptual Design: (ER Model is used at this stage. ) § What are the entities and relationships in the enterprise? § What information about these entities and relationships should we store in the database? § What are the integrity constraints or business rules that hold? § A database `schema’ in the ER Model can be represented pictorially (ER diagrams). § Can map an ER diagram into a relational schema. CSCD 34 - Data Management Systems. A. Vaisman v Logical Design: Convert the conceptual database design 2

Overview of Database Design (cont. ) v Schema Refinement: (Normalization) Check relational schema for redundancies and anomalies. v Physical Database Design and Tuning: Consider typical workloads and further refinement of the database design (v. g. build indices). v Application and Security Design: Consider aspects of the application beyond data. Methodologies like UML often used for addressing the complete software development cycle. CSCD 34 - Data Management Systems. A. Vaisman 3

ER Model Basics ssn name lot Employees Entity: Real-world object distinguishable from other objects. An entity is described using a set of attributes. v Entity Set: A collection of entities of the same kind. E. g. , all employees. v § § § All entities in an entity set have the same set of attributes. Each entity set has a key(a set of attributes uniquely identifying an entity). Each attribute has a domain. CSCD 34 - Data Management Systems. A. Vaisman 4

name ER Model Basics (Contd. ) ssn lot Employees v v lot Employees since name ssn dname did Works_In budget Departments supervisor subordinate Reports_To Relationship: Association among two or more entities. E. g. , Peter works in Pharmacy department. Relationship Set: Collection of similar relationships. § An n-ary relationship set R relates n entity sets E 1. . . En; each relationship in R involves entities e 1 E 1, . . . , en En § Same entity set could participate in different relationship sets, or in different “roles” in same set. § Relationship sets can also have descriptive attributes (e. g. , the since attribute of Works_In). A relationship is uniquely identified by participating entities without reference to descriptive attributes. CSCD 34 - Data Management Systems. A. Vaisman 5

Key Constraints (a. k. a. Cardinality) name since dname Consider Works_In ssn lot did budget (in previous slide): An employee can Employees Manages Departments work in many departments; a dept can have many employees. v In contrast, each dept has at most one manager, according to the key 1 -to-1 1 -to Many-to-1 Many-to-Many constraint on Constraints are IMPORTANT because they must be ENFORCED Manages. when IMPLEMENTING the database v CSCD 34 - Data Management Systems. A. Vaisman 6

Key Constraints (ternary relationships) Location name Each employee can work at most in one department at a single location 12 -233 12 -354 12 -243 12 -299 ssn name lot Employees dname did works_In budget Departments D 10 • • D 12 D 13 Rome London Paris CSCD 34 - Data Management Systems. A. Vaisman 7

Participation Constraints v Does every department have a manager? § If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial). • Every Department MUST have at least an employee • Every employee MUST work at least in one department • There may exist employees managing no department since name ssn did lot Employees dname Manages budget Departments Works_In since CSCD 34 - Data Management Systems. A. Vaisman 8

Weak Entities v A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. § § § Owner entity set and weak entity set must participate in a one-tomany relationship set (one owner, many weak entities). Weak entity sets must have total participation in this identifying relationship set. transac# is a discriminator within a group of transactions in an ATM. address atm. ID since transac# amount type ATM CSCD 34 - Data Management Systems. A. Vaisman Transactions 9

name ISA (`is a’) Hierarchies ssn lot Employees v. As in C++, or other PLs, attributes are inherited. hourly_wages hours_worked ISA v. If we declare A ISA B, every A entity is also considered to be a B entity. Hourly_Emps v v contractid Contract_Emps Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? if so, specify => Hourly_Emps OVERLAPS Contract_Emps. Covering constraints: Does every Employees’ entity also have to be an Hourly_Emps or a Contract_Emps entity? . If so, write Hourly_Emps AND Contract_Emps COVER Employees. » Reasons for using ISA: To add descriptive attributes specific to a subclass. To identify entities that participate in a relationship. CSCD 34 - Data Management Systems. A. Vaisman 10

ssn Aggregation v § Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. Employees are assigned to monitor SPONSORSHIPS. lot Employees Used when we have to model a relationship involving (entity sets and) a relationship set. § name Monitors since started_on pid pbudget Projects until dname did Sponsors budget Departments * Aggregation vs. ternary relationship: Monitors and Sponsors are distinct relationships, with descriptive attributes of their own. v Also, can say that each sponsorship v CSCD 34 - Data Management Systems. A. Vaisman 11

Conceptual Design Using the ER Model v Design choices: § § § v Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Identifying relationships: Binary or ternary? Aggregation? Constraints in the ER Model: § § A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams. CSCD 34 - Data Management Systems. A. Vaisman 12

Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? v Depends upon the use we want to make of address information, and the semantics of the data: v • If we have several addresses per employee, address must be an entity (since attributes cannot be setvalued). • If the structure (city, street, etc. ) is important, e. g. , we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic). CSCD 34 - Data Management Systems. A. Vaisman 13

Entity vs. Attribute (Contd. ) from name v v Works_In 4 does not allow an employee to work in a department for two or more periods (a relationship is identified by participating entities). Similar to the problem of wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this relationship. Accomplished by introducing new entity set, Duration. ssn to lot did Works_In 4 Employees ssn CSCD 34 - Data Management Systems. A. Vaisman name dname lot Employees from budget Departments did Works_In 4 Duration dname budget Departments to 14

Entity vs. Relationship v v First ER diagram OK if a manager gets a name separate discretionary ssn budget for each dept. Employees What if a manager gets a discretionary name budget that covers ssn all managed depts? § § Redundancy: dbudget stored for each dept managed by manager. Misleading: Suggests dbudget associated with department-mgr combination. since dbudget lot did Managers CSCD 34 - Data Management Systems. A. Vaisman budget Departments Manages 2 lot since Employees ISA dname Manages 2 dbudget dname did budget Departments This fixes the problem! 15

Binary vs. Ternary Relationships ssn name Employees v Suppose: § A policy cannot be owned by more than one employee. § Every policy must be owned by some employee. § Dependent is a weak entity set, identified by polici. Id. pname lot Policies policyid ssn name Dependents Covers Bad design cost pname lot age Dependents Employees CSCD 34 - Data Management Systems. A. Vaisman age Purchaser Beneficiary Better design policyid Policies cost 16

Binary vs. Ternary Relationships (Contd. ) Previous example illustrated a case when two binary relationships were better than one ternary relationship. v An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute: v § Although S “can-supply” P, D “needs” P, and D “deals -with” S, all these do not imply that D has agreed to buy P from S (because D could buy P from another supplier). CSCD 34 - Data Management Systems. A. Vaisman 17

Summary of Conceptual Design v Conceptual design follows requirements analysis, § v Yields a high-level description of data to be stored ER model popular for conceptual design § Constructs are expressive, close to the way people think about their applications. Basic constructs: entities, relationships, and attributes (of entities and relationships). v Some additional constructs: weak entities, ISA hierarchies, and aggregation. v Note: There are many variations on ER model. v CSCD 34 - Data Management Systems. A. Vaisman 18

Summary of ER (Contd. ) v Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. § § Some constraints (notably, functional dependencies) cannot be expressed in the ER model. Constraints play an important role in determining the best database design for an enterprise. CSCD 34 - Data Management Systems. A. Vaisman 19

Summary of ER (Contd. ) v ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include: § v Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. Ensuring good database design: resulting relational schema should be analyzed and refined further. FD information and normalization techniques are especially useful. CSCD 34 - Data Management Systems. A. Vaisman 20