sql - 基于概念模式编写 sql 脚本

Question

我有以下要求

雇主可以使用其用户名和密码登录系统并创建工作机会。
申请人可以使用其用户名和密码登录系统以查看工作机会。

一个 employer具有 employer_id,name,role 的属性

一个 applicant具有 applicant_id,DOB,Name 的属性

一个 jobOpportunity具有 JobId,Title,Description 的属性

这是我想出的图表。



将图表转换为 sql 脚本:

http://pastebin.com/wUnU5NMT

我不确定我是否根据要求正确编写脚本并绘制图表。

Answer 1

登录表上的键控看起来不正确，即:

 CREATE TABLE Login(
         (employer_id,applicant_id) INT PRIMARY KEY NOT NULL,
         -- Username + password OK
         FOREIGN KEY(applicant_id) REFERENCES Applicant(applicant_id));
         FOREIGN KEY(employer_id) REFERENCES Employer(employer_id));

由于登录将是雇主或申请人，因此这两者的复合键没有意义。

而是为登录创建一个代理键(例如，只是 LoginID INT )
似乎要求将您推向登录、雇主和申请人之间的继承模型。如果这样做，外键将被切换，以便 employer和 applicant FK 到 login.LoginID。您可以为雇主和登录创建新的代理主键，或者使用从登录继承的键作为这些表的主键。 (即，一列可以同时是主键和外键。)我将这样做:

 CREATE TABLE Login(
     (LoginID INT PRIMARY KEY NOT NULL,
     username VARCHAR(45) NOT NULL,
     password VARCHAR(45) NOT NULL);

 CREATE TABLE Employer(
     LoginID INT PRIMARY KEY NOT NULL,
     name VARCHAR(45) NOT NULL,
     ROLE VARCHAR(45) NOT NULL,
     FOREIGN KEY(LoginID) REFERENCES Login(LoginID));

 CREATE TABLE Applicant(
     LoginID INT PRIMARY KEY NOT NULL,
     name VARCHAR(45) NOT NULL,
     DOB VARCHAR(45) NOT NULL,
     FOREIGN KEY(LoginID) REFERENCES Login(LoginID));

CREATE TABLE Job(
     (jobId INT PRIMARY KEY NOT NULL,
     title VARCHAR(45) NOT NULL,
     description VARCHAR(45) NOT NULL,
     ListedByEmployerId INT NOT NULL,
     FOREIGN KEY(ListedByEmployerId) REFERENCES Employer(LoginID));

然而，此模型并不限制工作只能由雇主列出 - 这需要在应用程序中强制执行，或者使用数据库规则、触发器等。 Employer(LoginID) 的外键当然解决这个问题。

该要求没有提及允许申请人申请工作的任何内容，因此没有建模。

但如果他们这样做，您将需要允许许多申请人申请一个工作 - 即一个 Many:Many 连接表。