Basics of the Relational Model

# 1
read: Section 2.2: Basics of the Relational Model (section)
rules: presentation short bullet

revise: the current content is fine.  Don't change it much, just make it a bit shorter.

The relational model, a cornerstone of database management, offers a constrained yet highly effective approach to data organization. Its important aspects are as follows:

Data Representation

Data is represented as two-dimensional tables, which we call relations. This uniform structure simplifies organization.
Attributes

Columns within a relation are named attributes, each describing a distinct property of the entities in the table.
Schemas

A relation’s schema includes its name and its set of attributes, optionally with their domains. A database schema comprises all relation schemas.
Tuples

Rows of a relation are called tuples. Each tuple represents a single record with component values for all attributes.
Domains

Every attribute has an assigned domain, which mandates that its values must be atomic—simple types like integer or string, not complex structures.
Keys

A key is a set of attributes that uniquely identifies each tuple within a relation. This prevents duplicate records.
Set-Based Nature

Relations are fundamentally sets of tuples. Consequently, the order in which tuples are presented is immaterial.

gemini-2.5-flash

Version: 2
Model: gemini-2.5-flash
Created: 2025-08-31 00:59:59
Duration: 7.4106621742248535
Cummulative cost: $8.50e-03

Let’s dive into each of them in detail.

Attributes

Section outline

Defines ‘attributes’ as the names of columns in a relation.
Provides an example using Figure 2.3, where ‘title’, ‘year’, ‘length’, and ‘genre’ are attributes.
An attribute describes the meaning of the entries in its column.

# 2
read: Section 2.2.1: Attributes (section)
model: pro
rules: definition short presentation

Define an attribute.  Give an example based on an example in the book.

revise: in the definition, use math notation and bullets
revise: for the example, keep the schema, but give a simple table instance with only a few rows.

Definition: Attribute

An attribute is a named column of a relation. Formally, an attribute has the following properties:

A name, such as title or length, that is unique within a given relation schema.

An associated domain, denoted as $dom(\text{attribute})$, which specifies the set of permissible atomic values for that attribute.

The attribute describes a property of the entities represented by the relation. All values appearing in the column for an attribute must be drawn from its domain.

For example, consider a relation Movies with the schema Movies(title, year, length, genre). The attributes are title, year, length, and genre, where each specifies a property of a movie. An instance of this relation is shown below.

title	year	length	genre
Gone With the Wind	1939	231	drama
Star Wars	1977	124	sciFi
Wayne’s World	1992	95	comedy

gemini-2.5-pro

Version: 4
Model: gemini-2.5-pro
Created: 2025-08-31 01:32:12
Duration: 21.456767082214355
Cummulative cost: $8.21e-02

Schemas

Section outline

Defines a relation ‘schema’ as the combination of the relation name and its set of attributes.
Illustrates the notation for a schema, for example, Movies(title, year, length, genre).
Notes that while attributes form a set (unordered), a standard order is often specified for practical purposes.
Defines a ‘relational database schema’ (or ‘database schema’) as the collection of schemas for all relations in a database.

# 3
read: Section 2.2.2: Schemas (section)
model: pro
rules: short definition presentation

First define what a relation schema is: it should be the relation name and its set of attributes.  Make sure to mention that the order of attributes doesn't matter.  Use math notation.

Then define what a database schema is.  It is a collection of the relation schema.  Use math notation.

Then give an example based on `movies(title, year, length, genre)` table.

revise: the content generated so far is good.  But in the definition of the database schema, explicitly indicate the attributes of each relation schema as well.

A relation schema specifies the name of a relation and the set of attributes for that relation. A database schema is the collection of all relation schemas for a given database.

Definition: Relation Schema

A relation schema consists of a relation name $R$ and a set of attributes $\{A_1, A_2, \dots, A_n\}$. It is denoted by $R(A_1, A_2, \dots, A_n)$. Since the attributes form a set, their order is immaterial.

