Уважаемые коллеги!

Хотел с вами посоветоваться по вопросу проектирования одной базы данных, вернее для начала моделирование простой предметной области.

Краткое описание в виде фактов

1. Каждый студент учится в одной и только одной группе
2. Группа включает множество студентов, обычно в некотором предельном диапазоне x<=N<=y
3. Группа имеет номер, состоящий из номера группы(специализации) и номера курса 1-33, 2-42, 4-56, 5-56, 4-33
4. Студенты группы по итогам сессии переводятся на следующий курс: была 1-33 стала 2-33, а студенты вообщем остались :-)
4. Студенты группы в сессию должны сдать ряд экзаменов по предметам. Количество экзаменов ограничено обычно 4-5 за сессию (однако надо понимать, что по каждому изучаемому в течение семестра предмету должна быть выставлена итоговая оценка: зачет, диф.зачет, курсовая, экзамен) В данной задаче мы рассматриваем только результаты экзаменов!
5. оценка может принимать значение 2, 3, 4, 5 и неопределена (то есть студент экзамен не сдавал)

В результате я создал две модели

1. модель концептуальная

2. тоже но с использованием суррогатных ключей

Хотелось бы услышать замечания, пожелания, советы, другие варианты модели данных.

Для большей результативности опишу то что хотелось бы видеть в конечном приложении.

1 форма - Результаты экзаменов: Фио студента, Предмет, Оценка по предмету
2 форма - Список студентов - ФИО студента, Номер группы
3 форма - Группы - Курс, Номер группы
4 форма - Предметы - Название предмета
5 форма - Экзамены по предметам - Номер группы, Название предмета

Я бы еще добавлил Студент -- Курс -- Группа

Я бы еще добавлил Студент -- Курс -- Группа

Тогда уж Курс - (М) Группа - (М) Студент
На самом деле это не так важно, поскольку переход на курс одной и той же группы вряд ли имеет смысл завязывать на ссылочную целосность

в обоих вариантах есть некоторая проблема, сначала надо перевести все группы с последнего курса на некий гипотетический 6, а затем по нисходящей изменить курсы у других.

Можно сделать проще - хранить год поступления т.е. пара год поступления и номер группы уникальны, тогда отображаемый номер группы в стиле Курс-Номер группа будет определяться в приложении, либо автоматом меняться в определенный момент времени

Пока эта ситуация в задаче не актуальна...

Ну да, что-то я перепутал группу с факультетом ...

Эдуард, а чего ты не стал рисовать в UML?

Сразу видно, что у группы неправильно выбран ключ, он зависит от времени. Группа 1-46, учившаяся в 96-м году и группа 1-46, учившаяся в 2001-м году - это разные группы. Получается, ты должен будешь стирать из БД выпускников и ежегодно обновлять идентификаторы групп, переходящих с курса на курс.

Так что спокойно делай идентификатором код специальности и год формирования группы, а курс - атрибутом, возможно производным.

Не понял, почему ты в тексте говоришь "Экзамен", а в диаграмме только "Сессия". Сессия имеет атрибуты - Год и Время года (Зимняя/Летняя). Экзамен - это соответственно ключи на "Предмет", "Группа" и "Сессия", неключевой атрибут - дата проведения. Оценка - имеет ключи на Студент и Экзамен.

Денис, спасибо за ответ и интерес к теме.

Немного поясню предысторию вопроса.

Данная предметная область или лучше сказать исходные таблицы взяты из книги Карповой. "Базы данных". Из-во Питер.

На базе этого примера объясняются некоторые вопросы SQL.

Более того, данный пример положен в основу курса управления данными. Веду не я:-)

Мне с самого начала это пример не понравился. Он конечно не теряет значимости, то его роль ограничена. Тем не менее, положив его в основу курса, преподаватель на мой взгляд, создает предвзятое представление о проетировании БД и управлении данными

Исходная схема такова. Даны три таблицы. Или скорее отношения:
Студенты-Группы R1 (Фио студента, Номер группы)
Группы-Предметы R2 (Номер группы, Название предмета)
Итоги сесии R3 (Фио студента, Название предмета, Оценка)

