1
Data Modeling,
Entity-Relationship Diagram
PB007 Software Engineering I
Lukáš Daubner
daubner@mail.muni.cz
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram2
Entity-Relationship Diagram
̶ Data model
̶ Not a part of UML
̶ Representing the logical structure of relational database
̶ Its main components are:
̶ Entities
̶ Relations
̶ Attributes
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram3
Entity-Relationship Diagram
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram4
Two Worlds Collide
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram5
Two Worlds Collide
̶ World of Objects – Class Diagram
̶ Captures data and operations
̶ Classes are connected with different relationships with different semantics
̶ Objects have own dynamic lifecycle
̶ Manipulation through interaction
̶ World of Data – Entity-Relationship Diagram
̶ Captures just data
̶ Simple relationships
̶ Represents tables in relational database
̶ Manipulation through relational algebra
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram6
Object-Relational Mapping
̶ Conversion „between the worlds“
̶ Persistent class ~ Entity type (table)
̶ Object ~ Entity (table row)
̶ Class attribute ~ Entity attribute (table column)
̶ Association/Aggregation/Composition ~ Relation (connection via foreign keys)
̶ Inheritance ~ … (manual work needed, see following slides)
̶ Mapping is not always 1:1!
̶ Single class can be mapped to multiple tables
̶ And vice versa
̶ Not all classes are persistent (objects stored in database)
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram7
Example
Object-Relational Mapping
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram8
Object-Relational Mapping – Inheritance
̶ Each class becomes a table
̶ An attribute differentiates the subclass type
̶ One object instance in multiple tables
̶ More difficult data access
1:1 Mapping
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram9
Object-Relational Mapping – Inheritance
̶ All attributes in one table
̶ Some will have NULL value
̶ Breaks the 4.NF
̶ Suitable for small number of subclasses and few attributes
Merge to superclass
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram10
Object-Relational Mapping – Inheritance
̶ Superclass attributes are copied to non-abstract subclass tables
̶ Suitable if:
̶ Superclass has few attributes
̶ Many subclasses
̶ Subclasses have many attributes
Propagation to subclasses
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram11
Normal Forms
̶ Technique for data organization and good database design
̶ Elimination of repetitive data
̶ Reduction of table complexity
̶ Problem prevention
̶ E.g., update anomalies
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram12
1. Normal Form
Normal Forms
̶ Satisfies 0. NF (yes, it actually exists)
̶ Each attribute is atomic
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram13
2. Normal Form
Normal Forms
̶ Satisfies 1. NF
̶ No partial dependency
̶ Each non-key attribute are fully dependent on candidate keys
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram14
3. Normal Form
Normal Forms
̶ Satisfies 2. NF
̶ No transitive dependency
̶ Each non-key attribute is dependent on primary key (and candidate keys) only
̶ Non-key attributes are mutually independent
PB007 Software Engineering I — Data Modeling, Entity-Relationship Diagram15
You gotta do what you gotta do
Task for this week
̶ Process the feedback
̶ Create ERD based on the class diagram
̶ NB! Keep it consistent – you model the same system
̶ Normalize to 3. NF
̶ Based on the EDR create separate example violating 1. and 2. NF
̶ Do your part in peer review
̶ Link to roster is in study materials