Unit 2: E-R Modeling - Subjective Questions

An Entity–Relationship model is a conceptual data model used to represent the structure of a database independently of a particular database management system.

Its major components are:

• Entities: Real-world objects or concepts about which data is stored, such as Student or Course.
• Attributes: Properties that describe entities, such as student identification number, name, or email.
• Relationships: Associations among entities, such as a student enrolling in a course.
• Constraints: Rules that restrict the data, including cardinality, participation, and key constraints.

An ER model helps database designers understand data requirements, identify dependencies, and create a clear conceptual design before converting it into relational tables.

Entity type: An entity type is a collection of entities having the same attributes. For example, STUDENT is an entity type with attributes such as StudentID, Name, and DateOfBirth.

Entity set: An entity set is the collection of all entity instances of a particular entity type at a specific point in time.

Entity instance: An entity instance is a single occurrence of an entity type. For example, a student with StudentID S101 is one instance of the STUDENT entity type.

For example, if the STUDENT entity type has attributes (StudentID, Name, Department), then (S101, Anika, Computer Science) represents one entity instance.

Attributes describe the properties of entities or relationships. Important types include:

• Simple attribute: Cannot be divided further, such as Age or Salary.
• Composite attribute: Can be divided into smaller components, such as Address, which may contain Street, City, and PostalCode.
• Single-valued attribute: Has only one value for each entity, such as DateOfBirth.
• Multivalued attribute: May have multiple values, such as PhoneNumbers.
• Derived attribute: Computed from other attributes, such as Age derived from DateOfBirth.
• Stored attribute: Physically stored in the database and used to derive another attribute.
• Key attribute: Uniquely identifies an entity, such as StudentID.

These classifications help determine how attributes should be represented during relational schema conversion.

A relationship represents an association between two or more entities. A relationship set is a collection of relationships of the same type.

Relationship degree is based on the number of participating entity types:

• Unary relationship: Involves one entity type. Example: An employee supervises another employee.
• Binary relationship: Involves two entity types. Example: A student enrolls in a course.
• Ternary relationship: Involves three entity types. Example: A supplier supplies a part to a project.
• Higher-degree relationship: Involves more than three entity types.

Relationships may also have attributes. For example, an ENROLLS relationship can have EnrollmentDate and Grade as attributes.

Strong entity:

• Has its own primary key.
• Can be uniquely identified independently.
• Is represented by a single rectangle.
• Example: EMPLOYEE(EmployeeID, Name).

Weak entity:

• Does not have a complete key of its own.
• Depends on an owner or strong entity for identification.
• Has total participation in an identifying relationship.
• Is represented by a double rectangle.

A partial key, also called a discriminator, identifies weak entity instances associated with the same owner entity. The complete identifier is formed by combining the owner entity's primary key with the weak entity's partial key. For example, a DEPENDENT may be identified by (EmployeeID, DependentName).

Common ER diagram notations include:

• Rectangle: Represents a strong entity type.
• Double rectangle: Represents a weak entity type.
• Oval: Represents an attribute.
• Underlined attribute: Represents a key attribute.
• Double oval: Represents a multivalued attribute.
• Dashed oval: Represents a derived attribute.
• Diamond: Represents a relationship.
• Double diamond: Represents an identifying relationship for a weak entity.
• Lines: Connect entities to relationships and attributes.
• Double line: Indicates total participation.
• Single line: Indicates partial participation.
• Cardinality labels: Such as , , and , describe the number of entity instances that may participate.

Notation may vary slightly between Chen and Crow's Foot conventions, but the underlying concepts remain the same.

Cardinality ratio specifies the maximum number of relationship instances in which an entity can participate.

• One-to-one (): Each entity in one set is associated with at most one entity in another set. Example: A person has one passport.
• One-to-many (): One entity can be associated with many entities, while each related entity is associated with one entity. Example: One department has many employees.
• Many-to-many (): Many entities on both sides can participate. Example: Students enroll in many courses, and courses have many students.

Participation constraint specifies whether participation is mandatory:

• Total participation: Every entity must participate in at least one relationship instance.
• Partial participation: Some entities may not participate.

Cardinality is usually expressed as constraints, such as or .

Specialization is a top-down process in which a higher-level entity is divided into lower-level subtypes based on distinguishing characteristics. For example, EMPLOYEE may be specialized into FACULTY and ADMINISTRATOR.

