CIS 328 Database Systems I Chapter 2 EntityRelationship Model

1
CIS 328 Database Systems (I) Chapter
2Entity-Relationship Model
2
Entity Relationship Model (ER)

• ER model was proposed by Peter Chen in 1976
• ER model has become the standard tool for
  conceptual schema design
• ER model consists of three basic constructs
  entities, attributes and relationships.

3
What is an entity ?

• An entity is a thing in the real world with an
  independent existence.
• It may be an object with physical existence (e.g.
  person, car, house, employee), or it may be an
  object with conceptual existence (company, job,
  university, course).

4
Entity and Entity Set

• Two types of entities
• Strong entity can exist independently (or can
  uniquely identify itself)
• Weak entity existence depends on the existence
  of other (strong) entity or entities
• Examples
• An employee is a strong entity but the dependents
  of the employee could be weak entities
• An account in a bank is a strong entity but a
  transaction could be a week entity

5
Entity and Entity Set

• An entity type defines a set of entities that
  have the same attributes.
• STUDENT is an entity type (Schema)
• An entity set is a collection of entities of the
  same entity type
• Examples
• Rema, Ali, Amal, Samer, Rana are entity set of
  an entity type STUDENT

6
Attributes

• An entity has a set of attributes that describes
  it.
• Person(SSN, Name, Address, Job-description,
  Salary).
• An entity will have a value for each of its
  attributes
• (999-010-201, John Smith, 20 Alebany Rd,
  Cardiff, UK, Manager, 2500)
• The properties of an entity set are called
  attributes of the entity set.
• Students SSN, Name, Address, GPA, Status, ...
• Books Title, ISBN, Authors, Publisher, Year, ...
  
• For a given application, only a limited number of
  attributes of an entity set are of interest

7
Types of Attributes

• Simple (or atomic) attribute is a one which
  cannot be divided into smaller parts.
• Examples SSN, GPA, Salary.
• Composite attribute is an attribute which can be
  divided into smaller subparts, these subparts
  represent more basic attributes with independent
  meanings of their own.
• Examples Name First_Name, Middle_Name,
  Last_Name
• Address Street_Address, City, State, Zip code

8
An Example of a composite attribute
House No.
9
Types of Attributes

• A single-valued attribute is a one which has one
  (single) value for a particular entity.
• Example Age, BirthDate
• A multi-valued attribute is a one which may have
  one or more values for the same entity.
• College Degrees for Person 0, 1, 2, 3,
• Color for a Car 1, 2, ..
• Authors of Books
• Phone Number

10
Types of Attributes

• A stored attribute is a one whose value is
  explicitly stored in the database.
• e.g. name, birth-date.
• Derived-attributes whose values are computed
  from other attributes.
• Age from Birthdate
• Annual Salary from Monthly Salary
• NoOfEmployees gt Count number of employees in
  the Employee table.

11
Null Values

• An attribute may have null as its value.
• Null may mean
• not applicable (college degree)
• Unknown
• Missing (height of Smith)
• Not known (home phone for Smith).

12
Keys

• Key attribute is an attribute whose values are
  distinct (unique) for a given entity type.
• Keys may be
• simple one attribute (SSN), or
• composite a set of attributes whose values
  together uniquely identify an entity type
• Name(first name, father name, grandfather name,
  family name)

13
Value Sets or Domains

• A value set V or domain for a simple attribute A
  specifies the set of values that may be assigned
  to that attribute for each individual entity.
• e.g. Employee.Age Age gt16 and Age lt60
• gt V(Age) dom(Age) 16, 17, 18, , 60
• dom(StudentMajors) math, cs, ce, med, ee, me,
  nursing,

14
Complex Attribute

• AddressPhone(Phone(AreaCode, Phone),
  Address(StreetAddress(No, Name, AptNo), City,
  State))
• The above example states that a person may have
  several residences and each residence may have
  several phones. Phone numbers consist of area
  code and phone number. Address, by contrast,
  consists of street, city and state. Street
  address is a composite attribute which consists
  of street number, name and apartment number.
• ( ) indicates a composite attribute
• indicates a multi-valued attribute.

15
Relationship Types

• A relationship type is represented as
  diamond-shaped box which is connected by straight
  lines

16
Relationship Degree
Degree of a relationship type is the number of
participating entity types binary relationships,
ternary relationships, .
WORKS-FOR
COMPANY
EMPLOYEE
Binary Relationships
17
Ternary Relationships
SUPPLY
PROJECT
SUPPLIER
PART
18
Ternary Relationships
SELLS
PRODUCT
COMPANY
COUNTRY
19
Binary Recursive Relationships
EMPLOYEE
Supervises
Marry
PERSON
20
Relationships

