WIXLIB

Modeling:Entity-Relationship Diagrams1

Scenario http://www.imdb.com wants to store informationabout movies and has chosen you to help them Four steps:– Requirements Analysis: Discover what information needsto be stored, how the stored information will be used, etc.Taught in course on system analysis and design– Conceptual Database Design: High level description ofdata to be stored (ER model)– Logical Database Design: Translation of ER diagram to arelational database schema (description of tables)– Physical Database Design: Done by the DB system2

Requirements For actors and directors, we want to store theirname, a unique identification number, address andbirthday (why not age?) For actors, we also want to store a photograph For films, we want to store the title, year ofproduction and type (thriller, comedy, etc.) We want to know who directed and who acted ineach film. Every film has one director. We store thesalary of each actor for each film Etc 3

ER-Diagrams: General Information ER-diagrams are a formalism to model real-world scenarios There are many versions of ER-diagrams that differ both intheir appearance and in their meaning– We will use the version appearing in the book Database Systems:The Complete Book ER-diagrams have a formal semantics (meaning) that mustbe thoroughly understood, in order to create correctdiagrams Goal of modeling is to translate informal requirements to aprecise diagram. This diagram can then be translated intoto the desired data model, to allow data to be stored in adatabase4

Basic Concepts:Entities, Attributes,Relationships5

Entities, Entity Sets Entity ( )ישות : An object in the world that can bedistinguished from other objects– Examples of entities:– Examples of things that are not entities: Entity set ( )קבוצת ישויות : A set of similar entities– Examples of entity sets: Entity sets are drawn as rectanglesActor6

Attributes Attributes ( )תכונות : Used to describeentities– All entities in the set have the same attributes– A minimal set of attributes that uniquely identifyan entity is called a key– An attribute contains a single piece ofinformation (and not a list of data)7

Attributes (2) Examples of attributes: Examples of things that cannot beattributes: Attributes are drawn using ovals The names of the attributes which makeup a key are underlined8

ExamplebirthdayidActornameaddress9

Another Option for a Key?birthdayidActornameaddress10

Another Option for a Key?birthdayidActornameaddress11

Relationships, Relationship Sets Relationship ( )קשר : Association among twoor more entities– Examples of Relationships: Relationship Set ( )קבוצת קשרים : Set ofsimilar relationships– Examples of Relationship sets: Relationship sets are drawn using diamonds12

ExampleidActorProducedFilmtitlename13

Recursive Relationships An entity set can participate more than once in arelationship In this case, we add a description of the role to the nameaddress14

n-ary Relationship An n-ary relationship set R involves exactly n entitysets: E1, , En. Each relationship in R involves exactly n entities:e1 in E1, , en in En Formally, R E1x x EnidDirectornameidActorDirectedFilmtitlename15

Example Suppose that there are:– 2 Actors– 3 Directors– 4 FilmHow many pairs can be in therelationship set “Directed”?idHow many triples can be in therelationship set ename16

Multiplicity of Relationships17

Multiplicity of RelationshipsERF A member of E may be connected by R toany number of members from F, and viceversa This is called a many-many relationship18

Many-to-Many A film is directed by any number of directors A director can direct any number of edFilm19

Multiplicity of RelationshipsERF By adding arrows to the diagram, we can indicateconstraints on the relationship An arrow towards F indicates that:– A member of E may be connected by R to at most onemembers from F– (Still, a member of F may be connected by R to anynumber of members from E) This is called a many-one relationship20

One-to-Many A film is directed by at most one director A director can direct any number of edFilm21

Multiplicity of RelationshipsERF An arrow towards F and towards E indicates that:– A member of E may be connected by R to at most onemember from F– A member of F may be connected by R to at most onemember from E This is called a one-one relationship22

One-to-One A film is directed by at most one director A director can direct at most one dFilm23

ExampleWhere would you put the arrow?ageidStudentStudiesAtUniversitynamename24

Another ExampleWhere would you put the arrow?agefatheridPersonchildFatherOfname25

Multiplicities in MultiwayRelationshipsE1F1 REnFmFor any 1 i mFor any tuple of entities e1, ,en,f1, fi-1,fi 1,.,fmthere is at most one fi, such that e1, ,en,f1, ,fmare connected by R26

ExampleCARBSuppose that there are a entities in A b entities in B c entities in C d entities in DDWhat is themaximum numberof 4-tuples in R?What is theminimum number of4-tuples in R?27

ExampleidMannameidChildchildOfWomanidnamenameNote that formany reasons,this is a badmodelingWhat does this mean?28

Multiplicities in dnamenameNote that formany reasons,this is a badmodelingEach pair of a man and awoman can have at most onechild29

Referential Integrity andDegree Constraints30

Referential IntegrityERF So far, we can say that an entity participatesat most one time, but cannot require it toparticipate at least one time The rounded arrow above indicates thateach entity in E must participate exactly onetime in an R-relationship with an entity in F31

Degree ConstraintsActor 10StarsInMovie We can attach a bounding number to edges toindicate limits on the number of entities that can beconnected to a single entity via a relationship set In the example above, a move has at most 10 stars Note: a regular arrow is the constraint 1 Note: a rounded arrow is the constraint 132

