Unit 2Modeling the Information of an Enterprise UsingChen’s Entity/Relationship Model and Diagrams 2016 Zvi M. Kedem1

ER Diagrams in ContextUser Level(View Level)Derived TablesConstraints, PrivilegesDerivedCommunity Level(Base Level)Base TablesConstraints, PrivilegesImplementedPhysical LevelFilesIndexes, DistributionQueries (DML)Application Data Analysis (ER)Normalization (NFs)Schema Specification (DDL)Queries (DML)Query Execution (B , , Execution Plan)Relies onDBMS OS LevelConcurrencyRecoveryTransaction Processing (ACID, Sharding)Runs onCentralizedOrDistributed 2016 Zvi M. KedemStandard OSStandard Hardware2

Purpose Of ER Model And Basic Concepts Entity/relationship (ER) model provides a common,informal, and convenient method for communicationbetween application end users (customers) and thedatabase designers to model the information’s structure This is a preliminary stage towards defining the databaseusing a formal model, such as the relational model, to bedescribed later The ER model, frequently employs ER diagrams, whichare pictorial descriptions to visualize information’sstructure ER models are both simple and powerful 2016 Zvi M. Kedem3

Purpose Of ER Model And Basic Concepts There are three basic concepts appearing in the originalER model, which has since been extended We will present the model from more simple to more complexconcepts, with examples on the way We will go beyond the original ER model, and cover mostof the Enhanced ER model While the ER model’s concepts are standard, there areseveral varieties of pictorial representations of ERdiagrams We will focus on one of them: Chen’s notation We will also cover Crow’s foot notation in the context of the Visiotool Others are simple variations, so if we understand the above, wecan easily understand all of them You can look at some examples p model 2016 Zvi M. Kedem4

Basic Concepts The three basic concepts are (elaborated on very soon): Entity. This is an “object.” Cannot be defined even closeto a formal way. Examples: Bob Boston The country whose capital is ParisThere is only one such country so it is completely specified Relationship. Entities participate in relationships witheach other. Examples: Alice and Boston are in relationship Likes (Alice likes Boston) Bob and Atlanta are not in this relationship Attribute (property). Examples: Age is an attribute of persons Size is an attribute of cities 2016 Zvi M. Kedem5

Entity And Entity Set Entity is a “thing” that is distinguished from others in ourapplication Example: Alice All entities of the same “type” form an entity set; we usethe term “type” informally Example: Person (actually a set of persons). Alice is an entity inthis entity set What type is a little tricky sometimes Example. Do we partition people by sex or not? Sometimes makes sense (gave birth)This allows better enforcement of constraints. You could“automatically” make sure that only entities in the set of women,but not in the set of men can give birth Sometimes not (employment) 2016 Zvi M. Kedem6

Entity And Entity Set Example. When we say “the set of all Boeing airplanes,” isthis The set of all models appearing in Boeing’s catalog (abstractobjects), or The set of airplanes that Boeing manufactured (concrete objects) We may be interested in both and have two entity setsthat are somehow related We will frequently use the term “entity” while actuallyreferring to entity sets, unless this causes confusion 2016 Zvi M. Kedem7

Entity And Entity Set Pictorially, an entity set is denoted by a rectangle with itstype written inside By convention, singular noun, though we may not adhereto this convention if not adhering to it makes things clearer By convention, capitalized, or all capitals, if acronymPerson 2016 Zvi M. Kedem8

Attribute An entity may have (and in general has) a set of zero ormore attributes, which are some properties Each attribute is drawn from some domain (such asintegers) possibly augmented by NULL (more aboutNULLs later) All entities in an entity set have the same set of properties,though not generally with the same values Attributes of an entity are written in ellipses (for now solidlines) connected to the entity Example: FN: “First Name.” LN: “Last Name.” DOB: “Date ofBirth.”FNLNDOBPerson 2016 Zvi M. Kedem9

Attribute Attributes can be Base (such as DOB); or derived denoted by dashed ellipses (such asAge, derived from DOB and the current date) Simple (such as DOB); or composite having their component attributesattached to them (such as Address, when we think of it explicitly asconsisting of street and number and restricting ourselves to one city only) Singlevalued (such as DOB); or multivalued with unspecified inadvance number of values denoted by thick-lined ellipses (such as Child;a person may have any number of children; we do not consider childrenas persons in this example, this means that they are not elements of theentity set Person, just attributes of elements of this set)NumberChildFNLNDOBStreetAddressAgePerson 2016 Zvi M. Kedem10

