Основы работы с базами данных


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


Начало  Назад  Вперед



Книжный магазин