• The role name signifies the role that a
  participating entity from the entity type plays
  in each relationship instance
• Going back to the previous example, the entity e1
  plays the role of a supervisor, while entity e2
  plays the role of a supervisee

21
Cardinality Ratio

• Specifies the number of relationship instances
  that an entity can participate in
• Common cardinality ratios for binary relationship
  types are 11, 1N, and MN.

22
1N
WORKS-FOR
N
1
COMPANY
EMPLOYEE
An employee works for one company, and a company
has many employees working for it.
23
11
HAS
1
1
MANAGER
DEPARTMENT
A department has one manager and a manager
manages one department.
24
MN
WORKS-ON
M
N
PROJECT
EMPLOYEE
An employee works on many projects, and a project
has many employees working on it.
25
Participation Constraints

• Specifies whether the existence of an entity
  depends on its being related to another entity
  via the relationship type.
• There is total and partial participation.

26
Total participation
WORKS-FOR
N
1
DEPARTMENT
EMPLOYEE
Total participation.
Every employee must be related to a department
via WORKS-FOR relationship. A department must
have at least one employee.
27
Partial participation
N
1
Buys
PERSON
CAR
A person may buy a car and car may be bought by a
person
28
Total Partial participation
1
N
PROFESSOR
DEPARTMENT
Manages
A professor may manage a department (partial
participation), but a department must be managed
by a professor (total participation).
29
Attributes of Relationship Types
Works-for
1
N
EMPLOYEE
DEPARTMENT
Start-Date
We may keep a start date attribute to record for
each employee the date he/she started work for a
certain department.
30
A weak entity type is an entity which does not
have any key attributes
Works-for
Department
Employee
1
identifying relationship
Dependents
Fname
N
Sex
Dependent
Birthdate
Relationship
31
Weak Entity Types

• A weak entity type always has a total
  participation with its identifying entity type
• A Weak entity type has a partial key, i.e. this
  key is enough to identify its extension within
  the scope of its identifying entity type
• In the previous example, the first name is enough
  to identify kids within a single family, but is
  not enough to identify entities as stand alone
  entities (two families may use identical names
  for their kids)

32
ER Notations
Entity Type
ltNamegt
Attribute
ltNamegt
ltNamegt
Key Attribute
Multi-valued attribute
ltNamegt
33
ER Diagram Notations
Weak Entity Type
ltNamegt
Relationship Type
ltNamegt
Identifying Relationship Type
ltNamegt
34
ER Notations
ltNamegt
ltNamegt
Composite Attribute
ltNamegt
Derived Attribute
ltNamegt
partial key attribute
ltNamegt
35
Notations

• Entity Types
• singular name, capital letters
• Relationship Types
• usually singular verbs, capital letters
• Attribute
• nouns, capitalized
• Role names
• are in lowercase letters
• ER diagrams are drawn such that they are readable
  from left to right and top to bottom (Except weak
  entity types)

36
ER Notations
Total participation of E2 in R
R
E1
E2
Cardinality Ratio 1N for E1 and E2 in R
1
N
R
E2
E1
37
Relationships

• Several relationships may exist among the same
  set of entity sets.

Works_in
Employees
Departments
Manages
38
Degree of a Relationship (1)

• Definition The degree of a relationship is the
  number of entity sets participating the
  relationship.
• Recursive relationship
• Examples
• Supervises on Employees
• is_prerequisite_of on Courses
• is_classmate_of on Students

39
Degree of a Relationship (2)

• Binary relationship (degree 2)
• Examples
• takes between Students and Courses
• owns between Persons and Cars
• Ternary relationship (degree 3)
• Examples
• orders among Customers, Parts and Suppliers
  
• skill_used among Engineers, Skills and Projects

40
Cardinality (1)

• One-to-one (1-to-1) relationship between E1 and
  E2
• for each entity in E1, there is at most one
  associated entity in E2, and vice versa.
• Examples of 1-to-1 relationships
• Binary 1-to-1 relationship
• manages between Employees and Departments
• recursive 1-to-1 relationship
• is_married_to on Persons

41
Cardinality (2)

• One-to-many (1-to-m) relationship from E1 to E2
  for each entity of E1, there are zero or more
  associated entities of E2, but for each entity of
  E2, there is at most one associated entity of E1
