The EntityRelationship Data Model Chapter 2 Database Design

The Entity-Relationship Data Model Chapter 2 (Database Design) 1

Steps in designing a database • Analysis: – What information needs to be stored? – What are the relationships between different components of the stored information? – What is the suitable database structure (or schema)? • Design the database structure (using a database design language or notation suitable for expressing design) • Implementation in DBMS once committed to the design 2

Steps in picture • E/R diagram • Relational model • ODL, ORDL Ideas/ Problems Analysis Database Structure Design Database Design DBMS Implementation 3

Entity-Relationship Model • Traditional & popular • Graphical representation • Three types of elements – Entity set (rectangle) – Attributes (oval) – Relationship (diamond) 4

Entity Set • • Collection of similar objects Similar to a class in the sense of OOP Entity vs. entity set Example: Database about movies, their stars, the studio that producing them, and other aspects of movies. – A movie is an entity – Collection of movies is an entity set – Other entities in this db: ? 5

Attributes • Entity set has associated attributes • Each attribute represents a property of entities belonging to the entity set • Example: – The entity set Movies has the associated attribute Title each movie has a title • Assumption: attributes are atomic values address with two components (e. g. number, street) cannot be used as an attribute of an entity set 6

Relationship • Connections among entity sets • Represent a relationship between entity sets – If E and F are two entity sets and R connect the two, then R is a binary relation between E and F, mathematically we write R E x F. • Example: Movies and Stars are two entity sets and a connection Stars-In between the two the intention: m is related to s means that star s starts in movie m. 7

E/R Diagram • A graph representing entity sets, attributes, and relationships. – Entity set (rectangle) – Attributes (oval) – Relationship (diamond) 8

name address year title Stars_in Movies length name Owns film type Stars Studios address 9

A reading of the previous diagram • Three entity sets: – Movies – Stars – Studio [title, year, length, film type] [name, address] • Two relationships – (m: n) – Stars_in(Movies, Stars) – (m: 1) – Owns(Movies, Studios) 10

Instance of an E/R diagram • Concrete data of a database, whose structure is represented by a E/R diagram, creates a database instance • In an instance – For each entity set: a particular finite set of entities, each has values for each attribute – For each relationship connecting the entities E 1, E 2, …, En a set of tuples (e 1, e 2, …, en) each ei is an entity in the entity set of Ei – Where are the attributes? 11

Example • A possible database instance of the previous E/R diagram (Note: The info is inserted for illustrative purpose only – it needs not be true in real life) Movies Stars Studio Basic instinct 1990 150 Drama Sharon Stone a 1 Universal Studio Total recall 1989 120 Mystery Arnold Schwarzenegger a 2 Dream World Basic instinct Sharon Stone Total recall Arnold Schwarzenegger Stars_in Basic instinct Universal Studio Total recall Universal Studio Owns 12

A little of math before continuing • E, F are two sets – R E x F: R is a binary relation from E to F • R is a set whose member is a pair (e, f) where e is a member of E and f is a member of F • R could be empty, could be equal the Cartesian product of E and F – R is many to one relation if for each e in E there is at most one element (e, f) in R – R is one to one: many to one from E to F and many to one from F to E – R is many to many: not many to one from E to F and not many to one from F to E 13

many-one one-many-many 14

Multiplicity of Binary E/R Relationship • R is a binary relation from E to F – One to many – One to one – Many to many • Representing using arrow in the connection between entity set and relationship – Arrow entering an entity set represents the ‘one’ in the above description 15

name address year title Stars_in Movies length y n a m y many-one Owns film type Stars name Studios address 16

Multiway Relationships • Easy to handle in E/R diagram: connect all related entity sets with the relationship (Note: weakening the relationship (movie, studio)) Stars Movies Contracts Studios Contracts relationship: (studio, star, movie) 17

Roles in Relationship • An entity set can appear more than one times in a relationship • E/R diagram: labeling the arc connecting the entity set and the relationship Movies Sequel Original Sequel_of 18

Another example Stars Movies Contracts Producing studio Studio of star Studios 19

Attributes on Relationships • How to record the salary of a star in a contract? Stars Movies Contracts Studios ? salary ? ? 20

Attributes on Relationships • How to record the salary of a star in a contract? Ans: attribute of Contracts! Stars Movies Contracts salary Studios 21

name address year Stars title Movies Contracts * salary * length film type name Studios address The complete diagram of the Contracts relationship. Can we live without *? 22

name address year Stars title Movies length Contracts Salaries film type salary name Studios address 23

Eliminating Attributes on Relationship • Introducing a new entity set whose attributes are the attributes attached to the relationship • Connecting the new entity set to the relationship, with the arrow pointed to the new entity set 24

Converting Multiway Relationship to Binary Relationship • Useful when the language for defining the database structure does not allow multiway relationship. • Simple: – Introducing a new entity set representing the relationship – Introducing binary relation between the new entity set and the old ones which are connecting to the relationship 25

Stars Movies Contracts Studio of star Producing studio Before Studios Stars Movies After Star_of Movie_of Contracts Producing studio Studio of star Studios 26

Subclasses in the E/R Model • • Subclass: common in real-life Represented by the isa relationship isa is one-one relationship In E/R diagram: – draw as a triangle – no arrows into the entity sets 27

year Stars Voices Cartoons title length film type Movies weapon isa Murder Mysteries At home: read example 2. 10 and 2. 11. 28

Design Principles • • • Faithfulness Avoiding redundancy Simple Select the right relationships Select the right kind of element 29

Faithfulness • Entity sets and attributes should reflect reality. • Relationships are created only if they make sense given what we know about the domain/application. • Example: – Stars_in relationship should be many-many – Teaches relationship between Courses and Instructors? What? How? 30

No Redundancy • • Motto: Stored everything only one! Space reason (less serious) Consistency (serious) Example: Add an attribute studio. Name to the entity set Movies while having the relationship Owns between Movies and Studios – consequences: – More space (obvious: studio name stored twice) – Change in ownership of a movie change in the Owns relationship and the Movies entity set 31

Simplicity • Do only whatever is necessary! • Example: Owns Movies Studios is better than Movies Represents Holdings Owns Studios 32

Right Relationships • Should we represent every possible relationships? NO: due to space & redundancy requirements get only the necessarily one • How? Consider the assumptions, identify those that cannot be deduced from or represented by others 33

name address year title Stars_in Movies Contracts Owns length film type Stars name Studios address Assumption: a star can plays in a movie if there is a contract involving the star and the movie Stars_in is redundant 34

name address year title Stars_in Works_for Movies Owns length film type name Studios address Question: Would it make sense to have a relationship Works_for? Depending … 35

Right Kind of Element • Attribute vs. Entity set/Relationship • Example: address – in the examples so far: attribute – better as an entity with attributes such as street, number, zip code, state 36

Example: What happens if we replace Studios by its two attributes? redundancy, losing information (if a studio does not own a movie its address is lost) name address year title Stars_in Movies length film type Stars name Owns Studios address 37

When to use attribute for entity set E? • All relationships connecting to E must have an arrow entering E. • Attributes for E must collectively identify an entity. If there are more than one attributes then they must not depend on each other. • No relationship involves E more than one. 38

Converting from Entity sets to Attributes E A R F B F A B 39

Converting from Entity sets to Attributes E R A B F F R F F 40