Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Костромской государственный технологический университет

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

БД-ЛР-1.docx

Скачиваний:

Добавлен:

19.09.2019

Размер:

799.47 Кб

Скачать

☆

<<< < Предыдущая 12 / 22

Id_g serial primary key,

name_g char(20) not null,

id_spec int not null references NEW_spec

);

create table NEW_ludi

(

id_chel serial primary key,

fio char(100) not null

);

create table NEW_semestr

(

sem int primary key,

kurs int not null

);

create table NEW_disc

(

id_disc serial primary key,

name_d char(50) not null

);

create table NEW_prepod

(

id_pr serial primary key,

id_k int not null references NEW_kafedra,

id_chel int not null references NEW_ludi

);

create table NEW_RP

(

id_spec_disc serial primary key,

id_sp int not null references NEW_spec,

id_disc int not null references NEW_disc,

sem int not null references NEW_semestr

);

create table NEW_student

(

Id_st serial primary key,

nzk char(20) not null,

id_chel int not null references NEW_ludi,

id_g int not null references NEW_gruppa

);

create table NEW_ekzamen

(

Id_e serial primary key,

data_e date not null,

id_g int not null references NEW_gruppa,

aud char(20),

id_pr int not null references NEW_prepod,

id_disc int not null references NEW_disc

);

create table NEW_ocen

(

oc int primary key,

oc_pr char(20));

create table NEW_usp

(

id_usp serial not null primary key,

id_st int not null references NEW_student,

oc int not null references NEW_ocen,

id_e int not null references NEW_ekzamen,

dat date);

Перенос данных из исходной БД:

create table t_temp

(

id_f int,

name_k char(20)

);

insert into t_temp (id_f,name_k)

values (7,'АМТ');

insert into t_temp (id_f,name_k)

values (8,'ТГП');

insert into t_temp (id_f,name_k)

values (9,'ЧС');

insert into NEW_kafedra (name_f,name_k)

select name_f,name_k from facultet

inner join t_temp on facultet.id_f=t_temp.id_f;

insert into NEW_kafedra (name_k,name_f)

values('ВТ','FAST');

drop table t_temp;

create table t_temp

(

name_sp char(20),

id_k int

);

insert into t_temp (name_sp,id_k)

values ('AMT',14);

insert into t_temp (name_sp,id_k)

values ('Les',16);

insert into t_temp (name_sp,id_k)

values ('Yur',15);

insert into t_temp (name_sp,id_k)

values ('SAPR',17);

insert into NEW_spec (name_sp,id_k)

select spec,id_k from gruppa

inner join t_temp on gruppa.spec=t_temp.name_sp;

drop table t_temp;

insert into NEW_gruppa (name_g,id_spec)

select name_g,id_sp from gruppa

inner join NEW_spec on gruppa.spec=NEW_spec.name_sp;

insert into NEW_ludi (fio)

select st from student;

create table t_temp

(

id_gr int,

fio char(20)

);

insert into t_temp(id_gr,fio)

values (1,'Смирнов И.И.');

insert into t_temp(id_gr,fio)

values (1,'Иванов В.С.');

insert into t_temp(id_gr,fio)

values (2,'Фёдоров Ф.Ф.');

insert into t_temp(id_gr,fio)

values (3,'Ломагин А.В.');

insert into t_temp(id_gr,fio)

values (4,'Горшков А.В.');

insert into NEW_student (nzk,id_chel,id_g)

select reg_n,id_chel,id_gr from student

inner join NEW_ludi on student.st=NEW_ludi.fio

inner join t_temp on student.st=t_temp.fio;

drop table t_temp;