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
- Given the University Database (
univdb-sqlite.db
), identify the data type of each attribute (field) in the relationtakes
. - identify the primary keys for each of the relations (student, instructor, department, takes and teaches).
- Identify the foreign keys in each of the relations in the previous question.
- 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
andAustralia
). - Add an attribute in each of the
student
andinstructor
relations calledhome country
that is a foreign key referencing thecountry
table. Check this Link for an example. - Write an
SQL
query to remove the record of the student withID
= 12345. - 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? - Now, try the code
INSERT INTO advisor values(55555, 88888);
- Check if the referential integrity is forced or not then switch it and try the previous three exercises again.
- According to the available data, identify the carnality of the relationships between
student
andinstructor
. Is there a better way to represent such relationship (justify your answer).
Useful queries
- Display the schema of a relation using:
PRAGMA table_info(tab_name);
- Check the policy for referential integrity using:
PRAGMA foreign_key_list(tab_name);
- Check if the referential integrity is forced or not using:
PRAGMA foreign_keys;
- Force the referential integrity in SQLite using:
= 1; # use 0 to force no referential integrity PRAGMA foreign_keys
You can also export the database to a .sql
file and check the complete code of the database.