Transcript Introduction to Database

IS 4420
Database Fundamentals
Chapter 3:
Modeling Data in the
Organization
Leon Chen
Systems Development
Life Cycle
Project Identification
and Selection
Project Initiation
and Planning
Database
Development Process
Enterprise modeling
Conceptual data modeling
Analysis
Logical Design
Physical Design
Implementation
Maintenance
Logical database design
Physical database design
and definition
Database implementation
Database maintenance
2
Overview




What is data modeling
Importance of data modeling
Business rules
Entity-relationship model: entities, relationships,
and attributes
3
Conceptual Data Modeling
Business Rules
-Data names
-Data definitions
Conceptual Data Model
(Entity-Relationship Diagram)
4
Data Modeling is the Most Important



Input for follow-up processes
Data rather than processes are the most
complex
Data tend to be more stable than business
processes
5
Business Rules

Statements that define or constrain some
aspect of the business. For example:
Friday is business casual dress day
 Students can only register a course if
prerequisite is successfully completed


Automated through DBMS software
6
Entity-Relationship Model


STUDENT
A logical representation of the data for an organization
or for a business area
3 constructs:

Name

Registers


Entity - person, place, object, event, concept (often
corresponds to a row in a table)
Attribute - property or characteristic of an entity (often
corresponds to a field in a table)
Relationship – link between entities (corresponds to primary
key-foreign key equivalencies in related tables)
Type vs. instance
•
•

Entity type: Instructor
Entity instance: Leon Chen
Entity-Relationship Diagram


A graphical representation of entity-relationship model
Also called E-R diagram or just ERD
7
Sample E-R Diagram (Figure 3-1)
Attribute
Supplier_ID
Supplier_Name
Supplier_Address
8
Relationship symbols Attribute symbols
Entity symbols
A special entity that is
also a relationship
Relationship degrees
specify number of
entity types involved
Relationship
cardinalities specify
how many of each
entity type is allowed
9
Entity





Person: EMPLOYEE, STUDENT
Place: WAREHOUSE, COUNTRY
Object: BUILDING, MACHINE
Event: SALE, REGISTRATION
Concept: ACCOUNT, COURSE
EMPLOYEE
DEPENDENT
Strong entity
Weak entity
CERTIFICATE
Associative entity
10
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
Figure 3-4
Inappropriate entities
System user
System output
Appropriate entities
12
Attribute


Attribute - property or characteristic of an
entity type
Classifications of attributes:
Required vs. Optional Attributes
 Simple vs. Composite Attribute
 Single-Valued vs. Multivalued Attribute
 Stored vs. Derived Attributes
 Identifier Attributes - keys

13
Required vs. Optional Attributes

Example: entity – ONLINE_ACCOUNT

Required attributes (Not NULL)
•
•
•

Account_ID
Password
Owner_Name
Optional attributes
•
•
Phone_Number
Password_Hint
14
Figure 3-7 – A composite attribute
An attribute
broken into
component parts
15
Figure 3-8 – Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
Derived
from date employed and current date
Multivalued:
an employee can have
more than one skill
16
Figure 3-19 – An attribute that is both multivalued and composite
This is an example of
time-stamping.
More examples?
17
Identifiers (Keys)



Identifier (Key) - An attribute (or
combination of attributes) that uniquely
identifies individual instances of an entity
type
Candidate Key – an attribute that could be
a key. Examples (for STUDENT, PERSON)?
Simple Key versus Composite Key
18
Figure 3-9a Simple key attribute
The key is underlined
19
Figure 3-9b Composite key attribute
Flight_Number
Date
20
Guidelines for Identifiers



Will not change in value
Will not be null
Substitute new, simple keys for long,
composite keys

?  Game_Number
21
Relationship




Relationship Type vs. Relationship
Instance
Degree of a relationship
Cardinality of a relationship
Associative Entity – combination of
relationship and entity
22
Figure 3-11a
attribute of the relationship
The relationship type is modeled as the
diamond and lines between entity types
23
Relationship Instance is between
specific entity instances
24
Degree of a relationship is the number
of entity types that participate in it
One entity
related to
another of
the same
entity type
Entities of
two different
types related
to each other
Entities of three
different types
related to each
other
25
Cardinality of a Relationship

One-to-One


One-to-Many


Each entity in the relationship will have exactly one
related entity
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
26
27
28
Note: a relationship can have attributes of its own
29
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. Examples?



If zero, then optional
If one or more, then mandatory
Maximum Cardinality. Examples?

The maximum number
30
Cardinalities
Mandatory one
Mandatory many
11
Optional one
Optional many
18
31
Figure 3-16a Basic relationship
Mandatory minimum cardinalities – Figure 3-17a
32
Figure 3-17c
Optional cardinalities with unary degree, one-to-one relationship
33
Maximum Cardinality Constraint
max cardinality
constraint
34
Entities can be related to one another in more than one way
35
Attributes or Relationship?
Attributes can be transformed to relationships
36
Attributes or Relationship?
Attributes should be transformed to relationships
37
Associative Entities
entity

It’s an

AND it’s a

When should a
– it has attributes
relationship
– it links entities together
relationship with attributes be an
associative entity?





All relationships for the associative entity should be many
The associative entity could have meaning independent of the
other entities
The associative entity should have at least one or more
attributes other than the identifier
The associative entity may participate in other relationships
other than the entities of the associated relationship
Ternary relationships should be converted to associative entities
38
Figure 3-11b – An associative entity (CERTIFICATE)
39
Bill of materials structure
40
Figure 3-18 – Ternary relationship as an associative entity
41
Strong vs. Weak Entities

Strong entities




Weak entity




exist independently of other types of entities
has its own unique identifier
represented with single-line rectangle
dependent on a strong entity…cannot exist on its own
does not have a unique identifier
represented with double-line rectangle
Identifying relationship


links strong entities to weak entities
represented with double line diamond
42
Strong entity
Identifying relationship
Weak entity
43
Figure 3-22a
E-R diagram for Pine
Valley Furniture
44
Microsoft Visio
Notation for Pine
Valley Furniture
Different modeling
software tools may
have different notation
for the same
constructs
45
Relationship symbols Attribute symbols
Entity symbols
A special entity that is
also a relationship
Relationship degrees
specify number of
entity types involved
Relationship
cardinalities specify
how many of each
entity type is allowed
46
Sample E-R Diagram (Figure 3-1)
Attribute
Supplier_ID
Supplier_Name
Supplier_Address
47