CSDN博客

img whzhaha

实验六 教务管理数据库的设计

发表于2004/10/1 1:29:00  986人阅读

实验目的:
  >> 熟练掌握数据库应用系统的设计方法
  >> 熟练掌握概念设计中的ER模型设计方法
  >> 熟练掌握逻辑设计中ER模型向关系模型转换的规则

实验内容:
  >> 创建学校教学选课管理系统的概念模型
  >> 创建学校教学选课管理系统的逻辑模型(符合关系模型的规范化准则)

  >> 创建该数据库并保证数据的一致性

实验步骤:

此数据库的创建环境是MS SQL2000 perfesional,其功能是学生选课和教师授课,并能及时查询。
一、此数据库的概念模型E—R图是:

 

二、数据库的基本表结构分别是:Course、Select Course、Student、Teacher、Teacher Course;
 
1. 进入SQL中创建表,命令脚本是:
CREATE TABLE [dbo].[Course] ( [Cno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL , [Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Cdpt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Select Course] (
[Sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Tno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Tname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Student] (
[Sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Ssex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Sdpt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Teach Course] (
[Sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Tno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Teacher] (
[Tno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Tname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Tsex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Tdpt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

2. 为表中的主键创建外键,命令是:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Select Course_Course]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Select Course] DROP CONSTRAINT FK_Select Course_Course
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Teach Course_Course]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Teach Course] DROP CONSTRAINT FK_Teach Course_Course
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Select Course_Student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Select Course] DROP CONSTRAINT FK_Select Course_Student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Teach Course_Student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Teach Course] DROP CONSTRAINT FK_Teach Course_Student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Select Course_Teacher]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Select Course] DROP CONSTRAINT FK_Select Course_Teacher
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Teach Course_Teacher]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Teach Course] DROP CONSTRAINT FK_Teach Course_Teacher
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Select Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Select Course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Teach Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Teach Course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Teacher]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Teacher]
GO

3. 为数据库中选课授课创建视图,其命令为:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SCourse]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[SCourse]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TCourse]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[TCourse]
GO
CREATE VIEW dbo.SCourse
AS
SELECT Sno AS 学号, Tno AS 教师号, Cno AS 课程号, Tname AS 学生名称,
Cname AS 课程名称
FROM dbo.[Select Course]
GO
CREATE VIEW dbo.TCourse
AS
SELECT Sno AS 学号, Tno AS 教师号, Cno AS 课程号, Tname AS 教师姓名,
Cname AS 课程名称
FROM dbo.[Select Course]
GO
三、数据一致性设计
为所有的表建立INSERT,UPDATE,DELETE存储过程

CREATE PROCEDURE [delete_Course_1]
(@Cno_1 [char])
AS
DELETE [教学管理].[dbo].[Course]
WHERE
( [Cno] = @Cno_1)
GO
CREATE PROCEDURE [delete_Select Course_1]
(@Sno_1 [char],
@Tno_2 [char],
@Cno_3 [char],
@Tname_4 [varchar],
@Cname_5 [varchar])
AS
DELETE [教学管理].[dbo].[Select Course]
WHERE
( [Sno] = @Sno_1 AND
[Tno] = @Tno_2 AND
[Cno] = @Cno_3 AND
[Tname] = @Tname_4 AND
[Cname] = @Cname_5)
GO
CREATE PROCEDURE [delete_Student_1]
(@Sno_1 [char])
AS
DELETE [教学管理].[dbo].[Student]
WHERE
( [Sno] = @Sno_1)
GO
CREATE PROCEDURE [delete_Teach Course_1]
(@Sno_1 [char],
@Tno_2 [char],
@Cno_3 [char],
@Sname_4 [varchar],
@Cname_5 [varchar])
AS
DELETE [教学管理].[dbo].[Teach Course]
WHERE
( [Sno] = @Sno_1 AND
[Tno] = @Tno_2 AND
[Cno] = @Cno_3 AND
[Sname] = @Sname_4 AND
[Cname] = @Cname_5)
GO
CREATE PROCEDURE [delete_Teacher_1]
(@Tno_1 [char])
AS
DELETE [教学管理].[dbo].[Teacher]
WHERE
( [Tno] = @Tno_1)
GO
CREATE PROCEDURE [insert_Course_1]
(@Cno_1 [char](4),
@Cname_2 [varchar](50),
@Cdpt_3 [varchar](50))
AS
INSERT INTO [教学管理].[dbo].[Course]
( [Cno],
[Cname],
[Cdpt])
VALUES
( @Cno_1,
@Cname_2,
@Cdpt_3)
GO
CREATE PROCEDURE [insert_Select Course_1]
(@Sno_1 [char](4),
@Tno_2 [char](4),
@Cno_3 [char](4),
@Tname_4 [varchar](50),
@Cname_5 [varchar](50))
AS
INSERT INTO [教学管理].[dbo].[Select Course]
( [Sno],
[Tno],
[Cno],
[Tname],
[Cname])
VALUES
( @Sno_1,
@Tno_2,
@Cno_3,
@Tname_4,
@Cname_5)
GO
CREATE PROCEDURE [insert_Student_1]
(@Sno_1 [char](4),
@Sname_2 [varchar](50),
@Ssex_3 [char](2),
@Sdpt_4 [varchar](50))
AS
INSERT INTO [教学管理].[dbo].[Student]
( [Sno],
[Sname],
[Ssex],
[Sdpt])
VALUES
( @Sno_1,
@Sname_2,
@Ssex_3,
@Sdpt_4)
GO
CREATE PROCEDURE [insert_Teach Course_1]
(@Sno_1 [char](4),
@Tno_2 [char](4),
@Cno_3 [char](4),
@Sname_4 [varchar](50),
@Cname_5 [varchar](50))
AS
INSERT INTO [教学管理].[dbo].[Teach Course]
( [Sno],
[Tno],
[Cno],
[Sname],
[Cname])
VALUES
( @Sno_1,
@Tno_2,
@Cno_3,
@Sname_4,
@Cname_5)
GO
CREATE PROCEDURE [insert_Teacher_1]
(@Tno_1 [char](4),
@Tname_2 [varchar](50),
@Tsex_3 [char](2),
@Tdpt_4 [varchar](50))
AS
INSERT INTO [教学管理].[dbo].[Teacher]
( [Tno],
[Tname],
[Tsex],
[Tdpt])
VALUES
( @Tno_1,
@Tname_2,
@Tsex_3,
@Tdpt_4)
GO
 CREATE PROCEDURE [update_Course_1]
