數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計題目：汽車銷售管理系統(tǒng)成員：戴明弟（201201050803馮聰(201201050805畢曉峰(201201050801專業(yè)：軟件工程2012—1任務(wù)書汽車銷售管理系統(tǒng)的設(shè)計與實(shí)現(xiàn)調(diào)查本地從事汽車銷售的企業(yè)，根據(jù)企業(yè)汽車銷售的情況，設(shè)計用于汽車銷售的管理系統(tǒng)，主要功能有:1）基礎(chǔ)信息管理：廠商信息、車型信息和客戶信息；2）進(jìn)貨管理：車輛采購、車輛入庫;3） 銷售管理：車輛銷售、收益統(tǒng)計；4） 倉庫管理：庫存車輛、倉庫明細(xì)、進(jìn)銷存統(tǒng)計；5）系統(tǒng)維護(hù)：如數(shù)據(jù)安全管理（含備份與恢復(fù)）、操作員管理、權(quán)限設(shè)置等;汽車銷售管理系統(tǒng)的設(shè)計與實(shí)現(xiàn)A.引言a)設(shè)計目的鞏固和加深對數(shù)據(jù)庫系統(tǒng)基礎(chǔ)理論的理解；掌握使用數(shù)據(jù)庫進(jìn)行軟件系統(tǒng)設(shè)計的基本思想和方法；提高學(xué)生運(yùn)用數(shù)據(jù)庫理論解決實(shí)際問題的能力；培養(yǎng)學(xué)生調(diào)查研究、查閱技術(shù)文獻(xiàn)、資料、手冊以及編寫技術(shù)文檔的能力。b)設(shè)計要求以MicrosoftSQLServer或MySQL作為后臺數(shù)據(jù)庫，以VisualStudio、 Eclipse等軟件作為前臺開發(fā)工具，完成一個小型數(shù)據(jù)庫應(yīng)用系統(tǒng)的設(shè)計開發(fā)。C)設(shè)計環(huán)境以MicrosoftSQLServer2012 作為后臺數(shù)據(jù)庫，以NetBeans作為開發(fā)工具，以Java為開發(fā)語言。概要設(shè)計a)系統(tǒng)需求分析.調(diào)查廠商組織情況。包括了解各組織的部門組成情況，各部門的職責(zé)等，為分析信息流程做準(zhǔn)備。調(diào)查各部門的業(yè)務(wù)活動情況。包括了解各個部門和使用什么數(shù)據(jù)，如何加工處理這些數(shù)據(jù)，輸出什么信息，輸出到什么部門，輸出結(jié)果的格式是什么，這些是調(diào)查的重點(diǎn)。

