COSC 304 Midterm #1

Covers topics from Slides 1-6.

DDL

Data Definition Language

DML

Data Manipulation Language

DB External Level

The user’s view of the database that provides the part of the database relevant to the user

DB Conceptual Level

The logical structure of the database that describes what data is stored and its relationships

DB Internal Level

The physical representation of the database

Schema

A description of the structure of the database

Relation

A table with columns and rows

Attribute

A column of a relation

Tuple

A row of a relation

Domain

A set of allowable values for an attribute

Degree of a relation

How many attributes the relation contains

Cardinality of a relation

How many tuples the relation contains

Intension

The structure (schema) of the relation, including its attributes and domains

Extension

The set of tuples currently in the relation

Relation schema

Consists of attributes where each attribute has a name and a domain

Relation instance

A set of n-tuples where each is an element of the domain or is NULL

Superkey

A set of attributes that uniquely identifies a tuple in a relation

Candidate key

A minimal set of attributes that uniquely identifies a tuple in a relation

Primary key

The candidate key selected as the primary identifier for tuples in the relation

Foreign key

A set of attributes in one relation that refers to a key in the same or another relation

Domain constraint

Every value for an attribute must be an element of the attribute’s domain or be NULL

Entity integrity constraint

No attribute of a primary key can be NULL

Referential integrity constraint

A non-NULL foreign key value must match an existing value of the referenced key

σ

Selection operator - filter rows

π

Projection operator - keep only certain columns

×

Cartesian product operator - combine two tables in all possible ways

⋈

Join operator - combine two tables based on a condition

U

Union operator - keep rows in either of two tables

-

Difference operator - keep rows in first table that are not in second

∩

Intersection operator - keep rows that are in both tables

Predicate

The filtering condition of a boolean expression

Union-compatible

1) Both relations must have the same number of attributes
2) Corresponding attributes must have compatible domains/data types

A cartesian product has a total number of R _ S attributes and R _ S tuples.

+, *

Equijoin

A join that only contains the equality operator the the formula

Natural join

Equijoins attributes having the same name in both relations and includes one copy of each named attribute

Left outer join

Returns the normal join result plus unmatched tuples from the left relation, padded with NULL values for attributes from the right relation

Right outer join

Returns the normal join result plus unmatched tuples from the right relation, padded with NULL values for attributes from the left relation

Full outer join

Returns the normal join result plus unmatched tuples from both relations

Semi-join

Returns tuples from the first relation that have at least one matching tuple in the second relation.

Anti-join

Returns tuples from the first relation that have no matching tuple in the second relation

SQL Identifiers

Used to identify objects in the database such as tables, views, and columns

Equity Integrity Constraint

The primary key of a table must contain a unique, non-null value for each row

Referential Integrity Constraint

If a foreign key contains a value that is not NULL, that value must be present in some tuple in the relation containing the referenced key