Chapter 4 & 5 - INFS 4790

Strong Entity

entity that is existence-independent - Can exist apart from all of its related entities Also known as regular entity

Relationship Degree

indicates the number of entities or participants associated with a relationship

Optional Participation

one entity occurrence does not require a corresponding entity occurrence in a particular relationship

Relationships

- Association between entities - Participants are entities that participate in a relationship - Relationships between entities always operate in both directions

Unary Relationship

- Association is maintained within single entity - Also called "Recursive Relationship"

Derived Attribute

- Attribute whose value may be calculated (derived) from other attributes - For example, the Age attribute might be derived by subtracting the birth date from the current date. - Need not be physically stored with database - Sometimes to as: computed attributes

Attributes

- Characteristics of entities - In Chen model, attributes represented by ovals and are connected to entity rectangle with a line - Each oval contains the name of attribute it represents - In Crow's Foot model, attributes are written in attribute box below entity rectangle

Overlap Constraint

- Disjoint: Each supertype occurrence can be related to only one subtype - Overlapping: Each suptertype occurrence can be related to more thanone subtype

Entity Supertype

- Generic entity type that is related to one or more entity subtypes - Contains common characteristics

Identifiers (Primary Keys)

- One or more attributes that uniquely identify each entity instance - Underlined in the ERD - Key attributes are also underlined in frequently used table structure shorthand

Entity/Entities

- Refers to entity set and not to single entity occurrence - Corresponds to table and not to row in relational environment - In both Chen and Crow's Foot models, entity is represented by rectangle containing entity's name - Entity name, a noun, is usually written in capital letters.

Ternary Relationship

- Three entities are associated

Binary Relationship

- Two entities are associated

Entity Cluster

A "virtual" entity type used to represent multiple entities and relationships in the ERD. An entity cluster is formed by combining multiple interrelated entities into a single abstract entity object. An entity cluster is considered "virtual" or "abstract" because it is not actually an entity in the final ERD.

Overlapping Subtypes

A condition in which each entity instance (row) of the supertype can appear in more than one subtype

Design Trap

A problem that occurs when a relationship is improperly or incompletely identified and therefore is represented in a way that is not consistent with the real world. The most common design trap is known as a fan trap.

natural key (natural identifier)

A real-world, generally accepted identifier used to identify real-world objects. As its name implies, a natural key is familiar to end users and forms part of their day-to-day business vocabulary.

Weak Relationship

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity. Also known as: non-identifying relationship

Strong (Identifying) Relationship

A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity.

Surrogate Key

A system-assigned primary key, generally numeric and auto-incremented

Derived Attribute Advantage/Disadvantage

Advantage: Saves CPU processing cycles Saves data access time Data value is readily available Can be used to keep track of historical data Disadvantage: Requires constant maintenance to ensure derived value is current, especially if any values used in the calculation change.

Resolving Multivalued Attribute Problems

Although conceptual model can handle M:N relationships and multivalued attributes, you should not implement them in relational DBMS - Within original entity, create several new attributes, one for each of the original multivalued attribute's components -- Can lead to major structural problems in table - Create new entity composed of original multivalued attribute's components

Participants

An ER term for entities that participate in a relationship. For example, in the relationship "PROFESSOR teaches CLASS," the teaches relationship is based on the participants PROFESSOR and CLASS.

Ternary

An ER term used to describe an association (relationship) between three entities. For example: a DOCTOR prescribes a DRUG for a PATIENT

Composite Attribute

An attribute that can be further subdivided to yield additional attributes. For example, a phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.

Multivalued Attribute

An attribute that can have many values for a single entity occurrence. For example, an EMP_DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held.

Single-Value Attribute

An attribute that can have only one value.

Simple Attribute

An attribute that cannot be subdivided into meaningful components. Compare to composite attribute.

Weak Entity

An entity that displays existence dependence and inherits the primary key of its parent entity. For example, a DEPENDENT requires the existence of an EMPLOYEE.

Entity

An object of interest to the end user

subtype discriminator

Attribute created and added to the Supertype Identifies to which subtype, if any, each supertype occurrence is related enables an entity subtype to inherit the attributes and relationships of the supertype

Domains

Attributes have domain - Domain is attribute's set of possible values Attributes may share a domain The domain is not directly represented on the ERD

Primary Key Guidelines

Attributes/combination of attributes that uniquely identifies entity instances in an entity set Main function is to uniquely identify an entity instance or row within a table Guarantee entity integrity, not to "describe" the entity

Iterative Process

Based on repetition of processes and procedures

Composite and Simple Attributes

Composite attribute can be subdivided STU_ADDRESS STU_STREET STU_CITY STU_STATE STU_ZIPCODE Simple attribute cannot be subdivided STU_MAJOR

Entity Subtypes

Contains unique characteristics of each entity subtype

When To Use Surrogate Primary Keys

Especially helpful when there is: - No natural key - Selected candidate key has embedded semantic contents - Selected candidate key is too long or cumbersome If you use surrogate key, ensure that candidate key of entity in question performs properly through use of "unique" and "not null" constraints

