Week 3 - Relationships

Strong relationships

Primary key of the related entity contains the primary key component of the parent entity (AKA Identifying relationships)

Multiplicity

Specification of the range of cardinality values for a particular set;

Developing an ER Diagram

1) create detailed narrative of the organization's description of operations 2) Identify the business rules based on the description of operations 3) Identify the business rules based on the description of operations 4) Identify the main entities and relationships from the business rules 5) Develop initial Entity Relationship Diagram 6) Identify the attributes and primary keys that adequately describe the entities 7) revise and review Entity Relationship Diagram

primary key

uniquely identifies an entity instance or tuple within an entity

Entities in a identifying relationship are referred to as ________

weak entities

How do you resolve a non-specific relationship?

with an associative entity

What are some compromises database designers encounter?

- DB design must conform to design standards - High processing speed may limit the number and complexity of logically desirable relationships - Maximum information generation may lead to loss of clean design structures and high transaction speed

What is a "rare" relationship for relational databases?

1:1 relationships

What is the "norm" relationship for relational databases?

1:M relationships

What is the Relational model "ideal"?

1:M relationships

Foreign key

Attribute in one entity matches the primary key in another entity proving a link between the two entities

DB Relationships

Business association existing between one or more entities; May represent an event that links the entities or merely a logical affinity that exists between the entities; implicitly bi-directional;

Participation

Determines whether all or only some entity occurrences participate in a relationship

Synonym

Different names are used to describe the same attribute or entity; ie VENDOR and supplier refer to the same entity

Cardinality

Expresses the minimum and maximum number of possible entity relationship occurrences in which an entity can patriciate in

What guarantees referential integrity?

Foreign keys

Entity Relationship Model

Graphical representation of entities and their relationships in a database structure

Associative entity creation (2)

Inherits its primary key from each of the parent entities to be connected; Has its own primary key as well as the foreign keys of the connected entities;

Which relationship cannot be implemented in the relational model?

M:N relationships

What is required of foreign keys?

Must match the primary key of the source entity or may be NULL

Which relationship is existence independent? ie entity exists apart from its related entities

Non-Identifying/Weak relationships

Optional participation

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

Identifying relationships

Primary key of the related entity contains the primary key component of the parent entity (AKA Strong relationships)

Weak relationships

Primary key of the related entity does not contain the primary key component of the parent entity (AKA non-identifying relationship)

Non-identifying relationship

Primary key of the related entity does not contain the primary key component of the parent entity (AKA weak relationships)

Homonym

Same name is used to label different attributes; ie. C_NAME being used as name in the CUSTOMER entity and for consultant in the CONSULTANT entity

Entities in a non-indentifying relationsihp as referred to as _____________

Strong entities

What are three types of relationships?

Unary relationship; Binary relationship; Ternary relationship;

What is a unary relationship?

association within a single entity

What determines relationship strength?

business rules

What establishes multiplicity?

business rules

How are M:N relationships implemented?

by creating a new entity in 1:M relationships with the original entity; create an associative entity

Main components of a database

entities; attributes; relationships

Which relationship is existence dependent? ie entity exists only when associated with another related entity ocurrence

identifying/strong relationship

Relationship Degree

indicates the number of entities or participants associated with a relationship

What does it mean for DB relationships to be bi-directional?

it can be interpreted in both directions

Non-specific relationship

many-to-many relationship between two or more entries

What is M-N?

many-to-many; One or more tuples in an entity can be related to zero, one, or many tuples in another entity

what is a limitation of multiplicity?

may not be able to implement directly through the database design; -> applied by the application software or by triggers

Entity Relationship Diagram (ERD)

models the database components

mandatory participation

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

What is 1:M?

one-to-many relationship; Each tuple in the entity can be related to many tuples in the relating entity

What is 1:1?

one-to-one relationship; One entity can be related to only one other entity and vice versa

What is the most important characteristic of an entity?

primary key

What guarantees entity integrity?

primary keys

What is the relationship between primary keys and runtime?

primary keys have a direct bearing on efficiency and effectiveness (less/smaller is better)

When is a non-specific relationship used?

suitable for preliminary data models ONLY

what is a ternary relationship?

three entities are associated

what is a binary relationship?

two entities are associated