CSC 742 Database Management Systems
Topic #4: Data Modeling
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Phases of Database Design
Requirement Collection/Analysis Functional Requirements Functional Analysis Database Requirements Conceptual Design Conceptual Schema Logical Design Logical Schema Transaction Implementation Application Program
Spring 2002
High-Level Transaction Spec. Application Program Design
Physical Design Internal Schema
2
CSC 742: DBMS by Dr. Peng Ning
Part A: Entity-Relationship Model
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
What is ER Model About?
Structure of the data Entities and relationships between (among) entities Constraints Conditions that the entities and relationships must satisfy. Key constraint Domain constraint Structural constraint
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
ER Concepts
Entities Relationships Attributes
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Attributes
Atomic vs. composite Single- vs. multivalued Stored vs. derived Complex Attributes
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Null Values
Need Meanings not applicable unknown: missing or questionable existence
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Entities
Entity type (intension): e.g., Employee or Dept A collection of entities that have the same attributes Entity instance: e.g., Fred or Payroll Entity set (extension): e.g., {Fred, Bob, ...}
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Keys
An intension corresponds to all possible extensions Superkey: a set of attributes that are unique for an entity type (i.e., for all possible extensions) Key: a minimal superkeyfewer attributes won't be unique An entity type may have multiple keys
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
Relationships
Relationship types: e.g., works-in Relationship instances: e.g., Fred works-in Payroll
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
10
Relationship Properties
A relationship type associates entity types typically binary or ternary recursive may have attributes
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
11
Entity Types
Participate in relationship types Have roles in those relationship types
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
12
Cardinality
Cardinality constraints: number of relationship instances in which an entity instance may feature 1:1 1:N M:N
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
13
Achtung!
Don't confuse 1:N with N:1 Some notations, especially for O-O modeling, write the cardinalities differently
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
14
Inferring Cardinalities
We can construct paths between entity types These paths represent relationships composed from series of the existing relationships Their cardinalities can be inferred
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
15
Fan Traps
Situations where the inferred, i.e., implied, cardinality is weaker than the actual cardinality
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
16
Participation
Participation constraints: whether each entity instance must feature in some relationship instance total: yes partial: no
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
17
Chasm Traps
When the composed relationship, i.e., path, has a weaker participation constraint than is actual
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
18
Weak Entity Types
No key of its own attributes Must participate in a total relationship Another participant of the relationship becomes the owner Key = owner's key + partial key
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
19
Summary of ER Diagram Notations
Entity Weak Entity Relationship Identifying Relationship Attribute Key Attribute Multi-valued Attribute
Spring 2002 CSC 742: DBMS by Dr. Peng Ning 20
10
Summary of ER Diagram Notations
Composite Attribute
Derived Attribute E1
1
R
N
E2 E2 E
Total Participation of E2 in R Cardinality Ratio 1:N for E1:E2 In R
Structural Constraint (min,max) on Participation of E in R
21
E1 R
Spring 2002
R
(min,max)
CSC 742: DBMS by Dr. Peng Ning
Part B: Enhanced ER Model
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
22
11
Why Do We Need EER
ER modeling is sufficient for representing many database schemas for traditional database applications. Recent applications require additional semantic data modeling concepts Class/subclass relationship Type inheritance Specialization and generalization.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
23
Subclass-Superclass
Subclasses: Further refinement (grouping) of a (super)class Attributes are inherited Class/subclass relationship is different from the relationship in ER modeling.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
24
12
Specialization
Specialization: The process of defining a set of subclasses of an entity type Top-down conceptual refinement Allows us to Define a set of subclasses of an entity type Establish additional specific attributes with each subclass Establish additional specific relationship types between each subclass and other entity types or other subclasses.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
25
Generalization
Generalization: creating a superclass by combining classes bottom-up conceptual synthesis Can be viewed as the inverse of the specialization process.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
26
13
Classification
Predicate-based: when a defining predicate determines the subclass of which a given instance is member Attributed-based: when the predicate applies only on an attribute User-defined: when the user decide the subclass membership Disjoint vs. overlapping Total vs. partial
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
27
Constraints on Specialization/Generalization
Disjointness constraint The subclasses of the specialization must be disjoint. Specified by (d) Otherwise, the subclasses may overlap. Specified by (o)
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
28
14
Constraints on Specialization/Generalization
Completeness constraint Total Specialization Partial Specialization Disjointness and completeness constraints are independent. Superclass identified from generalization is usually total.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
29
Rules
Delete from superclass delete from all subclasses Insert into predicate-based superclass insert where predicate holds Insert into total superclass insert into a subclass can't reasonably be done unless a predicate is specified
CSC 742: DBMS by Dr. Peng Ning
Spring 2002
30
15
Structure
Hierarchy: single inheritance Lattice: multiple inheritance Shared subclass Attribute inheritance Single inheritance: trivial Multiple inheritance
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
31
Union Types
Category A subclass of the Union of some entity types A category has two or more super-classes Different from generalization.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
32
16
Aggregation
Combining objects to form a composite object. Three types of aggregations Aggregate attribute values of an object to form the object Represent an aggregation relationship as an ordinary relationship Combine objects that are related by a relationship into a higher-level aggregate object.
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
33
Association
Associate objects from several independent classes. Not quite aggregation because deleting an entity instance doesn't destroy the instances it is composed of
Spring 2002
CSC 742: DBMS by Dr. Peng Ning
34
17
