【導(dǎo)讀】 一、引言...................................................................................................................2 背景與目的..........................................................................................................2 后臺(tái)數(shù)據(jù)庫(kù)為SQLServer2021簡(jiǎn)介.....................................................................2 二、系統(tǒng)需求分析.............................................................

　　

【正文】 oom_address varchar(20) NOT NULL 房間位置 表 47 為創(chuàng)建 學(xué)生信息 表 Student 創(chuàng)建 學(xué)生信息 表 Student 列名 數(shù)據(jù)類型 可否為空 說明 聲明 S_number varchar(6) NOT NULL 學(xué)號(hào) 主鍵 S_name varchar(10) NOT NULL 姓名 建立 聚簇索引 S_sex varchar(4) NULL 性別 Class varchar(20) NULL 班級(jí) S_system varchar(16) NULL 系別 S_dorm varchar(20) NULL 宿舍 S_tel varchar(20) NULL 聯(lián)系方式 表 48 為創(chuàng)建 卡信息 表 Card 創(chuàng)建 卡信息 表 Card 列名 數(shù)據(jù)類型 可否為空 說明 聲明 Card_number varchar(6) NOT NULL 卡號(hào) 主鍵 建立 唯一索引 Balance varchar(6) NOT NULL 余額 建立 聚簇索引 Card_date datetime（ 8） NULL 辦卡日期 Person_name varchar(10) NULL 持卡者姓名 Consume varchar(20) NULL 花費(fèi) 建立 聚簇索引 表 49 為創(chuàng)建 教師信息 表 Teacher 創(chuàng)建 教師 信息 表 Teacher 列名 數(shù)據(jù)類型 可否為空 說明 聲明 T_number varchar(6) NOT NULL 教師號(hào) 主鍵 T_name varchar(6) NOT NULL 姓名 建立聚簇索引 T_sex varchar(4) NULL 性別 T_system varchar(20) NULL 系別 Address varchar(20) NULL 家庭住址 T_tel varchar(50) NULL 聯(lián)系方式 表 為創(chuàng)建管理查詢表 Manage 創(chuàng)建管理查詢表 Manage 列名 數(shù)據(jù)類型 可否為空 說明 Manager_number varchar(6) NOT NULL 管理員編號(hào) Date datetime NOT NULL 日期 表 為創(chuàng)建屬于表 Attribute 創(chuàng)建屬于表 Attribute 列名 數(shù)據(jù)類型 可否為空 說明 S_number varchar(6) NOT NULL 學(xué)號(hào) Consumer_number varchar(6) NOT NULL 消費(fèi)序號(hào) 表 為創(chuàng)建組成表 Compose 創(chuàng)建組成表 Compose 列名 數(shù)據(jù)類型 可否為空 說明 T_number varchar(6) NOT NULL 教師號(hào) Consumer_number varchar(6) NOT NULL 消費(fèi)序號(hào) 表 為創(chuàng)建使用表 Use 創(chuàng)建使用表 Use 列名 數(shù)據(jù)類型 可否為空 說明 S_number varchar(8) NOT NULL 學(xué)號(hào) Card_number varchar(6) NOT NULL 卡號(hào) 表 Hold 創(chuàng)建持有表 Hold 列名 數(shù)據(jù)類型 可否為空 說明 Card_number varchar(6) NOT NULL 卡號(hào) T_number varchar(6) NOT NULL 教師號(hào) 將概念模型轉(zhuǎn)換為全局邏輯模型后，還應(yīng)該根據(jù)局部應(yīng)用需求，結(jié)合具體DBMS 的特點(diǎn)，設(shè)計(jì)用戶的外模式。 目前關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)一般都提供了視圖（ View）概念，可以利用這一功能設(shè)計(jì)更符合局部用戶需要的用戶外模式。 定義數(shù)據(jù)庫(kù)全局模式主要是從系統(tǒng)的時(shí)間效率、空間效率、易維護(hù)等角度出發(fā)。由于用戶外 模式與模式是相對(duì)獨(dú)立的，因此在定義用戶外模式時(shí)可以注重考慮用戶的習(xí)慣與方便。 包括： 1) 使用更符合用戶習(xí)慣的別名。 在合并各分 E— R 圖時(shí)，曾做了消除命名沖突的工 作，以使數(shù)據(jù)庫(kù)系統(tǒng)中同一關(guān)系和屬性具有唯一的名字。這在設(shè)計(jì)數(shù)據(jù)庫(kù)整體結(jié)構(gòu)時(shí)是非常必要的。用 View 機(jī)制可以在設(shè)計(jì)用戶 View 時(shí)重新定義某些屬性名，使其與用戶一致，以方便使用。 2) 可以對(duì)不同級(jí)別的用戶定義不同的 View，以保證系統(tǒng)的安全性。 3) 簡(jiǎn)化用戶對(duì)系統(tǒng)的使用。 如果某些局部應(yīng)用中經(jīng)常要使用某些很復(fù)雜的查詢，為了方便用戶，可以將這些復(fù)雜查詢定義為視圖，用戶每次只對(duì)定義好的視圖進(jìn)行查詢，大大簡(jiǎn)化了用戶的使用 ? 消費(fèi)者查詢信息所建立 的視圖 1) 消費(fèi)者查詢自己卡中余額 ? 學(xué)生查詢視圖 S_Balance ? 教師 查詢視圖 T_Balance 2) 消費(fèi)者查詢自己的訂餐信息 ? 管理員查詢信息所建立的視圖 1) 食堂買商品所需金錢數(shù)量 2) 食堂每個(gè)月發(fā)給職工的工資總和 3) 管理員查詢消費(fèi)者信息 查詢學(xué)生的信息 查詢教師的信息 五、物理結(jié)構(gòu)設(shè)計(jì) 數(shù)據(jù)庫(kù)在物理設(shè)備上的存儲(chǔ)結(jié)構(gòu)與存取方法稱為數(shù)據(jù)庫(kù)物理的物理結(jié)構(gòu)，它依賴于選定的數(shù)據(jù)庫(kù)管理系統(tǒng)。為一個(gè)給定的邏輯數(shù)據(jù)模型選取最適合應(yīng)用要 求的物理結(jié)構(gòu)的過程，就是數(shù)據(jù)庫(kù)的物理設(shè)計(jì)。 數(shù)據(jù)庫(kù)的物理設(shè)計(jì)通常分為兩步： 1) 確定數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)，在關(guān)系數(shù) 據(jù)庫(kù)中主要指存取方法和存儲(chǔ)結(jié)構(gòu)； 2) 對(duì)物理結(jié)構(gòu)進(jìn)行評(píng)價(jià)，評(píng)價(jià)的重點(diǎn)是時(shí)間和空間效率。 如果評(píng)價(jià)結(jié)果滿足原設(shè)計(jì)要求，則可進(jìn)入到物理實(shí)施階段，否則，就需要重新設(shè)計(jì)或修改物理結(jié)構(gòu)，有時(shí)甚至要返回邏輯設(shè)計(jì)階段修改數(shù)據(jù)模型。 建立索引是加快查詢速度的有效手段。用戶可以根據(jù)應(yīng)用環(huán)境的需要，在基本表上建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查找速度。 一般來說，建立與刪除索引由數(shù)據(jù)庫(kù)管理員 DBA 或表的屬主（ owner），即建立表的人，負(fù)責(zé)完成。系統(tǒng)在存取數(shù)據(jù)時(shí)會(huì)自動(dòng)選擇合適的索引作為存取路徑，用戶不必也 不能顯示地選擇索引。 索引的選擇方法，一般來說： 1. 如果一個(gè) (或一組 )屬性經(jīng)常在查詢條件中出現(xiàn) ,則考慮在這個(gè) (或這組 )屬性上建立索引 (或組合索引 )； 2. 如果一個(gè)屬性經(jīng)常作為最大值或最小值等聚集函數(shù)的參數(shù)，則考慮在這個(gè)屬性上建立索引； 由上所述建立以下索引 ? 為庫(kù)存信息表 Stock 建立索引 1) 按 Trade_name， stock_number，建立聚簇索引， 2) 按 entertime 升序，建立唯一索引 ? 為消費(fèi)情況表 ConsumeSituation 建立索引 1) 按 Total 建立聚簇索引 2) 按 Date 升序，建立唯一索引 ? 為訂餐信息表 book_eat 建立索引 1) 按 Consumer_name， Booktime 建立聚簇索引 2) 按 Consumer_number 升序，建立唯一索引 ? 為卡信息表 Card 建立索引 1) 按 Balance ,Consume 建立聚簇索引 2) 按 Card_number 升序，建立唯一索引 ? 為 學(xué)生信息表 Student 建立索引 1) 按 S_name 建立聚簇索引 ? 為 學(xué)生信息表 Teacher 建立索引 1) 按 T_name 建立聚簇索引 觸發(fā)器 觸發(fā)器 （ Trigger）是用戶定義在表上的一類由事件驅(qū)動(dòng)的特殊過程。一旦定義，任何用戶對(duì)表的增 、刪、改操作均由服務(wù)器自動(dòng)激活相應(yīng)得觸發(fā)器，在DBMS 核心層進(jìn)行集中的完整性控制。 觸發(fā)器 類似于約束，但是比約束更加靈活， 可以實(shí)施比 FOREIGN KEY 約束， CHECK 約束更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力。觸發(fā)器的執(zhí)行，是由觸發(fā)事件激活的，并由數(shù)據(jù)庫(kù)服務(wù)器自動(dòng)執(zhí)行的。一個(gè)數(shù)據(jù)表上可能定義了多個(gè)觸發(fā)器，比如 BEFORE觸發(fā)器 ，多個(gè) AFTER 觸發(fā)器 等。同一個(gè)表上的多個(gè) 觸發(fā)器 激活時(shí)遵循如下執(zhí)行順序： （ 1） 執(zhí)行該表上的 BEFORE 觸發(fā)器 ； （ 2） 激活觸發(fā)器的 SQL 語(yǔ)句； （ 3） 執(zhí)行該表上的 AFTER 觸發(fā)器 。 對(duì)于同一個(gè)表上的多個(gè) BEFORE（ AFTER） 觸發(fā)器 ，遵循“誰先創(chuàng)建誰先執(zhí)行”的原則，即按照 觸發(fā)器 創(chuàng)建的先后順序執(zhí)行。有些 RDBMS 是按照觸發(fā)器 的名稱的字母排序順序執(zhí)行 觸發(fā)器 。 ? 定義 AFTER 行級(jí)觸發(fā)器，當(dāng)管理員表 Manager 的工資發(fā)生變化后就自動(dòng)在工資變化表 Inc_log 中增加一條相應(yīng)的記錄 首先建立工資變化表 Inc_log CREATE TABLE Inc_log (Manager_number varchar(6) REFERENCES Manager(Manager_number), Ine int, Manager_name varchar(6), Date TIMESTAMP )。 接著建立觸發(fā)器 Insert_Inc CREATE TRIGGER Insert_Inc AFTER INSERT ON Manager FOR EACH ROW AS BEGIN INSERT INTO Inc_log VALUES( NEW. Manager_number, CURRENT_USER,CURRENT_TIMESTAMP)。 END。 CREATE TRIGGER Update_Inc AFTER UPDATE ON Manager FOR EACH ROW AS BEGIN IF()THEN INSERT INTO Inc_log VALUES( NEW. Manager_number,NEW. Ine, CURRENT_USER,CURRENT_TIMESTAMP)。 END IF。 END。 六、 數(shù)據(jù)庫(kù)結(jié)構(gòu) 的 實(shí)現(xiàn) 經(jīng)過 前面的系統(tǒng)需求分析和概念結(jié)構(gòu)設(shè)計(jì)以后，得到數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)，數(shù)據(jù)庫(kù)物理結(jié)構(gòu)。現(xiàn) 在就可以在 SQL Server 2021 數(shù)據(jù)庫(kù)系統(tǒng)中，實(shí)現(xiàn)上面設(shè)計(jì)的數(shù)據(jù)庫(kù)結(jié)構(gòu)。這一步是利用 SQL Server 2021 數(shù)據(jù)庫(kù)系統(tǒng)中的 SQL 查詢分析器實(shí)現(xiàn)的。 關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)支持三級(jí)模式結(jié)構(gòu)，其模式、外模式和內(nèi)模式中的基本 對(duì)象有表、視圖和索引。因此 SQL 的數(shù)據(jù)定義功能包括模式定義、表定義、視圖和索引的定義，如下表所示。 表 SQL 的數(shù)據(jù)定義語(yǔ)句 操作對(duì)象 操作方式 創(chuàng)建 刪除 修改 表 CREATE TABLE DROP TABLE 視圖 CREATE VIEW DROP VIEW ALTER TABLE 索引 CREATE INDEX DROP INDEX 創(chuàng)建表格 SQL 語(yǔ)句 下面給出創(chuàng)建這些表格的 SQL 語(yǔ)句： 1) 為創(chuàng)建 庫(kù)存信息表 Stock CREATE TABLE Stock (Trade_no varchar(20) PRIMARY KEY, Trade_name varchar(20), Price int, Enter_number int, stock_number int, entertime datetime, outtime datetime, Manager_number varchar(6) NOT NULL, FOREIGN KEY (Manager_number) REFERENCES Manager (Manager_number), )。 2) 為創(chuàng)建 管理員信息 表 Manager CREATE TABLE Manager (Manager_number varchar(6) PRIMARY KEY, Manager_name varchar(6) NOT NULL, Manager_sex varchar(4), Manager_address varchar(50), Manager_tel varchar(20), Ine int )。 3) 為創(chuàng)建 消費(fèi)情況表 ConsumeSituation CREATE TABLE ConsumeSituation (Date datetime PRIMARY KEY, Total bigint NOT NULL, Floor_one varchar(6), Floor_two varchar(6), Floor_thr