Er Model Practice Questions

In the ER model, a weak entity set cannot exist without its identifying (owner) entity set.
To establish this dependency, the weak entity set must participate totally in the identifying relationship. This means every weak entity instance must be associated with an owner entity instance.
The owner entity set, however, does not necessarily have total participation in the identifying relationship; an owner entity instance might exist without any associated weak entity instances.
Therefore, the statement "The weak entity set MUST have total participation in the identifying relationship" is TRUE.
The other options are incorrect because the owner entity set is not required to have total participation, and and D are incorrect. Option B stating "Neither weak entity set nor owner entity set MUST" is also incorrect.

A weak entity set is represented by a rectangle with a double/bold border. The many-one relationship between a weak entity set and its identifying entity set is represented by a diamond with a double/bold border. The partial key of a weak entity set is represented by an oval with a dashed underline.

In the E-R model, a dominant entity $Y$ serves as the parent or owner, while the subordinate entity $X$ exhibits existential dependency on $Y$. This implies that the existence of $X$ is contingent upon the existence of $Y$. Consequently, when the dominant entity $Y$ is removed from the database, the subordinate entity $X$ loses its existential basis. To maintain referential integrity, the deletion of $Y$ must trigger a cascading deletion of all associated $X$ instances. This relationship dictates that $X$ cannot survive without $Y$, making Option B the correct interpretation.

The relationship from entity set E1 to E2 is many-to-one. This implies that any entity in E1 can be associated with at most one entity in E2. Additionally, the problem states that E1 has total participation in the relationship R. This means every entity in E1 must be part of the relationship. Combining these two conditions, it follows that every entity in E1 must be associated with exactly one entity in E2.

In the Entity-Relationship (ER) model, attributes are visually represented by ellipses. Different line styles of these ellipses categorize the specific properties of the attribute. For example, a key attribute is identified by an underlined name, while a multivalued attribute is depicted using a double ellipse. A derived attribute, which represents a value calculated from other related data rather than being stored directly, is uniquely denoted by a dashed ellipse. Therefore, the dashed ellipse is the correct symbol for a derived attribute.

The relation table for R should always be merged with the entity that has total participation and relationship should be many to one. Answer is C.

In the given ERD, Person and Exam are independent entities with distinct attributes. Mapping these to a relational model yields Person(NID, Name) and Exam(ExamID, ExamName). The $M:N$ relationship Qualification necessitates a separate relation, Qualification(NID, ExamID, QualifiedDate), which includes the primary keys of both participating entities. Option C, Exam(ExamID, NID, ExamName), incorrectly includes NID as an attribute within the Exam relation. Since NID is an attribute of the Person entity and not the Exam entity, this relation is invalid.

To convert the ER model to a relational model in 3NF, we follow standard mapping rules:

1. Each strong entity set gets its own relation. This gives us three relations:
   • E1(a11, a12) with a11 as the primary key.
   • E2(a21, a22) with a21 as the primary key.
   • E3(a31, a32) with a31 as the primary key.
2. The m:n relationship R12 requires a separate relation containing the primary keys of the participating entities:
   • R12(a11, a21)
3. For the 1:n relationship R13, the primary key of the entity on the '1' side (E1) is included as a foreign key in the relation for the 'n' side (E3). The E3 relation is modified to E3(a31, a32, a11).

This process results in a total of four relations: E1, E2, the modified E3, and R12.

In UML class diagrams, aggregation represents a "part-of" relationship where the component can exist independently of the container. This is distinct from composition, which signifies stronger ownership and is depicted using a filled (closed) diamond. Standard UML notation for aggregation requires a line connecting the two entities, terminating at the "whole" or container class with a small open (unfilled) diamond. This specific graphical representation distinguishes it from plain associations or composition. Therefore, the open diamond is the correct symbol for an aggregation association.

An Entity-Relationship (ER) model supports both multivalued attributes (e.g., multiple phone numbers for a person) and composite attributes (e.g., an address composed of street, city, and zip). Therefore, options A and B are correct statements regarding the ER model.

However, in a relational database, each cell at the intersection of a row and a column must contain a single, atomic value. This means an attribute in a row of a relational table can either have exactly one value or be NULL (representing the absence of a value), but it cannot hold multiple values. is correct, and option C, which states that an attribute in a relational table can have more than one value, is incorrect.

To determine the minimum number of tables, we apply standard ER to relational model mapping rules:

