WIXLIB

Entity-Relationship DiagramsFall 2017, Lecture 3There is nothing worse than asharp image of a fuzzy concept.Ansel Adams1Recall: Relational Database ManagementRelational DataBase Management Systems wereinvented to let you use one set of data inmultiple ways, including ways that areunforeseen at the time the database is builtand the 1st applications are written.(Curt Monash, analyst/blogger)2

3Software to be used in this Chapter MySQL Workbench which can be downloadedfrom http://www.mysql.com/products/workbench/4

Recap: Steps in Database DesignMiniworldFunctional RequirementsRequirementsAnalysisthis lectureData RequirementsFunctionalAnalysisDBMS independentConceptualDesignHigh LevelTransactionSpecificationConceptual SchemaLogical DesignDBMS dependentApplicationProgram DesignLogical SchemaPhysical DesignTransactionImplementationInternal SchemaApplication Programs5Phases of DB Design Requirements Analysis§ Database designers interview prospective usersand stakeholders§ Data requirements describe what kind of data isneeded§ Functional requirements describe the operationsperformed on the data Functional Analysis§ Concentrates on describing high-level useroperations and transactions Does also not contain implementation details Should be matched versus conceptual model6

Phases of DB Design Conceptual Design§ Transforms data requirements to conceptual model§ Conceptual model describes data entities, relationships,constraints, etc. on high-level Does not contain any implementation details Independent of used software and hardware Logical Design§ Maps the conceptual data model to the logical data model used bythe DBMS e.g. relational model, hierarchical model, Technology independent conceptual model is adapted to the used DBMSsoftware Physical Design§ Creates internal structures needed to efficiently store/managedata Table spaces, indexes, access paths, Depends on used hardware and DBMS software7Phases of DBMS Design While modeling the data, three design phases haveto be completed§ The result of a phase serves as input to the next phase§ Often, automatic transition is possible with someadditional designer feedbackconceptualdesignERdiagramUML, logicaldesigntables,columns, physicaldesigntablespaces,Indexes, 8

Example: DBA for Bank of America Requirements Specification§ Determine the requirements of clients (Database tostore information about customers, accounts, loans,branches, transactions, ) Conceptual Design§ Express client requirements in terms of E/R model.§ Confirm with clients that requirements are correct.§ Specify required data operations Logical Design§ Convert E/R model to relational, object-based, XMLbased, Physical Design§ Specify file organizations, build indexes9ER Modeling Traditional approach to Conceptual Modeling§ Entity-Relationship Models (ER-Models) Also known as Entity-Relationship Diagrams (ERD) Introduced in 1976 by Peter Chen Graphical representation Top-Down-Approach for modeling§ Entities and Attributes§ Relationships§ Constraints Some derivates became popular§ ER Crow’s Foot Notation (Bachman Notation)§ ER Baker Notation§ Later: Unified Modeling Language (UML)10

ER Modeling What are the entities and relationships? What info about E’s & R’s should be in DB? What integrity constraints (business rules) hold? ER diagram is a representation of the schema’ Can map an ER diagram into a relational schema. Conceptual design is where the SW/data engineeringbegins1112

E-R Diagram as Wallpaper Very common for them to be wall-sized13ER Model BasicsssnnamelotEmployees Entity:§ Real-world object, distinguishable from otherobjects.§ Described using a set of attributes.§ Has its own identity and represents just one thing Entity Set: A collection of similar entities. E.g., allemployees.§ All entities in an entity set have the same set ofattributes. (Until we consider hierarchies,anyway!)§ Each entity set has a key (underlined).§ Each attribute has a domain.14

ER Model ATE TABLE Employees(ssn CHAR(11),name CHAR(20),lot INTEGER,PRIMARY KEY (ssn))15E/R Diagrams In an entity-relationship diagram:§ Entity set rectangle.§ Attribute oval, with a line to the rectanglerepresenting its entity set.16

E/R Data Model: An ExampleEmployeeBranchWorks itychildrenWorks ForLots of notation to come.EmployeeEntity SetWorks ForRelationship SetphoneAttribute17In other words Entity “thing” or object. Entity set collection of similar entities.§ Similar to a class in object-oriented languages. Attribute property of (the entities of) an entityset.§ Attributes are simple values, e.g. integers or characterstrings, not structs, sets, etc.§ Example: name of an employee, color of a car, balanceof an account, location of a house, 18

Example:namemanfBeers Entity set Beers has two attributes, name andmanf (manufacturer). Each Beers entity has values for these twoattributes, e.g. (Corona, Grupo Modelo)19Entity Relationship Diagram SymbolsAn entity is represented by a rectangle which containsthe entity’s name.An entity that cannot be uniquely identified by itsattributes alone. The existence of a weak entity isdependent upon another entity called the owner entity.In the Chen notation, each attribute is represented by anoval containing atributte’s nameAn attribute whose value is calculated (derived) fromother attributes.An attribute that can have many values (there are manydistinct values entered for it in the same column of thetable).20

Entity Relationship Diagram SymbolsA relationship where entity is existence-independent ofother entities, and PK of Child doesn’t contain PKcomponent of Parent EntityA relationship where Child entity is existencedependent on parent, and PK of Child Entity containsPK component of Parent Entity.21E/R Data Model: Types of AttributesEmployeeBranchWorks AtessnphoneenamemanagerworkerWorks ldrenMultivaluedseniorityDerived22

