moea_joint_essay
113年
[資訊] 資訊管理、程式設計
第 三 題
📖 題組：
一、某書店資料庫中有數個資料表，其 DDL 如下，請回答下列問題：（3 題，共 16 分） CREATE TABLE Product ( pNo CHAR(6) NOT NULL, pName VARCHAR(30), unitPrice int, PRIMARY KEY (pNo) ); CREATE INDEX PriceIndex ON Product(unitPrice) CLUSTER; CREATE TABLE Member ( mId CHAR(8) NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, PRIMARY KEY (mId) ); CREATE TABLE Buy ( mId CHAR(8) NOT NULL, pNo CHAR(6) NOT NULL, BuyTime DATETIME NOT NULL, amount INT, PRIMARY KEY (mId, pNo, BuyTime), FOREIGN KEY (mId) REFERENCES Member(mId), FOREIGN KEY (pNo) REFERENCES Product(pNo) ); CREATE TABLE Author ( pNo CHAR(6) NOT NULL, name VARCHAR(50), PRIMARY KEY (pNo, name), FOREIGN KEY (pNo) REFERENCES Product(pNo) );
一、某書店資料庫中有數個資料表，其 DDL 如下，請回答下列問題：（3 題，共 16 分） CREATE TABLE Product ( pNo CHAR(6) NOT NULL, pName VARCHAR(30), unitPrice int, PRIMARY KEY (pNo) ); CREATE INDEX PriceIndex ON Product(unitPrice) CLUSTER; CREATE TABLE Member ( mId CHAR(8) NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, PRIMARY KEY (mId) ); CREATE TABLE Buy ( mId CHAR(8) NOT NULL, pNo CHAR(6) NOT NULL, BuyTime DATETIME NOT NULL, amount INT, PRIMARY KEY (mId, pNo, BuyTime), FOREIGN KEY (mId) REFERENCES Member(mId), FOREIGN KEY (pNo) REFERENCES Product(pNo) ); CREATE TABLE Author ( pNo CHAR(6) NOT NULL, name VARCHAR(50), PRIMARY KEY (pNo, name), FOREIGN KEY (pNo) REFERENCES Product(pNo) );
📝 此題為申論題，共 3 小題
小題 (三)
本 DBMS是由高速電腦建置的非分散式資料庫系統，執行 SQL語法查詢時，需先將資料從硬碟載入記憶體才能運算，為了減少硬碟存取次數，每次載入量為一個硬碟頁。由於存取硬碟頁的耗用時間，是本 DBMS進行 SQL語法查詢的最主要成本，故實際執行查詢前，估算硬碟頁存取次數將是效能調校關鍵。已知 Product 資料表裡共有100,000筆，每一硬碟頁可存放 200筆資料，以 pNo及 unitPrice為索引所建立的 B+ tree各有 4 層(含葉節點)，試問以下語法，將分別平均存取幾個硬碟頁? 請列示計算過程。
(1)執行「Select * from Product where pNo='xxxxxx'」（5 分）
(2)執行「Select * from Product where unitPrice>z」(z 為任意整數)（5 分）
(1)執行「Select * from Product where pNo='xxxxxx'」（5 分）
(2)執行「Select * from Product where unitPrice>z」(z 為任意整數)（5 分）
思路引導 VIP
(1) 對於未叢集的主鍵索引找尋特定筆數，需遍歷樹的高度加一次資料頁讀取。(2) 對於叢集索引的範圍查詢，找出起始節點後即可循序讀取連續的資料頁；平均條件會有一半資料符合，計算資料頁數量後相加即為存取次數。
小題 (一)
試以 SQL 語法，列出各作者之銷售總金額。（3 分）
思路引導 VIP
利用 JOIN 將 Author, Buy, Product 三個表關聯起來，透過 GROUP BY 作者姓名，並以 SUM 計算 (amount * unitPrice) 即可得出各作者的銷售總金額。
小題 (二)
當某讀者把某作者的所有書都買過，我們稱該讀者為「鐵粉」。試以 SQL 語法，列出達文西(Author.name='達文西')的所有「鐵粉」姓名。（3 分）
思路引導 VIP
這是一個典型的除法查詢 (Division) 應用。可以使用雙重 NOT EXISTS 語法來達成：「找出不存在有達文西的任何一本書，是該位讀者所沒有買過的」。