數(shù)據(jù)庫分析與設計

數(shù)據(jù)庫分析與設計王有天湖北經(jīng)濟學院2023/3/42數(shù)據(jù)電子商務系統(tǒng)軟硬件環(huán)境分布式軟件系統(tǒng)(B/S;C/S.P2P)TCP/IPHTTP(s);SMTP/POP3;FTP;...商務系統(tǒng)B/S;C/S現(xiàn)金流物流信息流…業(yè)務處理指令，往往體現(xiàn)為SQL的批處理。數(shù)據(jù)庫管理程序數(shù)據(jù)數(shù)據(jù)處理指令：比如SQL。用戶管理程序權限管理程序用戶用戶用戶用戶用戶用戶用戶用戶用戶用戶業(yè)務流程1業(yè)務流程2業(yè)務流程3業(yè)務流程4用戶控制業(yè)務經(jīng)營數(shù)據(jù)層界面……業(yè)務支持市場業(yè)務控制控制層表現(xiàn)層郵箱域名2023/3/43數(shù)據(jù)層數(shù)據(jù)是企業(yè)信息系統(tǒng)的核心。價值實現(xiàn)層業(yè)務經(jīng)營層邏輯控制層頻道

經(jīng)營用戶

使用內(nèi)容輸入

頻道數(shù)據(jù)資源層數(shù)據(jù)庫數(shù)據(jù)發(fā)布程序

開發(fā)人員維護內(nèi)容輸入界面

開發(fā)人員維護用戶需求市場2023/3/44對數(shù)據(jù)的管理系統(tǒng)數(shù)據(jù)庫DBMSDataBaseManagementSystem2023/3/45為什么要有DBMS數(shù)據(jù)完整性事務的處理如：轉(zhuǎn)賬事務相對獨立的系統(tǒng)模塊數(shù)據(jù)與程序分開。軟件的相對獨立研發(fā)集成數(shù)據(jù)處理的機制。故障恢復Log權限2023/3/46DBMS特點 封裝不能通過DBMS以外的渠道訪問數(shù)據(jù)庫。否則破壞完整性、一致性。支持事務處理能夠故障恢復控制訪問并發(fā)操作2023/3/47DBMS的發(fā)展圖書館/資料庫如果管理不善，就不能成為系統(tǒng)。人文件系統(tǒng)沒有封裝；沒有事務處理；沒有故障恢復。數(shù)據(jù)庫2023/3/48DBMS的分類網(wǎng)絡型/層次型關系型擴展面向?qū)ο竽：龜?shù)據(jù)多媒體數(shù)據(jù)庫網(wǎng)絡文件管理2023/3/49關系型數(shù)據(jù)庫數(shù)據(jù)存儲在表中賬號名稱密碼學生1a112b223c31學號姓名性別1張三男2李四女3課程名稱1電商2信息3…2023/3/410EntityRelation學生1賬號1張三賬號姓名2023/3/411關系種類：1:nn:m學生和課程學號姓名性別1張三男2李四女3課程名稱1電商2信息3…學號課程1112322023/3/412關系型數(shù)據(jù)庫的優(yōu)點記錄等長檢索快。例如：總記錄個數(shù)。某個字段值2023/3/413基本詞匯表字段記錄字段關鍵字段記錄記錄的字段值關系定義某個表中某字段的值取自另外一個表2023/3/414記錄的增加刪除更新記錄沒有順序。查詢語言可以按字段排序。刪除記錄取決于DBMS一般作標記，重用空間。可以壓縮表。但DBMS一般不自動進行。增加記錄刪除記錄或最后更新記錄定位到記錄字段，覆蓋。2023/3/415約束和觸發(fā)器字段取值范圍某個記錄必須符合某個范疇。更復雜的約束需要觸發(fā)器。2023/3/416數(shù)據(jù)庫中表和類的關系表固定字段，表示某一類對象。每個記錄相當于類的實例。每個字段值相當于實例的屬性或者與其他實例的關系。非同類的數(shù)據(jù)存儲在同一個表，并不適合。2023/3/417記錄和語句的關系每個記錄相當于符合特定語法的語句。表示一定的語義。Null表示不知道Null的運算2023/3/418關系型數(shù)據(jù)庫的范式第一范式：字段的原子性。要么視為原子。要么應該再分。否則不便查詢。

字段1

字段2

字段3字段4字段3.1字段3.2

2023/3/419第二范式非關鍵字段完全函數(shù)依賴于關鍵字段。函數(shù)依賴y=f(x);不能說y=f(x)附近。2023/3/420不符合第二范式造成問題假定選課關系表為SelectCourse(學號,姓名,年齡,課程名稱,成績,學分)關鍵字為組合關鍵字(學號,課程名稱)，因為存在如下決定關系：

