Notes 4, Chpt 3 Pt 3 - 422: Integrity Constraints

Give an example of Foreign Key constraint?

A Transcript refers to Coursecode, which is referenced in Course. Professor references Department ID which refers to Department.

Key Constraint

A key constraint is a sequence of attributes A1,..., An (n=1 possible) of a relation schema, S.

Key Constraint has 2 important properties. Name them.

A relation instance s of S satisfies the key constraint iff at most one row in s can contain a particular set of values, a1 through an, for the attributes A1,...,An and Minimality: no subset of A1 through An is a key constraint

This is a special case of referential integrity: A2 must be a ______________ key of R2.

A2 must be a candidate key of R2

R1 of A1 through A n references R2, B1 through B n which means Ai and Bi must have same ___________s -although not necessarily the same names.

Domains. R1 of A1 through A n references R2, B1 through B n which means Ai and Bi must have same domains - although not necessarily the same names.

If we have a table of employees and managers, every manager is also an ___________ and has a ___________ row in Table Employee.

Employee, Unique. Every manager is also an employee and hence has a unique row in Employee.

True or False? If no row exists in R2, there is no violation of referential integrity.

False. - If no row exists in R2 then we have a violation of referential integrity.

True or False? Rows of R2 need to be referenced - ex. must be symmetric.

False. - Not all rows of R2 need to be referenced: relationship is not symmetric. Ex: some course might not be taught, but listed in catalog.

True or False? R1 and R2 need to be distinct.

False. Example: Employee and Manager ID, manager is also employee.

True or False? A primary key can be null.

False. Primary Key must have a value.

True or False? A Relation can have several keys?

False. Relation can have several keys.

True or False? Target attributes form a candidate key in Transcript - Ex: Student ID missing.

False. Target attributes do not form a candidate key in Transcript - Ex: Student ID missing.

The Value of a ____________ key can be null.

Foreign. Value of a foreign key might not be specified.

Inclusion Dependency is a ___________ Integrity Constraint that is not a ___________ key constraint.

Inclusion Dependency is a Referential Integrity Constraint that is not a Foreign key constraint. Referential Integrity, not a Foreign Key Constraint.

Define Semantic Constraint.

Involves meaning of attributes, ex: cannot register for more than 18 credits

What are 4 different types of Keys possible in a Relation?

Minimal Key, Superkey, Primary Key and Candidate Key.

What is an example candidate key?

Name and Address for Student.

Define Minimal Key.

No subset of a key is a key itself. Ex: ID and Name is not a key of Student.

Foreign Key Constraint is concerned with _____________ Integrity.

Referential Integrity pertains to Foreign Key constraint.

Foreign Key Constraint Definition:

Referential integrity: Item named in one relation must refer to tuples that describe that item in another relation.

Superkey

Set of attributes containing key. Ex: ID and Name is a superkey of Student.

Define Key.

Set of attributes mentioned in a key constraint such as ID, CourseCode

Kinds of Integrity Constraints

Static and Dynamic. Static restricts the legal states of database and Dynamic puts limitation on sequences of database states.

Define Syntactic Constraint.

Structural, all values in a column must be unique.

Give an example of Inclusion Dependency using Teaching and Transcript.

Teaching - CourseCode, Semester - references Transcript - CourseCode, Semester - allows no empty classes.

True or False? Every Relation has a Key?

True

True or False? Attribute A1 is a foreign key of R1 referring to attribute A2 in R2, if whenever there is a value v of A1, there is a tuple of R2 in which A2 has value v, and A2 is a key of R2

True.

True or False? R1 of A1 through A n references R2, B1 through B n which means B1 through B n must be a Candidate Key of R2.

True.

True or False? There is a special case of referential integrity,

True.

True or False? A Foreign key might consist of several columns.

True. CourseCode, Semester of Transcript references CourseCode and Semester of Teaching.

True or False? The Value of a foreign key might not be specified.

True. Dept ID column of some professor might be null if professor is not assigned to department.

True or False? There is no simple enforcement mechanism for inclusion dependencies in SQL.

True. It requires assertions that we will learn later.

True or False? Names of the attributes A1 and A2 need not be the same.

True. With tables: ProfId attribute of Teaching references Id attribute of Professor

Constraint Checking has 3 parts:

• Automatically checked by DBMS • Protects database from errors • Enforces enterprise rules