Transcript 01 Lecture

BIS4435 – Industrial Data Management
Lecture 3
Lecture: Entity-Relationship Modeling
Dr. Nawaz Khan
School of Computing Science
E-mail: [email protected]
Duty time: Wednesdays : 2:30-4:30 pm
Dr. Nawaz Khan; Email: [email protected]
1
Unit Reading Assignment
Unit 3: Entity Relationship Modelling: A Traditional vs. Object
Oriented Approach
Lecture 3
Reading Suggestion:
Connolly, T.M., and Begg, C.E., Database Systems: A Practical
Approach to Design, Implementation and Management, Addison-Wesley,
4th Edition, ISBN: 0321210255(chapters 11, 13 and 25)
More Reading:
Fundamentals of Database Systems. R. Elmasri and S. B. Navathe,
4th Edition, 2004, Addison-Wesley, ISBN 0-321-12226-7 (Chapters 3, 7)
Dr. Nawaz Khan; Email: [email protected]
BIS4229 – Industrial Data Management
Technologies
2
NOTE: !!!!!
Lecture 3
This unit is the basis of the STAGE I
CW Portfolio.
 In particular, it helps you understand the
Structured Query Language (SQL)
discussed in unit 4 and Normalisation
discussed in unit 5
Dr. Nawaz Khan; Email: [email protected]
BIS4229 – Industrial Data Management
Technologies
3
Unit Learning Outcome
Lecture 3
 At the end of this unit you should be able to:
 Analyse cases to identify the entities and their
relationships involved
 Apply Entity Relationship modelling to represent entities,
their relationships and extend it further by identifying
attributes for each entity
 Transform the model into relations suitable for Relational
database implementation
 Identify the need of object oriented approach for semantic
data modelling
 Apply Unified modelling language to model database
objects
Dr. Nawaz Khan; Email: [email protected]
4
Lecture Outline
 What is the ER modeling? And why?
 Example Database Application (COMPANY)
 ER model concepts
Lecture 3
 Entities and Attributes
 Entity Types, Value Sets, and Key Attributes
 Relationships and Relationship Types
 Weak Entity Types
 More about relationships
 Structural constraints: cardinality ratio, membership class
 Recursive relationships
 Subtype and generalization of subtype
 ER diagram
 Transformation ER model -> relational database schema
 EER and UML for data modeling
Dr. Nawaz Khan; Email: [email protected]
5
Entity-Relationship Modeling
Lab Notes
JobList
Lecture 3
JobID CompanyID JobTitle Salary ….
Company
CompanyID CompanyName Address ...
 CompanyID duplicates in Joblist table:


No: 1-1 relationship
Yes: 1-N relationship (N side: Joblist)
Dr. Nawaz Khan; Email: [email protected]
6
Entity-Relationship Modeling
What is the ER modeling? And why?
 Database approach:

External
Schema 1
External
Schema N
External
Schema 2
Where is ER modeling?
Interface between
conceptual schema and
external schemas
Lecture 3
Conceptual
Schema
Interface between
conceptual schema
and internal schema
Internal
Schema
Database
physically stored
in files on disks
Dr. Nawaz Khan; Email: [email protected]
7
Entity-Relationship Modeling
What is the ER modeling? And why?
 ER modelling is a logical organisation of data within a
database system
 ER modelling technique is based on relational data model
 Why use ER data modelling:
Lecture 3
 User requirements can be specified formally &
unambiguously
 The conceptual data model is independent of any particular
DBMS
 It does not involve any physical or implemental details
 It can be easily understood by ordinary users.
 It provides an effective bridge between informal user
requirements and logical database design and
implementation
Dr. Nawaz Khan; Email: [email protected]
8
Entity-Relationship Modeling
Example Database Application (COMPANY)
 Requirements of the Company (oversimplified for illustrative
purposes)
Lecture 3
 The company is organized into DEPARTMENTs. Each
department has a name, number and an employee who
manages the department. We keep track of the start date of the
department manager
 Each department controls a number of PROJECTs. Each
project has a name, number and is located at a single location
 We store each EMPLOYEE’s social security number, address,
salary, sex, and birthdate. Each employee works for one
department but may work on several projects. We keep track of
the number of hours per week that an employee currently works
on each project. We also keep track of the direct supervisor of
each employee
 Each employee may have a number of DEPENDENTs. For
