云南大學(xué)數(shù)據(jù)庫(kù)期末大作業(yè)：數(shù)據(jù)庫(kù)設(shè)計(jì).doc

《云南大學(xué)數(shù)據(jù)庫(kù)期末大作業(yè)：數(shù)據(jù)庫(kù)設(shè)計(jì).doc》由會(huì)員分享，可在線(xiàn)閱讀，更多相關(guān)《云南大學(xué)數(shù)據(jù)庫(kù)期末大作業(yè)：數(shù)據(jù)庫(kù)設(shè)計(jì).doc（20頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

云南大學(xué)軟件學(xué)院 實(shí)驗(yàn)報(bào)告 課程： 數(shù)據(jù)庫(kù)原理與實(shí)用技術(shù)實(shí)驗(yàn) 學(xué)期： 任課教師： 專(zhuān)業(yè)： 學(xué)號(hào)： 姓名： 成績(jī)： 期末大作業(yè)： Electronic Ventor 數(shù)據(jù)庫(kù)設(shè)計(jì) 一、實(shí)驗(yàn)?zāi)康? （1）掌握數(shù)據(jù)庫(kù)設(shè)計(jì)的基本方法 （2）掌握各種數(shù)據(jù)庫(kù)對(duì)象的設(shè)計(jì)方法 （3）熟練掌握DBA必須具備的技能 二、實(shí)驗(yàn)內(nèi)容 1、 根據(jù)項(xiàng)目的應(yīng)用和項(xiàng)目的需求說(shuō)明文檔，進(jìn)行詳細(xì)的需求分析，給出需求分析的結(jié)果。 （1） 客戶(hù)可以在網(wǎng)站上注冊(cè)，注冊(cè)的客戶(hù)要提供客戶(hù)的姓名、電話(huà)、地址，以方便售后和聯(lián)系，姓名即作為用戶(hù)名，和密碼一起用于注冊(cè)和登錄，客戶(hù)編號(hào)可唯一識(shí)別用戶(hù)，卡號(hào)可網(wǎng)上支付。其中地址、電話(huà)以方便聯(lián)系和寄貨； （2） 網(wǎng)站管理員可以登記各種商品，供客戶(hù)查詢(xún)，訂購(gòu)。登記商品時(shí)要提供商品的名稱(chēng)、價(jià)格，商店中現(xiàn)有商品量，商品編號(hào)可唯一識(shí)別商品； （3） 類(lèi)別表示商品所屬類(lèi)別，類(lèi)別編號(hào)可唯一識(shí)別類(lèi)別，其中包含了，商品類(lèi)別名稱(chēng)和制造廠商，可以對(duì)商品進(jìn)行分類(lèi)售賣(mài)； （4） 客戶(hù)可以在網(wǎng)上下訂單，也可以到實(shí)體店購(gòu)物，其在訂單上所選擇的支付方式不同（信用卡、借記卡、現(xiàn)金，現(xiàn)金代表實(shí)體店購(gòu)物），網(wǎng)站管理員可以查看訂單，并及時(shí)將訂單的處理情況更新（比如貨物已寄出的信息，訂單狀態(tài)：0：未處理，1：已處理，2：已發(fā)貨）；訂單編號(hào)可唯一識(shí)別訂單，訂單中包含訂單產(chǎn)生時(shí)間，訂單狀態(tài)，支付方式和支付總額； （5） 實(shí)體商店有自己的店名，賣(mài)多種商品，每個(gè)商店都有固定的地址，顧客可以到店中買(mǎi)商品，（注：在實(shí)體店中購(gòu)買(mǎi)商品的顧客一律將顧客名默認(rèn)為佚名），當(dāng)商店中的庫(kù)存量小于10時(shí)會(huì)有提醒到倉(cāng)庫(kù)中拿貨； （6） 配送單中包含查詢(xún)號(hào)可唯一識(shí)別配送單，配送人，聯(lián)系方式； （7） 倉(cāng)庫(kù)中倉(cāng)庫(kù)編號(hào)可唯一識(shí)別倉(cāng)庫(kù)，其中每個(gè)倉(cāng)庫(kù)都有區(qū)號(hào)，代表其地址 。 （8） 各實(shí)體間關(guān)系 1） 一個(gè)客戶(hù)可以購(gòu)買(mǎi)多種商品，一種商品可以被多個(gè)客戶(hù)購(gòu)買(mǎi)； 2） 一個(gè)商品屬于且僅屬于一種類(lèi)別，一種類(lèi)別的商品可以包含多個(gè)商品或沒(méi)有； 3） 一種商品放在多個(gè)商店中銷(xiāo)售，一個(gè)商店至少銷(xiāo)售一種或銷(xiāo)售多種商品； 4） 一個(gè)訂單對(duì)應(yīng)一個(gè)客戶(hù)，一個(gè)客戶(hù)對(duì)應(yīng)多個(gè)訂單； 5） 一個(gè)訂單對(duì)應(yīng)至少有一件商品或多件，一個(gè)商品對(duì)應(yīng)多個(gè)訂單； 6） 一個(gè)訂單可以有一個(gè)商品配送單 7） 一個(gè)倉(cāng)庫(kù)可以存放多種商品，一種商品可以存放在一個(gè)倉(cāng)庫(kù)； 2、 數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)：用文字簡(jiǎn)要描述實(shí)體之間的聯(lián)系，畫(huà)出E-R圖（標(biāo)出各聯(lián)系中實(shí)體的基數(shù)）。 客戶(hù)-商品：n-n; 商品-類(lèi)別：n-1； 商品-商店：n-n; 客戶(hù)-訂單：1-n; 訂單-商品：1-n; 訂單-配送當(dāng)：1-1; 倉(cāng)庫(kù)-商品：1-n 3、數(shù)據(jù)庫(kù)物理設(shè)計(jì)：將E-R圖轉(zhuǎn)換為數(shù)據(jù)表。需要給出詳細(xì)的轉(zhuǎn)換規(guī)則，對(duì)應(yīng)生成的表，屬性（主屬性、描述性屬性，多值屬性，符合屬性等），主鍵，外鍵，約束（取值是否NULL等），索引（三種類(lèi)型的索引至少每種出現(xiàn)一次）等。 在客戶(hù)，配送單，類(lèi)別中建立索引，語(yǔ)句在4題中 如3中，有2個(gè)n-n關(guān)系，分生成訂購(gòu)表和銷(xiāo)售表 4、SQL中創(chuàng)建語(yǔ)句的使用：根據(jù)第二步中的結(jié)果，將相應(yīng)的表，屬性，主鍵，外鍵，約束等使用標(biāo)準(zhǔn)的SQL CREATE語(yǔ)句實(shí)現(xiàn)。（要求給出創(chuàng)建的標(biāo)準(zhǔn)語(yǔ)句，以及創(chuàng)建之后在SQL數(shù)據(jù)庫(kù)中的腳本和截圖）。 create table 客戶(hù) ( 客戶(hù)編號(hào) char(5) not null unique, 姓名 varchar(10), 密碼 varchar(15), 電話(huà) numeric(11), 地址 varchar(20), 卡號(hào) char(19), constraint PK_客戶(hù) primary key (客戶(hù)編號(hào)) ); create table 類(lèi)別 ( 類(lèi)別編號(hào) char(5) not null unique, 類(lèi)別名稱(chēng) varchar(10), 制造商 varchar(10) , constraint PK_類(lèi)別 primary key (類(lèi)別編號(hào)) ); create unique index 類(lèi)別_PK on 類(lèi)別 ( 類(lèi)別編號(hào) ASC ); create table 倉(cāng)庫(kù) ( 倉(cāng)庫(kù)編號(hào) char(5) not null unique, 倉(cāng)庫(kù)區(qū)號(hào) varchar(5), constraint PK_倉(cāng)庫(kù) primary key (倉(cāng)庫(kù)編號(hào)) ); create table 商品 ( 商品編號(hào) char(5) not null unique, 倉(cāng)庫(kù)編號(hào) char(5) not null, 類(lèi)別編號(hào) char(5) not null, 商品名稱(chēng) varchar(50), 價(jià)格 float, constraint PK_商品 primary key (商品編號(hào)), constraint fk_倉(cāng)庫(kù)編號(hào) foreign key(倉(cāng)庫(kù)編號(hào)) references 倉(cāng)庫(kù), constraint fk_類(lèi)別編號(hào) foreign key(類(lèi)別編號(hào))references 類(lèi)別 ); create unique clustered index 客戶(hù)_PK on 客戶(hù) ( 客戶(hù)編號(hào) ASC ); create table 訂單 ( 訂單編號(hào) char(5) not null, 客戶(hù)編號(hào) char(5), 訂購(gòu)時(shí)間 varchar(10), 支付方式 varchar(10), 訂單狀態(tài) numeric, 總額 float , constraint PK_訂單 primary key (訂單編號(hào)), constraint fk_客戶(hù)編號(hào) foreign key(客戶(hù)編號(hào))references 客戶(hù) ); create table 配送單 ( 查詢(xún)號(hào) char(10) not null unique, 訂單編號(hào) char(5) not null, 配送人 varchar(10), 聯(lián)系電話(huà) numeric(11), constraint PK_配送單 primary key (查詢(xún)號(hào)), constraint fk_訂單編號(hào) foreign key(訂單編號(hào)) references 訂單 ); create clustered index 配送_FK on 配送單 ( 訂單編號(hào) ASC ); create unique index 配送單_PK on 配送單 ( 查詢(xún)號(hào) ASC ); create table 商店 ( 商店號(hào) char(5) not null unique, 商店名 char(10), 地址 varchar(20), constraint PK_商店 primary key (商店號(hào)) ); create table 銷(xiāo)售 ( 商店號(hào) char(5) not null, 商品編號(hào) char(5) not null, 庫(kù)存量 int, constraint PK_銷(xiāo)售 primary key(商店號(hào), 商品編號(hào)) ); create table 訂購(gòu) ( 訂單編號(hào) char(5) not null, 商品編號(hào) char(5) not null, 數(shù)量 int, 金額 float, constraint PK_訂購(gòu) primary key (訂單編號(hào), 商品編號(hào)) ); 5、存儲(chǔ)過(guò)程、觸發(fā)器和視圖：根據(jù)需要給數(shù)據(jù)庫(kù)添加至少六個(gè)實(shí)用的存儲(chǔ)過(guò)程、觸發(fā)器和視圖，并說(shuō)明它們各自的功能。（需要給出語(yǔ)句執(zhí)行的結(jié)果示意圖） (1)創(chuàng)建視圖，查找商品名為蘋(píng)果的商品 create view find_goods as select 商品編號(hào),商品名稱(chēng),價(jià)格 from 商品 where 商品名稱(chēng) like 蘋(píng)果% （2）創(chuàng)建視圖，查找價(jià)格在3000-6000的商品 create view price as select 商品編號(hào),商品名稱(chēng) from 商品 where 價(jià)格>3000 and 價(jià)格<6000 (3)創(chuàng)建觸發(fā)器，提醒店鋪中庫(kù)存量小于10的商品 create trigger trigger_alarm on 銷(xiāo)售 after update as if(select 庫(kù)存量 from 銷(xiāo)售 where 庫(kù)存量<10)<10 print trigger out: select 商品編號(hào),庫(kù)存量 from 銷(xiāo)售 where 庫(kù)存量<10 return （4） 創(chuàng)建觸發(fā)器，當(dāng)刪除配送單中的數(shù)據(jù)時(shí)顯示出所刪信息 create trigger trigger_del on 配送單 after delete as print delete (5)創(chuàng)建存儲(chǔ)過(guò)程，根據(jù)商品編號(hào)，查詢(xún)?cè)撋唐返挠嗁?gòu)量 create proc sp_find_quantity @商品編號(hào) char(5), @sumq int output as select 商品編號(hào),sum(數(shù)量) from 訂購(gòu) group by 商品編號(hào) having 商品編號(hào)=@商品編號(hào) declare @sumq int exec sp_find_quantity @商品編號(hào)=s0001, @sumq=@sumq output print the sum quantity is:+ convert(varchar(5),@sumq （6）創(chuàng)建存儲(chǔ)過(guò)程，通過(guò)商品名稱(chēng)尋找商品信息 create proc sp_find_price @name char(10) as select * from 商品 where 商品名稱(chēng) like @name + % exec sp_find_price @name=戴爾 6、分析常見(jiàn)的業(yè)務(wù)流程，列舉出至少五種SQL SELECT語(yǔ)句。實(shí)現(xiàn)的語(yǔ)句要滿(mǎn)足如下要求： a) 在五種語(yǔ)句中的查詢(xún)能反映正常的業(yè)務(wù)需求； b) 分析中至少要分別出現(xiàn)一次ORDER BY、GROUP BY … HAVING子句； c) 分析中至少使用一次聚集函數(shù)； d) 分析中至少使用一次嵌套查詢(xún)； e) 分析中至少使用一次UNION或INTERSECT運(yùn)算； 給出每一種語(yǔ)句執(zhí)行的結(jié)果。 (1)計(jì)算并查找訂購(gòu)單中相同訂單訂購(gòu)超過(guò)5000的總額 select 訂單編號(hào),sum(金額)as 總額 from 訂購(gòu) group by 訂單編號(hào) having sum(金額)>5000 (2) 查找即購(gòu)買(mǎi)了商品編號(hào)為s0001又買(mǎi)了s0002的客戶(hù) select 姓名 from 客戶(hù) b,訂單 o,訂購(gòu) s where s.商品編號(hào)=s0001 and b.客戶(hù)編號(hào)=o.客戶(hù)編號(hào) and o.訂單編號(hào)=s.訂單編號(hào) intersect select 姓名 from 客戶(hù) b,訂單 o,訂購(gòu) s where s.商品編號(hào)=s0002 and b.客戶(hù)編號(hào)=o.客戶(hù)編號(hào) and o.訂單編號(hào)=s.訂單編號(hào) (3)按照顧客c0002消費(fèi)的總額大小順序排訂單編號(hào) select 訂單編號(hào),總額 from 訂單 where 客戶(hù)編號(hào)=c0001 order by 總額 desc (4)查詢(xún)訂單編號(hào)為00003的配送信息 select o.訂單編號(hào),配送人,聯(lián)系電話(huà) from 訂單 o,配送單 s where o.訂單編號(hào)=s.訂單編號(hào) and s.訂單編號(hào)=00003 (5)查找同樣買(mǎi)了商品編號(hào)為s0002的客戶(hù)信息 select 客戶(hù)編號(hào),姓名,電話(huà) from 客戶(hù) where 客戶(hù)編號(hào) in (select 客戶(hù)編號(hào) from 訂單，訂購(gòu) where 訂單.訂單編號(hào)=訂購(gòu).訂單編號(hào) and 訂購(gòu).商品編號(hào)=s0002) (6)查詢(xún)當(dāng)日銷(xiāo)售總額 select 訂購(gòu)時(shí)間,sum(總額) as 營(yíng)業(yè)額 from 訂單 group by 訂購(gòu)時(shí)間 7、 （選做）完成數(shù)據(jù)庫(kù)的設(shè)計(jì)之后，根據(jù)自己所熟悉的編程語(yǔ)言（C、C++、JAVA），實(shí)現(xiàn)一個(gè)簡(jiǎn)單的程序，能夠在程序中組裝SELECT語(yǔ)句，鏈接創(chuàng)建的數(shù)據(jù)庫(kù)，進(jìn)行查詢(xún)，并顯示查詢(xún)結(jié)果。若完成，請(qǐng)?zhí)峁┰敿?xì)的代碼清單（代碼作為附件，附在本報(bào)告的最后）。 用JSP頁(yè)面編寫(xiě) 實(shí)現(xiàn)如下： 8、實(shí)驗(yàn)小結(jié)。 在本次實(shí)驗(yàn)中一共建立了9個(gè)表，完成了6條Select語(yǔ)句，6條關(guān)于視圖、觸發(fā)器和存儲(chǔ)過(guò)程，在語(yǔ)句實(shí)現(xiàn)過(guò)程中，因?yàn)樯婕皟?nèi)容比較廣，差不多把數(shù)據(jù)庫(kù)語(yǔ)句全部復(fù)習(xí)了一遍才開(kāi)始做，所花的時(shí)間較長(zhǎng)，但也收益頗豐，對(duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)和實(shí)現(xiàn)也有了總體了解。最后在做選做時(shí)，選擇了通過(guò)網(wǎng)頁(yè)的方式實(shí)現(xiàn)，因?yàn)樵谧鰧?zhuān)業(yè)實(shí)訓(xùn)時(shí)也用了Java EE這項(xiàng)技術(shù)，所以實(shí)現(xiàn)起來(lái)頗為得心應(yīng)手。 附件 package com.ynu.myBusiness.db; import java.sql.Connection; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Statement; import com.devdaily.opensource.database.DDConnectionBroker; public class ConnectionBroker implements BuildConnection { private String driver = null; private String url = null; private String username = null; private String password = null; private int minConnections = 0; private int maxConnections = 0; private long timeout = 0; private long leaseTime = 0； private String logFile = null; private DDConnectionBroker broker = null; void setUp() { //驅(qū)動(dòng)包 driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //連接到數(shù)據(jù)庫(kù)business url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=business"; username = "sa"; password = "yym"; minConnections = 3; maxConnections = 6; timeout = 100; leaseTime = 60000; //日志文件存儲(chǔ)位置 logFile = "C:/D/DDConnectionBroker.log"; broker = null; } public Connection getConnection() throws SQLException { try { // construct（建立） the broker（中間人） broker = new DDConnectionBroker(driver, url, username, password, minConnections, maxConnections, timeout, leaseTime, logFile); } catch (SQLException se) { // could not get a broker; not much reason to go on System.out.println(se.getMessage()); System.out.println("Could not construct a broker, quitting."); } //建立broker成功 return broker.getConnection(); } public void freeConnection(Connection conn) throws SQLException { try { broker.freeConnection(conn); } catch (Exception e) { System.out.println("Threw an exception trying to free my Connection: "+ e.getMessage()); } } public int getNumberConnections() throws SQLException { if (broker != null) return broker.getNumberConnections(); else return -1; } public ConnectionBroker() { super(); setUp(); } } <%@ page language="java" contentType="text/html; charset=GB2312" pageEncoding="GB2312"%> <%@ page import="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.sql.ResultSet,java.sql.Statement" %>

數(shù)據(jù)庫(kù)實(shí)驗(yàn)大作業(yè)

搜索配送單中的數(shù)據(jù)

<% //從工廠中得到連接 ConnectionFactory factory=ConnectionFactory.getInstatnce(); Connection connection= factory.getConnection(); //執(zhí)行語(yǔ)句 PreparedStatement pstatement = null; //結(jié)果集 ResultSet rs = null; pstatement = connection.prepareStatement("select * from 配送單"); //執(zhí)行 rs = pstatement.executeQuery(); %> <% while (rs.next()) { out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); } %>

查詢(xún)號(hào)	訂單編號(hào)	配送人
" + rs.getString(1) + "	" + rs.getString(2) + "	" + rs.getString(3) + "

<%//顯示完畢，釋放資源 try { if (rs != null) rs.close(); if (pstatement != null) pstatement.close(); if (factory != null) factory.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } %>