資訊處理 110 年高等資料庫設計考古題

假設你想替臺灣COVID-19 的公費疫苗接種設計一個簡易資料庫，有以 下幾種實體（Entity Type）： 疫苗（Vaccine）：屬性包括名稱（name）、臨床試驗階段（phase）、 製造國（madeBy），剩餘數量（amount），其中名稱和製造國合起 來是唯一。 施打輪（Round）：屬性包括輪數（num）、預約開始日期（rBdate）、 預約結束日期（rEdate）、施打劑量（dose），其中輪數是唯一。 登記人（Person）：屬性包括識別號（pId）、姓名（name）、出生年 月日（bDate）、性別（gender），其中識別號是唯一。 接種站（Station）：屬性包括識別號（sId）、名稱（name）、容納人 數（capacity），其中識別號是唯一。 其中登記人在那一個施打輪的那一個接種站的接種日期都需記載，此外 每一施打輪所用的疫苗、有那些接種站也需記載。 請根據以上說明畫出實體關係圖（Entity Relationship Diagram），題 目沒有說明處可自行假設，但要說明清楚。（20 分） 請轉成關聯綱目（Relational schema），每一關聯的主鍵（Primary key） 和外鍵（Foreign key）要標示清楚。（10 分）

假設有一個記載討論版文章的資料表Article，包括以下欄位： dateTime //評論記載時間 author //發表人 school //學校 forum //討論版 title //標題 owner //版主 content //內容 reply //回答 相依（Dependency）如下： {dateTime, author}{forum, title, content} {forum}{owner} {author}{school} {dateTime, author}{reply} 其中為函數相依（Functional Dependency），為多值相依 （Multi-valued Dependency）。 請推導出Article 資料表的關聯鍵（key）。（7 分） 將Article 關聯綱目（Relational schema）分解成滿足第三正規式（Third Normal Form）的綱目，請列出分解後每一關聯綱目之主鍵（Primary key）和外鍵（Foreign key）並說明之。（10 分） 用SQL CREATE TABLE 語法表達出上一小題的資料庫綱目 （Database schema），屬性的型態請自訂。（8 分）

考慮以下的商品交易資料庫綱目（Database schema）： User(uId, name, rdate) //使用者編號，姓名，加入日期 Product(pId, seller, name, category, star) //商品編號，賣家名稱，商品名稱，類別，星等 Transaction(uId, pId, seller, date, amount, star) //使用者編號，商品編號，賣家名稱，日期，數量，星等 各資料表的主鍵（Primary key）以底線表示。此外，並有以下的外鍵 （Foreign key）： Transaction(uId)參考到User 的主鍵 Transaction(pId, seller)參考到Product 的主鍵 將每一個商品和其賣家的星等star 值改成Transaction 裡對於該商品和 其賣家的星等（star）之平均值。（7 分） 列出藍芽耳機（Product.category = ‘Bluetooth earphone’）的所有賣家名 稱和商品名稱。（4 分） 對於每一個總購買量超過（含）1000 的藍芽耳機（Product.category = ‘Bluetooth earphone’）商品和其賣家，列出賣家名稱、商品名稱和星等。 （8 分） 對於每一位賣家，列出其賣家名稱和2021 年有賣出的商品名稱。（6 分）

考慮以下的商品交易資料庫綱目（Database schema）： User(uId, name, rdate) //使用者編號，姓名，加入日期 Product(pId, seller, name, category, star) //商品編號，賣家名稱，名稱，類別，星等 Transaction(uId, pId, seller, date, amount, star) //使用者編號，商品編號，賣家名稱，日期，數量，星等 各資料表的主鍵（Primary key）以底線表示。此外，並有以下的外鍵 （Foreign key）： Transaction(uId)參考到User 的主鍵 Transaction(pId, seller)參考到Product 的主鍵 若我們有以下的SQL 敘述： SELECT uId, name FROM User AS U WHERE YEAR(rdate)>= ‘2020’ AND uId IN (SELECT uId FROM Transaction AS T WHERE T.uId = U.uId AND star = 5 AND EXISTS (SELECT * FROM Product WHERE pId = T.pId AND category = ‘Bluetooth earphone’)); 請說明以上查詢句的意義。（5 分） 以上查詢句的執行效率可能不佳，請指出造成其效率不佳的語法，並 改寫成一個效率較佳的敘述。（10 分） 針對你所改寫的查詢句，若想進一步提升其執行效率，請問可以建置 那些索引（Index）？請寫出建置這些索引的SQL 敘述並說明原因。 （5 分）