Database Processing Eighth Edition The Entity Relationship Model
Database Processing Eighth Edition The Entity. Relationship Model Chapter 3 David M. Kroenke 1 © 2002 by Prentice Hall
Data Modeling • Process of creating a logical representation of the structure of the database • The most important task in database development 2 © 2002 by Prentice Hall
The Data Model • A data model defines and graphically depicts the data structure and relationships among the data • A vocabulary and tool-set for creating the user’s data model 3 © 2002 by Prentice Hall
Data Modeling Creation • Interviewing users • Documenting requirements • Building a users data model – Using Entity-Relationship Model • Building a database prototype • A process of inference – Working backwards 4 © 2002 by Prentice Hall
Business Rules • Statements that define or constrain some aspect of the business • Assert business structure • Control/influence business behavior • Expressed in terms familiar to end users • Automated through DBMS software 5 © 2002 by Prentice Hall
A Good Business Rule is: • • Declarative – what, not how Precise – clear, agreed-upon meaning Atomic – one statement Consistent – internally and externally Expressible – structured, natural language Distinct – non-redundant Business-oriented – understood by business people 6 © 2002 by Prentice Hall
Common Data Models • Entity-Relationship Model • Semantic Object Model 7 © 2002 by Prentice Hall
Entity-Relationship Model (E -R Model) • An Entity-Relationship Model (E-R Model) consists of: – Entities – Attributes – Identifiers – Relationships 8 © 2002 by Prentice Hall
An Entity • An entity is an object that can be identified in the users’ work environment & that users want to track. • Entities of a given type are grouped into entity classes. • An entity instance is the representation of a particular entity. 9 © 2002 by Prentice Hall
An Entity Example 10 © 2002 by Prentice Hall
What Should an Entity Be? SHOULD BE: – An object that will have many instances in the database – An object that will be composed of multiple attributes – An object that we are trying to model SHOULD NOT BE: – A user of the database system – An output of the database system (e. g. a report) 11 © 2002 by Prentice Hall
Inappropriate Entities System user System output 12 © 2002 by Prentice Hall
Attributes • An attribute describes a characteristic of an entity • For example – An entity: Employee – Has attributes: • Employee. Name • Extension • Date. Of. Hire 13 © 2002 by Prentice Hall
Attributes • Classifications of attributes: – Simple versus Composite Attribute – Single-Valued versus Multivalued Attribute – Stored versus Derived Attributes – Identifier Attributes 14 © 2002 by Prentice Hall
Identifier • An identifier uniquely identifies a row in a table. • For an Employee, the Social. Security. Number may serve as the Indentifier. 15 © 2002 by Prentice Hall
Characteristics of Identifiers • Will not change in value • Will not be null 16 © 2002 by Prentice Hall
Relationships • A relationship describes how one or more entities are related with each other. • Relationship Classes are associations among entity classes • Relationship Instances are associations among entity instances • Relationships can have attributes 17 © 2002 by Prentice Hall
Degree of Relationship • Degree of relationship is number of entity classes in the relationship • Binary relationships (Degree 2) are very common Degree 2 Degree 3 18 © 2002 by Prentice Hall
Relationship Cardinality • Entity-Instance Participation in relationships is shown by – maximum cardinality – minimum cardinality 19 © 2002 by Prentice Hall
Maximum Cardinality • The maximum cardinality indicates/depicts the maximum number of instances involved in a relationship. • Alternatives include – 1: 1 (one-to-one) – 1: N (one-to-many) – N: M (many-to-many) 20 © 2002 by Prentice Hall
Relationship Examples Showing Maximum Cardinality Alternatives Employee has just 1 Auto, and vice- versa Dorm can have many students, Student can have just 1 dorm Student can be in many clubs, Club can have many students 21 © 2002 by Prentice Hall
Minimum Cardinality • The minimum cardinality indicates/depicts whether participation in the relationship is mandatory or optional. • Alternatives include – 0 (optional) – 1 (mandatory) 22 © 2002 by Prentice Hall
A Relationship Example Showing Minimum and Maximum Cardinality Minimum Cardinality: Dormitory must have at least 1 Student not required to be in a Dorm Maximum Cardinality: Dormitory can have many Students Student can be in at most 1 Dorm 23 © 2002 by Prentice Hall
A Recursive Relationship • A recursive relationship is when an entity has a relationship with itself. Employee 1: N Manages 24 © 2002 by Prentice Hall
Showing Attributes in Entity. Relationship Diagrams Note that relationship Dorm-Occupant has an attribute (Rent). 25 © 2002 by Prentice Hall
Entity-Relationship Diagram (E-R Diagram) • An entity-relationship diagram (E-R Diagram) is a graphical representation of the E-R model using a set of ‘somewhat’ standardized conventions 26 © 2002 by Prentice Hall
An Entity-Relationship Diagram (E-R Diagram) Example 27 © 2002 by Prentice Hall
Weak Entity • A weak entity is an entity whose instance survival depends (logically) on an associated instance in another entity (strong entity). Rounded Corners signify weak entities. 28 © 2002 by Prentice Hall
ID-dependent Entity • Where the identifier of one entity includes the identifier of another. 29 © 2002 by Prentice Hall
Subtype Entities • Some entities may have many common attributes and a few unique attributes. • The common attributes may be grouped together in a supertype entity and the unique attributes may be grouped together in a subtype entity. 30 © 2002 by Prentice Hall
CLIENT with Subtype Entities Indicates subtype 31 © 2002 by Prentice Hall
E-R Diagram Computer Assisted Software Engineering (CASE) Tools • Several Computer Assisted Software Engineering (CASE) Tools exist to help create E-R Diagrams and the resulting physical database elements. Products include: – IEW – IEF – DEFT – ER-WIN – Visio 32 © 2002 by Prentice Hall
Unified Modeling Language (UML) • The Unified Modeling Language (UML) is a set of structures and techniques for modeling and designing object-oriented programs (OOP) and applications. • A primary difference between UML & E-R Diagrams is that the UML representation includes information about object constraints and methods 33 © 2002 by Prentice Hall
Database Processing Eighth Edition The Entity. Relationship Model Chapter 3 David M. Kroenke 34 © 2002 by Prentice Hall
- Slides: 34