Unit 2: Database Design and Entity-Relationship Modeling

Section A — 1 Mark Questions

1. What is the primary purpose of database design? [1 Mark]

Answer: To produce a conceptual, logical, and physical model of a database that meets the data requirements of an application.

2. Define an entity set. [1 Mark]

Answer: An entity set is a collection of similar entities that share the same attributes.

3. What is an attribute in an ER model? [1 Mark]

Answer: An attribute is a property or characteristic of an entity.

4. How are entity sets represented in an ER diagram? [1 Mark]

Answer: They are represented using rectangles.

5. Define a relationship set. [1 Mark]

Answer: A relationship set is a set of associations among two or more entity sets.

6. What symbol is used to represent a relationship set in an ER diagram? [1 Mark]

Answer: A diamond shape.

7. Define a key constraint. [1 Mark]

Answer: A key constraint specifies that an entity in an entity set can be associated with at most one entity in another entity set via a relationship.

8. What is a weak entity set? [1 Mark]

Answer: An entity set that does not have sufficient attributes to form a primary key.

9. How is a weak entity set represented in an ER diagram? [1 Mark]

Answer: It is represented by a double-outlined rectangle.

10. What is a discriminator attribute? [1 Mark]

Answer: It is a set of attributes that allows for the distinguishing of weak entities that depend on the same owner entity.

11. Define participation constraint. [1 Mark]

Answer: It defines whether the existence of an entity depends on its being related to another entity via the relationship set.

12. What does a double line between an entity and a relationship indicate? [1 Mark]

Answer: It indicates total participation of the entity in the relationship.

13. What is a ternary relationship? [1 Mark]

Answer: A relationship involving three different entity sets.

14. Define a composite attribute. [1 Mark]

Answer: An attribute that can be divided into smaller sub-parts or components.

15. What is a multi-valued attribute? [1 Mark]

Answer: An attribute that can hold multiple values for a single entity instance.

16. What is a derived attribute? [1 Mark]

Answer: An attribute whose value can be calculated from other related attributes.

17. Define the term degree of a relationship. [1 Mark]

Answer: The number of entity sets involved in a relationship.

18. What is the role of a primary key? [1 Mark]

Answer: It uniquely identifies each entity within an entity set.

19. What is a recursive relationship? [1 Mark]

Answer: A relationship where the same entity set participates in the relationship more than once in different roles.

20. What is specialization in the ER model? [1 Mark]

Answer: The process of identifying subsets of an entity set that share common characteristics.

Section B — 2/3 Mark Questions

1. Differentiate between total and partial participation. [2 Marks]

Answer: Total participation means every entity in the set must participate in at least one relationship instance. Partial participation means that only some, but not necessarily all, entities participate.

2. Explain the concept of a multi-valued attribute with an example. [2 Marks]

Answer: A multi-valued attribute allows an entity to have more than one value for a specific attribute. For example, a student entity might have multiple phone numbers.

3. What is the significance of a primary key in database design? [2 Marks]

Answer: The primary key ensures data integrity by preventing duplicate rows and provides a unique identifier for searching, sorting, and establishing relationships between tables.

4. Describe the components of an ER diagram. [3 Marks]

Answer: The main components are entities (represented by rectangles), attributes (represented by ovals), and relationships (represented by diamonds). Lines connect these components to show structural associations.

5. Explain the difference between specialization and generalization. [3 Marks]

Answer: Specialization is a top-down design process where a higher-level entity is divided into lower-level subclasses. Generalization is a bottom-up process where multiple lower-level entities are combined into a higher-level entity based on common attributes.

6. When should you use a weak entity set? [2 Marks]

Answer: A weak entity set is used when an entity cannot be uniquely identified by its own attributes and relies on its relationship with an owner entity for identification.

7. List the steps in the database design process. [3 Marks]

Answer: The steps typically include: (1) Requirements analysis, (2) Conceptual database design using ER modeling, (3) Logical design (mapping to relational model), and (4) Physical design.

8. What is the purpose of an attribute discriminator in a weak entity set? [2 Marks]

Answer: The discriminator is used to distinguish between different weak entities that belong to the same owner entity. It acts as a partial key.

9. How do you represent a 1:N relationship in an ER model? [2 Marks]

Answer: It is represented by a line connecting the entity sets to the relationship diamond. A key constraint arrow or specific mapping can be used to indicate which side of the relationship can have multiple occurrences.

