2023年上半年中級數(shù)據(jù)庫系統(tǒng)工程師下午真題

2023年上半年中級數(shù)據(jù)庫系統(tǒng)工程師下午真題(總分：123.75，做題時(shí)間：150分鐘)一、試題一(總題數(shù)：1，分?jǐn)?shù)：15.00)1.

閱讀下列說明，回答問題1至問題3。

【說明】

某新能源汽車公司為了提升效率，需要開發(fā)一個(gè)汽車零件采購系統(tǒng)。請根據(jù)下述需求描述完成該系統(tǒng)的數(shù)據(jù)庫設(shè)計(jì)。

【需求描述】

(1)記錄供應(yīng)商的信息，包括供應(yīng)商的名稱、地址和一個(gè)電話。

(2)記錄零件的信息，包括零件的編碼、名稱和價(jià)格。

(3)記錄車型信息，包括車型的編號、名稱和規(guī)格。

(4)記錄零件采購信息。某個(gè)車型的某種零件可以從多家供應(yīng)商采購，某種零件也可以被多個(gè)車型采用，某家供應(yīng)商也可以供應(yīng)多種零件：還包括采購數(shù)量和采購日期。

【概念結(jié)構(gòu)設(shè)計(jì)】

根據(jù)需求階段收集的信息，設(shè)計(jì)的實(shí)體聯(lián)系圖(不完整)如下圖所示。

【邏輯結(jié)構(gòu)設(shè)計(jì)】

根據(jù)概念結(jié)構(gòu)設(shè)計(jì)階段完成的實(shí)體聯(lián)系圖，得出如下關(guān)系模式(不完整)：

供應(yīng)商(名稱，地址，電話)零件(編碼，名稱，價(jià)格)車型(編號，名稱，規(guī)格)

采購(車型編號，供應(yīng)商名稱，(a)，，(b)，采購日期)

（分?jǐn)?shù)：25.00）(1).

【問題1】

根據(jù)問題描述，補(bǔ)充上圖的實(shí)體聯(lián)系圖(不增加新的實(shí)體)

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：()解析：根據(jù)題干需求的(4)某個(gè)車型的某種零件可以從多家供應(yīng)商采購，某種零件也可以被多個(gè)車型采用，某家供應(yīng)商也可以供應(yīng)多種零件，還包括采購數(shù)量和采購日期?？梢缘玫焦?yīng)商、采購和車型是三元關(guān)系，并且是*：*：*的聯(lián)系。另外采購還有私有屬性采購數(shù)量和采購日期。(2).

【問題2】

補(bǔ)充邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果中的(a)、(b)兩處空缺，并標(biāo)注主鍵和外鍵完整性約束。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((a)零件編碼(b)采購數(shù)量主鍵：(車型編號，供應(yīng)商名稱，零件編碼，采購日期)外鍵：車型編號，供應(yīng)商名稱，零件編碼)解析：根據(jù)圖的實(shí)體聯(lián)系圖，可以得到采購關(guān)系的(a)是零件編碼，(b)是采購數(shù)量，然后主鍵是(車型編號，供應(yīng)商名稱，車型編號，采購日期)，外鍵有三個(gè)，分別是車型編號，供應(yīng)商名稱，車型編號。(3).

【問題3】

該汽車公司現(xiàn)新增如下需求：記錄車型在全國門店的銷售情況。門店信息包括門店的編號、地址和電話：銷售包括銷售數(shù)量和銷售日期等。

對原有設(shè)計(jì)進(jìn)行以下修改以實(shí)現(xiàn)該需求：

(1)在圖中體現(xiàn)門店信息及其車型銷售情況，并標(biāo)明新增的實(shí)體和聯(lián)系，及其必要屬性。