• Examples of 1-to-m relationships
• binary 1-to-m relationship
• advises between Professors and Students
• recursive 1-to-m relationship
• is_mother_of on Persons
• Many-to-one (m-to-1) relationship from E1 to E2
  same as 1-to-m relationship from E2 to E1

42
Cardinality (3)

• Many-to-many (m-to-m) relationship between E1 and
  E2 for each entity in E1, there are zero or more
  associated entities in E2, and vice versa
• Examples of m-to-m relationships
• binary m-to-m relationship
• takes between Students and Courses
• recursive m-to-m relationship
• is_component_of on Parts

43
ER Diagram (1)

Recursive relationship
is_married_to
1
1
Person
SSN
Name
Age
44
ER Diagram (2)
binary relationship

1
m
Professor
Student
advises
Age
SSN
Name
SSN
Name
Age
45
ER Diagram (3)

ternary relationship
Engineer
Skill_used
Skill
Project
46
Role of an Entity Set (1)

• Definition The role of an entity set in a
  relationship is the function it performs in the
  relationship.
• Case 1 Role can be determined from properly
  chosen names.

takes
m
n
Student
Course
1
is_TA_of
1
47
Role of an Entity Set (2)

• Case 2 Roles need to be explicitly given.

is_married_to
supervises
1
m
1
1
wife
husband
supervisor
supervisee
Person
Employee
48
Attribute of Relationship (1)

• Where to keep the grade information?

m
n
takes
Student
Course
grade
49
Attribute of Relationship (2)

• Another example

Supplier
m
Quantity
orders
n
r
Part
Project
50
Cardinality Constraint (1)

• One in ER model means zero or one
• Many in ER model means zero or more
• Cardinality constraints make them more precise

(1, 5)
(5, 60)
takes
Student
Course
51
Cardinality Constraint (2)

• General format
• 0 ? min_card ? max_card
• Interpretation
• Each entity in E may involve between min_card and
  max_card relationships in R.

(min_card, max_card)
E
R
52
Cardinality Constraint (3)

• Definition
• If every entity in E involves at least one
  relationship in R (i.e., min_card gt 1), E is
  said to have total participation in R
• If min_card 0, E is said to have partial
  participation in R

53
Cardinality Constraint (4)

• Employees has a partial participation.
• Departments has a total participation.

(0, 1)
(1,1)
manages
Employee
Department
54
Representing 1-to-1, 1-to-m, m-to-mRelationships

(0, 1)
(0, 1)
one-to-one
R
E
F
(0, m)
(0, n)
many-to-many
R
E
F
(0, m)
(0,1)
one-to-many
R
E
F
1
m
R
E
F
55
An Example Database Application

• Company Database

56
An Example Database Application

• The Company database keeps track of a companys
• Employees, Departments, Projects
• The following are the requirements and
  specifications
• The company is organized into departments.
• Each department has a
• unique name, unique number
• particular employee who manages the department
• We keep track of the start date when that
  employee began managing the department
• A department may have several locations

57
An Example Database Application

• A department controls a number of projects, each
  of which has a
• unique name, unique number, and single location
• We store each employees
• name, social security number, address, salary,
  sex, and birth date.
• An employee is assigned to one department but may
  work on several projects, which are not
  necessarily controlled by the same department
• We keep track of the number of hours per week
  that an employee works on each project
• We keep track of the direct supervisor of each
  employee

58
An Example Database Application

• We want to keep track of the dependents of each
  employee for insurance purposes.
• We keep each dependents first name, sex, birth
  date, and relationship to the employee

59
ER diagram for the company database
Each department has a unique name, a unique
number, particular employee who manages the
department. A department may have several
locations.
DEPARTMENT
60
ER diagram for the company database
A department controls a number of projects, each
of which has a unique name, unique number, and
single location
PROJECT
61
ER diagram for the company database
We store each employees name, social security
number, address, salary, sex, and birth date.
EMPLOYEE
62
ER diagram for the company database
We want to keep track of the dependents of each
employee for insurance purposes. We keep each
dependents first name, sex, birth date, and
relationship to the employee
DEPENDENT
63
ER diagram for the company database
Each department has a particular employee who
manages the department We keep track of the start
date when that employee began managing the
department
(1,1)
(0,1)
EMPLOYEE
DEPARTMENT
manager
department managed
64
ER diagram for the company database
An employee is assigned to one department.
(1,N)
(1,1)
EMPLOYEE
DEPARTMENT
employee
department
65
ER diagram for the company database
A department controls a number of projects
(1,1)
(0,N)
DEPARTMENT
PROJECT
controlling department
controlled project
66
ER diagram for the company database
An employee is assigned to one department but may
work on several projects, which are not
necessarily controlled by the same department. We
keep track of the number of hours per week that
an employee works on each project
(1,N)
(0,N)
EMPLOYEE
PROJECT
worker
project
67
ER diagram for the company database
We keep track of the direct supervisor of each
employee
EMPLOYEE
(0,N)
(0,1)
supervisor
supervisee
68
ER diagram for the company database
We want to keep track of the dependents of each
employee for insurance purposes.
(1,1)
(0,N)
EMPLOYEE
DEPENDENT
employee
dependent
69
Ternary relationship vs. Binary relationship

