推 zack30106opz:!!! 11/09 15:43
→ andy74139 :已收錄至資訊系精華區!! 11/09 23:29
課程名稱︰資料庫系統
課程性質︰必修
課程教師︰朱浩華
開課學院：電資
開課系所︰資工
考試日期（年月日）︰2008
考試時限（分鐘）：180
是否需發放獎勵金：是
（如未明確表示，則不予發放）
試題 :
I here with state that I understand and will adhere to the following
academic integrity: "I will not use or attempt to use any unauthorized
assistance, material, or study aids in this midterm examination. If I violate
academic integrity, I may receive a grade of 0."
__________________________
Signature
(2 points)
PART A: ER Diagram (24 Points)
(1) (12 points) In the space below, draw an E/R diagram that best represents
the following scenario. Indicate keys, many-one relationships, weak entity
sets, and other features of E/R diagrams covered in class, as appropriate.
There are hospitals, with a name and address. Address is unique.
There rooms in hospitals, with a room number.
There are people, with a name and social-security number.
Some people are doctors, and doctors have a specialty; some people are
patients, and patients have an age.
An operation involves a patient and a doctor, and takes place in a hospital
room. And operation has a date and time.
For full credit, do not make operations an entity set; rather, make it a
relationship.
(2) (12 points) Translate the above ER diagram into relational tables. Use SQL
create_table commaned to define all the relational tables.
PART B: Relational Algebra (20 Points)
Consider the following four relations to store data about animals in a zoo:
Animals(idNumber,type,cageNumber)
TypeKeepers(name, type)
CageKeepers(name,cageNumber)
idNumber is a key for Animals, and cageNumber is a key for Cages. TypeKeepers
lists people responsiible for caring for a particular type of animal, while
CageKeepers lists people responsible for caring for a certain cage. For each
of these two, both attributes from the key; i.e. it is possible for the same
person to care for multiple types/cages, and it is possible for the same
type/cage ro be cared for by several people.
(1) (6 points) Consider the following sequence of relational algebra statement:
Result = (Animals∞CageKeepers)∩(Animals∞Typekeepers)
Given the following sets of data for each of the relations, show (in the space
on the next page) the relation that will be stored into Result table (show
the schema and all the tuples that will be in the relation).
Animals
idnumber tupe cageNumber
1 Zebra 10
2 Monkey 20
3 Monkey 10
4 Kangaroo 30
5 Zebra 40
6 Monkey 40
Cagekeepers TypeKeepers
name cageNumber name Type
Ann 10 Ann Zebra
Chris 20 Bob Monkey
Chris 30 Ann Kangaroo
Bob 40 Chris Kangaroo
Bob 10
(2) (6 points) State briefly in English what the query from (1) is asking.
(3) (8 points) Write a relational algebra expression to find all pairs of
animals of two different types in the same cage. For each pair of animal
satisfying this requirement, your expression should return each animal's
idNumber and type, as well as the cageNumber of the cage they are in. For
example, if Animal 1 of type Zebra and Animal2 of type Monkey are both in
cage 10, then the result of your expression should include a tuple
(1,Zebra,2,Monkey,10) (the attribulte could be in a different order).
The same animal pair should not apperr twice in your result.
PART C: Query Equivalences (26 Points)
Each row of the table below shows two queries. In the blank third column of
the table write "YES" if the two queries are equivalent, and "NO" if they
are not equivalent. Remember that two queries are equivalent if they always
return exactly the same answer on all databases.
All queries refer to relations R(A,B) and/or S(A,B).
In both relations R and S, attribute A is a key and attribute B is not a key.
No attributes are permitted to contain NULL values.
Do not make any other assumptions about the data.
To lessen the score for guessing, you will earn 3 points for each correct
answer, -1 points for each incorrect answer, and 0 points for each answer
left blank.
PART D: SQL (16 Points)
The relations below represent information about the 15 members of the UN
Security Council and the resolutions (res) that they voted on.
Members(coutry-name,status)
Votes(res,country-name,vote)
The status of a country is either 'permanent' or 'elected'. A vote can be
either 'yes','no', or 'abstain'.
(1) (8 points) In order for a resolution to pass, there must be at least 9
'yes' votes from among the 15 members of the council. In addition, there
must be no veto; that is, none of the five permanent members of the council
vote 'no' on that resolution. We wish to write a query to find the set of
resolutions that passed.
(2) (8 points) Write a query to list each permanent member of the council,
along with the number of vetos('no' votes) they have cast. We must not forget
to consider the possibility that a permanent member cast no vetos.
PART E: More SQL(16 Points)
(1) (8 points) Consier a SQL table T(A int). Assume there are no NULL values.
Make no assumptions about keys. We execute the following SQL query:
select sum(B)
from T, (select distinct A, 1 as B from T) as T2
where T.A = T2.A and T2.A = 3
In one sentence, state in English what this query computes. The correct answer
is simple and brief.
(2) (8 points) Consider a SQL table T(A int, B int, C int). Assume there are
no NULL values.
Suppose the following SQL query always returns an empty result.
select *
from (select T1.A, T1.B, T2.C
from T as T1, T as T2
where T1.A = T2.A) as T3
where (T3.A, T3.B, T3.C) not in (select * from T)
Assuming we know nothing else about T, what does this behavior tell us about
the table T? The correct answer is simple and brief.
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.112.30.97