?在熟悉了業(yè)務(wù)活動的基礎(chǔ)上，協(xié)助用戶明確對新系統(tǒng)的各種要求，包括信息要求，處理要求，安全性與完整性要求，這是調(diào)查的又一重點(diǎn)。.確定新系統(tǒng)的邊界。對前面調(diào)查的結(jié)果進(jìn)行初步分析，確定哪些功能由計算機(jī)完成或?qū)頊?zhǔn)備讓計算機(jī)完成，哪些活動由人工完成。基礎(chǔ)信息管理:1、廠商信息的管理：查詢、增、刪、2、車型信息的管理：查詢、增、刪、3、客戶信息的管理：查詢、增、刪、進(jìn)貨管理:4、車輛采購信息的管理：查詢、增、刪、改54、車輛采購信息的管理：查詢、增、刪、改5、車輛入庫信息的管理：查詢、增、刪、改銷售管理:6、車輛銷售:客戶可以根據(jù)自己的需求，選擇不同型號的車型76、車輛銷售:客戶可以根據(jù)自己的需求，選擇不同型號的車型7、收益統(tǒng)計:廠商按照所出售的車輛數(shù)量及價格進(jìn)行統(tǒng)計倉庫管理:8、庫存車輛:倉庫管理員對已有車輛進(jìn)行詳細(xì)管理98、庫存車輛:倉庫管理員對已有車輛進(jìn)行詳細(xì)管理9、倉庫明細(xì):庫存各種類型車輛的詳細(xì)信息10、進(jìn)銷存統(tǒng)計：倉庫管理員對車輛的出售與進(jìn)貨進(jìn)行統(tǒng)計系統(tǒng)維護(hù):11、數(shù)據(jù)安全管理：系統(tǒng)管理員對該數(shù)據(jù)庫出現(xiàn)的問題進(jìn)行管理12、操作員管理：操作員根據(jù)車輛銷售情況及時更改數(shù)據(jù)庫13、權(quán)限設(shè)置：規(guī)定該數(shù)據(jù)庫訪問權(quán)限b）系統(tǒng)結(jié)構(gòu)設(shè)計E-R圖關(guān)系模型:車輛:車型號、車輛名稱、廠商名、價格、車輛狀態(tài)廠商：廠商名、廠商號、地址、生產(chǎn)車輛總數(shù)、廠商狀態(tài)、備注倉庫：倉庫號地址、進(jìn)貨車輛數(shù)、出貨車輛數(shù)、庫存車輛總數(shù)車輛銷售：車型號、廠商號、出售車輛數(shù)、車輛總成本、獲取總金額、利潤詳細(xì)設(shè)計a）系統(tǒng)數(shù)據(jù)庫設(shè)計CREATEDATABASE[MyDBcar]CREATEDATABASE[MyDBcar]dbo.Buy:進(jìn)貨表dbo.Car:車輛信息dbo.Dsale:訂購統(tǒng)計dbo.Fac:制造廠信息dbo.Msale:銷售統(tǒng)計dbo.Manager:管理員信息dbo.ROG:退貨統(tǒng)計b）各模塊的主要算法對應(yīng)的源代碼數(shù)據(jù)庫建立SQL語言:USE[master]GO/******Object: Database[MyDBcar] ScriptDate:2014/6/2522:38:42******/CONTAINMENT=NONEONPRIMARYALTERDATABASE [MyDBcar] SETANSIPADDINGOFFGOALTERDATABASE [MyDBcar] SETANSIWARNINGSOFFALTERDATABASE [MyDBcar] SETANSIPADDINGOFFGOALTERDATABASE [MyDBcar] SETANSIWARNINGSOFF(NAMEN'MyDB2',FILENAMENC'ProgramFiles'MicrosoftSQL,SIZE=5120KB,MAXSIZEServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDB2.mdf',SIZE=5120KB,MAXSIZEUNLIMITED,FILEGROWTH=1024KB)LOGON(NAMEN'MyDB2_log'FILENAMENC'ProgramFiles\MicrosoftSQL(NAMEN'MyDB2_log'FILENAMENC'ProgramFiles\MicrosoftSQL,SIZE=1024KB,MAXSIZE=Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDB2_log.ldf'2048GB,FILEGROWTH=10%),SIZE=1024KB,MAXSIZE=GOALTERDATABASE[MyDBcar]SETCOMPATIBILITY_LEVEL=110GOIF(1=FULLTEXTSERVICEPROPERTY('lsFullTextInstalled'))beginEXEC[MyDBcar].[dbo].[sp_fulltext_database]@action='enable'endGOALTERDATABASE[MyDBcar]SETANSINULLDEFAULTOFFALTERDATABASE[MyDBcar]SETANSINULLDEFAULTOFFGOALTERDATABASE[MyDBcar]SETANSINULLSOFFALTERDATABASE[MyDBcar]SETANSINULLSOFFGOGOALTERDATABASE [MyDBcar] SETARITHABORTOFFGOALTERDATABASE [MyDBcar] SETARITHABORTOFFALTERDATABASE [MyDBcar] SETRECURSIVETRIGGERSOFFALTERDATABASE [MyDBcar] SETRECURSIVETRIGGERSOFFGOALTERDATABASE[MyDBcar]SETAUTOCLOSEOFFGOALTERDATABASE[MyDBcar]SETAUTOCREATESTATISTICSONGOALTERDATABASE[MyDBcar]SETAUTOSHRINKOFFGOALTERDATABASE[MyDBcar]SETAUTOUPDATESTATISTICSONGOALTERDATABASE[MyDBcar]SETCURSORCLOSEONCOMMITOFFGOALTERDATABASE[MyDBcar]SETCURSORDEFAULTGLOBALGOALTERDATABASE[MyDBcar]SETCONCATNULLYIELDSNULLOFFGOALTERDATABASE[MyDBcar]SETNUMERICROUNDABORTOFFGOALTERDATABASE[MyDBcar]SETQUOTEDIDENTIFIEROFFGOGOALTERDATABASE [MyDBcar] SETDISABLEBROKERGOALTERDATABASE [MyDBcar] SETDISABLEBROKERALTERDATABASE [MyDBcar] SETMULTIUSERGOALTERDATABASE [MyDBcar] SETPAGEVERIFYCHECKSUMALTERDATABASE [MyDBcar] SETMULTIUSERGOALTERDATABASE [MyDBcar] SETPAGEVERIFYCHECKSUMGOALTERDATABASE[MyDBcar]SETAUTOUPDATESTATISTICSASYNCOFFGOALTERDATABASE[MyDBcar]SETDATECORRELATIONOPTIMIZATIONOFFGOALTERDATABASE[MyDBcar]SETTRUSTWORTHYOFFGOALTERDATABASE[MyDBcar]SETALLOWSNAPSHOTISOLATIONOFFGOALTERDATABASE[MyDBcar]SETPARAMETERIZATIONSIMPLEGOALTERDATABASE[MyDBcar]SETREADCOMMITTEDSNAPSHOTOFFGOALTERDATABASE[MyDBcar]SETHONORBROKERPRIORITYOFFGOALTERDATABASE[MyDBcar]SETRECOVERYFULLGOGOGOGOALTERDATABASE [MyDBcar] SETDBCHAININGOFFGOALTERDATABASE [MyDBcar] SETDBCHAININGOFFGOALTERDATABASE[MyDBcar]SETFILESTREAM(NON_TRANSACTED_ACCESS=OFF)GOALTERDATABASE[MyDBcar]SETTARGETRECOVERYTIME=0SECONDSGOEXECsys.sp_db_vardecimal_storage_formatN'MyDBcar',N'ON'GOUSE[MyDBcar]GO/******Object: Table[dbo].[Buy]ScriptDate:2014/6/2522:38:42******/******Object: Table[dbo].[Buy]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONGOSETQUOTEDIDENTIFIERONGOCREATETABLE[dbo].[Buy]([Date][datetime]NOTNULL,[Name][nchar](10)NULL,[Fac_Name][nchar](10)NULL,[Num][nchar](10)NULL,[Price][nchar](10)NULL

)ON[PRIMARY]GO/******Object: Table[dbo].[Car])ON[PRIMARY]GO/******Object: Table[dbo].[Car]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONGOSETQUOTEDIDENTIFIERONGOCREATETABLE[dbo].[Car]([Id][nchar](10)NOTNULL,[Name][nvarchar](max)NOTNULL,[Model][nchar](10)NULL,[Facturer][nvarchar](max)NULL,[Num][nchar](10)NULL,[Price][nchar](10)NULL)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]GO/******Object: Table[dbo].[D_sale]ScriptDate:2014/6/2522:38:42******/******Object: Table[dbo].[D_sale]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONSETQUOTEDIDENTIFIERONGOGOCREATETABLE[dbo].[D_sale]([Date][datetime]NOTNULL,[Name][nchar](10)NOTNULL,[Num][nchar](10)NOTNULL,[Price][nchar](10)NOTNULL)ON[PRIMARY]GO/******Object: Table[dbo].[Fac]ScriptDate:2014/6/2522:38:42******/******Object: Table[dbo].[Fac]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONGOSETQUOTEDIDENTIFIERONGOCREATETABLE[dbo].[Fac]([Fac_Name][nchar](10)NULL,[Name][nchar](10)NULL,[Price][nchar](10)NULL)ON[PRIMARY]GO/******Object: Table[dbo].[M_sale]ScriptDate:2014/6/2522:38:42******/SETQUOTEDIDENTIFIERONGOSETQUOTEDIDENTIFIERONGOSETANSINULLSONGOSETQUOTEDIDENTIFIERONGOCREATETABLE[dbo].[M_sale]([Name][nchar](10)NOTNULL,[Num][int]NOTNULL,[Amount][float]NOTNULL,CONSTRAINT[PK_M_sale]PRIMARYKEYCLUSTERED[Name]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOIMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GO/******Object: Table[dbo].[Manager]ScriptDate:2014/6/2522:38:42******/******Object: Table[dbo].[Manager]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONGOCREATETABLE[dbo].[Manager]([M_Name][nchar](10)NOTNULL,[M_password][nvarchar](max)NOTNULL,[Major][nchar](10)NOTNULL,CONSTRAINT[PK_Table_1]PRIMARYKEYCLUSTERED[M_Name]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOIMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]TEXTIMAGEON[PRIMARY]GO/******Object:Table[dbo].[ROG]ScriptDate:2014/6/2522:38:42******)ON[PRIMARY]TEXTIMAGEON[PRIMARY]GO/******Object:Table[dbo].[ROG]ScriptDate:2014/6/2522:38:42******/SETANSINULLSONGOSETQUOTEDIDENTIFIERONGOCREATETABLE[dbo].[ROG]([Date][datetime]NOTNULL,[Name][nchar](10)NOTNULL,[Num][nchar](10)NULL,[Price][nchar](10)NULL[Date][datetime]NOTNULL,[Name][nchar](10)NOTNULL,[Num][nchar](10)NULL,[Price][nchar](10)NULL)ON[PRIMARY]returncon;returncon;initComponents();initComponents();}}GOUSE[master]GOALTERDATABASE[MyDBcar]SETREAD_WRITEGONetbeans鏈接數(shù)據(jù)庫代碼:publicclassAccessConnection{publicAccessConnection(){}staticConnectiongetCon(){Connectioncon=null;try{Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=MyDBcar","sa","strike");catch(Exceptione){System.out.println(” 連接數(shù)據(jù)庫出錯；"+e);項目運(yùn)行實(shí)況與部分代碼publicclass 登陸界面extendsjavax.swing.JFrame{Stringuser,password;Connectioncon=null;Statementsql=null;ResultSetrs=null;booleanflag=false;/***CreatesnewformLaunch1*/public登陸界面（）｛{{privatevoidjButtonIActionPerformed(java.awt.event.ActionEventevt){//TODOaddyourhandlingcodehere:user=jTextField1.getText().tnm();password=jPasswordField1.getText().trim();inttype=0;Stringinq=newString("select*FromManagerwhereM_Name='"+user+"”'+"andM_password='"+password+"”');try{con=AccessConnection.getCon();sql=con.createStatement();rs=sql.executeQuery(inq);while(rs.next())flag=true;con.close();catch(SQLExceptione4){System.out.printin(”查詢”+e4);}if(flag)//TODOaddyourhandlingcodehere://TODOaddyourhandlingcodehere:privatevoidjButton4ActionPerformed(java.awt.event.ActionEventevt){privatevoidjButton4ActionPerformed(java.awt.event.ActionEventevt){flag=false;new管理界面(newString(user));this.dispose();else用戶名或者密碼出錯！用戶名或者密碼出錯！");JOptionPane.showMessageDialog(nuII,"更換用戸誚售* J退賞1查詢1privatevoidjButton1ActionPerformed(java.awt.event.ActionEventevt){//TODOaddyourhandlingcodehere:new查詢界面().setVisible(true);//TODOaddyourhandlingcodehere:this.dispose();privatevoidjButton2ActionPerformed(java.awt.event.ActionEventevt){//TODOaddyourhandlingcodehere:new銷售管理().setVisible(true);privatevoidjButton3ActionPerformed(java.awt.event.ActionEventevt){//TODOaddyourhandlingcodehere:new庫存統(tǒng)計().setVisible(true);PrivatevoidjButton1ActionPerformed(java.awt.event.ActionEventevt){year=jComboBox1.getSelectedltem().toString();year=jComboBox1.getSelectedltem().toString();con=AccessConnection.getCon();con=AccessConnection.getCon();month=jComboBox2.getSelectedltem().toString();DefaultTableModelmodel=(DefaultTableModel)jTable1.getModel();Object[]arry=newObject[5];while(model.getRowCount()>0){model.removeRow(model.getRowCount()-1);Calendarc=Calendar.getInstance();c.set(Calendar.YEAR,Integer.parseInt(year.trim()));c.set(Calendar.MONTH,Integer.parselnt(month.trim())-1);Stringst=newString(year+"-"+month+"-1");Stringed=newString(year+"-"+month+"-"+c.getActualMaximum(Calendar.DATE));Stringdel=newString("truncatetableM_sale");Operation.Update(del);boolean_flag=false;intsum_n=0;doublesum_p=0;Stringinq=newString("select*fromD_salewhereDatebetween'"+st+"'and'"+ed+"”');try{sql=con.createStatement();sql=con.createStatement();Double.parseDouble(_rs.getString(3).trim());Double.parseDouble(_rs.getString(3).trim());+name+"'"rs=sql.executeQuery(inq);while(rs.next()){_flag=true;name=rs.getString(2);num=rs.getString(3);price=rs.getString(4);sum_n+=Integer.parseInt(num.trimO);sum_p+=Double.parseDouble(price.trim());String_inq=newString("select*fromM_salewhereName='"try{Connection_con=AccessConnection.getCon();Statement_sql=_con.createStatement();ResultSet_rs=_sql.executeQuery(_inq);while(_rs.next()){flag=true;intn=Integer.pars