Programming Question

ER Modeling Example
Problem:
Create an ER model to represent the data used by the library.
The library provides books to borrowers. Each book is described by title, edition, and year of
publication, and is uniquely identified using the ISBN. Each borrower is described by his or her name and
address and is uniquely identified using a borrower number. The library provides one or more copies of
each book and each copy is uniquely identified using a copy number, status indicating if the book is
available for loan, and the allowable loan period for a given copy. A borrower may loan one or many
books, and the date each book is loaned out and is returned is recorded. Loan number uniquely
identifies each book loan.
Answer:
The ER model of the above description follows.
Entity-Relationship
Modelling
Pearson Education © 2014
ER diagram of Branch user views of DreamHome
2
Pearson Education © 2014
Concepts of the ER Model
Entity types
Relationship types
Attributes
3
Pearson Education © 2014
Entity Type
A Group of objects with same properties
1.
2.
Physical (or real) existence
Conceptual (abstract) existence
4
Pearson Education © 2014
Entity type
• We identify each entity type by a name and
a list of properties.
• Each uniquely identifiable object of an
entity type is referred to as an entity
occurrence.
5
Pearson Education © 2014
Diagrammatic representation of entity types
❑Each entity type is shown as a rectangle, labeled
with the name of the entity.
❑In Unified Modeling Language (UML), the first
letter of each word in the entity name is
uppercase.
6
Pearson Education © 2014
Relationship Types
Relationship type
A set of meaningful associations among
entity types.
Each relationship type is given a name that
describes its function.
Relationship occurrence
A uniquely identifiable association, which
includes one occurrence from each
participating entity type.
7
Pearson Education © 2014
Semantic net of Has relationship type
8
Pearson Education © 2014
ER diagram of Branch Has Staff relationship
• Each relationship type is shown as a line connecting the associated
entity types and labeled with the name of the relationship.
• Normally, a relationship is named using a verb (e.g., Supervises or
Manages) or a short phrase including a verb (e.g., LeasedBy).
• First letter is uppercase and labeled in one direction (arrow symbol).
9
Pearson Education © 2014
Degree of a Relationship
The number of participating entities in relationship
is called the degree of that relationship.
▪ A relationship of degree two is called binary
▪ A relationship of degree three is called ternary.
▪ A relationship of degree four is called quaternary.
▪ The term “complex relationship” is used to describe
relationships with degrees higher than binary.
➢ Represented by a diamond
➢ The name is inside the diamond
➢ Arrow is omitted
10
Pearson Education © 2014
Binary relationship: POwns
11
Pearson Education © 2014
Ternary relationship: Registers
12
Pearson Education © 2014
Quaternary relationship: Arranges
13
Pearson Education © 2014
Recursive Relationship
A relationship type where the same entity type participates more
than once in different roles.
Relationships may be given role names to indicate purpose that each
participating entity type plays in a relationship.
14
Pearson Education © 2014
Entities associated through two distinct relationships
with role names
Role names are usually not required if the function of the participating
entities in a relationship is unambiguous.
15
Pearson Education © 2014
Attributes
Attribute
A property of an entity or a relationship type.
▪ For example, a Staff entity type may be
described by the staffNo, name, position, and
salary attributes.
Attribute Domain
The set of allowable values for one or more
attributes.
▪ For example, the number of rooms associated
with a property is between 1 and 15 for each
entity occurrence.
16
Pearson Education © 2014
Attributes
Simple Attribute
Attribute composed of a single component with an
independent existence.
▪ Examples of simple attributes include position
and salary of the Staff entity.
▪ Simple attributes are sometimes called atomic
attributes.
Composite Attribute
Attribute composed of multiple components, each
with an independent existence.
▪ For example, address attribute of the Branch
entity with the value (163 Main St, Glasgow,
G11 9QX) can be subdivided into street (163
Main St), city (Glasgow), and postcode (G11
9QX) attributes.
17
Pearson Education © 2014
Attributes
Single-valued Attribute
Attribute that holds a single value for each occurrence of an
entity type.
▪
For example, each occurrence of Branch entity type has a single
value for the branch number (branchNo) attribute (e.g., B003)
Multi-valued Attribute
Attribute that holds multiple values for each occurrence of
an entity type.
▪ For example, Branch entity type can have multiple values for the
telNo attribute (for example, branch number B003 has telephone
numbers 0141-339-2178 and 0141-339-4439).
▪ A multi-valued attribute may have a set of numbers with upper and
lower limits. For example, minimum one and maximum 3 telephone
numbers.
18
Pearson Education © 2014
Attributes
Derived Attribute
Attribute that represents a value that is
derivable from value of a related attribute, or
set of attributes.
▪ For example, the value for the duration
attribute of the Lease entity is calculated from
the rentStart and rentFinish attributes.
19
Pearson Education © 2014
Keys
Candidate Key
Minimal set of attributes that uniquely identifies each occurrence of an
entity type.
▪ An entity type may have more than one candidate key.
▪ For example, a member of staff has a unique company-defined staff
number (staffNo) and also a unique National Insurance Number
(NIN) that is used by the government. We therefore have two
candidate keys—(1) staffNO and (2) NIN—for the Staff entity.
Primary Key
The Candidate key selected to uniquely identify each occurrence of an
entity type.
▪ The company-defined staff number contains a maximum of five
characters (for example, SG14), and the NIN contains a maximum
of nine characters (for example, WL220658D). Therefore, we select
staffNo as the primary key of the Staff entity type and NIN is then
referred to as the alternate key.
20
Pearson Education © 2014
Keys
Composite Key
A candidate key that consists of two or more attributes.
For example, consider an entity called Advert with propertyNo
(property number), newspaperName, dateAdvert, and cost
attributes. Many properties are advertised in many newspapers on a
given date. To uniquely identify each occurrence of the Advert
entity type requires values for the propertyNo, newspaperName,
and dateAdvert attributes. Thus, the Advert entity type has a
composite primary key made up of the propertyNo,
newspaperName, and dateAdvert attributes.
21
Pearson Education © 2014
Diagrammatic Representation of attributes
ER diagram of Staff and Branch entities and their attributes
22
Pearson Education © 2014
Strong and Weak Entity Type
Strong Entity Type
An entity type is referred to as being strong if its existence does not depend
upon the existence of another entity type.
▪ Examples: Staff, Branch, and Client entities.
▪ Each entity occurrence is uniquely identifiable using the primary key
attribute(s) of that entity type.
➢ For example, we can uniquely identify each member of staff using the
staffNo attribute, which is the primary key for the Staff entity type.
Weak Entity Type
A weak entity type is dependent on the existence of another entity type.
▪ For example, preference (see next slide).
▪ Each entity occurrence cannot be uniquely identified using only the
attributes associated with that entity type.
➢ For example, there is no primary key for the Preference entity
➢ It must use a foreign key in conjunction with its attributes to create a
primary key. The foreign key is typically a primary key of an entity it
is related to.
❖ For example, clientNo.
23
Pearson Education © 2014
Strong entity type: Client and weak entity type: Preference
24
Pearson Education © 2014
Attributes on Relationships
Relationship called Advertises with attributes
25
Pearson Education © 2014
Structural Constraints
The main type of constraint on
relationships is multiplicity.
➢ Multiplicity – the number (or range) of
possible occurrences of an entity type that
may relate to a single occurrence of an
associated entity type through a particular
relationship.
26
Pearson Education © 2014
Semantic net of Staff Manages
Branch relationship type
27
Pearson Education © 2014
ER Diagram: Multiplicity of Staff Manages
Branch (1:1) relationship
28
Pearson Education © 2014
Semantic net of Staff Oversees
PropertyForRent relationship type
29
Pearson Education © 2014
ER Diagram: Multiplicity of Staff
Oversees PropertyForRent (1:*)
relationship type
30
Pearson Education © 2014
Semantic net of Newspaper
Advertises PropertyForRent
relationship type
31
Pearson Education © 2014
ER Diagram: Multiplicity of
Newspaper Advertises
PropertyForRent (*:*) relationship
32
Pearson Education © 2014
Structural Constraints
Multiplicity for Complex Relationships
Number (or range) of possible occurrences
of an entity type in an n-ary relationship
when other (n-1) values are fixed.
33
Pearson Education © 2014
Semantic net of ternary Registers
relationship with values for Staff
and Branch entities fixed
34
Pearson Education © 2014
Multiplicity of ternary Registers
relationship
35
Pearson Education © 2014
Summary of multiplicity
constraints
36
Pearson Education © 2014
Structural Constraints
Multiplicity is made up of two types of
restrictions on relationships: cardinality
and participation.
37
Pearson Education © 2014
Structural Constraints
Cardinality
Describes maximum number of possible
relationship occurrences for an entity
participating in a given relationship type.
Participation
Determines whether all or only some entity
occurrences participate in a relationship.
38
Pearson Education © 2014
Structural Constraints
The most common degree for
relationships is binary.
Binary relationships are generally
referred to as being: (Cardinality)
one-to-one (1:1)
one-to-many (1:*)
many-to-many (*:*)
39
Pearson Education © 2014
Multiplicity as cardinality and
participation constraints
40
Pearson Education © 2014
Assignment: ER Model
Read lecture notes: ER Model SAU.ppt and LibraryERModelExample.pdf and then solve the following
problem.
Problem:
Create an ER model for the following descriptions:
A large organization has several parking lots, which are used by staff. Each parking lot has a unique
name, location, capacity, and number of floors (where appropriate). Each parking lot has parking spaces,
which are uniquely identified using a space number. A parking space can be marked with handicapped.
Members of staff can request the sole use of a single parking space. Each member of staff has a unique
ID number, first name, last name, date of birth, telephone number, email address, and vehicle license
number.
Provide any assumptions necessary to support your model.