Example (1)What does this mean?ageidStudentStudiesAtUniversitynamename33

Example (2)idMannameidChildchildOfWomanidnamenameNote that formany reasons,this is a terriblemodelingWhat does this mean?34

Relationship Sets with Attributes35

Relationship Sets Can haveAttributestitlebirthdayidActorActed InFilmyearnametypeaddressWhere does the salaryattribute belong?salary36

Important Note The entities in a relationship set must identify therelationship Attributes of the relationship set cannot be usedfor identification! Suppose we wanted to store the role of an actorin a film.– How should we store the role of the actor?– How would we store information about a person whoacted in one film in several roles?idActorActed InFilmtitlename37

Subclasses38

ISA HierarchiesISA Relationships: Defines a hierarchybetween entity sets– ISA is similar to inheritance ISA relationships are drawn as a triangle withthe word ISA inside it. The "super entity-set" isabove the triangle and the "sub entity-sets" arebelow39

Implications of an ISARelationship Every entity in B or in CAbelongs to A There may be entities in A thatisaBCdo not belong to B or to C There may be entities thatbelong to both B and C40

ExampleWhat are thekeys of:addressidbirthday1. Movie Person2. ActorMovie Personname3. DirectorISApictureActorDirector41

didIs this good method of modeling datafor the רבנות database on marriage?How can you fix it?42

Weak Entity Sets43

Intuition Sometimes, entities cannot be identified by theirown attributes. To identify such an entity, we need informationabout a “supporting relationship”– Example: Given a bank account number, you cannotidentify the actual bank account. For identification, youalso have to know the name of the bank.44

Example A פלוגה has a letter (. , ב , )א To uniquely identify the מספר גדוד פלוגה , one must know which גדוד it belongs to גדוד אות פלוגה שייכת ל פלוגה פלוגה is a weak entity set The relationship set שייכת ל is thesupporting relationship for פלוגה 45

Notation Weak entity set has adouble line גדוד מספר גדוד שייכת ל Supporting relationshipshave double lines אות פלוגה פלוגה Rounded arrow pointinginto the identifying entitysets46

) Example (1 גדוד מספר גדוד שייכת ל פלוגה אות פלוגה Keys: מספר גדוד : גדוד אות פלוגה , מספר גדוד : פלוגה מספר מחלקה , אות : מחלקה שייכת ל מחלקה 47 מספר מחלקה פלוגה , מספר גדוד

Example (2) Same award can be given byseveral organizationsphonenumbername(“Academy award for BestActor 2007”),Organization A year, award name andGivesorganization name uniquelydefine an awardWonAward Weak entity set canparticipate in additional (non-Actoryearnamesupporting) relationships48

Example (3) Awards are now identified byorganization and countryname Same award can be given bysame organization in es (“Academy awardfor Best Actor Israeli 2007”)GivesLocation Weak identity set has 2Awardsupporting relationships What is identifying key foryearnameaward?49

Design Principles50

Faithfulness The design should be accurate to thespecificationsActorActed InFilmsalary This is ok only if each actor has a set salary,regardless of all movies51

Avoiding Redundancy The design should not model the sameinformation in multiple waysMoviestudioNameOwnsStudioname Leads to fact repetition Leads to inconsistencies52

Simplicity Counts Avoid introducing elements that are notneededaddressidbirthdayPerson If we never need to storenameinformation about peopleisathat are not movie people,Movie Persondon’t put it in the diagramisapictureActorDirector53

Picking the Right Kind of ElementActorOwned ByStudioname The bottom diagram isActorsufficient if Studio has noattributes other thansnamename54

Summary Given a set of requirements, to translate therequirements into a diagram:– Identify the entity sets– Determine if there are hierarchies (ISA or weakrelationships) among entity sets– Identify the relationship sets– Identify the attributes– Determine constraints on relationship participation55

The Relational Model56

Data Models A data model is a notation for describingdata– Conceptual structure of the data– Operations on the data– Constraints on the data In this course we focus on the relationaldata model57

Conceptual Structure of the Data The basic element of the relational model isa relation (which is similar to a table) A relation has a schema, consisting of a– Name– List of attributes, possibly with domains A relation may also have an instance, whichis a set of tuples (i.e., rows) in the relation58

ExampleMovies TitleYearLengthGenreFollow 198590childrenWho 198790mystery Schema: Movies(title, year, length, genre) Relation name: Movies Attributes: title, year, length, genre Possible tuple instance– (“Follow that Bird”, 1985, 90, children) Scheme with domains:Movies(title: string, year: number,length: number, genre: string)59

Operations on the Data Relational algebra– Selection, projection, union, minus, join Stay tuned Discussed in detail next week60

Constraints on the Data We discuss complex constraints later on in thecourse For now, we introduce key constraints A set of attributes forms a key for a relation if therecannot be 2 different tuples with the same valuesfor all attributes of the key– Noted with underline Examples:– Movies(title, year, length, genre)– Actor(teudatZehut, name, address)61

A Step Closer Once we have a set of relational schemas inthe relational model, we are a step closer tostoring data in a DBMS– A DBMS has a data definition language(DDL), used to define tables in the database– Once we have decided on the relationalschemas, these can be directly translated intothe database using the DDL62

ER Diagrams toRelational Schemas63