大学数据库创建与查询实战——数据库表设计

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

在MySQL中，使用CREATE TABLE语句创建表，语法格式如下：

USE 数据库名 CREATE TABLE 表名 (

列名 类型（大小）

列名 类型（大小）

.......

)；

【注意】主键必须包含唯一的值；主键列不能包含NULL值；每个表有且只能有一个主键

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1

PRIMARY KEY关键字用于定义列为主键，可以使用多列来定义主键，列间以逗号分隔

MySQL命令终止符为分号；

FOREIGN KEY外键（既可以定义列也可以定义表）

CHECK约束用于限制列中的值的范围（既可以定义列也可以定义表）

比如在“ instructor”表创建“salary”列时创建CHECK约束。CHECK约束规定“salary”列必须只包含大于0的整数。
字段名称类型备注约束salarynumeric(8,2)薪资底薪大于29000

use universityDB;

create table instructor(

salary numeric(8,2) check(salary>29000)

);

大学数据库的整体设计

一个大学里面会有很多教学楼、学生、老师，他们之间的都有着密不可分的关系，因此将大学数据库整体设计如下图所示，总共涉及到八张表：

1. 学生表（student）：包含学生学号 ID，学生姓名 name，学生所在系 dept_name，总学分 tot_cred 属性。
2. 课程表（course）：包含课程号 course_id，课程名称 title，课程所属的系名 dept_name，学分 credits 属性。
3. 系表（department）：包含系名 dept_name，建筑 building，预算 budget 属性。
4. 教室表（classroom）：包含建筑 building，房间号 room_number，容量 capacity 属性。
5. 教学信息表（teaches）：包含教室号 ID，课程号 course_id，课程信息序号 sec_id，学期 semester，年份 year 属性。
6. 选课信息表（takes）：包含学生学号 ID，课程标识 course_id，课程段标识 sec_id，学期 semester，年份 year，成绩 grade 属性。
7. 教师信息表（instructor）。
8. 开课信息表（section）。

图1 大学数据库模型

教师信息表（instructor）

教师信息表的具体属性及约束如下：
字段名称类型备注约束IDvarchar(5)教师号主键namevarchar(20)教师名字非空dept_namevarchar(20)教师所在的系外键salarynumeric(8,2)薪资底薪大于 29000
开课信息表（section）

开课信息表的具体属性及约束如下：
字段名称类型备注约束course_idvarchar(8)课程号外键sec_idvarchar(8)课程段标识无semestervarchar(6)学期只能插入 Fall，Winter，Spring，Summer四个值。yearnumeric(4,0)年份

1701 < year < 2100

buildingvarchar(15)课程所在建筑外键room_numbervarchar(7)课程所在教室号外键time_slot_idvarchar(4)时间档序号无
section 表的主键由 course_id，sec_id，semester，year 四个属性共同组成。

编程要求

补充代码，创建教师信息表和开课信息表（其他六张表系统已为你创建完成），使用 universityDB 数据库，创建的表命名依次为 instructor 和 section。

#请在此添加创建开课信息表的SQL语句
########## Begin ##########
use universityDB;
create table instructor(
    ID varchar(5) COMMENT'教师号',primary key(ID),
    name varchar(20) COMMENT'教师名字'NOT NULL,
    dept_name varchar(20) DEFAULT NULL COMMENT'教师所在的系',
    salary numeric(8,2) COMMENT'薪资' check(salary>29000),
    foreign key (dept_name) references department(dept_name)
        on delete set null
);
create table section(
    course_id varchar(8)  COMMENT '课程号' ,
    sec_id varchar(8) COMMENT'课程段标识',
    semester varchar(6) COMMENT'学期' check(semester in('Fall','Winter','Spring','Summer')),
    year numeric(4,0)  COMMENT'年份',
    building varchar(15)  COMMENT '课程所在建筑' ,
    room_number varchar(7)  COMMENT '课程所在教室号' ,
    time_slot_id varchar(4) COMMENT'时间档序号',
    primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (building, room_number) references classroom(building, room_number)
        on delete set null
);

########## End ##########