我们在上一篇中介绍了 Join，接下来我们将介绍数据库设计。

1. The SurgeTech Conference

假如你是一个名为 SurgeTech 会议的管理人员，今天老大叫你创建一个数据库来管理参会人员（attendees），公司（companies），演讲（presentations），会议室（rooms）以及演讲者（oresentation attendance），你该怎么来设计呢？

首先，想一下这些不同的对象，应该如何转换为结构化的数据，这看起来很麻烦，但再麻烦的问题都可以化成许许多多的小问题。

1.1 Attendee

参会人员指的就是那些需要来签到的人员（包含了部分的 VIPs），需要登记到每个参会人员的 ID、姓名、手机号、邮箱以及是否 VIP：

图1 Attendee 表

1.2 Company

参会的公司也需要记录，包含公司的 ID、公司名、公司简介以及联系人（联系人也应该包含在上方的参会人员）：

图2 Company 表

1.3 Presentation

一些公司会在特定的时间进行演讲（包含了演讲开始时间和结束时间），因此需要记录每场演讲的公司 ID 以及会议室 ID：

图3 Presentation 表

1.4 Room

会议室的编号、楼层以及座位数需要记录：

图4 Room 表

1.5 Presentation Attendance

如果参会者对某个公司的演讲感兴趣，他们可以申请一张入场券。为了实现这个功能，就需要制作一张听众表，来记录入场券编号、演讲编号以及听众编号：

图5 Presentation Attendance 表

2. 主键与外键

2.1 主键

对于一个表，主键（Primary Key） 是必不可少的。主键能够一一对应到每一行数据，通常是某一列不重复的数据（或者某几列）。比如 ATTENDEE_ID 就是在表 ATTENDEE 的主键，而 COMPANY_ID 是表 COMPANY 的主键。

由于主键中的不允许有重复，因此也起到着确保数据完整性的约束作用。不可重复意味着在表 ATTENDEE 中不能出现两个 ATTEND_ID 为 2 的数据，否则将会导致错误。

如果某几列数据作为一个整体充当表的主键，那么就要求这几列数据的组合不能够重复。举个例子，如果你将 REPORT_ID 和 APPROVER_ID 设为主键，那么就不能出现有相同 REPORT_ID 和 APPROVER_ID 的记录

2.2 外键

主键（Primary Key）出现在父表（Parent Table）中，而外键（Foreign Key）则出现在子表（Child Table）中。子表中的外键指向父表中的主键，举个例子。ATTENDEE_ID 是 ATTENDEE 表的主键，但在 PRESENTATION_ATTENDANCE 表中 ATTENDEE_ID 则是一个外键。这两个联合在一起就成为了一个一对多的关系，而不像主键，外键并不要求唯一性，因为它就是一对多关系中的多。

主键和外键并不需要有相同的列名，如子表 PRESENTATION 中的外键 BOOKED_COMPANY_ID 指向父表 COMPANY 中的主键 COMPANY_ID
。

3. The Schema

在了解了主键和外键后，我们可以将这五个表画成一个 Database Schema，如下图所示。一个 Database Schema 展示了所有的表、列以及以及它们的关系。所有的主键和外键都用箭头连接起来了，这些箭头展示了父表与子表之间的联系。

在数据库中，schema（模式）是数据库的组织和结构^[1]

图6 The SurgeTech Conference Database Schema

如果你直接看这个图的话可能会有点晕，但再复杂的结构都可以分成几个简单的部分。当你用 SELECT 写查询时，最多也就用到两三个表，所以你可以先看一部分，这样会容易一点：

图7 父表 ATTENDEE 与子表 COMPANY 的关系图

4. 创建数据库

• 使用Jupyter Notebook 运行 SQL 语句需安装 ipython-sql

• %sql 以及 %%sql 为在 Notebook 中运行 SQL 语句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

载入 SQL 以及连接 SQLite：

%load_ext sql

我们已经设计好整个数据库的结构了，现在就可以看是创建数据库了，运行下面的代码，你就可以在你的当前路径的 DataBase 文件夹中产生一个名为 surgetech_conference2.db 的数据库文件：

%sql sqlite:///DataBase/surgetech_conference2.db

'Connected: @DataBase/surgetech_conference2.db'

4.1 创建表格

图8 Company 表

以刚才的 Company 表为例，我们希望：

