Relational Data Model and Relational Database Constraints

Relational Models represent data as...

a collection of relations

represents data as a table of values

In a relational model, each row is called....

a tuple

A tuple is....

a row in a table that contains data about a specific item in a database table (each row represents an entity)

Domain (D) is...

the set of valid atomic values for each attribute

Atomic means...

each value is indivisible

How can we specify a domain?

data type

range of values

Ex: phone numbers: set of ten digit phone numbers
Ex: GPA: set of possible values of computed grade point averages. Real number 0 ti 4

Domains are more _______ than data types

restrictive

A relation schema R

is made up of a relation name R and a list of attributes

R(A1, A2,....An)

Attribute Ai

name of role played by some domain D in the relation schema R

Degree (arity) of a relation

Number of attributes n of its relation schema

Three formats to represent relation schema

text-based format with arity degree ENTITY(attribute1, attribute 2, etc etc) (must have n number (n-nary) attributes in there)

Text-based format using data types ENTITY(Name: string, ssn, string, GPA, real, etc etc)

Schema Diagram

Relation (or relation state)

set of n-tuples r = {t1, t2,....tm}

Each n tuple t = <v1, v2, ...vn> is an ordered list of n values t (values of attributes at a point in time)
- each value vi, 1 </ i </ n is an element of dom(Ai) or a special NULL value

Based on relation schema

Relation state

r(R)

cartesian products of domains that define R contain all possible tuples

Cardinality

total number of possible unique values in a domain

total number of possible unique values (states) in a relation

Current relation state

relation state at a given time

reflects only the valid tuples that represent a particular state of the real world

Attribute names indicate

different roles, or interpretations, for the domain

Do tuples need to be ordered?

No, indices have an order but relations don't. And relations are a set of tuples

Do duplicates of tuples with the same attribute values exist?

No

A tuple is considered as a set of....

(<attribute>, <value>) pairs

Flat Relational Model

each value in a tuple is atomic

- multi-valued attributes must be represented by separate relations

- composite attributes represented by simple component attributes

- composite and multivalued attributes not allowed

Meanings for NULL values

value unknown

value exists but is not available

attribute does not apply to this tuple (value undefined)

What are constraints?

restrictions on actual values in database state

- derived from rules in the mini-world that the database represents

- also called integrity constraints, which help to insure data integrity of a dtabase

Types of constraints

Key constraints

Domain constraint (null value constraint)

Entity integrity constraint

Referential Integrity Constraint

Key Constraints...

No two tuples can have the same combination of values for all their attributes

- Superkey

- Key

Superkey

any combination of attributes that uniquely identifies a record(tuple)

- may or may not contain unnecessary attributes (for identification)

Every tuple has at least one superkey

Key

minimal superkey of R

contains identifying information for attribute and only those required attributes

Any key is a superkey

Does this work for vice cersa?

No

Any set of attributes that includes a key is a....

superkey

a minimal superkey is a....

key

Candidate Key

relation schema may have more than one key, and in each of the keys is called a candidate keys

can have multiple

Each must be a minimal superkey

Primary Key

designated among candidate keys

underlined attribute

only one primary key (other candidate keys are designated as unique keys)

Domain constraints include

Data types

restricted range

restricted pattern within a data type (like name as alpha only characters with max length)

user-defined data types

Entity Integrity Constraint

No primary key value can be NULL

Foreign key rules to maintain referential integrity

attributes in FK have the same domain as the primary key attributes PK

Value of FK in a tuple t2 of the current state either occurs as a value of PK for some tuple in the current state or is null

Foreign keys are not actual ______ and don't have to be...

keys; unique

Relational Database schema

set of relation schemas S = {R1, R2,...Rm}

Set of integrity constraints

relational database state

set of relation states DB = {r1, r2,...rm}

Each ri, is a state of Ri must satisfy all integrity constraints specified in the set IC

Invalid state

Does not obey all the integrity constraints

Functional dependency constraint

establishes a functional relationship among two sets of attributes X and Y in the same relation

Value of X determines a unique value of Y (everyone in the same area pays the same tax rate)

Retrieval is....

query

Update, Insert, and Delete are....

modification operations that change the states of relations in the database

The delete operation can violate only....

referential integrity if tuple being deleted is referenced by foreign keys from other tuples

Options to not violate referential integrity with the delete operation

1. Restrict - reject decision

2. Set null or set default to referencing attributes that would cause the violation

3. Cascade - propagate deletion by deleting tuples that reference the tuple that is being deleted

Update Operation can cause similar issues as with insert/delete if.....

updating a primary/foreign key

else, then it wouldn't cause any problems