Lecture 2: Entity Relationship Modelling

Question 1

List four concepts of the ER Model

Answer 1

1. Entity types
2. Relationship Types
3. Attributes
4. Multiplicity

Question 2

Why is conceptual design important?

Answer 2

It helps communicate database designs to non-technical users.

Also can be mapped as a relational schema.

Question 3

What are the types of ER representations?

Answer 3

1. Chen’s representation
2. UML representation (mordern used)

Question 4

What is an entity type?

Answer 4

Group of objects with same properties, identified as having an independent existence. Eg.. Beer

Question 5

Define Entity Occurrence.

Answer 5

Uniquely identifiable object of an entity type

e.g. Beer - Summers, XXX, Extra Dry

Question 6

Define what is a Relationship type.

Give an example.

Answer 6

Set of meaningful associations among entity types.

e.g. Song - “SING” - Artist

Question 7

Explain Relationship Occurrence.

Give an example.

Answer 7

Uniquely identifiable association, which includes one occurrence from each participating entity type.

e.g. “humpty dumpty - SINGS - Adele”

Question 8

Explain and give examples of “Degree of a Relationship”

Give examples of Each.

Answer 8

Number of participating entities in relationship.

1. Degree 2: Binary

• Artist sings song
• Customer buys Beer

1. Degree 3: Ternary
   * A waiter SERVES beer SERVES Customers
2. Degree 4: Quaternary

Question 9

Explain what a recursive relationship is.

Answer 9

Relationship type where same entity type participates more than once in different roles.

• lisa FREINDS WITH Jane
• Coke TASTES LIKE Pepsi

Question 10

Explain characteristics of an Attribute.

Answer 10

Attribute: Property of an entity or a relationship type.

e.g “A car”

• Year = 2010
• Type = Sedan
• Size = Small
• Colour = Red
• Number PLate = NULL

Question 11

What is an attribute Domain?

Answer 11

• Set of allowable values for one or more attributes
• Similar domains in entity relationship model
• Similar to data types in SQL
  
  • Not usually represented in ER Schemas.

Question 12

What are composite Attributes?

Answer 12

• Simple Attribute
  
  • Attribute composed of a single component with an independent existence
• Composite Attribute
  
  • Attribute composed of multiple components, each with an independent existence
  • E.g. STAFF
    
    • ID
    • Name
      
      • First name
      • Middle Name
      • Last Name

Question 13

Explain what a Multi-Valued attribute is.

Answer 13

• Single Valued attribute
  
  • ​attribute that holds a single value for each occurrence of an entity type​
• Multi-valued attirbute
  
  • attribute that holds multiple values for each occurrence of an entity type
  • e.g STAFF
    
    • ID
    • Degrees[1..*]

Question 14

Explain what a derived Attribute is

Answer 14

Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not neccarilly in the same entity type.

Question 15

List and give definitions of KEYs:

1. Candidate
2. Primary
3. Composite

​​

Answer 15

Candidate Key

• Minimal set of attributes that uniquely identifies each occurrence of an entity type

Primary Key

• Candidate key selected to uniquely identofy each occurrence of an entity type

Composite Key

• A candidate key that consists of two or more attributes.

Question 16

Structural Constraints: Multiplicity
Give an explanation

Answer 16

• number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particuler relationship

Question 17

Multiplicity in Binary Relationships. (binary is most common degree)

LIst the most common relationships.

Answer 17

Binary relationships are generally referred to as being:

• one-to-one ( 1 : 1 )
• one-to-many ( 1 : * )
• many-to-many( * : * )

Question 18

Explain the two structural constraints.

Answer 18

Cardinality

Describes maximum number of possible relationship occurrences for an entity occurrence participating in a given relationship type. (MAX)

Participation

Determines whether all or only some entity occurrences participate in a relationship occurrence of the relationship type. (MIN)

Question 19

What is a FAn TRAP?

Answer 19

Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambigious.

Question 20

Explain Chasm Trap

Answer 20

where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences.

Question 21

Quick review.

Define the following:

Entity types

Relationship types

Attributes

Multiplicity

Answer 21

Entity types: are group of objeccts with the same properties

Relationship types: are associations between entities

Attributes: are properties of entities or relationships

Multiplicity: of relationship types indicates participation and cardinality of connected entity types.