Unit 2 - 2.The structure of relational models

Example NL Predicate

<a,b,c,d,e> *is a tuple of Student only if* a student with a StudentID of 'a' has the name 'b'; was first Registered as a student in year 'c'; is counselled by the counsellor with CounsellorNo 'd' and studies in Region 'e'.

Null

A *marker* (not a value) indicating the absence of an attribute value in a tuple. Useful because sometimes an attribute might not be applicable to all tuples in a relation *or* the information to complete the attribute value might not be available yet.

Domain

A *named* set of values, with a *common meaning*, from which one or more attributes draw their actual values. Correspond to value sets and have the same type.

Relation (Formal Definition)

A Relation R on domains D[1]...D[n] (which are not necessarily distinct), consists of a *heading/intension* and a *body/extension*.

Posted Attribute Method

A general term used to describe approaches for representing the relationships between relations with foreign-key/primary-key associations. (Note that this wasn't possible for m:n relationships without an additional intersection relation). Advantage is that relationships can be represented by using existing relations.

Relational Model

A model defined in terms of relational theory.

Relations, Attributes & Occurrences

A relation can be thought of as a named structure made up of named attributes (visualised as columns) and a set of occurrences (visualised as rows) with varying attribute values. Note that entities and entity types are very similar to occurrences and relations, but are *referred to differently*.

Relation Vs Table

A relation is an abstract structure whereas a table is a depiction of that structure. It inadvertently adds further features such as row and column or position that have no relevance in a relation.

Relational Structures

A relational model is constructed out of relational structures.

Constraint

A restriction on allowable operations (on data instances) so that a database has integrity

Cardinality

A term that refers to the *number of tuples* in a relation. In table terminology this would be the equivalent to the number of rows in a table, or even the number of occurrences of an entity type (from the E-R Model).

Domain

All values within a relation attribute (represented as a table column) are drawn from the same domain, they are *homogeneous*; of the same kind.

Foreign Key

An attribute (or combination of attributes) in one relation, R[2], whose values are the same as values of a candidate key (usually the primary key) of some relation, R[1] (where R[1] and R[2] are not necessarily distinct.

Relation for relationship Method

As an alternative to the posted attribute method, *all relationships* can be represented by relations, similar to the m:n intersection relation approach. The multiplicity of the relationship can therefore be defined by including the foreign key that refers to them as primary or alternate. Posted attributes (ie. foreign keys) are therefore no longer needed in either of the original relations. Also help avoid redundant null entries, tuples only exist for valid relations.

Qualified Attribute Name

Attribute names must be unique within a relation but could appear in other relations. To resolve ambiguity it is possible to refer to an attribute using *dot notation* which is in the format: RelationName.AttributeName.

Recursive Relationships 1:n

Can be represented with the foreign key / primary key approach as long as the foreign has a different name than the primary key it references - because attribute names within a relation must be unique ! (of course they must be defined on the same domain).

Natural Language Predicate

Can be used to define the *meaning* of a relation, or alternatively - defines the characteristics/conditions of a tuple if it is to belong to the body of the relation.

Set

Collection of *unique elements* in *no order*.

Relational Theory

Conceptual prescription for modelling the representation and manipulation of data (using relations). Can be used to specify what is required independently of implementation, can be used to represent conceptual data models for a relational database. Components are : structure, manipulation and constraints.

Heading/Intension (Formal Definition)

Consists of a *fixed* set of attributes (A[1]..A[n]) such that each attribute A[i] corresponds to exactly one domain D[i]...D[n].

Body/Extension (Formal Definition)

Consists of a variable set of *distinct* tuples each of the form <A[i]:V[i]...A[n]:V[n]> where each V is a value from the domain of the attribute A.

Constraint definition

Describes how to constrain a database so that it only contains valid combinations of data (integrity).

Domain definitions

Domains are named and associated with the value sets which they allow. Often different domains are declared for attributes that we don't want to compare, even of their value sets are the same.

Attribute Uniqueness

Each attribute (depicted as a table column) of a relation is *uniquely named* (so it can be identified).

Candidate Key(s)

Has the properties of uniqueness (no two tuples of the relation have the same value for the key, made up of one or more attributes) and minimality (if key is combination of attributes, no attribute can be removed without breaking the property of uniqueness...ie. all attributes are necessary for uniqueness.). A defined candidate key adds a constraint on what the relation may contain (and therefore meaning).

Key

Identifies a row uniquely. An attribute or combination of attributes.

Inferring the degree of a relationship

If a relationship is 1:n, we would expect the common attribute(s) in a relationship to the be a candidate key at the '1' end (ie. it is unique in that relation) but not at the 'n' end (ie. there can be many tuples with the same value for the common attribute(s)).

Mandatory Participation, 1:1 and null

If the E-R model shows mandatory participation for one of the entity types in a 1:1 relationship but optional participation for the other, it is better that the foreign key is declared for the relation with the mandatory participation so it will never be null !

Declaring a foreign key

In a relational model: foreign key [attribute or combo] references [relation(primary key implied..candidate key would need to be declared here)]. This is establishes a relationship between our relation and the referenced relation via the declared foreign key (an attribute or combo of attributes).

"R for R" 1:1

In this case the relation used to represent the relationship could have its primary key as either of the foreign keys as both will be unique in the relation. So if a man has one wife and vice versa, the "married" relation could have either as the primary key.

"R for R" 1:n

In this case, the relation used to represent the relationship would define it's primary key as the foreign key to the relation on the :n side. So if a tutor has many students, the "tutoring" relation would have the StudentId as primary key as the relation tuples would reference the tutor many times, but each student uniquely.

Predicate

(Difficult to define), roughly seems to be a statement of characteristics. In grammar : The part of the sentence (or clause) which states something about the subject.

Relational Algebra

Manipulation is carried out in a relational model using relational algebra which makes use of a set of *relational operators*.

Venn Diagrams

Or *set diagrams* (used to teach basic set theory), represent sets by including their *elements* within a *circle*. They can represent *set relationships* by, for example, using overlaps for an intersection. In a database, Venn is useful because the sets can contain Tuples without appearing to give them order.

Row & Column Order

Order has no meaning for rows and columns in the table depiction of a relation. Therefore a table representing the same data but with a different order of attributes or occurrences could still refer to the same abstract relation.

Manipulation

Provides a means for describing insertion, deletion, amendment and retrieval. The result of *relation* manipulation is *always* another relation.

Relation Heading / Intension

Refers to the complete set of attribute names (or column headings in the table depiction). Ie. a relation is described by its attributes which, in total, represent its heading. Written in the format RelationName (Attribute[1]....Attribute[n]) with the primary key underlined. The heading is just a label and has no semantic significance.

Candidate Key

Refers to the possible keys that could be used when choosing a primary key. Ideally a single attribute or *the smallest combination of attributes that identifies rows uniquely*.

Relation Extension or Body

Refers to the tuples that exist for a relation at any given instant.

Structural components

Relations (can be depicted a tables), attributes and domains.

Inferring Relationships

Represented only implicitly, relationships are inferred from the attribute (or attributes) that different relations have in *common* (note : attributes may have different name but same domain is required, because only values in the same domain can be compared/related). Note ! it is possible for the inference to be false ! Attributes between relationships defined from the same domain *may not* have a meaningful relationship.

Defining a Domain

Requires the domain to be named and then defined with a range, enumeration or base data type. Meaning is capture because they prescribe certain COMPARISONS and ASSIGNMENTS as being INVALID (ie. they introduce further constraints which, in turn, capture aspects of the meaning of the data in the database). E.g. FirstName = string.

Defining a Relation

Requires the relation to be *named*, *attributes* to be listed with their associated *domains and, finally, the primary key to be defined by specifying the attributes that it is made from. E.g. [relation FlatMate, Name: PersonFirstName, Age:PersonAge, Room:RoomID, primary key (Name, Room)]

Row Uniqueness

Rows within a table (representing a relation) are always unique in some respect (ie. the combination of one or more attribute values)

Representing 1:1 relationships

Similar to foreign-key/primary-key approach used for 1:n, although we must guarantee that the foreign-key is unique in its relation to get 1:1. You can do this by declaring it as an *alternate key*. Ultimately you then end up with a relationship based on two candidate keys referencing each other, ie. 1:1.

Example NL Predicate including Null

Sometimes it is necessary to extend a Natural Language Predicate to include the possibility of Tuples containing Null markers : <a,b,c> is a tuple of Enrolment only if the student with a StudentID of 'a' is enrolled on the course with CourseCode 'b', and is *either* tutored on that course by the tutor with TutorNo 'c' or 'c' is Null.

Primary Key

Specific term for a key that helps us identify a row uniquely, equivalent to the *identifier* of an entity type. A primary key must always exist for a relation. It is worth noting that there may be other possible keys that achieve the same outcome (unique identification), but the default used will be the primary key.

Tuples

Term used to refer to an *row or occurrence* within a relation. This is to help avoid ambiguity with table terminology. A tuple can be referenced by its primary key.

Duplication & Inconsistency

The foreign key/primary key representation of 1:n, 1:n relationships does involve duplication of data which in theory could lead to inconsistency (e.g. if the primary key being referenced was to change, the foreign key might end up referencing nothing).

Degree of a relation

The number of attributes a relation has (helps avoid the use of the word column which is more appropriate for tables).

Intersection Relation

The relation created to represent m:n relationships in a relational model. Has a primary key that consists of the *combination of the primary keys of the two other relations*.

Rows

The rows depicted in a table (depiction of a relation) contain the attribute values for one occurrence within a relation. Sometimes these are written with angled brackets containing the attribute values <Jonathan, 35, English>.

Relational Models & m:n relationships

These are represented by using *another* relation to represent the relationship itself. This is due to the fact that we can't include more than one value in an attribute entry. In an E-R model, it effectively decomposes an m:n into two 1:n relationships with a new entity type representing the relationship inserted in the middle.

Ranges, Enumerations & Base Data Types

To define a domain with a range of values (1..10), an enumeration of possible values (30, 60) or a base data type with a known set (string, integer).

Relational operators

Used to *manipulate* the data within relational structures. [Also used in expressing constraints]

primary key/foreign key mechanism

Used to declare the *existence of a 1:n relationship* where, using a common attribute(s), the 'n' relation can declare the '1' relation as having a candidate key (ie. unique..1) that can be associated with it's own *foreign key* that has attribute(s) on the same domain.

Attribute Value

Value assigned to an attribute of a particular tuple.

Relation Attribute Value Type

Values within a relation attribute (depicted as a table column) are always of the same type.

Comparability

Values within a set, specifically a domain can be compared. Values in DIFFERENT DOMAINS CAN'T BE COMPARED even if the value data type is the same.

Atomic Relation Values

Values within a table row (occurrence of attributes) are never groups of values, always a single value.

Potential Key Values

When choosing a key, the potential values of it's attribute(s) need to be taken into account, not just the values that already exist. I.e could any combination of attribute values in the future break the *unique* aspect of the key ?

Alternate Key(s)

When there are a number of candidate keys and one is chosen as the primary key, the remaining keys are referred to as alternate keys. When defining the relation, these can be listed in the relational model after the primary key definition; alternate key (attribute(s)).

Foreign Key Name

doesn't have to be the same attribute name as the primary key (reference) in the relation to which we are defining a relationship. In some cases it is clearer if it is named differently to emphasise its meaning. However, it does need to be defined on the same domain.

Primary Key

of a relation is one particular key chosen from the candidate keys. A primary key might be chosen on the basis of convenience (e.g. the attribute values are under the control of the organisation).