CSCD 327

Relation

2 dimensional table in the Relational Model of Data

Candidate Key

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key

A Primary Key is a column or a combination of columns that uniquely identify a record.

Super Key

A set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.

Key Constraint

A set of attributes that are used to ensure that no two tuples in a relation instance have the same value

Table

A stored relation that both exists in a database and can be queried.

users

AD = DML DBA = DDL & DML

Temporary Tables

Are constructed by the SQL language processor when it performs its job of executing queries and data modifications. These relations are then thrown away and not stored.

SQL primitive data types

CHAR(n) VARCHAR(n) BIT(n) BIT VARYING(n) BOOLEAN INT SHORTINT FLOAT DOUBLE PRECISION DECIMAL(n,d) DATE TIME

DDL

Data Definition Language Used for declaring Database Schemas

DML

Data Manipulation Language Used for querying and modifying the database

Relation Instance

Describes the set of tuples in the relation "now"

Set Difference

Given two relations r & s, set difference returns all the tuples in r that are not in s.

Cardinality

Represents the number of tuples in a relation

Union

Requires two operands(relations), r & s r&s must be compatible (r&s have the same degree) each matching pair must have compatible data types Output Schema = r discards duplicates

Schema

The name of a relation and the set of it's attributes. Name(attribute1, attribute2)

Tuple

The rows of a relation, excluding the header.

Relational Algebra

Used to formalize a query. Is not a programming language.

View

a relation that is defined by computation. Views are not stored, but are constructed, in whole or in part, when needed.

Attribute

columns of a relation

Domain

data represented by a particular elementary type

Project

returns a specified list of tuples from input r Output Schema = changed discards duplicates

Select

returns a subset of tuples from input r Output Schema = r returns all valid tuples for all attributes

Relational Algebra Operations

select = 'sigma' projection = 'pi' union = 'U' set difference = '-' Cartesian product = 'X' rename = 'p' intersection natural join division assignment