Chapter 4 - The Relational Model

relational model

- data is logically structured within relations (tables) - each relation has a name and is made up of named attributes (columns) of data - each tuple (row) contains one value per attribute

Relational database

A collection of normalized relations with distinct relation names

Candidate key

A combination of attributes that uniquely identifies a database record without any extraneous data (minimal superkey)

Domain

A domain is the set of allowable values for one or more attributes.

Base relation

A named relation corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database

Relation schema

A named relation defined by a set of attribute and domain name pairs.

Relation

A relation is a table with columns and rows - subset of the Cartesian product of two or more sets

Primary key

Candidate key chosen for use in identification of tuples. A relation must always have a primary key.

Entity integrity

In a base relation, no attribute of a primary key can be null.

Degree

The degree of a relation is the number of attributes it contains.

Multiplicity constraints

The number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship. Multiplicity constrains the way that entities are related. It is a representation of the policies (or business rules) established by the user or enterprise.

Relational database schema

A set of relation schemas, each with a distinct name.

General constraints

Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise

Attribute

An attribute is a named column of a relation.

Referential integrity

If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null. A foreign key links each tuple in the child relation to the tuple in the parent relation containing the matching candidate key value. Referential integrity means that if the foreign key contains a value, that value must refer to an existing tuple in the parent relation.

Composite key

When a key consists of more than one attribute

Tuple

A tuple is a row of a relation. The tuples are called the extension (or state) of a relation, which changes over time. The nearest equivalent in SQL is a "row". SQL rows differ from relational tuples in a few fundamental ways, for example: SQL rows are always ordered lists of values rather than unordered sets; SQL rows permit values that are un-named or have duplicate names.

Cardinality

The cardinality of a relation is the number of tuples it contains.

Properties of Relation

- relation has a name that is distinct from all other relation names in the relational schema - each cell of the relation contains exactly one atomic (single) value - each attribute has a distinct name - the values of an attribute are all from the same domain - each tuple is distinct - the order of attributes has no significance - the order of tuples has no significance, theoretically

Foreign key

A column or combination of columns that is used to establish and enforce a link between the data in two tables - an attribute, or set of attributes, within one relation that is the candidate key of another relation A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Superkey

An attribute, or set of attributes, that uniquely identifies a tuple within a relation - may contain attributes not necessary for unique identification A superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal set of attributes necessary to identify a tuple; this is also called a minimal superkey.

View

The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the database but can be produced upon request by a particular user, at the time of request

When is updating a view allowed?

Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation. Updates are not allowed through views involving multiple base relations. Updates are not allowed through views involving aggregation or grouping operations.

RDBMS

Relational Database Management System - has become the dominant data-processing software in use today,

The two principal rules for the relational model

entity integrity referential integrity

relational keys

one or more attributes that uniquely identifies each tuple in a relation