DataBase System Concept相关语句 (笔记)

最新推荐文章于 2021-06-28 19:11:47 发布

一个路过的Developer

最新推荐文章于 2021-06-28 19:11:47 发布

阅读量401

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/sinat_20019511/article/details/106020458

版权

这篇博客主要介绍了数据库系统概念的应用，包括如何创建表和填充样本数据。首先，详细讲解了创建表的步骤和注意事项，接着展示了如何生成和导入样本数据，为实际操作提供了基础指南。

摘要由CSDN通过智能技术生成

一、表的创建

在这里插入图片描述

create table classroom(
	building varchar (15),
	room_number varchar (7),
	capacity numeric (4,0),
	primary key(building, room_number));

create table department(
	dept_name varchar (20),
	building varchar (15),
	budget numeric (12,2) check (budget > 0),
	primary key (dept_name));

create table course(
	course_id varchar(7),
	title varchar(50),
	dept_name varchar(20),
	credits numeric(2,0) check(credits > 0),
	primary key (course_id),
	foreign key (dept_name) references department(dept_name) on delete set null);

create table instructor(
	ID varchar (5),
	name varchar (20) not null,
	dept_name varchar (20),
	salary numeric (8,2) check (salary > 29000),
	primary key (ID),
	foreign key (dept_name) references department(dept_name) on delete set null);

create table section(
	course_id varchar (8),
	sec_id varchar (8),
	semester varchar (6) check (semester in('Fall', 'Winter', 'Spring', 'Summer')),
	year numeric (4,0) check (year > 1701 and year < 2100),
	building varchar (15),
	room_number varchar (7),
	time_slot_id varchar (4),
	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);

create table teaches(
	ID varchar (5),
	course_id varchar (8),
	sec_id varchar (8),
	semester varchar (6),
	year numeric (4,0),
	primary key (ID, course_id, sec_id, semester, year),
	foreign key (course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year) on delete cascade,
	foreign key (ID) references instructor(ID) on delete cascade);


create table student(
	ID varchar (5),
	name varchar (20) not null,
	dept_name varchar (20),
	tot_cred numeric (3,0) check (tot_cred >= 0),
	primary key (ID),
	foreign key (dept_name) references department(dept_name) on delete set null);

create table takes(
	ID varchar (5),
	course_id varchar (8),
	sec_id varchar (8),
	semester varchar (6),
	year numeric (4,0),
	grade varchar (2),
	primary key (ID, course_id, sec_id, semester, year),
	foreign key (course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year) on delete cascade,
	foreign key (