/* examples of creating domain, primary key, entity integrity, and
referential integrity violations using insert, delete and update
operations */

/* domain constraint, insert */
INSERT INTO section (sectionID, courseNumber, semester, year, instructor)
VALUES (1, 2400, 'Fall', 2008, 'O''Keefe');

/* entity integrity, insert */
INSERT INTO student (name, studentNumber, class, major)
VALUES ('O''Keefe', NULL, 1999, 'maths');

/* primary key integrity, insert */
INSERT INTO gradeReport (studentNumber, sectionID, grade)
VALUES (u3996194, 1, 'fail');
INSERT INTO gradeReport (studentNumber, sectionID, grade)
VALUES (u3996194, 1, 'HD');

/* referential integrity, insert */
INSERT INTO gradeReport (studentNumber, sectionID, grade)
VALUES (u3996194, 2, 'HD');

/* referential integrity, delete */
DELETE FROM course WHERE courseNumber=1;

/* domain constraint, update */
UPDATE section 
SET semester = 'Michaelmas' 
WHERE courseNumber in (
	SELECT courseNumber 
	FROM course 
	WHERE courseName = 'Logic');

/* primary key, update */
UPDATE student 
SET studentNumber=200001
WHERE name='O''Keefe';

/* entity integrity, update */
UPDATE student 
SET studentNumber=NULL
WHERE name='O''Keefe';

/* referential integrity, update (target) */
UPDATE course
SET courseNumber=2
WHERE courseName='Sheaf Theory';    


/* referential integrity, update (source) */
UPDATE section
SET courseNumber=2
WHERE courseName='Sheaf Theory';