Автор работы: Пользователь скрыл имя, 23 Октября 2011 в 09:45, курсовая работа
Целью курсовой работы является проектирование и построение концептуальной информационной модели предметной области «Учебный процесс в ВУЗе». В начало работы мне была выдана соответствующая документация. В основе базы данных по предложенной предметной области лежат общие сведения об учебном процессе в ВУЗе, а также регулярные запросы пользователей и файлов существующей системы обработки информации. В процессе изучения документации было принято решение о создании двух виртуальных документов: «Сведения о факультетах» и «Контингент студентов». Они необходимы для дополнения исходных отношений и расчета вторичных атрибутов.
Содержание 2
Задание 3
Введение 4
Глава 1. Описание исходных отношений 5
1.1.Анализ документов 5
1.2.Отношения 6
1.3. Описание словарей и кодификаторов 13
1.4. Сопоставимость атрибутов 14
Глава 2. Уточнение исходных отношений. 15
2.1. Свертка и нормализация отношений 15
2.2. Объединение отношений 17
2.3. Отношения после нормализации 18
Глава 3. Связывание отношений и простых структур в единую схему данных 20
3.1. Логическая модель 20
3.2.Физическая модель 21
3.3. Дополнительные объекты БД 22
3.3. Расчет вторичных атрибутов 23
Заключение 26
Приложение 27
Код DDL 27
CREATE TABLE O4.1__Curriculum (
Hours_for_lecture NUMBER(3) NULL,
Year_of_enter DATE NOT NULL,
Hours_for_laboratory NUMBER(3) NULL,
Hours_for_practice NUMBER(3) NULL,
Id_speciality NUMBER(6) NOT NULL,
Id_subject NUMBER(3) NOT NULL
);
CREATE UNIQUE INDEX XPKO4.1__Curriculum ON O4.1__Curriculum
(
Year_of_enter ASC,
Id_speciality ASC,
Id_subject ASC
);
CREATE INDEX XIF1O4.1__Curriculum ON O4.1__Curriculum
(
Id_speciality ASC
);
CREATE INDEX XIF2O4.1__Curriculum ON O4.1__Curriculum
(
Id_subject ASC
);
ALTER TABLE O4.1__Curriculum
ADD ( PRIMARY KEY (Year_of_enter, Id_speciality, Id_subject) ) ;
ALTER TABLE O4.1__Curriculum
ADD ( FOREIGN KEY (Id_subject)
REFERENCES K1__Subject ) ;
ALTER TABLE O4.1__Curriculum
ADD ( FOREIGN KEY (Id_speciality)
REFERENCES O4.2.1__Speciality) ;
Таблица « O4.2.1__Speciality »
CREATE TABLE O4.2.1__Speciality (
Id_speciality NUMBER(6) NOT NULL,
Address_hostel VARCHAR2(20) NULL,
Name_of_speciality VARCHAR2(30) NULL,
Id_cathedra NUMBER(3) NOT NULL
);
CREATE UNIQUE INDEX XPKO4.2.1__Speciality ON O4.2.1__Speciality
(
Id_speciality ASC
);
CREATE UNIQUE INDEX XAK1O4.2.1__Speciality ON O4.2.1__Speciality
(
Name_of_speciality ASC
);
CREATE INDEX XIF1O4.2.1__Speciality ON O4.2.1__Speciality
(
Id_cathedra ASC
);
CREATE INDEX XIE1O4.2.1__Speciality ON O4.2.1__Speciality
(
Address_hostel ASC
);
ALTER TABLE O4.2.1__Speciality
ADD ( PRIMARY KEY (Id_speciality) ) ;
ALTER TABLE O4.2.1__Speciality
ADD ( FOREIGN KEY (Id_cathedra)
REFERENCES K2__Cathedra );
CREATE SEQUENCE O4.2.1__Speciality _SEQ
minvalue 1
maxvalue 9999999
star with 1
increment by 1
cache 20;
CREATE TRIGGER O4.2.1__Speciality _ins_trg
before insert
on O4.2.1__Speciality
for each row
begin
select O4.2.1__Speciality _seq.nextval
into: new. Id_speciality
from dual;
end;
Таблица « O5.1_Faculty »
CREATE TABLE O5.1_Faculty (
Address_deanery VARCHAR2(20) NOT NULL,
id_dean NUMBER(2) NOT NULL,
Faculty VARCHAR2(5) NOT NULL,
Id_cathedra NUMBER(3) NOT NULL,
id_deputy_dean NUMBER(2) NOT NULL
);
CREATE UNIQUE INDEX XPKO5.1_Faculty ON O5.1_Faculty
(
Faculty ASC,
Id_cathedra ASC
);
CREATE INDEX XIF2O5.1_Faculty ON O5.1_Faculty
(
id_dean ASC
);
CREATE INDEX XIF3O5.1_Faculty ON O5.1_Faculty
(
Id_cathedra ASC
);
CREATE INDEX XIF4O5.1_Faculty ON O5.1_Faculty
(
id_deputy_dean ASC
);
ALTER TABLE O5.1_Faculty
ADD ( PRIMARY KEY (Faculty, Id_cathedra) );
ALTER TABLE O5.1_Faculty
ADD ( FOREIGN KEY (id_dean)
REFERENCES O5.4__Employee);
ALTER TABLE O5.1_Faculty
ADD ( FOREIGN KEY (Id_cathedra)
REFERENCES K2__Cathedra );
ALTER TABLE O5.1_Faculty
ADD ( FOREIGN KEY (id_deputy_dean)
REFERENCES O5.4__Employee);
Таблица « O5.4__Employee »
CREATE TABLE O5.4__Employee (
id_employee NUMBER(2) NOT NULL,
Last_name VARCHAR2(10) NULL,
Name VARCHAR2(10) NULL,
Fathers_name VARCHAR2(10) NULL,
Date_of_appointment DATE NULL,
Telefon_number VARCHAR2(15) NULL
);
CREATE UNIQUE INDEX XPKO5.4__Employee ON O5.4__Employee
(
id_employee ASC
);
ALTER TABLE O5.4__Employee
ADD ( PRIMARY KEY (id_employee) ) ; CREATE SEQUENCE O5.4__Employee _SEQ
minvalue 1
maxvalue 9999999
star with 1
increment by 1
cache 20;
CREATE TRIGGER O5.4__Employee _ins_trg
before insert
on O5.4__Employee
for each row
begin
select O5.4__Employee _seq.nextval
into: new. id_employee
from dual;
end;
Таблица « OB2.1.1__Student »
CREATE TABLE OB2.1.1__Student (
id_student NUMBER(100000) NOT NULL,
Last_name VARCHAR2(10) NULL,
Date_of_enter DATE NULL,
Date_of_deduction DATE NULL,
Number__of_diploma VARCHAR2(10) NULL,
Mark_of_diploma NUMBER(1) NULL,
Fathers_name VARCHAR2(10) NULL,
Name VARCHAR2(10) NULL,
City_of_registration VARCHAR2(20) NULL,
Gender NUMBER(1) NOT NULL
);
CREATE UNIQUE INDEX XPKOB2.1.1__Student ON OB2.1.1__Student
(
id_student ASC
);
CREATE UNIQUE INDEX XAK1OB2.1.1__Student ON OB2.1.1__Student
(
Number__of_diploma ASC
);
CREATE INDEX XIF1OB2.1.1__Student ON OB2.1.1__Student
(
Gender ASC
);
ALTER TABLE OB2.1.1__Student
ADD ( PRIMARY KEY (id_student) ) ;
ALTER TABLE OB2.1.1__Student
ADD ( FOREIGN KEY (Gender)
REFERENCES K3__Gender );
CREATE SEQUENCE OB2.1.1__Student _SEQ
minvalue 1
maxvalue 9999999
star with 1
increment by 1
cache 20;
CREATE TRIGGER OB2.1.1__Student _ins_trg
before insert
on OB2.1.1__Student
for each row
begin
select OB2.1.1__Student _seq.nextval
into: new. id_student
from dual;
end;
Таблица « OB3_Attainment »
CREATE TABLE OB3_Attainment (
№_group VARCHAR2(6) NOT NULL,
Term NUMBER(2) NOT NULL,
Number_of_hours_on_discipline NUMBER(3) NULL,
Attainment NUMBER(2) NULL,
Subject NUMBER(3) NOT NULL
);
CREATE UNIQUE INDEX XPKOB3_Attainment ON OB3_Attainment
(
№_group ASC,
Term ASC,
Subject ASC
);
CREATE INDEX XIF1OB3_Attainment ON OB3_Attainment
(
№_group ASC
);
CREATE INDEX XIF2OB3_Attainment ON OB3_Attainment
(
Subject ASC
);
CREATE INDEX XIE1OB3_Attainment ON OB3_Attainment
(
Subject ASC
);
ALTER TABLE OB3_Attainment
ADD ( PRIMARY KEY (№_group, Term, Subject) ) ;
ALTER TABLE OB3_Attainment
ADD ( FOREIGN KEY (Subject)
REFERENCES K1__Subject );
ALTER TABLE OB3_Attainment
ADD ( FOREIGN KEY (№_group)
REFERENCES O3__Group );
Таблица « OB6.2__Student_at_the_faculty »
CREATE TABLE OB6.2__Student_at_the_faculty (
id_student NUMBER(7) NOT NULL,
Faculty VARCHAR2(5) NOT NULL,
Id_cathedra NUMBER(3) NOT NULL
);
CREATE UNIQUE INDEX XPKOB6.2__Student_at_the_facul ON OB6.2__Student_at_the_faculty
(
id_student ASC,
Faculty ASC,
Id_cathedra ASC
);
CREATE INDEX XIF1OB6.2__Student_at_the_facu ON OB6.2__Student_at_the_faculty
(
id_student ASC
);
CREATE INDEX XIF2OB6.2__Student_at_the_facu ON OB6.2__Student_at_the_faculty
(
Faculty ASC,
Id_cathedra ASC
);
ALTER TABLE OB6.2__Student_at_the_faculty
ADD ( PRIMARY KEY (id_student, Faculty, Id_cathedra) );
ALTER TABLE OB6.2__Student_at_the_faculty
ADD ( FOREIGN KEY (Faculty, Id_cathedra)
REFERENCES O5.1_Faculty ) ;
ALTER TABLE OB6.2__Student_at_the_faculty
ADD ( FOREIGN KEY (id_student)
REFERENCES OB2.1.1__Student);
Таблица «OВ6.3__Competition »
CREATE TABLE OВ6.3__Competition (
Name_of_competition VARCHAR2(20) NOT NULL,
id_student NUMBER(7) NOT NULL,
Faculty VARCHAR2(5) NOT NULL,
Id_cathedra NUMBER(3) NOT NULL
);
CREATE UNIQUE INDEX XPKOВ6.3__Competition ON OВ6.3__Competition
(
Name_of_competition ASC,
id_student ASC,
Faculty ASC,
Id_cathedra ASC
);
CREATE INDEX XIF1OВ6.3__Competition ON OВ6.3__Competition