Entity Relationship Model - WordPress.com · 4/4/2016 · Entity Relationship Model ... •Document...
-
Upload
nguyentruc -
Category
Documents
-
view
229 -
download
0
Transcript of Entity Relationship Model - WordPress.com · 4/4/2016 · Entity Relationship Model ... •Document...
Objectives
Learn how to analyze real world data
Data and properties
Relationships
Represent data in a formal model, ER Model
The first step of database design
Learn to construct ER diagrams
Asst.Prof. Intiraporn Mulasastra
2
Outline
Notation basics
Understanding entity relationships
Generalization hierarchies
Diagram rules
Asst.Prof. Intiraporn Mulasastra
3
Data Modeling
Real World
Data Recordings
Data Modeling Data Representation
Data Items
<e,a,v>
<e,a,v>
<e,a,v>
The Notion of Data Physical Storage
Entity Relationship Model
Database Model
Asst.Prof. Intiraporn Mulasastra
4
Database application development
1. Requirement analysis
2. Component design
1. Data modeling
2. SW design
3. Implementation
Requirement gathering
•User interview
•Document analysis
• reports, business rules, etc. •Stakeholder Meeting
Asst.Prof. Intiraporn Mulasastra
5
A Library Case
Collection of Books, index cards
Members
Borrowing and Returning books
Tom borrows a database book.
Tom is a member, noun
A database book is a book, noun
Borrowing is a process (verb) involving a book and a member
Asst.Prof. Intiraporn Mulasastra
6
Entity type (Entity class)
A collection of things of interest: persons, places, things, events, processes
Contains attributes with identifier(s) characteristics/properties of things (entities)
Offering
OfferNo
OffLocation OffTime
Course
CourseNum
CrsDesc CrsUnit
Entity type Entity type
attributes attributes
Asst.Prof. Intiraporn Mulasastra
8
Entity Instance
An entity
An entity instance is a specific
occurrence of an entity type. a member of an entity type
Asst.Prof. Intiraporn Mulasastra
9
Entity Instances
ID FirstName LastName GPA
5410545044 วรญญ ฤกษด 3.0
5410546334 วศน หาวาร 3.1
5610545013 นต เพชรรตนโมรา 3.2
5610545048 ธนธร อศวะอ านวย 3.3
Student
Entity type
4 entity instances
Members of a set must have the same type Student = {s1, s2, s3, s4}
attribute
Asst.Prof. Intiraporn Mulasastra
10
Attributes
Properties of entity types
An entity type has attributes which together describe the entity
Student (ID, FirstName, LastName, GPA)
Each attribute has data type and other properties
Key or non-key
Asst.Prof. Intiraporn Mulasastra
12
Identifiers(keys)
• Entity instances have identifiers (keys)
• Keys are a type of attribute
Entity type : Student Contains attributes: ID, FirstName, LastName, GPA
Key : ID Unique value
ID can identify a specific instance in the entity type
Asst.Prof. Intiraporn Mulasastra
13
Types of Keys
Uniqueness
Keys may be unique or non-unique
If the key is unique, the data value for the key must be
unique among all instances of the entity
Candidate keys
Primary keys (one for each entity type)
Composite
A composite key consists of two or more attributes E.g., FirstName + LastName
Asst.Prof. Intiraporn Mulasastra
14
Entity Type vs. Entity Instance
An entity type is a description of the
structure and format of occurrences of
the entity
An entity instance is a specific occurrence of an entity type.
Asst.Prof. Intiraporn Mulasastra
17
Graphical presentation
•Entity type
•Entity type showing only the primary key attribute
•Entity type showing all attributes
•Conceptual design
•Preliminary design
•Final stage
1 2 3
Asst.Prof. Intiraporn Mulasastra
18
Relationships
Association among entity instances: have specific names
Usually between two entity types Unary
Binary
Ternary
etc. multiple entity types
Asst.Prof. Intiraporn Mulasastra
19
1.Unary Relationship
2.Binary Relationship
3.Ternary Relationship
teach
Asst.Prof. Intiraporn Mulasastra
20
Types of Binary Relationships
One to one
One to many
Many to one
Many to many
Asst.Prof. Intiraporn Mulasastra
21
One-to-One Relationship
Unary or binary
One-to-one binary relationship
An entity instance in one entity type is
related to an entity instance in another
entity type
Example
A student may have no
more than one email acct.
An email account may only
be used by one student
Asst.Prof. Intiraporn Mulasastra
22
Bidirectional Relationship
◦ Can be used to navigate in both directions
◦ Two names Course to Offering: Has, Provides *
Offering to Course: IsProvidedFor
Which name to use: try to use active* verb; not always possible
Course
Offering
Ex. Course 351 has three offerings Asst.Prof. Intiraporn Mulasastra
24
1:N relationship
ID Name
101 Database
102 OS
103 Network
OfferNo Semester Year
1 1 2014
2 2 2015
3 1 2015
ID Name
101 Database
102 OS
103 Network
1.Each course has many offerings
2.Each offering is provided for one course
Offering การเปดสอน
Course
OfferNo Semester Year
1 1 2014
2 2 2015
3 1 2015
N:1 Offering to Course
1:N Course to Offering
Asst.Prof. Intiraporn Mulasastra
25
N:M relationship
ID Name
101 Sarah
102 Tom
103 Tim
OfferNo Semester Year
1 1 2014
2 2 2015
3 1 2015
ID Name
101 Sarah
102 Tom
103 Tim
1.Each student can enroll in many offerings
2.Each offering can be enrolled by many students
Offering Student
OfferNo Semester Year
1 1 2014
2 2 2015
3 1 2015
Asst.Prof. Intiraporn Mulasastra
27
How to represent relationships?
In E-R model, each entity type consists of its properties only !
Offering
OfferNo
OffLocation OffTime
Course
CourseNum
CrsDesc CrsUnit
Entity type Entity type
attributes of Course
attributes of Offering
Where is the relationship?
Asst.Prof. Intiraporn Mulasastra
28
Data Representation
Database models
e.g., Relational data model
Entity Relationship Model
Conceptual model
No physical data representation
Entity Relationship Diagram rectangles represents entity types and attributes
relationships represented by lines linking entities
Asst.Prof. Intiraporn Mulasastra
29
Cardinality of Relationships
Cardinality means count
as a number of members in a set
Specify the number of entity instances that can
participate in a relationship instance
minimum : 0-n
maximum : 1- n
Course
Offering1 N
0,1,2,3,… or functional
Asst.Prof. Intiraporn Mulasastra
31
E-R Diagram
Represent E-R model
Entity and attributes
a rectangle with entity name and attributes
underlined attribute is a primary key
Relationship
dashed line with a name
Cardinality
Offering
OfferNo
OffLocation OffTime
Course
CourseNum
CrsDesc CrsUnit
1:1 0:n
has
Asst.Prof. Intiraporn Mulasastra
33
Cardinality Notation (ERD)
A vertical line represents one (1)
A circle represents zero (0)
A crow’s foot represent many (N)
Any integers
Function names
Crow’s foot Asst.Prof. Intiraporn Mulasastra
34
Cardinality Notation
Inside symbol:
minimum cardinality
CourseNo
CrsDesc
CrsUnits
Course
OfferNo
OffLocation
OffTime
Offering
Has
Perpendicular line:
one cardinality
Outside symbol:
maximum cardinality
Circle: zero
cardinality
Crow's foot: many
cardinality
1:1(max:min) 0:n (min:max)
Asst.Prof. Intiraporn Mulasastra
37
Summary of Cardinalities
Classification Cardinality Restrictions
Mandatory Minimum cardinality 1
Optional Minimum cardinality = 0
Functional or single-
valued
Maximum cardinality = 1
1-M Maximum cardinality = 1 in
one direction and Maximum
cardinality > 1 in the other
direction.
M-N Maximum cardinality is > 1
in both directions.
1-1 Maximum cardinality = 1 in
both directions.
Asst.Prof. Intiraporn Mulasastra
38
More Relationship Examples
FacSSN
FacSalary
FacRank
FacHireDate
Faculty
OfferNo
OffLocation
OffTime
Offering
TeamTeachesOfficeNo
OffPhone
OffType
Office
WorksIn
WorksIn:
- 1-1
- Optional: office can be empty
- Mandatory: faculty must be
assigned to an office
TeamTeaches:
- M-N
- Optional in both
directions
Asst.Prof. Intiraporn Mulasastra
39
Teacher
Conferenceattend
Customer
Product? ?
purchase
More Relationship Examples
Football Team
Player? ?
play
Depend on business rules Asst.Prof. Intiraporn Mulasastra
40
Bank Branch
Account? ?
has
Product
Warehouse? ?
is-in
More Relationship Examples
Asst.Prof. Intiraporn Mulasastra
41
Understanding Relationships
1. Identification dependency
2. M-N relationships with attributes
3. Self identifying relationships
4. M-way relationships
5. Equivalence between M-N and 1-M
relationships
Asst.Prof. Intiraporn Mulasastra
43
1.Identification Dependency
Identification dependency involves existence
dependency:
Weak entity is existent dependent on other entity
Also borrows part or all of PK
Ex. Existence of a room
Building
Room
Asst.Prof. Intiraporn Mulasastra
44
Identification Dependency
BldgID
BldgName
BldgLocation
Building
RoomNo
RoomCapacity
Room
Contains
Identification Dependency Symbols:
Solid relationship line for identifying
relationships
Diagonal lines in the corners denote
weak entities.
Others: Province & district, order-orderline
Indicates the source of PK
Borrows part or all of PK
Asst.Prof. Intiraporn Mulasastra
45
M-N Relationships
with Attributes
StdSSN
StdName
StudentOfferNo
OffLocation
OffTime
Offering
EnrollsIn
EnrGrade
attribute of relationship
Asst.Prof. Intiraporn Mulasastra
46
M-N Relationships
with Attributes (II)
PartNo
PartName
Part
SuppNo
SuppName
Supplier
Qty
Provides
a) Provides relationship
AuthNo
AuthName
Author
ISBN
Title
Book
AuthOrder
b) Writes relationship
Writes
Asst.Prof. Intiraporn Mulasastra
47
Instance Diagrams for
Self-Referencing Relationships
Faculty1
Faculty2 Faculty3
Faculty4 Faculty5
IS300
IS320
IS480 IS460
IS461
(a) Supervises (b) PreReqTo
Asst.Prof. Intiraporn Mulasastra
48
ERD Notation for
Self-Referencing Relationships
FacSSN
FacName
Faculty
a) manager-subordinate
Supervises
b) course prerequisites
CourseNo
CrsDesc
Course PrereqTo
Asst.Prof. Intiraporn Mulasastra
49
Associative Entity Types for
M-way Relationships
PartNo
PartName
Part
SuppNo
SuppName
Supplier
Associative
entity type
ProjNo
ProjName
Project
UsesPart-Uses
Supp-Uses
Proj-Uses
3 way relationship tracks who supplies a part on a specified project Asst.Prof. Intiraporn Mulasastra
51
Example
Project Part Supplier
101 Door HomePro 101 Door ThaiPro 102 Window HomePro 102 Door ThaiPro
Uses
Asst.Prof. Intiraporn Mulasastra
52
Relationship Equivalence
Replace M-N relationship
Associative entity type
Two identifying 1-M relationships
M-N relationship versus associative entity type
Largely preference
Associative entity type is more flexible in some situations
Asst.Prof. Intiraporn Mulasastra
53
Associative Entity Type Example
StdSSN
StdName
StudentOfferNo
OffLocation
OffTime
Offering
EnrGrade
EnrollmentRegistersGrants
AttDate
Present
Attendance
RecordedFor
Attendance: - Weak entity - PK: Combination of AttDate and PK of Enrollment Must use associative entity type for Enrollment rather than M-N relationship
Asst.Prof. Intiraporn Mulasastra
54
Generalization
A bottom-up design process – combine a number of
entity sets that share the same features into a higher-
level entity set.
Specialization and generalization are simple inversions
of each other; they are represented in an E-R diagram
in the same way.
The terms specialization and generalization are used
interchangeably.
Asst.Prof. Intiraporn Mulasastra
56
The ISA relationship also referred to as
superclass - subclass relationship
ISA
ISA
Asst.Prof. Intiraporn Mulasastra
57
Generalization Hierarchies
Employee
EmpNo
EmpName
EmpHireDate
...
SalaryEmp
EmpSalary
HourlyEmp
EmpRate
generalization hierarchy
symbol
supertype
subtypes
Asst.Prof. Intiraporn Mulasastra
58
Inheritance
Subtypes inherit attributes of supertypes (direct and
indirect)
Allows abbreviation of attribute list
Applies to code (methods) as well as attributes
(data)
Reduce the amount of code by inheriting code from similar objects
Asst.Prof. Intiraporn Mulasastra
60
Multiple Levels of Generalization
Security
Symbol
SecName
LastClose
Stock
OutShares
IssuedShares
Bond
Rate
FaceValue
D,C
Common
PERatio
Dividend
Preferred
CallPrice
Arrears
D,C
หนบรมสทธ หนสามญ
หลกทรพย
พนธบตร หนทน
Asst.Prof. Intiraporn Mulasastra
61
Comprehensive Example
Offering
OfferNo
OffLocation
OffTime
EnrGrade
EnrollmentRegisters
Grants
CourseNo
CrsDesc
CrsUnits
Course
Faculty
FacSalary
FacRank
FacHireHate
SSN
Name
City
State
Zip
UnivPerson
Has
Teaches
Supervises
C
Student
StdClass
StdMajor
StdGPA
A faculty member can be a student too.
Asst.Prof. Intiraporn Mulasastra
62
Diagram Rules
Ensure that ERD notation is correctly used
Similar to syntax rules for a computer language
Completeness rules: no missing specifications
Consistency rules: no conflicts among
specifications
Asst.Prof. Intiraporn Mulasastra
63
PK Rule Violation Example
BldgID
BldgName
BldgLocation
BuildingRoom
RoomNo
RoomCapacityContains
PK rule violation
A single 1-M identifying relationship
Room does not have a local key.
RoomNo is not
underlined No Local key
Asst.Prof. Intiraporn Mulasastra
64
Example
Offering
OfferNo
OffLocation
OffTime
EnrGrade
EnrollmentRegisters
Grants
CourseNo
CrsDesc
CrsUnits
Course
Faculty
FacSalary
FacRank
FacHireHate
SSN
Name
City
State
Zip
UnivPerson
Has
Teaches
Supervises
C
Student
StdClass
StdMajor
StdGPA
Why not a weak entity
Asst.Prof. Intiraporn Mulasastra
65
Summary
Data modeling is an important skill
Crow’s Foot ERD notation is widely used
Use notation precisely
Use the diagram rules to ensure structural
consistency and completeness
Understanding the ERD notation is a prerequisite
to applying the notation on business problems
Asst.Prof. Intiraporn Mulasastra
66