• A ternary relationship may not be represented by
  multiple binary relationships

m
supplies
Supplier
Supplier
m
r
n
supply
Project
can-supply
r
n
u
s
t
Part
Project
Part
uses

70
Ternary relationship vs. Binary relationship

• Supply(s, j, p)
• supplier s supplies project j with part p
• can-supply(s, p)
• supplier s supplies part p
• supplies(s, j)
• supplier s supports project j with parts
• uses(j, p)
• project j uses part p

71
Ternary relationship vs. Binary relationship

• Are the previous two representations of the
  SUPPLY relationship type equivalent?
• Not necessarily. The two representations are
  equivalent if we add the constraint
• a supplier may supply parts for one project only

72
Ternary relationship vs. Binary relationship

• Ternary relationship usually provides more
  accurate information.
• supply supply_to can-supply uses
  
• s1 p1 j1 s1 j1 s1 p1
  j1 p1
• s1 p2 j1 s2 j1 s1 p2
  j1 p2
• s2 p1 j1 s2 j2 s2 p1
  j2 p2
• s2 p2 j2 s2 p2
• (s2, p2, j1) may be incorrectly derived from the
  binary relationships

73
E-R Diagram with a Ternary Relationship
N
N
N
74
Design Issues

• Use of entity sets vs. attributes
• Choice mainly depends on the structure of the
  enterprise being modeled, and on the semantics
  associated with the attribute in question
• Use of entity sets vs. relationship sets
• Possible guideline is to designate a relationship
  set to describe an action that occurs between
  entities
• Binary versus n-ary relationship sets
• Although it is possible to replace any nonbinary
  (n-ary, for n gt 2) relationship set by a number
  of distinct binary relationship sets, a n-ary
  relationship set shows more clearly that several
  entities participate in a single relationship
• Placement of relationship attributes

75
Specialization

• Top-down design process
• we designate subgroupings within an entity set
  that are distinctive from other entities in the
  set
• These subgroupings become lower-level entity sets
  that have attributes or participate in
  relationships that do not apply to the
  higher-level entity set
• Depicted by a triangle component labeled ISA
• E.g. customer is a person
• Attribute inheritance
• a lower-level entity set inherits all the
  attributes and relationship participation of the
  higher-level entity set to which it is linked

76
Specialization Example
77
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

78
Specialization and Generalization (Contd.)

• Can have multiple specializations of an entity
  set based on different features (Multiple
  inheritance)
• E.g. permanent-employee vs. temporary-employee,
  in addition to officer vs. secretary vs. teller
• Each particular employee would be
• a member of one of permanent-employee or
  temporary-employee,
• and also a member of one of officer, secretary,
  or teller
• The ISA relationship also referred to as
  superclass - subclass relationship

79
Design Constraints on a Specialization/Generalizat
ion

• Constraint on which entities can be members of a
  given lower-level entity set.
• condition-defined
• E.g. all customers over 65 years are members of
  senior-citizen entity set senior-citizen ISA
  person
• user-defined
• Employees are assigned to one of four work teams
• Constraint on whether or not entities may belong
  to more than one lower-level entity set within a
  single generalization
• Disjoint
• an entity can belong to only one lower-level
  entity set
• Each bank account is savings account or checking
  account but not both
• Overlapping
• an entity can belong to more than one lower-level
  entity set
• Certain managers participate in more than work
  team

80
Design Constraints on a Specialization/Generalizat
ion (Contd.)

• Completeness constraint -- specifies whether or
  not an entity in the higher-level entity set must
  belong to at least one of the lower-level entity
  sets within a generalization
• total
• an entity must belong to one of the lower-level
  entity sets
• accounts generalization is total All account
  entities must be either a savings account or a
  checking account.
• partial
• an entity need not belong to one of the
  lower-level entity sets
