Chapter 9 Flashcards
Relational Database Design by ER- and EERR-to-Relational Mapping. (Side note: it is better to study this set in order + ignore my side notes to myself if you would like [they are for my own understanding])
T/F: Some of the goals during mapping include minimizing the null values.
true.
T/F: If the chosen key of regular entity type E is composite, the set of simple attributes that form it, will together form the primary key of R.
true.
The primary key of the relation R between weak entity type W and its owner entity type E, is the combination of the ________________ and ___________________, if any.
the primary key(s) of the owner(s), the partial key, of the weak entity type W.
When mapping the weak entity types:
For each weak entity type W in the ER schema with owner entity type E, create a relation R & include all simple attributes (or simple components of composite attributes) of ________ as attributes of R.
Also, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to ___________.
weak entity type W, the owner entity type(s).
So in summary, when mapping weak entity types W with owner entities type E:-
Create a relation R where its….
attributes: include all simple attributes of W.
foreign key: will be the primary key attribute(S) of the relation(S) that correspond to the owner entity type(s).
primary key: a combination of the primary key(s) of the owner(s) and the partial key of the weak entity type, if any.
Name the 3 possible approaches for mapping a binary 1:1 relation type.
1- Foreign Key ( 2 relations) approach
2- Merged relation (1 relation) option
3- Cross-reference or relationship relation ( 3 relations) option
In the foreign key approach, it is better to choose an entity type with ___________ in R as the relation S including the foreign key that is the primary key of relation T.
total participation.
In the merged relation approach, when both relations have ____________, we merge the two entity types and the relationship into a _____________.
total participation, single relation.
In the cross-reference or relationship relation approach, we set up a __________ for the purpose of cross-referencing the _________ of the two realtions S and T representing the entity types.
The primary key of R will be _________________, and the other foreign key will be a _________ of R.
Third relation R , primary keys, one of the two foreign keys, unique key.
T/F: The merging relation approach may be appropriate when both participations are total, as this would indicate that the two tables will have the exact same number of tuples at all times.
true.
As for the mapping of binary 1:N relationship types, the relation S representing the participating entity type at ________________ will include the primary key of relation T (the other entity type participating in R) as a foreign key. And any simple attributes of the 1:N relation type will be included as attributes of ________.
An alternative approach is to use a cross-reference relation, where the primary key of R is the same as the primary key of ________.
the N-side of the relationship type, relation S, relation S.
(side note for me: in other more understandable words, the relation on the N-side is carrying the entire relationship on its back.)
As for the mapping of binary M:N relationship types, we will use a __________ relation to represent the relationship type.
relationship (cross-referencing) relation.
[side note for me: many-to-many relationships are the only ones to only have the cross-referencing relation as an option, whereas 1:1 & 1:N have the options to be represented in a foreignkey or cross-referencing relation (and 1:1 has an extra merged relation option.)]
As for the mapping of binary M:N relationship types, we will create a new relation S to represent R. It will include the primary keys of the relations that represent the participating entity types as the foreign key attributes of S.
And the ___________ will form the primary key of S.
It will also include any simple attributes of the _________________ as the attributes of S.
combination of the two foreign key attributes of S, M:N relationship type.
(Explanation of example in the figure: The primary key of the WORKS_ON relation is the
combination of the foreign key attributes {ESSN, PNO}.)
As for the mapping of multivalued attributes, for each multivalued attribute A, we will __________.
create a new relation R.
As for the mapping of multivalued attributes, for each multivalued attribute A we will create a new relation R which will include an attribute corresponding to A, plus the primary key attribute K-as a foreign key in R-of the relation that represents the entity type of relationship type that has A as an attribute.
The primary key of R is ____________________.
the combination of A and K.
(in other words, R = multivalued [attribute A + primary key K]=> together they form primary key K of R.)
As for the mapping of N-ary relationship types, we will create a new relationship S to represent R. It will include the primary keys of the relations that represent the participating entity types as the foreign key attributes, and any simples attributes of the n-ary relationship type as attributes of S.
the primary key of S is usually _______________.
a combination of all the foreign keys.