Definition: Database Schema

A database schema is the set of schemas for all relations in the database. If the relations are $R_1, \dots, R_m$, the database schema is the set $\{R_1(A_{11}, \dots), \dots, R_m(A_{m1}, \dots)\}$.

For instance, the schema for the Movies relation described in the text is written as:

\[ \text{Movies}(\text{title, year, length, genre}) \]

Here, Movies is the name of the relation, and {title, year, length, genre} is its set of attributes. A complete database schema for motion pictures would include this relation schema along with others, such as schemas for actors and studios.

gemini-2.5-pro

Version: 3
Model: gemini-2.5-pro
Created: 2025-08-31 01:48:37
Duration: 16.102792024612427
Cummulative cost: $7.45e-02

Tuples

Section outline

Defines ‘tuples’ as the rows of a relation, excluding the header row.
Explains that a tuple has one component for each attribute of the relation.
Provides an example of a tuple written in isolation, like (Gone With the Wind, 1939, 231, drama).
Highlights that when a tuple is written in isolation, the component order must match the attribute order in the relation schema.

# 4
read: Section 2.2.3: Tuples (section)
model: flash
rules: short presentation definition

First define what a tuple is.  Use math notation.
Then explain the components of a tuple, and how it corresponds to attributes.  Use the example of "Gone with the wind" movie to explain how to match the components to attributes.

Argue that a dictionary might be a better representation of a tuple, but it's too verbose, so we drop the attributes when it is clear.

Revise: show the dictionary for the "Gone with the wind" tuple with the attribute names as a python dictionary in a code block.

Definition: Tuple

The rows of a relation, excluding the header, are designated as tuples. Formally, for a relation $R$ with an ordered schema $R(A_1, A_2, \ldots, A_n)$, a tuple $t$ is an $n$-component sequence: \[ t = (v_1, v_2, \ldots, v_n) \] Here, each $v_i$ is a value drawn from the domain of the corresponding attribute $A_i$.

Each tuple constitutes a singular record within the relation, possessing one component for each attribute defined in the relation’s schema. Consider the Movies relation, with a schema Movies(title, year, length, genre). The tuple (Gone With the Wind, 1939, 231, drama) illustrates this structure, containing four components, each aligned with its respective attribute:

Gone With the Wind for the title attribute.
1939 for the year attribute.
231 for the length attribute.
drama for the genre attribute.

While one might conceive of a tuple as a collection of attribute-value pairs, similar to a dictionary, as depicted below for the example movie:

{
    'title': 'Gone With the Wind',
    'year': 1939,
    'length': 231,
    'genre': 'drama'
}

This explicit naming of attributes for every component is often deemed overly verbose in practice. Thus, the standard representation of a tuple, particularly when presented in isolation, omits these explicit attribute names. This practice relies on the established order of attributes in the relation schema to infer the meaning of each component, thereby maintaining a compact notation.

gemini-2.5-flash

Version: 2
Model: gemini-2.5-flash
Created: 2025-08-31 01:56:43
Duration: 7.88500714302063
Cummulative cost: $8.67e-03

Domains

Section outline

Specifies that each component of a tuple must be ‘atomic’, meaning it belongs to an elementary type like integer or string and cannot be a complex structure.
Introduces the concept of a ‘domain’ as the specific elementary data type associated with each attribute.
Shows an extended schema notation that includes domains, for instance, Movies(title:string, year:integer, length:integer, genre:string).

# 5
read: Section 2.2.4: Domains (section)
model: flash
rules: presentation short definition

Discuss what domains are for attributes.  Give some example using the `Movie` table.

Definition: Domain

A domain for an attribute is the set of all permissible atomic values that may appear in that attribute’s column within a relation.

In the relational model, each attribute is fundamentally associated with a domain. This domain dictates the elementary type of data that the attribute can hold, such as an integer, a character string, or a date. The crucial requirement is that all values for an attribute must be atomic; composite structures, such as records or arrays, are not permitted as single components within a tuple.

