Chapter 2 Relational Models (Gemini)

Slide 31 och framåt är från slide-presentationen - det som inne var med i begreppen från boken.

Table

A two-dimensional grid of rows and columns used to store data. In the relational model, a table corresponds mathematically to a relation.

Relation

A formal mathematical term for a table, consisting of a set of unordered tuples (rows) that all share the same attributes (columns).

Tuple

A single row in a relation representing a specific, individual record or entity instance.

Attribute

A single column in a relation representing a specific property, characteristic, or field of the relation.

Relation instance

The actual collection of tuples present in a relation at a specific point in time (the current content of the table).

Domain

The predefined set of all allowable values for a given attribute (e.g., integers, dates, or strings of a certain length).

Atomic domain

A domain where each individual value is indivisible or simple (e.g., a single number or text string, rather than a list or nested structure).

Database schema

The overall logical blueprint or design of the entire database.

Database instance

A snapshot of all the data contained in the database at a specific moment in time.

Relation schema

The logical blueprint of a single relation, defined by its name and a list of its attributes (e.g., Student(ID, Name, Department)).

Keys

Attributes or sets of attributes used to uniquely identify tuples or establish relationships between tables.

Superkey

A set of one or more attributes that, taken collectively, allow us to uniquely identify every tuple in a relation.

Candidate key

A minimal superkey—a superkey from which no proper subset is also a superkey (meaning no redundant attributes are included).

Primary key

The specific candidate key chosen by the database designer as the principal means of identifying tuples within a relation.

Primary key constraints

Rules stating that primary key attributes must contain unique values for every tuple and cannot contain null values.

Foreign-key constraint

A rule ensuring that values in a referencing attribute match the primary key values in a referenced table, maintaining structural consistency.

Referencing vs Referenced relation (Foreign key constraint)

• Referencing relation: The table that contains the foreign key pointing to another table.

• Referenced relation: The table containing the primary key being pointed to.

Referential integrity constraint

A general rule ensuring that relationships between tables remain valid (e.g., you cannot assign an invalid department ID to a student).

Schema diagram

A graphical illustration showing database relations, their attributes, primary keys, and foreign-key relationships.

Query Language Types (3 st)

• Imperative: Query languages where the user specifies how to compute the result through an explicit, step-by-step procedural sequence.

• Functional: Query languages where queries are constructed by applying functions to data collections or other functions.

• Declarative (Nonprocedural): Query languages where the user specifies what data is desired without needing to provide the step-by-step execution path (e.g., SQL).

Relational algebra & Relational-algebra expression

• Relational algebra: A procedural query language that takes one or more relations as input and returns a new relation as output using specific mathematical operations.

• Relational-algebra expression: A combination of relation names, constants, and relational-algebra operators that evaluates to a relation.

Select ($\sigma$)

Filters rows (tuples) that satisfy a specified predicate condition. ($\sigma_{\text{condition}}(\text{Relation})$)

Project ($\Pi$)

Filters columns (attributes) and removes any resulting duplicate rows. ($\Pi_{\text{attributes}}(\text{Relation})$)

Cartesian product ($\times$)

Combines every tuple of the first relation with every tuple of the second relation. ($R \times S$)

Join ($\bowtie$)

Combines a Cartesian product with a selection condition into a single operation, linking related tuples from two relations based on matching attributes.

Union ($\cup$)

Combines tuples from two union-compatible relations, automatically eliminating duplicates. ($R \cup S$)

Set difference ($-$)

Returns tuples that are present in the first relation but not in the second. ($R - S$)

Set intersection ($\cap$)

Returns only the tuples that exist in both relations. ($R \cap S$)

Assignment ($\leftarrow$)

Assigns the result of a relational-algebra expression to a temporary relation variable.

Rename ($\rho$)

Renames a relation or its attributes to avoid naming conflicts. ($\rho_{S(A, B)}(R)$)

Naive set Theory foundations: (union, intersection, set difference, and Cartesian products)

The slides establish set theory as the mathematical basis for the relational model. They review set definitions, cardinality, subsets, and fundamental set operations (union, intersection, set difference, and Cartesian products) along with their commutative and associative properties.

Intensional vs. Extensional Data Definitions

The course explicitly contrasts intensional data definitions (the schema and specifications of what data we want, such as DDL statements and queries) with extensional data definitions (the data itself stored in relation instances).

Comprehensive Constraint Categorization (3 cathegories)

• Implicit (Data Model) Constraints: Inherent rules of the relational model, such as the prohibition of duplicate tuples and lack of implicit ordering.

• Explicit (Schema) Constraints: Schema-based rules defined via DDL, including domain, key, entity integrity, and referential integrity constraints.

• Application Constraints: Complex business rules that cannot be derived from the schema alone (e.g., maximum credit limits), which are enforced via application code, database assertions, or triggers.

Integrity Constraint Enforcement Strategies (INSERT, UPDATE, and DELETE operations)

The slides provide detailed strategies for how a DBMS maintains integrity during INSERT, UPDATE, and DELETE operations. This includes specific handling mechanisms like rejecting violations, setting foreign keys to NULL, and executing Cascade Deletes to automatically remove referencing child tuples.

Outer joins

Left, Right, and Full Outer Joins that preserve non-matching tuples by padding missing values with NULL.

Grouped Aggregation

Performing aggregate functions (Sum, Average, Min, Max, Count) grouped by specific attributes directly within relational algebra.

Query Trees

The internal tree data structures used by database management systems for bottom-up query evaluation, execution, and optimization.