Сгенерированная 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 */