Module 2- Relational Databases

Predicate Logic

Used extensively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false.

Set Theory

A part of mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model

Tuple

In the relational model, a table row

Attribute Domain

In data modeling, the construct used to organize and describe an attribute’s set of possible values.

Primary Key

In the relational model, an identifier composed of one or more attributes that uniquely identifies a row. Also, a candidate key selected as a unique entity identifier.

Key

One or more attributes that determine other attributes.

Determination

The role of a key. In the context of a database table, the statement “A determines B” indicates that knowing the value of attribute A means that the value of attribute B can be looked up.

Functional Dependence

Within a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of attribute A determines exactly one value of attribute B. The relationship “B is dependent on A” is equivalent to “A determines B” and is written as A → B.

Determinate

Any attribute in a specific row whose value directly determines

other values in that row.

Dependent

An attribute whose value is determined by another attribute.

Full Functional Dependence

A condition in which an attribute is functionally dependent on a composite key but not on any subset of the key

Composite Key

A multiple-attribute key

Key Attribute

An attribute that is part of a primary key.

Superkey

An attribute or attributes that uniquely identify each entity in a table

Candidate Key

A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey.

Entity Integrity

The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values.

Null

The absence of an attribute value.

Foreign Key

An attribute or attributes in one table whose values must match the primary key in another table or whose values must be null.

Referential Integrity

A condition by which a dependent table’s foreign key must have either a null entry or a matching entry in the related table.

Secondary Key

A key used strictly for data retrieval purposes. For example, customers are not likely to know their customer number (primary key), but the combination of last name, first name, middle initial, and telephone number will probably match the appropriate table row. 

Purpose of Entity Integrity

Each row will have a unique identity, and foreign key values can properly reference primary key values.

Purpose of Referntial Integrity

It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry; the enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.

Flags

Special codes implemented by designers to trigger a required response, alert end users to specified conditions, or encode values. Flags may be used to prevent nulls by bringing attention to the absence of a value in a table.

Relational Algebra

A set of mathematical principles that form the basis for manipulating relational table contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

Relvar

Short for relation variable, a variable that holds a relation. A relvar is a container (variable) for holding relation data, not the relation itself.

Closure

A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations.

SELECT

In relational algebra, an operator used to select a subset of rows. Also known as RESTRICT

PROJECT

In relational algebra, an operator used to select a subset of columns.

UNION

In relational algebra, an operator used to merge (append) two tables into a new table, dropping the duplicate rows. The tables must be union-compatible.

Union-Compatible

Two or more tables that have the same number of columns and the corresponding columns have compatible domains.

INTERSECT

In relational algebra, an operator used to yield only the rows that are common to two union-compatible tables.

DIFFERENCE

In relational algebra, an operator used to yield all rows from one table that are not found in another union-compatible table.

PRODUCT

In relational algebra, an operator used to yield all possible pairs of rows from two tables. Also known as the Cartesian product

JOIN

In relational algebra, a type of operator used to yield rows from two tables based on criteria. There are many types of joins, such as natural join, theta join, equijoin, and outer join.

Natural Join

A relational operation that yields a new table composed of only the rows with common values in their common attribute(s).

Join Columns

Columns that are used in the criteria of join operations. The join columns generally share similar values.

Equijoin

A join operator that links tables based on an equality condition that compares specified columns of the tables.

Theta Join

A join operator that links tables using an inequality comparison operator (<,>, <=, >=) in the join condition.

Inner Join

A join operation in which only rows that meet a given criterion are selected. The criterion can be an equality condition (natural join or equijoin) or an inequality condition (theta join). The most commonly used type of join.

Outer Join

A join operation that produces a table in which all unmatched pairs are retained; unmatched values in the related table are left null

Lefter Outer Join

A join operation that yields all the rows in the left table, including those that have no matching values in the other table

Right Outer Join

A join operation that yields all of the rows in the right table, including the ones with no matching values in the other table.

DIVIDE

In relational algebra, an operator that answers queries about one set of data being associated with all values of data in another set of data.

Data Dictionary

A DBMS component that stores metadata—data about data. Thus, the data dictionary contains the data definition as well as their characteristics and relationships. A data dictionary may also include data that are external to the DBMS. Also known as an information resource dictionary.

System Catalog

A detailed system data dictionary that describes all objects in a database

Homonym

The use of the same name to label different attributes. Homonyms generally should be avoided. 

Synonym

The use of different names to identify the smæ object, such as an entity, an attribute, or a relationship; synonyms should generally be avoided

Composite Entity

An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity’s primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity or associative entity

Linking Table

In the relational model, a table that implements an M:M relationship

Index

An ordered array of index key values and row ID values (pointers). Indexes are generally used to speed up and facilitate data retrieval. Also known as an index key.

Unique Key

An index in which the index key can have only one associated pointer value (row).

Information (Dr. Codd’s 12 Relational Database Rules)

All information in a relational database must be logically represented as column values in rows within tables.

Guaranteed Access (Dr. Codd’s 12 Relational Database Rules)

Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name.

Systematic Treatment of Nulls (Dr. Codd’s 12 Relational Database Rules)

Nulls must be represented and treated in a systematic way, independent of data type.

Dynamic online catalog based on the relational model (Dr. Codd’s 12 relational database rules)

The metadata must be stored and managed as ordinary data—that is, in tables within the database; such data must be available to authorized users using the standard database relational language.

Comprehensive data sublangauge (Dr. Codd’s 12 relational database rules)

The relational database may support many languages; however, it must support one well-defined, declarative language as well as data definition, view definition, data manipulation (interactive and by program), integrity constraints, authorization, and transaction management (begin, commit, and rollback).

View updating (Dr. Codd’s 12 relational database rules)

Any view that is theoretically updatable must be updatable through the system.

High-level insert, update, and delete (Dr. Codd’s 12 relational database rules)

The database must support set-level inserts, updates, and deletes.

Physical data independence (Dr. Codd’s 12 relational database rules)

Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed.

Logical data independence (Dr. Codd’s 12 relational database rules)

Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns).

Integrity Independence (Dr. Codd’s 12 relational database rules)

All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level.

Distribution independence (Dr. Codd’s 12 relational database rules)

The end users and application programs are unaware of and unaffected by the data location (distributed vs. local databases).

Nonsubversion (Dr. Codd’s 12 relational databse rules)

If the system supports low-level access to the data, users must not be allowed to bypass the integrity rules of the database.

Rule zero (Dr. Codd’s 12 relational database rules)

All preceding rules are based on the notion that to be considered relational, a database must use its relational facilities exclusively for management.

Relational Data Model

Consists of tables and methods relating the tables through key fields

Three components of relational data model

data structure, key relationships, relational database operators

Tables (relations)

A two-dimensional structure of data about an entity

Entity

The thing for which data will be collected

Relationships

An association between instances of one or more entities

Columns

Attributes of interest for an entity

Rows

An instance or occurrence of an entity

Tables

A logical representation of data consisting of columns and rows

Requirements of tables

• All values in a column must have the same data format

• Each row/column intersection is a data value (key)

• Each table must have a column that uniquely identifies each row

Table properties

• Entries in columns are single-valued

  • No repeated groups

• Entries in columns are of the same domain

  • Size, data type, range, etc.

• Each row is unique →must occur since each row has a primary key

• Order of rows/columns are not significant

Two functions of keys

1. Uniquely identify a entity in a table

2. Establish relationships between tables

What determines non-key attributes?

Primary key → the value of each attribute should be functionally dependent on the primary key