(2)給出新增加的關(guān)系模式，并標(biāo)注主鍵和外鍵元整性約束。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((1)

(2)銷售(門店編號，車型編號，銷售數(shù)量，銷售日期)。主鍵：(門店編號，車型編號，銷售日期)；外鍵：門店編號，車型編號。門店(門店編號，地址，電話)。主鍵：門店編號；外鍵：無。)解析：新增的關(guān)系模式有：銷售(門店編號，車型編號，銷售數(shù)量，銷售日期)。主鍵：(門店編號，車型編號，銷售日期)；外鍵：門店編號，車型編號。門店(門店編號，地址，電話)。主鍵：門店編號；外鍵：無。二、試題二(總題數(shù)：1，分?jǐn)?shù)：15.00)2.閱讀下列說明，回答問題1至問題2。

【說明】

一汽車廠商對配件進(jìn)行統(tǒng)一管理，設(shè)計(jì)了相應(yīng)的數(shù)據(jù)庫，其中一個(gè)表記錄了維修配件的使用信息。其表結(jié)構(gòu)如下：

維修配件使用表(車牌號，維修時(shí)間，配件編碼，配件名稱，配件供應(yīng)商，配件倉庫編碼，倉庫地址，維修配件數(shù)量)

其中，車牌號和配件編碼滿足唯一性。假設(shè)同一輛車在同一次維修情況下可能需要多種維修配件；一種配件只能存放于一個(gè)配件倉庫，一種配件只能由一個(gè)配件供應(yīng)商提供。維修時(shí)間精確到秒。（分?jǐn)?shù)：22.50）(1).

【問題1】

題中給出的維修配件使用表存在數(shù)據(jù)冗余，請給出具體的冗余屬性并說明因此會出現(xiàn)哪些異常?

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：(冗余屬性有：配件名稱，配件供應(yīng)商，配件倉庫編碼，倉庫地址。出現(xiàn)的異常有：存在插入異常，修改異常和刪除異常。)解析：根據(jù)題干信息可以得到函數(shù)的依賴關(guān)系有：配件編碼→(配件名稱，配件供應(yīng)商，配件倉庫編碼)配件倉庫編碼→倉庫地址(車牌號，維修時(shí)間，配件編碼)→維修配件數(shù)量所以候選鍵是(車牌號，維修時(shí)間，配件編碼)?？梢缘玫饺哂鄬傩杂信浼Q，配件供應(yīng)商，配件倉庫編碼，倉庫地址。存在插入異常，修改異常和刪除異常的。維修配件表存在部分函數(shù)依賴和傳遞函數(shù)依賴，所以維修配件使用表是不滿足BCNF，可以將維修配件表分解為，分解1(配件倉庫編碼，倉庫地址)，主鍵是配件倉庫編碼，外鍵無。分解2(配件編碼，配件名稱，配件供應(yīng)商，配件倉庫編碼)，主鍵是配件編碼，外鍵是配件倉庫編碼。分解3(車牌號，維修時(shí)間，配件編碼，維修配件數(shù)量)，主鍵是(車牌號，維修時(shí)間，配件編碼)，外鍵是配件編碼。這三個(gè)表都滿足BCNF。(2).

【問題2】

維修配件使用表是否滿足BCNF?如果不滿足，請對其進(jìn)行模式分解，使分解后的關(guān)系模式滿足BCNF，并標(biāo)記出主鍵和外鍵。

（分?jǐn)?shù)：7.50）__________________________________________________________________________________________

正確答案：(不滿足。分解為三個(gè)關(guān)系模式，分別是：分解1(配件倉庫編碼，倉庫地址)，主鍵是配件倉庫編碼，外鍵無。分解2(配件編碼，配件名稱，配件供應(yīng)商，配件倉庫編碼)，主鍵是配件編碼，外鍵是配件倉庫編碼。分解3(車牌號，維修時(shí)間，配件編碼，維修配件數(shù)量)，主鍵是(車牌號，維修時(shí)間，配件編碼)，外鍵是配件編碼。)解析：三、試題三(總題數(shù)：1，分?jǐn)?shù)：15.00)3.閱讀下列說明，回答問題1至問題4。

【說明】

某教務(wù)管理系統(tǒng)的部分?jǐn)?shù)據(jù)庫關(guān)系模式如下：

學(xué)生：STUDENT(Sno，Sname，Ssex，Sage，Sdept)，各屬性分別表示學(xué)號、姓名、性別、年齡、所在系名;

