Материал: Горбатов Аттестационные испытания автоматизированных систем от 2014

create table AUDITORY(aud_id integer not NULL PRIMARY KEY, aud_num integer not NULL,

aud_location varchar2(10) not NULL, UNIQUE (aud_num, aud_location));

create table COMPUTER(comp_id integer not NULL PRIMARY KEY, CPU_type varchar2(20) not NULL,

Freq integer not NULL, HDD integer not NULL, RAM integer not NULL,

Net varchar2(3) CHECK (Net in ('+','-')), OS varchar2(20),

Monitor varchar2(20), aud_id integer,

FOREIGN KEY (aud_id) references AUDITORY(aud_id));

create table KAFEDRA(kaf_id integer not NULL PRIMARY KEY, kaf_name varchar2(40) not NULL UNIQUE);

create table ST_GROUP(g_id integer not NULL PRIMARY KEY, g_name varchar2(10) not NULL UNIQUE,

kaf_id integer,

FOREIGN KEY (kaf_id) references KAFEDRA(kaf_id));

create table STUDENT(st_id integer not NULL PRIMARY KEY, st_F varchar2(35) not NULL,

st_I varchar2(35) not NULL, st_O varchar2(35),

g_id integer,

FOREIGN KEY (g_id) references ST_GROUP(g_id));

create table PROFESSOR(prof_id integer not NULL PRIMARY KEY, prof_F varchar2(35) not NULL,

prof_I varchar2(35) not NULL, prof_O varchar2(35) not NULL, kaf_id integer,

FOREIGN KEY (kaf_id) references KAFEDRA(kaf_id));

create table COURSE(course_id integer not NULL PRIMARY KEY,

541

course_name varchar2(35) not NULL UNIQUE, start_week integer not NULL,

end_week integer not NULL,

offset varchar2(6) CHECK (offset in ('+','-')), exam varchar2(3) CHECK (exam in ('+','-')),

constraint check_week CHECK( (start_week < end_week) and ( start_week > 0 and end_week < 17) ));

create table SEMESTER(sem_id integer not NULL PRIMARY KEY, sem_name varchar2(30) not NULL UNIQUE);

create table DAY(day_id integer not NULL PRIMARY KEY, day_name varchar2(30) not NULL UNIQUE);

create table PAIR(pair_id integer not NULL PRIMARY KEY, start_time DATE not NULL,

end_time DATE not NULL);

create table SCHEDULE(sched_id integer not NULL PRIMARY KEY, year integer not NULL,

course_id integer not NULL, prof_id integer not NULL, g_id integer not NULL, aud_id integer not NULL, sem_id integer not NULL, day_id integer not NULL, pair_id integer not NULL,

FOREIGN KEY (course_id) references COURSE(course_id), FOREIGN KEY (prof_id) references PROFESSOR(prof_id), FOREIGN KEY (g_id) references ST_GROUP(g_id), FOREIGN KEY (aud_id) references AUDITORY(aud_id), FOREIGN KEY (sem_id) references SEMESTER(sem_id), FOREIGN KEY (day_id) references DAY(day_id), FOREIGN KEY (pair_id) references PAIR(pair_id));

create table CLASS(class_id integer not NULL PRIMARY KEY, class_date DATE not NULL,

present integer not NULL,

542

changed varchar2(10) CHECK (changed in ('+','-')), sched_id integer,

FOREIGN KEY (sched_id) references SCHEDULE(sched_id));

create table ERROR(err_id integer not NULL PRIMARY KEY, err_type varchar2(30),

err_description varchar2(50) not NULL,

repaired varchar2(8) CHECK (repaired in ('+','-')), class_id integer not NULL,

comp_id integer,

FOREIGN KEY (class_id) references CLASS(class_id), FOREIGN KEY (comp_id) references COMPUTER(comp_id));

--INSERT.SQL

insert into AUDITORY values(1,206,'Т'); insert into AUDITORY values(2,313,'Т'); insert into AUDITORY values(3,410,'Б'); insert into AUDITORY values(4,414,'Б');

insert into COMPUTER values(1,'AMD Athlon',2000,40,256,'+','Windows 2000 Server', 'Sony 15"', 1); insert into COMPUTER values(2,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(3,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(4,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(5,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(6,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(7,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(8,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(9,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1);

543

insert into COMPUTER values(10,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(11,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(12,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(13,'AMD Athlon',1600,40,256,'+','Windows 2000 Pro', 'Sony 15"', 1); insert into COMPUTER values(14,'AMD Athlon',1600,40,256,'- ','Windows 2000 Pro', 'Sony 15"', 1);

insert into COMPUTER values(15,'AMD Athlon',1600,40,256,'- ','Windows 2000 Pro', 'Sony 15"', 1);

insert into COMPUTER values(16,'Intel Pentium II',600,40,256,'+','Windows 2000 Server', 'Siemens 15"', 2); insert into COMPUTER values(17,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(18,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(19,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(20,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(21,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(22,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(23,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(24,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(25,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2); insert into COMPUTER values(26,'Intel Pentium MMX',200,10,128,'+','Windows NT 4.0', 'Siemens 15"', 2);

insert into COMPUTER values(27,'Intel Pentium MMX',200,10,128,'- ','Windows NT 4.0', 'Siemens 15"', 2);

544

insert into COMPUTER values(28,'Intel Pentium MMX',266,10,128,'- ','Windows NT 4.0', 'Siemens 15"', 2);

insert into COMPUTER values(29,'Intel Pentium MMX',233,10,128,'- ','Windows NT 4.0', 'Siemens 15"', 2);

insert into COMPUTER values(30,'4х Ultra Sparc',850,100,512,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(31,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(32,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(33,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(34,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(35,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(36,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(37,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(38,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(39,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(40,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(41,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(42,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(43,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(44,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

insert into COMPUTER values(45,'Ultra Sparc',400,20,128,'+','OS Solaris', 'Sun 19"', 4);

545