Transcript Document

Chapter 3
Database Development Process
7/17/2015
Database Concepts
1
Objectives
• Definition of terms
• Importance of data modeling
• Write good names and definitions for entities,
relationships, and attributes
• Distinguish unary, binary, and ternary relationships
• Model different types of attributes, entities,
relationships, and cardinalities
• Draw E-R diagrams for common business situations
• Convert many-to-many relationships to associative
entities
• Model time-dependent data using time stamps
7/17/2015
Database Concepts
2
SDLC Revisited – Data
Modeling is an Analysis Activity
Project Identification
and Selection
Project
Project Initiation
Initiation
and
and Planning
Planning
Database activity –
conceptual data modeling
Analysis
Analysis
Logical Design
Physical Design
Implementation
Purpose – thorough analysis
Deliverable – functional system specifications
7/17/2015
Database Concepts
Maintenance
3
Database Analyst Responsibilities
• Identify and understand those rules that
govern data
• Represent those rules so that they can
be unambiguously understood by
– information systems developers and
– users
• Implement those rules in database
technology
7/17/2015
Database Concepts
4
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
7/17/2015
Database Concepts
5
A Good Business Rule is:
• Declarative
• Expressible
– what, not how
– validated
– structured
– natural language
• Precise
• Distinct
– clear, agreed-upon
meaning
– non-redundant
• Business-oriented
• Atomic
– understood in the
language used by
business people
– Indivisible, one rule
• Consistent
– internally and externally
7/17/2015
Database Concepts
6
A Good Data Name is:
• Related to business, not technical,
characteristics
• Meaningful and self-documenting
• Unique
• Readable
• Composed of words from an approved
list
• Repeatable
7/17/2015
Database Concepts
7
Data Definitions
• Explanation of a term or fact
– Term
• word or phrase with specific meaning to the
business
• Key words such as course, instructor, student
– Fact
• association between two or more terms
• Example
Association to term
term
– A course is a module of instruction in a
particular subject area.
Association to term
7/17/2015
Database Concepts
8
Data Definitions
• Guidelines for good data definition
– Gathered in conjunction with systems
requirements
– Accompanied by diagrams
– Iteratively created and refined
– Achieved by consensus
7/17/2015
Database Concepts
9
E-R Model Constructs
• Entity type
• Relationship instance
– collection of entities that
share common properties or
characteristics
– Weak entity
• Existence depends on
another entity
– Strong entity
• Relationship type
– category of
relationship…link between
entity types
• Exists independently
• Entity instance
– A single occurrence of an
entity type
– Person, place, object, event,
concept (the row in a table)
7/17/2015
– link between entities
(corresponds to primary
key-foreign key
equivalencies in related
tables)
• Attribute
– property or characteristic of
an entity type (a field in a
table)
Database Concepts
10
Basic ER Notation
Entity
symbols
Attribute
symbols
Relationship
symbols
A special
entity that
is also a
relationship
Relationship
degrees
specify
number of
entity types
involved
7/17/2015
More about these later…
Relationship
cardinalities specify
how many of each entity
type is allowed
Database Concepts
11
Sample E-R Diagram
7/17/2015
Database Concepts
12
Sample E-R Diagram (Figure 3-1)
7/17/2015
Database Concepts
13
What Should an Entity Be?
• Should:
– Be an object that will have many instances in the
database
– Be an object that will be composed of multiple
attributes
– Be an object that we are trying to model
• Should Not Be:
– Be a user of the database system
– Be an output of the database system (e.g. a
report)
7/17/2015
Database Concepts
14
Inappropriate entities
System
user
Inappropriate
entities
System
output
Appropriate
entities
7/17/2015
Database Concepts
15
Attributes
• Is a property or characteristic of an
entity type
7/17/2015
Database Concepts
16
Attributes
• Classifications of attributes:
– Required versus Optional Attributes
– Simple versus Composite Attribute
• LName vs. Address (Street, City, ST, Zip)
– Single-Valued versus Multivalued Attribute
• Student vs. Employee-Skill (C++, VB)
– Stored versus Derived Attributes
• SSN vs. YearsInService
– Identifier Attributes
• MaxNoPassengers vs. FlightID(Number, Date)
7/17/2015
Database Concepts
17
Identifiers (Keys)
• Identifier (Key)
– An attribute (or combination of attributes) that
uniquely identifies individual instances of an entity
type
– Simple Key vs. Composite Identifier
• MaxNoPassengers vs. FlightID(Number, Date)
• Candidate Identifier (Key)
– an attribute that could be a key…satisfies the
requirements for being a key
– More about these in Chap 5
7/17/2015
Database Concepts
18
Characteristics of Identifiers
• Will not change in value
• Will not be null
• No intelligent identifiers
– Ex. containing locations or people that
might change
• Substitute new, simple keys for long,
composite keys
7/17/2015
Database Concepts
19
A Composite Attribute
An attribute
broken into
component parts
Figure 3-8 Entity with multivalued attribute (Skill)
and derived attribute (Years_Employed)
Multivalued
an employee can have
more than one skill
7/17/2015
Derived
from date
employed and
current date
Database Concepts
20
Simple & Composite
Key Attribute
The identifier is boldfaced and underlined
7/17/2015
Database Concepts
21
Time-stamping Example
This attribute
that is both
multivalued and
composite
7/17/2015
Database Concepts
22
More on Relationships
• Relationship Types vs.
Relationship Instances
– The relationship type is
modeled as the diamond
and lines between entity
types
– The relationship instance is
between specific entity
instances
• Two entities can have
more than one type of
relationship between
them (multiple
relationships)
• Associative Entity
• Relationships attributes
– combination of
relationship and entity
– describe features pertaining to
the association between the
entities in the relationship
7/17/2015
Database Concepts
23
Degree of Relationships
• Degree of a relationship is the number
of entity types that participate in it
– Unary Relationship
– Binary Relationship
– Ternary Relationship
7/17/2015
Database Concepts
24
Figure 3-10 Relationship types and instances
a) Relationship
type
b) Relationship
instances
7/17/2015
Database Concepts
25
Degree of Relationships
One entity
related to
another of
the same
entity type
7/17/2015
Entities of
two different
types related
to each other
Database Concepts
Entities of three
different types
related to each
other
26
Cardinality of Relationships
• One-to-One
– Each entity in the relationship will have exactly
one related entity
• One-to-Many
– An entity on one side of the relationship can have
many related entities, but an entity on the other
side will have a maximum of one related entity
• Many-to-Many
– Entities on both sides of the relationship can have
many related entities on the other side
7/17/2015
Database Concepts
27
Cardinality Constraints
• Cardinality Constraints
– the number of instances of one entity that can or
must be associated with each instance of another
entity
• Minimum Cardinality
– If zero, then optional
– If one or more, then mandatory
• Maximum Cardinality
– The maximum number
7/17/2015
Database Concepts
28
Examples of Relationships of
Different Degrees (Unary)
7/17/2015
Database Concepts
29
Examples of Relationships of
Different Degrees (Binary)
7/17/2015
Database Concepts
30
Examples of Relationships of
Different Degrees (Ternary)
Note: a relationship can have attributes of its own
7/17/2015
Database Concepts
31
Examples of Cardinalities Constraints
Mandatory cardinalities
A patient history is
recorded for one and
only one patient
7/17/2015
A patient must have recorded
at least one history, and can
have many
Database Concepts
32
Examples of Cardinalities Constraints
One Optional/One Mandatory
A project must be
assigned to at least one
employee, and may be
assigned to many
7/17/2015
An employee can be assigned
to any number of projects, or
may not be assigned to any
at all
Database Concepts
33
Examples of Cardinalities Constraints
Optional cardinalities
A person is is
married to at most
one other person,
or may not be
married at all
7/17/2015
Database Concepts
34
Examples of Multiple Relationships
Employees & Departments
Entities can be related to one another in more than one way
7/17/2015
Database Concepts
35
Examples of Multiple Relationships
Professors and Courses (fixed lower limit constraint)
Here, min
cardinality
constraint is 2
7/17/2015
Database Concepts
36
Multivalued attributes represented as Relationships
simple
composite
7/17/2015
Database Concepts
37
Strong vs. Weak Entities
• Strong entities
– exist independently of other types of entities
– has its own unique identifier
– represented with single-line rectangle
• Weak entities
– dependent on a strong entity…cannot exist on its
own
– do not have a unique identifier
– represented with double-line rectangle
7/17/2015
Database Concepts
38
Identifying Relationships
• Identifying relationship
– links strong entities to weak entities
– represented with double-line diamond
7/17/2015
Database Concepts
39
Example of Weak Entity & its
Identifying Relationships
Strong entity
7/17/2015
Weak entity
Database Concepts
40
Associative Entities
• It is an entity
– it has attributes
• AND
• It is a relationship
– it links entities together
7/17/2015
Database Concepts
41
Associative Entities
• When should a relationship with
attributes be an associative entity?
– The associative entity
• relationships should be many
• could have meaning independent of the other entities
• preferably has a unique identifier, and should also have
other attributes
• may participate in other relationships other than the
entities of the associated relationship
– Ternary relationships should be converted to
associative entities
7/17/2015
Database Concepts
42
A Binary Relationship with an Attribute
Here, the date completed attribute pertains specifically to the
employee’s completion of a course…it is an attribute of the
relationship
7/17/2015
Database Concepts
43
Associative Entity
Associative entity is like a relationship with an attribute, but it
is also considered to be an entity in its own right.
Note that the many-to-many cardinality between entities in
Figure 3-11a has been replaced by two one-to-many
relationships with the associative entity.
7/17/2015
Database Concepts
44
Associate Entity
Bill-of-Materials Structure
This could just be a relationship with
attributes…it’s a judgment call
7/17/2015
Database Concepts
45
Ternary Relationship
as an Associate Entity
7/17/2015
Database Concepts
46
Microsoft Visio
Notation for Pine
Valley Furniture
E-R diagram
Different modeling
software tools may
have different
notation for the
same constructs
7/17/2015
Database Concepts
47
Naming Relationships
• A relationship name should be a verb
phrase
– Assigned_to, Teaches
• Avoid vague names
– Has or Is_related_to
7/17/2015
Database Concepts
48
Defining Relationships
• A relationship definition
– Explains what action is being taken and (possibly)
why it is important
– Gives examples to clarify the action
– Should explain
• any optional participation
• the reason for any explicit maximum cardinality other
than many
• any mutually exclusive relationships
• any restrictions on participation in the relationship
• whether an entity instance involved in a relationship
instance can transfer participation to another relationship
instance
7/17/2015
Database Concepts
49
Homework Assignment
• Homework Assignment 3
• Project Exercise
– Page 138, #1, #2
– Due next week
• In-class Activity
– Page 134, #18, Create an ERD and then
create an Access database to match your
ERD.
7/17/2015
Database Concepts
50
7/17/2015
Database Concepts
51