課程：COURSE(Cno，Cname，Cpno，Ceredit)，各屬性分別表示課程號、課程名、先修課的課程號、學(xué)分；

選課：SC(Sno，Cno，Grade)，各屬性分別表示學(xué)號、課程號、成績。

有關(guān)關(guān)系模式的說明如下：

(1)下劃線標(biāo)出的屬性是表的主鍵。

(2)課程名取值唯一。

根據(jù)以上描述，回答下列問題，將SQL語句的空缺部分補(bǔ)充完整。（分?jǐn)?shù)：26.25）(1).

【問題1】

請將下面創(chuàng)建課程表COURSE的SQL語句補(bǔ)充完整，要求定義實(shí)體完整性約束、參照完整性約束以及其他完整性約束。

CREATETABLECOURSE(

CnoCHAR(4)PRIMARYKEY,

CnameCHAR(30)(a),

CpnoCHAR(4)REFERENCES(b)((c))

CcreditINT);

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：((a)UNIQUE(b)COURSE(c)Cno)解析：問題1中COURSE的SQL語句為。CREATETABLECOURSE(CnoCHAR(4)PRIMARYKEY,CnameCHAR(30)UNIQUE,CpnoCHAR(4)REFERENCESCOURSE(Cno)CcreditINT)(2).

【問題2】

有一門課程號為“C036的新開課要求所有學(xué)生選修。該課的基本信息已經(jīng)錄入課程表COURSE中，現(xiàn)需在選課表SC中插入該課的選課記錄。實(shí)現(xiàn)此功能的SQL語句如下，請補(bǔ)全。

(d)INTOSC(Sno,(e))

SELECTSno,(f)

FROM(g);

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((d)INSERT(e)Cno(f)‘C036’(g)STUDENT)解析：(3).

【問題3】

查詢每一門課程的間接先修課(先修課的先修課)，要求輸出課程號和間接先修課的課程號。即使某門課程沒有先修課，也需要輸出，不過其間接先修課為空。此功能由下面的SQL語句實(shí)現(xiàn)，請補(bǔ)全。

SELECTK1.Cno,(h)

FROMCOURSEK1(i)OUTERJOINCOURSEK2(j)((k));

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((h)k2.Cpno(i)LEFT(j)ON(k)k1.Cpno=k2.Cno)解析：問題3中查詢每一門課程的間接先修課(先修課的先修課)，要求輸出課程號和間接先修課的課程號。即使某門課程沒有先修課，也需要輸出，不過其間接先修課為空。SQL語句為：。SELECTK1.Cno，k2.CpnoFROMCOURSEK1LEFTOUTERJOINCOURSEK2ONk1.Cpno=k2.Cno;(4).

【問題4】

查詢選修了課程表中已有全部課程的學(xué)生，要求輸出學(xué)號和姓名。此功能由下面的SQL語句實(shí)現(xiàn)，請補(bǔ)全。

SELECTSno,SnameFROMSTUDENT

WHERENOTEXISTS

(SELECT*FROM(l)

WHERE(m)

(SELECT*FROM(n)

WHERE(o)));

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((l)COURSE(m)notexists(n)SC(o)SC.Sno=STUDENT.SnoandSC.Cno=COURSE.Cno)解析：問題4是雙重否定(兩個(gè)NOTEXISTS)的語句表肯定。查詢選修了課程表中已有全部課程的學(xué)生，要求輸出學(xué)號和姓名。語句如下所示：SELECTSno,SnameFROMSTUDENTWHERENOTEXISTS(SELECT*FROMcourseWHEREnotexists(SELECT*FROMSCWHERESC.Sno=student.Snoandcno=o));四、試題四(總題數(shù)：1，分?jǐn)?shù)：15.00)4.閱讀下列說明，回答問題1至問題3。

【說明】

某企業(yè)內(nèi)部信息系統(tǒng)部分簡化后的關(guān)系模式如下：員工表：EMPLOYEES(Eid，Ename，Address，Phone，Jid):屬性含義分別為：員工編碼、員工姓名、家庭住址、聯(lián)系電話、崗位級別編碼。

