Intro to database Week 3 (midterm)

Relations

using math & logic to represent data models

how do you distinguish rows (tuple) in a table (informal)

by using keys

Domain of values (formal)

set of possible values for a certain attribute R(A1,A2,…,An)

What is the informal terms of a table/relation

Table

Column header

All possible column values or data type

Row

Table definition

Populated table

What is the formal terms of a table/relation

Relation

Attribute

Domain

Tuple

Schema

State of the Relation

What are the three main characteristics of Relations

1. Tuples are NOT ORDERED because relation is a SET of tuples

2. Attributes in a tuple ARE ORDERED

3. Values in tuple are ATOMIC null = unknown

What are the three types of key constraints

Super key: set of attributes where no two tuples will have same super key

Candidate key: minimal super key where removal of an attribute from it does not make another key

Primary key: the actual candidate key chosen

Entity integrity

the primary key attributes of each relation cannot have null values

Referential Integrity

when two relations have a relationship however the foreign key can only be an existing primary key of another relation or NULL

What are the two Relations states

Relational database state: a union of all the individual relation states at a particular time

Transaction: group of UPDATE operations

What are the 3 main ways to modify relations

INSERT: insert new tuple to relation

DELETE: remove old tuple

UPDATE: change attribute of tuple of relation

Propagation

Changes that occur automatically to maintain valid constraints

How do you handle violation of constraints

RESTRICT (REJECT): cancel operation that causes violation

CASCADE: propagate the operation (in DELETE, we delete referencing tuples)

SET NULL: set foreign keys of referencing tuples to NULL