Спроектировать и создать базу данных Успеваемость Набор данных

Спроектировать и создать базу данных: Успеваемость Набор данных Поле Nsb Тип Числовой Nz Fam Im Otsh Dr Числовой Текстовый Дата/Время Описание Номер студенческого билета Номер зачетной книжки Фамилия студента Имя студента Отчество студента Дата рождения студента

Pol Bit Imdis Текстовой Sem Kol Mark Date Числовой Дата/Время Id_pr Пол студента, 1 мужской, 0 - женский Наименование дисциплины Номер семестра Кол_во часов Оценка Дата сдачи экзамена Числовой Индивидуальный номер преподавателя Fam_pr Текстовый Фамилия преподавателя

Im_pr Otsh_pr Dol Step Zvan Id_kaf Kaf Tel Id_sp Spec Текстовый Текстовой Числовой Текстовый Числовой Текстовой Имя преподавателя Отчество преподавателя Должность Научная степень Звание преподавателя Код кафедры Название кафедры Телефон кафедры Код специальности Название специальности

Ключ: Nsb, Imdis, Sem, Id_sp, Date, Id_pr Неключевые атрибуты: Nz, Fam, Im, Otsh, Dr, Pol, Kol, Mark, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel, Spec Nsb (Nz, Fam, Im, Otsh, Dr, Pol) Студент (Nsb, Nz, Fam, Im, Otsh, Dr, Pol) Успеваемость (Nsb, Imdis, Sem, Id_sp, Date, Id_pr, Kol, Mark, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel, Spec)

Id_pr (Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel) Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel ) Успеваемость (Nsb, Imdis, Sem, Id_sp, Date, Id_pr, Kol, Mark, Spec) Студент (Nsb, Nz, Fam, Im, Otsh, Dr, Pol) Imdis, Sem, Id_sp Kol Учебный_план (Imdis, Sem, Id_sp, kol)

Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel ) Студент (Nsb, Nz, Fam, Im, Otsh, Dr, Pol) Успеваемость (Nsb, Imdis, Sem, Id_sp, Date, Id_pr, Mark, Spec) Id_sp Spec Специальность (Id_sp, Spec) Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel ) Студент (Nsb, Nz, Fam, Im, Otsh, Dr, Pol)

Учебный_план (Imdis, Sem, Id_sp, kol) Успеваемость (Nsb, Imdis, Sem, Id_sp, Date, Id_pr, Mark) Все 5 отношений находятся в 2 НФ. Рассмотрим отношение Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf, Kaf, Tel ) Id_pr Id_kaf (Kaf, Tel) Имеет место транзитивная зависимость Кафедры (Id_kaf, Kaf, Tel) Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf )

Введем код дисциплины Id_dis Предмет (Id_dis , Imdis) Учебный_план (Id_dis , Sem, Id_sp, kol) Таким образом, получим Специальность (Id_sp, Spec) Студент (Nsb, nz, Fam, Im, Otsh, Dr, Pol) Предмет (Id_dis , Imdis) Учебный_план (Id_dis , Sem, Id_sp, kol) Кафедры (Id_kaf, Kaf, Tel) Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf ) Успеваемость (Nsb, Id_dis, Sem, Id_sp, Date, Id_pr, Mark)

Все 7 отношений находятся в 3 НФ Рассмотрим отношение Студент. Здесь два возможных ключа Nsb и Nz. Имеет место зависимость Nz Nsb. Билет_зачетка (Nsb, nz) Студент (Nsb, Fam, Im, Otsh, Dr, Pol ) Специальность (Id_sp, Spec) Предмет (Id_dis , Imdis) Учебный_план (Id_dis , Sem, Id_sp, kol) Кафедры (Id_kaf, Kaf, Tel) Преподаватель (Id_pr, Fam_pr, Im_pr, Otsh_pr, Dol, Step, Zvan, Id_kaf ) Успеваемость (Nsb, Id_is, Sem, Id_sp, Date, Id_pr, Mark)

Все 8 отношений находятся в НФБК Связи между отношениями 1: М Предмет М: 1 Учебный_план 1: М Преподаватель М: 1 Кафедры Успеваемость Специальность 1: М Студент 1: 1 Билет_зачетка

Таблица Билет_зачетка (B_Z) Имя Название атрибута Тип поля Размер поля № студенческого Nsb int билета № зачетной книги nz int Таблица Студент (Stud) Имя Название атрибута Тип поля Размер поля № студенческого Nsb int билета фамилия Fam Varchar 50 имя Im Varchar 50 отчество Otsh Varchar 50 Дата рождения Dr Date. Time Ограничения FK, PK Not null Ограничения PK Not null Не ранее 1967

Таблица Учебный_план (USH_PL) Имя Название атрибута Тип поля Размер поля Код дисциплины Id_dis int Семестр Sem int Код специальности Id_sp int Кол_часов kol int Ограничения PK, FK PK PK, FK Таблица Предмет (Disp) Имя Название атрибута Тип поля Размер Ограничения поля Код дисциплины Id_dis int PK Название дисциплины Imdis Varchar 50

Таблица Специальность (Spec) Имя Название атрибута Тип поля Размер Ограничения поля Код специальности Id_sp int PK Название Imsp Varchar 50 специальности Таблица Кафедры (Kaf) Имя Тип Название атрибута Размер Ограничения поля Код кафедры Id_kaf int PK Not null Название кафедры kaf Varchar 50 Телефон Tel Varchar 10

Таблица Преподаватель (Prep) Имя Название атрибута Тип поля Размер поля Инд. номер препод. Id_pr int фамилия Fam_Pr Varchar 50 имя Im_pr Varchar 50 отчество Otsh_pr Varchar 50 Должность Dol Varchar 50 Звание Zvan Varchar 50 Код кафедры Id_kaf int Ограниче ния PK Not null

Таблица Успеваемость (USPEV) Имя Раз Название атрибута Тип поля Ограничения поля мер № студенческого Nsb int PK, FK билета Код дисциплины Id_dis int PK, FK Код специальности Id_sp int PK, FK Семестр sem int PK Индивидуальный Id_pr int PK, FK номер преподавателя Дата зкзамена Date datetime PK Оценка Mark int от 2 до 5

create database uspevaemost use uspevaemost Ограничения: год рождения раньше, чем 1967 ; mark от 2 до 5. CREATE TABLE stud (nsb int PRIMARY KEY , fam varchar(50) NOT NULL , im varchar(50) NOT NULL , OTSH varchar(50) NULL , dr datetime NOT NULL CHECK(year(dr) >1967) , pol bit NOT NULL )

CREATE TABLE USPEV ( NSB int FOREIGN KEY REFERENCES stud (nsb) , ID_DIS int FOREIGN KEY REFERENCES DISP (ID_DIS) , ID_SP int FOREIGN KEY REFERENCES SPEC (ID_SP) , SEM int , ID_PR int FOREIGN KEY REFERENCES PREP (ID_PR) , DATE datetime , MARK int CHECK (mark>=2 AND mark<=5) , primary key (NSB, ID_DIS, i. D_SP, ID_PR, SEM, DATE) )

Диаграмма базы данных

INSERT INTO stud (nsb, fam, im, OTSH, dr, pol) VALUES (1, 'Иванов', 'Иванович', '02. 1993', 1) Вывести фамилии преподавателей и количество принятых ими экзаменов select fam_pr as фамилия, count(mark) as кол_экзам from prep left join uspev on prep. id_pr=uspev. id_pr group by fam_pr фамилия кол_экзам Арбузов Сахаров Филипов 2 1 0