Сгенерированная Case Studio SQL-программа создания таблиц базы данных для сервера Oracle
/* Created 02.02.2006 Modified 03.02.2006 Project Kontingent Model Students Company AGTU Author Groshev Version 2006.1 Database Oracle 9i */ Create table "Student" ( "Nz" Char(7) NOT NULL , "Fio" Char(45), "date_p" Date, "n_fclt" Decimal(2,0) NOT NULL , "n_spect" Char(9) NOT NULL , "kurs" Decimal(1,0), "n_grup" Char(10), "n_pasp" Char(10)) / Create table "Ocenki" ( "semestr" Decimal(2,0), "n_predm" Decimal(2,0) NOT NULL , "ball" Char(1), "data_b" Date, "Prepod" Char(45), "Nz" Char(7) NOT NULL ) / Create table "Predmet" ( "n_predm" Decimal(2,0) NOT NULL , "name_p" Char(120)) / Create table "FCLT" ( "n_fclt" Decimal(2,0) NOT NULL , "name_f" Char(120)) / Create table "SPECT" ( "n_spect" Char(9) NOT NULL , "name_S" Char(120)) / Alter table "Student" add primary key ("Nz") / Alter table "Predmet" add primary key ("n_predm") / Alter table "FCLT" add primary key ("n_fclt") / Alter table "SPECT" add primary key ("n_spect") / Alter table "Ocenki" add foreign key ("Nz") references "Student" ("Nz") on delete cascade / Alter table "Ocenki" add foreign key ("n_predm") references "Predmet" ("n_predm") on delete cascade / Alter table "Student" add foreign key ("n_fclt") references "FCLT" ("n_fclt") on delete cascade / Alter table "Student" add foreign key ("n_spect") references "SPECT" ("n_spect") on delete cascade / -- Update trigger for Student Create Trigger "tu_Student" after update of "Nz","n_fclt","n_spect" on "Student" referencing new as new_upd old as old_upd for each row declare numrows integer; begin -- cascade child Ocenki update when parent Student changed if (:old_upd."Nz" != :new_upd."Nz") then begin update "Ocenki" set "Nz" = :new_upd."Nz" where "Ocenki"."Nz" = :old_upd."Nz" ; end; end if; -- restrict parent SPECT when child Student updated if :new_upd."n_spect" != :old_upd."n_spect" then begin select count( * ) into numrows from "SPECT" where :new_upd."n_spect" = "SPECT"."n_spect"; if ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20002,'Parent does not exist.
Cannot update child.'); end; end if; end; end if; -- restrict parent FCLT when child Student updated if :new_upd."n_fclt" != :old_upd."n_fclt" then begin select count( * ) into numrows from "FCLT" where :new_upd."n_fclt" = "FCLT"."n_fclt"; if ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20002,'Parent does not exist. Cannot update child.'); end; end if; end; end if;
end; / -- Update trigger for Ocenki Create Trigger "tu_Ocenki" after update of "n_predm","Nz" on "Ocenki" referencing new as new_upd old as old_upd for each row declare numrows integer; begin -- restrict parent Predmet when child Ocenki updated if :new_upd."n_predm" != :old_upd."n_predm" then begin select count( * ) into numrows from "Predmet" where :new_upd."n_predm" = "Predmet"."n_predm"; if ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20002,'Parent does not exist. Cannot update child.'); end; end if; end; end if; -- restrict parent Student when child Ocenki updated if :new_upd."Nz" != :old_upd."Nz" then begin select count( * ) into numrows from "Student" where :new_upd."Nz" = "Student"."Nz"; if ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20002,'Parent does not exist. Cannot update child.'); end; end if; end; end if;
end; / -- Update trigger for Predmet Create Trigger "tu_Predmet" after update of "n_predm" on "Predmet" referencing new as new_upd old as old_upd for each row declare numrows integer; begin -- cascade child Ocenki update when parent Predmet changed if (:old_upd."n_predm" != :new_upd."n_predm") then begin update "Ocenki" set "n_predm" = :new_upd."n_predm" where "Ocenki"."n_predm" = :old_upd."n_predm" ; end; end if; end; / -- Update trigger for FCLT Create Trigger "tu_FCLT" after update of "n_fclt" on "FCLT" referencing new as new_upd old as old_upd for each row declare numrows integer; begin -- cascade child Student update when parent FCLT changed if (:old_upd."n_fclt" != :new_upd."n_fclt") then begin update "Student" set "n_fclt" = :new_upd."n_fclt" where "Student"."n_fclt" = :old_upd."n_fclt" ; end; end if; end; / -- Update trigger for SPECT Create Trigger "tu_SPECT" after update of "n_spect" on "SPECT" referencing new as new_upd old as old_upd for each row declare numrows integer; begin -- cascade child Student update when parent SPECT changed if (:old_upd."n_spect" != :new_upd."n_spect") then begin update "Student" set "n_spect" = :new_upd."n_spect" where "Student"."n_spect" = :old_upd."n_spect" ; end; end if; end; / -- Insert trigger for Student Create Trigger "ti_Student" after insert on "Student" referencing new as new_ins for each row declare numrows integer; begin -- restrict child Student when parent SPECT insert if (:new_ins."n_spect" is not null) then begin select count( * ) into numrows from "SPECT" where :new_ins."n_spect" = "SPECT"."n_spect"; IF ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20004,'Parent does not exist.
Cannot insert child.'); end; end if; end; end if; -- restrict child Student when parent FCLT insert if (:new_ins."n_fclt" is not null) then begin select count( * ) into numrows from "FCLT" where :new_ins."n_fclt" = "FCLT"."n_fclt";
IF ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20004,'Parent does not exist. Cannot insert child.'); end; end if; end; end if; end; / -- Insert trigger for Ocenki Create Trigger "ti_Ocenki" after insert on "Ocenki" referencing new as new_ins for each row declare numrows integer; begin -- restrict child Ocenki when parent Predmet insert if (:new_ins."n_predm" is not null) then begin select count( * ) into numrows from "Predmet" where :new_ins."n_predm" = "Predmet"."n_predm"; IF ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20004,'Parent does not exist. Cannot insert child.'); end; end if; end; end if; -- restrict child Ocenki when parent Student insert if (:new_ins."Nz" is not null) then begin select count( * ) into numrows from "Student" where :new_ins."Nz" = "Student"."Nz"; IF ( numrows = 0 ) then begin RAISE_APPLICATION_ERROR(-20004,'Parent does not exist. Cannot insert child.'); end; end if; end; end if; end; / Create role "Stud_admin" / Create role "Dekan" / Grant "Stud_admin" to "Petrov_P_P" / Grant "Dekan" to "Иванов_И_И" / /* Roles permissions */ Grant select on "Student" to "Stud_admin" / Grant update on "Student" to "Stud_admin" / Grant delete on "Student" to "Stud_admin" / Grant insert on "Student" to "Stud_admin" / Grant references on "Student" to "Stud_admin" / Grant select on "Student" to "Dekan" / Grant update on "Student" to "Dekan" / Grant delete on "Student" to "Dekan" / Grant insert on "Student" to "Dekan" / Grant references on "Student" to "Dekan" / Grant select on "Ocenki" to "Stud_admin" / Grant update on "Ocenki" to "Stud_admin" / Grant delete on "Ocenki" to "Stud_admin" / Grant insert on "Ocenki" to "Stud_admin" / Grant references on "Ocenki" to "Stud_admin" / Grant select on "Ocenki" to "Dekan" / Grant update on "Ocenki" to "Dekan" / Grant delete on "Ocenki" to "Dekan" / Grant insert on "Ocenki" to "Dekan" / Grant references on "Ocenki" to "Dekan" / Grant select on "Predmet" to "Stud_admin" / Grant update on "Predmet" to "Stud_admin" / Grant delete on "Predmet" to "Stud_admin" / Grant insert on "Predmet" to "Stud_admin" / Grant references on "Predmet" to "Stud_admin" / Grant select on "FCLT" to "Stud_admin" / Grant update on "FCLT" to "Stud_admin" / Grant delete on "FCLT" to "Stud_admin" / Grant insert on "FCLT" to "Stud_admin" / Grant references on "FCLT" to "Stud_admin" / Grant select on "SPECT" to "Stud_admin" / Grant update on "SPECT" to "Stud_admin" / Grant delete on "SPECT" to "Stud_admin" / Grant insert on "SPECT" to "Stud_admin" / Grant references on "SPECT" to "Stud_admin" / /* Users permissions */
