创建数据库

USE master
GO

IF EXISTS(
  SELECT name
  FROM sys.databases
  WHERE name = N'teaching'
)
DROP DATABASE teaching
CREATE DATABASE teaching 
  ON  PRIMARY 
   ( NAME = N'teaching', FILENAME = N'D:\teaching\teaching.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  LOG ON 
   ( NAME = N'teaching_log', FILENAME = N'D:\teaching\teaching_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE teaching 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'student'
)
DROP TABLE student
CREATE TABLE student(
	studentno nchar(10) NOT NULL,
	sname nchar(8) NOT NULL,
	sex nchar(1) NOT NULL,
	birthday datetime NOT NULL,
	classno nchar(6) NOT NULL,
    	point smallint NOT NULL,
    	phone nchar(12) NOT NULL,
    	Email nchar(20) NOT NULL,
	CONSTRAINT CK_sex CHECK (sex='男' OR sex='女'),
    	CONSTRAINT PK_student PRIMARY KEY CLUSTERED (studentno  ASC)
) 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'course'
)
DROP TABLE dbo.course
CREATE TABLE dbo.course(
	courseno nchar(6) NOT NULL,
	cname nchar(20) NULL,
	type nchar(8) NULL,
	period tinyint NULL,
	credit numeric(4, 1) NULL,
    	CONSTRAINT PK_course PRIMARY KEY CLUSTERED (Courseno  ASC)
) 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'teacher'
)
DROP TABLE dbo.teacher
CREATE TABLE dbo.teacher(
	teacherno nchar(6) NOT NULL,
	tname nchar(8) NULL,
	major nchar(10) NULL,
	prof nchar(10) NOT NULL,
	department nchar(12)  NULL,
    	CONSTRAINT PK_teacher PRIMARY KEY CLUSTERED (teacherno ASC)
) 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'class'
)
DROP TABLE dbo.class
CREATE TABLE dbo.class(
	classno nchar(6)  NOT NULL,
	classname nchar(12) NULL,
	department nchar(12) NULL,
	monitor nchar(8) NULL,
    	CONSTRAINT PK_class PRIMARY KEY CLUSTERED (classno ASC)
) 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'score'
)
DROP TABLE dbo.score
CREATE TABLE dbo.score(
	studentno nchar(10)  NOT NULL,
	courseno  nchar(6)  NOT NULL,
	usually numeric(6, 2) DEFAULT 0,
	final numeric(6, 2) DEFAULT 0,
    	score as usually*30/100+final*70/100,
    	CONSTRAINT PK_score PRIMARY KEY CLUSTERED (studentno ASC,courseno ASC)
) 
GO

IF EXISTS (
  SELECT * FROM sys.objects 
  WHERE name = N'teach_class'
)
DROP TABLE dbo.teach_class
CREATE TABLE dbo.teach_class(
	teacherno nchar(6) NOT NULL,
    	classno nchar(6) NOT NULL,
	courseno  nchar(6) NOT NULL,
    	CONSTRAINT PK_teach_class PRIMARY KEY CLUSTERED (teacherno ASC,classno ASC,courseno ASC)
) 
GO

ALTER TABLE score
   ADD CONSTRAINT FK_score_student FOREIGN KEY (studentno) REFERENCES student (studentno) ON UPDATE CASCADE ON DELETE CASCADE,
       CONSTRAINT FK_score_course  FOREIGN KEY (courseno)  REFERENCES course (courseno) ON UPDATE CASCADE ON DELETE CASCADE
GO

ALTER TABLE teach_class
    ADD CONSTRAINT FK_teach_class_teacher FOREIGN KEY (teacherno) REFERENCES teacher (teacherno) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT FK_teach_class_class   FOREIGN KEY (classno)   REFERENCES class (classno) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT FK_teach_class_course  FOREIGN KEY (courseno)  REFERENCES course (courseno) ON UPDATE CASCADE ON DELETE CASCADE
GO

CREATE RULE score_rule AS
   @score BETWEEN 0 AND 100
GO
EXEC sp_bindrule 'score_rule','score.usually'
EXEC sp_bindrule 'score_rule','score.final'

insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0822111208','韩吟秋','男','1989-12-18','080601',666,'15878945612','han@163.com'  )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0823210007','宿致远','男','1988-12-7', '080501',658,'12545678998','sui2@163.com' )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0824113307','崔岩坚','男','1988-4-8',  '080601',787,'15556845645','cui@126.com'  )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0922210009','许海冰','男','1989-9-8',  '080501',789,'13623456778','qwe@163.com'  )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0922221324','何影',  '女','1990-10-7', '080501',879,'13178978999','aaa@sina.com' )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0925111109','敬秉辰','男','1991-1-2',  '080801',789,'15678945623','jing@sina.com')
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0925121107','梁欣',  '女','1989-7-7',  '080502',777,'13145678921','bing@126.com' )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0935222201','夏文婓','女','1990-8-9',  '080502',867,'15978945645','tang@163.com' )
insert into student (studentno,sname,sex,birthday,classno,point,phone,Email)values('0937221508','平靖',  '女','1989-12-17','080802',789,'12367823453','ping@163.com' )