each dependent, we keep track of their name, sex, birthdate,
and relationship to employee
Dr. Nawaz Khan; Email: [email protected]
9
Summary of ER diagram notation
Symbol
Meaning
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
IDENTIFYING RELATIONSHIP TYPE
Lecture 3
ATTRIBUTE
KEY ATTRIBUTE
MULTIVALUED ATTRIBUTE
COMPOSITE ATTRIBUTE
DERIVED ATTRIBUTE
E1
E1
E2
R
R
R
N
(min,max)
TOTAL PARTICIPATION OF E2 IN R
E2
CARDINALITY RATIO 1:N FOR E1:E2 IN R
E
STRUCTURAL CONSTRAINT (min, max) ON
PARTICIPATION OF E IN R
Dr. Nawaz Khan; Email: [email protected]
10
Entity-Relationship Modeling
ER diagram for the COMPANY database
Lecture 3
Dr. Nawaz Khan; Email: [email protected]
11
Entity-Relationship Modeling
ER model concepts
 Entities and Attributes
Lecture 3
 Entities are specific objects or things in the mini-world that
are represented in the database; for example, the
EMPLOYEE John Smith, the Research DEPARTMENT, the
ProductX PROJECT
 Attributes are properties used to describe an entity; for
example, an EMPLOYEE entity may have a Name, SSN,
Address, Sex, BirthDate
 A specific entity will have a value for each of its attributes; for
example, a specific employee entity may have Name=‘John
Smith’, SSN=‘123456789’, Address=‘731 Fondren, Houston,
TX’, Sex=‘M’, BirthDate=‘09-JAN-55’
Dr. Nawaz Khan; Email: [email protected]
12
Entity-Relationship Modeling
ER model concepts
 Types of attributes
Lecture 3
 Simple: Each entity has a single atomic value for the
attribute; for example SSN or Sex
 Composite: The attribute may be composed of several
components; for example, Address (Apt#, House#, Street,
City, State, ZipCode, Country) or Name(FirstName,
MiddleName, LastName). Composition may form a hierarchy
where some components are themselves composite
 Multi-valued: An entity may have multiple values for that
attribute; for example, Color of a CAR or PreviousDegrees of
a STUDENT. Denoted as {Color} or {PreviousDegrees}
 In general, composite and multi-valued attributes may be
nested arbitrarily to any number of levels although this is
rare. For example, PreviousDegrees of a STUDENT is a
composite multi-valued attribute denoted by
{PreviousDegrees(College, Year, Degree, Field)}
Dr. Nawaz Khan; Email: [email protected]
13
Entity-Relationship Modeling
ER model concepts
 Entity Types, Value Sets, and Key Attributes
Lecture 3
 Entities with the same basic attributes are grouped or typed
into an entity type. For example, the EMPLOYEE entity type
or the PROJECT entity type
 An attribute of an entity type for which each entity must have
a unique value is called a key attribute of the entity type. For
example, SSN of EMPLOYEE
 A key attribute may be composite. For example,
VehicleTagNumber is a key of the CAR entity type with
components (Number, State)
 An entity type may have more than one key: choose one
arbitrarily as the primary key
Dr. Nawaz Khan; Email: [email protected]
14
Entity-Relationship Modeling
ER model concepts
 Relationships and Relationship Types
Lecture 3
 A relationship relates two or more distinct entities with a specific
meaning; for example, EMPLOYEE John Smith works on the
ProductX PROJECT or EMPLOYEE Franklin Wong manages the
Research DEPARTMENT
 Relationships of the same type are grouped or typed into a
relationship type. For example, the WORKS_ON relationship type
in which EMPLOYEEs and PROJECTs participate, or the
MANAGES relationship type in which EMPLOYEEs and
DEPARTMENTs participate
 The degree of a relationship type is the number of participating
entity types. Both MANAGES and WORKS_ON are binary
relationships
 More than one relationship type can exist with the same
participating entity types; for examples, MANAGES and
WORKS_FOR are distinct relationships between EMPLOYEE and
DEPARTMENT participate
Dr. Nawaz Khan; Email: [email protected]
15
Entity-Relationship Modeling
ER model concepts
 Weak Entity Types
Lecture 3
 An entity that does not have a key attribute
 A weak entity must participate in an identifying relationship
type with an owner or identifying entity type
 Entities are identified by the combination of:
A partial key of the weak entity type
The particular entity they are related to in the
identifying entity type
 Example: a DEPENDENT entity is identified by the
dependent’s first name (unique wrt. each EMPLOYEE) and
the specific EMPLOYEE that the dependent is related to.
DEPENDENT is a weak entity type with EMPLOYEE as its
identifying entity type via the identifying relationship type
DEPENDENT_OF
Dr. Nawaz Khan; Email: [email protected]
16
Entity-Relationship Modeling
ER diagram for the COMPANY database
Lecture 3
Dr. Nawaz Khan; Email: [email protected]
17
Entity-Relationship Modeling
More about relationships
 Structural constraints: one way to express semantics of
