Conceptual Design Using the Entity-Relationship (ER) Model

1 Database Management Systems, R. Ramakrishnan1 Conceptual Design Using theEntity- relationship (ER) ModelModule 5, Lectures 1 and 2 Database Management Systems, R. Ramakrishnan2 Overview of Database DesignvConceptual Design : (ER Model is used at this stage.) What are the entities and relationships in the enterprise? What information about these entities and relationshipsshould we store in the database? What are the integrity constraints or business rules that hold? A database ` schema in the ER Model can be representedpictorially (ER diagrams). Can map an ER diagram into a relational Refinement: (Normalization) Check relationalschema for redundancies and related Database Design and Tuning: Consider typicalworkloads and further refine the database Management Systems, R. Ramakrishnan3ER Model BasicsvEntity: Real-world objectdistinguishable fromother objects. An entity is described (in DB) Using a set of Set: A collection of similarentities.

2 , all employees. All entities in an entity set have thesame set of attributes. (Until weconsider ISA hierarchies, anyway!) Each entity set has a key. Each attribute has a domain. Can map entity set to a relation easily. CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn))Employeesssnnamelotssnnamelot123-2 2-3666 Attishoo48231-31-5368 Smiley22131-24-3650 Smethurst 35 Database Management Systems, R. Ramakrishnan4ER Model Basics (Contd.)vRelationship: Association among 2 or more , Attishoo works in Pharmacy 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 EnuSame entity set could participate in differentrelationship sets, or in different roles in same Management Systems, R.

3 Ramakrishnan5ER Model Basics (Contd.)vRelationship sets can also havedescriptive attributes ( , thesince attribute of Works_In).vIn translating a relationship setto a relation, attributes of therelation must include: Keys for each participatingentity set (as foreign keys).uThis set of attributesforms superkey for therelation. All descriptive TABLE Works_In( ssn CHAR(1), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)ssndidsince123-22-3666 511/1/91123-22-3666 563/3/93231-31-5368 512/2/92 Database Management Systems, R. Ramakrishnan6 Key ConstraintsvConsider Works_In:An employee canwork in manydepartments; a deptcan have contrast, eachdept has at mostone manager,according to thekey constraint onManages.* Translation to relational Model ?Many-to-Many1-to-11-to ManyMany-to-1dnamebudgetdidsincelotnames snManagesEmployeesDepartmentsDatabase Management Systems, R.

4 Ramakrishnan7 Translating ER Diagrams with Key ConstraintsvMap relationship to atable: Note that did isthe key now! Separate tables forEmployees eachdepartment has aunique manager, wecould insteadcombine Managesand TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)Database Management Systems, R. Ramakrishnan8 Participation ConstraintsvDoes every department have a manager? If so, this is a participation constraint: the participation ofDepartments in Manages is said to be total (vs. partial).uEvery did value in Departments table must appear in arow of the Manages table (with a non-null ssn value!)

5 Lotnamednamebudgetdidsincenamednamebudge tdidsinceManagessinceDepartmentsEmployee sssnWorks_InDatabase Management Systems, R. Ramakrishnan9 Participation Constraints in SQLvWe can capture participation constraints involvingone entity set in a binary relationship , but little else(without resorting to CHECK constraints).CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION)Database Management Systems, R. Ramakrishnan10 Weak EntitiesvA weak entity can be identified uniquely only byconsidering the primary key of another (owner) entity . Owner entity set and weak entity set must participate in aone-to-many relationship set (1 owner, many weak entities). Weak entity set must have total participation in thisidentifying relationship Management Systems, R.

6 Ramakrishnan11 Translating Weak entity SetsvWeak entity set and identifying relationshipset are translated into a single table. When the owner entity is deleted, all owned weakentities must also be TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE)Database Management Systems, R. Ramakrishnan12 ISA (`is a ) HierarchiesContract_EmpsnamessnEmployees lothourly_wagesISAH ourly_Empscontractidhours_workedvAs in C++, or other PLs,attributes are we declare A ISA B, every Aentity is also considered to be a Bentity. (Query answers shouldreflect this: unlike C++!)vOverlap constraints: Can Joe be an Hourly_Emps as well as aContract_Emps entity ? (Allowed/disallowed)vCovering constraints: Does every Employees entity also have tobe an Hourly_Emps or a Contract_Emps entity ?