• Some employee entities may not be members of any
  of the lower-level team entity sets

81
Aggregation

• Consider the ternary relationship works-on
• Suppose we want to record managers for tasks
  performed by an employee at a branch

82
Aggregation (Cont.)

• Relationship sets works-on and manages represent
  overlapping information
• Every manages relationship corresponds to a
  works-on relationship
• However, some works-on relationships may not
  correspond to any manages relationships
• So we cant discard the works-on relationship
• Eliminate this redundancy via aggregation
• Treat relationship as an abstract entity
• Allows relationships between relationships
• Abstraction of relationship into new entity
• Without introducing redundancy, the following
  diagram represents
• An employee works on a particular job at a
  particular branch
• An employee, branch, job combination may have an
  associated manager

83
E-R Diagram With Aggregation
84
Summary of Symbols Used in E-R Notation
85
Summary of Symbols (Cont.)
86
Alternative E-R Notations
87
E-R Diagram for a Banking Enterprise
88
2.9 Reduction of an E-R schema to Tables
89
Binary 11 relationship
1
1

• Either choose S and place the primary key of T as
  a foreign key in S.

FK
t1
r1
S(s1, , , ) T(t1, )
90
Binary 11 relationship
1
1

• Or choose T and place the primary key of S as a
  foreign key in T.

FK
t1
r1
T(t1, , , ) S(s1, )
It is always better to choose the entity with
total participation
91
Example Binary 11 relationship
location
No
Name
Dname
Date
manage
1
1
Department
Manager
This Department (Dname, location) Manager (No,
Name, Dname, date) OR
This is better
Department (Dname, location, no, date) Manager
(No, Name)
92
Binary 1N relationship
r1
r2
s1
t1
1
N
R
T
S
S(s1, , t1, r1, r2) T(t1, )
We place the FK in the entity which has
cardinality 1
T(t1, , s1, r1, r2) S(s1, )
93
Binary MN relationship
e2
r1
r1
e1
M
N
R
E2
E1
Create a new relation R to represent R
E1(e1, ..) E2(e2, ..) R(e1, e2, r1, r2)
e1 is FK in R references E1 and e2 is FK in R
references E2 Both form a PK for R
94
Multi-valued attributes
K
e1
E1
E1(K, ) R(K, e1)
K is a FK in R references E1 and K and e1 form a
PK for R
95
Multi-valued attributes
SSN
Phone
Employee (SSN, ) Phone (SSN, Phone)
Employee
Another solution, but above is better Employee
(SSN, , phone1, phone2, phone3)
96
n-ary relationship R, n gt 2,
For each n-ary relationship R, n gt 2, create a
new relation to represent R. The primary key of R
will be the primary keys of all entity types that
participate in R
97
n-ary relationship R, n gt 2,
e1
e3
e2
E1
R
E3
E2
r1
E1(e1, ) E2(e2, ) E3(e3, ) R(e1, e2, e3, r1)
98
Weak Entity Type
e2
e3
e1
E(e1, e2, e3, ) W(e1, w1, w2,w4, w5) e1 is a FK
in W references E and both w1 and e1 form a PK of
W
E
r
w4
w5
w1
w3
W
w2
99
An Example (1)

1
m
advises
Professor
Student
Rank
SSN
Name
SSN
Name
GPA
100
An Example (2)

• Two relations are sufficient
• Professors Students
• SSN Name Rank SSN Name GPA PSSN
• 123 Jack Prof. 456 John 3.4
  123
• 234 Ann Prof. 567 Carl 3.2
  123
• 345 Bob Prof. 678 Ken 3.5
  345

101
Transform Binary Relationship (1)

• Case 1 one-to-many relationship x 1 and y m
  (or x (?, m) and y (?, 1))
• E(A, B), F(C, D, A)
• Relationship R is transformed to a foreign key
• Attribute A in E is a foreign key

A
B
D
C
x
y
F
E
R
102
Transform Binary Relationship (2)

• Depts(Name, Location)
• Employees(SSN, Name, Age, Dept_name)
• Renaming is useful for understandability.

Age
SSN
Name
Name
Location
m
1
work_in
Employees
Depts
103
Transform Binary Relationship (3)

• Case 2 one-to-one relationship
• Case 2.1 x (1, 1) and y (1, 1)
• This one
• E(A, B), F(C, D, A)
• Or this one
• E(A, B, C), F(C, D)

A
B
D
C
x
y
F
E
R
104
Transform Binary Relationship (4)
Age
SSN

