1. Индивидуальное задание

Результат выборки:
LASTNAME FIRSTNAME 3
Гладких Татьяна 2 Панченко Владимир 4Порошин Сергей 4 Солощук Михаил 6Фастовский Эдуард 4 5 record(s) selected.


7.3 Проверка ограничений целостности

Для проверки триггера выполним запросы, первый будет вставлять и изменять записи в таблице лекций на значение дня недели больше 7.

UPDATE Lecture SET DayOfWeekNumber=8
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "The DayOfWeekNumberis incorrect". SQLSTATE=75001
INSERT INTO LECTURE(GROUPNAME, LECTURERID, TIMEID, WEEKTYPEID, DISCIPLINENUMBER, DAYOFWEEKNUMBER, AUDITORIUM, BUILDINGNAME)
VALUES('КИТ-64', 27, 4, 1, '54354559', 9, '202', 'ГАК');
------------------------------ Command Entered ------------------------------
INSERT INTO LECTURE(GROUPNAME, LECTURERID, TIMEID, WEEKTYPEID, DISCIPLINENUMBER, DAYOFWEEKNUMBER, AUDITORIUM, BUILDINGNAME)
VALUES('КИТ-64', 27, 4, 1, '54354559', 9, '202', 'ГАК') ;
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "The DayOfWeekNumber is incorrect". SQLSTATE=75001


Приложения


Приложение А. SQL-скрипты создания таблиц базы данных

CREATE TABLE DB2ADMIN.DISCIPLINE (

DISCIPLINENUMBER VARCHAR (20) NOT NULL,
NAME VARCHAR (250) NOT NULL ,
HOURS SMALLINT NOT NULL ,
PRIMARY KEY (DISCIPLINENUMBER)
CREATE TABLE DB2ADMIN.WEEKTYPE (
WEEKTYPEID SMALLINT GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
NAME VARCHAR (100) NOT NULL ,
PRIMARY KEY (WEEKTYPEID)
CREATE TABLE DB2ADMIN.TIME (
TIMEID SMALLINT GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
HOUR SMALLINT NOT NULL,
MINUTE SMALLINT NOT NULL,
PRIMARY KEY (TIMEID)
CREATE TABLE DB2ADMIN.CHAIRTYPE (
CHAIRTYPEID SMALLINT GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
NAME VARCHAR (250) NOT NULL,
PRIMARY KEY (CHAIRTYPEID)
CREATE TABLE DB2ADMIN.UNIVERSITY (
UNIVERSITYID INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
NAME VARCHAR (100) NOT NULL,
ACCREDITATIONLEVEL SMALLINT NOT NULL,
CREATIONDATE DATE NOT NULL ,
ADDRESS VARCHAR (250),
PHONE VARCHAR (40),
PRIMARY KEY (UNIVERSITYID)
CREATE TABLE DB2ADMIN.DEPARTMENT (
DEPARTMENTID INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
UNIVERSITYID INTEGER NOT NULL ,
NAME VARCHAR (250) NOT NULL ,
CREATIONDATE DATE NOT NULL ,
PRIMARY KEY (DEPARTMENTID) ,
FOREIGN KEY (UNIVERSITYID) REFERENCES DB2ADMIN.UNIVERSITY (UNIVERSITYID)
CREATE TABLE DB2ADMIN.CHAIR (
CHAIRID INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
CHAIRTYPEID SMALLINT NOT NULL ,
DEPARTMENTID INTEGER ,
NAME VARCHAR (250) NOT NULL ,
CREATIONDATE DATE NOT NULL ,
PRIMARY KEY (CHAIRID) ,
FOREIGN KEY (CHAIRTYPEID) REFERENCES DB2ADMIN.CHAIRTYPE (CHAIRTYPEID) ,
FOREIGN KEY (DEPARTMENTID) REFERENCES DB2ADMIN.DEPARTMENT (DEPARTMENTID)
CREATE TABLE DB2ADMIN.LECTURER (
LECTURERID INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
CHAIRID INTEGER NOT NULL ,
LASTNAME VARCHAR (70) NOT NULL ,
FIRSTNAME VARCHAR (70) NOT NULL ,
MIDDLENAME VARCHAR (70) NOT NULL ,
ACADEMICDEGREENAME VARCHAR (120) NOT NULL ,
POST VARCHAR (250) NOT NULL ,
PRIMARY KEY (LECTURERID) ,
FOREIGN KEY (CHAIRID) REFERENCES DB2ADMIN.CHAIR (CHAIRID)
CREATE TABLE DB2ADMIN.LECTURE (
GROUPNAME VARCHAR (20) NOT NULL ,
LECTURERID INTEGER NOT NULL ,
DISCIPLINENUMBER VARCHAR (20) NOT NULL ,
TIMEID SMALLINT NOT NULL ,
WEEKTYPEID SMALLINT NOT NULL ,
DAYOFWEEKNUMBER SMALLINT NOT NULL ,
AUDITORIUM VARCHAR (20) NOT NULL ,
BUILDINGNAME VARCHAR (20) NOT NULL ,
PRIMARY KEY (GROUPNAME, LECTURERID, TIMEID, WEEKTYPEID, DAYOFWEEKNUMBER) ,
FOREIGN KEY (LECTURERID) REFERENCES DB2ADMIN.LECTURER (LECTURERID) ,
FOREIGN KEY (DISCIPLINENUMBER) REFERENCES DB2ADMIN.DISCIPLINE (DISCIPLINENUMBER) ,
FOREIGN KEY (TIMEID) REFERENCES DB2ADMIN.TIME (TIMEID) ,
FOREIGN KEY (WEEKTYPEID) REFERENCES DB2ADMIN.WEEKTYPE (WEEKTYPEID)
инфологический процедурный база целостность