• COMPANY_ID 是一个整形（INTERGER）数据，并且将其设为主键（PRIMARY KEY），同时能够自动标号（AUTOINCREMENT），即第一个 COMPANY_ID 为 1 ，第二个 COMPANY_ID 为 2，以此类推；
• NAME 是长度不超过 30 的字符数据（VARCHAR）并且不能为缺失值（NOT NULL）；
• DESCRIPTION 是长度不超过 60 的字符数据（VARCHAR）；
• PRIMARY_CONTACT_ID 是一个整形（INTERGER）的数据并且不能为缺失值（NOT NULL）；

%%sql
CREATE TABLE COMPANY 
(COMPANY_ID        INTEGER      PRIMARY KEY AUTOINCREMENT, NAME              VARCHAR(30)  NOT NULL,DESCRIPTION       VARCHAR(60),PRIMARY_CONTACT_ID INTEGER     NOT NULL
);

 * sqlite:///DataBase/surgetech_conference2.db
Done.[]

同理，我们可以继续创建其他的表格：

%%sql
CREATE TABLE ROOM 
(ROOM_ID       INTEGER PRIMARY KEY AUTOINCREMENT, FLOOR_NUMBER  INTEGER NOT NULL,SEAT_CAPACITY INTEGER NOT NULL
);CREATE TABLE PRESENTATION 
(PRESENTATION_ID   INTEGER PRIMARY KEY AUTOINCREMENT, BOOKED_COMPANY_ID INTEGER NOT NULL,BOOKED_ROOM_ID    INTEGER NOT NULL,START_TIME        TIME,END_TIME          TIME
);CREATE TABLE ATTENDEE 
(ATTENDEE_ID   INTEGER PRIMARY KEY AUTOINCREMENT, FIRST_NAME    VARCHAR (30) NOT NULL, LAST_NAME     VARCHAR (30)  NOT NULL,PHONE         INTEGER,EMAIL         VARCHAR (30),VIP BOOLEAN   DEFAULT (0) -- 默认值为0);CREATE TABLE PRESENTATION_ATTENDANCE 
(TICKET_ID       INTEGER PRIMARY KEY AUTOINCREMENT, PRESENTATION_ID INTEGER,ATTENDEE_ID     INTEGER
);

 * sqlite:///DataBase/surgetech_conference2.db
Done.
Done.
Done.
Done.[]

你可以查看下是否创建成功：

%%sql
select * from sqlite_master where type = "table";

 * sqlite:///DataBase/surgetech_conference2.db
Done.

type	name	tbl_name	rootpage	sql
table	COMPANY	COMPANY	2	CREATE TABLE COMPANY ( COMPANY_ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR(30) NOT NULL, DESCRIPTION VARCHAR(60), PRIMARY_CONTACT_ID INTEGER NOT NULL )
table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)
table	ROOM	ROOM	4	CREATE TABLE ROOM ( ROOM_ID INTEGER PRIMARY KEY AUTOINCREMENT, FLOOR_NUMBER INTEGER NOT NULL, SEAT_CAPACITY INTEGER NOT NULL )
table	PRESENTATION	PRESENTATION	5	CREATE TABLE PRESENTATION ( PRESENTATION_ID INTEGER PRIMARY KEY AUTOINCREMENT, BOOKED_COMPANY_ID INTEGER NOT NULL, BOOKED_ROOM_ID INTEGER NOT NULL, START_TIME TIME, END_TIME TIME )
table	ATTENDEE	ATTENDEE	6	CREATE TABLE ATTENDEE ( ATTENDEE_ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRST_NAME VARCHAR (30) NOT NULL, LAST_NAME VARCHAR(30) NOT NULL, PHONE INTEGER, EMAIL VARCHAR (30), VIP BOOLEAN DEFAULT (0) )
table	PRESENTATION_ATTENDANCE	PRESENTATION_ATTENDANCE	7	CREATE TABLE PRESENTATION_ATTENDANCE ( TICKET_ID INTEGER PRIMARY KEY AUTOINCREMENT, PRESENTATION_ID INTEGER, ATTENDEE_ID INTEGER )

参考资料

[1] 我是康小小.数据库中的Schema是什么？[EB/OL].https://blog.csdn.net/u010429286/article/details/79022484, 2018-01-10.

[2] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 67-89

相关文章：

SQL | 目录

SQLite | Select

SQLite | Where

SQLite | Group by and Order by

SQLite | CASE Statements

SQLite | Join

SQLite | SQLite 与 Pandas 比较篇之一