Generalization is a bottom-up process in which common features of multiple entity types are combined into a higher-level supertype. For example, CAR and TRUCK may be generalized into VEHICLE.

Important constraints include:

• Disjointness: A supertype instance belongs to only one subtype.
• Overlap: A supertype instance may belong to multiple subtypes.
• Total completeness: Every supertype instance must belong to a subtype.
• Partial completeness: Some supertype instances may belong to no subtype.

Subtypes inherit the attributes and relationships of their supertype.

To convert a strong entity type into a relational schema:

1. Create one relation with the same name as the entity type.
2. Include all simple attributes as columns.
3. Replace each composite attribute with its component attributes.
4. Select the entity's key attribute as the primary key.
5. For a multivalued attribute, create a separate relation containing the owner's primary key and the multivalued attribute.
6. Omit derived attributes if they can be computed when needed.

For example, the entity STUDENT(StudentID, Name, Address, PhoneNumbers) may be converted into:

• STUDENT(StudentID, Name, Street, City)
• STUDENT_PHONE(StudentID, PhoneNumber)

Here, StudentID is the primary key of STUDENT and part of the composite key of STUDENT_PHONE.

For a one-to-one relationship between entities and , the primary key of one relation is included as a foreign key in the other relation.

The foreign key should generally be placed:

• In the relation representing the entity with total participation, to avoid unnecessary null values.
• In the relation where the relationship attributes can be stored conveniently.
• In the relation with fewer expected null or redundant values.

For example, suppose each EMPLOYEE manages at most one DEPARTMENT, and each department has exactly one manager. The schema may be:

DEPARTMENT(DepartmentID, DepartmentName, ManagerID)

Here, ManagerID is a foreign key referencing EMPLOYEE(EmployeeID). A UNIQUE constraint can be applied to ManagerID to preserve the one-to-one restriction.

For a one-to-many () relationship, place the primary key of the entity on the one side as a foreign key in the relation on the many side. Any relationship attributes are also stored in the many-side relation.

Example:

• DEPARTMENT(DepartmentID, DepartmentName)
• EMPLOYEE(EmployeeID, Name, DepartmentID)

Here, DepartmentID in EMPLOYEE is a foreign key.

For a many-to-many () relationship, create a new relation for the relationship. Include the primary keys of both participating entity relations as foreign keys. Their combination usually forms the composite primary key.

Example:

• STUDENT(StudentID, Name)
• COURSE(CourseID, Title)
• ENROLLMENT(StudentID, CourseID, EnrollmentDate, Grade)

The primary key of ENROLLMENT is .

A weak entity is mapped by creating a relation containing:

• Its own attributes.
• The primary key of its owner entity as a foreign key.
• Its partial key.
• A composite primary key formed from the owner's primary key and the partial key.

For example:

EMPLOYEE(EmployeeID, Name)

DEPENDENT(EmployeeID, DependentName, Relationship)

The primary key of DEPENDENT is .

For a ternary relationship, create a separate relation containing the primary keys of all three participating entity relations as foreign keys. Include any relationship attributes. The combination of these keys generally forms the primary key, although the exact key may depend on cardinality constraints.

For example, SUPPLIES(SupplierID, PartID, ProjectID, Quantity) represents a supplier supplying a part to a project.

ACID properties ensure that database transactions are processed reliably.

• Atomicity: A transaction is treated as one indivisible unit. Either all operations are completed or none are applied.
• Consistency: A transaction changes the database from one valid state to another while preserving all integrity constraints.
• Isolation: Concurrent transactions should not interfere in a way that produces incorrect results. Intermediate states are hidden from other transactions.
• Durability: Once a transaction commits, its changes survive system failures and power loss.

For example, in a bank transfer, money must be deducted from one account and added to another atomically. If a failure occurs after deduction but before deposit, recovery mechanisms must undo or complete the transaction. ACID properties therefore protect correctness, concurrency, and persistence.

Atomicity implementation:

• The DBMS records transaction actions in a log.
• If a transaction fails before commit, the system performs rollback using the log.
• Locks, checkpoints, and recovery protocols help restore the previous consistent state.

Durability implementation:

