华南农业大学期末考试试卷（A 卷-Answer Sheets ） 2012学年第1 学期 考试科目： Database system 考试类型：（闭卷） 考试时间： 120 分钟 学号 姓名 年级专业 Instructions to candidates: 1. Write your name, student number and class on both the question papers and the answer papers. 2. DO NOT write your answers on the question papers. Write them ALL ON THE ANSWER PAPERS. 3. Write your answers in either Chinese or English. If the answer in English is correct, you can get bonus marks. 3. Hand in all papers (both the question papers and the answer papers). Question 2 [12 marks]:
(1) An invoice has attributes: Invoice#(primary key), TotalOrderAmt, Date, Terms, ShipVia. A customer has attributes: Cust#(primary key), CName, Street, City, State, Zip, Phone. A product has attributes: Prod#(primary key), StandardPrice, Description. The relationship between invoice and customer is many-to-one. One invoice can relate to only one customer, while one customer can relate to any number of invoices. The relationship between invoice and product is many-to-many. Any number of products can be placed in one invoice, and one product can appear in different invoices. The relationship between invoice and product has two attributes: SellPrice and Quantity.
(2) create table Invoice
(Invoice_Number char(10),
TotalOrderAmt integer,
Invoice_Date date,
Terms char(30),
ShipVia char(20),
Cust_Number char(10),
Primary key(Invoice_Number),
Foreign key(Cust_Number) references Customer)
Create table Customer
(Cust_Number char(10),
CName char(10),
Street char(30),
City char(10),
State char(10),
Zip char(10),
Phone char(20),
Primary key(Cust_Number))
Create table Product
(Prod_Number char(10),
StandardPrice number,
Description char(30),
Primary key(Prod_Number))
Create table LineItem
(Invoice_Number char(10),
Prod_Number char(10),
SellPrice number,
Quantity number,
Primary key(Invoice_Number, Prod_Number) Foreign key(Invoice_Number) references Invoice, Foreign key(Prod_Number) references Product) Question 3 [8 marks]: (1) ∏CHAPTER,ENO,POINTS δFIRST=’Ann ’ ∧LAST=’Smith ’(STUDENTS RESULTS) (2) ∏FIRST,LAST,CHAPTER,ENO (STUDENTS RESULTS ( CHAPTER,ENO G MAX(POINTS) RESULTS) or ∏FIRST,LAST,CHAPTER,ENO (STUDENTS δPOINTS=MAXPOINT (RESULTS EXERCISES) (3) ∏CHAPTER,ENO,POINTS (RESULTS δEMAIL=’%scau%’STUDENTS) (4) Question 4 [12 marks]: (1) select cno from computer where memory_size>2 and disk_size>500 (2) select avg(price) from manufacturer, shop, purchase where purchase.sno=shop.sno and shop.scity=’Guangzhou ’ and purchase.mno=manufacturer.mno and manufacturer.mcity=’Beijing ’ and o=’LX3000’ (3) select cno from purchase where shop.scity=’Guangzhou ’ and price = select max(price) from purchase, shop where purchase.sno=shop.sno and shop.scity=’Guangzhou ’ (4) select mname from manufacturer where mno not in (select mno from purchase) Question 5 [10 marks]
(1) The candidate keys for table T are D and AC because D +=ABCD
and (AC)+=ABCD
(2) 1NF. For A→B , A is not a superkey, and B is not contained in a candidate key,
thus T is not in 3NF. Every attribute in T has atomic domain, thus T is in 1NF.
(3) (a5, b6, c7, d8)
(4) For A→B , A is not a superkey, thus the original schema can be decomposed into
AB and ACD.
Question 6 [13 marks]:
(1) T1：lock-X(p1) read(p1) write(p1) lock-S(p2)read(p2) lock-X(p3) unlock(p1) unlock(p2)write(p3) unlock(p3)
T2: lock-X(p1) read(p1) write(p1) lock-X(p2) unlock(p1)read(p2) write(p2) unlock(p2)
T3: lock-X(p3)read(p3) write(p3) lock-S(p2) unlock(p3)read(p2) unlock(p2)
the equivalent serial schedule is :T3,T1,T2
(3) For ease of implementation, immediate modification is easier because the system don’t need to defer the writes. For overhead cost, deferred modification uses redo operation in recovery, while immediate modification uses redo and undo operations in recovery, thus immediate modification has higher cost.。