数据库实验一源代码

/*创建数据库*/
CREATE DATABASE SC
ON
(NAME=jxglsjk_dat,
FILENAME='E:\教学管理数据库\SC.mdf',
SIZE=30,
FILEGROWTH=20%)
LOG ON
(NAME=jxglsjk_log,
FILENAME='E:\教学管理数据库\SC.ldf',
SIZE=3,
FILEGROWTH=1MB)
GO

/*使用数据库SC*/
USE SC;

/*返回与当前数据库关联的文件的物理名称及属性*/
EXEC sp_helpfile;

/*创建各表*/
CREATE TABLE department
(dno char(6) PRIMARY KEY,
dept_name char(20) NOT NULL,
header char(8));

CREATE TABLE speciality
(spno char(8)PRIMARY KEY,
dno char(6) not null, 
spname char(20) not null,

FOREIGN KEY (dno) REFERENCES department(dno));

CREATE TABLE class
(spno char(8),
class_no char(4),
header char(8),
PRIMARY KEY(spno,class_no),
FOREIGN KEY (spno) REFERENCES speciality(spno));

CREATE TABLE teacher
(t_no char(8) PRIMARY KEY,
t_name char(8) NOT NULL,
t_sex char(2),
t_birthday smalldatetime,
dno char(6),
tech_title char(10),
FOREIGN KEY (dno) REFERENCES department(dno));

CREATE TABLE course
(cno char(10) PRIMARY KEY,
cname char(20) NOT NULL,
spno char(8),
ctno tinyint,
lecture tinyint,
experiment tinyint,
semester tinyint,
credit tinyint,
FOREIGN KEY (spno) REFERENCES speciality(spno));

CREATE TABLE student