汽车管理系统课程设计.doc

数据库系统概论课程设计题目： 汽车销售管理系统 成员：戴明弟（201201050803） 冯 聪（201201050805） 毕晓峰（201201050801）专业：软件工程20121任务书汽车销售管理系统的设计与实现调查本地从事汽车销售的企业，根据企业汽车销售的情况，设计用于汽车销售的管理系统，主要功能有：1)基础信息管理：厂商信息、车型信息和客户信息；2)进货管理：车辆采购、车辆入库；3)销售管理：车辆销售、收益统计；4)仓库管理：库存车辆、仓库明细、进销存统计；5)系统维护：如数据安全管理（含备份与恢复）、操作员管理、权限设置等；汽车销售管理系统的设计与实现A.引言a)设计目的 巩固和加深对数据库系统基础理论的理解；掌握使用数据库进行软件系统设计的基本思想和方法；提高学生运用数据库理论解决实际问题的能力；培养学生调查研究、查阅技术文献、资料、手册以及编写技术文档的能力。b)设计要求 以Microsoft SQL Server或MySQL作为后台数据库，以Visual Studio、 Eclipse等软件作为前台开发工具，完成一个小型数据库应用系统的设计开发。c)设计环境 以Microsoft SQL Server 2012 作为后台数据库，以NetBeans作为开发工具，以Java为开发语言。B.概要设计a)系统需求分析（1）.调查厂商组织情况。包括了解各组织的部门组成情况，各部门的职责等，为分析信息流程做准备。（2）.调查各部门的业务活动情况。包括了解各个部门和使用什么数据，如何加工处理这些数据，输出什么信息，输出到什么部门，输出结果的格式是什么，这些是调查的重点。（3）.在熟悉了业务活动的基础上，协助用户明确对新系统的各种要求，包括信息要求，处理要求，安全性与完整性要求，这是调查的又一重点。（4）.确定新系统的边界。对前面调查的结果进行初步分析，确定哪些功能由计算机完成或将来准备让计算机完成，哪些活动由人工完成。基础信息管理：1、厂商信息的管理：查询、增、删、改2、车型信息的管理：查询、增、删、改3、客户信息的管理：查询、增、删、改进货管理：4、车辆采购信息的管理：查询、增、删、改5、车辆入库信息的管理：查询、增、删、改销售管理：6、车辆销售：客户可以根据自己的需求，选择不同型号的车型7、收益统计：厂商按照所出售的车辆数量及价格进行统计仓库管理：8、库存车辆：仓库管理员对已有车辆进行详细管理9、仓库明细：库存各种类型车辆的详细信息10、进销存统计：仓库管理员对车辆的出售与进货进行统计系统维护：11、数据安全管理：系统管理员对该数据库出现的问题进行管理12、操作员管理：操作员根据车辆销售情况及时更改数据库13、权限设置：规定该数据库访问权限b)系统结构设计E-R图关系模型：车辆：车型号、车辆名称、厂商名、价格、车辆状态厂商：厂商名、厂商号、地址、生产车辆总数、厂商状态、备注仓库：仓库号、地址、进货车辆数、出货车辆数、库存车辆总数车辆销售：车型号、厂商号、出售车辆数、车辆总成本、获取总金额、利润C.详细设计a)系统数据库设计dbo.Buy：进货表dbo.Car:车辆信息dbo.D_sale:订购统计dbo.Fac:制造厂信息dbo.M_sale:销售统计dbo.Manager:管理员信息dbo.ROG:退货统计b)各模块的主要算法对应的源代码数据库建立SQL语言:USE masterGO/* Object: Database MyDBcar Script Date: 2014/6/25 22:38:42 */CREATE DATABASE MyDBcar CONTAINMENT = NONE ON PRIMARY ( NAME = NMyDB2, FILENAME = NC:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDB2.mdf , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = NMyDB2_log, FILENAME = NC:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDB2_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE MyDBcar SET COMPATIBILITY_LEVEL = 110GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled)beginEXEC MyDBcar.dbo.sp_fulltext_database action = enableendGOALTER DATABASE MyDBcar SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE MyDBcar SET ANSI_NULLS OFF GOALTER DATABASE MyDBcar SET ANSI_PADDING OFF GOALTER DATABASE MyDBcar SET ANSI_WARNINGS OFF GOALTER DATABASE MyDBcar SET ARITHABORT OFF GOALTER DATABASE MyDBcar SET AUTO_CLOSE OFF GOALTER DATABASE MyDBcar SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE MyDBcar SET AUTO_SHRINK OFF GOALTER DATABASE MyDBcar SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE MyDBcar SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE MyDBcar SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE MyDBcar SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE MyDBcar SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE MyDBcar SET QUOTED_IDENTIFIER OFF GOALTER DATABASE MyDBcar SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE MyDBcar SET DISABLE_BROKER GOALTER DATABASE MyDBcar SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE MyDBcar SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE MyDBcar SET TRUSTWORTHY OFF GOALTER DATABASE MyDBcar SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE MyDBcar SET PARAMETERIZATION SIMPLE GOALTER DATABASE MyDBcar SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE MyDBcar SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE MyDBcar SET RECOVERY FULL GOALTER DATABASE MyDBcar SET MULTI_USER GOALTER DATABASE MyDBcar SET PAGE_VERIFY CHECKSUM GOALTER DATABASE MyDBcar SET DB_CHAINING OFF GOALTER DATABASE MyDBcar SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GOALTER DATABASE MyDBcar SET TARGET_RECOVERY_TIME = 0 SECONDS GOEXEC sys.sp_db_vardecimal_storage_format NMyDBcar, NONGOUSE MyDBcarGO/* Object: Table dbo.Buy Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.Buy(Date datetime NOT NULL,Name nchar(10) NULL,Fac_Name nchar(10) NULL,Num nchar(10) NULL,Price nchar(10) NULL) ON PRIMARYGO/* Object: Table dbo.Car Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.Car(Id nchar(10) NOT NULL,Name nvarchar(max) NOT NULL,Model nchar(10) NULL,Facturer nvarchar(max) NULL,Num nchar(10) NULL,Price nchar(10) NULL) ON PRIMARY TEXTIMAGE_ON PRIMARYGO/* Object: Table dbo.D_sale Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.D_sale(Date datetime NOT NULL,Name nchar(10) NOT NULL,Num nchar(10) NOT NULL,Price nchar(10) NOT NULL) ON PRIMARYGO/* Object: Table dbo.Fac Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.Fac(Fac_Name nchar(10) NULL,Name nchar(10) NULL,Price nchar(10) NULL) ON PRIMARYGO/* Object: Table dbo.M_sale Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.M_sale(Name nchar(10) NOT NULL,Num int NOT NULL,Amount float NOT NULL, CONSTRAINT PK_M_sale PRIMARY KEY CLUSTERED (Name ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO/* Object: Table dbo.Manager Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.Manager(M_Name nchar(10) NOT NULL,M_password nvarchar(max) NOT NULL,Major nchar(10) NOT NULL, CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (M_Name ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY TEXTIMAGE_ON PRIMARYGO/* Object: Table dbo.ROG Script Date: 2014/6/25 22:38:42 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.ROG(Date datetime NOT NULL,Name nchar(10) NOT NULL,Num nchar(10) NULL,Price nchar(10) NULL) ON PRIMARYGOUSE masterGOALTER DATABASE MyDBcar SET READ_WRITE GONetbeans链接数据库代码：public class AccessConnection public AccessConnection() static Connection getCon() Connection con = null; try Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver); con = DriverManager.getConnection(jdbc:sqlserver:/localhost:1433; DatabaseName=MyDBcar,sa,strike); catch(Exception e) System.out.println(连接数据库出错;+e); return con; 项目运行实况与部分代码public class 登陆界面 extends javax.swing.JFrame String user,password; Connection con = null; Statement sql = null; ResultSet rs = null; boolean flag = false; /* * Creates new form Launch1 */ public 登陆界面() initComponents(); private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: user = jTextField1.getText().trim(); password = jPasswordField1.getText().trim(); int type = 0; String inq = new String(select * From Manager where M_Name=+ user + + and M_password=+ password +); try con = AccessConnection.getCon(); sql = con.createStatement(); rs = sql.executeQuery(inq); while(rs.next() flag = true; con.close(); catch(SQLException e4) System.out.println(查询 + e4); if(flag) flag = false; new 管理界面(new String(user); this.dispose(); else JOptionPane.showMessageDialog(null,用户名或者密码出错！); private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: new 查询界面().setVisible(true); private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: this.dispose(); private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: new 销售管理().setVisible(true); private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: new 库存统计().setVisible(true); private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) / TODO add your handling code here: year = jComboBox1.getSelectedItem().toString(); month = jComboBox2.getSelectedItem().toString(); DefaultTableModel model = (DefaultTableModel) jTable1.getModel(); Object arry = new Object5; while (model.getRowCount() 0) model.removeRow(model.getRowCount() - 1); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, Integer.parseInt(year.trim(); c.set(Calendar.MONTH, Integer.parseInt(month.trim() - 1); String st = new String(year + - + month + -1); String ed = new String(year + - + month + - + c.getActualMaximum(Calendar.DATE); String del = new String(truncate table M_sale); Operation.Update(del); boolean _flag = false; int sum_n = 0; double sum_p = 0; String inq = new String(select *from D_sale where Date between + st + and + ed + ); try con = AccessConnection.getCon(); sql = con.createStatement(); 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.trim(); sum_p += Double.parseDouble(price.trim(); String _inq = new String(select *from M_sale where Name = + name + ); try Connection _con = AccessConnection.getCon(); Statement _sql = _con.createStatement(); ResultSet _rs = _sql.executeQuery(_inq); while (_rs.next() fl