Chapter 2 - Database Requirements and ER Modeling

Degree of a relationship

reflects how many entities are involved in the relationship

Identifying relationship

relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity •Each weak entity must be associated with its owner entity via an identifying relationship •Unique attribute from the owner entity uniquely identifies every instance of the weak entity via an identifying relationship

Binary relationship

relationship between two entities (degree 2 relationship)

ER diagram (ERD)

the result of the ER modeling -serves as a blueprint for the database

Entities and attributes

use singular (rather than plural) pronouns

Relationships

use verbs or phrases rather than nouns

Multipel unique attributes (candidate keys)

when an entity has more than one unique attribute is also called a candidate key

DATABASE REQUIREMENTS AND ER MODEL USAGE

§ER modeling provides a straightforward technique for collecting, structuring, and visualizing requirements §An understanding of ER modeling is crucial, not just for creating ER models based on the requirements, but also during the requirements collection process itself §It helps keep the focus on asking or seeking answers to the right questions in order to establish the relevant facts about entities, attributes, and relationships

Multiple relationships between same entities

•Same entities in an ER diagram can be related via more than one relationship

Maximum cardinality

*One* (represented by a straight bar: I) *Many* (represented by a crow's foot symbol)

Minimum cardinality (participation)

*Optional* (represented by a circular symbol: 0) *Mandatory* (represented by a straight bar: I)

Weak entity

ER diagram construct depicting an entity that does not have a unique attribute of its own

Relationship

ER modeling construct depicting how entities are related -within an ER diagram, each entity must be related to at least one another via a relationship

NAMING CONVENTIONS FOR ER DIAGRAMS

Entities and attributes and relationships Exapm For example: - entity names STUDENT, STORE, and PROJECT are better choices than STUDENTS, STORES and PROJECTS - attribute name Phone is a better choice than the attribute name Phones - relationship names Inspects, Manages, and BelongsTo are better choices than Inspection, Management and Belonging

Relationship attributes

In some cases M:N relationships can actually have attributes of their own

NAMING CONVENTIONS FOR ER DIAGRAMS

Names should be as brief as possible, without being too condensed as to obscure the meaning of the construct §If possible, give all attributes in the entire ER diagram different names

Types of relationships (maximum cardinality-wise)

One-to-one relationship (1:1) One-to-many relationship (1:M) Many-to-many relationship (M:N)

MULTIPLE ER DIAGRAMS

When depicting multiple ER diagrams, each diagram should be visualized separately Instead of multiple ER diagrams in one schema a better choice is to present each ER diagram separately

Relationship roles

additional syntax that can be used in ER diagrams at the discretion of a data modeler to clarify the role of each entity in a relationship -Relationship roles can be used in relationships of any degree, but their usefulness is most apparent when they are used in unary relationships.

Multivalued attribute

attribute for which instances of an entity can have multiple values for the same attribute -Used in cases in which there is a variable number of values that can be assigned to the particular attribute of the entity.

Partial key

attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances -combination of the partial key and the unique attribute from the owner entity uniquely identifies every instance of the weak entity

Optional attribute

attribute that is allowed to not have a value -Typically, most attributes are required attributes (attributes that must have a value for each entity instance). However, some attributes are optional attributes.

Composite attribute

attribute that is composed of several attributes -Not an additional attribute of an entity -Its purpose is to indicate a situation in which a collection of attributes has an additional meaning, besides the individual meanings of each attribute

Unique Attribute

attribute whose value is different for each entity instance -every regular entity must have at least one unique attribute

Derived attribute

attribute whose values are calculated and not permanently stored in a database -The value of a derived attribute is calculated from the stored values of other attributes and/or additional available data (such as current date).

Entity-relationship (ER) modeling

conceptual database modeling technique -enables the structuring and organizing of the requirements collection process -provides a way to graphically represent the requirements

Entities

constructs that represent what the database keeps track of -the basic building blocks of an ER diagram -Represents various real world notions, such as people, places, objects, events, items, and other concepts -within one ERD each entity must have a different name

Cardinality contstraints

depict how many instances of one entity can be associated with instances of another enity

Attribute

depiction of a characteristic of an entity -represents the details that will be recorded for each entity instance -within one entity, each attribute must have a different name

Owner entity

entity whose unique attribute provides as mechanism for identifying instances of a weak entity

Relationship instances

occurrences of a relationship -occur when an instance of one entity is related to an instance of another entity via a relationship -Relationship themselves are depicted in the ER diagrams while relationship instances are not -Relationship instances are eventually recorded in the database that is created based on the ER diagram

Entity instances (entity members)

occurrences of an entity -entities themselves are depicted in the ER diagrams while entity instances are not -Entity instances are eventually recorded in the database that is created based on the ER diagram

Unary relationship (recursive relationship)

occurs when an entity is involved in a relationship with itself (degree 1 relationship)

Identifying relationship is either 1:M or 1:1 relationship

•In case of 1:M identifying relationship, a weak entity must have a partial key attribute •In case of 1:1 identifying relationship, a weak entity doesn't need to have a partial key attribute

Exact minimum and maximum cardinality in relationships

•In some cases the exact minimum and/or maximum cardinality in relationships is known in advance •Exact minimum/and or maximum cardinalities can be depicted in ER diagrams