Повторяю для определенных целей - скажем изучения SQL, такая схема вполне нормальна. Но она слишком оторвана от реальной практики на мой взгляд.

Я решил ее немного улучшить, без внесения особо сложных моментов. Таких как факт перехода с курса на курс. Хотя возможно, такое упрощение только вредит делу.

Кстати задачу можно решать и с помощью UML, если привязать сюда учебные аспекты связанные с использованием MDA (Delphi BOLD).

Твоя схема мне понравилась, надо внимательно ее изучить:-)

Хм, кажется тему кто-то подправил?

Кажется, имеет смысл сузить исходную задачу.

То есть я не ставлю себе целью получения такой модели данных, которая позволяет хранить и управлять информацией о всех экзаменационных сессиях.

Постулируем тот факт, что от сессии до сессии живут суденты весело, а сессия, а сессия всего два раз в год!

Итак 1 раз в полгода происходит заполнение некоторой БД  фактами о сдачи экзаменов. Что бы не забираться в глубины предметной области и остаться на самом простом уровне восприятия задачи предположим, что результаты сессии - архивируются и помещаются на хранение, при архивировании во все сущности добавляется год и время года. Одновременно, происходит создание новой БД с переносом данных по студентам - группам (номер курса в номере группы у студентов увеличивается на 1). Тогда таблица скажем Группа-Предмет  остается без изменения, т.к. она фактически хранит учебный план подготовки для каждой группы (вернее список предметов, которые должны вообще сдаваться за учебный год) Тогда наверное имеет смысл добавить атрибут: семестр.

Итак, имеющаяся "базовая" структура данных не устраивает тем, что при реализации приложения, программисту приходится довольно серьезно продумывать вопросы поддержания целостности. Например: при реализации формы Экзамены(итоги сессии) я к примеру делаю два поля - полями combobox - к ним lookup-таблица, соотвествующая. Эти поля есть ФИО и ПРЕДМЕТ. Какие сразу возникают проблемы:
1. В списке студентов ВСЕ студенты - а следовательно масса однофамильцев может быть (вводить скажем номер зачетки - выход из положения - но не хочется)
2. в списке предметов будут повторяющиеся предметы, если только  не делать источником данных запрос
3. Никто не гарантирует, что студент группы 2-45, будет вдруг сдавать экзамен по предмету, который читают только для группы 5-45 или 1-23

Как можно разрешить проблему?
1. в combobox вытаскивать поле ФИО студента и Номер группы (№ группы - скрытое)
2. в combobox аналогично вытаскивать Предмет и Номер группы (№ группы скрытое)
Очевидно что лучше использовать не сами lookup-table а запросы параметрические.
Далее вешаем на combobox соотвествующие процедуры-события, когда при выборе чего-то в одном поле, второе поле автоматом фильтруется

Таким образом - задача в составление такой схемы БД, которая максимально полно использует стандартные механизмы поддержания целостности

Насчет ведомости.

Вне всякого сомнения такая сущность вполне могла бы существовать.
С другой стороны, ведомость есть некий документ или отчет, имеющий определенную структуру и являющийся результатом композиции данных.
Т.е. ведомость может формироваться как отчет.

Но это может быть и первичный документ с разными состояниями: черновик и учтена.
Черновик может формироваться автоматически, а перервод в состояние учетна будет происходить после, когда будет внесены оценки.

Кстати, надо отметить, что вопрос не имеет отношения к целостности. По моему тут как раз нечеткое представление о том, как лучше сделать интерфейсную часть. Вот если бы мы расписали бы сценарии для работы с формами, то можно было бы говорить о чем-то.

Во все нет. У меня вполне четкое представление об интерфейсной части. Тут нет никаких проблем.
Насчет целостности все достаточно прозрачно. Посмотрим на мои модели, они практически равнозначны.
Таблица связи между ГРУППОЙ и ПРЕДМЕТОМ фактически формирует список предметов, которые должна сдавать определенная группа. Эта промежуточная таблица соединена с таблицей СЕССИЯ, соотвественно по моему убеждению, в нею при выборе студента, будет попадать информация о предметах для которых группы совпадают. В этом я и вижу ораничение целостности

Мне, например, кажется, что в приведенном предложении выбирать фамилию студента из списка кроется нарушение принципа однократности ввода информации.