7 (Yes/no)vReasons for Using ISA: To add descriptive attributes specific to a subclass. To identify entitities that participate in a Management Systems, R. Ramakrishnan13 Translating ISA Hierarchies to RelationsvGeneral approach: 3 relations: Employees, Hourly_Emps and : Every employee is recorded inEmployees. For hourly emps, extra info recorded inHourly_Emps (hourly_wages, hours_worked, ssn); mustdelete Hourly_Emps tuple if referenced Employeestuple is deleted).uQueries involving all employees easy, those involvingjust Hourly_Emps require a join to get some : Just Hourly_Emps and Contract_Emps. Hourly_Emps: ssn, name, lot, hourly_wages, hours_worked. Each employee must be in one of these two Management Systems, R. Ramakrishnan14 AggregationvUsed when we haveto Model arelationshipinvolving (entititysets and) arelationship set. Aggregation allows usto treat a relationshipset as an entity setfor purposes ofparticipation in(other) relationships.

8 Monitors mapped totable like any otherrelationship set.* Aggregation vs. ternary relationship : v Monitors is a distinct relationship , with a descriptive Also, can say that each sponsorship is monitored by at most one Management Systems, R. Ramakrishnan15 Conceptual Design Using the ER ModelvDesign choices: Should a concept be modelled as an entity or an attribute? Should a concept be modelled as an entity or a relationship ? Identifying relationships: Binary or ternary? Aggregation?vConstraints in the ER Model : A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER for further refining the schema : relational schema obtained from ER diagram is a good firststep. But ER Design subjective & can t express certainconstraints; so this relational schema may need Management Systems, R. Ramakrishnan16 entity vs. AttributevShould address be an attribute of Employees or anentity (connected to Employees by a relationship )?

9 VDepends upon the use we want to make of addressinformation, and the semantics of the data:uIf we have several addresses per employee, address mustbe an entity (since attributes cannot be set-valued).uIf the structure (city, street, etc.) is important, , wewant to retrieve employees in a given city, address mustbe modelled as an entity (since attribute values areatomic).Database Management Systems, R. Ramakrishnan17 entity vs. Attribute (Contd.)vWorks_In2 does notallow an employee towork in a departmentfor two or more to the problemof wanting to recordseveral addresses for anemployee: we want torecord several values of thedescriptive attributes foreach instance of Management Systems, R. Ramakrishnan18 entity vs. RelationshipvFirst ER diagram OK ifa manager gets aseparate discretionarybudget for each if a manager getsa discretionary budgetthat covers allmanaged depts? Redundancy of dbudget,which is stored for eachdept managed by themanager.

10 Manages2namednamebudgetdidEmployeesDepar tmentsssnlotdbudgetsinceEmployeessincena mednamebudgetdidDepartmentsssnlotMgr_App tsManages3dbudgetapptnumMisleading: suggests dbudget tied to managed dept. Database Management Systems, R. Ramakrishnan19 Binary vs. Ternary RelationshipsvIf each policy isowned by just 1employee: Key constrainton Policieswould meanpolicy can onlycover 1dependent!vWhat are theadditionalconstraints in the2nd diagram?agepnameDependentsCoversnameEmpl oyeesssnlotPoliciespolicyidcostBeneficia ryagepnameDependentspolicyidcostPolicies PurchasernameEmployeesssnlotBad designBetter designDatabase Management Systems, R. Ramakrishnan20 Binary vs. Ternary Relationships (Contd.)vThe keyconstraints allowus to combinePurchaser withPolicies andBeneficiary lead toNOT if Policies isa weak entity set?CREATE TABLE Policies ( policyid INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (policyid).)