Relational Databases

What is a Relation?

set of rows/tuples with a description

made up of two parts - instance and relationship schema

What is an Instance?

table with rows and columns

#rows = cardinality, #fields/attributes = degree/arity

What is a Relationship Schema (RS)?

specifies the relations name and type (domain) of each column

What is an example of the format of a RS?

Student(sid: INT, name: TEXT, login: TEXT, age: INT, gpa: DOUBLE)

lowercase - name

capital - type/domain

What is an Integrity Constraint (IC)?

condition that must be true for any database instance

What is a Legal Instance of a Relation?

satisfies all specified ICs

What is a Domain Constriant?

in any database instance, a value of the attribute must be an element of the attribute’s domain as set in the RS

What is an Entity Integrity Constraint?

a key value can’t be duplicated or left empty in any instance

What is Integrity of Data?

the state of data in which data obeys the constraints set by the DBA

When is Referential Integrity achieved?

if all foreign key constraints are enforced

What is a Foreign Key?

a logical pointer

peripheral attribute that establishes referential integrity between entity sets

How do you create a Foreign Key?

either:

• import the primary key from one table to the other or,

• create a new table that holds the primary keys of the tables in relation

What are Foreign Key Requirements?

• must have the same name and domain as the referencing relation

• related entries must have the same values

What does NO ACTION do?

delete/update is rejected

default

What does CASCADE do?

also deletes all tuples that refer to deleted tuple

What does SET NULL/ SET DEFAULT do?

sets foreign key value of referencing tuple