relationship: cardinality ratio and membership class
 Cardinality ratio (functionality): It specifies the number of
relationship instances that an entity can participate in a binary
relationship
Lecture 3



one-to-one (1:1)
one-to-many (1:M) or many-to-one (M:1)
many-to-many (M:N)
 An example of a 1:1 binary relationship is MANAGES which
relates a department entity to the employee who manages that
department. This represents the miniworld constraints that an
employee can manage only one department and that a
department has only one manager
 Relationship types of degree 2 are called binary. Relationship
types of degree 3 are called ternary and of degree n are called
n-ary. In general, an n-ary relationship is not equivalent to n
binary relationships (reading suggestion !!)
Dr. Nawaz Khan; Email: [email protected]
18
One-to-many(1:N) or Many-to-one (N:1) RELATIONSHIP
Lecture 3
EMPLOYEE
WORKS_FOR
e1

r1
e2

r2
e3

e4

e5
r3
r4

e6

e7

DEPARTMENT

d1

d2

d3
r5
r6
r7
Dr. Nawaz Khan; Email: [email protected]
19
MANY-TO-MANY(M:N)
RELATIONSHIP
EMPLOYEE
WORKS_ON
PROJECT
r9
Lecture 3
e1

r1
e2

r2
e3

e4

e5
r3
r4

e6

e7

p1

p2

p3
r5
r6
r8
Dr. Nawaz Khan; Email: [email protected]

r7
20
Entity-Relationship Modeling
More about relationships
 Membership class (participation constraint):
Lecture 3
 Mandatory (total participation) - every instance of a participating
entity type must participate in the relationship. Example: ATTEND
relationship between STUDENTS and COURSE
 Optional (partial participation) - not every instance of a
participating entity type must participate in the relationship.
Example: OFFER relationship between SCHOOL and MODULE is
optional for SCHOOL but mandatory for MODULE
 Notation:
 Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N
SHOWN BY PLACING APPROPRIATE NUMBER ON THE LINK
 Participation constraint (on each participating entity type): total (called
existence dependency) or partial.
IN ER DIAGRAMS, TOTAL PARTICIPATION IS DISPLAYED AS A
DOUBLE LINE CONNECTING THE PARTICIPATING ENTITY
TYPE TO THE RELATIONSHIP, WHEREAS PARTIAL
PARTICIPATION IS REPRESENTED BY A SINGLE LINE
Dr. Nawaz Khan; Email: [email protected]
21
Entity-Relationship Modeling
ER diagram for the COMPANY database
Lecture 3
Dr. Nawaz Khan; Email: [email protected]
22
Entity-Relationship Modeling
More about relationships
 Recursive relationships (involuted relationship):
relationship among different instances of the same entity
Lecture 3
1
MARRY
PERSON
M
1
COMPRISE
PART
N
SUPERVISE
EMPLOYEE
N
1
Dr. Nawaz Khan; Email: [email protected]
23
Entity-Relationship Modeling
ER diagram
Lecture 3
 An ER model can be expressed in the form of the ER
diagram
 An entity type is represented by a rectangular box
 A relationship is represented by a diamond-shaped box
 Relationships are linked to their constituent entity types by
arcs
 The functionality of a relationship is indicated on the arc
 Attributes of entity types/relationships, and membership
classes of entity types are listed separately from the
diagram
 The key attribute(s) is underlined
Dr. Nawaz Khan; Email: [email protected]
24
Entity-Relationship Modeling
ER diagram
 Example: The university database maintains records of its
departments, lecturers, course modules, and students
 The requirements are summarised as follows:
Lecture 3
 The university consists of departments. Each department
has a unique name and some other descriptive attributes
 A department must also have a number of lecturers, one of
which is the head of department
 All lecturers have different names (we assume so anyway).
They must teach one or more modules. A lecturer can only
belong to one department
 Modules are offered by departments and taught by lecturers.
They must also be attended by some students. Each module
has a unique module number.
 Students must enrol for a number of modules. Each student
is given a unique student number.
Dr. Nawaz Khan; Email: [email protected]
25
DEPARTMENT
1
1
1
OFFER
HEAD_OF
IS_IN
Lecture 3
1
N
MODULE
M
TEACH
1
N
LECTURER
N
M
ENROL
Dr. Nawaz Khan; Email: [email protected]
STUDENT
26
Entity-Relationship Modeling
ER diagram
 Entity types and their attributes:
Lecture 3




DEPARTMENT: DNAME, LOCATION, FACULTY, …
MODULE: MDL-NUMBER, TITLE, TERM, …
STUDENT: SNUMBER,SNAME,ADDRESS,SEX,DOB, …
LECTURER: LNAME, ROOMNUMBER, PHONE, ...
Dr. Nawaz Khan; Email: [email protected]
27
Entity-Relationship Modeling
ER diagram
 Relationships:
 HEAD_OF:
 1:1 between LECTURER and DEPARTMENT
 Membership: Mandatory for DEPARTMENT
 IS_IN:
Lecture 3
 1:N between DEPARTMENT and LECTURER
 Membership: Mandatory for both
 OFFER:
 1:N between DEPARTMENT and MODULE
 Membership: Mandatory for MODULE
 ENROL:
 M:N between STUDENT and MODULE
 Membership: Mandatory for both
 Attribute: DATE (date of enrolment)
 TEACH:
 1:M between LECTURER and MODULE
 Membership: Mandatory for both
 Do complete the ER Diagram !!
Dr. Nawaz Khan; Email: [email protected]
28
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Transformation of entity types
 Entity --> Relation
 Attribute of entity --> Attribute of relation
 Primary key of entity --> Primary key of relation
Lecture 3
 Transformation of binary relationships - depends on
functionality of relationship and membership class of
participating entity types
Dr. Nawaz Khan; Email: [email protected]
29
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Mandatory membership class
Lecture 3
 For two entity types E1 and E2: If E2 is a mandatory member
of an N:1 (or 1:1) relationship with E1, then the relation for
E2 will include the prime attributes of E1 as a foreign key to
represent the relationship
 For a 1:1 relationship: If the membership class for E1 and E2
are both mandatory, a foreign key can be used in either
relation
 For an N:1 relationship: If the membership class of E2, which
is at the N-side of the relationship, is optional (i.e. partial),
then the above guideline is not applicable
Dr. Nawaz Khan; Email: [email protected]
30
Entity-Relationship Modeling
Transformation ER model --> relational database schema
DEPARTMENT
Lecture 3
1
OFFER
MODULE
N
Assume, every module must be offered by a department,
then the entity type MODULE is a mandatory member of the
relationship OFFER. The relation for MODULE is:
MODULE(MDL-NUMBER, TITLE, TERM, ..., DNAME)
Dr. Nawaz Khan; Email: [email protected]
31
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Optional membership classes
Lecture 3
 If entity type E2 is an optional member of the N:1
relationship with entity type E1 (i.e. E2 is at the N-side of the
relationship), then the relationship is usually represented by
a new relation containing the prime attributes of E1 and E2,
together with any attributes of the relationship. The key of
the entity type at the N-side (i.e. E2) will become the key of
the new relation
 If both entity types in a 1:1 relationship have the optional
membership, a new relation is created which contains the
prime attributes of both entity types, together with any
attributes of the relationship. The prime attribute(s) of either
entity type will be the key of the new relation.
Dr. Nawaz Khan; Email: [email protected]
32
Entity-Relationship Modeling
Transformation ER model --> relational database schema
BORROWER
1
ON_LOAN
N
BOOK
Lecture 3
One possible representation of the relationship:
BORROWER(BNUMBER, NAME, ADDRESS, ...)
BOOK(ISBN, TITLE, ..., BNUMBER)
A better alternative:
BORROWER(BNUMBER, NAME, ADDRESS, ...)
BOOK(ISBN, TITLE, ...)
ON_LOAN(ISBN, BNUMBER)
Dr. Nawaz Khan; Email: [email protected]
33
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 N:M binary relationships:
Lecture 3
 An N:M relationship is always represented by a new relation
which consists of the prime attributes of both participating
entity types together with any attributes of the relationship
 The combination of the prime attributes will form the primary
key of the new relation
 Example: ENROL is an M:N relationship between
STUDENT and MODULE. To represent the relationship,
we have a new relation:
ENROL(SNUMBER, MDL-NUMBER, DATE)
Dr. Nawaz Khan; Email: [email protected]
34
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Transformation of recursive/involuted relationships:
The name(s) of the prime attribute(s) needs to be changed
to reflect the role each entity plays in the relationship
Lecture 3
 Example 1: 1:1 involuted relationship, in which the
memberships for both entities are optional
PERSON(ID, NAME, ADDRESS, ...)
MARRIAGE(HUSBAND-ID, WIFE_ID, DATE_OF_MARRIAGE)
Dr. Nawaz Khan; Email: [email protected]
35
Entity-Relationship Modeling
More about relationships
 Recursive relationships (involuted relationship):
