网上选课系统数据库设计

CREATE DATABASE网上选课系统数据库设计ON PRIMARY
(NAME= N'网上选课系统数据库设计',FILENAME= N'E:\蔡伟亭_李雨晴_杨可帆\网上选课系统数据库设计.mdf',SIZE= 3072KB ,MAXSIZE= UNLIMITED,FILEGROWTH = 1024KB )
LOG ON
(NAME= N'网上选课系统数据库设计_log',FILENAME= N'E:\蔡伟亭_李雨晴_杨可帆\网上选课系统数据库设计_log.ldf',SIZE= 1024KB ,MAXSIZE= 2048GB , FILEGROWTH= 10%)
COLLATE Chinese_PRC_CI_AS
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE学生(
[学号] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[姓名] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[性别] [char](2)COLLATE Chinese_PRC_CI_AS NOT NULL,
[民族] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[专业] [char](20)COLLATE Chinese_PRC_CI_AS NOT NULL,
[籍贯] [char](80)COLLATE Chinese_PRC_CI_AS NOT NULL,
[身份证号] [char](18)COLLATE Chinese_PRC_CI_AS NOT NULL,
[用户名] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[学院编号] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED
(
[学号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE课题组(
[课题组编号] [char](18)COLLATE Chinese_PRC_CI_AS NOT NULL,
[课题组名称] [char](30)COLLATE Chinese_PRC_CI_AS NOT NULL,
[课题组类型] [char](16)COLLATE Chinese_PRC_CI_AS NOT NULL,
[组长] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[电话] [char](30)COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_课题组] PRIMARY KEY CLUSTERED
(
[课题组编号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
CREATE TABLE学院(
[学院编号] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[学院名称] [char](20)COLLATE Chinese_PRC_CI_AS NOT NULL, [学院类型] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL, [院长编号] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL, [院办地址] [char](80)COLLATE Chinese_PRC_CI_AS NOT NULL, [院办电话] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_学院] PRIMARY KEY CLUSTERED
(
[学院编号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
CREATE TABLE管理员(
[用户名] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[密码] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[ID] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_管理员] PRIMARY KEY CLUSTERED
(
[用户名] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE课程(
[课程编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, [课程名] [char](40)COLLATE Chinese_PRC_CI_AS NOT NULL, [课程类型] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL, [学分数] [smallint] NULL,
[学时数] [smallint] NULL,
CONSTRAINT [PK_课程] PRIMARY KEY CLUSTERED
(
[课程编号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE选课(
[学号] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL,
[课程编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, [成绩] [smallint] NULL,
CONSTRAINT [PK_选课] PRIMARY KEY CLUSTERED
(
[学号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE教师(
[教师编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, [姓名] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[性别] [char](2)COLLATE Chinese_PRC_CI_AS NOT NULL,
[学历] [char](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[家庭住址] [char](80)COLLATE Chinese_PRC_CI_AS NOT NULL, [电话] [char](13)COLLATE Chinese_PRC_CI_AS NOT NULL,
[课题组编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_教师] PRIMARY KEY CLUSTERED
(
[教师编号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
USE [网上选课系统数据库设计]
GO
CREATE TABLE授课(
[教师编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, [课程编号] [char](6)COLLATE Chinese_PRC_CI_AS NOT NULL, [授课时间] [datetime] NOT NULL,
[授课地点] [char](10)COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_授课] PRIMARY KEY CLUSTERED
(
[教师编号] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY] )ON [PRIMARY]
GO
use网上选课系统数据库设计
go
create view view_学生选课
as
select学生.学号,姓名,课程编号
from学生,选课
where学生.学号=选课.学号
go
create view view_教师授课
as
select教师.教师编号,姓名,课程编号
from教师,授课
where教师.教师编号=授课.教师编号
go
create view view_选课授课
as
select学号,选课.课程编号,授课时间,授课地点from选课,授课
where选课.课程编号=授课.课程编号
go
create view view_学生教师
as
select学号,选课.课程编号,教师编号
from选课,授课
where选课.课程编号=授课.课程编号
go
create index SY_学生_学号
on学生(学号)
go
create unique index SY_教师_教师编号
on教师(教师编号)
go
create index SY_学院_学院编号
on学院(学院编号)
go
create unique index SY_课程_课程编号
on课程(课程编号)
go
create procedure学生_pro
as
select学号,avg(成绩)
as'平均分'
from选课
group by学号
go
create procedure选课_pro as
select*from选课
where学号=301207118 execute选课_pro
go
create procedure学_生_pro as
select学号,sum(成绩)
as'总成绩'
from选课
group by学号
go
create procedure授课_pro as
select*from授课
where教师编号=110
execute授课_pro
go
四．物理设计。