(學號,課程名稱)→(姓名,年齡,成績,學分)這個數(shù)據(jù)庫表不滿足第二范式，因為存在如下決定關系：

(課程名稱)→(學分)(學號)→(姓名,年齡)

即存在組合關鍵字中的字段決定非關鍵字的情況。

2023/3/421不符合第二范式造成問題數(shù)據(jù)冗余：

同一門課程由n個學生選修，“學分”就重復n-1次；同一個學生選修了m門課程，姓名和年齡就重復了m-1次。更新異常：

若調(diào)整了某門課程的學分，數(shù)據(jù)表中所有行的“學分”值都要更新，否則會出現(xiàn)同一門課程學分不同的情況。

插入異常：

假設要開設一門新的課程，暫時還沒有人選修。這樣，由于還沒有“學號”關鍵字，課程名稱和學分也無法記錄入數(shù)據(jù)庫。

刪除異常：

假設一批學生撤銷某些課程的選修，這些選修記錄就應該從數(shù)據(jù)庫表中刪除。但是，與此同時，課程名稱和學分信息也被刪除了。很顯然，這也會導致插入異常。2023/3/422不符合第二范式造成問題冗余重復的數(shù)據(jù)更新麻煩更新不一致造成沖突冗余的根源在于非關鍵字段不是由關鍵字段完全決定。部分就能決定，結(jié)果另外一部分關鍵字改變時，非關鍵字段重復。2023/3/423關系型數(shù)據(jù)庫的范式第三范式非關鍵字段之間不存在傳遞依賴。否則存在冗余。冗余帶來同樣的問題。2023/3/424適當?shù)娜哂噙m當?shù)娜哂嘣斐芍貜偷赡芴岣咝阅堋Ｈ鏸ndex是故意的冗余。照顧語義。2023/3/425數(shù)據(jù)庫設計的多樣性對同一件事情描述方法可以不同。因此數(shù)據(jù)庫的結(jié)構有所不同。但這些數(shù)據(jù)庫中的數(shù)據(jù)應該是等價的?？梢韵嗷ネ茖?。2023/3/426關系型數(shù)據(jù)庫的數(shù)據(jù)操作DBMS完成。RDBMS支持查詢語言SQLSQL=StructuredQueryLanguage是一個語言規(guī)范。DBMS一般支持該規(guī)范，并有自己的擴展。查詢和操作：增刪改查2023/3/427SQL的組成(分類)

DATABASE TABLE CREATE VIEW INDEX SQLDDL：

ALTER DROP SQLQUERY：SELECT…FROM… INSERT… SQL SQLDML： UPDATE… DELETE… GRANT SQLDCL： REVOKE

2023/3/428增加記錄InsertTable1(Field1,…)Values(…,…)InsertTable1(Field1,…)Select…2023/3/429更新UpdateTable1SetField1=…,Field2=…Where…2023/3/430刪除DeleteTable1Where…2023/3/431Select,Where篩選記錄SelectField1,Field4fromTable1Where(Field1=‘張三’andField2=2)orField1=‘李四’篩選條件LikeBetweenAnd>,>=,<,<=,=2023/3/432Join,On豎著連接表，按一定條件Table1joinTable2onTable1.Field1=Table2.Field1LeftJoinRightJoin對于連接的表可以進行查詢（如選擇）2023/3/433Into查詢結(jié)果存儲在表中SelectTable1.Field1,…intoNewTable1From…2023/3/434Union

豎著合并表

SelectField1,… FromTable1 Where…UnionSelectField1,… FromTable2 Where2023/3/435交叉表查詢TRANSFORMSum(銷售收入)AS銷售收入之總計SELECT部門,人員FROM銷售GROUPBY部門,人員PIVOT季度;部門人員1234銷售1部張三3024255635781962133299333銷售2部李四

3780

銷售2部王五

