2025年P(guān)ython數(shù)據(jù)庫(kù)應(yīng)用專(zhuān)項(xiàng)訓(xùn)練試卷數(shù)據(jù)庫(kù)設(shè)計(jì)技巧考試時(shí)間：______分鐘總分：______分姓名：______一、簡(jiǎn)答題1.請(qǐng)簡(jiǎn)述數(shù)據(jù)庫(kù)設(shè)計(jì)第一范式（1NF）的核心要求及其主要解決的問(wèn)題。2.在設(shè)計(jì)數(shù)據(jù)庫(kù)表時(shí)，選擇合適的數(shù)據(jù)類(lèi)型的重要性體現(xiàn)在哪些方面？3.什么是ER圖？請(qǐng)說(shuō)明ER圖中主要包含哪些元素，并簡(jiǎn)述它們各自表示的含義。4.“數(shù)據(jù)庫(kù)設(shè)計(jì)應(yīng)遵循范式理論，越高越好”這種說(shuō)法是否絕對(duì)？請(qǐng)結(jié)合實(shí)際應(yīng)用場(chǎng)景，說(shuō)明在不同范式級(jí)別（如2NF,3NF）之間權(quán)衡的考慮因素。二、設(shè)計(jì)題1.假設(shè)需要為一個(gè)在線(xiàn)圖書(shū)銷(xiāo)售平臺(tái)設(shè)計(jì)數(shù)據(jù)庫(kù)部分。該平臺(tái)需要管理用戶(hù)信息、圖書(shū)信息、訂單信息以及訂單詳情（即用戶(hù)購(gòu)買(mǎi)的具體圖書(shū)和數(shù)量）。請(qǐng)根據(jù)此需求，進(jìn)行數(shù)據(jù)庫(kù)概念設(shè)計(jì)：*識(shí)別核心實(shí)體，并列出每個(gè)實(shí)體的主要屬性（至少包含屬性名和數(shù)據(jù)類(lèi)型建議，如`用戶(hù)ID`VARCHAR,`圖書(shū)名稱(chēng)`VARCHAR等）。*分析實(shí)體之間的關(guān)系，明確關(guān)系的類(lèi)型（一對(duì)一、一對(duì)多、多對(duì)多），并說(shuō)明理由。對(duì)于多對(duì)多關(guān)系，如何通過(guò)關(guān)聯(lián)表來(lái)實(shí)現(xiàn)？*為每個(gè)實(shí)體設(shè)計(jì)主鍵，并說(shuō)明選擇依據(jù)。*指出需要設(shè)置的外鍵，并說(shuō)明其作用。*列舉至少3個(gè)你在設(shè)計(jì)過(guò)程中需要考慮并做出的關(guān)鍵設(shè)計(jì)決策，并簡(jiǎn)述理由。2.已有一個(gè)簡(jiǎn)化的數(shù)據(jù)庫(kù)設(shè)計(jì)，包含三個(gè)表：`員工`（`員工ID`INT,`姓名`VARCHAR,`部門(mén)ID`INT,`郵箱`VARCHAR），`部門(mén)`（`部門(mén)ID`INT,`部門(mén)名稱(chēng)`VARCHAR），`工資`（`員工ID`INT,`工資`DECIMAL,`發(fā)放日期`DATE）。該設(shè)計(jì)存在一些潛在問(wèn)題。請(qǐng)分析該設(shè)計(jì)的主要問(wèn)題（至少指出兩個(gè)），并提出具體的優(yōu)化方案，目的是減少數(shù)據(jù)冗余并保證數(shù)據(jù)一致性。說(shuō)明你的優(yōu)化方案如何解決原有問(wèn)題。三、評(píng)估與優(yōu)化題1.考慮以下場(chǎng)景：一個(gè)學(xué)校需要記錄學(xué)生的選課信息。每個(gè)學(xué)生可以選修多門(mén)課程，每門(mén)課程可以有多個(gè)學(xué)生選修。此外，還需要記錄每門(mén)課程的學(xué)分。*簡(jiǎn)述如果直接在`學(xué)生`表和`課程`表中建立多對(duì)多關(guān)系，會(huì)導(dǎo)致的數(shù)據(jù)冗余問(wèn)題。*設(shè)計(jì)一個(gè)包含`選課`關(guān)聯(lián)表的方案來(lái)處理這種多對(duì)多關(guān)系。在`選課`表中，除了關(guān)聯(lián)學(xué)生和課程的外鍵，你建議增加哪些字段，并說(shuō)明原因？*如果經(jīng)常需要查詢(xún)某個(gè)學(xué)生的總學(xué)分或某個(gè)課程的選課學(xué)生名單，從數(shù)據(jù)庫(kù)設(shè)計(jì)角度，可以采取哪些技巧來(lái)優(yōu)化查詢(xún)性能？請(qǐng)至少提出兩種方法。四、實(shí)踐應(yīng)用題（假設(shè)使用Python和SQLite）1.假設(shè)你已經(jīng)根據(jù)某個(gè)設(shè)計(jì)（不必詳細(xì)給出）創(chuàng)建了以下兩個(gè)表：```sqlCREATETABLEauthors(author_idINTEGERPRIMARYKEY,nameTEXTNOTNULL,birth_yearINTEGER);CREATETABLEbooks(book_idINTEGERPRIMARYKEY,titleTEXTNOTNULL,publication_yearINTEGER,author_idINTEGER,FOREIGNKEY(author_id)REFERENCESauthors(author_id));```請(qǐng)寫(xiě)出Python代碼片段（使用`sqlite3`庫(kù)），完成以下任務(wù)：*連接到一個(gè)名為`library.db`的SQLite數(shù)據(jù)庫(kù)（如果不存在則創(chuàng)建）。*插入至少兩行`authors`表的數(shù)據(jù)。*基于剛才插入的作者信息，插入至少兩行`books`表的數(shù)據(jù)，并為其中一本書(shū)指定一個(gè)已存在的作者ID。*編寫(xiě)一個(gè)SQL查詢(xún)語(yǔ)句（在Python中使用字符串格式化或參數(shù)化方式），用于查找所有1990年之后出生的作者所寫(xiě)的書(shū)籍的標(biāo)題和作者名。將查詢(xún)結(jié)果打印出來(lái)。試卷答案一、簡(jiǎn)答題1.解析思路：回答1NF的核心是“原子性”，即每個(gè)字段都不能再分解。解決的問(wèn)題是避免數(shù)據(jù)冗余和不一致，確保每個(gè)字段只包含一個(gè)不可再分的基本數(shù)據(jù)項(xiàng)。答案：第一范式（1NF）要求數(shù)據(jù)庫(kù)表的每一列都包含原子值，即每個(gè)字段內(nèi)的值都是不可再分的獨(dú)立單元。它主要解決的問(wèn)題是數(shù)據(jù)冗余和更新異常，通過(guò)確保每個(gè)記錄在各個(gè)屬性上是原子性的，避免了在一個(gè)字段內(nèi)存儲(chǔ)重復(fù)組數(shù)據(jù)，從而保證了數(shù)據(jù)的簡(jiǎn)潔性和一致性。2.解析思路：回答選擇合適數(shù)據(jù)類(lèi)型的重要性，從存儲(chǔ)效率、計(jì)算性能、數(shù)據(jù)準(zhǔn)確性、接口兼容性等多個(gè)角度展開(kāi)。答案：選擇合適的數(shù)據(jù)類(lèi)型非常重要，主要體現(xiàn)在：1）存儲(chǔ)效率：不同類(lèi)型占用空間不同，合理選擇可節(jié)省存儲(chǔ)空間；2）計(jì)算性能：整數(shù)運(yùn)算通常比字符串運(yùn)算快；3）數(shù)據(jù)準(zhǔn)確性：類(lèi)型必須能準(zhǔn)確表示數(shù)據(jù)，如用`DATE`類(lèi)型存儲(chǔ)日期；4）接口兼容性：確保數(shù)據(jù)類(lèi)型符合API或標(biāo)準(zhǔn)規(guī)范，便于數(shù)據(jù)交換和集成；5）約束有效性：某些類(lèi)型自帶約束（如`VARCHAR`長(zhǎng)度限制），有助于保證數(shù)據(jù)有效性。3.解析思路：解釋ER圖是什么，然后列出并解釋核心元素：實(shí)體（Entity）、屬性（Attribute）、關(guān)系（Relationship）及其基數(shù)（Cardinality）。答案：ER圖（實(shí)體-關(guān)系圖）是一種用于數(shù)據(jù)庫(kù)概念設(shè)計(jì)的圖形化工具，它直觀地表示現(xiàn)實(shí)世界中的實(shí)體、它們的屬性以及實(shí)體之間的聯(lián)系。主要元素包括：實(shí)體（通常用矩形表示，代表現(xiàn)實(shí)世界中的事物或?qū)ο?，如“學(xué)生”、“課程”）；屬性（通常用橢圓形表示，描述實(shí)體的特征，如“學(xué)生”的“學(xué)號(hào)”、“姓名”）；關(guān)系（通常用菱形表示，連接兩個(gè)或多個(gè)實(shí)體，表示它們之間的關(guān)聯(lián)，如“選修”關(guān)系連接“學(xué)生”和“課程”）；基數(shù)（通常在關(guān)系旁標(biāo)注，表示實(shí)體間連接的數(shù)量關(guān)系，如“一對(duì)一”、“一對(duì)多”、“多對(duì)多”）。4.解析思路：首先否定“越高越好”的絕對(duì)性，然后說(shuō)明范式級(jí)別是權(quán)衡的結(jié)果。低范式（如1NF）簡(jiǎn)單但冗余，高范式（如3NF）減少冗余但可能影響查詢(xún)性能。答案：“數(shù)據(jù)庫(kù)設(shè)計(jì)應(yīng)遵循范式理論，越高越好”這種說(shuō)法并非絕對(duì)。雖然遵循范式理論（特別是1NF,2NF,3NF）有助于減少數(shù)據(jù)冗余、避免更新異常、保證數(shù)據(jù)一致性，但過(guò)高追求范式級(jí)別（如達(dá)到BCNF或4NF,5NF）可能會(huì)使數(shù)據(jù)庫(kù)結(jié)構(gòu)變得過(guò)于復(fù)雜，導(dǎo)致查詢(xún)時(shí)需要過(guò)多的表連接（JOIN操作），從而降低查詢(xún)性能。因此，數(shù)據(jù)庫(kù)設(shè)計(jì)需要在規(guī)范化（符合范式）和性能（查詢(xún)效率）之間進(jìn)行權(quán)衡，根據(jù)實(shí)際應(yīng)用場(chǎng)景的需求和優(yōu)先級(jí)，選擇合適的范式級(jí)別，有時(shí)甚至?xí)榱瞬樵?xún)效率而采用反范式設(shè)計(jì)。二、設(shè)計(jì)題1.解析思路：識(shí)別核心實(shí)體：用戶(hù)、圖書(shū)、訂單、訂單詳情。列出各實(shí)體關(guān)鍵屬性并建議數(shù)據(jù)類(lèi)型。分析關(guān)系：用戶(hù)與訂單是一對(duì)多，訂單與訂單詳情是一對(duì)多，圖書(shū)與訂單詳情是多對(duì)多（通過(guò)關(guān)聯(lián)表）。設(shè)計(jì)主鍵：唯一標(biāo)識(shí)每個(gè)實(shí)體。設(shè)計(jì)外鍵：維護(hù)關(guān)系完整性。關(guān)鍵決策點(diǎn)：如何處理圖書(shū)庫(kù)存、訂單狀態(tài)、關(guān)聯(lián)表設(shè)計(jì)等。答案：*實(shí)體與屬性：*用戶(hù)(User):用戶(hù)ID(INT,主鍵),用戶(hù)名(VARCHAR),密碼(VARCHAR/TEXT),郵箱(VARCHAR),注冊(cè)日期(DATETIME)*圖書(shū)(Book):圖書(shū)ID(INT,主鍵),圖書(shū)名稱(chēng)(VARCHAR),作者(VARCHAR),出版社(VARCHAR),出版日期(DATETIME),價(jià)格(DECIMAL),庫(kù)存數(shù)量(INT)*訂單(Order):訂單ID(INT,主鍵),用戶(hù)ID(INT,外鍵),下單時(shí)間(DATETIME),訂單狀態(tài)(VARCHAR)*訂單詳情(OrderDetail):詳情ID(INT,主鍵),訂單ID(INT,外鍵),圖書(shū)ID(INT,外鍵),購(gòu)買(mǎi)數(shù)量(INT),單價(jià)(DECIMAL)*關(guān)系：*用戶(hù)與訂單：一對(duì)多(一個(gè)用戶(hù)可以有多個(gè)訂單)*訂單與訂單詳情：一對(duì)多(一個(gè)訂單可以包含多行詳情)*圖書(shū)與訂單詳情：多對(duì)多(一本書(shū)可以在多個(gè)訂單中出現(xiàn)，一個(gè)訂單可以包含多本書(shū))。通過(guò)`訂單詳情`關(guān)聯(lián)表實(shí)現(xiàn)。*主鍵：用戶(hù)ID,圖書(shū)ID,訂單ID,詳情ID。*外鍵：`訂單`表的`用戶(hù)ID`參照`用戶(hù)`表的`用戶(hù)ID`；`訂單詳情`表的`訂單ID`參照`訂單`表的`訂單ID`；`訂單詳情`表的`圖書(shū)ID`參照`圖書(shū)`表的`圖書(shū)ID`。*關(guān)鍵設(shè)計(jì)決策：1.訂單狀態(tài)管理：在`訂單`表中增加`訂單狀態(tài)`字段（如'待付款','已付款','已發(fā)貨','已完成','已取消'），便于追蹤和管理訂單流程。2.庫(kù)存管理：在`圖書(shū)`表中增加`庫(kù)存數(shù)量`字段。在創(chuàng)建訂單詳情時(shí)，需要檢查并更新`圖書(shū)`表的`庫(kù)存數(shù)量`，確保庫(kù)存準(zhǔn)確。這需要在業(yè)務(wù)邏輯層或數(shù)據(jù)庫(kù)層面（觸發(fā)器）實(shí)現(xiàn)。3.關(guān)聯(lián)表字段：在`訂單詳情`表中，除了`訂單ID`,`圖書(shū)ID`,`購(gòu)買(mǎi)數(shù)量`，建議增加`單價(jià)`字段，記錄下單時(shí)的圖書(shū)價(jià)格，避免因圖書(shū)調(diào)價(jià)導(dǎo)致訂單金額變動(dòng)。2.解析思路：分析現(xiàn)有設(shè)計(jì)問(wèn)題：`員工`表中的`部門(mén)ID`是外鍵但無(wú)對(duì)應(yīng)`部門(mén)`表的主鍵引用（或引用不明確），導(dǎo)致數(shù)據(jù)不一致可能；`郵箱`字段無(wú)約束可能導(dǎo)致重復(fù)。優(yōu)化方案：創(chuàng)建`部門(mén)`表并讓`員工`表通過(guò)`部門(mén)ID`外鍵關(guān)聯(lián)；為`員工.部門(mén)ID`添加`NOTNULL`約束（假設(shè)允許員工屬于部門(mén)）；為`員工.郵箱`添加`UNIQUE`約束。答案：主要問(wèn)題：1.數(shù)據(jù)冗余與不一致：`部門(mén)`信息直接存儲(chǔ)在`員工`表的`部門(mén)ID`字段中，如果部門(mén)名稱(chēng)需要變更，需要更新所有相關(guān)員工記錄，容易出錯(cuò)且效率低。`部門(mén)`表缺少自身的主鍵和可能的其他信息（如部門(mén)領(lǐng)導(dǎo)、地點(diǎn)），導(dǎo)致結(jié)構(gòu)不清晰，數(shù)據(jù)難以維護(hù)。2.外鍵約束缺失：`員工`表中的`部門(mén)ID`字段雖然名義上是關(guān)聯(lián)`部門(mén)`，但題目未明確其作為外鍵的約束（如`FOREIGNKEY(部門(mén)ID)REFERENCES部門(mén)(部門(mén)ID)`），這可能導(dǎo)致插入不存在的`部門(mén)ID`，破壞數(shù)據(jù)完整性。優(yōu)化方案：1.創(chuàng)建獨(dú)立的`部門(mén)`表：`部門(mén)`（`部門(mén)ID`INTPRIMARYKEY,`部門(mén)名稱(chēng)`VARCHARNOTNULL,`部門(mén)負(fù)責(zé)人ID`INTNULL,`部門(mén)地點(diǎn)`VARCHARNULL,...）。2.修改`員工`表：刪除原有的`部門(mén)ID`字段（如果只是ID關(guān)聯(lián)），或者保留但作為普通字段，然后添加外鍵約束`FOREIGNKEY(部門(mén)ID)REFERENCES部門(mén)(部門(mén)ID)ONDELETESETNULLONUPDATECASCADE`。如果保留`部門(mén)ID`且是唯一標(biāo)識(shí)，則應(yīng)設(shè)為外鍵。3.添加約束：為`員工`表的`郵箱`字段添加`UNIQUE`約束，保證每名員工的郵箱地址唯一。這樣優(yōu)化后，`部門(mén)`信息被規(guī)范化管理，減少了冗余，更新和維護(hù)更加方便，數(shù)據(jù)一致性也得到了保證。三、評(píng)估與優(yōu)化題1.解析思路：場(chǎng)景是多對(duì)多關(guān)系。直接在基礎(chǔ)表添加關(guān)聯(lián)字段會(huì)導(dǎo)致該字段在非關(guān)聯(lián)行也存儲(chǔ)大量重復(fù)的空值或占位符，造成冗余。`選課`關(guān)聯(lián)表解決了這個(gè)問(wèn)題，通過(guò)存儲(chǔ)實(shí)際的連接記錄。答案：*數(shù)據(jù)冗余問(wèn)題：如果直接在`學(xué)生`表和`課程`表中各增加一個(gè)對(duì)方的ID字段來(lái)表示選課關(guān)系，那么對(duì)于沒(méi)有選特定課程的學(xué)生，其課程ID字段將存儲(chǔ)大量`NULL`值；同樣，對(duì)于沒(méi)有學(xué)生選修的課程，其學(xué)生ID字段也將存儲(chǔ)大量`NULL`值。這不僅浪費(fèi)存儲(chǔ)空間，也使得數(shù)據(jù)行變得不均勻，查詢(xún)時(shí)需要處理大量的空值，影響效率。*關(guān)聯(lián)表方案建議：*設(shè)計(jì)`選課`關(guān)聯(lián)表：`選課`（`選課ID`INTPRIMARYKEY,`學(xué)生ID`INTNOTNULL,`課程ID`INTNOTNULL,`選課時(shí)間`DATETIMEDEFAULTCURRENT_TIMESTAMP,`成績(jī)`DECIMALNULL,...）*增加字段理由：1.`選課時(shí)間`：記錄選課行為發(fā)生的時(shí)間，可能有統(tǒng)計(jì)需求。2.`成績(jī)`（或`分?jǐn)?shù)`）：如果系統(tǒng)需要記錄學(xué)生每門(mén)課的成績(jī)，應(yīng)在此表增加該字段。3.（可選）`學(xué)分`：如果學(xué)分與課程綁定且不常變動(dòng)，可以放在`課程`表；如果選課時(shí)才確定學(xué)分（如選了特定項(xiàng)目），則可能需要在此表記錄。*優(yōu)化查詢(xún)性能方法：1.添加索引：在`選課`表的`學(xué)生ID`和`課程ID`字段上都創(chuàng)建索引。這樣查詢(xún)某個(gè)學(xué)生的所有課程或某個(gè)課程的所有學(xué)生時(shí)，數(shù)據(jù)庫(kù)可以利用索引快速定位相關(guān)記錄，避免全表掃描。2.反范式設(shè)計(jì)（適度）：如果查詢(xún)非常頻繁且性能要求極高，可以考慮在`學(xué)生`表或`課程`表中添加冗余字段，直接存儲(chǔ)關(guān)聯(lián)信息。例如，在`學(xué)生`表中增加`已選課程數(shù)量`字段，或存儲(chǔ)最近選的`課程ID`。但這會(huì)增加數(shù)據(jù)更新的復(fù)雜性，需謹(jǐn)慎使用。四、實(shí)踐應(yīng)用題（假設(shè)使用Python和SQLite）解析思路：使用`sqlite3`庫(kù)連接數(shù)據(jù)庫(kù)。使用`cursor.execute()`執(zhí)行`INSERT`語(yǔ)句插入作者數(shù)據(jù)。同樣使用`INSERT`語(yǔ)句插入圖書(shū)數(shù)據(jù)，注意`author_id`需要引用前面插入的作者的ID。編寫(xiě)參數(shù)化的SQL查詢(xún)語(yǔ)句，使用`?`占位符或命名參數(shù)，并使用`cursor.execute()`和`cursor.fetchall()`或`cursor.fetchone()`獲取結(jié)果。最后使用`print()`輸出結(jié)果。答案：```pythonimportsqlite3#連接到library.db數(shù)據(jù)庫(kù)（若不存在則創(chuàng)建）conn=sqlite3.connect('library.db')cursor=conn.cursor()#插入作者數(shù)據(jù)cursor.execute("INSERTINTOauthors(name,birth_year)VALUES(?,?)",('J.K.Rowling',1965))cursor.execute("INSERTINTOauthors(name,birth_year)VALUES(?,?)",('GeorgeOrwell',1903))#獲取剛插入的作者的ID（假設(shè)是最后插入的ID，實(shí)際應(yīng)用中可能需要確認(rèn)或使用RETURNINGID）#注意：這里簡(jiǎn)化處理，實(shí)際可能需要使用cursor.lastrowid或更可靠的方法獲取特定作者的ID#假設(shè)第一本書(shū)記錄的是第一位作者，第二本書(shū)記錄的是第二位作者first_author_id=1#假設(shè)第一個(gè)作者插入后ID為1second_author_id=2#假設(shè)第二個(gè)作者插入后ID為2#插入圖書(shū)數(shù)據(jù)cursor.execute("INSERTINTObooks(title,publication_year,author_id)VALU