Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1The Entity-Relationship ModelChapter 2Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2Overview of Database Design 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.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3ER Model Basics Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes.  Entity Set: A collection of similar entities. E.g., all employees.  All entities in an entity set have the same set of attributes. (Until we consider ISA hierarchies, anyway!) Each entity set has a key. Each attribute has a domain.EmployeesssnnamelotDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4ER Model Basics (Contd.) Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department. Relationship Set: Collection of similar relationships. An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1 E1, ..., en En• Same entity set could participate in different relationship sets, or in different “roles” in same set.lotdnamebudgetdidsincenameWorks_InDepartmentsEmployeesssnReports_TolotnameEmployeessubor-dinatesuper-visorssnDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5Key Constraints Consider Works_In: An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager, according to the key constrainton Manages.Many-to-Many1-to-1 1-to Many Many-to-1dnamebudgetdidsincelotnamessnManagesEmployeesDepartmentsDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6Participation Constraints 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 Departments entity must appear in an instance of the Manages relationship.lotnamednamebudgetdidsincenamednamebudgetdidsinceManagessinceDepartmentsEmployeesssnWorks_InDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7Weak Entities 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-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. lotnameagepnameDependentsEmployeesssnPolicycostDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8ISA (`is a’) HierarchiesContract_EmpsnamessnEmployeeslothourly_wagesISAHourly_Empscontractidhours_worked As in C++, or other PLs, attributes are inherited. If we declare A ISA B, every A entity is also considered to be a B entity.  Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed) Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)  Reasons for using ISA:  To add descriptive attributes specific to a subclass. To identify entitities that participate in a relationship.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9Aggregation Used when we have to model a relationship involving (entititysets and) a relationship set. Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. Aggregation vs. ternary relationship:  Monitors is a distinct relationship, with a descriptive attribute. Also, can say that each sponsorship is monitored by at most one employee.budgetdidpidstarted_onpbudgetdnameuntilDepartmentsProjectsSponsorsEmployeesMonitorslotnamessnsinceDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10Conceptual Design Using the ER Model Design choices: 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.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? Depends upon the use we want to make of address information, and the semantics of the data:• If we have several addresses per employee, addressmust be an entity (since attributes cannot be set-valued). • If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, addressmust be modeled as an entity (since attribute values are atomic). Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12Entity vs. Attribute (Contd.) Works_In4 does not allow an employee to work in a department for two or more periods. 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. nameEmployeesssnlotWorks_In4fromtodnamebudgetdidDepartmentsdnamebudgetdidnameDepartmentsssnlotEmployeesWorks_In4DurationfromtoDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13Entity vs. Relationship First ER diagram OK if a manager gets a separate discretionary budget for each dept. What if a manager gets a discretionary budget that covers all managed depts? Redundancy: dbudgetstored for each dept managed by manager. Misleading: Suggests dbudget associated with department-mgr combination.Manages2namednamebudgetdidEmployeesDepartmentsssnlotdbudgetsincednamebudgetdidDepartmentsManages2EmployeesnamessnlotsinceManagers dbudgetISAThis fixes theproblem!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14Binary vs. Ternary Relationships If each policy is owned by just 1