32899922141112352341部門人員季度銷售收入銷售1部張三1300000銷售1部張三2560012銷售1部張三362000銷售1部張三43299100銷售2部李四23780銷售2部王五2328999銷售2部王五32214111銷售2部王五42349999銷售1部張三12425銷售1部張三23566銷售1部張三3134213銷售1部張三4233銷售2部王五423422023/3/436as字段別名和表別名表別名允許給表另起一個名字，相當于將表復制一遍參加查詢。視為單獨的表，臨時。2023/3/437Groupby進行分組集合運算2023/3/438Having，WhereWhere決定哪些元組被選擇參加運算，作用于關系中的元組Having決定哪些分組符合要求，作用于分組2023/3/439OrderBy排序DescAsc2023/3/440In,ExistsSELECT FROM… WHERES# IN (SELECTS# FROMStudent WHERECourse=‘C1’)；2023/3/441In,ExistsSELECTSN FROMS WHEREEXISTS (SELECT* FROMSC WHERES.S#=SC.S# ANDC#=‘C1’)；2023/3/442查詢的圖形化-QBE2023/3/443查詢的圖形化2023/3/444權限管理語言的圖形化2023/3/445電子商務系統(tǒng)中對SQL的調(diào)用DBMS可以批處理SQL語句。電子商務系統(tǒng)調(diào)用SQL效率更高安全，保持數(shù)據(jù)完整性。支持事務。2023/3/446事務處理事務一組數(shù)據(jù)操作，要么成功要么失敗。SQL BEGINTRANSACTION … COMMIT

ROLLBACK2023/3/447事務特點Atomicity事務是數(shù)據(jù)庫的邏輯工作單位，事務中包括的諸操作要么都做，要么都不做。Consistency事務執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)Isolation一個事務的執(zhí)行不能被其他事務干擾。并發(fā)執(zhí)行的各個事務之間不能互相干擾。Durability一個事務一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應該是永久性的。2023/3/448故障恢復出現(xiàn)異常。造成事務中斷。比如斷電。此時數(shù)據(jù)可能不一致。單獨的數(shù)據(jù)庫文件難于解決該問題。Log寫入數(shù)據(jù)庫文件前，先寫日志。在日志中保留恢復信息。在數(shù)據(jù)庫文件寫成功后，在日志中標記。檢查日志。沒有成功的寫入應該嘗試再寫或者放棄。2023/3/449日志格式每條日志記錄的內(nèi)容事務標識操作類型（插入、刪除或修改）操作對象（記錄ID、BlockNO.）更新前數(shù)據(jù)的舊值（對插入操作而言，此項為空值）更新后數(shù)據(jù)的新值（對刪除操作而言,此項為空值）2023/3/450故障恢復系統(tǒng)重啟時，如果日志完整，向前繼續(xù)執(zhí)行事務如果日志不完整，撤銷該事務。2023/3/451數(shù)據(jù)庫軟件一般是關系型的文件型Access沒有日志。服務OracleMsSqlServerMySQL2023/3/452數(shù)據(jù)庫軟件這些軟件都支持SQL，并擴展。SQLServer下的T-SQL2023/3/453SQLServer簡介原來為另外一家公司產(chǎn)品，后被微軟收購。最新版本2005。支持.net?？梢远x類型可以定義函數(shù)、存儲過程、觸發(fā)器等。支持xml2023/3/454SQLServer安裝安裝時會檢查：至少512M，建議1G內(nèi)存。安裝在WindowsServer2003需要Servicepack較新版本最好安裝有.net2023/3/455SQLServer數(shù)據(jù)庫文件管理多個數(shù)據(jù)庫數(shù)據(jù)庫文件一個或多個文件組每個文件組有一個或多個文件。分布在多個磁盤上可以并行讀取。不放在壓縮磁盤上。日志2023/3/456連接可以用客戶端連接。比如:剛才的ManagementStudioAccess理解為一種服務/客戶端使用關系。程序連接比如：A中建立SqlConnection對象，指定數(shù)據(jù)庫位置(地址)、用戶名、密碼、數(shù)據(jù)庫2023/3/457管理2023/3/458MgmtStudio:T-SQL2023/3/459Access+SQLServerAccess文件SQLServer服務Access程序SQLServerMgt.Studio2023/3/460Access+SQLServer2023/3/461新建2023/3/462瀏覽2023/3/4632023/3/4642023/3/4652023/3/4662023/3/4672023/3/4682023/3/4692023/3/4702023/3/4712023/3/4722023/3/4732023/3/474Access+SQLServer實際例子2023/3/4752023/3/476T-SQL除了標準SQL外，支持更多功能?？梢杂凶兞?、邏輯控制，因此可以編程。SQLServer2005支持在.net環(huán)境中編程，如類型、觸發(fā)器等。2023/3/477T-SQL2023/3/478數(shù)據(jù)庫空間一般單獨提供。目前市場上提供的數(shù)據(jù)庫空間有：MySQLMSSqlServerOracle較少。價格差不多。2023/3/479數(shù)據(jù)庫的維護備份復制/分布數(shù)據(jù)完整性權限控制安全性2023