2024年軟考中級(jí)數(shù)據(jù)庫(kù)系統(tǒng)工程師考試真題及答案一、上午基礎(chǔ)知識(shí)（共75題，每題1分，總分75分）1.下列關(guān)于數(shù)據(jù)庫(kù)系統(tǒng)組成的描述中，正確的是()A.數(shù)據(jù)庫(kù)系統(tǒng)僅包含數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)管理系統(tǒng)

B.數(shù)據(jù)庫(kù)管理員（DBA）是數(shù)據(jù)庫(kù)系統(tǒng)的組成部分

C.應(yīng)用程序不屬于數(shù)據(jù)庫(kù)系統(tǒng)的組成

D.硬件平臺(tái)是數(shù)據(jù)庫(kù)系統(tǒng)的邏輯組成而非物理組成答案：B解析：數(shù)據(jù)庫(kù)系統(tǒng)（DBS）由數(shù)據(jù)庫(kù)（DB）、數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）、應(yīng)用程序、數(shù)據(jù)庫(kù)管理員（DBA）和硬件平臺(tái)共同組成。A選項(xiàng)遺漏了應(yīng)用程序、DBA和硬件；C選項(xiàng)錯(cuò)誤，應(yīng)用程序是數(shù)據(jù)庫(kù)系統(tǒng)的應(yīng)用層組件；D選項(xiàng)錯(cuò)誤，硬件平臺(tái)是物理組成部分。2.關(guān)系代數(shù)中，若要查詢“計(jì)算機(jī)系（CS）中年齡大于20歲的學(xué)生”，需依次使用的操作是()A.選擇→投影→笛卡爾積

B.投影→選擇→自然連接

C.選擇→投影

D.自然連接→選擇→投影答案：C解析：首先通過(guò)選擇（σ）操作篩選出“系別=CS且年齡>20”的元組，再通過(guò)投影（π）操作提取學(xué)生基本信息（如學(xué)號(hào)、姓名等）。笛卡爾積和自然連接用于多表關(guān)聯(lián)，本題僅涉及單表查詢，故排除A、B、D。3.以下SQL約束中，用于保證實(shí)體完整性的是()A.CHECK(age>18)

B.UNIQUE(email)

C.PRIMARYKEY(sno)

D.FOREIGNKEY(cno)REFERENCEScourse(cno)答案：C解析：實(shí)體完整性通過(guò)主鍵（PRIMARYKEY）約束實(shí)現(xiàn)，確保表中每行唯一標(biāo)識(shí)；A是域完整性（檢查約束）；B是唯一性約束（保證列值不重復(fù)）；D是參照完整性（外鍵約束）。4.事務(wù)的ACID特性中，“即使系統(tǒng)崩潰，已提交事務(wù)的修改也不會(huì)丟失”體現(xiàn)的是()A.原子性（Atomicity）

B.一致性（Consistency）

C.隔離性（Isolation）

D.持久性（Durability）答案：D解析：持久性（Durability）確保事務(wù)提交后，其對(duì)數(shù)據(jù)庫(kù)的修改永久保存，即使系統(tǒng)故障也不會(huì)丟失。原子性保證事務(wù)的“全做或全不做”；一致性保證數(shù)據(jù)狀態(tài)合法；隔離性防止事務(wù)間干擾。5.關(guān)系模式R(U,F)中，U={A,B,C,D},F={AB→C,C→D,D→A}，則R的候選鍵是()A.AB

B.BC

C.CD

D.BD答案：A解析：候選鍵是能唯一確定所有屬性的最小屬性集。計(jì)算AB的閉包：AB+=AB（初始）→AB→C→ABC→C→D→ABCD，包含所有屬性，且無(wú)冗余，故AB是候選鍵。6.關(guān)于B+樹(shù)索引與B樹(shù)索引的對(duì)比，正確的是()A.B+樹(shù)所有查詢都需訪問(wèn)葉節(jié)點(diǎn)，B樹(shù)可能在非葉節(jié)點(diǎn)結(jié)束

