Представление “X из Y” в SQL
В моей базе данных у меня есть много курсов, которые обязательны. Некоторые являются избирательными. Однако существуют курсы третьего вида: список, из которого необходимо выбрать X курсов. Список (и номер X) отличается для каждой учебной программы. Как Вы представили бы это реляционным образом?
8 ответов
Мне кажется интересным, что в принятом ответе говорится: "Нет способа реляционно представить 'X of Y'", хотя это по сути то, о чем спрашивалось в вопросе. Мне кажется, что "X of Y" действительно можно смоделировать (и в значительной степени реализовать) с помощью SQL, и вот предлагаемый способ:
Пример сценария: студенты, изучающие курс "Французский язык", должны выбрать два компонента (x) из трех возможных компонентов (y).
CREATE TABLE Components
(
component_name VARCHAR(100) NOT NULL,
UNIQUE (component_name)
);
INSERT INTO Components (component_name) VALUES
('Oral'),
('Writing'),
('Vocab'),
('Databases');
Очевидно, что "Базы данных" не входят в курс французского языка, поэтому нам нужны таблицы для разработчиков курсов, чтобы моделировать курсы (эти таблицы имеют много соответствующих ключей-кандидатов, поэтому для ясности я определяю их в "нижней части" оператора CREATE TABLE):
CREATE TABLE XofYCourses
(
course_name VARCHAR(100) NOT NULL,
x_components_choice_tally INTEGER NOT NULL
CHECK (x_components_choice_tally > 0),
y_components_tally INTEGER NOT NULL
CHECK (y_components_tally > 0),
CHECK (x_components_choice_tally < y_components_tally),
UNIQUE (course_name),
UNIQUE (course_name, y_components_tally),
UNIQUE (course_name, x_components_choice_tally)
);
INSERT INTO XofYCourses (course_name, y_components_tally,
x_components_choice_tally) VALUES
('French', 2, 3);
Приведенное выше позволяет нам моделировать атрибут "два из трех" курса французского языка. Теперь нам нужна таблица для моделирования трех возможных компонентов этого курса:
CREATE TABLE XofYCourseComponents
(
course_name VARCHAR(100) NOT NULL,
y_components_tally INTEGER NOT NULL,
FOREIGN KEY (course_name, y_components_tally)
REFERENCES XofYCourses (course_name, y_components_tally),
component_sequence INTEGER NOT NULL
CHECK (component_sequence > 0),
component_name VARCHAR(100) NOT NULL
REFERENCES Components (component_name),
CHECK (component_sequence <= y_components_tally),
UNIQUE (course_name, component_sequence),
UNIQUE (course_name, component_name)
);
INSERT INTO XofYCourseComponents (course_name,
component_sequence, y_components_tally, component_name)
VALUES
('French', 1, 3, 'Oral'),
('French', 2, 3, 'Writing'),
('French', 3, 3, 'Vocab');
Теперь о зачислении. Билли хочет пройти курс французского языка...
CREATE TABLE Students
(
student_name VARCHAR(20) NOT NULL,
UNIQUE (student_name)
);
INSERT INTO Students (student_name) VALUES ('Billy');
...и выбирает "Устный" и "Vocab":
CREATE TABLE XofYCourseComponentChoices
(
student_name VARCHAR(20) NOT NULL
REFERENCES Students (student_name),
course_name VARCHAR(100) NOT NULL,
x_components_choice_tally INTEGER NOT NULL,
FOREIGN KEY (course_name, x_components_choice_tally)
REFERENCES XofYCourses (course_name, x_components_choice_tally),
component_name VARCHAR(100) NOT NULL,
FOREIGN KEY (course_name, component_name)
REFERENCES XofYCourseComponents (course_name, component_name),
x_component_sequence INTEGER NOT NULL
CHECK (x_component_sequence > 0),
CHECK (x_component_sequence <= x_components_choice_tally),
UNIQUE (student_name, course_name, component_name),
UNIQUE (student_name, course_name, x_component_sequence)
);
INSERT INTO XofYCourseComponentChoices (student_name, course_name,
component_name, x_component_sequence, x_components_choice_tally)
VALUES
('Billy', 'French', 'Oral', 1, 2),
('Billy', 'French', 'Vocab', 2, 2);
Приведенная выше структура хорошо работает для обеспечения максимальных значений, т.е. не более трех компонентов для курса французского языка и не более двух вариантов для каждого студента.
Однако этот способ не позволяет обеспечить точное количество, например, чтобы Билли не выбрал только один компонент. Стандартный SQL имеет решения этой проблемы, например, ограничения CHECK, которые поддерживают подзапросы (например, чтобы подсчитать, что для Билли есть в общей сложности две строки...) и ограничения DEFERRABLE (...но задерживают подсчет до момента фиксации транзакции). Наличие функции "множественного назначения" было бы еще лучше. Однако большинство продуктов SQL не имеют таких возможностей.
Означает ли отсутствие поддержки полного решения, что мы ничего не делаем и просто верим, что приложение воздержится от записи недопустимых данных? Конечно, нет!
Хорошим промежуточным подходом является отмена привилегий для базовых таблиц и предоставление вспомогательных хранимых процедур, например, одной для зачисления студента, которая принимает выбранные им компоненты курса в качестве параметров: подсчет производится после INSERTов, и если он нарушает правила данных (например, меньше двух для французского), то транзакция откатывается и возвращается ошибка.
У вас есть два варианта: вы можете смоделировать данные ближе к реальности, где некоторые требования - это требования одного курса, а другие - требования X из Y курсов, или вы можете смоделировать все требования как X из Y, где требования одного курса - это требования "1 из 1".
Я бы рекомендовал что-то вроде этого:
Course
---------------
CourseID
Description
...
Program
---------------
ProgramID
Description
...
CourseGroup
---------------
CourseGroupID
CourseID
ProgramCourseGroup
---------------
ProgramID
CourseGroupID
RequiredCourses
Course и Program - это две таблицы верхнего уровня. Они определяют простой список всех курсов и программ, соответственно, без каких-либо отношений между ними.
CourseGroup определяет группу курсов. Она связана с Course, но ни с какими другими таблицами.
ProgramCourseGroup связывает группы курсов с программами. Программа указывает, что требуется определенная группа курсов, тогда RequiredCourses указывает, сколько курсов из этой группы необходимо пройти, чтобы выполнить требование.
Например, у вас есть программа "Плетение корзин", которая требует:
- Введение в корзины
- Основные техники плетения
и два из следующих четырех курсов:
- Пасхальные корзины
- Ручные корзины
- Корзины для пикника
- SCUBA Diving
Ваши данные будут выглядеть так:
Course
------------------------------------
CourseID Description
1 Intro to baskets
2 Basic weaving techniques
3 Easter baskets
4 Handbaskets
5 Picnic baskets
6 SCUBA Diving
Program
--------------------------
ProgramID Description
1 Basket Weaving
CourseGroup
--------------------------
CourseGroupID CourseID
1 1
2 2
3 3
3 4
3 5
3 6
ProgramCourseGroup
-----------------------------------------
ProgramID CourseGroupID RequiredCourses
1 1 1
1 2 1
1 3 2
В принципе можно было бы ожидать, что можно будет создать такое ограничение, чтобы обеспечить соблюдение правила:
CHECK
(NOT EXISTS
(SELECT 1
FROM CourseEnrolement c, ProgramEnrolement p
WHERE c.StudentId = p.StudentId
AND c.ProgramId = p.ProgramId
GROUP BY p.StudentId, p.ProgramId, p.NumberOfCoursesRequired
HAVING COUNT(*) <> p.NumberOfCoursesRequired
))
К сожалению, SQL делает это практически невозможным, или, по крайней мере, делает это очень сложно обновить базу данных, пока действует ограничение. Поэтому, если вы действительно хотите представить такое правило в базе данных, вам может потребоваться лучшая модель, чем может предложить SQL. На практике такие правила часто применяются в коде приложения.
|--------| |-----------------| |---------------|
|Program | |CoursesByProgram | |Courses |
|------ | |-----------------| |---------------|
|Id PK |---------|ProgramId PK | |CourseId PK |
|Name | |CourseId PK |-------------|Name |
|--------| |ListId FK NULL | |---------------|
|IsCompulsory bit|
|IsElective bit|
-------------------
|
|
|-----------------|
|List |
|-----------------|
|Id PK |
|Name |
------------------
Я бы сделал следующее:
Сначала создайте таблицу курсов:
courseId courseName IsCompulsory OptionalCoursesId
======== ========== ============ ================
Затем вторую таблицу для необязательных курсов:
OptionalcourseId courseName etc etc etc
================ ========== === === ===
по сути, вторая таблица - это таблица ссылок, которая содержит все необязательные варианты.
Пример:
courseId courseName IsCompulsory OptionalCoursesId
======== ========== ============ ================
1 science 0 NULL
2 IT 0 2
в таблице optionalCourses:
OptionalcourseId courseName
================ ==========
2 SQL
2 C#
2 Java
5 Extreme Ironing
5 Native Julu dancing
Надеюсь, это имеет смысл
Здесь нужны 3 таблицы: StudyPrograms, Courses и Components. Components представляет курсы, которые входят в каждую StudyProgram, и является связующей таблицей между Courses и StudyPrograms.
Каждая запись компонента может содержать поле, указывающее, является ли курс обязательной частью учебной программы. Вы также можете включить поле, указывающее, является ли курс одним из списка, который можно выбрать.
Не существует способа реляционно представить "X из Y", вам понадобится логика в ваших хранимых процедурах, чтобы обеспечить выполнение этого бизнес-правила (или, возможно, на уровне кода доступа к данным, в зависимости от того, как вы хотите организовать приложение).
Я бы определил одну таблицу Курсы , одну таблицу Компоненты , одну таблицу Программы и таблицы ссылок между Курсами и Компоненты и между Компонентами и Программами . Таблица связей между программами и Компонентами также должна иметь столбец, указывающий, сколько кредитов из связанной программы требуется из связанного компонента.
Например, у вас может быть математический компонент, в котором у вас есть такие курсы, как алгебра, тригонометрия, исчисление I и II и т. Д. - это указано в записях в таблице ComponentCourses .
Затем у вас может быть как программа Physics , так и программа Chemistry , для которых требуются математические классы.Если Физика требует 6 баллов по математике, а Химия требует 4, вы указываете это с помощью значения Кредитов в таблице ProgrammeComponents .
Чтобы узнать, соответствует ли определенный набор курсов требованиям программы к получению диплома, просто просуммируйте кредитные значения курсов по каждому компоненту и посмотрите, соответствуют ли они требуемым значениям в таблице ProgrammeComponents .
Пример:
Ниже приведен пример табличных данных, которые определяют две программы, физику и химию, и их отношения с тремя курсами математики и курсом механики. Требования к студентам будут следующими:
- Студенты-физики должны будут выбрать не менее 3 кредитов по математике из 4 доступных.
- Студенты-химики должны будут выбрать не менее 2 зачетных единиц по математике.
- Студенты-физики могут по желанию пройти курс механики.
Как видите, эта схема действительно гибкая в том, что касается определения «корзин» курсов, которые вы можете «выбрать и выбрать» с минимальным количеством (0 <требуемых кредитов в компоненте <общее количество кредитов в компоненте), полностью необязательно курсы (обязательные баллы = 0), а также обязательные курсы (обязательные баллы = общее количество баллов по компоненту).
Схема:
Courses Components
******* **********
CourseId | CourseName | Credits ComponentId | ComponentName
1 | Algebra | 1 1 | Math
2 | AP Algebra | 2 2 | Physics
3 | Trigonometry | 1
4 | Mechanics | 1
ComponentCourses Programmes
**************** **********
CourseId | ComponentId ProgramId | ProgramName
1 | 1 1 | Physics
2 | 1 2 | Chemistry
3 | 1
4 | 2
ProgrammeComponents
ProgrammeId | ComponentId | RequiredCredits
1 | 1 | 3
2 | 1 | 2
1 | 2 | 0
Вы можете рассматривать эти курсы как «набор», и затем создайте отдельную таблицу COURSES_SET с идентификаторами наборов. У ваших курсов может быть оптический set_id , который ссылается на таблицу наборов курсов. Это всего лишь один способ сделать это ...
Пример:
COURSES_SET
CourseSetID Name
----------- ---------------
1 Early Renaissance medical techniques
2 Jurassic theological certificate program
3 Mad Science
COURSES
CourseID Name CourseSetID CourseSequenceNumber
-------- -------------------- ----------- ---------------------
1001 The joys of leeches 1 1
2011 How to keep your
patient from dying 1 2
1700 Is there a T-Rex?
Arguements for and
Against 2 1
1301 Intro to Algorithms (NULL) (NULL)
3301 Cackling: An advanced 3 3
course
Эта модель позволяет вам связать курс с набором (хотя вы также можете назвать его «программой») курсов, а также присвойте порядковый номер, чтобы учащиеся читали их в правильном порядке. Если бы можно было пройти два курса одновременно (последовательность не важна между ними), вы могли бы присвоить им одинаковый порядковый номер.
Тогда у вас может быть отдельная таблица STUDY_PROGRAM , на которую ссылается COURSES_SET , чтобы вы знали, к какой учебной программе принадлежит набор курсов.