Шаг 16. Программная реализация

Используя язык доступа к БД SQL (Transact-SQL) можно составить следующий сценарий создания БД:

CREATE TABLE Exam (

  ID              char(18) NOT NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE Spec (

  ID              char(18) NOT NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE Standard (

  ID              char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  MaxHourWeek     char(18) NULL,

  SkillType       char(18) NULL,

  PRIMARY KEY (ID, SpecID),

  FOREIGN KEY (SpecID)

                        REFERENCES Spec

)

go

CREATE TABLE TeachForm (

  ID              char(18) NOT NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE TeachPlan (

  ID              char(18) NULL,

  SpecID          char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  TimeLength      char(18) NULL,

  SignDate        char(18) NULL,

  Speciality      char(18) NULL,

  TeachLevelType  char(18) NULL,

  PRIMARY KEY (ID, SpecID, StandardID, TeachFormID),

  FOREIGN KEY (TeachFormID)

                        REFERENCES TeachForm,

   FOREIGN KEY (SpecID)

                        REFERENCES Spec,

  FOREIGN KEY (StandardID, SpecID)

                        REFERENCES Standard

)

go

CREATE TABLE Subject (

  ID              char(18) NOT NULL,

  StandardID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  Index           char(18) NULL,

  Name            char(18) NULL,

  StanHourQuantity char(18) NULL,

  TeachCourse     char(18) NULL,

  PRIMARY KEY (ID, StandardID, TeachPlanID, SpecID, TeachFormID),

  FOREIGN KEY (StandardID, SpecID)

                        REFERENCES Standard,

  FOREIGN KEY (TeachPlanID, SpecID, StandardID, TeachFormID)

                        REFERENCES TeachPlan

)

go

CREATE TABLE SemExamination (

  SubjectID       char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  ExamID          char(18) NOT NULL,

  SemNumb         char(18) NULL,

  PRIMARY KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID, ExamID),

  FOREIGN KEY (ExamID)

                        REFERENCES Exam,

  FOREIGN KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID)

                        REFERENCES Subject

)

go

CREATE TABLE Examination (

  SubjectID       char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  ExamID          char(18) NOT NULL,

  Quantity        char(18) NULL,

  PRIMARY KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID, ExamID),

  FOREIGN KEY (ExamID)

                         REFERENCES Exam,

  FOREIGN KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID)

                        REFERENCES Subject

)

go

CREATE TABLE Room (

  ID              char(18) NOT NULL,

  Type            char(18) NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE Usage (

  RoomID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  PRIMARY KEY (RoomID, TeachPlanID, SpecID, StandardID,

         TeachFormID),

  FOREIGN KEY (TeachPlanID, SpecID, StandardID, TeachFormID)

                        REFERENCES TeachPlan,

  FOREIGN KEY (RoomID)

                        REFERENCES Room

)

go

CREATE TABLE SubjElem (

  ID              char(18) NOT NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE SubjSet (

  ID              char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  Index           char(18) NULL,

  Name            char(18) NULL,

  StanHourQuantity char(18) NULL,

  PRIMARY KEY (ID, StandardID, SpecID, TeachPlanID, TeachFormID),

  FOREIGN KEY (StandardID, SpecID)

                        REFERENCES Standard,

  FOREIGN KEY (TeachPlanID, SpecID, StandardID, TeachFormID)

                        REFERENCES TeachPlan

)

go

CREATE TABLE SetSubjTime (

  SubjSetID       char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  SubjElemID      char(18) NOT NULL,

     StanHourQuantity char(18) NULL,

  PRIMARY KEY (SubjSetID, StandardID, SpecID, TeachPlanID,

         TeachFormID, SubjElemID),

  FOREIGN KEY (SubjElemID)

                        REFERENCES SubjElem,

  FOREIGN KEY (SubjSetID, StandardID, SpecID, TeachPlanID,

         TeachFormID)

                        REFERENCES SubjSet

)

go

CREATE TABLE SubjTime (

  SubjectID       char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  SubjElemID      char(18) NOT NULL,

  StanHourQuantity char(18) NULL,

  PlHourQuantity  char(18) NULL,

  PlHourQuantityWeek char(18) NULL,

  PRIMARY KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID, SubjElemID),

  FOREIGN KEY (SubjElemID)

                        REFERENCES SubjElem,

  FOREIGN KEY (SubjectID, StandardID, TeachPlanID, SpecID,

         TeachFormID)

                        REFERENCES Subject

)

go

CREATE TABLE Semestr (

  Number          char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  WeekQuantity    char(18) NULL,

  PRIMARY KEY (Number, SpecID, TeachPlanID, StandardID,

         TeachFormID),

  FOREIGN KEY (TeachPlanID, SpecID, StandardID, TeachFormID)

                        REFERENCES TeachPlan

)

go

CREATE TABLE Grade (

  Number          char(18) NOT NULL,

  PRIMARY KEY (Number)

)

go

CREATE TABLE TeachElem (

  ID              char(18) NOT NULL,

  Name            char(18) NULL,

  PRIMARY KEY (ID)

)

go

CREATE TABLE TimeElemPlan (

  SpecID          char(18) NOT NULL,

  TeachPlanID     char(18) NOT NULL,

  StandardID      char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  TeachElemID     char(18) NOT NULL,

  GradeNumber     char(18) NOT NULL,

  BeginDate       char(18) NULL,

  EndDate         char(18) NULL,

  PRIMARY KEY (SpecID, TeachPlanID, StandardID, TeachFormID,

         TeachElemID, GradeNumber),

  FOREIGN KEY (GradeNumber)

                        REFERENCES Grade,

  FOREIGN KEY (TeachElemID)

                        REFERENCES TeachElem,

  FOREIGN KEY (TeachPlanID, SpecID, StandardID, TeachFormID)

                        REFERENCES TeachPlan

)

go

CREATE TABLE StandElemen (

  StandartID      char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  ElemID          char(18) NOT NULL,

  HourQuantity    char(18) NULL,

  PRIMARY KEY (StandartID, SpecID, ElemID),

  FOREIGN KEY (ElemID)

                        REFERENCES TeachElem,

  FOREIGN KEY (StandartID, SpecID)

                        REFERENCES Standard

)

go

CREATE TABLE Norms (

  StandardID      char(18) NOT NULL,

  TeachFormID     char(18) NOT NULL,

  SpecID          char(18) NOT NULL,

  TimeLength      char(18) NULL,

  PRIMARY KEY (StandardID, TeachFormID, SpecID),

  FOREIGN KEY (StandardID, SpecID)

                        REFERENCES Standard,

  FOREIGN KEY (TeachFormID)

                        REFERENCES TeachForm

)

go

Шаг 17. Получение запросов пользователей

Регламентные запросы:

1. График учебного процесса и сводные данные по бюджету времени

2. План учебного процесса

Нерегламентные запросы:

1. Перечислить наименование предметов, выносимых на летнюю экзаменационную сессию.

2. Перечислить названия общепрофессиональных дисциплин.

Результаты запросов приведены в приложении.

Приложения

1. В приложениях приводится документы, по которым происходило проектирование БД. Государственный стандарт не прилагается вследствие своего большого объема.

2. Также к курсовому прилагается крупномасштабная модель «сущность-связь» для большей наглядности.

3. Третье приложение состоит из результатов ответов на запросы пользователей.