Attribute To have a simple example of a person with attributes Child: BobChild: CarolFN: AliceLN: XieDOB: 1980-01-01Address.Number: 100Address.Street: MercerAge: Current Date minus DOB specified in years (rounded down)NumberChildFNLNDOBStreetAddressAgePerson 2016 Zvi M. Kedem11

Sets, Subsets, and Supersets Relations subset and superset are defined among sets It is analogous to Let us review by an example of three sets A {2,5,6} B {1,2,5,6,8} C {2,5,6} Then we have AB and A is a subset of Band A is a proper subset, actually is not all of B; AB AC and A is a subset of Cand A is not a proper subset, actually is equal to C; A C Caution: sometimesby 2016 Zvi M. Kedemis used to denote what we denote12

Keys Most of the times, some subset (proper or not) of theattributes of an entity has the property that two differententities in an entity set must differ on the values of theseattributes This must hold for all conceivable entities in our database Such a set of attributes is called a superkey (“weak”superset of a key: either proper superset or equal) A minimal superkey is called a key (sometimes called acandidate key). This means that no proper subset of it is itself a superkeyLongitudeLatitudeCountryStateNameSizeCity 2016 Zvi M. Kedem13

Keys Informally: superkey values can identify an individualentity but there may be unnecessary attributes Informally: key value can identify an individual entity butthere are no unnecessary attributes Example: Social Security Number Last Name form asuperkey, which is not a key as Social Security Number isenough to identify a person 2016 Zvi M. Kedem14

Keys In our example: Longitude and Latitude (their values) identify (at most) one City,but only Longitude or only Latitude do not (Longitude, Latitude) form a superkey, which is also a key (Longitude, Latitude, Size, Name) form a superkey, which is not akey, because Size and Name are superfluous (Country, State, Name) form another key (and also a superkey, asevery key is a superkey) For simplicity, we assume that every country is dividedinto states and within a state the city name is uniqueLongitudeLatitudeCountryStateNameSizeCity 2016 Zvi M. Kedem15

Primary Keys If an entity set has one or more keys, one of them (noformal rule which one) is chosen as the primary key In SQL the other keys, loosely speaking, are referred tousing the keyword UNIQUE In the ER diagram, the attributes of the primary key areunderlined So in our example, one of the two meSizeCityLongitudeLatitudeCountryCity 2016 Zvi M. Kedem16

Relationship Several entity sets (one or more) can participate in arelationship Relationships are denoted by diamonds, to which theparticipating entities are “attached” A relationship could be binary, ternary, . By convention, a capitalized verb in third person singular(e.g., Likes), though we may not adhere to this conventionif not adhering to it makes things clearet 2016 Zvi M. Kedem17

Relationship We will have some examples of relationships We will use three entity sets, with entities (and theirattributes) in those entity sets listed inyang 2016 Zvi M. Kedem18

Binary Relationship Let’s look at Likes, listing all pairs of (x,y) where person xLikes product y, and the associated ER diagram First listing the relationship informally (we omit article “a”): Chee likes computerChee likes monitorLakshmi likes computerMarsha likes computer Note Not every person has to Like a product Not every product has to have a person who Likes it (informally,be Liked) A person can Like many products A product can have many person each of whom Likes itNamePerson 2016 Zvi M. KedemTypeLikesProduct19

Relationships Formally we say that R is a relationship among (notnecessarily distinct) entity sets E1, E2, , En if and only ifR is a subset of E1 E2 En (Cartesian product) In our example above: n 2E1 {Chee, Lakshmi, Marsha, Michael, Jinyang}E2 {computer, monitor, printer}E1 E2 { (Chee,computer), (Chee,monitor), (Chee,printer),(Lakshmi,computer), (Lakshmi,monitor), (Lakshmi,printer),(Marsha,computer), (Marsha,monitor), (Marsha,printer),(Michael,computer), (Michael,monitor), (Michael,printer),(Jinyang,computer), (Jinyang,monitor), (Jinyang,printer) } R { (Chee,computer), (Chee,monitor), (Lakshmi,computer),(Marsha,monitor) } R is a set (unordered, as every set) of ordered tuples, orsequences (here of length two, that is pairs) 2016 Zvi M. Kedem20

