Log On MyDataBase=2
with Override
/*以sa身份添加用户peng，改变数据库xsgl的所有者为peng*/
sp_addlogin "peng","123456",xsgl,"us_english"
go
use xsgl
go
sp_changedbowner peng,true
/*打开数据库*/
use xsgl
go
/*创建class表*/
create table class
(class_id char(8) not null,
class_name varchar(10) not null,
master char(8) not null,
class_tel char(11) not null)
/*把class表的select的权限授给public组*/
grant select on class to public
/*创建courses表*/
create table courses
(course_id char(8) not null,
course_name char(20) not null,
course_mark int not null,
material_id char(20) not null)
/*把courses表的select的权限授给public组*/
grant select on courses to public
/*创建teacher表*/
create table teacher
(teacher_id char(8) not null,
teacher_name char(12) not null,
teacher_sex char(2) not null,
teacher_age int not null,
title char(10) not null,
specialty_id char(8) not null,
department_id char(8) not null)
/*把teacher表的select的权限授给public组*/
grant select on teacher to public
/*创建department表*/
create table department
(department_id char(8) not null,
department_name char(20) not null,
department_director char(12) not null,
department_secretary char(12) not null,
department_tel char(11) null)
/*把department表的select的权限授给public组*/
grant select on department to public
/*创建specialty表*/
create table specialty
(specialty_id char(8) not null,
specialty_name char(20) not null,
specialty_director char(12) not null,
specialty_tel char(11) null,
department_id char(8) not null)
/*把specialty表的select的权限授给public组*/
grant select on specialty to public
/*创建material表*/
create table material
(material_id char(20) not null,
material_name varchar(30) not null,
publish varchar(30) not null,
price money not null)
/*把material表的select的权限授给public组*/
grant select on material to public
/*创建teacher_course表*/
create table teacher_course
(teacher_id char(8) not null,
course_id char(8) not null,
material_id char(20) not null,
time int null,
specialty_id char(8) not null)
/*把class表的select的权限授给public组*/
grant select on teacher_course to public
/*创建student表*/
create table student
(student_id char(8) not null,
student_name char(12) not null,
student_sex char(2) not null,
class_id char(8) not null,
specialty_id char(8) not null,
department_id char(8) not null)
/*把student表的select的权限授给public组*/
grant select on student to public
/*创建student_course表*/
create table student_course
(course_id char(8) not null,
student_id char(10) not null,
mark int not null)
/*把student_course表的select的权限授给public组*/
grant select on student_course to public
/*设置主键*/
execute sp_primarykey class,class_id
execute sp_primarykey courses,course_id
execute sp_primarykey department,department_id
execute sp_primarykey teacher,teacher_id
execute sp_primarykey material,material_id
execute sp_primarykey specialty,specialty_id
execute sp_primarykey student,student_id
/*设置外键*/
execute sp_foreignkey teacher_course,teacher,teacher_id
execute sp_foreignkey teacher_course,courses,course_id
execute sp_foreignkey teacher_course,material,material_id
execute sp_foreignkey teacher_course,specialty,specialty_id
execute sp_foreignkey student,class,class_id
execute sp_foreignkey student,department,department_id
execute sp_foreignkey student,specialty,specialty_id
