Terse Notes — Fundamentals of Database Systems Part 3

Jesse Ruiz (she/they)
6 min readJun 30, 2024

--

Photo by Tim Mossholder on Unsplash

Book Credit to Elmasri Ramez And Navathe Shamkant

Book: Fundamentals Of Database System, 7th Edition by Elmasri Ramez And Navathe Shamkant

Ch 3 — Data Modeling Using the Entity–Relationship (ER) Model

Review Questions

3.1. Discuss the role of a high-level data model in the database design process.

A high-level conceptual data model plays a crucial role in the database design process:

  • It allows designers to describe the data requirements and constraints in a concise and precise manner, without worrying about implementation details.
  • The conceptual schema created using the high-level model serves as a reference to ensure all user data requirements are met and do not conflict.
  • It enables communication between designers and non-technical users by using concepts that are easier to understand than low-level implementation details.
  • Designers can focus on specifying the properties and constraints of the data during the conceptual design phase, making it easier to create a good database design.

3.2. List the various cases where use of a NULL value would be appropriate.

NULL values would be appropriate in the following cases:

  • To represent missing or unknown data values
  • For attributes with values that do not exist in the real world being modeled
  • When the value for an attribute is not known at the current time but may be known at a later time
  • To represent nulls introduced in a relational database by operations like left/right/outer joins

3.3. Define the following terms: entity, attribute, attribute value, relationship instance, composite attribute, multivalued attribute, derived attribute, complex attribute, key attribute, and value set (domain).

Entity: A thing or object in the real world with an independent existence, such as a person, car, or company.

Attribute: A property that describes an entity, such as an employee’s name or age.

Attribute value: The specific value for a given attribute of a particular entity, such as ‘John Smith’ for the Name attribute of an EMPLOYEE entity.

Relationship instance: An association among individual entities from the participating entity types in a relationship type.

Composite attribute: An attribute that can be further subdivided into component attributes, such as Name consisting of First_name, Middle_initial, and Last_name.

Multivalued attribute: An attribute that can have a set of values for a single entity instance, such as Locations for a DEPARTMENT entity.

Derived attribute: An attribute whose value can be derived from other attribute values.

Complex attribute: An attribute with a structure composed of other attribute components, such as Address with components Street, City, Zip.

Key attribute: An attribute whose values uniquely identify each entity instance of an entity type.

Value set (domain): The set of all possible values an attribute can take.

3.4. What is an entity type? What is an entity set? Explain the differences among an entity, an entity type, and an entity set.

Entity type: A description of an entity, including its name and attributes. For example, EMPLOYEE is an entity type with attributes like Name, Ssn, etc.

Entity set: The collection of all entities of a particular entity type that exist in the database at a given point in time. For example, the set of all current employee entities.

An entity type is a conceptual definition, while an entity set represents the actual entity instances based on that definition in the database.

3.5. Explain the difference between an attribute and a value set.

An attribute is a property that describes an entity.

A value set (domain) is the set of all possible values that an attribute can take. For example, the value set of the Sex attribute may be {M, F}.

3.6. What is a relationship type? Explain the differences among a relationship instance, a relationship type, and a relationship set.

Relationship type: The descriptive definition of an association among two or more entity types, such as the WORKS_ON relationship among EMPLOYEE and PROJECT.

Relationship set: The collection of all relationship instances of a particular relationship type that exist in the database.

Relationship instance: A single association among individual entities from the participating entity types in a relationship type. For example, (John, P1) is an instance of WORKS_ON.

3.7. What is a participation role? When is it necessary to use role names in the description of relationship types?

A participation role specifies the function that an entity type plays in a relationship type. Role names are used when the meaning of a relationship type is not clear from just the entity types involved.

For example, in a CHILD_CUSTODY relationship between two PERSON entity types, role names like Parent and Child help clarify each participant’s role.

3.8. Describe the two alternatives for specifying structural constraints on relationship types. What are the advantages and disadvantages of each?

There are two alternatives for specifying structural constraints like cardinality ratios on relationship types:

  1. Graphical notation in ER diagrams using specific symbols and attachments.
  2. Verbal specification as assertions or rules accompanying the ER diagram.

The graphical notation is more concise but can get complicated for complex constraints. Verbal specifications are more flexible but can be ambiguous.

3.9. Under what conditions can an attribute of a binary relationship type be migrated to become an attribute of one of the participating entity types?

An attribute of a binary relationship type can be migrated to become an attribute of one of the participating entity types if:

  • The attribute depends on the existence of only one of the two entity types involved in the relationship
  • The attribute’s value makes sense only for entities of that particular entity type

For example, the Hours attribute of the WORKS_ON relationship could be migrated to the EMPLOYEE entity type if hours worked is specific to each employee rather than to the project.

3.10. When we think of relationships as attributes, what are the value sets of these attributes? What class of data models is based on this concept?

When we think of relationships as attributes:

  • For a binary relationship R between entity types A and B, we can have an attribute of A whose value set is the B entity set (or vice versa).
  • Alternatively, we can have a multivalued attribute of A whose value set is the power set of the B entity set (set of all subsets of B).

This perspective of treating relationships as attributes is the basis for the object-oriented and object-relational data models.

3.11. What is meant by a recursive relationship type? Give some examples of recursive relationship types.

A recursive relationship type is a relationship type where an entity type participates in a relationship with other entities of the same type.

Examples:

  • The SUPERVISION relationship among EMPLOYEE entities, where an employee supervises other employees
  • The PARTOF relationship among product components in a manufacturing database

3.12. When is the concept of a weak entity used in data modeling? Define the terms owner entity type, weak entity type, identifying relationship type, and partial key.

The concept of a weak entity type is used when:

  • Some entities do not have a primary key attribute of their own
  • Their identification depends on being related to specific entities from another entity type

Owner entity type: The entity type on which the weak entity type depends for identification

Weak entity type: The entity type that does not have key attributes of its own

Identifying relationship type: The relationship type relating the weak entity to the owner that allows identification of weak entities

Partial key: The attribute(s) of a weak entity type that uniquely identify weak entities related to the same owner entity

3.13. Can an identifying relationship of a weak entity type be of a degree greater than two? Give examples to illustrate your answer.

Yes, an identifying relationship type relating a weak entity type to its owner can have a degree greater than two.

For example, in a COMPANY database:

  • EMPLOYEE is a weak entity type identified by the ternary relationship WORKS_FOR relating it to the owner entity types COMPANY and DEPARTMENT
  • The partial key of EMPLOYEE could be Employee_number unique across each (Company, Department) pair

3.14. Discuss the conventions for displaying an ER schema as an ER diagram.

Conventions for displaying an ER schema as an ER diagram include:

  • Entity types are represented as rectangular boxes
  • Attribute names are displayed in ovals attached to their entity types
  • Multivalued attributes use double ovals
  • Relationship types are represented by diamond-shaped boxes attached to the participating entity types
  • Role names are displayed next to the relationship diamond
  • Structural constraints like cardinality ratios are shown using specific notation

3.15. Discuss the naming conventions used for ER schema diagrams.

Common naming conventions for ER schemas include:

  • Using singular names for entity types (EMPLOYEE) and plural for relationship types (WORKS_ON)
  • Avoiding abbreviations and acronyms to improve readability
  • Using active verb phrases for relationship type names (MANAGES, SUPERVISES)
  • Choosing names that are brief but still convey meaning
  • Maintaining consistency in naming across the schema

The key aspects are choosing clear, concise and meaningful names while following consistent conventions throughout the schema design.

--

--