崗位級別表：JOB_LEVELS(Jid，Jname，Jbase_salary):屬性含義分別為：崗位級別編碼、崗位名稱、崗位基本工資。

員工工資表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，tax，year，month):屬性含義分別為：員工編碼、考勤工資、績效工資、加班工資、最終工資、稅、年份、月份。

該企業(yè)在每月25日計(jì)算員工的工資。首先是根據(jù)考勤系統(tǒng)以及績效系統(tǒng)中的數(shù)據(jù)，計(jì)算出員工的考勤、績效和加班工資，存入到員工工資表；其次結(jié)合員工的崗位基本工資，計(jì)算出最終工資，完成對員工工資表記錄的更新。最后依據(jù)員工工資表完成工資的發(fā)放。（分?jǐn)?shù)：25.00）(1).

【問題1】

下面是月底25日計(jì)算某員工最終工資的存儲過程程序，請補(bǔ)全空缺處的代碼。

CREATEPROCEDURESalaryCalculation((a)empIdchar(8),INiYearnumber(4),INiMonthnumber(2))

DECLARE

attendancenumber(14,2);

meritnumber(14,2);

overtimenumber(14,2);

basenumber(14,2);

all_salarynumber(14,2);

BEGIN

SELECTattendance_wage,merit_pay,overtime_wageINTO(b)

FROMSALARY

WHEREEid=empldFORUPDATE;

SELECTJbase_salaryINTO:baseFROMEMPLOYEEST1,(c)

WHERET1.Jid=T2.JidANDT1.Eid=empld;

allsalary:=attendance+merit+overtime+base;

UPDATESALARYSETsalary=:all_salary

WHERE(d)ANDyear=iYearANDmonth=iMonth;

(e);

EXCEPTIONWHENOTHERSTHEN

(f);

END;

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：((a)IN(b):attendance，:merit，:overtime(c)JOB_LEVELST2(d)eid=empld(e)COMMIT(f)ROLLBACK)解析：問題1的語句如下所示，下面是月底25日計(jì)算某員工最終工資的存儲過程程序。CREATEPROCEDURESalaryCalculation(INempIdchar(8),INiYearnumber(4),INiMonthnumber(2))DECLAREattendancenumber(14,2);meritnumber(14,2);overtimenumber(14,2);basenumber(14,2);all_salarynumber(14,2);BEGINSELECTattendance_wage,merit_pay,overtime_wageINTO:attendance，:merit，:overtime//注意這里是根據(jù)上下文來決定的，所以前面加了：，因?yàn)樵谙旅嬗?baseFROMSALARYWHEREEid=empldFORUPDATE;SELECTJbase_salaryINTO:baseFROMEMPLOYEEST1,JOB_LEVELST2WHERET1.Jid=T2.JidANDT1.Eid=empld;allsalary:=attendance+merit+overtime+base;UPDATESALARYSETsalary=:all_salaryWHEREeid=empldANDyear=iYearANDmonth=iMonth;COMMIT;EXCEPTIONWHENOTHERSTHENROLLBACK;END;(2).

【問題2】

為了防止對員工工資表的非法修改(包括內(nèi)部犯罪)，系統(tǒng)特意規(guī)定了員工工資表修改的業(yè)務(wù)規(guī)則：對員工工資表的修改只能在每月25日的上班時(shí)間進(jìn)行。

下面是員工工資表修改業(yè)務(wù)規(guī)則對應(yīng)的程序，請補(bǔ)全空缺處的代碼。

CREATETRIGGERCheckBusinessRule

(g)INSERTORDELETEOR(h)onSALARY

FOREACH(i)

BEGIN

IF(TO_CHAR(sysdate,'DD')＜＞(j))

