-----------------------------------------------------------------------

--準(zhǔn)備：創(chuàng)建數(shù)據(jù)庫
use master

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'roommaster')
	DROP DATABASE [roommaster]
GO

CREATE DATABASE roommaster  
ON (NAME = N'roommaster_Data', FILENAME = N'D:\roommaster_Data.MDF' , SIZE = 5, FILEGROWTH = 10%) 
LOG ON (NAME = N'roommaster_Log', FILENAME = N'D:\roommaster_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

use roommaster
go

--1、創(chuàng)建Department表
-------------------------------------
create table major
(
majorno char(4) primary key,
majorName varchar(30) not null unique
)
-------------------------------------
go
--2,創(chuàng)建student表
create table student
(
	studentno varchar(10) primary key,
	studentname varchar(20) not null,
	majorno char(4) not null foreign key references major(majorno),
	smima varchar(10) not null default '123456'
	
)

-------------------------------------
go
--3,創(chuàng)建teacher表
create table teacher
(
	teacherno varchar(8) primary key,
	teachername varchar(20) not null,
	tmima varchar(10) not null default '123456'
)
--------------------------------------
go
--4,創(chuàng)建room表
--drop table room
create table room
(	
	roomno char(10)primary key,
	located varchar(30)
)
--------------------------------------
go
--5,創(chuàng)建course表
--drop table course
create table course
(
	courseno char(8) primary key,
	coursename varchar(30) not null,
	majorno char(4) not null foreign key references major(majorno),
	teacherno varchar(8) not null foreign key references teacher(teacherno),
	roomno char(10) not null foreign key references room(roomno),
	thetime varchar(50) not null
)
--------------------------------------
go

--6,創(chuàng)建choosed表
create table choosed
(
	studentno varchar(10) not null foreign key references student(studentno),
	courseno char(8) not null foreign key references course(courseno)
)
---------------------------------------
go

--7.建立存儲(chǔ)過程spdeleteteacher,參數(shù)@teacher,刪除老師之前先刪除老師所教的課程

create procedure spdeleteteacher
(
	@teacherno char(8)
)
as
begin transaction spdeleteteacher	
	delete from course where courseno in (select courseno from course where teacherno=@teacherno)
	if @@Error<>0  rollback transaction spdeleteteacher
	delete from teacher where teacherno=@teacherno
	if @@Error<>0  rollback transaction spdeleteteacher
	else commit transaction spdeleteteacher
-----------------------------------------------------
--exec spdeleteteacher '01001'
go
----向student表中增加屬性coursecount----------------------------------------------------
alter table student add coursecount int not null default 0
update student set courseCount=(select count(*) from choosed where choosed.studentno=student.studentno)
-------------------------------------------------------
go
---9,觸發(fā)器
create trigger tgUpdatecourseCountForInsert
on choosed
for Insert
as
	update student set courseCount=courseCount+(select count(studentno) from inserted where inserted.studentno=student.studentno) 
	where studentno in(select studentno from inserted)
go

create trigger tgUpdatecourseCountForDelete
on choosed
for delete
as
	update student set courseCount=courseCount-(select count(studentno) from deleted where deleted.studentno=student.studentno) 
	where studentno in(select studentno from deleted)
go
--------------------------------------------------------
--9，創(chuàng)建視圖
create	view teachedby as
select studentname,teachername
from student,choosed,course,teacher
where student.studentno=choosed.studentno and choosed.courseno=course.courseno and course.teacherno=teacher.teacherno


--8.建立存儲(chǔ)過程spdeletecourse,參數(shù)@courseno,刪除課程之前先刪除與該課程有關(guān)的選課信息

create procedure spdeletecourse
(
	@courseno char(8)
)
as
begin transaction spdeletecourse	
	delete from choosed where studentno in (select studentno from choosed where courseno=@courseno) and courseno=@courseno
	if @@Error<>0  rollback transaction spdeletecourse
	delete from course where courseno=@courseno
	if @@Error<>0  rollback transaction spdeletecourse
	else commit transaction spdeleteteacher

----exec spdeletecourse '00102'







---向teacher表中增加屬性tcoursecount-----------------------------------------------------
alter table teacher add tcoursecount int not null default 0
update teacher set tcourseCount=(select count(*) from course where course.teacherno=teacher.teacherno)
-------------------------------------------------------
go
---9,觸發(fā)器
create trigger tgUpdatetcourseCountForInsert
on course
for Insert
as
	update teacher set tcourseCount=tcourseCount+(select count(courseno) from inserted where inserted.teacherno=teacher.teacherno) 
	where teacherno in(select teacherno from inserted)
go

create trigger tgUpdatetcourseCountForDelete
on course
for delete
as
	update teacher set tcourseCount=tcourseCount-(select count(courseno) from deleted where deleted.teacherno=teacher.teacherno) 
	where teacherno in(select teacherno from deleted)
go

----------------------
---有部分存儲(chǔ)過程和視圖在企業(yè)管理器中直接創(chuàng)建