1、NBA球員管理系統 數據庫原理課程設計報告 NBA球員管理系統設計與實現 學 院： 信息學院 專業班級： 計算機科學與技術1班 組員姓名： 王治銘 201424020105 陳昭儒 201424020106 殷鵬飛 201424020110 指導老師： 李曉莉 完成日期： 2016-6-17 一、 概述1.1 背景說明軟件名稱：NBA球員信息管理系統用戶：球隊數據分析師1.2 開發環境操作系統 Windows 7軟件配置Visual Basic 6.0 & SQL Server 2014二、 需求分析2.1 用戶需求NBA發展至今，整個聯盟涵括了三十支球隊，每支球隊的球員名單上限在常規

2、賽時期可以達到15位，加上不計其數的自由球員，管理并分析這些球員的生涯數據成為了一件棘手的事，NBA球員信息管理系統可以師解決這個問題。本系統面向的對象為球隊數據分析師，對于專業人士，他們不僅有權瀏覽數據，也可以對數據進行修改、刪除，用戶共享數據并更新數據。2.2 主要功能本系統從用戶的需求出發，可實現以下功能： a) 球員數據瀏覽b) 球員數據修改c) 球員數據刪除2.3 數據字典名稱數據類型字段描述charchar球員姓名Pragesmallint球員年齡Pnosmallint球衣號碼PIDchar球員代號Pcapitalmoney球員薪金Tnamechar隸屬球隊名稱TIDchar隸屬球

3、隊代號ppgfloat場均得分rpgfloat場均籃板apgfloat場均助攻spgfloat場均搶斷bpgfloat場均蓋帽Pbesmallint最近一場得分三、 數據庫概念結構設計根據需求分析，可知本系統有以下E-R圖：球員薪金隸屬球隊代號隸屬球隊名稱球衣號碼球員年齡球員代號球員姓名球員信息3.1 球員信息E-R圖3.2 球員數據E-R圖場均搶斷場均助攻場均籃板場均蓋帽球員代號場均得分球員數據 球員姓名3.3 球員表現E-R圖最近一場得分球員表現球員姓名球員代號 四、 邏輯結構設計4.1球員信息表列名NULL約束PIDNOT NULLprimary keyPnameNOT NULLPcap