E/R Data Model: Types ofrelationshipsEmployeeBranchWorks itychildrenWorks ForWorks ForMany-to-One (n:1)Works AtMany-to-Many (n:m)23E/R Data Model: RecursiverelationshipsEmployeeBranchWorks AtessnphoneenamemanagerworkerWorks ForsincebnamesenioritybcitychildrenRecursive relationships: Must be declared with rolesEmployeemanagerworkerWorks For24

Business Rules A business rule is “a statement thatdefines or constrains some aspect of thebusiness. It is intended to assert businessstructure or to control or influence thebehavior of the business.rules prevent,cause, or suggest things to happen” Entity-relationship diagrams are used todocument rules and policies of anorganization25In fact, documenting rules andpolicies of an organization thatgovern data is exactly whatdata modeling is all about.26

tDepartmentsWorks In Relationship: Association among two or more entities.E.g., Attishoo works in Pharmacy department.§ relationships can have their own attributes. Relationship Set: Collection of similar relationships.§ An n-ary relationship set R relates n entity sets E1 . En ; eachrelationship in R involves entities e1 Î E1, ., en Î idlotWorks InCREATE TABLE Works In(ssn CHAR(11),did INTEGER,since DATE,PRIMARY KEY (ssn, did),FOREIGN KEY (ssn)REFERENCES Employees,FOREIGN KEY (did)REFERENCES 1-31-5368did515651since1/1/913/3/932/2/9228

Example: Drinkers likesome beers.FrequentsNote:license beer, full,noneBars sell somebeers.LikesDrinkers frequentsome bars.nameDrinkersaddr29Example: Relationship Set For the relationship Sells, we might have arelationship set like:BarJoe’s BarJoe’s BarSue’s BarSue’s BarSue’s BarBeerBudMillerBudPete’s AleBud Lite30

ER Model Basics melotDepartmentssubordinateReports ToWorks In Same entity set can participate in differentrelationship sets, or in different “roles” in thesame set.31namessnsincelotdnamedidbudgetKey ConstraintsEmployeesAn employee canwork in manydepartments; adept can havemany employees.In contrast, each depthas at most onemanager, accordingto the key constrainton Manages.ManagesDepartmentsWorks InsinceMany-toMany1-to Many1-to-132

namessnsincednamelotdidbudgetKey ConstraintsEmployeesAn employee canwork in manydepartments; adept can havemany employees.DepartmentsManagesWorks InsinceIn contrast, each depthas at most onemanager, accordingto the key constrainton Manages.CREATE TABLE Manages(ssn CHAR(11),did INTEGER,since DATE,PRIMARY KEY(did),FOREIGN KEY (ssn) REFERENCES Employees,FOREIGN KEY (did) REFERENCES Departments)33Participation Constraints Does every employee work in a department? If so, this is a participation constraint§ the participation of Employees in Works In is said to be total(vs. partial)§ What if every department has an employee working in it? Basically means “one or etDepartmentsWorks InMeans: “one or more”sinceMeans: “exactly one”34

Weak EntitiesA weak entity can be identified uniquely only with theprimary key of another (owner) entity.§ Owner entity set and weak entity set mustparticipate in a one-to-many relationship set (oneowner, many weak entities).§ Weak entity set must have total participation in thisidentifying relationship sWeak entities have only a “partial key” (dashed underline)35Weak EntitiesCREATE TABLEPolicy ( pnameCHAR(20),age INTEGER,cost REAL,ssn CHAR(11) NOT NULL,PRIMARY KEY (pname, ssn),FOREIGN KEY (ssn) REFERENCES Employees,ON DELETE dents36

Binary vs. Ternary RelationshipsssnIf each policy is owned byjust 1 ageDependentsEmployees Think through allthe constraints inthe 2nd diagram!ageDependentsCoversBad designKey constraint onPolicies wouldmean policy cannamessnonly cover 1dependent!pnamelotPurchaserBeneficiaryBetter designPoliciespolicyidcost37Binary vs. Ternary Relationships(Contd.)The keyconstraints allowus to combinePurchaser withPolicies andBeneficiary withDependents.Participationconstraints lead toCREATE TABLE Policies (policyid INTEGER,cost REAL,ssn CHAR(11) NOT NULL,PRIMARY KEY (policyid).FOREIGN KEY (ssn) REFERENCES Employees,ON DELETE CASCADE)CREATE TABLE Dependents (pname CHAR(20),age INTEGER,NOT NULLpolicyid INTEGER,constraints.PRIMARY KEY (pname, policyid).What if Policies is FOREIGN KEY (policyid) REFERENCES Policies,a weak entity set?ON DELETE CASCADE)38

Binary vs. Ternary Relationships (Contd.) Previous example illustrated a case when two binaryrelationships were better than one ternary. An example in the other direction: a ternary relationContracts relates entity sets Parts, Departments andSuppliers, and has descriptive attribute quantity.§ No combination of binary relationships is anadequate rs39Binary vs. Ternary Relationships th§ S “can-supply” P, D “needs” P, and D “deals-with” S does notimply that D has agreed to buy P from S.§ How do we record qty?40

AggregationssnnamelotEmployeesMonitorssincestarted artmentsAllows relationships with relationship sets.41Aggregation vs. incestarted onsorsdnamebudgetDepartmentsuntilsincestarted gation vs. ternary relationship? Monitors is a distinct relationship, witha descriptive attribute. Also, can say that each sponsorship is42monitored by at most one employee.