1. Strong Entity $P$: Represents a table $T_P$ with attributes $\{P1, P2\}$.
2. Strong Entity $M$ with Relationship $R1$: Since $M$ has total participation in $R1$ and is on the "many" side of the $M \to P$ relationship (indicated by the arrow toward $P$), $R1$ is merged into $M$. This creates table $T_M$ with attributes $\{M1, M2, M3, P1\}$.
3. Weak Entity $N$: As a weak entity dependent on $P$, $N$ requires a separate table $T_N$ containing its partial key $N1$, its attribute $N2$, and the primary key $P1$ from the identifying entity $P$.
4. Consequently, we need 3 tables in total: $T_P$, $T_M$, and $T_N$.

To determine the minimum number of tables, we apply standard ER-to-relational mapping rules:

1. Entity $M$ has attributes $\{M1, M2, M3\}$ with $M1$ as the primary key.
2. Relationship $R1$ connects $M$ and $P$. Since $M$ has total participation in $R1$ (indicated by the double line), we can merge $R1$ into the table for entity $M$ to minimize the number of tables.
3. Merging $R1$ into $M$ requires adding the primary key of the related entity $P$ (which is $P1$) to the attributes of $M$.
4. The resulting schema for this table is $\{M1, M2, M3, P1\}$.
5. This schema matches the attribute set provided in Option A.

Entities $E_1$ and $E_2$ each require a separate table, totaling 2 tables.
The $1:M$ relationship $R_1$ is implemented by adding the primary key of the "one" entity as a foreign key within the table of the "many" entity, requiring 0 additional tables.
The $M:N$ relationship $R_2$ necessitates an associative junction table to store the pairings of $E_1$ and $E_2$ primary keys, adding 1 table.
The total number of tables is $1 (E_1) + 1 (E_2) + 0 (R_1) + 1 (R_2) = 3$.

In Entity-Relationship (ER) modeling, an attribute is attached to a relationship if its value depends on the interaction between the participating entities. The 'rent payment' is not an intrinsic property of an individual 'Hotel Room' (which has a base price) or a single 'Person' (who has personal details). Rather, the payment amount is specific to the instance of a particular 'Person' staying in a specific 'Hotel Room' for a defined period. Since the 'Lodging' relationship captures this specific interaction between the two entities, the 'rent payment' attribute must be associated with the 'Lodging' relationship.

This scenario depicts a many-to-many (M:N) relationship because multiple students can be enrolled in a single course, and a single course can have multiple students enrolled.

A Book can exist without having any authors (e.g., a book in preparation). This means Book's participation is partial. Every Author must have written at least one book to be in the database, so Author's participation is total.

Let's analyze the constraints:

1. Cardinality Ratio: The statement "Each SpaceTelescope can perform multiple Observations" implies that one SpaceTelescope can be related to many Observations. The statement "every Observation is performed by exactly one SpaceTelescope" implies that one Observation can be related to only one SpaceTelescope. Therefore, the cardinality ratio between SpaceTelescope and Observation is 1:N (one SpaceTelescope to many Observations).

2. Participation of SpaceTelescope: The statement "a SpaceTelescope might exist in the database without having performed any observations yet" indicates that not every SpaceTelescope entity must participate in the Performs relationship. This signifies Partial Participation for SpaceTelescope.

3. Participation of Observation: The statement "every recorded Observation must be linked to a performing SpaceTelescope" indicates that every Observation entity must participate in the Performs relationship. This signifies Total Participation for Observation.

Combining these, the correct option is: Cardinality: 1:N, Participation of SpaceTelescope: Partial, Participation of Observation: Total.

A: TRUE - A ternary (or any n-ary where n≥3) relationship always requires a separate relation in the relational model. B: FALSE - The primary key of the ternary table depends on the cardinality; it may be a subset of the three PKs, not necessarily all three. C: FALSE - Some ternary relationships CAN be decomposed into binary ones without loss, but not all (Fan trap / connection trap scenarios). D: TRUE - Ternary cardinality constraints are indeed more complex, as constraints must be expressed for each pair of entity sets given a fixed third. E: FALSE - Even in 1:1:N ternary relationships, a separate table is required; unlike binary 1:N relationships, you cannot simply embed a FK here.

Table count: 1) Student table, 2) Course table, 3) Instructor table - these are the three entity tables. R1 (M:N relationship) must always be converted into a separate relationship table. R2 (1:N with total participation of Course) can be represented by adding Instructor's primary key as a foreign key into the Course table - no extra table needed. So total = 3 entity tables + 1 relationship table (for R1) = 4 tables.