学生学籍管理信息系统讲课教案

类型为主索引,表达式为课程号。

(3)创建成绩表的索引

成绩表:先点击右键,然后选择修改。在弹出的界面中选择索引页面,索引名为index2,通索引,表达式为课程号;索引名为xh,类型为主索引,表达式为学号。

(4)创建奖惩表的索引

奖惩表:先点击右键,然后选择修改。在弹出的界面中选择索引页面,索引名为index3,类型为主索引,表达式为学号。

(5)创建班级表的索引 (6)创建教师表的索引

2.2.4 数据表之间的逻辑关系

3、系统实现

3.1 数据库访问

(1)数据访问类及实现代码 1)触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义,任何用户对表的增、删、改操作均由服务器自动激活相应的触发器,在DBMS核心层进行集中的完整性控制。由于本系统的触发器比较多,在这里我只写出了其

中的一部分触发器,具体如下所示。

数据更新:

CREATE TRIGGER studentupdate ON dbo.student FOR UPDATE AS

UPDATE STUDENT

SET SNO = (SELECT SNO FROM INSERTED) WHERE SNO = (SELECT SNO FROM DELETED);

CREATE TRIGGER courseupdate ON dbo.course / FOR UPDATE AS

UPDATE COURSE

SET SNO = (SELECT CNO FROM INSERTED) WHERE SNO = (SELECT CNO FROM DELETED);

CREATE TRIGGER scoreupdate ON dbo.student FOR UPDATE AS

UPDATE SCORE

SET SNO = (SELECT SNO FROM INSERTED) WHERE SNO = (SELECT SNO FROM DELETED);

CREATE TRIGGER student1 ON dbo.major FOR UPDATE AS

UPDATE STUDENT

SET MNO = (SELECT MNO FROM INSERTED) WHERE MNO = (SELECT MNO FROM DELETED);

CREATE TRIGGER student2update ON dbo.depart FOR UPDATE AS

UPDATE STUDENT

SET DMPNO = (SELECT DMPNO FROM INSERTED)

WHERE DMPNO = (SELECT DMPNO FROM DELETED); 数据删除:

CREATE TRIGGER teach1update ON dbo.teacher FOR DELETE AS

UPDATE TEACH SET TNO = NULL

WHERE TNO = (SELECT TNO FROM DELETED);

CREATE TRIGGER stu_majorupdate ON dbo.major FOR DELETE AS

UPDATE STUDENT SET MNO = NULL

WHERE MNO = (SELECT MNO FROM DELETED);

CREATE TRIGGER teacherupdate ON dbo.depart FOR DELETE AS

UPDATE TEACHER SET DMPNO = NULL

WHERE DMPNO = (SELECT DMPNO FROM DELETED)

2)java访问数据库及代码 package DBCONN; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Conmysql { //这是mysql的连接字符串 private static String url =

\

static Connection conn = null;//定义一个空连接,后面用 static Statement stmt = null;//定义一个空查询语句,后面用 static ResultSet rs = null;//定义一个空的返回结果集,后面用 static Object result;

public static Object exqutesql(String sql) { try {

//注册mssql驱动

Class.forName(\//连接数据库

conn = DriverManager.getConnection(url, \//准备查询语句

stmt = conn.createStatement(); String beginsql = \

beginsql = sql.trim().substring(0, 1); // boolean execute = stmt.execute(sql);

//判断sql的类型,是insert/select/delete/update

//注意:只有select才能是executeQuery,其他三种都是executeUpdate if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql);// // System.out.println(result); // }

if (beginsql.equalsIgnoreCase(\

result = stmt.executeQuery(sql);//保存查询结果到result,下同 }

if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql); }

if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql); System.out.println(result); }

// System.out.println(tag);

} catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace();

} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally {

//注意:最后的时候要关闭所有连接,首先是stmt,然后是conn // try { // stmt.close(); // conn.close();

// } catch (SQLException e) { // // TODO Auto-generated catch block // e.printStackTrace(); // } }

return result; }

}

(2) SQL语句 USE [xsxjgl] GO

/****** 对象: Table [dbo].[student] SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

SET ANSI_PADDING ON GO

CREATE TABLE [dbo].[student]( [sno] [char](10) NOT NULL, [sname] [char](10) NULL, [ssex] [char](2) NULL, [sage] [int] NULL, [smz] [char](10) NULL, [sjg] [char](10) NULL, [sdepart] [char](30) NULL, [smajor] [char](30) NULL, [sgrade] [int] NULL, [sclass] [smallint] NULL, [szzmm] [char](10) NULL, [srxcj] [smallint] NULL,

脚本日期: 05/07/2014 11:07:39 ******/

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4