往年期末试题答案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注：全部为基础题。选择题中包含本课程教学大纲中所要求掌握的大部分知识点，主要是考核学生对于基本概念的理解和掌握程度。(SELECT customer_number FROM deitor);4) (2 分)SELECT MAX(balance) FROM account;注：该题为基础题，考核编写 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)注：该题为综合题，考核了函数依赖、范式、关系分解的知识点。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 小题为基础题，考核数据更新操作的 SQL 语句；其它均为综合题，考核子查询、数据删除、表的新建、主键、外键和 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)注：该题为基础题，考核了如何将 ER 图转换为关系模式的知识点。三（共 27 分）1（7 分）1)(1 分)课程名称：数据库系统（英） 学分： 3 教学大纲：0602250

7、5试卷：A方式：闭卷 满分分值：70时间：120分钟2（7 分）1) （1 分）SELECT DISTINCT Loan_number FROM Loan WHERE amount1200;2) （2 分）第1 页共2页理工大学课程及评分标准课程名称：数据库系统（英） 学分： 3 教学大纲：06022505试卷：A方式：闭卷 满分分值：70时间：120分钟试卷中：基础题共 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