B.B+樹(shù)葉節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，僅存儲(chǔ)索引鍵

C.B樹(shù)的葉節(jié)點(diǎn)之間無(wú)指針連接，B+樹(shù)有

D.B+樹(shù)適合范圍查詢，B樹(shù)不適合答案：A解析：B+樹(shù)的非葉節(jié)點(diǎn)僅存儲(chǔ)索引鍵，葉節(jié)點(diǎn)存儲(chǔ)完整數(shù)據(jù)且通過(guò)指針連接，所有查詢最終都要訪問(wèn)葉節(jié)點(diǎn)；B樹(shù)的非葉節(jié)點(diǎn)可能存儲(chǔ)數(shù)據(jù)，查詢可能在任意節(jié)點(diǎn)結(jié)束。B選項(xiàng)錯(cuò)誤，B+樹(shù)葉節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)；C選項(xiàng)錯(cuò)誤，B+樹(shù)葉節(jié)點(diǎn)有指針，B樹(shù)沒(méi)有；D選項(xiàng)錯(cuò)誤，B樹(shù)也可用于范圍查詢，但B+樹(shù)更高效。7.以下NoSQL數(shù)據(jù)庫(kù)中，屬于列族存儲(chǔ)的是()A.Redis

B.Cassandra

C.MongoDB

D.Neo4j答案：B解析：Cassandra是典型的列族數(shù)據(jù)庫(kù)（ColumnFamily），適用于海量數(shù)據(jù)存儲(chǔ)；Redis是鍵值存儲(chǔ)；MongoDB是文檔存儲(chǔ)；Neo4j是圖數(shù)據(jù)庫(kù)。8.數(shù)據(jù)庫(kù)備份策略中，“每周日做全量備份，每日凌晨做增量備份”的恢復(fù)流程是()A.全量備份→所有增量備份按順序恢復(fù)

B.全量備份→最后一次增量備份

C.最近一次增量備份→全量備份

D.全量備份→差異備份→增量備份答案：A解析：增量備份記錄自上次備份（全量或增量）以來(lái)的修改，恢復(fù)時(shí)需先恢復(fù)全量備份，再按時(shí)間順序恢復(fù)所有增量備份，確保數(shù)據(jù)完整性。9.以下數(shù)據(jù)加密方式中，屬于存儲(chǔ)加密的是()A.TLS協(xié)議加密傳輸中的數(shù)據(jù)

B.對(duì)數(shù)據(jù)庫(kù)連接字符串進(jìn)行加密

C.對(duì)表中的敏感字段（如身份證號(hào)）進(jìn)行AES加密

D.對(duì)數(shù)據(jù)庫(kù)日志文件進(jìn)行哈希校驗(yàn)答案：C解析：存儲(chǔ)加密針對(duì)靜態(tài)數(shù)據(jù)，如字段級(jí)加密（C選項(xiàng)）、文件加密等；A是傳輸加密；B是連接參數(shù)保護(hù)；D是完整性校驗(yàn)。10.大數(shù)據(jù)處理框架Hadoop中，負(fù)責(zé)資源管理和任務(wù)調(diào)度的組件是()A.HDFS

B.MapReduce

C.YARN

D.HBase答案：C解析：YARN（YetAnotherResourceNegotiator）是Hadoop的資源管理系統(tǒng)，負(fù)責(zé)集群資源分配和任務(wù)調(diào)度；HDFS是分布式文件系統(tǒng)；MapReduce是計(jì)算框架；HBase是基于HDFS的列族數(shù)據(jù)庫(kù)。（注：因篇幅限制，此處展示前10題，實(shí)際考試上午卷含75題，后續(xù)題目可參考以下題型擴(kuò)展：關(guān)系模式規(guī)范化、SQL高級(jí)特性（窗口函數(shù)、CTE）、事務(wù)隔離級(jí)別、分布式數(shù)據(jù)庫(kù)一致性（CAP定理）、數(shù)據(jù)倉(cāng)庫(kù)與OLAP、數(shù)據(jù)隱私保護(hù)（GDPR/《個(gè)人信息保護(hù)法》）、數(shù)據(jù)庫(kù)性能優(yōu)化（執(zhí)行計(jì)劃分析、索引策略）等。）二、下午應(yīng)用技術(shù)（共6題，總分75分）1.數(shù)據(jù)庫(kù)設(shè)計(jì)題（15分）某高校需設(shè)計(jì)“學(xué)生選課管理系統(tǒng)”，需求如下：