Relationships Let us elaborate E1 E2 was the “universe” It listed all possible pairs of a person liking a product At every instance of time, in general only some of thispairs corresponded to the “actual state of the universe”; Rwas the set of such pairs 2016 Zvi M. Kedem21

Important Digression Ultimately, we will store (most) relationships as tables So, our example for Likes could terMarshaMonitor Where we identify the “participating” entities using theirprimary keys 2016 Zvi M. Kedem22

Ternary Relationship Let’s look at Buys listing all tuples of (x,y,z) where personx Buys product y from vendor z Let us just state it informally: Chee buys computer from IBMChee buys computer from DellLakshmi buys computer from DellLakshmi buys monitor from AppleChee buys monitor from IBMMarsha buys computer from IBMMarsha buys monitor from DellPersonBuysProductVendor 2016 Zvi M. Kedem23

Relationship With Nondistinct Entity Sets Let’s look at Likes, listing all pairs of (x,y) where person xLikes person y Let us just state it informally Chee likes LakshmiChee likes MarshaLakshmi likes MarshaLakshmi likes MichaelLakshmi likes LakshmiMarsha likes Lakshmi Note that pairs must be ordered to properly specify therelationship, Chee likes Lakshmi, but Lakshmi does notlike CheeNamePerson 2016 Zvi M. KedemLikes24

Relationship With Nondistinct Entity Sets Again: Chee likes LakshmiChee likes MarshaLakshmi likes MarshaLakshmi likes MichaelLakshmi likes LakshmiMarsha likes Lakshmi Formally Likes is a subset of the Cartesian productPerson Person, which is the set of all ordered pairs ofthe form (person,person) Likes is the set { (Chee,Lakshmi), (Chee,Marsha),(Lakshmi,Marsha), (Lakshmi,Michael),(Lakshmi,Lakshmi), (Marsha,Lakshmi) } Likes is an arbitrary directed graph in which persons serveas vertices and arcs specify who likes whom 2016 Zvi M. Kedem25

Important Digression Ultimately, we will store (most) relationships as tables So, our example for Likes could akshmiMichaelLakshmiLakshmiMarshaLakshmi Where we identify the “participating” entities using theirprimary keys But it is difficult to see (unless we keep track of columnsorder) whether Lakshmi Likes Michael or Michael LikesLakshmi 2016 Zvi M. Kedem26

Relationship With Nondistinct Entity Sets Frequently it is useful to give roles to the participatingentities, when, as here, they are drawn from the sameentity set. So, we may say that if Chee likes Lakshmi, then Chee isthe “Liker” and Lakshmi is the “Liked” Roles are explicitly listed in the diagram, but thesemantics of they mean cannot be deduced from lookingat the diagram onlyNameLikerPersonLikesLiked 2016 Zvi M. Kedem27

Important Digression Ultimately, we will store (most) relationships as tables So, our example for Likes could aLakshmiMichaelLakshmiLakshmiMarshaLakshmi Where we identify the “participating” entities using theirprimary keys but we rename them using roles So we do not need to keep track of columns order and weknow that Lakshmi Likes Michael and Michael does notLike(s) Lakshmi, though we still do not know what “Likes”really means 2016 Zvi M. Kedem28

Relationship With Nondistinct Entity Sets Consider Buys, listing all triples of the form (x,y,z) wherevendor x Buys product y from vendor z A typical tuple might be (Dell,printer,HP), meaning thatDell buys a printer from HPVendor 2016 Zvi M. KedemBuysProduct29

ER Diagrams To show which entities participate in which relationships,and which attributes participate in which entities, we drawline segments between: Entities and relationships they participate in Attributes and entities they belong to We also underline the attributes of the primary key foreach entity that has a primary key Below is a simple ER diagram (with a simpler Person thanwe had ikesPersonSSN 2016 Zvi M. KedemName30

Further Refinements To The ER Model We will present, in steps, further