Research and Data Modelling

Snug

lecture4.pdf

Home >Information Systems homework help >Research and Data Modelling

Chapter 4

Entity Relationship (ER) Modeling

Learning Objectives

• In this chapter, students will learn:

• The main characteristics of entity relationship components

• How relationships between entities are defined, refined, and incorporated into the database design process

• How ERD components affect database design and implementation

• That real-world database design often requires the reconciliation of conflicting goals

Entity Relationship Model (ERM)

• Basis of an entity relationship diagram (ERD)

• ERD depicts the:

• Conceptual database as viewed by end user

• Database’s main components

-Entities

-Attributes

-Relationships

• Entity - Refers to the entity set and not to a single entity occurrence

Attributes

• Characteristics of entities

• Required attribute: Must have a value, cannot be left empty

• Optional attribute: Does not require a value, can be left empty

• Domain - Set of possible values for a given attribute

• Identifiers: One or more attributes that uniquely identify each entity instance

Figure 4.1 - The Attributes of the Student Entity: Chen and Crow’s Foot

Attributes

• Composite identifier: Primary key composed of more than one attribute

• Composite attribute: Attribute that can be subdivided to yield additional attributes

• Simple attribute: Attribute that cannot be subdivided

• Single-valued attribute: Attribute that has only a single value

• Multivalued attributes: Attributes that have many values

Figure 4.3 - A Multivalued Attribute in an Entity

Attributes

• Multivalued attributes: Attributes that have many values and require creating:

• Several new attributes, one for each component of the original multivalued attribute

• A new entity composed of the original multivalued attribute’s components

• Derived attribute: Attribute whose value is calculated from other attributes

• Derived using an algorithm

Figure 4.6 - Depiction of a Derived Attribute

Table 4.2 - Advantages and Disadvantages of Storing Derived Attributes

Relationship Strength

Weak (non-identifying) relationship

• Primary key of the related entity does not contain a primary key component of the parent entity

Strong (identifying) relationships

• Primary key of the related entity contains a primary key component of the parent entity

Figure 4.8 - A Weak (Non-Identifying) Relationship between COURSE and CLASS

Strong Entity Conditions:

• Not Existence-dependent

• Has Weak relationship

• Has its own primary key

Figure 4.9 - A Strong (Identifying) Relationship between COURSE and CLASS

Weak Entity Conditions:

• Existence-dependent

• Has strong relationship

• Has a primary key that is partially or totally derived from parent entity in the relationship

• Database designer determines whether an entity is weak based on business rules

Figure 4.10 - A Weak Entity in an ERD

Relationships – covered in last chapter

• Association between entities that always operate in both directions

• Participants: Entities that participate in a relationship

• Connectivity: Describes the relationship classification

• Cardinality: Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity

Figure 4.7 - Connectivity and Cardinality in an ERD (covered in L3)

Relationship Participation (covered in L3)

Optional participation

• One entity occurrence does not require a corresponding entity occurrence in a particular relationship

Mandatory participation

• One entity occurrence requires a corresponding entity occurrence in a particular relationship

Table 4.3 - Crow’s Foot Symbols

Figure 4.13 - CLASS is Optional to COURSE

Figure 4.14 - COURSE and CLASS in a Mandatory Relationship

Relationship Degree

• Indicates the number of entities or participants associated with a relationship

• Unary relationship: Association is maintained within a single entity

• Recursive relationship: Relationship exists between occurrences of the same entity set

• Binary relationship: Two entities are associated

• Ternary relationship: Three entities are associated

Figure 4.15 - Three Types of Relationship Degree

Figure 4.17 - An ER Representation of Recursive Relationships

Associative Entities

• Also known as composite or bridge entities

• Used to represent an M:N relationship between two or more entities

• Is in a 1:M relationship with the parent entities

• Composed of the primary key attributes of each parent entity

• May also contain additional attributes that play no role in connective process

Figure 4.23 - Converting the M:N Relationship into Two 1:M Relationships

Developing an ER Diagram - Review

• Create a detailed narrative of the organization’s description of operations

• Identify business rules based on the descriptions

• Identify main entities and relationships from the business rules

• Develop the initial ERD

• Identify the attributes and primary keys that adequately describe entities

• Revise and review ERD

Figure 4.26 - The First Tiny College ERD Segment

Figure 4.27 - The Second Tiny College ERD Segment

Figure 4.28 - The Third Tiny College ERD Segment

Figure 4.29 - The Fourth Tiny College ERD Segment

Figure 4.30 - The Fifth Tiny College ERD Segment

Figure 4.31 - The Sixth Tiny College ERD Segment

Figure 4.32 - The Seventh Tiny College ERD Segment

Figure 4.33 - The Eighth Tiny College ERD Segment

Figure 4.34 - The Ninth Tiny College ERD Segment