(@Cno_1 [char],
@Cno_2 [char](4),
@Cname_3 [varchar](50),
@Cdpt_4 [varchar](50))
AS
UPDATE [教学管理].[dbo].[Course]
SET [Cno] = @Cno_2,
[Cname] = @Cname_3,
[Cdpt] = @Cdpt_4
WHERE
( [Cno] = @Cno_1)
GO
 CREATE PROCEDURE [update_Select Course_1]
(@Sno_1 [char],
@Tno_2 [char],
@Cno_3 [char],
@Tname_4 [varchar],
@Cname_5 [varchar],
@Sno_6 [char](4),
@Tno_7 [char](4),
@Cno_8 [char](4),
@Tname_9 [varchar](50),
@Cname_10 [varchar](50))
AS
UPDATE [教学管理].[dbo].[Select Course]
SET [Sno] = @Sno_6,
[Tno] = @Tno_7,
[Cno] = @Cno_8,
[Tname] = @Tname_9,
[Cname] = @Cname_10
WHERE
( [Sno] = @Sno_1 AND
[Tno] = @Tno_2 AND
[Cno] = @Cno_3 AND
[Tname] = @Tname_4 AND
[Cname] = @Cname_5)
GO
 CREATE PROCEDURE [update_Student_1]
(@Sno_1 [char],
@Sno_2 [char](4),
@Sname_3 [varchar](50),
@Ssex_4 [char](2),
@Sdpt_5 [varchar](50))
AS
UPDATE [教学管理].[dbo].[Student]
SET [Sno] = @Sno_2,
[Sname] = @Sname_3,
[Ssex] = @Ssex_4,
[Sdpt] = @Sdpt_5
WHERE
( [Sno] = @Sno_1)
GO
CREATE PROCEDURE [update_Teach Course_1]
(@Sno_1 [char],
@Tno_2 [char],
@Cno_3 [char],
@Sname_4 [varchar],
@Cname_5 [varchar],
@Sno_6 [char](4),
@Tno_7 [char](4),
@Cno_8 [char](4),
@Sname_9 [varchar](50),
@Cname_10 [varchar](50))
AS
UPDATE [教学管理].[dbo].[Teach Course]
SET [Sno] = @Sno_6,
[Tno] = @Tno_7,
[Cno] = @Cno_8,
[Sname] = @Sname_9,
[Cname] = @Cname_10
WHERE
( [Sno] = @Sno_1 AND
[Tno] = @Tno_2 AND
[Cno] = @Cno_3 AND
[Sname] = @Sname_4 AND
[Cname] = @Cname_5)
GO
 CREATE PROCEDURE [update_Teacher_1]
(@Tno_1 [char],
@Tno_2 [char](4),
@Tname_3 [varchar](50),
@Tsex_4 [char](2),
@Tdpt_5 [varchar](50))
AS
UPDATE [教学管理].[dbo].[Teacher]
SET [Tno] = @Tno_2,
[Tname] = @Tname_3,
[Tsex] = @Tsex_4,
[Tdpt] = @Tdpt_5
WHERE
( [Tno] = @Tno_1)
 GO
至此,整个数据库建立完成,并通过是运行。

阅读全文
0 0

相关文章推荐

img
取 消
img