GK One-Line Question & Answer

By creating a separate junction/cross-reference table with foreign keys referencing both entity tables plus any relationship attributes

Specialization is top-down (start with supertype, define subtypes); generalization is bottom-up (identify commonalities among existing entity types to create a supertype)

Double ellipse (ellipse within an ellipse)

Create a M:N relationship Works_On between Employee and Project with hours as a relationship attribute

Treating a relationship and its participating entities as a higher-level abstract entity to participate in another relationship used when a relationship itself needs to have a relationship

Place the foreign key in the table with total participation or merge the two entities into one table if appropriate

To clarify the role/function each entity plays in a relationship especially important in recursive (self-referential) relationships

Every supertype instance MUST be a member of at least one subtype contrasted with partial where supertype instances need not belong to any subtype

As a union of supertype entities where a subtype can be associated with instances from different supertype entity sets shown with a U symbol in EER diagrams

Creating a table whose primary key is the combination of its partial key (discriminator) and the primary key of its identifying strong entity with a foreign key to the owner table

IsA represents inheritance/specialization (a Car IsA Vehicle); HasA represents composition/aggregation (a Car HasA Engine) - fundamentally different entity relationships

Converted into a separate table with the parent entity PK as FK and the multi-valued attribute as part of the PK

Total participation of Department in Manages relationship with cardinality 1:1 where Department has total and Manager has partial participation

The process of modifying the ER schema over time as requirements change challenging because it requires updating existing data, queries, application code, and possibly complex data migrations

A circle (zero) and a vertical line (one) on the same end of the relationship line

A modeling error where a many-to-one and one-to-many relationship from a single entity incorrectly suggests a path between entities leading to incorrect query results - resolved by restructuring the relationships

A gap in a relationship path where a minimum cardinality of zero means some entities have no corresponding connected entities causing data to be missed in queries

Various strategies: single table with type indicators and nullable columns, separate subtype tables with FK to supertype, or a type membership table allowing multiple type memberships

Grade is a property of the specific enrollment instance (the combination of a particular student in a particular course) not of Student alone or Course alone

A relationship simultaneously associating three entity types used when decomposition into binary relationships would lose information about the three-way association

The challenge of determining whether two references to entities represent the same real-world entity (deduplication/record linkage) crucial in data integration and federated database design

Superkey: any set of attributes uniquely identifying tuples; Candidate key: a minimal superkey (no subset is also a superkey); Primary key: one chosen candidate key for the table

A constraint X determines Y where for any two tuples with the same X value they must also have the same Y value (X functionally determines Y)

Changing a fact stored in multiple redundant rows requiring all rows to be updated simultaneously with inconsistency resulting if any update is missed

A normal form where every constraint is a logical consequence of domain constraints and key constraints guaranteeing freedom from all modification anomalies

Given relations R(A,B) and S(B), produces tuples from R[A] that are associated with ALL tuples in S - used for for-all type queries

BCNF requires that for every non-trivial FD X to Y, X must be a superkey (no exceptions) while 3NF allows Y to be a prime attribute as an exception

A set of sound and complete inference rules for FDs: Reflexivity (if Y is subset of X then X determines Y), Augmentation (if X determines Y then XZ determines YZ), and Transitivity (if X determines Y and Y determines Z then X determines Z)

Natural join automatically joins on all common attribute names and eliminates duplicate columns; equi-join requires explicit equality conditions and may keep duplicate columns

In a relation R(X,Y,Z), X multidetermines Y means that for a given X value the set of Y values is independent of the Z values - used to define 4NF

The set of ALL attributes that can be functionally determined by X using the FDs in F - used to test if X is a superkey (if X+ = all attributes)

It ensures that when a relation is decomposed into smaller relations the original relation can be perfectly reconstructed by joining the decomposed relations with no spurious tuples

A constraint where a relation can be losslessly decomposed into n projections and reconstructed by joining them; a relation is in 5NF if every join dependency is implied by the candidate keys

UNION removes duplicate rows from the combined result; UNION ALL keeps all rows including duplicates (and is faster since no deduplication is needed)

Testing whether a decomposition has the lossless join property and whether a set of functional dependencies is preserved by applying FDs to a canonical table (tableau)

Returns all rows from the left table and matching rows from the right table; non-matching right-side columns contain NULL

The set of all permissible/valid values for an attribute defining the data type and any domain constraints

Theta join allows any comparison operator (=, <, >, <=, >=, !=) as the join condition; equi-join is a special case of theta join using only equality (=)

Relational algebra is procedural (specifies how to retrieve data); relational calculus is declarative (specifies what data to retrieve without specifying how) - both are equivalent in expressive power (Codds theorem)

Relation scheme (schema) R(A1,A2,...An) is the structural definition with attribute names and their domains; relation instance r(R) is the actual set of tuples conforming to that schema at a point in time