• gt Depts(Name, Location)
• Managers(SSN, Name, Age, Dept_name)
• gt Depts(Name, Location, Manager_SSN)
• Managers(SSN, Name, Age)

Name
Name
Location
(1, 1)
(1, 1)
work_in
Managers
Depts
105
Transform Binary Relationship (5)

• Case 2.2 x (0, 1) and y (0, 1)
• (or x 1 and y 1, and both E and F are
  partial participation)
• Use the same transformation rule for Case 2.1
• Or place the FK in a table with less size

106
Transform Binary Relationship (6)

• Case 2.3 x (0, 1) and y (1, 1)
• gt E(A, B), F(C, D, A)
• The entity set with the total participation is
  transformed to a relation with a foreign key.

A
B
D
C
x
y
F
E
R
107
Transform Binary Relationship (7)

• gt Depts(Name, Location, Manager_SSN)
• Employees(SSN, Name, Age)
• Why not let Employees have the foreign key?

Age
SSN
Name
Name
Location
(0, 1)
(1, 1)
manages
Employees
Depts
108
Transform Binary Relationship (8)

• Case 2.4 x (1, 1) and y (0, 1)
• gt E(A, B, C), F(C, D)

A
B
D
C
x
y
F
E
R
109
Transform Binary Relationship (9)

• Case 3 many-to-many relationship
• x m and y n
• Case 3.1 R has no attribute
• gt E(A, B), F(C, D), R(A, C)
• Transform the m-to-m relationship to a separate
  relation.
• R has two foreign keys.
• The key of R consists of the foreign keys

110
Transform Binary Relationship (10)
Age
SSN

• gt Students(SSN, Name, Age)
• Courses(Course, Title)
• Takes(SSN, Course)
• Case 3.2 R has attribute Z
• gt E(A, B), F(C, D), R(A, C, Z)

Name
Course
Title
m
n
takes
Students
Courses
111
Transform Ternary Relationship

• gt E1(A, B), E2(C, D), E3(G, H),
• R(A, C, G, Z)

D
C
B
A
Z
E2
E1
R
E3
H
G
112
Transform Recursive Relationship (1)

• Create a shadow entity set and transform the
  recursive relationship into a binary relationship
• Apply the rules for transforming binary
  relationships
• After the transformation, remove one redundant
  relation, or if there is no redundant relation,
  remove the relation with fewer attributes

113
Transform Recursive Relationship (2)

Title
Course
Title
Course
m
Courses
Courses
prereq
m
n
n
Course
prereq
Courses
Title
Courses(Course, Title) Prereq(Course,
Prereq-Course)
114
Transform Recursive Relationship (3)

Name
Name
SSN
Persons(SSN, Name, Age, Spouse_SSN)
SSN
Age
Age
(0,1)
Persons
mar_to
Persons
(0,1)
(0,1)
(0,1)
married_to
Persons
115
Transform Recursive Relationship (4)

Name
Name
SSN
Persons(SSN, Name, Age, Mother_SSN)
SSN
Age
Age
1
Persons
mo_of
Persons
m child
1 mother
m
mother_of
Persons
116
Transform Multi-valued Attribute (1)

• Create a separate relation for each multi-valued
  attribute.
• E_C.A should be defined to be a foreign key
  referencing E.A

B
A
C
E
E(A, B) E_C(A, C)
117
Transform Multi-valued Attribute (2)

• gt Books (ISBN, Title, Publisher)
• Book_Authors (ISBN, Author)
• Define Book_Authors.ISBN as a foreign key
  referencing Books.ISBN

Publisher
ISBN
Authors
Title
Books
118
Transform Composite Attribute (1)

• Method 1 Use only simple attributes and ignore
  the composite attribute.
• gt

D
H
B
A
C
E(A, D, H, C)
E
119
Transform Composite Attribute (2)

• Method 2 Transform the composite attribute to a
  separate relation.
• gt

E(A, C), E_B (A, D, H)
D
H
B
A
C
E
120
Transform Composite Attribute (3)

• An Example using method 2

Format
Height
Width
Bitmap
Picture
SSN
Age
Name
Salary
Employees
Employees (SSN, Name, Age, Salary) Emp_Pic (SSN,
Bitmap, Format, Height, Width)
121
Transform Weak Entity Set

• ? E (A, B, C)
• F(A, D, G, H)
• The key of F consists of the Primary key of E and
  the partial key of F.
• F.A is a foreign key referencing E.A

B
A
C
G
D
H
1
m
E
F
R
122
E-R Diagram for a Banking Enterprise