Featured image

Table of Contents Link to heading

Terminology Mapping Link to heading

The relational model uses precise terminology that differs from common everyday usage. Understanding the mapping prevents confusion when moving between academic material and practical SQL work:

Relational Term SQL / Common Term Alternative
relation table —
attribute column field
tuple row record

Three structural rules apply to every relation:

  • Every column name must be unique within the table
  • Every row must be unique — no exact duplicate records
  • Every row must have a unique Primary Key that identifies it and no other record

Relations Link to heading

A relation defines a real-world or conceptual object that the database collects information about. When implemented in a DBMS, a relation is a table.

A relational database consists of multiple relations, each with a distinct name. By convention, relation names use PascalCase — Student, CourseEnrolment, ProductOrder.

Some relations represent tangible objects (Book, Employee, Product). Others represent intangible but meaningful concepts (Course, Sale, Contract). Both are first-class entities in the relational model.

Attributes Link to heading

An attribute is a property that describes a relation. Each attribute must have a unique name within its relation. By convention, attribute names use camelCase — studentID, dateOfBirth, courseCode.

Every attribute has a domain — a definition of the type and range of values it can hold. Domains are the most fundamental constraint in the relational model and are enforced by the DBMS on every insert and update.

Types of Attributes Link to heading

Understanding attribute types is important for database design — they determine how data should be structured and stored:

1. Simple attributes hold a single atomic value that cannot be meaningfully decomposed.

  • A phone number (10 digits) is a simple attribute — splitting it further loses meaning.

2. Composite attributes are composed of two or more simple attributes that together describe a concept.

  • A person’s full name may consist of firstName and lastName.
  • In the relational model, composite attributes are typically decomposed into their component simple attributes — storing a single fullName column makes searching and sorting harder.

3. Derived attributes are calculated from other stored attributes rather than stored directly.

PurchaseOrder
quantity
price
/total
  • total = quantity × price — total is a derived attribute (often indicated with / prefix in notation)
  • Derived attributes are generally not stored in the database; they are computed at query time to avoid redundancy and update anomalies

4. Structured attributes are composed of multiple sub-attributes organised hierarchically.

Employee
name
→ salutation
→ firstName
→ lastName
address
→ addressLine1
→ addressLine2
  • name is a structured attribute containing salutation, firstName, and lastName
  • Like composite attributes, structured attributes are decomposed into their components during logical design

5. Single-valued attributes hold exactly one value per tuple — the standard case.

  • taxFileNumber, dateOfBirth, studentID

6. Multivalued attributes can hold more than one value for a single entity.

  • A person may have multiple phoneNumber values, multiple emailAddress values
  • Multivalued attributes violate the first normal form (1NF) requirement of atomic values
  • Resolution: move the multivalued attribute to a separate table, linked to the original by a foreign key

Multivalued attribute

Domains and Data Types Link to heading

A domain defines the data type and the range of acceptable values for an attribute. Declaring an attribute’s domain constrains what the DBMS will accept — any value that violates the domain constraint is rejected at the time of insertion or update, before it reaches the data.

Common SQL data types:

Data Type Description
char(n) Fixed-length string of exactly n characters — padded with spaces if shorter
varchar(n) Variable-length string up to n characters — no padding
int Integer (whole number) — range is platform-dependent
decimal(m, n) Decimal number with m total digits and n decimal places
date Date value (day/month/year)
datetime Date and time (day/month/year hour:minute:second)
bit Boolean value (1/0 or True/False)

Domain declaration examples:

Attribute Name Domain
studentName varchar(100)
favColour varchar(10) — values: {red, green, blue}
Tip
Prefer varchar over char for text fields. char(n) pads shorter values with trailing spaces, which can cause subtle comparison failures — 'John' stored in char(10) becomes 'John ', which will not match the string 'John' in an equality check without explicit trimming.

Table Schemas Link to heading

A table schema is the structural definition of a relational table — its name, attributes, and their domains. It is the design of the table, distinct from the data the table contains.

Format: RelationName(attribute1, attribute2, …)

Example:

Customer(customerID, firstName, familyName, address, dateOfBirth)

The schema defines what the table looks like; the data populates it. A schema can exist with zero tuples (an empty table) — the structure is valid regardless of whether data has been inserted.

Tuples Link to heading

A tuple is a single row of data in a relation — one instance of the entity the relation describes. Every tuple contains one value per attribute, in the order defined by the schema.

Key properties:

  • Tuples within a relation are unordered — the relational model does not guarantee retrieval order; use ORDER BY in SQL to control it
  • Every tuple has a fixed number of values — one per attribute, no more, no less
  • No two tuples may be identical — the uniqueness requirement is enforced by the Primary Key constraint

Putting It Together Link to heading

Terms and Notations

The diagram shows a complete relation with its schema, attributes (columns), and tuples (rows). Each column has a defined domain; each row is a unique instance of the entity; the Primary Key column uniquely identifies every tuple.