-學(xué)生信息：學(xué)號(hào)（唯一）、姓名、性別、入學(xué)時(shí)間

-課程信息：課程號(hào)（唯一）、課程名、學(xué)分、所屬學(xué)院

-選課關(guān)系：學(xué)生可選多門(mén)課，每門(mén)課可被多個(gè)學(xué)生選，需記錄選課時(shí)間和成績(jī)（0-100分）要求：

(1)繪制該系統(tǒng)的E-R圖（需標(biāo)注實(shí)體屬性、聯(lián)系類型）；

(2)將E-R圖轉(zhuǎn)換為關(guān)系模式，并標(biāo)注主碼和外碼。答案：

-(1).E-R圖設(shè)計(jì)：

-實(shí)體1：學(xué)生（學(xué)號(hào)，姓名，性別，入學(xué)時(shí)間），主碼：學(xué)號(hào)

-實(shí)體2：課程（課程號(hào)，課程名，學(xué)分，所屬學(xué)院），主碼：課程號(hào)

-聯(lián)系：選課（學(xué)生，課程），聯(lián)系類型：多對(duì)多（M:N），屬性：選課時(shí)間、成績(jī)(2).關(guān)系模式轉(zhuǎn)換：學(xué)生（學(xué)號(hào)，姓名，性別，入學(xué)時(shí)間）主碼：學(xué)號(hào)

課程（課程號(hào)，課程名，學(xué)分，所屬學(xué)院）主碼：課程號(hào)

選課（學(xué)號(hào)，課程號(hào)，選課時(shí)間，成績(jī)）主碼：（學(xué)號(hào)，課程號(hào)）；外碼：學(xué)號(hào)→學(xué)生.學(xué)號(hào)，課程號(hào)→課程.課程號(hào)2.關(guān)系模式規(guī)范化題（12分）關(guān)系模式R(學(xué)號(hào)，姓名，課程號(hào)，課程名，成績(jī)，學(xué)院，院長(zhǎng))，函數(shù)依賴集F={

學(xué)號(hào)→姓名，學(xué)號(hào)→學(xué)院，學(xué)院→院長(zhǎng)，

課程號(hào)→課程名，(學(xué)號(hào)，課程號(hào))→成績(jī)

}要求：

(1)分析R屬于第幾范式（需說(shuō)明理由）；

(2)將R分解為3NF，要求保持函數(shù)依賴和無(wú)損連接。答案：

-(1).R屬于1NF，不滿足2NF。

理由：主碼是（學(xué)號(hào)，課程號(hào)），存在部分函數(shù)依賴：學(xué)號(hào)→姓名、學(xué)號(hào)→學(xué)院、學(xué)院→院長(zhǎng)、課程號(hào)→課程名，這些非主屬性（姓名、學(xué)院、院長(zhǎng)、課程名）部分依賴于主碼，違反2NF要求（所有非主屬性完全依賴于主碼）。(2).3NF分解步驟：分解為R1(學(xué)號(hào)，姓名，學(xué)院，院長(zhǎng))（包含學(xué)號(hào)→姓名，學(xué)號(hào)→學(xué)院，學(xué)院→院長(zhǎng)）

R2(課程號(hào)，課程名)（包含課程號(hào)→課程名）

R3(學(xué)號(hào)，課程號(hào)，成績(jī))（包含(學(xué)號(hào)，課程號(hào))→成績(jī)）

驗(yàn)證：R1中院長(zhǎng)傳遞依賴于學(xué)號(hào)（學(xué)號(hào)→學(xué)院→院長(zhǎng)），需進(jìn)一步分解R1為R11(學(xué)號(hào)，姓名，學(xué)院)和R12(學(xué)院，院長(zhǎng))。最終3NF模式：

