Data modeling using E/R diagramming

Data modeling using E/R diagramming • Why analyze data? • Notation for E/R • Entities with attributes • Types of attributes • Relationships • Cardinality • History / things evolve • Total participation • Weak entities • Steen Jensen, autumn 2013

Why analyze data? Figure 3.1 from chapt. 3 in Elmasri • Insight in data - and structures of data • Create a structure in “unstructured” data • Prior to designing a database as a part of the systems development process.

ER modeling Figure 3.23 from chapt. 3 in Elmasri • ER stands for entityrelationship • ER modeling is a widely used technique for data analysis • Many notations (syntax) for ER diagrams • Different books  different notations • Diagrams can be drawn using Dia,Visio or a similar tool.

Entities with attributes Figure 3.7 from chapt. 3 in Elmasri • Characteristics of an entity • An important ”thing” in the world we model • Entities have attributes (data) • Similar to a class in OOA, but has no methods / functions • Naming convention: noun, singular • Notation (this book) • Entity: Rectangle • Attribute: Oval • Every “instance” of the entity must be unique • Identified by a key, one or more attributes • Notation for keys: Underline the name of the attribute(s)

Types of attributes • Simple or composite attributes • Simple (atomic) e.g. age • Notation: oval • Composite e.g. address = street + number + ZIP code + town • Notation: Oval connected to several ”sub” ovals.

Types of attributes II Figure 3.7 from chapt. 3 in Elmasri • Single valued or multi-valued • Single valued e.g. CPR • Multi-valued e.g. phone number • One person may have several phones • Notation: Double oval • An attribute may be composite and multi-valued!

Exercise in attributes • Take a look at figure 3.7 from slide 6 • Start by yourself identifying the following terms in the diagram (5-10 min.): • Simple (atomic) • Composite • Multi-valued • Continue discussing your solution with the three other students at your table - use RoundRobin (Danish: OrdetRundt), where you try to agree on a mutual solution (10-15 min.) • Finally the solution should be presented/discussed in class

Types of attributes III Figure 3.15 from chapt. 3 in Elmasri, partly • Derived: Calculated from other attributes e.g. sum or average • Notation: Oval with a dashed border • An attribute with no value has value NULL • Values does not exist • Value unknown

Relationships • Relationships between entities • Notation: rhomb (“diamond”) • Similar to association in OOA • Relationships may have attributes • Notation: Oval

Relationships II • Binary relationship • Relationship between 2 entities (normal) • Relationships, higher degree • Relationship between more entities (rare) • Recursive relationships • Relationship of 2 entities belonging to the same entity type e.g. an employee with a relation to a boss (another employee)

Cardinality • 1-1 very rare • 1 entity of type A has a relation to at most 1 entity of type B and vice versa • 1 husband ~ 1 wife • 1-many, 1-N very common • 1 entity of type A may have relation to many entities of type B, not vice versa. • 1 department ~ many employees

Cardinality II • Many - many, N-M common • 1 relation of type A may have relations to many entities of type B and vice versa. • 1 employee ~ many projects • 1 project ~ many employees

Cardinality notation • Step into the entity type and look at the other entity through the relation. • How many entities of the other type do you see? • Write the number on far end of the relation.

Exercise in relationships & cardinality • Take a look at figure 3.27 page 97 in the slides from Elmasri (physical page 43) – approx. 30 minutes • Start by pairing up with the student next to you and suggest the cardinality for each of the 10 cases • Also make a quick sketch for each case, so you know where to put the cardinalities • Share and discuss your solution with the other pair of students at your table • Finally the solution should be presented/discussed in class

(min, max) cardinality NB!!! • Often you’re only concerned with max. cardinalities • Sometimes you want both (min, max) cardinality. • Note: Cardinalities placed in the other end of the relation!!!

History – things evolve • History: More generations of relations • Cardinality goes up • 1 man ~ many wives [not simultaneously] • Put a timestamp / period on the relation (attribute on the relation) • Relation marriage: start_date, end_date • end_date may be NULL :-)

Total participation • Ordinary relation • An entity may be related to another entity • Total participation • En entity must be related to another entity. • Min. cardinality 1 (not 0) • Notation: Double line on the relation

Weak entities • An entity that is dependent on some other entity • If the ”strong” entity is deleted so is the weak entity • Notation: • Weak entity: Rectangle with double borders • Identifying relationship: Rhomb with double borders (shows the strong entity)

Weak entities II • A weak entity has a partial key • Not unique • Unique in combination with the key from the ”strong” entity • Notation: Dashed line under the partial key

Best practice • Procedure (best practice) • find entities and [easy] attributes • relationships • cardinalities • keys • weak entities (if any) • total participation (if any)

Assignment: Make E/R diagram for Amazon (see also week plan) Make an E/R diagram of the data behind Amazon http://www.amazon.co.uk Focus on books only Use a tool such as Dia (http://dia-installer.de/ ), Visio or a similar Start today and continue at home, so you can present a solution next time in class – upload your diagram in jpg format to the folder called ER solutions for Amazon in studie.rhs.dk

Data modeling using E/R diagramming