Chapter 2
1
• Defi Defin nition of terms • I mporta portanc nce e of data mode odeling • Write rite good ood na names an and de definition tions s for for en entiti titie es, re a ons ps, an a r u es es • Disti stinguis uish un unary, binary, and ternary re relation tionsh shiips , , relationships, and cardinalities • Draw E-R E-R dia rams for for common business situation • Conve onvert rt ma many-to-m ny-to-ma any rel relation tionsh shiips to ass associ ocia ative tive entities • Model tim time-depe depend nde ent data data usi using ng tim time sta stam mps 2
Business Rule • aspect of the business • Contro ontroll/in /i nfluence business beh behavior vior • A re express xpresse ed in in term terms s fa familiar to end end use users • Govern how data data are stored stored and and ha handl ndle ed. • Ou Ourr DB A pp wi willl (h (hop ope efully) au auto tom mate
3
• The E/R mode odel is used to con construc structt a con conce ce tua tual data model – structurea structure and constraints technology independent.
4
Business Rule • aspect of the business • Contro ontroll/in /i nfluence business beh behavior vior • A re express xpresse ed in in term terms s fa familiar to end end use users • Govern how data data are stored stored and and ha handl ndle ed. • Ou Ourr DB A pp wi willl (h (hop ope efully) au auto tom mate
3
• The E/R mode odel is used to con construc structt a con conce ce tua tual data model – structurea structure and constraints technology independent.
4
• An E-R model is base based d on: – En Enttit itie ies s – Re Rellationships between entities – A ttrib ribu utes of entities and relationships • E-R dia iag gram – a graphical representation of an E-R model
5
Sample E-R Diagram (Figure 2-1)
6
Basic E-R notation (Figure 2-2)
Entity symbols
Attribute s mbols
A special enti en titt th thaat is is also a relationship
Relationship symbols
Relationship degrees specify Relationship cardinalities specify how many of each entity type is allowed
entity types involved
7
Modeling Business Rules • Business rul rules – State Statements tha that def define the busi busine ness. – Express xpresse ed in in natural tural langua nguage for end end use users – Expresse xpressed in in da data models for for system system devel velopers
•
oo
us ness u e s: (Table 2-1)
– – – –
Decla Dec lara rattive ive– – wha what, not not how how Pre Pr ecis ise e – clear, a reed-u on meanin Ato At omic – one state statem ment Cons Con sistent – inte nternally and and externa xternally , – Dis Disttin inc ct – non-r non-re edund dunda ant – Business-ori -orie ented – unde understood by by busi business pe people ople
8
A Good Data Name is: • e a e o us ness, no ec n ca • Meaningful and self-documenting • Unique • • Composed of words from an approved list • epea a e • Written in standard syntax 9
Modeling Entities and Attributes
10
• Things in the real world, physical or not: person, , , ,
• Entity Type – collection of entities that share properties or characteristics – Student – Represented by a rectangle
• Entity Instance – each of the instances of an entity type 11
• SHOULD BE: – An object that we are trying to model – An object that will have many instances in the database
• SHOULD NOT BE: – A user of the database system – An output of the database system (e.g. a report)
12
Figure 2-4 Example of inappropriate entities
System user
Ina
ro riate entities
System output
Appropriate entities
13
Attributes • Attribute - property or characteristic of an entity type that is of interest to the organization. • Classifications of attributes: – Required versus Optional Attributes – – Single-Valued versus Multi-valued Attribute – – Identifier Attributes
14
Figure 2-7 A composite attribute An attribute broken into component parts
Figure 2-8 Entity with multivalued attribute (Skill)
Derived
an employee can have
from date employed and current date 15
Identifiers (Keys) • Identifier (Key) - An attribute (or combination of attr utes t at un que y ent es n v ua nstances of an entity type • Candidate Key – an attribute that could be a ke …satisfies the re uirements for bein a ke
16
Criteria for Selectin Identifiers • Will not change in value over the life of each instance of the entity type. • Will not beNULL. • No intelligent identifiers (containing e.g. • Substitute new, simple (e.g., surrogate a r u e e s or on , compos e e s (e.g., entity type of Game: Game#instead o ome_ eaman s or_ eam 17
Figure 2-9 Simple and composite identifier attributes
The i enti ier is boldfaced and underlined
18
Practice: Person • Produce an E-R diagram for the following situation: We have one entity, called Person, with the following a r u es: e en er ; ame, w c s compose of one or more given names and one or more family names; one or more aliases; an address (com osed of street, city, state, zip); date of birth; and age, which can be calculated from the date of birth.
19
Relationships • Association between two or more entities • Represented by connecting lines
20
Figure 2-10 Relationship types and instances a) Relationship type (Completes)
b) Relationship instances
21
Relationships • Degree • Cardinality constraints
22
Degree of relationships – from Figure 2-2
One entity related to anot er o the same entit t e
Entities of two different types re a e o each other
Entities of three different types related to each other 23
Fig. 2-12: (b) Binary relationships
24
Interpreting Cardinalities
25
Interpreting Cardinalities
26
• Cardinalit Constraints - the number of instancesof one entity that can or must be associated with each instance of ano er en y. • Minimum Cardinality – zero, en op ona – If oneor more, then mandatory
• – Themaximumnumber
27
. following diagram – A erson is national of zero or morecountrie – A country has one or more people.
28
Fig. 2-16: Introducing cardinality constraint
(a) Relationship with cardinality constraints
29
Figure 2-17 Examples of cardinality constraints
a) Mandatory cardinalities
A patient history is only one patient
A patient must have recorded , have many 30
Figure 2-17 Examples of cardinality constraints (cont.)
b) One optional, one mandatory
31
Maximum Cardinalities • Looking at the MAXIMUM cardinality on BOTH sides we classif relationshi s as: 1:1
1:M
M:N
32
Practice: Products • We have two kinds of entities: Products and Cate ories. For each roduct we kee its id (identifier), name, price, wholesale price, and profit margin, which is calculated from the price and the w o esa e pr ce. or eac ca egory we eep s (identifier) and its name. Each product belongs to zero or more cate ories and each cate or can have zero or more products.
33
Practice : CD • Let us model a CD with three entities: CD, PERSON . – – – –
A CD has a number, which is its identifier, and a title A person has an ID and a name, divided into first, last A song has an ID, a title and a length We keep track of which person is a song’s author. A person . – We keep track of which people perform on a CD. Zero or more people can perform on a CD, and people can perform on zero or more s. – We keep track of which songs are included on a CD. One or more songs are included on a CD, and a song is included in zero or more CDs. 34
Stron vs.Weak Entities • Strong entity – Exists independently of other types of entities – Has its own unique identifier – -
• Weak entity – De endent on a stron entit …cannot exist on its own – Does not have a unique identifier (only a partial identifier) – Partial identifier underlined with double-line – nt ty ox as ou e ne
• Identifying relationship – 35
Figure 2-5 Example of a weak identity and its identifying
Strong entity
Weak entity 36
Weak Entity Strong Entity
Strong entity
ow a strong entity (unique ID)
37
Practice: Course Sections • Model the courses using two entities: COURSE and . , have zero or more sections. Each section has a number and the term offered, which is composed of semester and year.
38
Practice: Weak Entities • Give another example of a weak entity type.
39
40
Figure 2-11a A binary relationship with an attribute Attribute on a relationship ( Link Attribute/Associative)
Here, the date completed attribute pertains specifically ’ … attribute of the relationship 41
Practice: Relationships with Attributes • #8, page 103. The figure below shows a grade report that is mailed to students at the end of each semester. report. Assume that each course is taught by one instructor.
42
Practice: Relationships with Attributes
43
Homework Assi nment • Exercise #15(a,g), page #104 , • Exercise #25, page #107
44
45
Associative Entities • One of the hardest concepts in E-R modeling into an entity • Each instance of an associative entity represents an instance of the relationship • Needed to represent ternary relationships, and for cases w en we nee o conver a rea ons p n o an entity, to relate it to other entities.
46
Associative Entities - Example • Previous model: a database of courses taken by employees. For each employee we keep its SSN (identifier), name and , . also keep the date the employee completed that course.
o
•
o
uppose t at we a so want to recor n ormat on a out t e institutions issuing the certificates. HOW??? 47
Figure 2-11b An associative entity (CERTIFICATE)
o
o
Associative entity is like a relationship with an attribute, but it is also considered to be an entit in its own ri ht Note that the many-to-many cardinality between entities in - relationships with the associative entity 48
o
Fill in the missing car ina ities.
o
Issues
INSTITUTION I nstitution I D
Institution Address (City, State, Zip) 49
Fig. 2-11: (b) An associative entity (CERTIFICATE)
Em lo ee _ ID Course_ID
What is an alternative to assign the PK? 50
Associative Entities • An entity - has attributes • When should a relationship with attributes instead be an associative entit ? – All relationships for theassociative entity should be many – The associative entity could have meaning independent of – The associative entity preferably has a unique identifier, and should also have other attributes – The associative entity may participate in other relationships other than the entities of the associated relationship
51
Homework Assi nment • Exercise #15(f), page #104
52
53
• Associates three entities at the same time • Can't we just live with binary relationships?
54
• Say we wanted to keep track of which Person got which Degrees from which University. PERSON
go
UNIVERSITY
• Can this be modeled with binary relationships? 55
• Y es with the hel of an associative entit
DEGREE
• Ternary relationship “got” is now an associative entity, DIPLOMA. Cardinality constraints? 56
Figure 2-12 c) Example of a ternary relationship
Note: a relationship can have attributes of its own 57
Fig. 2-14: Ternary relationships as an associative entity
As a general rule, ternary relationships should be converted to associative entities. 58
Fig. 2-18: Cardinality constraints in a ternary relationship
at are t e us ness ru es 59
.
60
, • Exercise #18, page 106
61
62
• Also known as recursive relationships • Two or more entities in the relationship are of the same type • Example: we want to represent when an em lo ee su ervises another em lo ee.
63
employee supervises another employee. • e cou star w t somet ng e t s:
SUPERVISOR
Supervises
SUPERVISEE
64
• Both supervisor and supervisee are employees, so , Supervises Supervisee Supervisor
65
, , • Should always define roles – even more important Supervises Supervisee V Supervisor
What are the business rules? 66
Fig. 2-12 (a) Unary relationships
Practice: Add missin cardinalities. 67
Figure 2-17 Examples of cardinality constraints (cont.)
c) Optional cardinalities
A erson is married to at most one other person, or may not be married at all
68
Whole
Part
• Part-Whole is also a recursive relationship: Has Components
Part V ITEM
uant ty
>
Whole
69
Fi . 2-13: b Two ITEM bill-of-materials instances
70
Fig. 2-13 (c ) : An associative entity - bill of materials structure
attributes…it’s a judgment call 71
• Give another exam le of a unar relationshi .
72
• Exercise #10, a e #103.
73
• Sometimes ou will wonder whether to re resent data as an attribute or an entity. This is a common dilemma. • Let us look at a few situations.
74
Figure 2-15a and 2-15b Multivalued attributes can be represented as relationships/Associative Entity
simple
composite
75
• So when SHOULD an attribute be linked to an entity type via a relationship? – Attribute refers to a concept in the data model – Multiple entity instances share the same attribute
• Example on next page
76
Fig. 2-15: Using relationships and entities to link related attribute (c) Composite attribute of data shared with other entity types
77
u p e e a ons ps •
ore an one re a ons p e ween the same entity types (Fig. 2-21)
78
Fig. 2-21: Examples of multiple relationships a
mp oyees an
epartments
Entities can be related to one another in more than one way. 79
Fig. 2-21: (b) Professors and courses (fixed upon constraint) ew us ness u e: An instructor who is scheduled to teach a
to teach that course??
Here, minimum cardinality constraint is 2, what’s for?
80
• • • • • •
Exercise #11, a e #103 Exercise #15(d,i), page #105 Exercise#18 a e#10 Exercise #20, page #106 , Exercise #26, page #108
81
Fi ure 2-19. Sim le exam le of time-stam in
multivalued and composite. 82
Fig. 2-20: (a) E-R diagram not recognizing product reassignment
Fig. 2-20: (b) E-R diagram recognizing product reassignment
83
Fig. 2-20: (a) E-R diagram not recognizing product reassignment In the middle of year, due to a reorganization of the sales function some products are reassigned to different product lines
PRODUCT LINE
ow o re ec pro uc changed over time?
Sales
Assigned
Product
ne
Product-Line
50 000
P
B
$40,000
P
A
(out of $50,000)
PRODUCT
Placed
ORDER 84
Fig. 2-20: Pine Valley Furniture product database Solution: adding a new relationship of “Sales_for_product_line”
(b) E-R diagram recognizing product reassignment
85
Fig. 2-20: Pine Valley Furniture product database c E-R dia ram with associative entit for roduct assi nment to roduct line over time
86
e nt ty eat ons p
-
oe
Congratulation !! one of the most concept (E-R ) for base systems.
87
Notations
88
