Sybase 11 procedures for verifying the referential integrity of an existing Sybase database whose schema is generated from the OPM schema shown in section 3 are contained in file Example_check.SYB11:
use Example
go
create procedure c_CLASSES
as
begin
select * into #1 from CLASSES
where CLASSES._dbid is not null
and not exists ( select * from DATABASES
where DATABASES._dbid = CLASSES._dbid )
if (select count(*) from #1) != 0
begin
print "The following tuples in CLASSES
do not have reference in
DATABASES "
select * from #1
end
return 0
end
go
create procedure c_COURSE
as
begin
select * into #1 from COURSE
where not exists ( select * from OBJECTS
where OBJECTS._oid = COURSE._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in COURSE
do not have reference in
OBJECTS "
select * from #1
end
select * into #2 from COURSE
where COURSE._defaultVersion is not null
and not exists ( select * from _vCOURSE
where _vCOURSE._oid = COURSE._oid
and _vCOURSE._version = COURSE._defaultVersion )
if (select count(*) from #2) != 0
begin
print "The following tuples in COURSE
do not have reference in
_vCOURSE "
select * from #2
end
return 0
end
go
create procedure c_COURSE_references
as
begin
select * into #1 from COURSE_references
where not exists ( select * from _vCOURSE
where _vCOURSE._oid = COURSE_references._oid
and _vCOURSE._version = COURSE_references._version )
if (select count(*) from #1) != 0
begin
print "The following tuples in COURSE_references
do not have reference in
_vCOURSE "
select * from #1
end
select * into #2 from COURSE_references
where not exists ( select * from REFERENCE
where REFERENCE._oid = COURSE_references.references )
if (select count(*) from #2) != 0
begin
print "The following tuples in COURSE_references
do not have reference in
REFERENCE "
select * from #2
end
return 0
end
go
create procedure c_DEPARTMENT
as
begin
select * into #1 from DEPARTMENT
where not exists ( select * from OBJECTS
where OBJECTS._oid = DEPARTMENT._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in DEPARTMENT
do not have reference in
OBJECTS "
select * from #1
end
select * into #2 from DEPARTMENT
where not exists ( select * from DepartmentNames
where DepartmentNames._code = DEPARTMENT.name )
if (select count(*) from #2) != 0
begin
print "The following tuples in DEPARTMENT
do not have reference in
DepartmentNames "
select * from #2
end
return 0
end
go
create procedure c_FACULTY
as
begin
select * into #1 from FACULTY
where not exists ( select * from PERSON
where PERSON._oid = FACULTY._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in FACULTY
do not have reference in
PERSON "
select * from #1
end
select * into #2 from FACULTY
where FACULTY._defaultVersion is not null
and not exists ( select * from _vFACULTY
where _vFACULTY._oid = FACULTY._oid
and _vFACULTY._version = FACULTY._defaultVersion )
if (select count(*) from #2) != 0
begin
print "The following tuples in FACULTY
do not have reference in
_vFACULTY "
select * from #2
end
select * into #3 from FACULTY
where FACULTY.department is not null
and not exists ( select * from DEPARTMENT
where DEPARTMENT._oid = FACULTY.department )
if (select count(*) from #3) != 0
begin
print "The following tuples in FACULTY
do not have reference in
DEPARTMENT "
select * from #3
end
select _oid
into #4 from FACULTY
where exists (select * from STUDENT
where STUDENT._oid = FACULTY._oid)
if (select count(*) from #4) != 0
begin
print "The following instances in FACULTY
are also in STUDENT:"
select * from #4
end
return 0
end
go
create procedure c_PERSON
as
begin
select * into #1 from PERSON
where not exists ( select * from OBJECTS
where OBJECTS._oid = PERSON._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in PERSON
do not have reference in
OBJECTS "
select * from #1
end
return 0
end
go
create procedure c_REFERENCE
as
begin
select * into #1 from REFERENCE
where not exists ( select * from OBJECTS
where OBJECTS._oid = REFERENCE._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in REFERENCE
do not have reference in
OBJECTS "
select * from #1
end
return 0
end
go
create procedure c_SCLASSES
as
begin
select * into #1 from SCLASSES
where not exists ( select * from CLASSES
where CLASSES._cid = SCLASSES._class )
if (select count(*) from #1) != 0
begin
print "The following tuples in SCLASSES
do not have reference in
CLASSES "
select * from #1
end
select * into #2 from SCLASSES
where SCLASSES._subclass is not null
and not exists ( select * from CLASSES
where CLASSES._cid = SCLASSES._subclass )
if (select count(*) from #2) != 0
begin
print "The following tuples in SCLASSES
do not have reference in
CLASSES "
select * from #2
end
return 0
end
go
create procedure c_STUDENT
as
begin
select * into #1 from STUDENT
where not exists ( select * from PERSON
where PERSON._oid = STUDENT._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in STUDENT
do not have reference in
PERSON "
select * from #1
end
select * into #2 from STUDENT
where STUDENT._defaultVersion is not null
and not exists ( select * from _vSTUDENT
where _vSTUDENT._oid = STUDENT._oid
and _vSTUDENT._version = STUDENT._defaultVersion )
if (select count(*) from #2) != 0
begin
print "The following tuples in STUDENT
do not have reference in
_vSTUDENT "
select * from #2
end
select _oid
into #3 from STUDENT
where exists (select * from FACULTY
where FACULTY._oid = STUDENT._oid)
if (select count(*) from #3) != 0
begin
print "The following instances in STUDENT
are also in FACULTY:"
select * from #3
end
return 0
end
go
create procedure c_STUDENT_attends
as
begin
select * into #1 from STUDENT_attends
where not exists ( select * from _vSTUDENT
where _vSTUDENT._oid = STUDENT_attends._oid
and _vSTUDENT._version = STUDENT_attends._version )
if (select count(*) from #1) != 0
begin
print "The following tuples in STUDENT_attends
do not have reference in
_vSTUDENT "
select * from #1
end
select * into #2 from STUDENT_attends
where STUDENT_attends.course is not null
and STUDENT_attends._vcourse is not null
and not exists ( select * from _vCOURSE
where _vCOURSE._oid = STUDENT_attends.course
and _vCOURSE._version = STUDENT_attends._vcourse )
if (select count(*) from #2) != 0
begin
print "The following tuples in STUDENT_attends
do not have reference in
_vCOURSE "
select * from #2
end
select * into #3 from STUDENT_attends
where STUDENT_attends.inDepartment is not null
and not exists ( select * from DEPARTMENT
where DEPARTMENT._oid = STUDENT_attends.inDepartment )
if (select count(*) from #3) != 0
begin
print "The following tuples in STUDENT_attends
do not have reference in
DEPARTMENT "
select * from #3
end
return 0
end
go
create procedure c__vCOURSE
as
begin
select * into #1 from _vCOURSE
where not exists ( select * from COURSE
where COURSE._oid = _vCOURSE._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in _vCOURSE
do not have reference in
COURSE "
select * from #1
end
return 0
end
go
create procedure c__vFACULTY
as
begin
select * into #1 from _vFACULTY
where not exists ( select * from FACULTY
where FACULTY._oid = _vFACULTY._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in _vFACULTY
do not have reference in
FACULTY "
select * from #1
end
return 0
end
go
create procedure c__vSTUDENT
as
begin
select * into #1 from _vSTUDENT
where not exists ( select * from STUDENT
where STUDENT._oid = _vSTUDENT._oid )
if (select count(*) from #1) != 0
begin
print "The following tuples in _vSTUDENT
do not have reference in
STUDENT "
select * from #1
end
return 0
end
go
create procedure c_FACULTY_rank
as
begin
select * into #1 from FACULTY
where rank not in ("Professor", "Associate Professor",
"Assistant Professor")
if (select count(*) from #1) != 0
begin
print "The following tuples in FACULTY
violate the domain definition on rank"
select * from #1
end
return 0
end
go
create procedure c__vFACULTY_salary
as
begin
select * into #1 from _vFACULTY
where salary not between 30000 and 150000
if (select count(*) from #1) != 0
begin
print "The following tuples in _vFACULTY
violate the domain definition on salary"
select * from #1
end
return 0
end
go
create procedure check_all
as
begin
exec c_CLASSES
exec c_COURSE
exec c_COURSE_references
exec c_DEPARTMENT
exec c_FACULTY
exec c_PERSON
exec c_REFERENCE
exec c_SCLASSES
exec c_STUDENT
exec c_STUDENT_attends
exec c__vCOURSE
exec c__vFACULTY
exec c__vSTUDENT
exec c_FACULTY_rank
exec c__vFACULTY_salary
end
go
quit