往年期末試題答案db2008a答案

1、理工大學課程及評分標準(SELECT customer_name FROM cutomer NATURAL JOIN borrower) UNION(SELECT customer_name FROM cutomer NATURAL JOIN deitor);3) （2 分）SELECT DISTINCT customer_number FROM LoanWHERE branch_name=Perryridge AND customer_number NOT IN （每題 1 分，共 25 分）110 D A D D A1120 D C B C A2125 C B C A CB A B D A

2、 C B A B C注：全部為基礎(chǔ)題。選擇題中包含本課程教學大綱中所要求掌握的大部分知識點，主要是考核學生對于基本概念的理解和掌握程度。(SELECT customer_number FROM deitor);4) (2 分)SELECT MAX(balance) FROM account;注：該題為基礎(chǔ)題，考核編寫 SQL 語句完成簡單查詢的知識點。二 (共 18 分) 1、(10 分)a)b)clerk - store, store - city, item# - key of sale: clerk, date, item#, sizeprice(3 分)3（6 分）1) （2 分）DE

3、LETE FROM accountWHERE branch_name IN (SELECT branch_name FROM BranchWHERE branch_city = Needham);2) （2 分）UPDATE account SET balance=balance1.06 WHERE balance10000;3) （2 分）CREATE TALBE account(key of item: item#, size(2 分)(2 分)(clerk, store),c)d)sale: 1NFitem: 1NF(clerk, date, item#, size),(store,ci

4、ty),(3 分)(item#, size), (item#, price)注：該題為綜合題，考核了函數(shù)依賴、范式、關(guān)系分解的知識點。2、(8 分)Words(stem, root_word)或 Words(stem, synonym_word)account_numberchar(10),char(15),branch_name balanceWordContexts(pagenumber, ition, actualText, word_stem)float,PRIMARY KEY (account_number), FOREIGN KEY (branch_name) REFEREN CH

5、ECK (balance = 0);Pages(number, work_id)branch(branch_name),Work(id, tile, year, length, author_name, written_era)注：該題第 2 小題為基礎(chǔ)題，考核數(shù)據(jù)更新操作的 SQL 語句；其它均為綜合題，考核子查詢、數(shù)據(jù)刪除、表的新建、主鍵、外鍵和 check 約束等知識點。Author(name, birth, death, description)4（7 分）1) （2 分）SELECT branch_name, AVG(balance) FROM accountGROUP BY bra

6、nch_nameHAVING AVG(balance) 1200;2) （2 分）SELECT branch_name, assets, COUNT(distinct customer_number) FROM deitor, branchWHERE deitor.branch_name = branch.branch_name GROUP BY branch_name, assets;Period(era, region)注：該題為基礎(chǔ)題，考核了如何將 ER 圖轉(zhuǎn)換為關(guān)系模式的知識點。三（共 27 分）1（7 分）1)(1 分)課程名稱：數(shù)據(jù)庫系統(tǒng)（英） 學分： 3 教學大綱：0602250

7、5試卷：A方式：閉卷 滿分分值：70時間：120分鐘2（7 分）1) （1 分）SELECT DISTINCT Loan_number FROM Loan WHERE amount1200;2) （2 分）第1 頁共2頁理工大學課程及評分標準課程名稱：數(shù)據(jù)庫系統(tǒng)（英） 學分： 3 教學大綱：06022505試卷：A方式：閉卷 滿分分值：70時間：120分鐘試卷中：基礎(chǔ)題共 51 分，占 73；綜合題共 14 分，占 20；提高題共 5 分，占 7。3) （3 分）CREATE VIEW v_borrower ASSELECT customer.customer_number, customer

8、_name, SUM(amount) AS total_amount FROM customer, borrower, loanWHERE customer.customer_number=borrower.customer_number AND borrower.loan_number=loan.loan_numberGROUP BY customer.customer_number, customer_name;CREATE VIEW v_deitor ASSELECT customer.customer_number, customer_name, SUM(balance) AS tot

9、al_balance FROM customer, deitor, accountWHERE customer.customer_number=deitor.customer_number AND deitor.account_number=account.account_numberGROUP BY customer.customer_number, customer_name;SELECT customer_number, customer_name FROM v_borrowerWHERE total_amount=(SELECT MAX(total_amount) FROM v_borrower) AND customer_number IN(SELECT customer_number FROM v_deitorWHERE total_balance=(SELECT MAX(total_balance)FROM v_deitorWHERE customer_number IN(SELECT customer_numb