Consider the Movies relation introduced previously. Its schema, when augmented with domain specifications, provides clarity on the expected data types:

Movies (title:string, year:integer, length:integer, genre:string)

From this schema, we delineate the specific domains for each attribute:

title: A string type
year: An integer type
length: An integer type
genre: A string type

Consequently, every tuple in the Movies relation must conform to these specifications, ensuring data consistency and integrity.

gemini-2.5-flash

Version: 2
Model: gemini-2.5-flash
Created: 2025-08-31 02:03:11
Duration: 4.700090169906616
Cummulative cost: $4.50e-03

Equivalent Representations of a Relation

Section outline

Emphasizes that relations are sets of tuples, not lists, so the order of tuples is immaterial.
Explains that the order of attributes (columns) can also be changed, as long as the components within each tuple are permuted in the same way.
Concludes that different orderings of rows and columns are considered different presentations of the same relation.

# 6
read: Section 2.2.5: Equivalent Representations of a Relation (section)
model: flash
rules: presentation short

First give two equivalent versions of the movies relation with the same set of tuples, but permutated in different order.  Remark that they are equivalent modulo row permutation.

Then give two equivalent of the movie relation (same tuples), but with attribute permutation.

A relation, as a set of tuples, permits several equivalent presentations. The order in which the tuples appear is immaterial. Likewise, the order of attributes can be varied, provided the components of each tuple are permuted to match the new attribute order.

Consider the Movies relation from Figure 2.3:

title	year	length	genre
Gone With the Wind	1939	231	drama
Star Wars	1977	124	sciFi
Wayne’s World	1992	95	comedy

This relation is equivalent to the following, where the order of rows has been permuted:

title	year	length	genre
Star Wars	1977	124	sciFi
Wayne’s World	1992	95	comedy
Gone With the Wind	1939	231	drama

Furthermore, the order of attributes does not alter the underlying relation. If we permute the attributes, the components of each tuple must also be permuted accordingly. The following table represents the same Movies relation, but with a different attribute order:

year	genre	title	length
1939	drama	Gone With the Wind	231
1977	sciFi	Star Wars	124
1992	comedy	Wayne’s World	95

gemini-2.5-flash

Version: 1
Model: gemini-2.5-flash
Created: 2025-08-31 02:07:00
Duration: 5.245328903198242
Cummulative cost: $2.91e-03

Relation Instances

Section outline

Defines an ‘instance’ of a relation as a specific set of tuples for that relation at a given point in time.
Notes that relations are dynamic, with tuples being inserted, updated, or deleted over time.
Contrasts instance changes with schema changes (adding/deleting attributes), which are less common and more expensive.
Defines the ‘current instance’ as the set of tuples that exist in the relation ‘now’.

# 7
read: Section 2.2.6: Relation Instances (section)
model: pro
rules: presentation short bullet definition

Write in bullet form with nested content.
- Defines an 'instance' of a relation as a specific set of tuples for that relation at a given point in time.
- Notes that relations are dynamic, with tuples being inserted, updated, or deleted over time.
- Contrasts instance changes with schema changes (adding/deleting attributes), which are less common and more expensive.
- Defines the 'current instance' as the set of tuples that exist in the relation 'now'.

Definition: Relation Instance

A set of tuples for a given relation is an instance of that relation.
An instance represents the relation at a point in time.

Relations are not static. Tuples are inserted, updated, and deleted as the data they represent changes. For example, new movies are produced, and information about existing movies may be corrected.
Instance changes are more common than schema changes.

It is less frequent for the schema of a relation to change. Altering the schema by adding or removing attributes is an expensive operation, as it may require rewriting every tuple in the relation.
A database stores the current instance.

A conventional database system maintains only one version of a relation: the set of tuples that exist in the relation “now.” This is called the current instance.

gemini-2.5-pro