А вот этого тезиса не понял совсем. Какое такое нарушение принципа одноератности ввода тут нарушается. Поясни свои доводы, я их не понимаю
Смотри: мы имеем таблицу-сущность СТУДЕНТЫ(idstud, FIO)
ПРЕДМЕТЫ(idpred, Название)
Далее имеем отношение Итоги сессии(idstud, idpred, Оценка)
В форме я делаю для первых двух полей поля подстановки, подставляется то id, а  отображается ФИО - никаого нарушения однократности ввода нет

Данная предметная область или лучше сказать исходные таблицы взяты из книги Карповой. "Базы данных". Из-во Питер. На базе этого примера объясняются некоторые вопросы SQL. Более того, данный пример положен в основу курса управления данными. Веду не я:-)

Мне с самого начала это пример не понравился. Он конечно не теряет значимости, то его роль ограничена. Тем не менее, положив его в основу курса, преподаватель на мой взгляд, создает предвзятое представление о проетировании БД и управлении данными

Исходная схема такова. Даны три таблицы. Или скорее отношения:
Студенты-Группы R1 (Фио студента, Номер группы)
Группы-Предметы R2 (Номер группы, Название предмета)
Итоги сесии R3 (Фио студента, Название предмета, Оценка)

Имеет место плохое понимание того, что есть отношения. (Фамилия, Имя, Отчество) уже образуют отношение. И отношение это называется Студент. То, что в силу мощности связи Студент-Группа 1-ко-многим имеет смысл в Отношение студент при переходе к таблицам добавить столбец-ссылку на Группу, не должно приводить к переименованию Отношения, а теперь таблицы Студент.

Повторяю для определенных целей - скажем изучения SQL, такая схема вполне нормальна. Но она слишком оторвана от реальной практики на мой взгляд.

Ой, так уж и нормальна. Попробуй в системе, построенной по такой модели, ответить на простой вопрос - "каково соотношение Иванов и Олегов среди студентов вуза?", "Сколько однофамильцев, сколько возможных братьев и сестёр?". А информация там есть.

Я решил ее немного улучшить, без внесения особо сложных моментов. Таких как факт перехода с курса на курс. Хотя возможно, такое упрощение только вредит делу.

Именно.

Кстати задачу можно решать и с помощью UML, если привязать сюда учебные аспекты связанные с использованием MDA (Delphi BOLD).

UML имеет ценность и без MDA, а как средство моделирования, способное выражать больше семантики, нежели ERM.

Кажется, имеет смысл сузить исходную задачу.

То есть я не ставлю себе целью получения такой модели данных, которая позволяет хранить и управлять информацией о всех экзаменационных сессиях.

В процессе концептуального моделирования предметной области имеет смысл выделить максимальное количество Классов, и только потом, при переходе к проектированию, сокращать их количество и всячески реорганизовывать.

Итак 1 раз в полгода происходит заполнение некоторой БД  фактами о сдачи экзаменов. Что бы ... остаться на самом простом уровне восприятия задачи предположим, что результаты сессии - архивируются и помещаются на хранение, при архивировании во все сущности добавляется год и время года. Одновременно, происходит создание новой БД с переносом данных по студентам - группам (номер курса в номере группы у студентов увеличивается на 1).

Клёво, добавляются новые атрибуты, есть отдельное хранилище - архив, создаётся новая БД, туда переносятся данные - и всё это ты называешь упрощением? А не проще ли было добавить к Экзамену и Сессии атрибут Состояние, который для Экзамена будет принимать значения "Планируется", "Проведён", а для Сессии - "Планируется", "Активная", "Закрытая"? И переключая состояния, получать нужные выборки?

1. В списке студентов ВСЕ студенты - а следовательно масса однофамильцев может быть (вводить скажем номер зачетки - выход из положения - но не хочется)

Вообще-то экзамен сдаётся не каждым студентом по отдельности, а группой, поэтому к моменту указания студента группа уже должна быть выбрана.

Таким образом - задача в составление такой схемы БД, которая максимально полно использует стандартные механизмы поддержания целостности

Я бы сказал - задача просто правильно спроектировать БД

