Bài 03 :: Mô hình dữ liệu quan hệ

Relational Database Model

The lesson introduces the relational database model and explains how to convert an entity–relationship (ER) model into relational tables. It covers core relational concepts and ER-to-relational mapping rules.

Relational Database Fundamentals

The relational model represents data in tables, with a logical structure grounded in set theory. Edgar F. Codd proposed the model in his 1970 article, “A Relational Model for Large Shared Data Banks.”

• A relation is a table. Its columns are defined by a schema and have names and data types; its rows represent individual records, such as employees.
• An attribute is a column that describes a property of the records. Values in a column share a data type, such as string, date, or integer.
• A tuple is a row containing one value for each attribute. A value may be null when it is absent.
  • Tuples should not be duplicated.
  • Row order has no meaning, but each value’s position matters because it corresponds to a particular attribute.
• A relational schema describes a relation’s name and attributes, often written as R(A1, A2, …, An). Its degree is the number of attributes it contains.
• A database schema is the collection of relational schemas that make up a database.
• A domain is the set of allowed values for an attribute, typically based on a basic data type such as integer, string, date, or Boolean.

Keys and Relationships Between Tables

• A superkey is any set of attributes that uniquely identifies each tuple in a relation. It may contain more attributes than necessary.
• A candidate key is a minimal superkey: removing any attribute would mean it no longer uniquely identifies tuples.
• A primary key is the candidate key chosen to identify records. It must not contain null values. A primary key made up of multiple attributes is a composite key.
• A foreign key is an attribute—or set of attributes—in one relation that refers to a key in another relation. It links related records and supports referential integrity.
  • An attribute can serve as both a primary key and a foreign key.
  • A foreign key can refer to a key in the same relation, as in an employee-manager relationship.
  • Multiple foreign keys can refer to the same primary key.

Converting an ER Model into a Relational Model

Strong Entities

Create a relation for each strong entity, usually using the entity’s name. Include its attributes and designate its key as the primary key. Break composite attributes into their component attributes when appropriate.

Many-to-Many Relationships

Create a separate relation for the relationship. Include the primary keys of the participating entities as foreign keys, along with any attributes belonging to the relationship. The participating keys commonly form a composite primary key.

For example, an employee’s project assignment can store the employee ID, project ID, and hours worked.

One-to-Many Relationships

Add the primary key from the “one” side to the relation on the “many” side. The added attribute becomes a foreign key.

For example, if each employee works in one department and a department has many employees, add the department ID to the employee relation.

One-to-One Relationships

Transfer the primary key of one participating relation into the other as a foreign key. The direction depends on the design.

For a recursive relationship, such as one employee supervising others, add a self-referencing foreign key to the same relation. Use a distinct attribute name, such as manager_id.

Weak Entities

Create a relation for the weak entity and include the primary key of its owning strong entity as a foreign key. Combine the owner’s key with an attribute or set of attributes that distinguishes the weak entity’s records to form a primary key.

Choose the identifying attributes carefully so the combination is unique. Additional attributes may be needed if names alone are not sufficient.

Speakers and Sources

• Speaker: One instructor or narrator; no other speakers are identifiable in the subtitles.
• Source cited: Edgar F. Codd’s 1970 article, “A Relational Model for Large Shared Data Banks,” published in the Journal of the ACM.
• Illustrative examples: Employee, department, project, assignment, family-member, driver, and driver’s-license data models.