Разработка модели базы данных

/*
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 */