![Page 1: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/1.jpg)
IS 4420Database Fundamentals
Chapter 3:Modeling Data in the
Organization
Leon Chen
![Page 2: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/2.jpg)
2
Systems Development Life
Cycle Project Identification
and Selection
Project Initiation and Planning
Analysis
Physical Design
Implementation
Maintenance
Logical Design
Enterprise modeling
Conceptual data modeling
Logical database design
Physical database design and definition
Database implementation
Database maintenance
Database Database Development Development
Process Process
![Page 3: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/3.jpg)
3
Overview What is data modeling Importance of data modeling Business rules Entity-relationship model: entities,
relationships, and attributes
![Page 4: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/4.jpg)
4
Conceptual Data Modeling
Business Rules-Data names-Data definitions
Conceptual Data Model(Entity-Relationship Diagram)
![Page 5: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/5.jpg)
5
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
![Page 6: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/6.jpg)
6
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
![Page 7: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/7.jpg)
7
Entity-Relationship Model A logical representation of the data for an
organization or for a business area 3 constructs:
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
STUDENT
Name
Registers
![Page 8: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/8.jpg)
8
Sample E-R Diagram (Figure 3-1)
Supplier_ID
Supplier_Name
Supplier_Address
Attribute
![Page 9: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/9.jpg)
9
Relationship degrees specify number of entity types involved
Entity symbols
A special entity that is also a relationship
Relationship symbols
Relationship cardinalities specify how many of each entity type is allowed
Attribute symbols
![Page 10: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/10.jpg)
10
Entity Person: EMPLOYEE, STUDENT Place: WAREHOUSE, COUNTRY Object: BUILDING, MACHINE Event: SALE, REGISTRATION Concept: ACCOUNT, COURSE
EMPLOYEE DEPENDENT
Strong entity Weak entityAssociative entity
CERTIFICATE
![Page 11: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/11.jpg)
11
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)
![Page 12: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/12.jpg)
12
Inappropriate entities
System userSystem user System outputSystem output
Appropriate entities
Figure 3-4
![Page 13: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/13.jpg)
13
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
![Page 14: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/14.jpg)
14
Required vs. Optional Attributes
Example: entity – ONLINE_ACCOUNT Required attributes (Not NULL)
• Account_ID• Password• Owner_Name
Optional attributes• Phone_Number• Password_Hint
![Page 15: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/15.jpg)
15
Figure 3-7 – A composite attribute
An attribute broken into component parts
![Page 16: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/16.jpg)
16
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
![Page 17: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/17.jpg)
17
Figure 3-19 – An attribute that is both multivalued and composite
This is an example of time-stamping.More examples?
![Page 18: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/18.jpg)
18
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
![Page 19: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/19.jpg)
19
Figure 3-9a Simple key attribute
The key is underlined
![Page 20: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/20.jpg)
20
Figure 3-9b Composite key attribute
Flight_Number Date
![Page 21: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/21.jpg)
21
Guidelines for Identifiers
Will not change in value Will not be null Substitute new, simple keys for long,
composite keys ? Game_Number
![Page 22: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/22.jpg)
22
Relationship Relationship Type vs. Relationship
Instance Degree of a relationship Cardinality of a relationship Associative Entity – combination of
relationship and entity
![Page 23: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/23.jpg)
23
Figure 3-11a
The relationship type is modeled as the diamond and lines between entity types
attribute of the relationship
![Page 24: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/24.jpg)
24
Relationship Instance is between specific entity instances
![Page 25: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/25.jpg)
25
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
Degree of a relationship is the number of entity types that
participate in it
![Page 26: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/26.jpg)
26
Cardinality of a Relationship 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
![Page 27: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/27.jpg)
27
![Page 28: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/28.jpg)
28
![Page 29: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/29.jpg)
29Note: a relationship can have attributes of its own
![Page 30: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/30.jpg)
30
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
![Page 31: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/31.jpg)
31
Cardinalities
Mandatory one
Mandatory many
Optional one
Optional many
11
18
![Page 32: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/32.jpg)
32
Figure 3-16a Basic relationship
Mandatory minimum cardinalities – Figure 3-17a
![Page 33: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/33.jpg)
33
Figure 3-17cOptional cardinalities with unary degree, one-to-one relationship
![Page 34: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/34.jpg)
34
max cardinality constraint
Maximum Cardinality Constraint
![Page 35: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/35.jpg)
35
Entities can be related to one another in more than one way
![Page 36: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/36.jpg)
36
Attributes can be transformed to relationships
Attributes or Relationship?
![Page 37: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/37.jpg)
37
Attributes should be transformed to relationships
Attributes or Relationship?
![Page 38: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/38.jpg)
38
Associative Entities It’s an entity – it has attributes
AND it’s a relationship – it links entities together
When should a 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
![Page 39: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/39.jpg)
39Figure 3-11b – An associative entity (CERTIFICATE)
![Page 40: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/40.jpg)
40
Bill of materials structure
![Page 41: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/41.jpg)
41
Figure 3-18 – Ternary relationship as an associative entity
![Page 42: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/42.jpg)
42
Strong vs. Weak Entities
Strong entities exist independently of other types of entities has its own unique identifier represented with single-line rectangle
Weak entity 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
![Page 43: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/43.jpg)
43
Strong entity Weak entityIdentifying relationship
![Page 44: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/44.jpg)
44
Figure 3-22a E-R diagram for Pine Valley Furniture
![Page 45: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/45.jpg)
45
Microsoft Visio Notation for Pine Valley Furniture
Different modeling software tools may have different notation for the same constructs
![Page 46: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/46.jpg)
46
Relationship degrees specify number of entity types involved
Entity symbols
A special entity that is also a relationship
Relationship symbols
Relationship cardinalities specify how many of each entity type is allowed
Attribute symbols
![Page 47: IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen](https://reader037.vdocuments.us/reader037/viewer/2022110206/56649cf75503460f949c7fcf/html5/thumbnails/47.jpg)
47
Sample E-R Diagram (Figure 3-1)
Supplier_ID
Supplier_Name
Supplier_Address
Attribute