Conceptual data modeling -- capturing the meaning of data from the viewpoint of the user

Concept

Definition

Model

A set of concepts to describe the structure of and operations on a database

Schema

A language or diagrammatic conventions that can be used to provide the modeling construct

Instance

A description of reality at a given point in time

Data modeling tools

  1. Entity-relationship diagram (1976 by Peter Chen)
  2. Object-oriented diagram

Basic constructs of the E-R model

Concept

Definition

Examples

Entity

A person, place, object, event or concept

Employee, department, building, sale, account

Relationship

An entity that serves to interconnect two or more entity types

Assignment (Employee-Department)

Attribute

A property or characteristic of an entity/relationship type

Employee_name, department_location, sale_date

Constraints

Guiding policies or rules that defines or restricts the structure and processing of a database

All business majors must have a GPA of 2.9 or above

Entity

  1. Entity type/set/class - a collection of entities that share common properties, Ei, not mutually disjoint, i.e., e Î E1, and e Î E2
  2. Entity instance - a single occurrence of an entity type, e
  3. Strong entity type - an entity that exists independently of other entity types
  4. Weak/ID-dependent entity type - an entity type whose existence depends on some other entity type, e.g., BUILDING-ROOM

Relationship

  1. Relationship type - association among entity types (Fig. 3-10a)
  2. Relationship instance - association among entity instances (Fig. 3-10b)
  3. Identifying relationship - the relationship between a weak entity type and its owner (Fig. 3-5)
  4. Gerund/Associative entity - a relationship that is represented as an entity type (Fig. 3-11b)
  5. Degree of a relationship - the number of entity types that participate in a relationship
  6. Cardinality constraint - specifies the number of instances of one entity that can be associated with each instance of another entity
  7. Subtype/Supertype relationship - applying specialization/generalization technique to capture the subset/superset relationship between two entity types (Fig. 4-1)

4 conditions for a gerund

  1. Related to the participating entity types in a "many" relationship
  2. Identified with a single-attribute identifier
  3. Has one or more attributes
  4. Participates in one or more relationships beyond the associated relationship

Degree of a relationship

  1. Unary/recursive - a relationship between the instances of a single entity type, e.g., Student-Room_With (Fig. 3-12a)
  2. Binary - a relationship between the instances of two entity types, e.g, Faculty-Teach-Course (Fig. 3-12b)
  3. Ternary - a relationship among the instances of three entity types, e.g., FACULTY-Offer-COURSE-Offer-DEPARTMENT

Cardinality constraints

  1. one-to-one, e.g, STUDENT-Assign-PARKING
  2. one-to-many, e.g., DEPARTMENT-Offer-COURSE
  3. many-to-many, e.g., STUDENT-register-COURSE

Attribute

  1. Simple/atomic attribute - an attribute that cannot be broken down into component parts e.g., Student_ID
  2. Composite attribute - an attribute that can be broken down into component parts, e.g., Student_Name (First, Middle, Last)
  3. Single-valued attribute - an attribute that can take on only one value for a given entity instance, e.g, Student_DateOfBirth
  4. Multi-valued attribute - an attribute that may take on more than one value for a given entity instance, e.g., Student_Major
  5. Base attribute - an attribute whose values are stored in the database, e.g., Student_Phone
  6. Derived attribute - an attribute whose values can be calculated from related attribute values, e.g., Student_GPA
  7. Identifier - an attribute (Simple) or combination of attributes (Composite) that uniquely identifies individual entity instance, e.g., Student_ID

Criteria for selecting identifiers

  1. Permanent
  2. Non-null
  3. Non-derived
  4. Simple

 Constraints

Business rules (Fig. 4-11)

Subtype/supertype rules

 

Basic E-R notation (Fig. 3-2)

Concept

Symbol

Entity

Rectangle

Weak entity

Double rectangle

Associative entity

Diamond within a rectangle

Relationship

Diamond

Identifying relationship

Double diamond

Cardinality

Crow's foot

Mandatory cardinality

Solid "|"(s) superimposed on the relationship line

Optional cardinality

A "0" superimpose on the relationship line

Subtype/Supertype

Circle

Direction of subtype/supertype

Open-end of the "U" points towards a supertype

Total specialization

Double line extending from a supertype

Disjoint rule

A "d" in the circle joining the supertype & its subtypes

Overlap rule

An "o" in the circle joining the supertype & its subtypes

Attribute

Ellipse

Multi-valued attribute

Double ellipse

Identifier

Underlined

Derived attribute

Broken ellipse

 

4 steps in designing a conceptual data model using the E-R diagram

Guidelines for identifying entities & attributes

Guidelines for defining relationships

Which of the following contains a redundant relationship?

Note:  Locate is a redundant relationship 

Which of the following should be defined as a ternary relationship?

 

 

Evaluating a conceptual data model

 

Example of an E-R diagram not meeting the expressiveness, clarity and readability criteria:

 

 

Example of an E-R diagram not meeting the clarity and readability criteria: