Entities The Entity-Relationship Model name ssn lot Employees - PDF document

Entities The Entity-Relationship Model name ssn lot Employees Database Management Systems, R. Ramakrishnan and J. Gehrke 1 Database Management Systems, R. Ramakrishnan and J. Gehrke 2 ER Model Basics Relationships � Entity: Real-world object distinguishable from other objects. An entity is described since (in DB) using a set of attributes name dname ssn budget lot did � Entity Set : A collection of similar entities. E.g., all employees Works_In Employees Departments – All entities in an entity set have the same set of attributes – Each entity set has a key – Each attribute has a domain Database Management Systems, R. Ramakrishnan and J. Gehrke 3 Database Management Systems, R. Ramakrishnan and J. Gehrke 4 ER Model Basics (Contd.) Relationships (Contd.) � Relationship : Association among two or more name entities. ssn lot – E.g., Attishoo works in Pharmacy department. � Relationship Set : Collection of similar Employees relationships. super- subor- visor dinate – An n-ary relationship set R relates n entity sets E1 Reports_To ... En – Each relationship in R involves entities e1 in E1, ..., en in En � Want to capture supervisor-subordinate relationship Database Management Systems, R. Ramakrishnan and J. Gehrke 5 Database Management Systems, R. Ramakrishnan and J. Gehrke 6

Relationships (Contd.) Ternary Relationship name id name id Parts Parts name id name id name name id id Suppliers Departments Departments Suppliers Contract � Want to capture information that a Supplier s supplies Part p to Department d Database Management Systems, R. Ramakrishnan and J. Gehrke 7 Database Management Systems, R. Ramakrishnan and J. Gehrke 8 How are these different? Key Constraints since to name from dname � An employee can name dname ssn lot did budget ssn lot did budget work in many departments; a dept Employees Works_In2 Departments Employees Works_In Departments can have many employees since � Each dept has at name dname name dname most one manager, ssn lot did budget ssn lot did budget according to the Works_In3 Departments Employees key constraint on Employees Manages Departments Manages. Duration to from Database Management Systems, R. Ramakrishnan and J. Gehrke 9 Database Management Systems, R. Ramakrishnan and J. Gehrke 10 Participation Constraints What does this mean? since name dname � An employee can ssn lot did budget work in many since since departments; a dept name name dname dname Employees Works_In Departments can have many ssn did did budget budget lot employees Employees Manages Departments � Each employee Works_In works in at least since name dname one department ssn lot did budget according to the since participation Employees Works_In Departments constraint on Works_In Database Management Systems, R. Ramakrishnan and J. Gehrke 11 Database Management Systems, R. Ramakrishnan and J. Gehrke 12

name ssn lot Weak Entities ISA (`is a’) Hierarchies Employees � A weak entity can be identified uniquely only by considering � As in C++, or other PLs, hourly_wages hours_worked ISA the primary key of another ( owner ) entity. attributes are inherited. contractid – Owner entity set and weak entity set must participate in a one-to- � If we declare A ISA B, every A many relationship set (one owner, many weak entities). Contract_Emps Hourly_Emps entity is also considered to be a B – Weak entity set must have total participation in this identifying entity. relationship set. � 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 name to be an Hourly_Emps or a Contract_Emps entity? (Yes/no) cost pname ssn age lot � Reasons for using ISA : – To add descriptive attributes specific to a subclass . Policy Dependents Employees – To identify entitities that participate in a relationship . Database Management Systems, R. Ramakrishnan and J. Gehrke 13 Database Management Systems, R. Ramakrishnan and J. Gehrke 14 name ssn lot Aggregation Conceptual Design Using the ER Model Employees � Design choices: � Used when we have to model a Monitors until – Should a concept be modeled as an entity or an attribute? relationship – Should a concept be modeled as an entity or a relationship? involving (entitity – Identifying relationships: Binary or ternary? Aggregation? sets and) a started_on since dname � Constraints in the ER Model: relationship set . pid pbudget did budget – Aggregation allows us – A lot of data semantics can (and should) be captured. Sponsors Projects Departments to treat a relationship – But some constraints cannot be captured in ER diagrams. set as an entity set � Aggregation vs. ternary relationship : for purposes of participation in � Monitors is a distinct relationship, (other) relationships. with a descriptive attribute. � Also, can say that each sponsorship is monitored by at most one employee. Database Management Systems, R. Ramakrishnan and J. Gehrke 15 Database Management Systems, R. Ramakrishnan and J. Gehrke 16 Summary of Conceptual Design � Conceptual design follows requirements analysis � ER model popular for conceptual design � Basic constructs: entities , relationships , and attributes � Some additional constructs: weak entities , ISA hierarchies , and aggregation . � Note: There are many variations on ER model. Database Management Systems, R. Ramakrishnan and J. Gehrke 17