Database Processing Eighth Edition The Entity Relationship Model

Data Modeling • Process of creating a logical representation of the structure of the

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

Entity-Relationship Model (E -R Model) • An Entity-Relationship Model (E-R Model) consists of: –

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 –

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

Characteristics of Identifiers • Will not change in value • Will not be null

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 Cardinality • Entity-Instance Participation in relationships is shown by – maximum cardinality –

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

Showing Attributes in Entity. Relationship Diagrams Note that relationship Dorm-Occupant has an attribute (Rent).

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.

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