同濟大學《數(shù)據(jù)庫技術(shù)及應用》項目設計報告項目設計報告題目：同濟大學嘉定校區(qū)教室管理系統(tǒng)小組成員姓名：于曉宇、高茜班級：DB3班姓名：于曉宇學號：042123年級：大三專業(yè)：工程管理指導教師：袁科萍日期：2007年01月5日1.開發(fā)背景、目的、意義上學期去本部教務處查詢考試安排時，偶然看到墻上很多個小口袋插著很多小卡片來表示各個教室的使用情況，覺得效率很低、工作量很大。偌大的一個學校用人工來管理這個系統(tǒng)是一件很不科學的事。這學期有機會自己做項目，便很自然的想到了這個教室管理系統(tǒng)，希望能通過所學知識設計一個簡單實用的系統(tǒng)來改善這種狀況，提高效率。雖然整個系統(tǒng)并不是非常復雜，但是確實方便可行，它可以幫助我們快速的查詢每個教室的課程安排及使用情況、每個課程的上課地、點每個時間的空閑教室，也可以查到每個教師的授課地點等，可謂是一個多功能的課表，方便我們進行各種與教室、課程等相關(guān)的查詢與管理。2.功能描述權(quán)限管理：用戶輸入正確的用戶名和密碼方可登入使用本系統(tǒng)，并根據(jù)等入者權(quán)限顯示不同的操作界面密碼管理：每個用戶均可修改自身的密碼，超管可以添加刪除普通用戶但不可修改其密碼教室使用情況查詢：?指定時間，查詢所有該時間空閑的教室?指定教室，查詢所有該教室的課程安排其他查詢?指定課程，查詢所有該課程的相關(guān)信息?指定教師，查詢所有該老師開設的課程借用教室：輸入教室的信息，如果經(jīng)過核對該教室確實空閑可借，則節(jié)約能夠成功3.數(shù)據(jù)庫設計E-R圖admintypepasswordadministratorsidnamecollegestudentusespecialtyridBuildingcapacityclassroomarragedoorplatecidtypecourceteachnamecridetHourperirodcollegenameTidteacherpost共有七張表，一個視圖，一個觸發(fā)器，三個存儲過程。表的設計Administrator表：Student表：Teacher表：Course表：Classroom表：Arrange表：Use表：視圖設計Building視圖：觸發(fā)器設計數(shù)據(jù)完整性約束觸發(fā)器：存儲過程設計Login存儲過程：BuildingToDoorPlate存儲過程：InsertIntoUse存儲過程：4.窗口設計共有六個窗體。登入窗體主窗體空閑教室查詢結(jié)果顯示窗體教室使用查詢結(jié)果顯示窗體課程查詢結(jié)果顯示窗體教師查詢結(jié)果顯示窗體5.設計代碼ModulePublicModulePublicConnectionStringAsNewString("DataSource=WinXP-SP2;InitialCatalog=CRMIS;IntegratedSecurity=True")EndModuleImportsSystem.Data.SqlClientImportsSystem.DataPublicClassLoginPrivateSubButtonLogin_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonLogin.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReadermyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.StoredProceduremyCommand.Parameters.Add("@Admin",SqlDbType.Char,5).Value=TextBoxAdmin.TextmyCommand.Parameters.Add("@Passwd",SqlDbType.Char,17).Value=TextBoxPasswd.TextmyCommand.CommandText="Login"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)IfmyReader.ReadThenMain.TextBoxUser.Text=TextBoxAdmin.TextIfmyReader.GetBoolean(0)=FalseThenMain.TabControlMain.TabPages.Remove(Main.TabPageAdmin)EndIfMe.Hide()Main.Show()ElseMsgBox("用戶名不存在或密碼錯誤，請重新輸入！",MsgBoxStyle.OkOnly,"Warning")TextBoxAdmin.Text=""TextBoxPasswd.Text=""EndIfmyReader.Close()myConnection.Close()EndSubPrivateSubButtonQuit_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonQuit.ClickMe.Close()EndSubEndClassPublicClassMainPrivateSubMain_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.Load'TODO:Thislineofcodeloadsdataintothe'DataSet.Building'table.Youcanmove,orremoveit,asneeded.Me.BuildingTableAdapter.Fill(Me.DataSet.Building)'TODO:Thislineofcodeloadsdataintothe'DataSet.Administrator'table.Youcanmove,orremoveit,asneeded.Me.AdministratorTableAdapter.Fill(Me.DataSet.Administrator)EndSubPrivateSubMain_Closed(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.ClosedLogin.Close()EndSubPrivateSubButtonAdd_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonAdd.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()TrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandText=_"insertintoAdministratorvalues('"&ComboBoxAdmin.Text&"','',"&ComboBoxType.SelectedIndex&")"IfmyCommand.ExecuteNonQuery()Then",MsgBoxStyle.OkOnly,"添加管理員MsgBox("添加管理員成功！")EndIfmyConnection.Close()CatchexAsExceptionMsgBox("添加管理員失敗！",MsgBoxStyle.OkOnly,"添加管理員")EndTryEndSubPrivateSubButtonDelete_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonDelete.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()TrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandText=_"deletefromAdministratorwhereAdmin='"&ComboBoxAdmin.Text&"'"IfmyCommand.ExecuteNonQuery()ThenMsgBox("刪除管理員成功！",MsgBoxStyle.OkOnly,"刪除管理員")ElseMsgBox("刪除管理員失敗！",MsgBoxStyle.OkOnly,"刪除管理員")EndIfmyConnection.Close()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubButtonEdit_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonEdit.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()TrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandText=_"updateAdministratorsetPasswd='"&TextBoxPasswd.Text&"'whereAdmin='"&TextBoxUser.Text&"'"IfmyCommand.ExecuteNonQuery()ThenMsgBox("新密碼設置成功！",MsgBoxStyle.OkOnly,"修改密碼")ElseMsgBox("新密碼設置失敗！",MsgBoxStyle.OkOnly,"修改密碼")EndIfmyConnection.Close()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubComboBoxBuilding_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBoxBuilding.SelectedIndexChangedDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.StoredProceduremyCommand.Parameters.Add("@Building",SqlDbType.Char,5).Value=ComboBoxBuilding.TextmyCommand.CommandText="BuildingToDoorPlate"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)ComboBoxDoorPlate.Items.Clear()ComboBoxDoorPlate.Text=""DoWhilemyReader.ReadComboBoxDoorPlate.Items.Add(myReader.GetString(0))LoopmyReader.Close()myConnection.Close()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubComboBoxRentBuilding_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBoxRentBuilding.SelectedIndexChangedDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.StoredProceduremyCommand.Parameters.Add("@Building",SqlDbType.Char,5).Value=ComboBoxRentBuilding.TextmyCommand.CommandText="BuildingToDoorPlate"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)ComboBoxRentDoorPlate.Items.Clear()ComboBoxRentDoorPlate.Text=""DoWhilemyReader.ReadComboBoxRentDoorPlate.Items.Add(myReader.GetString(0))LoopmyReader.Close()myConnection.Close()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubButtonUnused_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonUnused.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.TextmyCommand.CommandText=_"selectdistinctbuilding,doorplate,capacityfromclassroom,arrange"+_"whereclassroom.rid=arrange.ridandclassroom.ridnotin(selectridfromarrangewhere[Weekday]="+(ComboBoxWeekday.SelectedIndex+1).ToString+"and[UTime]="+(ComboBoxTime.SelectedIndex+1).ToString+")"+_"andclassroom.ridnotin(selectridfrom[use]whereuweekday="+(ComboBoxWeekday.SelectedIndex+1).ToString+"andutime="+(ComboBoxTime.SelectedIndex+1).ToString+")"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)DoWhilemyReader.ReadClassRoom.DataGridViewClassRoom.Rows.Add()ClassRoom.DataGridViewClassRoom.Rows(ClassRoom.DataGridViewClassRoom.RowCount-1).Cells(0).Value=myReader.GetString(0)ClassRoom.DataGridViewClassRoom.Rows(ClassRoom.DataGridViewClassRoom.RowCount-1).Cells(1).Value=myReader.GetString(1)ClassRoom.DataGridViewClassRoom.Rows(ClassRoom.DataGridViewClassRoom.RowCount-1).Cells(2).Value=myReader.GetInt32(2)LoopmyReader.Close()myConnection.Close()ClassRoom.Show()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubButtonCourse_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonCourse.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.TextmyCommand.CommandText=_"selectcid,,cridethour,period,type,capacity,fromcourse,teacher"+_"like'%"+TextBoxCourse.Text+"%'andcourse.tid=teacher.tid"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)DoWhilemyReader.ReadCourse.DataGridViewCourse.Rows.Add()Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(0).Value=myReader.GetString(0)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(1).Value=myReader.GetString(1)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(2).Value=myReader.GetInt32(2)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(3).Value=myReader.GetInt32(3)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(4).Value=myReader.GetString(4)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(5).Value=myReader.GetInt32(5)Course.DataGridViewCourse.Rows(Course.DataGridViewCourse.RowCount-1).Cells(6).Value=myReader.GetString(6)LoopmyReader.Close()myConnection.Close()Course.Show()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubButtonTeacher_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonTeacher.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.TextmyCommand.CommandText=_"selectdistinctteacher.tid,,college,post,fromteacher,course"+_"whereteacher.tid=='"+TextBoxTeacher.Text+"'"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)DoWhilemyReader.ReadTeacher.DataGridViewTeacher.Rows.Add()Teacher.DataGridViewTeacher.Rows(Teacher.DataGridViewTeacher.RowCount-1).Cells(0).Value=myReader.GetString(0)Teacher.DataGridViewTeacher.Rows(Teacher.DataGridViewTeacher.RowCount-1).Cells(1).Value=myReader.GetString(1)Teacher.DataGridViewTeacher.Rows(Teacher.DataGridViewTeacher.RowCount-1).Cells(2).Value=myReader.GetString(2)Teacher.DataGridViewTeacher.Rows(Teacher.DataGridViewTeacher.RowCount-1).Cells(3).Value=myReader.GetString(3)Teacher.DataGridViewTeacher.Rows(Teacher.DataGridViewTeacher.RowCount-1).Cells(4).Value=myReader.GetString(4)LoopmyReader.Close()myConnection.Close()Teacher.Show()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubButtonRent_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonRent.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderDimTableAsStringDimIdentityAsStringTryIfComboBoxIdentity.SelectedIndexThenTable="teacher"Identity="tid"ElseTable="student"Identity="sid"EndIfmyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.TextmyCommand.CommandText=_"select*from"+Table+"where"+Identity+"='"+TextBoxRenter.Text+"'"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)IfNotmyReader.ReadThenMsgBox("身份違法",MsgBoxStyle.OkOnly,"Warning")ReturnEndIfmyReader.Close()myCommand.CommandText=_"select*fromclassroom,arrange"+_"where((classroom.rid=arrange.ridandweekday='"+(ComboBoxRentWeekday.SelectedIndex+1).ToString+"'andarrange.utime='"+(ComboBoxRentTime.SelectedIndex+1).ToString+"'))andbuilding='"+ComboBoxRentBuilding.Text+"'anddoorplate='"+ComboBoxRentDoorPlate.Text+"'"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)IfmyReader.ReadThenMsgBox("該教室有人使用",MsgBoxStyle.OkOnly,"Warning")ReturnEndIfmyReader.Close()myCommand.CommandText=_"select*fromclassroom,[use]"+_"where((classroom.rid=[use].ridanduweekday='"+(ComboBoxRentWeekday.SelectedIndex+1).ToString+"'and[use].utime='"+(ComboBoxRentTime.SelectedIndex+1).ToString+"'))andbuilding='"+ComboBoxRentBuilding.Text+"'anddoorplate='"+ComboBoxRentDoorPlate.Text+"'"+_"andyear(udate)='"+(ComboBoxYear.SelectedIndex+2000).ToString+"'andmonth(udate)='"+(ComboBoxMonth.SelectedIndex+1).ToString+"'andday(udate)='"+(ComboBoxday.SelectedIndex+1).ToString+"'"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)IfmyReader.ReadThenMsgBox("該教室有人使用",MsgBoxStyle.OkOnly,"Warning")ReturnEndIfmyReader.Close()myCommand.CommandText=_"selectridfromclassroomwherebuilding='"+ComboBoxRentBuilding.Text+"'anddoorplate='"+ComboBoxRentDoorPlate.Text+"'"myReader=myCommand.ExecuteReader()IfmyReader.ReadThenmyCommand.CommandType=CommandType.StoredProceduremyCommand.Parameters.Add("@Rid",SqlDbType.Char,5).Value=myReader.GetString(0)myCommand.Parameters.Add("@Uweekday",SqlDbType.Char,1).Value=ComboBoxRentWeekday.SelectedIndex+1myCommand.Parameters.Add("@Utime",SqlDbType.Char,1).Value=ComboBoxRentTime.SelectedIndex+1myCommand.Parameters.Add("@Uid",SqlDbType.Char,10).Value=TextBoxRenter.TextmyCommand.Parameters.Add("@Udate",SqlDbType.DateTime,8).Value=ComboBoxYear.Text+"-"+ComboBoxMonth.Text+"-"+ComboBoxday.TextmyCommand.CommandText="InsertIntoUse"myReader.Close()EndIfIfmyCommand.ExecuteNonQuery()ThenMsgBox("借用教室成功",MsgBoxStyle.OkOnly,"借用教室")ElseMsgBox("借用教室失敗",MsgBoxStyle.OkOnly,"借用教室")EndIfmyReader.Close()myConnection.Close()CatchexAsExceptionMsgBox(ex.Message,MsgBoxStyle.OkCancel,"Exception")EndTryEndSubPrivateSubComboBoxMonth_SelectedIndexChanged(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesComboBoxMonth.SelectedIndexChangedDimindexAsIntegerIfComboBoxMonth.SelectedIndex=0OrComboBoxMonth.SelectedIndex=2OrComboBoxMonth.SelectedIndex=4OrComboBoxMonth.SelectedIndex=6OrComboBoxMonth.SelectedIndex=7OrComboBoxMonth.SelectedIndex=9OrComboBoxMonth.SelectedIndex=11ThenComboBoxday.Items.Clear()Forindex=1To31ComboBoxday.Items.Add(index)NextElseIfComboBoxMonth.SelectedIndex=1ThenComboBoxday.Items.Clear()Forindex=1To28ComboBoxday.Items.Add(index)NextElseComboBoxday.Items.Clear()Forindex=1To30ComboBoxday.Items.Add(index)NextEndIfEndSubPrivateSubButtonClassRoom_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesButtonClassRoom.ClickDimmyConnectionAsNewSqlClient.SqlConnection()DimmyCommandAsNewSqlClient.SqlCommand()DimmyReaderAsSqlClient.SqlDataReaderTrymyConnection.ConnectionString=ConnectionStringmyConnection.Open()myCommand.Connection=myConnectionmyCommand.CommandType=CommandType.TextmyCommand.CommandText=_"selectarrange.utime,arrange.weekday,,fromcourse,teacher,arrange,classroom"+_"wherecourse.tid=teacher.tidandcourse.cid=arrange.cidandarrange.rid=classroom.ridandbuilding='"+ComboBoxBuilding.Text+"'anddoorplate='"+ComboBoxDoorPlate.Text+"'"myReader=myCommand.ExecuteReader(CommandBehavior.SequentialAccess)Arrange.DataGridViewArrange.Rows.Add()Arrange.DataGridViewArrange.Rows.Add()Arrange.DataGridViewArrange.Rows.Add()Arrange.DataGridViewArrange.Rows.Add()Arrange.DataGridViewArrange.Rows.Add()Arrange.DataGridViewArrange.Rows(0).Cells(0).Value="第Arrange.DataGridViewArrange.Rows(1).Cells(0).Value="第Arrange.DataGridViewArrange.Rows(2).Cells(0).Value="第Arrange.DataGridViewArrange.Rows(3).Cells(0).Value="第Arrange.DataGridViewArrange.Rows(4).Cells(0).Value="第一，二節(jié)"三，四節(jié)"五，六節(jié)"七，八節(jié)"九，十節(jié)"DoWhilemyReader.ReadDimRowAsIntegerDimCellAsIntegerDimRowTextAsStringDimCellTextAsStringRowText=myReader.GetString(0)If(RowText.Equals("1"))ThenRow=0ElseIf(RowText.Equals("2"))ThenRow=1ElseIf(RowText.Equals("3"))ThenRow=2ElseIf(RowText.Equals("4"))ThenRow=3ElseIf(RowText.Equals("5"))ThenRow=4EndIfCellText=myReader.GetString(1)If(CellText.Equals("1"))ThenCell=1ElseIf(CellText.Equals("2"))ThenCell=2ElseIf(CellText.Equals("3"))ThenCell=3ElseIf(CellText.Equals("4"))ThenCell=4ElseIf(