內(nèi)蒙古2025自考[計算機科學與技術]數(shù)據(jù)庫系統(tǒng)原理案例題專練一、簡答題（每題10分，共20分）1.案例：某內(nèi)蒙古乳制品公司使用數(shù)據(jù)庫管理其產(chǎn)品庫存和銷售數(shù)據(jù)。數(shù)據(jù)庫中包含以下表結(jié)構：-`Products`（產(chǎn)品編號PK，產(chǎn)品名稱，產(chǎn)品類別，庫存量）-`Sales`（銷售編號PK，產(chǎn)品編號FK，銷售日期，銷售數(shù)量，銷售門店）簡述如何使用SQL語句實現(xiàn)以下功能：（1）查詢庫存量低于10的所有產(chǎn)品及其類別。（2）統(tǒng)計每個銷售門店的累計銷售數(shù)量。2.案例：假設某內(nèi)蒙古旅游公司需要設計一個數(shù)據(jù)庫來管理游客信息和酒店預訂。數(shù)據(jù)庫中包含以下表結(jié)構：-`Customers`（客戶編號PK，姓名，聯(lián)系電話，郵箱）-`Hotels`（酒店編號PK，酒店名稱，酒店地址，星級）-`Reservations`（預訂編號PK，客戶編號FK，酒店編號FK，入住日期，退房日期）簡述以下需求對應的SQL查詢語句：（1）查找所有入住過“內(nèi)蒙古呼和浩特香格里拉酒店”的游客姓名。（2）計算每個星級的酒店總數(shù)及平均預訂數(shù)量。二、設計題（每題15分，共30分）1.案例：某內(nèi)蒙古煤炭企業(yè)需要設計一個數(shù)據(jù)庫來管理礦井信息、設備維護和員工記錄。要求如下：-礦井信息包括礦井編號（PK）、礦井名稱、位置、年產(chǎn)量。-設備維護記錄包括維護編號（PK）、設備編號（FK）、維護日期、維護內(nèi)容。-員工記錄包括員工編號（PK）、姓名、所屬礦井編號（FK）、職位。設計以下關系模式：（1）畫出E-R圖，標明實體、屬性和關系。（2）將E-R圖轉(zhuǎn)換為關系模式（3NF）。2.案例：某內(nèi)蒙古草原旅游平臺需要設計一個數(shù)據(jù)庫來管理草原景區(qū)、游客評價和導游信息。要求如下：-草原景區(qū)包括景區(qū)編號（PK）、景區(qū)名稱、位置、面積。-游客評價包括評價編號（PK）、景區(qū)編號（FK）、游客姓名、評分、評價內(nèi)容。-導游信息包括導游編號（PK）、姓名、導游證編號、擅長景區(qū)。設計以下數(shù)據(jù)庫結(jié)構：（1）定義主鍵和外鍵約束。（2）編寫SQL語句創(chuàng)建上述表結(jié)構。三、應用題（每題25分，共50分）1.案例：某內(nèi)蒙古畜牧業(yè)公司使用數(shù)據(jù)庫管理其牧場數(shù)據(jù)。數(shù)據(jù)庫中包含以下表結(jié)構：-`Cows`（牛編號PK，牛名，出生日期，品種，體重）-`HealthRecords`（健康編號PK，牛編號FK，檢查日期，癥狀，治療措施）-`SalesRecords`（銷售編號PK，牛編號FK，銷售日期，銷售價格，購買者）要求：（1）編寫SQL語句查詢所有體重超過1000公斤的奶牛，并按出生日期降序排列。（2）統(tǒng)計每種品種的牛數(shù)量，并篩選出數(shù)量超過10的品種。（3）查詢2024年1月1日之后所有有健康記錄的牛名及癥狀。2.案例：某內(nèi)蒙古電力公司需要設計一個數(shù)據(jù)庫來管理用戶用電信息和繳費記錄。數(shù)據(jù)庫中包含以下表結(jié)構：-`Customers`（客戶編號PK，客戶姓名，地址，聯(lián)系方式）-`Bills`（賬單編號PK，客戶編號FK，用電量，繳費日期，繳費狀態(tài)）-`Payments`（繳費記錄編號PK，賬單編號FK，繳費金額，實際繳費日期）要求：（1）編寫SQL語句查詢所有未繳費的賬單信息（繳費狀態(tài)為“未繳費”）。（2）統(tǒng)計每個客戶的累計繳費金額，并按繳費金額降序排列。（3）編寫SQL語句實現(xiàn)以下需求：如果一個賬單的用電量超過2000度，則自動將其繳費狀態(tài)更新為“需審核”。答案與解析一、簡答題1.答案：（1）`SELECT產(chǎn)品名稱,產(chǎn)品類別FROMProductsWHERE庫存量<10;`（2）`SELECT銷售門店,SUM(銷售數(shù)量)AS累計銷售數(shù)量FROMSalesGROUPBY銷售門店;`解析：第（1）題使用`WHERE`子句篩選庫存量低于10的產(chǎn)品，并返回產(chǎn)品名稱和類別。第（2）題使用`GROUPBY`按銷售門店分組，并使用`SUM`統(tǒng)計每個門店的累計銷售數(shù)量。2.答案：（1）`SELECTCustomers.姓名FROMCustomersJOINReservationsONCustomers.客戶編號=Reservations.客戶編號JOINHotelsONReservations.酒店編號=Hotels.酒店編號WHEREHotels.酒店名稱='內(nèi)蒙古呼和浩特香格里拉酒店';`（2）`SELECT星級,COUNT(DISTINCT酒店編號)AS酒店總數(shù),AVG(Sales.銷售數(shù)量)AS平均預訂數(shù)量FROMHotelsJOINReservationsONHotels.酒店編號=Reservations.酒店編號GROUPBY星級;`解析：第（1）題通過多表連接查詢?nèi)胱∵^指定酒店的游客姓名。第（2）題統(tǒng)計每個星級的酒店總數(shù)（使用`COUNT(DISTINCT)`去重）和平均預訂數(shù)量（假設`Sales`表存儲預訂數(shù)量）。二、設計題1.答案：（1）E-R圖（文字描述）：-實體：礦井（礦井編號，礦井名稱，位置，年產(chǎn)量）、設備（維護編號，設備編號，維護日期，維護內(nèi)容）、員工（員工編號，姓名，所屬礦井編號，職位）。-關系：礦井與設備（一對多，一個礦井有多個設備維護記錄）、礦井與員工（一對多，一個礦井有多個員工）。（2）關系模式（3NF）：sqlCREATETABLE礦井(礦井編號INTPRIMARYKEY,礦井名稱VARCHAR(50),位置VARCHAR(100),年產(chǎn)量INT);CREATETABLE設備(維護編號INTPRIMARYKEY,礦井編號INT,設備編號VARCHAR(20),維護日期DATE,維護內(nèi)容TEXT,FOREIGNKEY(礦井編號)REFERENCES礦井(礦井編號));CREATETABLE員工(員工編號INTPRIMARYKEY,姓名VARCHAR(50),所屬礦井編號INT,職位VARCHAR(20),FOREIGNKEY(所屬礦井編號)REFERENCES礦井(礦井編號));解析：E-R圖通過實體和關系描述了礦井、設備維護和員工之間的關系。關系模式轉(zhuǎn)換為3NF消除了冗余，如`設備`表中的`礦井編號`作為外鍵關聯(lián)`礦井`表。2.答案：（1）E-R圖（文字描述）：-實體：草原景區(qū)（景區(qū)編號，景區(qū)名稱，位置，面積）、游客評價（評價編號，景區(qū)編號，游客姓名，評分，評價內(nèi)容）、導游（導游編號，姓名，導游證編號，擅長景區(qū)）。-關系：景區(qū)與評價（多對多，一個景區(qū)有多個評價）、景區(qū)與導游（多對多，一個景區(qū)可由多個導游負責）。（2）表結(jié)構創(chuàng)建語句：sqlCREATETABLE草原景區(qū)(景區(qū)編號INTPRIMARYKEY,景區(qū)名稱VARCHAR(50),位置VARCHAR(100),面積DECIMAL(10,2));CREATETABLE游客評價(評價編號INTPRIMARYKEY,景區(qū)編號INT,游客姓名VARCHAR(50),評分INT,評價內(nèi)容TEXT,FOREIGNKEY(景區(qū)編號)REFERENCES草原景區(qū)(景區(qū)編號));CREATETABLE導游(導游編號INTPRIMARYKEY,姓名VARCHAR(50),導游證編號VARCHAR(20),擅長景區(qū)INT,FOREIGNKEY(擅長景區(qū))REFERENCES草原景區(qū)(景區(qū)編號));解析：E-R圖通過多對多關系描述景區(qū)、評價和導游的關聯(lián)。表結(jié)構中定義了主鍵和外鍵約束，確保數(shù)據(jù)一致性。三、應用題1.答案：（1）`SELECT牛名,出生日期FROMCowsWHERE體重>1000ORDERBY出生日期DESC;`（2）`SELECT品種,COUNT()AS牛數(shù)量FROMCowsGROUPBY品種HAVINGCOUNT()>10;`（3）`SELECTCows.牛名,HealthRecords.癥狀FROMCowsJOINHealthRecordsONCows.牛編號=HealthRecords.牛編號WHEREHealthRecords.檢查日期>'2024-01-01';`解析：第（1）題篩選體重超過1000公斤的牛，并按出生日期降序排列。第（2）題統(tǒng)計每種品種的牛數(shù)量，并篩選數(shù)量超過10的品種。第（3）題查詢2024年1月1日后有健康記錄的牛名和癥狀。2.答案：（1）`SELECTFROMBillsWHERE繳費狀態(tài)='未繳費';`（2）`SELECTCustomers.客戶姓名,SUM(Bills.用電量Bills.繳費金額)AS累計繳費金額FROMCustomersJOINBillsONCustomers.客戶編號=Bills.客戶編號GROUPBYCustomers.客戶姓名ORDERBY累計繳費金