当前位置：首页 > 2007数据库复习题及答案
FOR $outer in (friends.xml)//person, LET $child := $outer/children
WHERE ($outer/cellphone > 2000 ) RETURN $outer/id
FOR $inner IN (employees.xml)/employees/emp[id=$outer/id] RETURN {
$outer/cellphone $child/child
$inner/workphone $inner/address/city }
1) List the XML output that the XQUERY expression would generate when applied to the given XML input documents.
2) Design a relational schema to store the two given XML data files.
3) Design an object-relational schema to store the two given XML data files.
4) List the SQL query that you would generate to execute the given XQUERY expression on your relational database. State what final computations would remain to be done by the XQUERY processor beyond executing your SQL statement, if any. 解:
1)
2) person(pid, cellphone, name) child(cid, parentid, name) toy(tid, cid, toy_name) emp(pid, workphone, contact, city, zip, street) 3) person(pid, name, cellphoneSet MultiSet(cellphones), ChildSet MultiSet(children))
5
cellphones(cellphone)
children(name, toySet MultiSet(toys)) toys(toyname)
emp(pid, workphone, contact, city, zip, street) 4) select person.cellphone,
array( select child.name child.toy form child
where child.parentid=person.pid) as child_array,
emp.workphone, emp.city from person, child, emp
where person.pid=emp.pid AND person.cellphone>2000 第五题:
Suppose you have to represent the information about parts. Each part has a name (unique),and a textual description. Parts may be simple or complex. A simple part has a color but no children subparts. A complex part has a number of children subparts (which can be simple or complex), each of which may be repeated. (E.g., a car has 4 wheels.) You can assume that each part can be a child subpart of at most one other part (so each part, together with its subparts, can be viewed as a tree). Do not assume any fixed number of levels of part composition.
1) Define the schema of XML documents containing part information using DTDs. 2) Give an example of a document instance which is valid under the DTDs. 3) Write the following queries in XQuery: (a) find the names of all the yellow parts.
(b) find all the parts that have at least 5 distinct children subparts.
(c) find all the parts containing a descendant subpart named “spoke\ not containing a descendant subpart named “tire\ Answer: ANSWER:
1）
]>
2)
6
3)(a) for $p in /parts/part
where $p/color=”yellow” return $p/@name (b) for $p in /parts/part
where count(distinct($p/subpartinfo))>=5 return $ p/@name (c) for $p in /parts/part
where ($p/@name=”sopke”)and(not($p/@name=”tire”)) return $p/@name 第六题:
Consider these XML elements for the 'prefix' and 'infix' application of a binary function, here add, to its two variable arguments, here x and y:
Complete the following XSLT template - by just filling in the seven versions of \- for the (XML-to-XML) transformation of 'prefix' applications into 'infix' applications:
<_larg_>
Could this transformation be 'inverted' - mapping 'infix' applications to 'prefix' applications - without information loss (write in only \Answer: No!!!!! 第七题:
Consider the relation PARTS, which has Part# as hash key and which includes records with the following Part# values: 2369, 3760, 5046, 4871, 5659, 2222, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 3157, 6975,
7
4981, 9208.The hash function uses 8 buckets, numbered 0 to 7. Each bucket is one disk block and holds two records.
1) Load these records into the file in the given order using the hash function h(K)=K mod 8. Calculate the average number of block accesses for a random retrieval on Part#.
2) Now load the records into expandable hash files based on linear hashing. Start with a single disk block, using the hash function h0= K mod 2o, and show how the file grows and how the hash functions change as the records are inserted. Assume that blocks are split whenever an overflow occurs, and show the value of n at each stage. 解:1)
平均查找代价：(8＋6＊2＋3＋3＋3）/ 17＝1.71 2)如下页图 第八题:
设关系r1(A，B，C)，r2(C，D，E)有如下特性：r1 有200 000 个元组，r2 有45 000 个元组，一块中可容纳25 个r1 元组或30 个r2 元组。试估算以下每一种策略计算r1|><|r2 所需存取的块数： 1) 嵌套循环连接 2) 块嵌套循环连接 3) 归并连接 4) 散列连接
解：r1需要8000个块，r2需要1500个块。假设有一个存储器有M页。如果M>8000，那么使用平坦嵌套循环，通过1500＋8000次磁盘存取就可以很容易的完成连接操作。因此我们只考虑M<=8000的情况。 A． 嵌套循环连接：
使用r1作为外关系，我们需要进行200 000×1500＋8000＝300，008，000次磁盘存取。如果r2是外关系，那么我们需要45 000×8 000＋1 500＝360 001 500次磁盘存取。 B． 块嵌套循环连接：
如果r1是外关系，我们需要??8000/(M?2)??×1500＋8000次磁盘存取，如果r2是外关系，我们需要
??1500/(M-2)??×8000＋1500次磁盘存取。
C.归并连接
假设r1和r2最初没有按连接关键字进行排序，那么总的排序加上输出的耗费为Bs＝1500（2
??logM-1(1500/M)??＋1）＋8000（2 ??logM-1(8000/M)??＋1）次磁盘存取。假设具有相同连接属性值的所
有员组装入内存中，那么总的耗费是Bs＋1500＋8000次磁盘存取。 d.散列连接
我们假设不发生溢出。因为r2比较小，所以我们用r2作为创建关系，用r1作为探针关系。如果M>1500，那么就不需要进行递归分割，于是耗费为3（1500＋8000）＝28 500次磁盘存取，否则耗费为2（1500＋8000）??logM-1(1500/M)+2??＋1500＋8000次磁盘存取。
8
本文作者：...共分享92篇相关文档