10. Explain the concept of mapping cardinality. [3 Marks]

Answer: Mapping cardinality defines the number of entities to which another entity can be associated. The common types are one-to-one (1:1), one-to-many (1:N), many-to-one (N:1), and many-to-many (M:N).

Section C — 5 Mark Questions

1. Explain the Extended Entity-Relationship (EER) model features: Specialization, Generalization, and Aggregation. [5 Marks]

Answer:

Overview

Extended ER features provide more expressive power to model complex real-world relationships that standard ER diagrams cannot capture easily.

Specialization and Generalization

• Specialization: The process of defining a set of subclasses of an entity type. For example, a 'Person' entity might be specialized into 'Student' and 'Faculty'.
• Generalization: The reverse process, identifying common attributes across multiple entities to form a higher-level entity.

Aggregation

Aggregation is a feature used to model a relationship between a relationship and an entity. It treats an existing relationship as an abstract entity, allowing it to participate in other relationships. This is crucial for modeling scenarios where an action itself is the subject of another action, such as 'Managing' a specific 'Project' assigned to an 'Employee'.

2. Discuss the design process of a database, highlighting the transition from requirements to an ER model. [5 Marks]

Answer:

Requirement Analysis

The design process begins with gathering information about the data needs of the organization. This involves interviewing stakeholders to identify required data points and business rules.

Conceptual Design (ER Model)

In this phase, we map the requirements into an ER diagram:

• Entities: Identify the major objects (e.g., Customers, Products).
• Attributes: Define the properties (e.g., Name, ID, Address).
• Relationships: Determine how entities interact (e.g., Customers buy Products).
• Constraints: Apply constraints like cardinality (1:1, 1:N, M:N) and participation (Total or Partial) to enforce business rules.

Transitioning to Logical Schema

Once the ER model is finalized, it is converted into a logical schema, typically tables, where entities become tables and relationships are represented by foreign keys.

3. Elaborate on the constraints in the ER model: Key constraints, Participation constraints, and Cardinality constraints. [5 Marks]

Answer:

Key Constraints

These define the uniqueness of entities. A primary key constraint ensures that no two entities within an entity set have the same identifier, preventing data redundancy.

Participation Constraints

• Total Participation: Indicated by a double line, it means every instance of the entity must be involved in at least one relationship instance.
• Partial Participation: Indicated by a single line, it means some instances might not participate in any relationship.

Cardinality Constraints

These specify the maximum number of relationships an entity can participate in:

• One-to-One (1:1): One entity is associated with at most one other entity.
• One-to-Many (1:N): One entity is associated with multiple instances of another entity.
• Many-to-Many (M:N): Multiple instances of an entity are associated with multiple instances of another entity.

4. Explain the concept of Weak Entity Sets and their dependency on identifying entities. [5 Marks]

Answer:

Definition

A weak entity set is an entity set that does not possess a primary key of its own. It cannot be identified uniquely without the existence of a corresponding 'Identifying Entity' (or Owner Entity).

Dependency

The existence of a weak entity is dependent on the owner entity. If the owner entity is deleted, the weak entities associated with it usually lose their meaning and are also deleted (cascading delete).

Key Components

• Discriminator: A set of attributes that acts as a partial key for the weak entity. It uniquely identifies the weak entity within the context of the owner entity.
• Identification: The primary key of the weak entity is formed by the combination of the owner entity's primary key and the weak entity's own discriminator.

Example

In a company database, a 'Dependent' entity (like a spouse) might be a weak entity because it is identified by the 'Employee' ID plus the dependent's name.

5. Compare and contrast Attributes in ER models: Simple vs. Composite, and Single-valued vs. Multi-valued. [5 Marks]

Answer:

Simple vs. Composite Attributes

Simple attributes are atomic and cannot be divided (e.g., EmployeeID). Composite attributes can be divided into smaller sub-parts that represent more basic attributes with independent meanings (e.g., 'Address' can be broken into 'Street', 'City', 'State', and 'ZipCode').

Single-valued vs. Multi-valued Attributes

Single-valued attributes take on one specific value for an entity (e.g., Date of Birth). Multi-valued attributes take on a set of values for the same entity (e.g., a list of degrees for an 'Education' attribute). In an ER diagram, multi-valued attributes are represented by double ovals to signify their special nature.

Table Comparison