Version: 2
Model: gemini-2.5-pro
Created: 2025-08-31 02:12:18
Duration: 15.139781951904297
Cummulative cost: $3.19e-02

Keys of Relations

Section outline

Introduces ‘key constraints’ as a fundamental type of constraint on a relation’s schema.
Defines a ‘key’ as a set of attributes for which no two tuples in any possible instance of the relation can have the same values.
Explains that a key is a constraint on all possible instances, not just the current one.
Introduces ‘artificial keys’ (e.g., employee IDs) created specifically to ensure uniqueness.
Shows the convention of underlining key attributes in the schema: Movies(title, year, length, genre).

# 8
read: Section 2.2.7: Keys of Relations (section)
model: flash
rules: presentation short bullet definition

Revise: Keep the current content, and add the convention of underlining key attributes in the schema: Movies(title, year, length, genre).
Use mathjax to show the underlined attributes of {title, year}.

Key constraints are a fundamental rule imposed upon a relational schema. They are essential for ensuring data integrity by preventing duplicate records and enabling the unique identification of each tuple.

Definition: Key

Given a relation schema $R(A_1, \dots, A_n)$ and a set of attributes $K \subseteq \{A_1, \dots, A_n\}$, $K$ is a key for $R$ if, for any two distinct tuples $t_1, t_2$ in any valid instance $r$ of $R$, it holds that $t_1[K] \neq t_2[K]$.

This implies that a key guarantees uniqueness for tuples across all attributes in the key set. A key is a statement regarding all possible instances of a relation, not merely the data currently present. For instance, in a Movies relation, while title alone might not suffice due to remakes, the combination of {title, year} serves as a plausible key, as two distinct movies are unlikely to share the same title and production year.
Schema Notation

We indicate the attributes forming a key by underlining them in the relation schema. For the Movies relation, with title and year as the key attributes, this would be represented as:

\[\text{Movies}(\underline{\text{title},\text{year}}, \text{length}, \text{genre})\]
Artificial Keys

It is often pragmatic to introduce an attribute whose primary purpose is to serve as a key. Such an attribute is termed an artificial key. These are values, often generated and carefully managed, to ensure absolute uniqueness, thus avoiding reliance on natural attributes that may not always be unique. Common examples include employee IDs, student IDs, and Social Security numbers.

gemini-2.5-flash

Version: 3
Model: gemini-2.5-flash
Created: 2025-08-31 02:21:10
Duration: 3.900268077850342
Cummulative cost: $2.06e-02

An Example Database Schema

Section outline

Presents a complete database schema for movies, building on the running example.
Describes the schema for five relations: Movies, MovieStar, StarsIn, MovieExec, and Studio.
Explains the attributes, data types, and keys for each relation.
Discusses design choices, such as using name as a key for MovieStar (a ‘convenient fiction’) and an artificial key cert# for MovieExec.

# 9
read: Section 2.2.8: An Example Database Schema (section)
model: pro
rules: presentation short bullet

Read the examples in Section 2.2.8.  Create a bullet list with one item for each example
of a relation schema, and a sample instance of that relation.

The relations are:
- Movies
- MovieStar
- StarsIn
- MovieExec
- Studio

Explains the attributes, data types, and keys for each relation.

After the list, add a paragraph to disucss design choices, such as using name as a key for MovieStar (a ‘convenient fiction’) and an artificial key cert# for MovieExec.  Make this into a bullet as well.

Make sure to use mathjax for relation schemas so you can underline the keys.  Place the whole relation schema in a single mathjax expression.  For example $\text{relname}(\underline{\text{key}, \text{key}}, \text{attr}, ...)$.

An example database schema concerning movies illustrates the concepts of the relational model. The schema consists of five relations.

Movies

This relation stores information about films. The key is a composite of title and year, which uniquely identifies a movie, including remakes. Additional attributes include studioName and an integer identifier for the producer, producerC#.

