Lecture 1 - Relational Constraints

Question 1

What are relational constraints?

Answer 1

conditions that must hold for all tuples for each relation

i.e. all rows in the table must have a bounded number for each column

Question 2

How many fundamental constraints is there?

Answer 2

3

Question 3

What are the 3 fundamental constraints?

Answer 3

• key constraint (unique tuple identification)
• entity integrity constraint (keys never null)
• Referential integrity constraint (interpretation on relationships)

Question 4

What is a superkey?

Answer 4

a set of attributes containing at least one attribute that uniquely identifies any tuple.

Question 5

What is a candidate key?

Answer 5

is the minimal superkey; i.e., the set with the smallest number of attributes that uniquely identify tuples.

i.e., the removal of any attribute from K results in K’ that is no longer a superkey

Question 6

What is a primary key?

Answer 6

If a relation has several candidate keys, one is chosen arbitrarily to be primary key; the rest candidate keys are called secondary keys.It uniquely identifies each row in the table.

Question 7

Can primary key be null?

Answer 7

NO

Question 8

Can primary key be a set of attributes?

Answer 8

YES, it becomes a composite primary key

Question 9

How are PKs represented in a relation schema?

Answer 9

Underlined

Question 10

What is a REFERENTIAL INTEGRITY CONSTRAINT?

Answer 10

The interpretation of relationships

• foreign keys rules e.g if FK is null, then that FK should not be part of it’s own primary key i.e. not violating the entity integrity constraint

Question 11

What is an FK?

Answer 11

A key that

t1 is referencing relation (contains FK)
t2 is referenced (contains what FK copies)

t1[FK] references t2[PK] → t1[FK] = t2[PK] or t1[FK] = NULL

Question 12

What is Semantic Integrity Constraints?

Answer 12

based on application semantics and
cannot be expressed by the relational model…

• e.g., “the maximum number of hours per employee for all projects
  he/she works on is 56 hrs per week”.
• e.g., ‘each department should control 2 to 5 projects per year .’
• e.g., ‘each department has up to 100 employees’

we require a specific constraint specification language to handle

Question 13

Whether we need absolute consistency in our database depends on …

Answer 13

appllication and its context. To have asolute consistencies we need an optimal processing algorithm, otherwise we can use suboptimal ones that e.g. flush to db after enough data is processed