• A commit record is written to stable storage before the commit is acknowledged.
• Write-ahead logging ensures that log records reach storage before modified data pages are written.
• Checkpoints reduce recovery time.
• Backup copies, replication, and archived logs provide additional protection.

During recovery, the DBMS generally performs:

• Redo: Reapply changes of committed transactions.
• Undo: Reverse changes of incomplete transactions.

These mechanisms ensure that committed work is preserved and incomplete work is removed.

Consistency means that every committed transaction satisfies database rules such as primary key, foreign key, domain, and check constraints. For example, a foreign key constraint prevents an enrollment from referring to a nonexistent student.

Isolation means that concurrent transactions behave as if they were executed in an appropriately controlled order. It prevents problems such as:

• Dirty reads
• Non-repeatable reads
• Lost updates
• Phantom reads

Concurrency control supports isolation through:

• Locking protocols, such as two-phase locking.
• Timestamp ordering.
• Multiversion concurrency control.
• Serializable scheduling.

Together, integrity constraints protect valid states, while concurrency control prevents simultaneous operations from producing invalid or unexpected results.

Relational constraints restrict the values and relationships allowed in a database.

• Domain constraint: Specifies the permitted values and data type of an attribute. For example, Age may be restricted to integers between and .
• Key constraint: Ensures that no two tuples have the same value for a candidate or primary key.
• Entity integrity: Requires that a primary key be unique and never contain NULL.
• Referential integrity: Requires every non-null foreign key value to match a referenced primary key value in another relation.

For example, if EMPLOYEE(DepartmentID) references DEPARTMENT(DepartmentID), an employee cannot be assigned to a department that does not exist. These constraints maintain accuracy, consistency, and meaningful relationships among data.

Consider the relation STUDENT(StudentID, Email, Name, DepartmentID).

• Super key: Any set of attributes that uniquely identifies a tuple. For example, {StudentID} and {StudentID, Name} are super keys.
• Candidate key: A minimal super key. StudentID and possibly Email are candidate keys if each is unique.
• Primary key: The candidate key selected to identify tuples officially. For example, StudentID.
• Alternate key: A candidate key not selected as the primary key. If Email is unique but not selected, it is an alternate key.
• Foreign key: An attribute or set of attributes referencing a key in another relation. DepartmentID may reference DEPARTMENT(DepartmentID).

A primary key identifies rows within a relation, whereas a foreign key represents a relationship between relations.

Composite key: A key consisting of two or more attributes. For example, can identify an enrollment.

Natural key: A key derived from meaningful real-world data, such as a national identification number or ISBN.

Surrogate key: An artificial identifier generated by the system, such as an auto-incrementing integer.

Comparison:

• Composite keys accurately represent uniqueness but may make foreign keys and joins more complex.
• Natural keys have business meaning and may avoid extra columns, but they can change or be lengthy.
• Surrogate keys are compact, stable, and efficient for joins, but they have no business meaning and require an additional uniqueness constraint when necessary.

The appropriate choice depends on stability, uniqueness, size, and application requirements.

A possible relational design is:

• DEPARTMENT(DepartmentID, DepartmentName)
• STUDENT(StudentID, StudentName, Email, DepartmentID)
• INSTRUCTOR(InstructorID, InstructorName, DepartmentID)
• COURSE(CourseID, CourseTitle, Credits, DepartmentID, InstructorID)
• ENROLLMENT(StudentID, CourseID, Semester, Grade)

Keys and constraints:

• DepartmentID is the primary key of DEPARTMENT.
• StudentID is the primary key of STUDENT.
• InstructorID is the primary key of INSTRUCTOR.
• CourseID is the primary key of COURSE.
• The primary key of ENROLLMENT is .
• DepartmentID in STUDENT, INSTRUCTOR, and COURSE is a foreign key referencing DEPARTMENT.
• InstructorID in COURSE references INSTRUCTOR.
• StudentID and CourseID in ENROLLMENT reference STUDENT and COURSE.
• Credits should satisfy a domain constraint such as .
• Email may be declared UNIQUE.

This schema represents one-to-many department relationships and a many-to-many student-course relationship through ENROLLMENT.

The ER model is a conceptual model, while the relational model is a logical model based on relations or tables.

The ER model helps designers capture requirements, communicate with users, identify entities and relationships, detect missing data requirements, and produce a structured relational schema through systematic conversion rules.