insert into teacher (teacherno,tname,major,prof,department) values ('t05001','韩晋升','软件工程'  ,'教授'  ,'计算机学院')
insert into teacher (teacherno,tname,major,prof,department) values ('t05003','刘元朝','网络技术'  ,'教授'  ,'计算机学院')
insert into teacher (teacherno,tname,major,prof,department) values ('t05011','海封'  ,'计算机设计','副教授','计算机学院')
insert into teacher (teacherno,tname,major,prof,department) values ('t05017','卢明欣','软件测试'  ,'讲师'  ,'计算机学院')
insert into teacher (teacherno,tname,major,prof,department) values ('t06011','胡海悦','机械制造'  ,'教授'  ,'机械学院'  )
insert into teacher (teacherno,tname,major,prof,department) values ('t06023','姚思远','铸造工艺'  ,'副教授','机械学院'  )
insert into teacher (teacherno,tname,major,prof,department) values ('t07019','马爱芬','经济管理'  ,'讲师'  ,'管理学院'  )
insert into teacher (teacherno,tname,major,prof,department) values ('t08017','田有余','金融管理'  ,'副教授','管理学院'  )

insert into course (courseno,cname,type,period,credit) values ('c05103','电子技术','必修',64,4.0)
insert into course (courseno,cname,type,period,credit) values ('c05109','C语言'   ,'必修',72,4.5)
insert into course (courseno,cname,type,period,credit) values ('c05127','数据结构','必修',64,4.0)
insert into course (courseno,cname,type,period,credit) values ('c05138','软件工程','选修',48,3.0)
insert into course (courseno,cname,type,period,credit) values ('c06108','机械制图','必修',60,3.5)
insert into course (courseno,cname,type,period,credit) values ('c06127','机械设计','必修',64,4.0)
insert into course (courseno,cname,type,period,credit) values ('c06172','铸造工艺','选修',42,2.5)
insert into course (courseno,cname,type,period,credit) values ('c08106','经济法'  ,'必修',48,3.0)
insert into course (courseno,cname,type,period,credit) values ('c08123','金融学'  ,'必修',40,2.5)
insert into course (courseno,cname,type,period,credit) values ('c08171','会计软件','选修',32,2.0)

insert into class (classno,classname,department,monitor) values ('080501','计08本1班','计算机学院','韩吟秋')
insert into class (classno,classname,department,monitor) values ('080502','计08本2班','计算机学院','宿致远')
insert into class (classno,classname,department,monitor) values ('080601','机08本1班','机械学院'  ,'崔岩坚')
insert into class (classno,classname,department,monitor) values ('080602','机08本2班','机械学院'  ,'韩吟秋')
insert into class (classno,classname,department,monitor) values ('080801','管08本1班','管理学院'  ,'许海冰')
insert into class (classno,classname,department,monitor) values ('080802','管08本2班','管理学院'  ,'何影')

insert into score(studentno,courseno,usually,final) values ('0822111208','c05109',85.00,91.00)
insert into score(studentno,courseno,usually,final) values ('0822111208','c06108',89.00,95.00)
insert into score(studentno,courseno,usually,final) values ('0822111208','c06127',78.00,67.00)
insert into score(studentno,courseno,usually,final) values ('0823210007','c05103',82.00,69.00)
insert into score(studentno,courseno,usually,final) values ('0823210007','c05109',87.00,86.00)
insert into score(studentno,courseno,usually,final) values ('0824113307','c05103',85.00,77.00)
insert into score(studentno,courseno,usually,final) values ('0824113307','c05109',88.00,79.00)
insert into score(studentno,courseno,usually,final) values ('0824113307','c06108',66.00,82.00)
insert into score(studentno,courseno,usually,final) values ('0922210009','c05103',87.00,82.00)
insert into score(studentno,courseno,usually,final) values ('0922210009','c05109',77.00,91.00)
insert into score(studentno,courseno,usually,final) values ('0922221324','c05103',88.00,62.00)
insert into score(studentno,courseno,usually,final) values ('0922221324','c05109',91.00,77.00)
insert into score(studentno,courseno,usually,final) values ('0925111109','c08106',79.00,99.00)
insert into score(studentno,courseno,usually,final) values ('0925111109','c08123',85.00,92.00)
insert into score(studentno,courseno,usually,final) values ('0925111109','c08171',77.00,92.00)
insert into score(studentno,courseno,usually,final) values ('0925121107','c05103',74.00,91.00)
insert into score(studentno,courseno,usually,final) values ('0925121107','c05109',89.00,62.00)
insert into score(studentno,courseno,usually,final) values ('0935222201','c05109',99.00,92.00)
insert into score(studentno,courseno,usually,final) values ('0935222201','c08171',95.00,82.00)
insert into score(studentno,courseno,usually,final) values ('0937221508','c08106',78.00,95.00)
insert into score(studentno,courseno,usually,final) values ('0937221508','c08123',78.00,89.00)
insert into score(studentno,courseno,usually,final) values ('0937221508','c08171',88.00,98.00)

insert into teach_class(teacherno,classno,courseno) values ('t05001','080501','c05103')
insert into teach_class(teacherno,classno,courseno) values ('t05001','080501','c05109')
insert into teach_class(teacherno,classno,courseno) values ('t05001','080501','c05127')
insert into teach_class(teacherno,classno,courseno) values ('t05017','080501','c05138')
insert into teach_class(teacherno,classno,courseno) values ('t05017','080501','c06108')
insert into teach_class(teacherno,classno,courseno) values ('t05017','080501','c06127')
insert into teach_class(teacherno,classno,courseno) values ('t08017','080501','c08171')
insert into teach_class(teacherno,classno,courseno) values ('t08017','080501','c06172')
insert into teach_class(teacherno,classno,courseno) values ('t08017','080501','c08106')
insert into teach_class(teacherno,classno,courseno) values ('t07019','080501','c08123')
insert into teach_class(teacherno,classno,courseno) values ('t07019','080501','c08171')
GO


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

绝地反击T

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值