What to submit:
1. 2.
3.
CSET3300 Database Driven Website Lab 4
Constraints and Referential Integrity
The set of create table statements for each task
What type of error you have for the tasks that are supposed to have errors. Include
both the query and the description of the errors. Note the description of errors should
be in plain English. Do not copy and paste the error message from the screen.
If there is cascading changes etc. Describe the change in plain English.
You will enhance a movie-ratings database. In this set of exercises you will declare integrity
constraints on the data, and you will verify that they are being enforced by the underlying database
management system. You will experiment with several types of constraints: key constraints, non-null
constraints, and referential integrity. A SQL file to set up the original schema and data for the movie-
ratings database can be obtained by copying the constraints file to your directory:
cp ~etest/coursefiles/rating_lab.sql
in mysql:
mysql> use <db_name>;
Please refer to the pre assignment lab on usage, and then mysql> source rating_lab.sql;
rating_lab.sql
The source command would run all the queries in the rating_lab.sql file one-by-one. You should be
have the three tables and data inside. You will be using the same data, but modifying the schema to
add constraints. The original schema and data can be loaded as specified in the file into MySQL.
You are advised to type the SQL queries in this file to your SSH Console as directly copy and paste
from a PDF file may generate errors.
Schema:
Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.
Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.
Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.
Unlike most of our other exercises, which are a set of queries to be written individually, this exercise
set involves bigger chunks of work followed by a series of tests. If the constraints are implemented
correctly, the tests will generate or not generate errors as specified. To verify that the referential
integrity policies are implemented correctly, there is a check of the final database state.
Task 1: Constraint Declarations
Modify the three CREATE TABLE statements in the rating_lab.sql to add the following constraints. Key Constraints (Note: none of the following are primary keys)
Non-Null Constraints
Task 2: Load the Database
Task 3: Constraint Enforcement
Each of the following commands should generate an error.
11. update Movie set mID = mID + 1;
12. insert into Movie values (109, ‘Titanic’, 1997, ‘JC’); 13. insert into Reviewer values (201, ‘Ted Codd’);
14. update Rating set rID = 205, mID = 104;
15. insert into Reviewer values (209, null);
None of the following commands should generate errors.
1. mID is a key for Movie
2. (title,year) is a composite key for Movie
3. rID is a key for Reviewer
4. (rID,mID,ratingDate) is a composite key for Rating but with null values allowed
5. Reviewer.name may not be NULL
6. Rating.stars may not be NULL
After creating the three tables using your modified CREATE TABLE statements, you should be able
to load the original data (i.e., execute all of the INSERT statements in the data file) without any
errors.
22. insert into Movie values (109, ‘Titanic’, 2001, null);
23. update Rating set mID = 109;
24. update Movie set year = 1901 where director <> ‘James Cameron’; 25. update Rating set stars = stars – 1;
Task 4: Referential Integrity Declarations
Further modify one or more of your CREATE TABLE statements to include the following referential
integrity constraints and policies.
26. Referential integrity from Rating.rID to Reviewer.rID
Reviewers updated: cascade
Reviewers deleted: set null
All others: error
26. Referential integrity from Rating.mID to Movie.mID
Movies deleted: cascade
All others: error
Task 5: Reload the Database
Task 6: Referential Integrity Enforcement
Each of the following commands should generate an error.
mysql> SET foreign_key_checks = 0; mysql> source rating_lab.sql mysql> SET foreign_key_checks = 1;
27. insert into Rating values (209, 109, 3, ‘2001-01-01’);
Recreate the three tables using your modified CREATE TABLE statements. You should be able to
load the original data (i.e., execute all of the INSERT statements in the data file) without any errors.
Important Note: if you made mistake and want to reload the database by running the
rating_lab.sql file, you need to first turn the foreign key off then on like this:
28. update Rating set rID = 209 where rID = 208; 29. update Rating set mID = mID + 1;
30. update Movie set mID = 109 where mID = 108;
31. update Movie set mID = 109 where mID = 102; 32. update Reviewer set rID = rID + 10;
33. delete from Reviewer where rID > 215;
34. delete from Movie where mID < 105;
Examples shown in class:
Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute. Example:
CREATE TABLE Beers (
name CHAR(20) UNIQUE,
manf CHAR(20)
);
CREATE TABLE Beers (
name CHAR(20) not NULL,
manf CHAR(20)
); The bar and beer together are the key for Sells:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
None of the following commands should generate errors, but they will make additional database
modifications according to the referential-integrity policies.
price REAL,
PRIMARY KEY (bar, beer) /* UNIQUE (bar, beer)*/
);
CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20),
price REAL, FOREIGN KEY(beer)
REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE
);