sql - 在SQL中表示 “X of Y”

Question

在我的数据库中，我有很多必修课。有些是选修课。但是，还有第三类类(class):您必须从中选择X个类(class)的列表。每个学习计划的列表(和数字X)是不同的。您将如何用关系表示呢？

Answer 1

我发现有趣的是，被接受的答案说:“根本没有办法用关系表示'X of Y'”，而这本质上就是这个问题所要的。在我看来，“Y的X”确实可以使用SQL建模(并在很大程度上执行)，这是一种建议的方式:

示例场景:参加“法语”类(class)的学生必须从总共三个可能的成分(y)中选择两个成分(x)。

CREATE TABLE Components
(
 component_name VARCHAR(100) NOT NULL, 
 UNIQUE (component_name)
);

INSERT INTO Components (component_name) VALUES 
('Oral'), 
('Writing'), 
('Vocab'), 
('Databases');

显然，“数据库”并不属于法语类(class)，因此我们需要一个供类(class)设计者用来对类(class)进行建模的表(这些表具有许多相关的候选键，因此为了清楚起见，我将它们在 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);

上面的内容使我们可以对法语类(class)的“三分之二”属性进行建模。现在我们需要一个表格来模拟该类(class)的三个可能组成部分:

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');

...并选择“口头”和“词汇”:

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);

上述结构是加强最大值的一种好方法，即法语类(class)的组成不得超过三个组成部分，每个学生的选择不得超过两个选择。

但是，它没有做的是确保准确的金额，例如比利不只选择一种成分。标准SQL可以解决此问题，例如支持子查询的 CHECK约束(例如，计算Billy总共有两行...)和 DEFERRABLE约束(...但将计数推迟到提交事务时为止)。具有“多重分配”功能会更好。但是，大多数SQL产品没有这些功能。

缺少对完整解决方案的支持是否意味着我们什么也不做，只是相信应用程序将不会写入无效数据？当然不是!

一个好的临时方法是从基表中撤消特权，并提供辅助存储过程，例如:招收一名以所选类(class)组成部分作为参数的学生:该计数在 INSERT之后执行，如果它违反数据规则(例如，法语少于2个)，则交易将回滚并返回错误。