The Relational Database Model

Relational Database Model

organizes data into relations, commonly known as tables.

Tables

to store data

Rows

to represent records

Columns

to represent attributes

Keys

to uniquely identify and connect records

Constraints

to maintain data accuracy and integrity

Relation

is a table made up of rows and columns.

Tuple

is a row in a relation. It represents one record.

Attribute

is a column in a table. It describes a property of an entity.

Domain

is the set of allowable values for an attribute.

Relational Schema

describes the structure of a relation.

Keys

help identify records and connect tables.

Superkey

A superkey is any attribute or combination of attributes that can uniquely identify a row in a table.

Example: STUDENT(StudentID, Email, StudentName)

Candidate Key

is a minimal superkey. It uniquely identifies a row without unnecessary attributes.

Example: StudentID, Email

Primary Key

is the candidate key selected as the main identifier of a table.

Example: STUDENT(StudentID, StudentName, Program)

Composite Key

A composite key is a key made up of two or more attributes.

Example: ENROLLMENT(StudentID, CourseCode, Semester)

Foreign Key

is an attribute in one table that refers to the primary key of another table.

Example: STUDENT(StudentID, StudentName) PK

ENROLLMENT(EnrollmentID, StudentID, CourseCode) FK

Partial Key

is an attribute of a weak entity that can identify weak entity records only within the same owner entity.

Integrity Constraints

are rules that ensure data in the database remains accurate, valid, and consistent.

Entity Integrity Constraint

The entity integrity constraint states that a primary key cannot be null.

This is because the primary key is used to identify each record.

Referential Integrity Constraint

states that a foreign key value must match an existing primary key value in the related

table, or it must be null if allowed.

Domain Integrity Constraint

ensures that attribute values belong to a valid set of values.

Example: Grade must be between 1.00 and 5.00.

Units must be greater than 0.

Key Constraint

ensures that candidate keys or primary keys are unique.

Null Constraint

controls whether an attribute is allowed to have no value.

Example: StudentName must not be null.

MiddleName may be null.

Unique Constraint

ensures that no two records have the same value for a certain attribute.

Rule 1: Map Each Strong Entity into a RelationRule 1: Map Each Strong Entity into a Relation

Each strong entity becomes a table.

The entity’s simple attributes become columns.

The entity’s key becomes the primary key of the table.

Rule 2: Map Composite Attributes into Their Simple Components

A composite attribute should be broken down into its individual parts.

Rule 3: Map Multivalued Attributes into Separate Relations

A multivalued attribute should be placed in a separate table.

Example: If a student may have multiple contact numbers, create a separate relation.

Rule 4: Map One-to-One Relationships

For a 1:1 relationship, place the primary key of one table as a foreign key in the other table.

Rule 5: Map One-to-Many Relationships

For a 1:M relationship, place the primary key of the “one” side as a foreign key in the “many” side.

Rule 6: Map Many-to-Many Relationships

For an M:M relationship, create a new relation, also called an associative table or junction table.

Rule 7: Map Relationship Attributes

If a relationship has its own attributes, place those attributes in the relation created for the relationship.

Rule 8: Map Weak Entities

A weak entity depends on an owner entity.

map a weak entity:

Create a relation for the weak entity.

map a weak entity:

●Include the primary key of the owner entity as a foreign key.

map a weak entity:

Combine the owner key with the weak entity’s candidate key to form the primary key.