Возможно, чего-то не понимаю я. Если не трудно, нарисуй формы, о которых ты говоришь и напиши сценарий, кудпа жать и что получится.

Нарисованные тобою формы вполне подходят и первы и второй вариант. Пока используется первый, но естесвтенно оценки не выбираются из списка. На самом деле в этом нет необходимости

Проблему я вижу в том, что представление об интерфейсной части появились до представлений о сценарии работы пользователя и его интересах вообще.

Во все нет. Такого как раз не было. Однако такая иллюзия возникает потому, что исходная схема уже реализованная в учебном практическом задании.
Цель преподавания(сформулированная не мною) - ознакомить с основными приемам разработки приложения для работы с базой данных: собственно проектирование таблиц, создание форм разного назначения, формирование запросов, подключение внешних таблиц, внешние динамические соединения, отчеты. Т.е. упор делается на RAD. Инструмент  FoxPro. К сожалению процессу не предшествует моделирование данных и исследование предметной области как таковой. Все что известно - это уже представленные отношения с неким набором данных. Концепция приложения - это уже выдумка преподавателя и его видение этой задачи. Я совсем не сомневаюсь в его компетенции, но какмне кажется вопросы управления данными через приложение маскируют важность задачи моделирования предметной области.

Примером тому ваша активная критика моих рассуждений.

Моя задача - не внося существенных изменений в предложенную схему, сделать ее более правильной и прозрачной для использования.

Ниже прилагаются формы приложения.

Имеет место плохое понимание того, что есть отношения. (Фамилия, Имя, Отчество) уже образуют отношение. И отношение это называется Студент.

Денис, мне не нравится такое утверждение: имеет место плохое понимание.
Во первых если ты внимательно изучил схему и мои записи, то я нигде не утвержадю что Студент это Фамилия, Имя, Отчество. Я пишу лишь, что студент это ФИО. А если студент имеет один атрибут ФИО - то это множество (вернее мультимножество, если учесть тезок, хотя для простоты задачи мы полагаем что нет полных тезок), а не отношение.
Я прекрасно понимаю, что такое отношение. И сущность по своей сути есть всегда отношение, так же как и связи между двумя сущностями называются бинарным отношением.

Ой, так уж и нормальна. Попробуй в системе, построенной по такой модели, ответить на простой вопрос - "каково соотношение Иванов и Олегов среди студентов вуза?", "Сколько однофамильцев, сколько возможных братьев и сестёр?". А информация там есть.

Не надо умножать сложности там где ее нет. А соотношение Иванов и Олегов при желании можно сделать и на существующей схеме. А пробратьев и сестер - это уже твои придумки.

Горе с этими аналитиками - сразу начинают навязывать тебе свое представление о мире, полагая, что их представление самое, что не на есть правильное:-)
Не нужно мне в данной задаче знать, братья али сестра, нет нужды знать сколько однофамильцев и сколько Олегов.
Нужно было бы так и было бы.
Еще раз говорю, документируется задача в пределах 1 сессии. Все - небольше, неменьше.
Никто не отрицает правильности всех ваших замечаний, если говорить о реальной предметной области и реальной задаче учета сессии, работы деканата (про работу деканата мы еще по говорим, но в другом месте:-)

UML имеет ценность и без MDA, а как средство моделирования, способное выражать больше семантики, нежели ERM.

Да не сомневаюсь я в ценности UML  без MDA. Бог с тобой. Насчет семантической мощности тоже не спорю. ERM используется как инструмент, с которым студенты знакомы. А с UML еще пока нет

Вообще-то экзамен сдаётся не каждым студентом по отдельности, а группой, поэтому к моменту указания студента группа уже должна быть выбрана.

вообще-то оценку получет студент, а не группа. группа просто структурная единица определяющая набор дисциплин, по которым сдается экзамен студентом этой группы...

Я бы сказал - задача просто правильно спроектировать БД

Правильно, но исходя лишь из тех понятий, что я уже изложил.

Моя задача - не внося существенных изменений в предложенную схему, сделать ее более правильной и прозрачной для использования.

Тогда почему ты называешь тему "Проектирование БД", а не "Реинжиниринг БД", "Рефакторинг БД" или "Реорганизация БД"?