Bài 02 :: Mô hình thực thể kết hợp

Summary

The lecture introduces the Entity–Relationship (ER) model as a way to translate real-world data requirements into a relational database design. It covers the database design process, core ER concepts and notation, and a practical sequence for building an ER diagram.

1. Database Design Process

Database design aims to create a logical and physical data structure that efficiently supports users’ and applications’ requirements. The lecture outlines these stages:

1. Gather and analyze requirements

   • Meet with users and people involved in the organization’s processes.
   • Use interviews and other direct discussions to understand how work is currently done and what information needs to be stored.
   • Base the design on actual operational needs.

2. Develop a conceptual design

   • Model real-world data requirements using entities, attributes, and relationships.
   • Use the conceptual model as an intermediate step between the real-world problem and a relational database.

3. Choose a database management system (DBMS)

   • Select a system appropriate to the implementation environment and its operational and maintenance needs.

4. Create the logical design

   • Organize the data structure at the database-management level, based on the conceptual design.

5. Create the physical design

   • Decide how data will be stored and organized.
   • Consider indexes and other choices that affect performance, especially as the amount of data grows.

6. Review and adjust

   • Check and refine the design before putting the database into operation.

2. Example Used in the Lecture

A company-management scenario illustrates how to identify data requirements. The company has:

• Departments, each with identifying information and a manager.
• Projects, each with identifying information and an associated department.
• Employees, with details such as an ID, name, gender, date of birth, address, salary, and hobbies.
• Employee assignments, since employees may work on multiple projects. The system needs to track their work hours and responsibilities over time.
• Employee relatives, whose names, personal details, and relationships to employees may need to be recorded.

The example shows how an informal description of business needs can be converted into entities, attributes, relationships, and constraints.

3. Core ER Model Concepts

• Entity: A distinguishable real-world object or concept, such as an employee, department, project, invoice, or student.
• Entity set/type: A collection of similar entities. One employee is an entity; all employees form an employee set.
• Attribute: A property that describes an entity or relationship, such as an employee’s name, birth date, or salary. Attributes have values and data types.
• Relationship: An association between entities, such as an employee participating in a project or working in a department. Relationships can also have attributes, such as the number of hours an employee works on a project.

An ER diagram represents these elements graphically. Common notation includes:

• Rectangles for entities.
• Ellipses for attributes.
• Diamonds for relationships.
• Variations such as double shapes or dashed lines to distinguish certain kinds of entities, relationships, or attributes.
• Cardinality and participation markers to show how many entities may take part in a relationship and whether participation is optional or required.

4. Attribute Types and Keys

Attributes can be classified in several ways:

• Simple or composite: A simple attribute cannot be broken into smaller parts; a composite attribute can, such as a name divided into given and family names.
• Single-valued or multivalued: An employee ID is generally single-valued, while a person may have several hobbies.
• Stored or derived: Age can be calculated from a stored date of birth.
• Key attribute: An attribute, or combination of attributes, that uniquely identifies an entity. For example, an employee ID can identify an employee. Some records require a composite key made from multiple attributes, such as student ID, semester ID, and course code for a course registration.

Entities of the same type share a common set of attributes, while their key values distinguish one entity from another.

5. Relationships, Cardinality, and Participation

Relationships may involve different numbers of entity types:

• Binary: Connects two entity types, such as employees and projects.
• Recursive (unary): Connects entities of the same type, such as one employee supervising another.
• Ternary: Connects three entity types, as in a registration scenario involving a student, a course, and a semester.

Cardinality constraints describe how many instances may be related—for example, one-to-one, one-to-many, or many-to-many. Participation constraints indicate whether an entity’s involvement is mandatory or optional.

In the company example, an employee may belong to one department, a department may have many employees, and an employee may participate in multiple projects or none.

6. Strong and Weak Entities

• A strong entity exists independently and has its own identifying key.
• A weak entity depends on another entity for its existence or identification. Its key may incorporate the key of the related strong entity, and it must participate in the identifying relationship.

Examples include invoice details that depend on an invoice, course-registration records that depend on a student, and relatives recorded in association with an employee. ER diagrams use distinctive notation—such as double rectangles and double diamonds—to represent weak entities and their identifying relationships.

7. Practical Steps for Constructing an ER Model

The lecture gives this sequence for building an ER model:

1. Identify all entity sets in the problem.
2. Determine the relationships among those entities.
3. Identify attributes and assign each to the appropriate entity or relationship.
4. Specify the value range or data type for each attribute.
5. Choose key attributes that uniquely identify entities.
6. Define relationship constraints, including minimum and maximum participation (cardinality and optionality).

The resulting model should accurately reflect the problem, avoid unnecessary duplication, and be clear and easy to understand.

Speakers and Sources Featured

• One lecturer/instructor presents the material and examples. No personal name is identifiable in the subtitles.
• No other identifiable speakers or named external sources are featured.