Schema: Movies($\underline{\text{title}}$:string, $\underline{\text{year}}$:integer, length:integer, genre:string, studioName:string, producerC#:integer)

title year length genre studioName producerC#

Star Wars 1977 124 sciFi Fox 12345

Gone With the Wind 1939 231 drama MGM 54321
MovieStar

This relation contains biographical data for movie stars. The star’s name serves as the key, a “convenient fiction” that assumes uniqueness. The schema includes a single character gender and a birthdate of type “date.”

Schema: MovieStar($\underline{\text{name}}$:string, address:string, gender:char, birthdate:date)

name address gender birthdate

Harrison Ford Hollywood, CA M 1942-07-13

Carrie Fisher Beverly Hills, CA F 1956-10-21
StarsIn

This relation connects movies to the stars who appeared in them. The key must consist of all three attributes, as a star may appear in multiple movies in a given year. It links Movies and MovieStar.

Schema: StarsIn($\underline{\text{movieTitle}}$:string, $\underline{\text{movieYear}}$:integer, $\underline{\text{starName}}$:string)

movieTitle movieYear starName

Star Wars 1977 Harrison Ford

Star Wars 1977 Carrie Fisher
MovieExec

This relation holds data about movie executives. To guarantee a unique identifier for each person, an artificial key, cert#, is used. This is necessary because names may not be unique.

Schema: MovieExec(name:string, address:string, $\underline{\text{cert\#}}$:integer, netWorth:integer)

name address cert# netWorth

George Lucas Marin, CA 12345 5400000000

Alan Ladd Jr. Hollywood, CA 67890 100000000
Studio

This relation contains information about movie studios. The studio name is assumed to be unique and therefore acts as the key.

Schema: Studio($\underline{\text{name}}$:string, address:string, presC#:integer)

name address presC#

Fox Hollywood, CA 67890

MGM Beverly Hills, CA 54321
Design Choices for Keys

The design of these relations reveals different approaches to key selection. For the MovieStar and Studio relations, an existing attribute, name, is used as the key based on the assumption that these names are unique. In contrast, the MovieExec relation uses an artificial key, cert#. This is a more robust practice, as it guarantees uniqueness and avoids potential problems with non-unique names. Creating attributes for the sole purpose of serving as keys is a common and important technique in database design.

title	year	length	genre	studioName	producerC#
Star Wars	1977	124	sciFi	Fox	12345
Gone With the Wind	1939	231	drama	MGM	54321

name	address	gender	birthdate
Harrison Ford	Hollywood, CA	M	1942-07-13
Carrie Fisher	Beverly Hills, CA	F	1956-10-21

movieTitle	movieYear	starName
Star Wars	1977	Harrison Ford
Star Wars	1977	Carrie Fisher

name	address	cert#	netWorth
George Lucas	Marin, CA	12345	5400000000
Alan Ladd Jr.	Hollywood, CA	67890	100000000

name	address	presC#
Fox	Hollywood, CA	67890
MGM	Beverly Hills, CA	54321

gemini-2.5-pro

Version: 3
Model: gemini-2.5-pro
Created: 2025-08-31 02:31:05
Duration: 27.65026593208313
Cummulative cost: $9.40e-02

# conclusion
read: the whole section 2.
rules: short presentation

write a conclusion paragraph.

The relational model, as presented, provides a framework for data management, is the fundamental concept of a two-dimensional table, or relation. It will enable the design of high-level languages, such as SQL, to perform data access and modification.

Key elements of this model include relations, defined by schemas comprising attributes and their respective domains, and populated by tuples. The inherent set-based nature of relations makes the order of tuples and attributes irrelevant from the data itself. A crucial constraint in this model is the concept of a key, which ensures uniqueness among tuples.

gemini-2.5-flash

Version: 1
Model: gemini-2.5-flash
Created: 2025-08-31 02:33:22
Duration: 7.329422235488892
Cummulative cost: $3.70e-03