relationship among different instances of the same entity
Lecture 3
1
MARRY
PERSON
M
1
COMPRISE
PART
N
SUPERVISE
EMPLOYEE
N
1
Dr. Nawaz Khan; Email: [email protected]
36
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Example 2: 1:M involuted relationship.
Lecture 3
 If the relationship is mandatory or almost mandatory:
EMPLOYEE(ID, ENAME, ..., SUPERVISOR_ID)
 If the relationship is optional:
EMPLOYEE(ID, ENAME, ...)
SUPERVISE(ID, START_DATE, ..., SUPERVISOR_ID)
 Example 3: N:M involuted relationship
PART(PNUMBER, DESCRIPTION, ...)
COMPRISE( MAJOR-PNUMBER, MINOR-PNUMBER, QUANTITY)
Dr. Nawaz Khan; Email: [email protected]
37
Entity-Relationship Modeling
Transformation ER model --> relational database schema
 Transformation of subtypes:
Lecture 3
 The transformation of a type hierarchy results in a separate
relation for the root entity type and each subtype
 The key of each relation is the key of the root type
 The relation for each subtype also contains its specific
attributes in addition to the key
 Example:
PERSON(ID, NAME, ADDRESS, ...)
STUDENT(ID, <attributes specific to students>)
LECTURER(ID, <attributes specific to lecturers>)
PROFESSOR(ID, <attributes specific to professors>)
Dr. Nawaz Khan; Email: [email protected]
38
Extended E-R Modeling
Lecture 3
 Generalisation: is a process where an entity is constructed
from two or more existing entities based on the similarities they
have. For example, an entity Employee is constructed from two
entities, Part-Time Employee and Full-Time Employee.
Therefore it can be said that the generalisation process is a
bottom up approach.
 Aggregation: is a process where an entity is constructed from
two or more existing entities that are the essential components
of the entitiy that is being defined. For example, entity Employee
consists of Name, Address and DateOfBirth entities. The Name,
Address and DateOfBirth entities are essential components of
entity Employee.
 Association: is a process where an entity is constructed from
the relationships that are defined between two entities. For
example, an entity Emplyee-Department is constructed from two
entities Employee and Department to show the departments that
are assigned to each employee.
Dr. Nawaz Khan; Email: [email protected]
39
Example: EER Modelling
Employee
Name
Lecture 3
Employee ID
Full-time
Employee
Monthly
salary
Dr. Nawaz Khan; Email: [email protected]
Date of Birth
Address
Part-time
Employee
Hourly Rate
salary
40
EER Modelling: When?
Lecture 3
The choice of using EER modelling technique depends on
the following factors:
 some entities share common attributes with others but not
with all
 an entity type that shares common attributes with others,
has very specific relationship and that is unique to that
entity type
 specific business rules/constraints need to be applied
 an entity has an especial relationship with its own
instances, in other words when entities demonstrate
recursive relationships
 if ‘is a’ and ‘has a’ relationships are identified, for example,
a Truck is a Vehicle and Truck has a Part.
Dr. Nawaz Khan; Email: [email protected]
41
UML Notations: Class and Association
Class
Employee
Class Name
EID
Name
Address
DateOfBirth.
Attributes
Lecture 3
CalcHour()
CalcAmount()
AssignJob(Job)
Operations
Association
Employee
0..1
AssignJob
0..1
Job
Employee
1
AssignJob
1..*
Job
Employee
*
AssignJob
*
Job
Dr. Nawaz Khan; Email: [email protected]
42
UML Notations: Generalisation
Employee
Lecture 3
EID
Name
Address
DateOfBirth.
AssignJob(Job)
employeeType
FullTimeEmployee
PartTimeEmployee
SalaryGrade
HourRate
CalcPension()
CalcWeeklyAmount()
Dr. Nawaz Khan; Email: [email protected]
43
UML Notation: Generalisation and Aggregation
School
1
1..*
Lecture 3
Course
1..*
Employee
1..*
Student
EID
Name
Address
DateOfBirth.
AssignJob(Job)
employeeType
FullTimeEmployee
PartTimeEmployee
SalaryGrade
HourRate
CalcPension()
CalcWeeklyAmount()
Dr. Nawaz Khan; Email: [email protected]
44
Activity Week3
 Draw an ER diagram for a Bank. Each bank can have
multiple branches, and each branch can have multiple
accounts and loans.
Lecture 3
 List the entities
 Identify PK
 What modification you need if you like to include customer in
the schema
Dr. Nawaz Khan; Email: [email protected]
45