4、italNOT NULLPrageNOT NULLPnoNOT NULLTnameNOT NULLTIDNOT NULL4.2球員數據表列名NULL約束PIDNOT NULLprimary keyPnameNOT NULLppgNOT NULLrpgNOT NULLapgNOT NULLspgNOT NULLbpgNOT NULL4.3 球員表現表列名NULL約束PIDNOT NULLprimary keyPnameNOT NULLPbeNOT NULL五、 創建數據庫及其對象5.1 建表5.1.1 球員信息表create table player(PID char(10) NOT NULL,

5、Pname char(25) NOT NULL,Pcapital money NOT NULL,Prage smallint NOT NULL,Tname char(30) NOT NULL,Pno smallint NOT NULL,TID char(15) NOT NULL,primary key (PID,Pname)5.1.2 球員數據表create table playerData (PID char(10) NOT NULL,Pname char(25) NOT NULL,ppg float NOT NULL,rpg float NOT NULL,apg float NOT NUL

6、L,spg float NOT NULL,bpg float NOT NULL,primary key (PID,Pname)5.1.3 球員表現表create table behave(PID char(10) NOT NULL,Pbe smallint NOT NULL,Pname char(25) NOT NULLprimary key (PID)六、 基于VB的UI設計6.1 Form1窗口Private Sub Add_Click() Adodc1.Recordset.AddNew Add.Enabled = False Del.Enabled = False Edit.Enable

7、d = True Cancel.Enabled = TrueEnd SubPrivate Sub Cancel_Click()Adodc1.Recordset.CancelUpdateAdd.Enabled = TrueDel.Enabled = TrueCancel.Enabled = FalseEnd SubPrivate Sub Command1_Click()/打開form2窗口Form2.ShowEnd SubPrivate Sub Del_Click() Dim res As Integer res = MsgBox("確實要刪除此行嗎？", vbExclama

8、tion + vbYesNo + vbDefaultButton2) If res = vbYes Then Adodc1.Recordset.Delete Adodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF = True Then Adodc1.Recordset.MoveLast End If End IfEnd SubPrivate Sub Edit_Click()Adodc1.Recordset.Fields("Pno") = Trim(TextPno.Text)Adodc1.Recordset.Fields("

9、;Pname") = Trim(TextPname.Text)Adodc1.Recordset.Fields("Prage") = Trim(TextPrage.Text)Adodc1.Recordset.Fields("Pcapital") = Trim(TextPcpt.Text)Adodc1.Recordset.Fields("PID") = Trim(TextPID.Text)Adodc1.Recordset.Fields("TID") = Trim(TextTID.Text)Adodc1.Rec

10、ordset.Fields("Tname") = Trim(TextTname.Text)Adodc1.Recordset.UpdateAdd.Enabled = TrueDel.Enabled = TrueCancel.Enabled = FalseEnd SubPrivate Sub Exit_Click() EndEnd SubPrivate Sub First_Click() Adodc1.Recordset.MoveFirstEnd SubPrivate Sub Last_Click()Adodc1.Recordset.MoveLastEnd SubPrivate

11、 Sub Next_Click()Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF = True Then Adodc1.Recordset.MoveLast End IfEnd SubPrivate Sub Previous_Click() Adodc1.Recordset.MovePrevious If Adodc1.Recordset.BOF = True Then Adodc1.Recordset.MoveFirst End IfEnd SubPrivate Sub Print_Click()/打開apg窗口 apg.ShowEnd S

12、ubPrivate Sub Form_Load() Cancel.Enabled = False Combo1.AddItem "球員信息" Combo1.AddItem "球員數據" Combo1.AddItem "球員表現" Combo1.AddItem "球員表現對比" Dim strPID As String Dim strSelect As String strPID = Trim(TextPID.Text) strSelect = "select * from player" Ado

13、dc1.CommandType = adCmdText Adodc1.RecordSource = strSelect Adodc1.Refresh Set DtgCond.DataSource = Adodc1 Call InitGird Dim strPID1 As String Dim strSelect1 As String strPID1 = Trim(TextPID.Text) strSelect1 = "select * from playerData" Adodc2.CommandType = adCmdText Adodc2.RecordSource =

14、strSelect1 Adodc2.Refresh Set DtgCond1.DataSource = Adodc2 Call InitGird1 Dim strPID2 As String Dim strSelect2 As String strPID2 = Trim(TextPID.Text) strSelect2 = "select * from behave" Adodc3.CommandType = adCmdText Adodc3.RecordSource = strSelect2 Adodc3.Refresh Set DtgCond2.DataSource =

15、 Adodc3 Call InitGird2 Dim strPID3 As String Dim strSelect3 As String strPID3 = Trim(TextPID.Text) strSelect3 = "select * from playerData join behave on playerData.PID = behave.PID " Adodc4.CommandType = adCmdText Adodc4.RecordSource = strSelect3 Adodc4.Refresh Set DtgCond3.DataSource = Ad

16、odc4 End SubPrivate Sub InitGird() With DtgCond .Columns(0).Caption = "ID" .Columns(1).Caption = "Name" .Columns(2).Caption = "Capital" .Columns(3).Caption = "Age" .Columns(4).Caption = "Team" .Columns(5).Caption = "No" .Columns(6).Caption

17、= "Team ID" .Columns(0).Width = 1000 .Columns(1).Width = 1000 .Columns(2).Width = 1000 .Columns(3).Width = 1000 End WithEnd SubPrivate Sub InitGird1() With DtgCond1 .Columns(0).Caption = "ID" .Columns(1).Caption = "Name" .Columns(2).Caption = "ppg" .Columns(3)

18、.Caption = "rpg" .Columns(4).Caption = "apg" .Columns(5).Caption = "spg" .Columns(6).Caption = "bpg" .Columns(0).Width = 1000 .Columns(1).Width = 1000 .Columns(2).Width = 1000 .Columns(3).Width = 1000 End WithEnd SubPrivate Sub InitGird2() With DtgCond2 .Colum

19、ns(0).Caption = "ID" .Columns(1).Caption = "pbe" .Columns(2).Caption = "Name" .Columns(0).Width = 1000 .Columns(1).Width = 1000 .Columns(2).Width = 1000 End WithEnd SubPrivate Sub Combo1_Click()If Combo1.Text = "球員信息" ThenFrame1.ZOrder 0ElseIf Combo1.Text = &q

20、uot;球員數據" ThenFrame2.ZOrder 0ElseIf Combo1.Text = "球員表現" ThenDtgCond2.ZOrder 0ElseIf Combo1.Text = "球員表現對比" ThenDtgCond3.ZOrder 0End IfEnd Sub6.2 Form2窗口Private Sub Add_Click() Adodc2.Recordset.AddNew Add.Enabled = False Del.Enabled = False Edit.Enabled = True Cancel.Enabled

21、 = TrueEnd SubPrivate Sub Cancel_Click()Adodc2.Recordset.CancelUpdateAdd.Enabled = TrueDel.Enabled = TrueCancel.Enabled = FalseEnd SubPrivate Sub Del_Click() Dim res As Integer res = MsgBox("確實要刪除此行嗎？", vbExclamation + vbYesNo + vbDefaultButton2) If res = vbYes Then Adodc2.Recordset.Delete

22、 Adodc2.Recordset.MoveNextIf Adodc2.Recordset.EOF = True Then Adodc2.Recordset.MoveLast End If End IfEnd SubPrivate Sub Edit_Click()Adodc2.Recordset.Fields("Pbe") = Trim(TextPbe.Text)Adodc2.Recordset.Fields("Pname") = Trim(TextPname.Text)Adodc2.Recordset.Fields("PID") =

23、 Trim(TextPID.Text)Adodc2.Recordset.UpdateAdd.Enabled = TrueDel.Enabled = TrueCancel.Enabled = FalseEnd SubPrivate Sub Exit_Click() EndEnd SubPrivate Sub First_Click() Adodc2.Recordset.MoveFirstEnd SubPrivate Sub Last_Click()Adodc2.Recordset.MoveLastEnd SubPrivate Sub Next_Click()Adodc2.Recordset.Mo

24、veNext If Adodc2.Recordset.EOF = True Then Adodc2.Recordset.MoveLast End IfEnd SubPrivate Sub Previous_Click() Adodc2.Recordset.MovePrevious If Adodc2.Recordset.BOF = True Then Adodc2.Recordset.MoveFirst End IfEnd SubPrivate Sub Form_Load() Cancel.Enabled = FalseEnd Sub6.3 apg窗口Private Sub 

25、;Add_Click() Adodc2.Recordset.AddNew Add.Enabled = False Del.Enabled = False Edit.Enabled = True Cancel.Enabled = TrueEnd SubPrivate Sub Cancel_Click()Adodc2.Recordset.CancelUpdateAdd.Enabled = TrueDel.Enabled&

26、#160;= TrueCancel.Enabled = FalseEnd SubPrivate Sub Del_Click() Dim res As Integer res = MsgBox("確實要刪除此行嗎？", vbExclamation + vbYesNo + vbDefaultButton2) If res = vbYes Then

27、60;Adodc2.Recordset.Delete Adodc2.Recordset.MoveNextIf Adodc2.Recordset.EOF = True Then     Adodc2.Recordset.MoveLast   End If End IfEnd SubPrivate Sub Edit_Click()Adodc2.Recordset.Fields("ppg")&

28、#160;= Trim(Textppg.Text)Adodc2.Recordset.Fields("Pname") = Trim(TextPname.Text)Adodc2.Recordset.Fields("rpg") = Trim(Textrpg.Text)Adodc2.Recordset.Fields("apg") = Trim(Textapg.Text)Adodc2.Recordset.Fields("PID") = Trim(TextPID.Text)Adodc2.Recordset.Fields("bpg") = Trim(Textbpg.Text)Adodc2.Recordset.Fields("spg") = Trim(Textspg.Text)Adodc2.Recordset.UpdateAdd.Enabled = TrueDel.Enabled = TrueCancel.Enabled = FalseEn