Сгенерированная Case Studio программа создания таблиц базы данных для СУБД Access
Сгенерированная 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 */
' Created 02.02.2006
' Modified 03.02.2006
' Project Kontingent
' Model Students
' Company AGTU
' Author Groshev
' Version 2006.1
' Database Access 2000
'=======================================================
'=== MS Access 2000 database creation method
'===
'=== 1. Create a new database in the MS Access 2000
'=== 2. Create a new module
'=== 3. Copy the CASE Studio 2 output SQL script into the new MS Access 2000 module
'=== 4. Select from main menu "Tools" item "References..." and check
'=== the "Microsoft DAO 3.6 Object Library."
'=== 5. Place your mouse cursor somewhere in the main procedure Main()
'=== 6. Run the module code (Click the "Run Sub/UserForm" button or press F5)
'=======================================================
Public dbs As DAO.Database
Public tdf As DAO.TableDef
Public idx As DAO.Index
Public rel As DAO.Relation
Sub Main()
Set dbs = CurrentDb()
On Error GoTo ErrorHandler
Call CreateTables
Call CreatePrimaryKeys
Call CreateIndexes
Call CreateAlterKeys
Call CreateRelations
Call CreateQueries
MsgBox "Script successfully processed.", vbInformation
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3010
MsgBox "Table " & tdf.Name & " allready exist!", vbInformation
Err.Clear
Case 3284
MsgBox "Index " & idx.Name & " for table " & tdf.Name & " allready exist!", vbInformation
Err.Clear
Case Else
MsgBox Err.Description, vbCritical
End Select
End Sub
' Create tables
'===============
Sub CreateTables()
Call CreateTable1 'Student
Call CreateTable2 'Ocenki
Call CreateTable3 'Predmet
Call CreateTable4 'FCLT
Call CreateTable5 'SPECT
End Sub
'=== Create table Student ======
Sub CreateTable1()
Set tdf = dbs.CreateTableDef( "Student" )
Call AddFieldToTable("Nz", dbText, 7, 0, "", "", "", TRUE )
Call AddFieldToTable("Fio", dbText, 45, 0, "", "", "", FALSE )
Call AddFieldToTable("date_p", dbDate, 0, 0, "", "", "", FALSE )
Call AddFieldToTable("n_fclt", dbSingle, 0, 0, "", "", "", TRUE )
Call AddFieldToTable("n_spect", dbText, 9, 0, "", "", "", TRUE )
Call AddFieldToTable("kurs", dbSingle, 0, 0, "", "", "", FALSE )
Call AddFieldToTable("n_grup", dbText, 10, 0, "", "", "", FALSE )
Call AddFieldToTable("n_pasp", dbText, 10, 0, "", "", "", FALSE )
dbs.TableDefs.Append tdf
End Sub
'=== Create table Ocenki ======
Sub CreateTable2()
Set tdf = dbs.CreateTableDef( "Ocenki" )
Call AddFieldToTable("semestr", dbSingle, 0, 0, "", "", "", FALSE )
Call AddFieldToTable("n_predm", dbSingle, 0, 0, "", "", "", TRUE )
Call AddFieldToTable("ball", dbText, 1, 0, "", "", "", FALSE )
Call AddFieldToTable("data_b", dbDate, 0, 0, "", "", "", FALSE )
Call AddFieldToTable("Prepod", dbText, 45, 0, "", "", "", FALSE )
Call AddFieldToTable("Nz", dbText, 7, 0, "", "", "", TRUE )
dbs.TableDefs.Append tdf
End Sub
'=== Create table Predmet ======
Sub CreateTable3()
Set tdf = dbs.CreateTableDef( "Predmet" )
Call AddFieldToTable("n_predm", dbSingle, 0, 0, "", "", "", TRUE )
Call AddFieldToTable("name_p", dbText, 120, 0, "", "", "", FALSE )
dbs.TableDefs.Append tdf
End Sub
'=== Create table FCLT ======
Sub CreateTable4()
Set tdf = dbs.CreateTableDef( "FCLT" )
Call AddFieldToTable("n_fclt", dbSingle, 0, 0, "", "", "", TRUE )
Call AddFieldToTable("name_f", dbText, 120, 0, "", "", "", FALSE )
dbs.TableDefs.Append tdf
End Sub
'=== Create table SPECT ======
Sub CreateTable5()
Set tdf = dbs.CreateTableDef( "SPECT" )
Call AddFieldToTable("n_spect", dbText, 9, 0, "", "", "", TRUE )
Call AddFieldToTable("name_S", dbText, 120, 0, "", "", "", FALSE )
dbs.TableDefs.Append tdf
End Sub
' Create primary keys
'=====================
Sub CreatePrimaryKeys()
'=== Create primary key for table Student ======
Set tdf = dbs.TableDefs( "Student" )
Set idx = tdf.CreateIndex( "pk_Student" )
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex( "Nz", False )
tdf.Indexes.Append idx
'=== Create primary key for table Predmet ======
Set tdf = dbs.TableDefs( "Predmet" )
Set idx = tdf.CreateIndex( "pk_Predmet" )
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex( "n_predm", False )
tdf.Indexes.Append idx
'=== Create primary key for table FCLT ======
Set tdf = dbs.TableDefs( "FCLT" )
Set idx = tdf.CreateIndex( "pk_FCLT" )
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex( "n_fclt", False )
tdf.Indexes.Append idx
'=== Create primary key for table SPECT ======
Set tdf = dbs.TableDefs( "SPECT" )
Set idx = tdf.CreateIndex( "pk_SPECT" )
idx.Primary = True
idx.Unique = True
idx.IgnoreNulls = False
Call AddFieldToIndex( "n_spect", False )
tdf.Indexes.Append idx
End Sub
' Create indexes
'================
Sub CreateIndexes()
End Sub
' Create alter keys (unique indexes in MS ACCESS)
'================================================
Sub CreateAlterKeys()
End Sub
' Create relations
'==================
Sub CreateRelations()
'=== Create relations between parent table Student and child table Ocenki ======
Set rel = dbs.CreateRelation("Student_Ocenki")
rel.Table = "Student"
rel.ForeignTable = "Ocenki"
rel.Attributes = dbRelationUpdateCascade+dbRelationDeleteCascade
Call AddFieldToRelation("Nz", "Nz")
dbs.Relations.Append rel
'=== Create relations between parent table Predmet and child table Ocenki ======
Set rel = dbs.CreateRelation("Predmet_Ocenki")
rel.Table = "Predmet"
rel.ForeignTable = "Ocenki"
rel.Attributes = dbRelationUpdateCascade+dbRelationDeleteCascade
Call AddFieldToRelation("n_predm", "n_predm")
dbs.Relations.Append rel
'=== Create relations between parent table FCLT and child table Student ======
Set rel = dbs.CreateRelation("FCLT_Student")
rel.Table = "FCLT"
rel.ForeignTable = "Student"
rel.Attributes = dbRelationUpdateCascade+dbRelationDeleteCascade
Call AddFieldToRelation("n_fclt", "n_fclt")
dbs.Relations.Append rel
'=== Create relations between parent table SPECT and child table Student ======
Set rel = dbs.CreateRelation("SPECT_Student")
rel.Table = "SPECT"
rel.ForeignTable = "Student"
rel.Attributes = dbRelationUpdateCascade+dbRelationDeleteCascade
Call AddFieldToRelation("n_spect", "n_spect")
dbs.Relations.Append rel
End Sub
' Create queries
'================
Sub CreateQueries()
Dim qdf As QueryDef
End Sub
' Add fields to table
'=====================
Sub AddFieldToTable(FieldName As String, DataType As String, SizeCol As Integer,
Attributes As Long, DefaultValue As Variant, ValText As String,
ValRule As String, NotN As Boolean)
Dim fld As DAO.Field
Set fld = tdf.CreateField( FieldName, DataType )
If SizeCol <> 0 Then fld.Size = SizeCol
If Attributes <> 0 Then fld.Attributes = Attributes
fld.Required = NotN
fld.DefaultValue = DefaultValue
fld.ValidationRule = ValRule
fld.ValidationText = ValText
tdf.Fields.Append fld
End Sub
' Add properties to table
'=========================
Sub AddPropertyToTable( PropertyName As String, Value As Variant, DataType As String)
Dim prp As DAO.Property
Set prp = tdf.CreateProperty(PropertyName, DataType, Value)
tdf.Properties.Append prp
End Sub
' Add properties to field
'=========================
Sub AddPropertyToField(FieldName As String, PropertyName As String, Value As Variant,
DataType As String)
Dim prp As DAO.Property
Dim fld As DAO.Field
Set fld = tdf.Fields( FieldName )
Set prp = fld.CreateProperty(PropertyName, DataType, Value)
fld.Properties.Append prp
End Sub
' Add fields to index
'=====================
Sub AddFieldToIndex( FieldName As String, Descending As Boolean )
Dim fld As DAO.Field
Set fld = idx.CreateField( FieldName )
If Descending = True Then fld.Attributes = dbDescending
idx.Fields.Append fld
End Sub
' Add fields to relation
'========================
Sub AddFieldToRelation( PKField As String, FKField As String )
Dim fld As DAO.Field
Set fld = rel.CreateField( PKField )
fld.ForeignName = FKField
rel.Fields.Append fld
End Sub