OR(to_number(TO_CHAR(sysdate,'HH24))

(k)BETWEEN8AND18)THEN

Raise_Error;//拋出異常

ENDIF;

END;

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((g)before(h)update(i)row(j)‘25’(k)not)解析：問題2的語句如下所示，為了防止對員工工資表的非法修改(包括內(nèi)部犯罪)，系統(tǒng)特意規(guī)定了員工工資表修改的業(yè)務(wù)規(guī)則：對員工工資表的修改只能在每月25日的上班時(shí)間進(jìn)行。員工工資表修改業(yè)務(wù)規(guī)則對應(yīng)的程序是：CREATETRIGGERCheckBusinessRulebeforeINSERTORDELETEORupdateonSALARYFOREACHrowBEGINIF(TO_CHAR(sysdate,'DD')＜＞‘25’)OR(to_number(TO_CHAR(sysdate,'HH24))notBETWEEN8AND18)THENRaise_Error;//拋出異常ENDIF;END;(3).

【問題3】

人事部門具有每月對員工進(jìn)行額外獎罰的權(quán)限，該獎罰也反應(yīng)到員工的最終工資上。假設(shè)當(dāng)某月計(jì)算一位員工的最終工資時(shí)，同一時(shí)間人事部門對該員工執(zhí)行了獎勵2000元的事務(wù)操作，對應(yīng)事務(wù)的部分調(diào)度序列如下表所示。

(1)請說明該事務(wù)調(diào)度存在哪種并發(fā)問題?

(2)采用2PL是否可以解決該并發(fā)問題?是否會產(chǎn)生死鎖?

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((1)該事務(wù)存在丟失更新的問題。因?yàn)槿耸虏块T獎罰事務(wù)更新的最終工資被計(jì)算最終工資事務(wù)的寫入而覆蓋了。(2)可以?？赡軙a(chǎn)生死鎖。)解析：問題3考查兩段鎖協(xié)議(2PL)，兩段鎖協(xié)議(2PL)是指對任何數(shù)據(jù)進(jìn)行讀寫之前必須對該數(shù)據(jù)加鎖:在釋放一個(gè)封鎖之后，事務(wù)不再申請和獲得任何其他封鎖。所謂'兩段'鎖的含義是:事務(wù)分為兩個(gè)階段。第一階段是獲得封鎖，也稱為擴(kuò)展階段。第二階段是釋放封鎖，也稱為收縮階段。需要注意的是采用兩段鎖協(xié)議也有可能產(chǎn)生死鎖，這是因?yàn)槊總€(gè)事務(wù)都不能及時(shí)解除被它封鎖的數(shù)據(jù)，可能會導(dǎo)致多個(gè)事務(wù)互相都要求對方己封鎖的數(shù)據(jù)不能繼續(xù)運(yùn)行。五、試題五(總題數(shù)：1，分?jǐn)?shù)：15.00)5.

閱讀下列說明，回答問題1至問題3。

【說明】

某裝備的組裝過程需要經(jīng)過多道程序，由于工作空間狹小，同時(shí)只能有一人在操作間工作，所以在每道工序之間需要先完成使用配件的出庫后，操作人員攜帶配件到操作間進(jìn)行安裝工作，安裝過程中需要掃描配件編碼以自動記錄該配件的安裝情況。

假使存在三個(gè)事務(wù)用于處理某一類配件的某次安裝實(shí)施，事務(wù)T1負(fù)責(zé)出庫登記，T2負(fù)責(zé)安裝登記，T3負(fù)責(zé)將未使用的配件重新入庫。所有三個(gè)事務(wù)執(zhí)行完成后，T1出庫的數(shù)量應(yīng)等于T2安裝的數(shù)量與T3重新入庫的數(shù)量之和。數(shù)據(jù)項(xiàng)I記錄配件的庫存數(shù)量，數(shù)據(jù)項(xiàng)J記錄成功安裝的數(shù)量。

某次組裝過程出庫了12個(gè)配件，安裝了6個(gè)。假設(shè)數(shù)據(jù)庫系統(tǒng)采用檢查點(diǎn)機(jī)制對故障進(jìn)行恢復(fù)，部分日志文件如下表所示。日志記錄內(nèi)容中：＜Ti，START＞表示事務(wù)Ti開始執(zhí)行，＜Ti，COMMIT＞表示事務(wù)Ti提交，＜Ti，D，V1，V