Chapter 2: Foundation Concepts

1
Data Modeling and Database Design

• Chapter 2 Foundation Concepts

2
Conceptual Data Modeling

• Overall activity of attempting to represent
  meaning is called semantic or conceptual modeling
  
• The need for conceptual modeling in database
  design
• Opportunity for enhanced user participation in
  the design process
• Merits that accrue from DBMS-independent modeling
  
• Ease of understanding of the big picture and the
  consequent facilitation of maintenance of schemas
  and applications in the long run

3
Conceptual Modeling Context
4
Conceptual Model

• A conceptual model involves
• A context, a setting in which the phenomena being
  modeled transpires
• A grammar that defines a set of constructs and
  rules for the phenomenon
• A method that describes how to use the grammar
• The product of conceptual modeling is the
  conceptual
• modeling script (e.g., an ER model, a NIAM model,
  
• a semantic object model).

5
Entity-Relationship (ER) Model

• Modeling grammar originally proposed by Peter
  Chen in 1976
• The most widely accepted data modeling grammar
  for conceptual design
• Chosen by ANSI in 1988 as the standard model for
  Information Resource Directory Systems (IRDSs)
• ER modeling grammar obeys the attributes of a
  semantic data modeling technique expressiveness,
  simplicity, minimalism, formality, and unique
  interpretation

6
Entity-Relationship (ER) Model (continued)

• Core pieces (units) of the ER modeling grammar
  (1) entities, (2) attributes, and (3)
  relationships

7
Units of the E-R Modeling GrammarEntity

• Entity type
• Conceptual representation of an object type
• A set of related attributes
• Has relationship(s) with other entity types
• Base entity type Weak entity type
• Entity An instance (occurrence) of an entity
  type
• Entity Set A collection (set) of entities (i.e.,
  entity instances) of an entity type
• Entity class A set of entity types that have
  shared properties

8
Units of the E-R Modeling GrammarAttribute
Conceptual representation of a property of an
object type object has properties and entity
has attributes
9
Attribute
10
Unique Identifier, Key Attribute and Non-Key
Attribute

• Unique identifier
• An attribute, atomic or composite, whose values
  are distinct for each entity in the entity set
• Key attribute
• Any attribute that is a constituent part of a
  unique identifier is a key attribute
• A key attribute is a proper subset of a unique
  identifier
• Non-key attribute
• Any attribute that is not a constituent part of
  (subset of) a unique identifier

11
An Example of an Entity Type
12
An Example of an Entity Type (Continued)

• Note various attribute characteristics in
  PATIENT
• Optional simple/atomic Date_of_ birth, Address
• Optional multi-valued Phone
• Optional simple/atomic derived Age
• Mandatory composite Pat_Name, Pat_ID
• Mandatory simple/atomic that serves as a unique
  identifier SSN
• Mandatory composite that serves as a unique
  identifier Pat_ID
• Complex Medical Profile

13
Entity and Attribute-Level Data Integrity
Constraints

• Data integrity constraints (or simply integrity
  constraints) are rules that govern behavior of
  data at all times in a database
• In other words, integrity constraints are
  technical expressions of the rules that govern
  the envorinment
• They must be preserved across all three tiers of
  data modeling
• Some constraints cannot be expressed explicitly
  in an ERD and are therefore carried forward in
  textual form (i.e., semantic integrity
  constraints)

14
Example
15
Entity and Attribute-Level Data Integrity
Constraints

• At the conceptual tier of data modeling, two
  types of data integrity constraints pertaining to
  entity types and attributes are specified
• The domain constraint imposed on an attribute to
  ensure that its observed value is not outside the
  defined domain
• Intra-entity constraints impose rules on groups
  of attributes within the same entity type
• The key (or uniqueness) constraint that requires
  entities of an entity type to be uniquely
  identifiable

16
Relational Business rule Integrity Constraints

• Between entity types other constraints exist.
• The referential integrity constraint imposes
  limits on the number of entity occurrences that
  may participate in the relationship.
• The business rule constraint places other,
  complex, rules on the data stored in the database.

17
Units of the E-R Modeling Grammar Relationship
Type

• Relationship Type
• Conceptualization of association between object
  types
• Binary (degree 2) ternary (degree 3) n-ary
  (degree n) unary recursive (degree 1)
• Structural constraints Multiplicity
• Cardinality ratio or Cardinality constraint
  connectivity
• 11 1n n1 mn
• Maximum cardinality
• Participation constraint
• Minimum cardinality
• Total participation existence dependency
• Partial participation

18
n-ary Relationships
19
n-ary Relationships (continued)

• Example 1.Professor Einstein teaching Physics
  using Introduction to Physics.
• Example 2. Professor Einstein teaching Physics
  using Principles of Physics.
• Example 3. Professor Einstein teaching Math
  using Principles of Calculus.
• Example 4. Professor Chu teaching Math using
  Introduction to Calculus.

20
n-ary Relationships (continued)

• Example 1. Dr. Fields prescribes Advil to treat
  Sharon Moore for a headache.
• Example 2. Dr. Fields prescribes Advil to treat
  Michelle Li for a headache.

21
n-ary Relationships (continued)
22
Role Names
23
Structural Constraints of a Relationship Type

• Two structural constraints define a relationship
  type
• Cardinality constraint (ratio) specifies the
  maximum number of instances of an entity type
  that relate to a single instance of an associated
  entity type through a binary relationship type
  (i.e., 11, 1n, nm maximum cardinality)
