Chapter 2 - Integrity Constraints

Question 1

What are integrity constraints?

Answer 1

Rules and conditions derived from real-world requirements and specified when defining or creating database schema

Question 2

Why are integrity constraints necessary?

Answer 2

They help to ensure accuracy, consistency and validity of data in the DB.

They ensure that the stored data is faithful to the real-world meaning it represents

Question 3

When are integrity constraints checked?

Answer 3

They are checked when modifications (insert, update, delete) are made to the relations in the DB.
This checking ensures that data modifications adhere to the specified constraints.

Question 4

What happens when DBMS checks the database and sees that integrity constraints are violated?

Answer 4

DBMS may prevent modification or indicate an error

Question 5

When do we consider a database legal?

Answer 5

When integrity constraints hold true for all data.

Question 6

What are the 3 types of integrity constraints?

Answer 6

Domain constraints,
Key constraints,
Referential constraints

Question 7

What are domain constraints? How is this done?

Answer 7

Domain constraints restrict the kind of attributes or values a column can hold in the DB table.

This is done by
1. Specifying data types
2. Specifying range of values: eg. age cannot be less than zero, telephone no cannot contain digit outside 0-9.

Question 8

What are key constraints?

Answer 8

1. We should only have ONE PRIMARY KEY.
2. No two tuples should have the same value for primary key.
3. Attribute in primary key cannot have NULL values.

Question 9

What are relational integrity constraints? (REFERENTIAL CONSTRAINTS)

Answer 9

We must ensure that the reference from one table to another table must be valid.

1. A foreign key must have a matching primary key.
2. The reference key element (foreign key) must either either exist in the referenced table or be NULL

Question 10

How do we preserve Integrity Constraints (ICs) in a relational database and how to ensure that these constraints are maintained.

Answer 10

1. High-level database design: ICs should be modelled and defined at high-level database design stage, such as when creating an ER diagram. This involves specifying constraints like primary keys, foreign keys, unique constraints, and not null constraints using the design phase.
2. Continuous evaluation: ICs should be evaluated continuously, especially during data modification operations like insert, update, delete or alteration of tables.
3. SQL support for ICs: SQL provides various constructs and constraints for defining and enforcing ICs. Some examples include NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY REFERENCES

Question 11

Can we infer that an IC is true by looking at an instace?

Answer 11

NO. ICs are statements about all possible instances of a database, not just a single instance.
While you can use SQL constraints to enforce ICs, you cannot infer the truth of an IC solely by looking at a single instance of the DB. ICs are constraints that apply to the ENTIRE DATASET.

Question 12

What do we communicate to DBMS?

Answer 12

1. Defining the structure of the database. This includes creating, altering, dropping, truncating, commenting on, or renaming database objects such as tables, indexes, views, and more.
2. Manipulating data.
   You communicate with the DBMS to manipulate data within the database. Common data manipulation operations include SELECT (retrieving data), INSERT (adding new data), UPDATE (modifying existing data), DELETE (removing data), and more

Question 13

How do we communicate with DBMS?

Answer 13

1. Relational Algebra
2. SQL