Composite Identifier

In ER modeling, a key composed of more than one attribute.

Optional Attribute

In ER modeling, an attribute that does not require a value; therefore, it can be left empty

Required Attribute

In ER modeling, an attribute that must have a value. In other words, it cannot be left empty.

Partial Completeness

In a generalization or specialization hierarchy, a condition in which some supertype occurrences might not be members of any subtype.

Total Completeness

In a generalization/specialization hierarchy, a condition in which every supertype occurrence must be a member of at least one subtype.

Generalization

In a specialization hierarchy, the grouping of common attributes into a supertype entity

Disjoint Subtypes

In a specialization hierarchy, these are unique and nonoverlapping subtype entity sets Also known as "nonoverlapping subtypes"

mandatory participation

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

Completeness Constraint

Partial: Supertype occurrences are not required to be associated with an subtype Total: Supertype occurrences are required to be associated with some subtype

Composite Primary Keys

Primary keys ideally composed of only single attribute Possible to use a composite key - Primary key composed of more than one attribute

Cardinality

Property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity Expresses specific minimum and maximum number of entity occurrences associated with one occurrence of related entity

Extended Entity Relationship Model (EERM)

Sometimes referred to as the enhanced entity relationship model; the result of adding more semantic constructs (entity supertypes, entity subtypes, and entity clustering) to the original entity relationship (ER) model.

Inheritance

Subtypes inherit the PK of the Supertype -Inherited PK is both PK and FK in the subtype -Forces all "IS-A" relationships to be 1:1 Subttype logically inherits all of the attributes of the supertype Subtype logically inherits all of the "HAS-A" relationships of the supertype enables an entity subtype to inherit the attributes and relationships of the supertype

Connectivity

Term used to describe the relationship classification (1 to M) Established by business rules

Chapter 4 Summary

The ERM uses ERDs to represent the conceptual database as viewed by the end user. The ERM's main components are entities, relationships, and attributes. The ERD includes connectivity and cardinality notations, and can also show relationship strength, relationship participation (optional or mandatory), and degree of relationship (such as unary, binary, or ternary). Connectivity describes the relationship classification (1:1, 1:M, or M:N). Cardinality expresses the specific number of entity occurrences associated with an occurrence of a related entity. Connectivities and cardinalities are usually based on business rules. In the ERM, an M:N relationship is valid at the conceptual level. However, when implementing the ERM in a relational database, the M:N relationship must be mapped to a set of 1:M relationships through a composite entity. In the ERM, an M:N relationship is valid at the conceptual level. However, when implementing the ERM in a relational database, the M:N relationship must be mapped to a set of 1:M relationships through a composite entity. In the ERM, an M:N relationship is valid at the conceptual level. However, when implementing the ERM in a relational database, the M:N relationship must be mapped to a set of 1:M relationships through a composite entity. Database designers, no matter how well they can produce designs that conform to all applicable modeling conventions, are often forced to make design compromises. Those compromises are required when end users have vital transaction-speed and information requirements that prevent the use of "perfect" modeling logic and adherence to all modeling conventions. Therefore, database designers must use their professional judgment to determine how and to what extent the modeling conventions are subject to modification. To ensure that their professional judgments are sound, database designers must have detailed and in-depth knowledge of data-modeling conventions. They must also document the design process from beginning to end, which helps keep the design process on track and allows for easy modifications in the future.

EER diagram (EERD)

The entity relationship diagram resulting from the application of extended entity relationship concepts that provide additional semantic content in the ER model.

Specialization

The grouping of unique attributes into a subtype entity

Relational Schema

The organization of a relational database as described by the database administrator

Relational Schema

The organization of a relational database as described by the database administrator, that uses the following format: TABLE NAME ( KEY ATTRIBUTE 1, ATTRIBUTE 2, ATTRIBUTE 3,...ATTRIBUTE K) For example, a CAR entity may be represented by CAR (CAR VIN, MOD_CODE, CAR_YEAR, CAR_COLOR)

Time-Variant Data

Data whose values are a function of time. For example, time-variant data can be seen at work when a company's history of all administrative appointments is tracked.

Developing an ER Diagram

Database design is iterative rather than linear or sequential process

Database Design Challenges: Conflicting Goals

Database design must conform to design standards High processing speeds are often a top priority in database design Quest for timely information might be focus of database design

Specialization Hierarchy

Depicts arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities) Relationships sometimes described in terms of "IS-A" relationships Subtype can exist only within context of supertype and every subtype can have only one supertype to which it is directly related Can have many levels of supertype/subtype relationships

Fan Trap

Design trap that occurs when one entity is in two 1:M relationships with other entities thus producing an association among the other entities that is not expressed in the model

True/ False : Entity and object are often used interchangeably

True

When to Use Composite Primary Keys

Useful as identifiers of composite entities, where each primary key combination is allowed only once in M:N relationship - Automatically provides benefit of ensuring that there cannot be duplicate values Useful as identifiers when an identifying relationship exists