• Participation constraint is based on whether, in
  order to exist, an entity of that entity type
  needs to be related to an entity of the other
  entity type through a binary relationship type
  (i.e., total/mandatory or partial/optional
  minimum cardinality) total participation
  existence dependency

24
Cardinality Constraint (Ratio)
25
Cardinality Constraint (Ratio) (continued)
26
Cardinality Constraint (Ratio)(continued)
27
Participation Constraint
28
Participation Constraint (continued)
29
Cardinality Ratio and Participation Constraints
30
Cardinality Ratio and Participation
Constraints (continued)
31
Cardinality Ratio and Participation
Constraints (continued)
32
Cardinality Ratio and Participation
Constraints (continued)
33
Cardinality Ratio and Participation
Constraints (continued)
34
Cardinality Ratio and Participation
Constraints (continued)
35
Cardinality Ratio and Participation
Constraints (continued)
36
Cardinality Ratio and Participation
Constraints (continued)
37
Putting It All Together
38
Putting It All Together (continued)
39
Putting It All Together (continued)
40
Putting It All Together (continued)
41
Something To Think About

• Think of each entity type and relationship type
  as a separate table for right now
• For example

• Vendor
• VenID VenName
• Joe Inc
• Bill Inc

Supplies VenID ProdID Cost 2 25 7 1 24 8 1
25 7

• Product
• ProdID ProdName
• Soup
• Noodles
• Chocolate

42
Base/Strong vs. Weak Entity Types
43
Base/Strong vs. Weak Entity Types (continued)

• Base (or strong) entity types are those where the
  entities have independent existence (i.e., each
  entity is unique)
• A base entity type has a unique identifier
• Weak entity types are those where entities have a
  dependent existence (i.e., duplicate entity
  instance may be present)
• A weak entity type does not have a unique
  identifier
• A weak entity type has an identifying
  relationship with an identifying parent entity
  type
• A weak entity type has a partial key also
  known as a discriminator

44
Partial Key (Discriminator) Defined

• Partial Key - An attribute, atomic or composite,
  in a weak entity type, which in conjunction with
  a unique identifier of the parent entity type in
  the identifying relationship type, uniquely
  identifies weak entities is called the partial
  key of a weak entity type.

45
Sample Data Sets for BUILDING and APARTMENT
Building Data Set Bldg_no Floors Size (sq.
ft) Vacancy S51 3 15425 6 S52
1 3250 N51 3 15425
6 N52 3 16250 4 APARTMENT
Data Set Apt_no bedrooms bathrooms Size (sq.
ft) Rent 11 1 1
600 830 12
1 1
660 850 21
2
930 985 22
1 1
600 830 11 1
1 600
830 The first four apartments listed are
located in Building Number S51 while the fifth
apartment is located in Building Number N51.
Figure 2.23 Weak entity type an example
46
Exercises

• Draw a data model for each of the following
  problems
• A farmer can have many cows, but a cow belongs to
  only one farmer.
• A nation can have many states and a state many
  cities.
• A patient can have many physicians, and a
  physician can have many patients.
• A dairy farmer has several herds of cows. He has
  assigned each cow to a particular herd. In each
  herd, the farmer has one cow that is his
  favorite.
• For each data model, identify cardinality ratio,
  participation constraints, as well as domain and
  key constraints!

47
Vignette 1
48
Vignette 1 (continued)
49
Vignette 1 (continued)
Semantic Problems A college offers many courses
and also has several instructors. The
attribute Name occurs twice in the COLLEGE entity
type.
50
Vignette 1 (continued)
51
Vignette 1 (continued)
Another semantic problem An instructor is
capable of teaching a variety of courses, but
this is not shown in Figure 2.28b.
52
Vignette 1 (continued)
53
Vignette 1 (continued)
Syntactic problem While Figure 2.28c depicts
a relationship between courses and instructors, a
syntactical rule of the ER modeling grammar is
violated. A relationship between attributes of an
entity type is not permitted in the grammar.
54
Vignette 1 (continued)
55
Vignette 1 (continued)
The syntactic error in Figure 2.28c is corrected
by modeling COURSE and INSTRUCTOR as independent
entity types related to the COLLEGE entity type,
and establishing a relationship between the
COURSE and INSTRUCTOR entity types. In addition,
since courses are offered every term, Term is
modeled as an optional multi-valued attribute of
COURSE.
56
Vignette 1 (continued)
57
Vignette 1 (continued)
58
Vignette 1 (continued)
Figure 2.28f is good, but the fact that courses
are offered over the four terms, and in each term
one or more of the courses are offered, is yet to
be incorporated in the ER diagram. In
addition, the business rule that the same course
is never taught by more than one instructor in a
specific term is not incorporated in the
conceptual model either.
59
Vignette 1 (continued)
60
Vignette 1 (continued)
Note that a syntactic error is created in the
attempt to convey the business rule that says the
same course is never taught by more than one
instructor in a specific term. It is illegal
to express a relationship between the
relationship type Assigned and another
relationship type Offered.
61
Vignette 1 (continued)
62
Vignette 3
63
Vignette 3 (continued)
64
Vignette 3 (continued)

• Corrected semantic errors
• Phone in VENDOR is a multi-valued attribute.
• Part and Name are two distinct unique
  identifiers of PART.

65
Vignette 3 (continued)
66
Vignette 3 (continued)
67
Vignette 3 (continued)
68
Vignette 3 (continued)