R11(學(xué)號(hào)，姓名，學(xué)院)主碼：學(xué)號(hào)，外碼：無(wú)

R12(學(xué)院，院長(zhǎng))主碼：學(xué)院，外碼：無(wú)

R2(課程號(hào)，課程名)主碼：課程號(hào)，外碼：無(wú)

R3(學(xué)號(hào)，課程號(hào)，成績(jī))主碼：(學(xué)號(hào)，課程號(hào))，外碼：學(xué)號(hào)→R11.學(xué)號(hào)，課程號(hào)→R2.課程號(hào)3.SQL編程題（13分）根據(jù)第1題的“學(xué)生選課”數(shù)據(jù)庫(kù)，編寫(xiě)SQL語(yǔ)句實(shí)現(xiàn)以下需求：查詢“計(jì)算機(jī)學(xué)院”（所屬學(xué)院=‘計(jì)算機(jī)學(xué)院’）所有課程中，平均成績(jī)高于85分的課程號(hào)及平均成績(jī)（保留2位小數(shù)）；

統(tǒng)計(jì)每個(gè)學(xué)生的選課門(mén)數(shù)及平均成績(jī)，結(jié)果按選課門(mén)數(shù)降序、平均成績(jī)降序排列；

使用窗口函數(shù)查詢每個(gè)學(xué)生的最高成績(jī)及其在該課程中的排名（排名相同不跳號(hào)）。答案：

-(1).

sqlSELECT課程號(hào),ROUND(AVG(成績(jī)),2)AS平均成績(jī)FROM選課WHERE課程號(hào)IN(SELECT課程號(hào)FROM課程WHERE所屬學(xué)院='計(jì)算機(jī)學(xué)院')GROUPBY課程號(hào)HAVINGAVG(成績(jī))>85;(2).SELECT學(xué)號(hào),COUNT(課程號(hào))AS選課門(mén)數(shù),ROUND(AVG(成績(jī)),2)AS平均成績(jī)

FROM選課

GROUPBY學(xué)號(hào)

ORDERBY選課門(mén)數(shù)DESC,平均成績(jī)DESC;(3).WITH學(xué)生成績(jī)AS(

SELECT學(xué)號(hào),課程號(hào),成績(jī),

RANK()OVER(PARTITIONBY課程號(hào)ORDERBY成績(jī)DESC)AS課程排名

FROM選課

)

SELECT學(xué)號(hào),課程號(hào),成績(jī)AS最高成績(jī),課程排名

FROM學(xué)生成績(jī)

WHERE(學(xué)號(hào),成績(jī))IN(

SELECT學(xué)號(hào),MAX(成績(jī))FROM選課GROUPBY學(xué)號(hào)

);4.事務(wù)與并發(fā)控制題（12分）某銀行數(shù)據(jù)庫(kù)存在以下事務(wù)：

-T1：查詢賬戶A余額→若余額≥1000，轉(zhuǎn)賬500到賬戶B→更新A、B余額

-T2：查詢賬戶B余額→若余額≥2000，轉(zhuǎn)賬1000到賬戶C→更新B、C余額要求：

(1)分析T1和T2并發(fā)執(zhí)行時(shí)可能出現(xiàn)的問(wèn)題（舉例說(shuō)明）；

(2)若數(shù)據(jù)庫(kù)隔離級(jí)別為“可重復(fù)讀”，能否解決上述問(wèn)題？說(shuō)明理由；

(3)提出一種避免該問(wèn)題的優(yōu)化策略。答案：

-(1).可能出現(xiàn)“丟失更新”或“不可重復(fù)讀”問(wèn)題。例如：

