Integrity constraints and database design

Integrity constraints can be arbitrary predicates that ensure the consistency and the validity of the values in a database. They act as guards against any accidental damage that could happen to the database. During this lab, you will practice using a set of integrity constraints that have been studied during the lecture.

Exercises

  1. Given the University Database (univdb-sqlite.db), identify the data type of each attribute (field) in the relation takes.
  2. identify the primary keys for each of the relations (student, instructor, department, takes and teaches).
  3. Identify the foreign keys in each of the relations in the previous question.
  4. Create a table country(ID, name, continent), where the values of continent is restricted to take a value of (Asia, Africa, Europe, North America, South America and Australia).
  5. Add an attribute in each of the student and instructor relations called home country that is a foreign key referencing the country table. Check this Link for an example.
  6. Write an SQL query to remove the record of the student with ID = 12345.
  7. If you know that student with ID = 12345 is referenced in the advisor relation, how can we delete the record without receiving an error message?
  8. Now, try the code INSERT INTO advisor values(55555, 88888);
  9. Check if the referential integrity is forced or not then switch it and try the previous three exercises again.
  10. According to the available data, identify the carnality of the relationships between student and instructor. Is there a better way to represent such relationship (justify your answer).

Useful queries

  1. Display the schema of a relation using:
PRAGMA table_info(tab_name);
  1. Check the policy for referential integrity using:
PRAGMA foreign_key_list(tab_name);
  1. Check if the referential integrity is forced or not using:
PRAGMA foreign_keys;
  1. Force the referential integrity in SQLite using:
PRAGMA foreign_keys = 1;     # use 0 to force no referential integrity

You can also export the database to a .sql file and check the complete code of the database.