Table 4.4 - Components of the ERM

Database Design Challenges: Conflicting Goals

Database design must conform to design standards

Need for high processing speed may limit the number and complexity of logically desirable relationships

Need for maximum information generation may lead to loss of clean design structures and high transaction speed

Figure 4.38 - Various Implementations of the 1:1 Recursive Relationship

Primary Key Guidelines

Modelling Activity case 1: Create ERD for the following case

Prepare to share this with the others on

the online meeting

Modelling Practice

• Create ERDs then RDM for each of these scenarios:

1- Wagga Hospital needs to keep records of the nurses who

work in each Ward.

Business Rule: Each nurse can work only in one ward

Prepared By: Antoinette Cevenini

Sample Answer -- ERD

• Add the PKs and FKs where appropriate

Prepared By: Antoinette Cevenini

ANSWER 1 - ERD:

Modelling Practice

• Create ERDs then RDM for each of these scenarios:

2- Wagga Hospital needs to keep records of the persons

admitted to each ward.

Business Rules & Constraints:

• Each ward can have many patients who have been

admitted.

• Admission date and time need to be recorded

• A patient may be admitted more than once, either to the

same ward or to another ward.

Prepared By: Antoinette Cevenini

Modelling Practice – Sample Answer

WARD (ward_Code, Ward name, ward_loc) PRIMARY KEY (ward_Code)

PATIENT(patient-id, p_surname, p_first-name, ward_Code) PRIMARY KEY (patient_ID) FOREIGN KEY (ward_Code) REFERENCES ward table

ANSWER 2 - ERD:

Could a patient be

admitted more than once ?

ANSWER 2 - RDM:

Prepared By: Antoinette Cevenini

Modelling Practice

• Create ERDs for each of these scenarios:

Business Rules & Constraints:

• A patient may be admitted more than once, either to the

same ward or to another ward.

? How does your ERD change? Modify accordingly

Prepared By: Antoinette Cevenini

ANSWER 2:

Modelling Practice – Sample Answer 52

What other attributes should admission have?

ADMISSION (admission_id, admission_date, admission_time, p_id,

W_Code)

Prepared By: Antoinette Cevenini

Modelling Practice

• Create ERDs then RDM for each of these scenarios:

3- When a patient is admitted, the expected

operation code is recorded. But when they have the

actual operation, what is actually done may be

different to the actual operation code recorded.

A patient who is admitted may have several

operations.

Operations have the operation-code recorded. For

example HB = Heart bypass

ANSWER 3 - ERD:

Modelling Practice – Sample Answer

ADMISSION (admission_id, admission_date, admission_time, p_id, W_Code, expected-op)

OPERATION (O_id, O_Name, actual-op, admission-id, operation-date) FOREIGN KEY (admission-id) REFERENCES admission table

Prepared By: Antoinette Cevenini

Modelling Activity case 2 Work in groups of 3

Prepared By: Antoinette Cevenini

Modelling Activity case 3 : Work in groups of 3

66 Prepare the entity relational diagrams for each of the following cases:

Use either the Chen or the Crows Foot Notations.

1. An owner can own many dogs, but a dog can only have one owner.

2. A supplier of wine can supply many different wines. For example CSU winery supplies 1997 CSU Shiraz, 1998 CSU Chardonnay etc. Each of these wines can only come from

the CSU winery.

3. A company supplies parts. They receive many customer orders which each contain many items. Each item on an order has a certain quantity, description and specific

price.

4. Pure-bred dogs can only be of one breed, whilst a breed will have many dogs of that type.

5. A courier company employs a number of drivers; each driver may deliver many consignments, but is always assigned to one truck.

6. Big Construction is an engineering company that works on several construction projects at any one time. Each project may require several engineers to work on it and an

engineer must work on at least one project. The date an engineer starts and ends works

on a project must be recorded.

7. Each task may require a number of parts to be used in order to complete it. A part may be used on many tasks or none at all. The number of parts used on a particular task may

differ depending on

the nature of the project. The number of parts used per task and project needs to be

recorded.

8. ATU offers several business courses; each course is supervised by only one professor. In some semesters the professor may also teach a class or two in addition to supervising

a course.

9. A caravan park has many cabin-types for hire. For example SC = standard cabin for $32 per night,

DL = delux cabin for $40 per night,

XD = extra delux cabin for $50 per night.

a) For each cabin-type there are several sites available. Each site must be of only one cabin-type.

b) A customer at a caravan park needs to book a cabin (or two or more) for the night

More modelling/design

Activities:

Basic ERDs

You may complete

these with the PASS

leader after class

Complete the ERDs as

they appear on the

following document

Go to app.gosoapbox.com and enter the following Event Access Code:

414-360-557

https://app.gosoapbox.com/

Next Week (wk-6) Normalisation