T1讀取A余額為1500（允許轉(zhuǎn)賬），T2讀取B余額為2500（允許轉(zhuǎn)賬）；T1將B余額增加500（變?yōu)?000），T2將B余額減少1000（變?yōu)?500），最終B余額為1500，而正確結(jié)果應(yīng)為3000-1000=2000（T1的更新被T2覆蓋），導(dǎo)致丟失更新。(2).可重復(fù)讀隔離級(jí)別通過(guò)鎖機(jī)制保證事務(wù)期間讀取的記錄不被修改，能避免丟失更新和不可重復(fù)讀，但無(wú)法解決“幻讀”。本題中，可重復(fù)讀能確保T1和T2在各自事務(wù)內(nèi)讀取的B余額是一致的，防止一方覆蓋另一方的更新。(3).優(yōu)化策略：使用“串行化”隔離級(jí)別（最高級(jí)別），強(qiáng)制事務(wù)串行執(zhí)行；或在轉(zhuǎn)賬操作中對(duì)賬戶B加寫(xiě)鎖（X鎖），直到事務(wù)提交，避免并發(fā)修改。5.數(shù)據(jù)庫(kù)性能優(yōu)化題（11分）某電商訂單數(shù)據(jù)庫(kù)（表名：orders，字段：order_id（主鍵），user_id，create_time，total_amount，status）存在以下查詢：SELECTuser_id,AVG(total_amount)ASavg_amount

FROMorders

WHEREcreate_timeBETWEEN'2024-01-01'AND'2024-06-30'

ANDstatusIN('已支付','已發(fā)貨')

GROUPBYuser_id;要求：

(1)分析當(dāng)前查詢可能存在的性能瓶頸；

(2)提出索引優(yōu)化方案（需說(shuō)明索引類型及字段順序）；

(3)簡(jiǎn)述執(zhí)行計(jì)劃中“索引掃描”與“全表掃描”的區(qū)別。答案：

-(1).性能瓶頸：

-未對(duì)create_time和status建立索引，WHERE條件需全表掃描；

-GROUPBYuser_id需對(duì)過(guò)濾后的數(shù)據(jù)進(jìn)行排序，若數(shù)據(jù)量大則耗時(shí)；

-total_amount的AVG計(jì)算需遍歷所有符合條件的記錄。(2).索引優(yōu)化方案：

建立復(fù)合索引（status,create_time,user_id,total_amount）。

理由：WHERE條件中status是離散值（有限枚舉），作為索引首列可快速過(guò)濾；create_time范圍查詢緊隨其后；包含user_id（GROUPBY字段）和total_amount（計(jì)算字段），形成“覆蓋索引”，避免回表。(3).索引掃描與全表掃描的區(qū)別：索引掃描：通過(guò)索引快速定位符合條件的記錄位置，僅讀取索引中包含的字段（或通過(guò)索引找到數(shù)據(jù)行），IO開(kāi)銷??；

全表掃描：遍歷表中所有數(shù)據(jù)頁(yè)，逐行檢查是否符合條件，IO開(kāi)銷大，適用于小表或無(wú)合適索引的場(chǎng)景。6.分布式數(shù)據(jù)庫(kù)設(shè)計(jì)題（12分）某互聯(lián)網(wǎng)公司需設(shè)計(jì)分布式用戶數(shù)據(jù)庫(kù)（存儲(chǔ)用戶基本信息：user_id（主鍵），name，age，email），要求支持高并發(fā)讀、水平擴(kuò)展，且保證“用戶所在城市”維度的本地查詢高效。要求：

(1)設(shè)計(jì)分片策略（需說(shuō)明分片鍵、分片方式）；

(2)說(shuō)明如何解決分片后的跨分片查詢問(wèn)題；

(3)列舉兩種分布式數(shù)據(jù)庫(kù)一致性協(xié)議，并說(shuō)明適用場(chǎng)景。答案：

-(1).分片策略：

分片鍵：新增字段city（用戶所在城市）；

分片方式：按city哈希分片（如哈希(city)%分片數(shù)），或按city范圍分片（如將城市按拼音首字母分組）。

理由：保證同一城市的用戶數(shù)據(jù)存儲(chǔ)在同一分片，本地查詢（如“查詢北京市所有用戶”）無(wú)需跨分片，提升效率。(2).跨分片查詢解決方法：應(yīng)用層路由：應(yīng)用程序根據(jù)查詢條