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