Database Chp. 4
required vs. optional attributes
Required must have a value, cannot be left empty. Optional does not require a value and call be left empty.
connectivity
describes the relationship classification. "speak" the relationship in both directions to determine connectivity direction (1:M usually) (line endings on CF ERD)
optional relationship participation
one entity occurrence does not require a corresponding entity occurrence in a particular relationship (0:1 or 0:N cardinality)
mandatory relationship participation
one entity occurrence requires a corresponding entity occurrence in a particular relationship (1:1 or 1:N cardinality)
attribute identifier
one or more attributes that uniquely identify each entity instance
What are the entities that participate in a relationship called?
participants
composite identifier
primary key composed of more than one attribute
strong (identifying) relationship
primary key of the related entity contains a primary key component of the parent entity (solid line). Existent dependent entities automatically have this
weak (non-identifying) relationship
primary key of the related entity does not contain a primary key component of the parent entity (dashed line)
recursive relationship
relationship exists between occurrences of the same entity set
attribute domain
set of possible values for a given attribute (Sex: M or F, GPA: 0 -> 4.0)
derived attribute
value is calculated from other attributes and may not physically be stored (ie: class rank from num hours)
What do you have to do to multivalued attributes to make them function correctly in a database?
you may need to separate it into multiple different attributes or make a new entity composed of the original attributes components (most common)
binary relationship
2 entities are associated
ternary relationship
3 entities are associated
What are the main components of entity relationship components?
Cardinality, connectivity, entities, relationships, identifying/non-identifying
composite attribute
Attribute that can be subdivided to yield additional attributes (ie, fullname)
What does an ERD depict?
Conceptual database as viewed by the end user and the databases main components (entities, attributes, and relationship)
In a relational environment, does an entity correspond to a table or a row?
Corresponds to a table, not a row in the relational environment.
existence dependence
Entity exists in the database only when it is associated with another related entity occurrence (ie, dependent entity with employee entity)
How are entity names written in Chen and Crow's foot ERDs?
Entity names are nouns written in capital letters, and are represented by a rectangle with the entity's name
weak entity
NOT the same as a weak relationship. must be existence dependent and have a primary key that is partially or totally derived from the parent entity. entity weakness is based on the business rules (decided by the designer)
Does the term "entity" refer to the entity set or a single entity occurrence?
Refers to the entity set and not to a single entity occurrence.
What are some advantages and disadvantages or storing derived attributes? Not storing?
Stored Advantages: saved processing cycles, data access time, tracking of historical data. Nonstored Advantages: saves storage space, current value always yielded Stored Disadvantages: constant maintenance to ensure derived value is current, especially if any values in the calculation are changed. Nonstored Disadvantages: uses processing cycles, increase data access time, adds coding complexity to queries.
Will real-life database design require the reconciliation of conflicting goals? Why?
Yes, because sometimes plans will interfere with other design elements, and one will have to be compromised for the sake of the other one.
unary relationship
associated is maintained within a single entity
relationships
association between entities that always operate in both directions
simple attribute
attribute that cannot be subdivided (ie: middle initial)
single-valued attribute
attribute that has only a single value (ie: SSN, birthdate)
multivalued attributes
attributes that may have many values (ie: dependents, job history)
Entity Relationship Model
basis of an entity relationship diagram
What are cardinality and connectivity determined by?
business rules
What is an attribute?
characteristic of an entity (columns within the tables)
associative (composite) entities
composite or bridge entities. used to break apart and represent a M:N relationship between 2+ entities. had 1:M relationships with the parent entities and is composed of the primary key attributes of each parent (may also include additional attributes)
How are relationships between entities defined, refined, and incorporated into the database design process?
create a detailed narrative, identify the business rules, identify the main entities and relationships from those rules, develop an initial ERD, identify attributes and primary keys, then review and revise the ERD
existence independence
entity exists apart from all of its related entities. referred to as a strong or regular entity (ie, list of majors available at a school)
cardinality
expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity (numbers below connections on CF ERD)
relationship degree
indicates the number of entities or participants associated with a relationship